所有数据库容量大小
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;
所有数据库各表容量大小
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;
指定数据库容量大小
例:查看cake库容量大小
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='cake';
指定数据库各表容量大小
例:查看cake库各表容量大小
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='cake'
order by data_length desc, index_length desc;
下面是执行案例
MySQL [cake]> 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='cake';
+------------+-----------+------------------+------------------+
| 数据库 | 记录数 | 数据容量(MB) | 索引容量(MB) |
+------------+-----------+------------------+------------------+
| cake | 11288411 | 62084.71 | 493.86 |
+------------+-----------+------------------+------------------+
1 row in set (0.44 sec)
MySQL [cake]> 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='cake'
-> order by data_length desc, index_length desc;
+------------+----------------------------------------+-----------+------------------+------------------+
| 数据库 | 表名 | 记录数 | 数据容量(MB) | 索引容量(MB) |
+------------+----------------------------------------+-----------+------------------+------------------+
| cake | abc-aaaaa-aaaaaaa | 24485 | 18287.54 | 7.90 |
| cake | users_history | 143010 | 9232.84 | 0.00 |
| cake | shop_product | 124804 | 8975.21 | 48.29 |
| cake | abc_abcdefgd | 61376 | 6618.92 | 33.71 |
| cake | cakefo_shop_product | 82390 | 4331.40 | 0.00 |
| cake | information | 104199 | 3398.68 | 32.21 |
| cake | stock_history | 9559497 | 3385.87 | 0.00 |