在 MySQL 中可以通过在 information_schema 数据库中查询有关数据库的很多元数据信息,如果想知道 MySQL 数据库中每个库或每个表占用的空间、以及表索引占用的空间和表记录的行数的话,可以通过其中的一个 TABLES 表进行查看,TABLES 表中的数据来查看当前某个库的大小,某个表的大小,以及某个表的索引大小和行记录等等。
这个表主要字段分别是:
字段 | 注释 |
TABLE_SCHEMA | 数据库名 |
TABLE_NAME | 表名 |
ENGINE | 所使用的存储引擎 |
ROW_FORMAT | 行格式 |
TABLE_ROWS | 表记录数 |
DATA_LENGTH | 表数据大小 |
INDEX_LENGTH | 表索引大小 |
DATA_FREE | 表碎片 |
其他字段请参考 MySQL 的手册,我们只需要了解这几个就足够了。
需要知道的是 MySQL 并没有真正的实时维护 DATA_LENGTH 和 INDEX_LENGTH 的值,而是定期刷新它们,而且是不规则地刷新它们,所以这部分数据的增长变化是没有规律的。但对于 TABLE_ROWS,DATA_FREE 则是有规律的更新,似乎是每次有 10% 的行更改时,就更新一次统计信息。
1)查看单个数据库的数据大小
1 |
SELECT sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名'; |
2)查看单个数据库的索引大小
1 |
SELECT sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名'; |
3)查看单个数据库的数据加索引大小(等于库占用空间的大小)
1 |
SELECT sum(INDEX_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名'; |
示例如下,数据加索引大小:
1 2 3 4 5 6 7 |
MySQL> SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='zabbix'; +------------------------------------+ | sum(DATA_LENGTH)+sum(INDEX_LENGTH) | +------------------------------------+ | 6799360 | +------------------------------------+ 1 row in set (0.07 sec) |
得到的结果是以字节为单位,除 1024 为 K,除 1048576 为 M,如下:
1 2 3 4 5 6 7 |
MySQL> SELECT concat((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(1024*1024),'M') FROM information_schema.TABLES where TABLE_SCHEMA='zabbix'; +--------------------------------------------------------------+ | concat((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(1024*1024),'M') | +--------------------------------------------------------------+ | 6.4844M | +--------------------------------------------------------------+ 1 row in set (0.00 sec) |
4)查看单个表的数据大小
1 |
SELECT sum(DATA_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名' AND table_name='表名'; |
5)查看单个表的索引大小
1 |
SELECT sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名' AND table_name='表名'; |
6)查看单个表的数据加索引大小(等于表占用空间的大小)
1 |
SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名' AND table_name='表名'; |
示例如下,单个表数据加索引大小:
1 2 3 4 5 6 7 |
mysql> SELECT concat((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(1024*1024),'M') FROM information_schema.TABLES where TABLE_SCHEMA='zabbixdb' AND TABLE_NAME='history'; +--------------------------------------------------------------+ | concat((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(1024*1024),'M') | +--------------------------------------------------------------+ | 0.0313M | +--------------------------------------------------------------+ 1 row in set (0.00 sec) |
7)查看单个表的行数
1 2 3 4 5 6 7 |
mysql> SELECT sum(TABLE_ROWS) FROM information_schema.TABLES where TABLE_SCHEMA='zabbix' AND table_name='history'; +-----------------+ | sum(TABLE_ROWS) | +-----------------+ | 73 | +-----------------+ 1 row in set (0.00 sec) |
8)查看表使用的存储引擎
1 |
mysql> select ENGINE from INFORMATION_SCHEMA.tables where TABLE_SCHEMA='zabbix' and table_name='history'; |
查看库中所有表的大小并排序
1 |
mysql> SELECT table_schema,table_name,(data_length/1024/1024),(index_length/1024/1024) FROM information_schema.tables where table_schema='zabbix' order by data_length; |
其实也可以使用 show 语法来查看表详细信息。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> show table status like 'history'\G *************************** 1. row *************************** Name: history Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 7526 Avg_row_length: 13560 Data_length: 102056264 Max_data_length: 281474976710655 Index_length: 820224 Data_free: 0 Auto_increment: 9000 Create_time: 2015-07-16 12:22:34 Update_time: 2017-04-13 17:01:23 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) |
查出来的跟 information_schema.TABLES 表中的信息一致。
另外,我们可以通过 information_schema.innodb_sys_tablespaces 查看表空间信息以查看实际文件大小。与 DATA_LENGTH 和 INDEX_LENGTH 不同,innodb_sys_tablespaces 是实时更新,无需特殊配置。
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from information_schema.innodb_sys_tablespaces where name='sbtest/cbd_order'\G *************************** 1. row *************************** SPACE: 814 NAME: sbtest/cbd_order FLAG: 33 FILE_FORMAT: Barracuda ROW_FORMAT: Dynamic PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single FS_BLOCK_SIZE: 4096 FILE_SIZE: 329714237440 ALLOCATED_SIZE: 329714618368 |
使用这个表的好处是,它还处理“InnoDB页压缩”,正确显示了 file_size(磁盘上的逻辑文件大小)和 allocated_size(为此文件分配的空间,并且可以显着缩小)之间的区别。
最后,让我们看一下不同的 InnoDB 压缩方式如何影响 information_schema 中提供的信息。
如果你使用旧的 InnoDB 压缩(InnoDB表压缩),你将看到 data_length 和 index_length 中显示的压缩数据大小作为结果。例如,avg_row_length 将远低于你的预期。
如果在 MySQL 5.7 中使用新的 InnoDB 压缩(InnoDB页压缩),你将看到文件大小相对应的值,而不是如 information_schema 中所示的分配大小。
<参考>
https://www.percona.com/blog/2016/01/26/finding_mysql_table_size_on_disk/