数据库（表）查看表大小和索引所占内存空间

一. 表大小

1. 查询所有数据表的大小，并排序

use information_schema;
# 正序
select table_schema as '库名', table_name as '表名', round(data_length/1024/1024) as '表大小（M）' from tables order by 表大小（M）;

# 逆序
select table_schema as '库名', table_name as '表名', round(data_length/1024/1024) as '表大小（M）' from tables order by 表大小（M） DESC;

# 取排名前10
select table_schema as '库名', table_name as '表名', round(data_length/1024/1024) as '表大小（M）' from tables order by 表大小（M） DESC limit 1,10;

# 取排名前10，保留两位小数(四舍五入)
select table_schema as '库名', table_name as '表名', round(data_length/1024/1024,2) as '表大小（M）' from tables order by 表大小（M） DESC limit 1,10;

2. 查询所有数据库的大小

use information_schema;

# 查询所有数据库的数据量大小 
select table_schema as '库名', table_name as '表名', round(sum(data_length/1024/1024)) as '表大小（M）' from tables group by table_schema;

# 查询所有数据库容量大小，并排序
select table_schema as '库名', round(sum(data_length/1024/1024)) as '库大小（M）' from tables group by table_schema;

# 查询所有数据库容量大小，显示数据量最多的前十个库
select table_schema as '库名', round(sum(data_length/1024/1024)) as '库大小（M）' from tables group by table_schema desc limit 10;

3. 查询数据库总容量

use information_schema;
select table_schema as '库名', round(sum(data_length/1024/1024)) as '库大小（M）' from tables;

二. 索引占用内存空间

select  table_name, round(index_length/1024/1024,2) as '索引内存(M)' from tables where table_schema = <库名>;