MySQL 一鍵查詢所有庫/單庫所有表磁碟佔用大小 SQL (information_schema 實戰)

核心查詢：所有庫總大小 (含數據+索引)，按數據量降序

SELECT 
    TABLE_SCHEMA AS `Database`,
    CONCAT(ROUND(SUM(DATA_LENGTH) / 1024 / 1024, 2), ' MB') AS `Data Size`,
    CONCAT(ROUND(SUM(INDEX_LENGTH) / 1024 / 1024, 2), ' MB') AS `Index Size`,
    CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2), ' MB') AS `Total Size`,
    COUNT(*) AS `Table Count`
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')  -- 排除系統庫
GROUP BY TABLE_SCHEMA
ORDER BY SUM(DATA_LENGTH) DESC;

核心查詢：指定庫下所有表大小，按數據量降序

SELECT 
    TABLE_NAME AS `Table`,
    ENGINE,
    TABLE_ROWS AS `Estimated Rows`,
    CONCAT(ROUND(DATA_LENGTH / 1024 / 1024, 2), ' MB') AS `Data Size`,
    CONCAT(ROUND(INDEX_LENGTH / 1024 / 1024, 2), ' MB') AS `Index Size`,
    CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2), ' MB') AS `Total Size`,
    ROUND(DATA_FREE / 1024 / 1024, 2) AS `Fragmentation (MB)`,
    CREATE_TIME,
    UPDATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'  -- 修改為目標库名
ORDER BY DATA_LENGTH DESC;

進階版：含表引擎、行數估算、碎片率、自增 ID 最大值

SELECT 
    t.TABLE_NAME,
    t.ENGINE,
    t.TABLE_ROWS,
    CONCAT(ROUND(t.DATA_LENGTH/1024/1024,2),' MB') AS Data_Size,
    CONCAT(ROUND(t.INDEX_LENGTH/1024/1024,2),' MB') AS Index_Size,
    CONCAT(ROUND((t.DATA_LENGTH+t.INDEX_LENGTH)/1024/1024,2),' MB') AS Total_Size,
    ROUND(t.DATA_FREE/1024/1024,2) AS Frag_MB,
    CASE WHEN t.DATA_LENGTH>0 THEN ROUND(t.DATA_FREE/t.DATA_LENGTH*100,2) ELSE 0 END AS Frag_Pct,
    t.AUTO_INCREMENT,
    t.TABLE_COLLATION
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA = 'your_database_name'
ORDER BY t.DATA_LENGTH DESC;

關鍵欄位解讀

實戰場景

1. 容量規劃：每日定時跑腳本記錄庫/表增長趨勢，預測磁碟擴容時間點。
2. 碎片治理：Frag_Pct > 20% 且 Frag_MB > 100 的表納入 OPTIMIZE TABLE / ALTER TABLE ... ENGINE=InnoDB 重建計劃 (低峰期、在線工具)。
3. 大表拆分/歸檔：Top 10 大表若超預期，評估分區/歸檔/冷熱分離。
4. 索引優化：Index_Size / Data_Size > 1.5 疑似過度索引，配合 pt-duplicate-key-checker 分析。
5. 異常監控：單表單日增長 > 10% 告警，防止失控寫入/備份腳本異常。

避坑提醒

• information_schema.TABLES 查詢會獲取字典鎖，極大量表 (萬張+) 時可能阻塞 DDL，**低峰期執行**。
• InnoDB TABLE_ROWS **不可信**，精確行數需 SELECT COUNT(*) (大表極慢) 或計數表/觸發器維護。
• 系統庫 (mysql, sys, performance_schema, information_schema) 通常排除，除非監控系統表膨脹。
• 8.0 information_schema 為視圖，查詢性能優於 5.7 (內存臨時表)，但仍需謹慎。