在实际生产过程中，数据库可能会堆积一些无用的数据，通过查询占用空间大小，可以在一定程度上帮助我们分析问题。
information_schema说明
通过MySQL的 information_schema 数据库，可查询数据库中每个表占用的空间、表记录的行数；该库中有一个 TABLES 表，这个表主要字段分别是：
字段名 值
TABLE_SCHEMA 数据库名
TABLE_NAME 表名
ENGINE 所使用的存储引擎
TABLES_ROWS 记录数
DATA_LENGTH 数据大小
INDEX_LENGTH 索引大小
查看所有库的大小
SELECT
TABLE_SCHEMA AS 'Database Name',
CONCAT(ROUND(SUM(data_length/(1024*1024)),2),' M') AS 'Data Size',
CONCAT(ROUND(SUM(index_length/(1024*1024)),2),' M') AS 'Index Size',
CONCAT(ROUND(SUM((data_length+index_length)/(1024*1024)),2),' M') AS'Total'
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
ORDER BY SUM(data_length+index_length) DESC;
查询结果示例如下:
+--------------------+-----------+------------+----------+
| Database Name | Data Size | Index Size | Total |
+--------------------+-----------+------------+----------+
| test | 308.88 M | 14.31 M | 323.20 M |
| mysql | 2.20 M | 0.18 M | 2.39 M |
| information_schema | 0.16 M | 0.00 M | 0.16 M |
| sys | 0.02 M | 0.00 M | 0.02 M |
| performance_schema | 0.00 M | 0.00 M | 0.00 M |
+--------------------+-----------+------------+----------+
查看指定库的大小
在查看所有库大小的基础上添加WHERE条件:
SELECT
TABLE_SCHEMA AS 'Database Name',
CONCAT(ROUND(SUM(data_length/(1024*1024)),2),' M') AS 'Data Size',
CONCAT(ROUND(SUM(index_length/(1024*1024)),2),' M') AS 'Index Size',
CONCAT(ROUND(SUM((data_length+index_length)/(1024*1024)),2),' M') AS'Total'
FROM information_schema.TABLES
WHERE table_schema='test'
GROUP BY TABLE_SCHEMA;
查询结果示例如下:
+--------------------+-----------+------------+----------+
| Database Name | Data Size | Index Size | Total |
+--------------------+-----------+------------+----------+
| test | 308.88 M | 14.31 M | 323.20 M |
+--------------------+-----------+------------+----------+
查看指定库的所有表的大小
SELECT
CONCAT(table_schema,'.',table_name) AS 'Table Name',
table_rows AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024),2),' M') AS 'Data Size',
CONCAT(ROUND(index_length/(1024*1024),2),' M') AS 'Index Size',
CONCAT(ROUND((data_length+index_length)/(1024*1024),2),' M') AS'Total'
FROM information_schema.TABLES
WHERE table_schema='test'
ORDER BY (data_length+index_length) DESC;
查询结果示例如下:
+-------------------+----------------+-----------+------------+----------+
| Table Name | Number of Rows | Data Size | Index Size | Total |
+-------------------+----------------+-----------+------------+----------+
| test.Message | 268683 | 33.58 M | 14.03 M | 47.61 M |
| test.Notification | 35381 | 24.55 M | 0.00 M | 24.55 M |
| test.Order | 3546 | 5.52 M | 0.17 M | 5.69 M |
| test.AccessToken | 41850 | 4.55 M | 0.00 M | 4.55 M |
| test.user | 365 | 0.13 M | 0.00 M | 0.13 M |
+-------------------+----------------+-----------+------------+----------+
查看指定库的指定表的大小
在查看所有表大小的基础上添加and table_name=条件:
SELECT
CONCAT(table_schema,'.',table_name) AS 'Table Name',
table_rows AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024),2),' M') AS 'Data Size',
CONCAT(ROUND(index_length/(1024*1024),2),' M') AS 'Index Size',
CONCAT(ROUND((data_length+index_length)/(1024*1024),2),' M') AS'Total'
FROM information_schema.TABLES
WHERE table_schema='test' and table_name='Message'
ORDER BY (data_length+index_length) DESC;
查询结果示例如下:
+-------------------+----------------+-----------+------------+----------+
| Table Name | Number of Rows | Data Size | Index Size | Total |
+-------------------+----------------+-----------+------------+----------+
| test.Message | 268683 | 33.58 M | 14.03 M | 47.61 M |
+-------------------+----------------+-----------+------------+----------+
