MySQL 查看表大小的几种方法

来自linux中国网wiki
docker>Evan2019年9月9日 (一) 09:03的版本 →‎查看指定数据库各表容量大小
(差异) ←上一版本 | 最后版本 (差异) | 下一版本→ (差异)
跳到导航 跳到搜索

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

例:查看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查看数据库表容量大小