如何查找出MYSQL数据库数据、索引的占用空间？

一、查询指定数据库（test_new）的占用空间大小

使用时记得修改 test_new 数据库名称

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_new';

示例：

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

使用时记得修改 test_new 数据库名称

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_new' 
ORDER BY
    data_length DESC,
    index_length DESC;

示例：

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

使用时记得修改 mysql_test_first 数据库名称

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 = 'mysql_test_first' 
ORDER BY
    data_length DESC,
    index_length DESC 
    ) AA 
GROUP BY
    aa.表名 WITH ROLLUP 
    ) bb 
ORDER BY
    bb.数据容量（GB） DESC,
    bb.索引容量（GB） DESC;

示例：

四、发现问题

为什么每次查询的记录数不变，或者记录数隔段时间有突然增长一次？

可以思考一下，下一篇文章进行总结。

借鉴博客：https://blog.csdn.net/xutong_123/article/details/127842221