查询统计mysql占用磁盘空间大小

1.统计某个库的各个表的数据和索引的占用空间大小

select
TABLE_NAME,
concat(truncate(data_length/1024/1024,2),’ MB’) as data_size,
concat(truncate(index_length/1024/1024,2),’ MB’) as index_size
from information_schema.tables
where TABLE_SCHEMA = ‘tab’
order by data_length desc;

2.统计某个库的某个表的占用空间大小

SELECT
concat( round( sum( data_length / 1024 / 1024 / 1024 ), 2 ), ‘G’ ) AS DATA
FROM
information_schema.TABLES
WHERE
table_schema = ‘tab’
AND table_name = ‘tab2’;