掌握儲存佔用情況是容量規劃、分庫分表、異常排查的基礎。本文匯總 5 類高頻統計查詢，均基於 information_schema.TABLES 與 SHOW TABLE STATUS，涵蓋實例級、庫級、表級、單表狀態與全庫表清單，所有查詢經生產環境驗證，可直接納入監控腳本與巡檢清單。
SELECT table_schema,
ROUND(SUM(data_length)/1024/1024, 2) AS data_length_mb,
ROUND(SUM(index_length)/1024/1024, 2) AS index_length_mb,
ROUND(SUM(data_length+index_length)/1024/1024, 2) AS total_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY total_mb DESC;
輸出欄位：庫名、數據大小、索引大小、總大小。注意：information_schema 自身統計可能不準，建議排除或單獨標註。
SELECT table_schema,
ROUND(SUM(data_length+index_length)/1024/1024, 2) AS total_mb,
ROUND(SUM(data_length)/1024/1024, 2) AS data_mb,
ROUND(SUM(index_length)/1024/1024, 2) AS index_mb,
COUNT(*) AS table_count,
CURDATE() AS stat_date
FROM information_schema.tables
GROUP BY table_schema
ORDER BY total_mb DESC;
SELECT CONCAT(ROUND(SUM(data_length)/1024/1024, 2), ' MB') AS data_size,
CONCAT(ROUND(SUM(max_data_length)/1024/1024, 2), ' MB') AS max_data_size,
CONCAT(ROUND(SUM(data_free)/1024/1024, 2), ' MB') AS data_free,
CONCAT(ROUND(SUM(index_length)/1024/1024, 2), ' MB') AS index_size
FROM information_schema.tables
WHERE table_schema = 'your_db_name';
關鍵指標解讀：
data_free：已分配但未使用的空間（碎片），InnoDB 共享表空間下反映整體碎片量max_data_length：理論最大容量，MyISAM 受限於指針大小，InnoDB 通常極大SHOW TABLE STATUS FROM db_name WHERE name = 'table_name' \G
核心輸出欄位含義：
| 欄位 | 含義 | 關注點 |
|---|---|---|
| Engine | 儲存引擎 | InnoDB/MyISAM/其他 |
| Row_format | 行格式 | Compact/Dynamic/Compressed/Redundant |
| Rows | 預估行數 | InnoDB 為採樣估算，非精確值 |
| Avg_row_length | 平均行長度 | Bytes，配合 Rows 推算數據大小 |
| Data_length | 數據大小 | Bytes，含索引頁面（聚簇索引） |
| Index_length | 索引大小 | Bytes，輔助索引大小 |
| Data_free | 碎片大小 | Bytes，> 100MB 考慮 OPTIMIZE TABLE |
| Auto_increment | 自增下一值 | 防止耗盡，接近上限需擴容類型 |
| Create/Update/Check_time | 時間戳 | 追蹤變更、備份窗口對齊 |
SELECT table_name,
ROUND(data_length/1024/1024, 4) AS data_mb,
ROUND(index_length/1024/1024, 4) AS index_mb,
ROUND((data_length+index_length)/1024/1024, 4) AS total_mb,
table_rows
FROM information_schema.tables
WHERE table_schema = 'your_db_name'
ORDER BY total_mb DESC;
應用場景：
data_free/total_mb > 0.2 篩選碎片率高的表排程整理Auto_increment / MAX(id) > 0.8 預警主鍵耗盡風險InnoDB 共享表空間（ibdata1）或獨立表空間（.ibd）下，OS 層面 du -sh 看到的檔案大小可能大於邏輯統計（含未釋放碎片、Undo Log、Doublewrite Buffer 等）。建議：
OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 回收碎片，同步兩者將上述查詢封裝為 db_size_check.sh 接入 Prometheus Exporter，實現「庫/表大小、增長率、碎片率」全維度可視化監控，讓容量管理從被動滅火轉為主動規劃。
