统计mysql数据库占用磁盘空间大小和行数

1. 查看占用磁盘空间大小

select sum(t1.data_size ) as data_sum_size,  
sum(t1.index_size) as index_sum_size
from(
select
TABLE_NAME,
table_schema,
truncate(data_length/1024/1024,2) as data_size, -- 查看数据占用大小单位为MB
truncate(index_length/1024/1024,2) as index_size -- 查看索引占用大小 单位为MB
from information_schema.tables
where TABLE_SCHEMA = '数据库名'
order by data_length desc) t1;

如果想查看该数据库中每个表占用大小的话

select
TABLE_NAME,
table_schema,
truncate(data_length/1024/1024,2) as data_size, -- 查看数据占用大小单位为MB
truncate(index_length/1024/1024,2) as index_size -- 查看索引占用大小 单位为MB
from information_schema.tables
where TABLE_SCHEMA = '数据库名'
order by data_length desc

2. 查看数据库行数统计

select sum(t1.table_rows) as table_sum from (
select table_name,table_rows from information_schema.tables
where TABLE_SCHEMA = '数据库名'
order by table_rows desc) t1;

如果要查看数据库中每个表的行数的话

select table_name,table_rows from information_schema.tables
where TABLE_SCHEMA = '数据库名'
order by table_rows des