MySQL统计数据库表容量大小

说明：通过information_schema.tables 视图表来计算MySQL库表容量大小,注意此视图统计大小信息有一定的延迟，并不是实时同步，可以使用碎片执行命令 ANALYZE TABLE

查看所有数据库容量大小

# SQL语句
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
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

# 查询结果
+--------------------+-----------+------------------+------------------+
 数据库              记录数     数据容量(MB)      索引容量(MB)     
+--------------------+-----------+------------------+------------------+
 lpb                  50073838           1764.03              5.54 
 mysql                    4267              2.04              0.28 
 world                    5269              0.58              0.16 
 wordpress                 186              0.30              0.40 
 lxf                         7              0.03              0.00 
 sys                         6              0.01              0.00 
 test001                     0              0.01              0.00 
 lzb                         0              0.01              0.00 
 kvdnc_master1               3              0.01              0.00 
 kvdnc_master2               4              0.01              0.00 
 __tencentdb__               0              0.01              0.00 
 information_schema          0              0.00              0.00 
 performance_schema    2856341              0.00              0.00 
+--------------------+-----------+------------------+------------------+

查看所有数据库各表容量大小

# SQL语句
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
order by data_length desc, index_length desc;

# 查询结果
+-----------+----------------+-----------+------------------+------------------+
 数据库     表名            记录数     数据容量(MB)      索引容量(MB)     
+-----------+----------------+-----------+------------------+------------------+
 lpb        qiqios           49893861           1752.00              0.00 
 lpb        test_flush_log      99792              8.51              0.00 
 lpb        dept_emp            80182              3.51              5.54 
 mysql      help_topic            652              1.51              0.09 
 world      city                 4046              0.39              0.10 
+-----------+----------------+-----------+------------------+------------------+

查询指定数据库容量大小

# SQL语句
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='mysql';

# 查询结果
+-----------+-----------+------------------+------------------+
 数据库     记录数     数据容量(MB)      索引容量(MB)     
+-----------+-----------+------------------+------------------+
 mysql           4267              2.04              0.28 
+-----------+-----------+------------------+------------------+

查看指定数据库各表容量大小

# SQL语句
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='lpb'
order by data_length desc, index_length desc;

# 查询结果
+-----------+----------------+-----------+------------------+------------------+
 数据库     表名            记录数     数据容量(MB)      索引容量(MB)     
+-----------+----------------+-----------+------------------+------------------+
 lpb        qiqios           49893861           1752.00              0.00 
 lpb        test_flush_log      99792              8.51              0.00 
 lpb        dept_emp            80182              3.51              5.54 
 lpb        test                    3              0.01              0.00 
+-----------+----------------+-----------+------------------+------------------+