StarRocks（MySQL）中查询所有表占用的总存储空间大小

在StarRocks中查询所有表占用的总存储空间大小，可以通过查询information_schema.TABLES系统表来实现。以下是几种常用的查询方式：

1. 查询所有表的总存储空间（GB）

SELECT
　　ROUND(SUM(DATA_LENGTH) / 1024 / 1024 / 1024, 2) AS '总数据大小(GB)',
　　ROUND(SUM(COALESCE(INDEX_LENGTH,0)) / 1024 / 1024 / 1024, 2) AS '总索引大小(GB)',
　　ROUND(SUM(DATA_LENGTH + COALESCE(INDEX_LENGTH,0)) / 1024 / 1024 / 1024, 2) AS '总存储大小(GB)'
FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'sys', '_statistics_');

2. 按数据库分组统计存储空间

SELECT 
    TABLE_SCHEMA AS '模式名',
    ROUND(SUM(DATA_LENGTH) / 1024 / 1024 / 1024, 2) AS '总数据大小(GB)',
    ROUND(SUM(COALESCE(INDEX_LENGTH,0)) / 1024 / 1024 / 1024, 2) AS '总索引大小(GB)',
    COUNT(*) AS '表数量'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'sys', '_statistics_')
GROUP BY TABLE_SCHEMA 
ORDER BY SUM(DATA_LENGTH) DESC;

3. 查看每个表的详细存储信息（包含占比）

SELECT 
    t.TABLE_SCHEMA AS '模式名',
    t.TABLE_NAME AS '表名',
    ROUND(t.DATA_LENGTH / 1024 / 1024 / 1024, 2) AS '数据大小(GB)',
    ROUND(COALESCE(t.INDEX_LENGTH,0) / 1024 / 1024, 2) AS '索引大小(MB)',
    ROUND(SUM(t.DATA_LENGTH / 1024 / 1024 / 1024) OVER (), 2) AS '总数据大小(GB)',
    ROUND(t.DATA_LENGTH / 1024 / 1024 / SUM(t.DATA_LENGTH / 1024 / 1024) OVER (), 2) AS '单表占比(%)'
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA NOT IN ('information_schema', 'sys', '_statistics_')
ORDER BY t.DATA_LENGTH DESC;

注意事项

• 估算值问题：TABLE_ROWS字段是估算值，不精确，如需精确行数需执行`SELECT COUNT(*)1。
• 权限要求：执行上述查询需要至少SELECT权限访问information_schema数据库1。
• 分区表：如果表是分区表，建议使用SHOW PARTITIONS FROM database.table获取更精确的分区数据量1。
• 单位换算：DATA_LENGTH和INDEX_LENGTH的单位是字节，查询时根据需要转换为KB/MB/GB。

4. 查看每个表的详细存储信息（字节）

SELECT 
    TABLE_SCHEMA  AS '模式名', 
    TABLE_NAME   AS  '表名',
    TABLE_COMMENT  AS  '表注释',
    DATA_LENGTH   AS  '数据大小（字节）',
    COALESCE(INDEX_LENGTH,0)  AS  '索引大小(字节)',
    DATA_LENGTH + COALESCE(INDEX_LENGTH,0)   AS  '总存储大小(字节)' 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'sys', '_statistics_') 
ORDER BY  TABLE_SCHEMA, TOTAL_LENGTH DESC ;