MySQL如何查看数据库、表占用磁盘大小

一、查询指定数据库（例“test”）占用磁盘空间大小

SELECT
	TABLE_SCHEMA AS "数据库",
	sum( table_rows ) AS "记录数",
	concat( TRUNCATE ( sum( data_length ) / 1024 / 1024, 2 ), ' MB' ) / 1024 AS "数据容量（GB）",
	concat( TRUNCATE ( sum( index_length ) / 1024 / 1024, 2 ), 'MB' ) / 1024 AS "索引容量（GB）" 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'test';

结果：

二、查询指定数据库（例“test”）每张表、索引占用磁盘空间大小

SELECT
	table_schema AS "数据库",
	table_name AS "表名",
	table_rows AS "记录数",
	TRUNCATE ( data_length / 1024 / 1024, 2 ) / 1024 AS "数据容量（GB）",
	TRUNCATE ( index_length / 1024 / 1024, 2 ) / 1024 AS "索引容量（GB）" 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'test' 
ORDER BY
	data_length DESC,
	index_length DESC;

结果：

 三、查询指定数据库（例“test”）每张表、索引占用磁盘空间大小及汇总，一个sql搞定

SELECT COALESCE
	( bb.表名, '汇总' ) AS "表名",
	bb.记录数,
	bb.数据容量（ GB）,
	bb.索引容量（ GB） 
FROM
	(
SELECT
	aa.表名,
	sum( AA.记录数 ) AS "记录数",
	sum( AA.数据容量（ GB） ) AS "数据容量（GB）",
	sum( AA.索引容量（ GB） ) AS "索引容量（GB）" 
FROM
	(
SELECT
	table_schema "数据库",
	table_name AS "表名",
	table_rows AS "记录数",
	TRUNCATE ( data_length / 1024 / 1024, 2 ) / 1024 AS "数据容量（GB）",
	TRUNCATE ( index_length / 1024 / 1024, 2 ) / 1024 AS "索引容量（GB）" 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'test' 
ORDER BY
	data_length DESC,
	index_length DESC 
	) AA 
GROUP BY
	aa.表名 WITH ROLLUP 
	) bb 
ORDER BY
	bb.数据容量（ GB） DESC,
	bb.索引容量（ GB） DESC;

结果：