MySQL系列(27)：查询数据库中各个表的容量及大小

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

查看表容量

MySQL 的 information_schema 库中的 tables 存储了数据表的元数据信息,其中几个字段的含义：

• table_schema: 记录数据库名；
• table_name: 记录数据表名；
• table_rows: 关于表的粗略行估计；
• data_length : 记录表的大小（单位字节）；
• index_length : 记录表的索引的大小；

示例：

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