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

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

SELECTTABLE_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）" FROMinfORMation_schema.TABLES WHEREtable_schema = 'test';

结果：

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

SELECTtable_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）" FROMinformation_schema.TABLES WHEREtable_schema = 'test' ORDER BYdata_length DESC,index_length DESC;

结果：

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

SELECT COALESCE( bb.表名, '汇总' ) AS "表名",bb.记录数,bb.数据容量（ GB）,bb.索引容量（ GB） FROM(SELECTaa.表名,sum( AA.记录数 ) AS "记录数",sum( AA.数据容量（ GB） ) AS "数据容量（GB）",sum( AA.索引容量（ GB） ) AS "索引容量（GB）" FROM(SELECTtable_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）" FROMinformation_schema.TABLES WHEREtable_schema = 'test' ORDER BYdata_length DESC,index_length DESC ) AA GROUP BYaa.表名 WITH ROLLUP ) bb ORDER BYbb.数据容量（ GB） DESC,bb.索引容量（ GB） DESC;

结果： 

来源地址：https://blog.csdn.net/xutong_123/article/details/127842221