查询指定数据库每张表、索引占用磁盘空间大小及汇总
1年前
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;