MySQL-统计数据库表容量

1.查看整个实例占用空间大小：

SELECT
	concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
	concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB 
FROM
	information_schema.`TABLES`;

2.查看各个库占用大小：

SELECT
	TABLE_SCHEMA,
	concat( TRUNCATE ( sum( data_length )/ 1024 / 1024, 2 ), ' MB' ) AS data_size,
	concat( TRUNCATE ( sum( index_length )/ 1024 / 1024, 2 ), 'MB' ) AS index_size 
FROM
	information_schema.`TABLES`
GROUP BY
	TABLE_SCHEMA;

3.查看单个库占用空间大小：

SELECT
	concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
	concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB 
FROM
	information_schema.`TABLES`
WHERE
	table_schema = 'schema_name';

4.查看单个表占用空间大小：

SELECT
	concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
	concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB 
FROM
	information_schema.`TABLES`
WHERE
	table_schema = 'schema_name' 
	AND table_name = 'table_name';

5.查找某一个库中表的碎片

SELECT t.TABLE_SCHEMA,
       t.TABLE_NAME,
       t.TABLE_ROWS,
	   concat(round(t.DATA_LENGTH / 1024 / 1024, 2), 'M') AS size,
       t.INDEX_LENGTH,
       concat(round(t.DATA_FREE / 1024 / 1024, 2), 'M') AS datafree
FROM information_schema.tables t
WHERE t.TABLE_SCHEMA = 'haha' order by DATA_LENGTH desc;