TTP
MySQL统计数据库容量大小
07/12
本文最后更新于2022年07月12日,已超过28天没有更新。如果文章内容或图片资源失效,请留言反馈,我会及时处理,谢谢!
MySQL统计数据库容量大小
查询所有数据库的总容量大小
use information_schema;
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;
+-----------+
| data |
+-----------+
| 3052.76MB |
+-----------+
1 row in set (0.02 sec)
统计每个库的大小
select table_schema,sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb from information_schema.TABLES group by table_schema;
查看指定数据库大小
-
示例:查看test数据库大小
use information_schema; select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='test'; +----------+ | data | +----------+ | 142.84MB | +----------+ 1 row in set (0.00 sec) select table_schema as '数据库',sum(table_rows) as '记录数',sum(truncate(data_length/1024/1024,2)) as '数据容量(MB)',sum(truncate(index_length/1024/1024,2)) as '索引容量(MB)' from information_schema.tables where table_schema='test';
查看所有数据库各表的大小
select table_schema as '数据库',table_name as '表名',table_row as '记录数',truncate(data_length/1024/1024,2) as '数据容量(MB)',truncate(index_length/1024/1024,2) as '索引容量(MB)' from information_schema.tables order by data_length desc,index_length desc;
查看指定数据库的各表大小
-
比如查看mysql各表大小
select table_schema as '数据库',table_name as '表名',table_row as '记录数',truncate(data_length/1024/1024,2) as '数据容量(MB)',truncate(index_length/1024/1024,2) as '索引容量(MB)' from information_schema.tables where table_schema='mysql' order by data_length desc,index_length desc;