【MySQL】快速统计数据表大小

/*库统计-所有数据总量*/

select SUM((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as `sum_data（MB）`
from information_schema.tables
where table_schema not in ('information_schema','performance_schema','mysql','sys'); 

/*库统计-按表*/
select * from (
select TABLE_SCHEMA as "SCHEMA", TABLE_NAME,sum(DATA_LENGTH/1024/1024) as `DATA（MB）`,sum(INDEX_LENGTH/1024/1024) as `INDEX（MB）`,sum((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as `SUM（MB）`,now() as `CURTIME`
from information_schema.tables
where TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys')
group by TABLE_NAME)  as t
order by `SUM（MB）` desc;

/*库统计-按库*/
select * from (
select TABLE_SCHEMA as "SCHEMA",sum(DATA_LENGTH/1024/1024) as `DATA（MB）`,sum(INDEX_LENGTH/1024/1024) as `INDEX（MB）`,sum((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as `SUM（MB）`,now() as `CURTIME`
from information_schema.tables
where TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys')
group by TABLE_SCHEMA)  as t
order by `SUM（MB）` desc;

/*表统计-指定库*/
select  table_schema as 'SCHEMA',table_name as 'TABLE_NAME',(DATA_LENGTH/1024/1024) as `DATA(MB)`,(INDEX_LENGTH/1024/1024) as `INDEX(MB)`,((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as `SUM(MB)`,table_rows as 'RECORDS',now()  as `CURTIME`
from  information_schema.tables
where table_schema = 'dbname'
order   by  records desc;