0%

MySQL查看库大小、表大小、索引大小

在实际生产过程中,数据库可能会堆积一些无用的数据,通过查询占用空间大小,可以在一定程度上帮助我们分析问题。

information_schema说明

通过MySQL的 information_schema 数据库,可查询数据库中每个表占用的空间、表记录的行数;该库中有一个 TABLES 表,这个表主要字段分别是:

字段名
TABLE_SCHEMA 数据库名
TABLE_NAME 表名
ENGINE 所使用的存储引擎
TABLES_ROWS 记录数
DATA_LENGTH 数据大小
INDEX_LENGTH 索引大小

查看所有库的大小

1
2
3
4
5
6
7
8
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;

查询结果示例如下:

1
2
3
4
5
6
7
8
9
+--------------------+-----------+------------+----------+
| 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条件:

1
2
3
4
5
6
7
8
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;

查询结果示例如下:

1
2
3
4
5
+--------------------+-----------+------------+----------+
| Database Name | Data Size | Index Size | Total |
+--------------------+-----------+------------+----------+
| test | 308.88 M | 14.31 M | 323.20 M |
+--------------------+-----------+------------+----------+

查看指定库的所有表的大小

1
2
3
4
5
6
7
8
9
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;

查询结果示例如下:

1
2
3
4
5
6
7
8
9
+-------------------+----------------+-----------+------------+----------+
| 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=条件:

1
2
3
4
5
6
7
8
9
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;

查询结果示例如下:

1
2
3
4
5
+-------------------+----------------+-----------+------------+----------+
| Table Name | Number of Rows | Data Size | Index Size | Total |
+-------------------+----------------+-----------+------------+----------+
| test.Message | 268683 | 33.58 M | 14.03 M | 47.61 M |
+-------------------+----------------+-----------+------------+----------+