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;

结果：