您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 資料庫表大小統計與碎片分析：information_schema 實戰查詢大全

編輯：MySQL 綜合教學

MySQL 資料庫表大小統計與碎片分析：information_schema 實戰查詢大全

精準掌握儲存佔用是容量規劃、分庫分表、異常排查的基石。本文彙總 5 類高頻統計查詢，均基於 information_schema.TABLES 與 SHOW TABLE STATUS，涵蓋實例級總覽、庫級詳情、單庫深度、單表狀態深度解讀、全庫表資產清單，所有查詢經生產環境驗證，可直接納入監控體系與巡檢 SOP。

一、實例級總覽：所有庫大小彙總（MB）

SELECT table_schema,
       ROUND(SUM(data_length)/1024/1024, 2) AS data_mb,
       ROUND(SUM(index_length)/1024/1024, 2) AS index_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;

二、庫級詳情：含表數量與統計基準日

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_database_name';

指標解讀：data_free 為已分配未使用空間（碎片），InnoDB 共享表空間下反映整體碎片量；max_data_length 為引擎理論上限。

四、單表狀態深度解讀：SHOW TABLE STATUS \G

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	時間戳	變更追蹤、備份窗口對齊

五、單庫全表資產清單：一條 SQL 導出

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_database_name'
ORDER BY total_mb DESC;

進階應用場景**：

導出 CSV → Excel 樞紐分析 Top 20 大表、增長趨勢
篩選 data_free/total_mb > 0.2 → 碎片整理排程候選表
巡檢 Auto_increment / (SELECT MAX(id) FROM table) > 0.8 → 主鍵耗盡預警
結合 information_schema.COLUMNS 統計大字段（TEXT/BLOB/JSON）佔比

六、物理 vs 邏輯大小：認清差異避免誤判

邏輯統計（上述查詢）：優化器視角，用於趨勢分析、容量規劃、碎片率計算
物理檔案（du -sh /var/lib/mysql/db/*.ibd）：OS 視角，含 Undo Log、Doublewrite Buffer、自適應哈希索引、碎片空洞，用於磁碟告警、備份空間預估
同步手段：定期 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 重建表空間，回收碎片、壓縮頁面，使邏輯/物理趨近

建議將核心查詢封裝為 db_size_exporter.py 接入 Prometheus，構建「庫/表大小、日增長率、碎片率、自增消耗率」四維儀表板，實現從被動擴容到主動規劃的運維升級。