查看指定数据库各表容量大小
例：查看mysql库各表容量大小 以mysql 库作例子
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='mysql'
order by data_length desc, index_length desc;
查看所有数据库各表容量大小
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) |
+--------------------+----------------------------------------------+-----------+------------------+------------------+
| l_cailu_market | t_market_trend_30day | 93109 | 9.54 | 8.54 |
| l_cailu_market | t_market_trend_7day | 91539 | 9.54 | 7.54 |
| l_cailu_market | t_market_trend_24hour | 51658 | 5.51 | 4.51 |
| mysql | help_topic | 513 | 0.46 | 0.01 |
| mysql | help_keyword | 538 | 0.10 | 0.01 |
| l_cailu_market | t_market_exchange_kline_1day | 0 | 0.01 | 0.01 |
| l_cailu_market | t_market_exchange_kline_15min | 0 | 0.01 | 0.01 |
| l_cailu_market | t_market_trend_1year | 0 | 0.01 | 0.01 |
see also
http://blog.linuxchina.net/?p=751
MySQL查看数据库表容量大小