MySql数据库、数据表 - 数据量统计

统计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 = 'your_database_name'
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
-- where table_schema = 'your_database_name'
order by table_schema asc, data_length desc, index_length desc;

-- 查询单表每个索引大小
select
sum(stat_value) pages,
table_name,
index_name,
concat(round(sum(stat_value)/1000000 * @@innodb_page_size, 2), 'M') size
from mysql.innodb_index_stats
where
table_name = 'user_info_index'
and database_name = 'db_my_test'
and stat_description LIKE 'Number of pages in the index'
group by table_name, index_name;

查询结果

数据库容量查询结果

数据库各表容量查询结果

单表每个索引大小

补充：
可以借助Navicat工具查询单个数据库中各表的数据量情况