最近DBA反馈我们有个数据库实例下磁盘快满了，需要我们看看有没有优化空间。这时候就需要我们来查看统计下实例下各数据库数据表的数据分布，从而来做出优化方案。这里就记录一下查询数据库容量的相关SQL。

1.查询总的数据量

SELECT
SUM(truncate((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024/1024,2)) AS 'total(GB)'
FROM information_schema.TABLES

total(GB)
1488.37

2.查询所有库的数据量

SELECT
table_schema,SUM(truncate((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024/1024,2)) AS 'total(GB)'
FROM information_schema.TABLES group by table_schema;

table_schema    total(GB)
database1   44.61
database2   1443.76
information_schema  

3.查询database1库的数据量

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024/1024, 2)) as '数据容量(GB)',
sum(truncate(index_length/1024/1024/1024, 2)) as '索引容量(GB)'
from information_schema.TABLES
where table_schema='database1';

数据库     记录数     数据容量(GB)    索引容量(GB)
database1   616625904       9.44            25.70

4.查询database1库下每个表的数据量

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='database1'
order by data_length desc, index_length desc limit 100;

数据库     表名      记录数 数据容量(MB)    索引容量(MB)
database1   table1  114971053   59346.00        22366.00
database1   table2  6508752     4219.00     1292.04
database1   table3  783227      3786.95     239.23
database1   table4  606758      3742.96     234.17
database1   table5  896421      3686.98     220.25
database1   table6  940418      3579.96     214.18
database1   table7  850946      3459.96     212.18
database1   table8  845201      3397.95     214.23

参考文章：https://www.cnblogs.com/–smile/p/11451238.html