MySQL 表碎片整理：InnoDB 與 MyISAM 的優化實戰

MySQL 表碎片整理：InnoDB 與 MyISAM 的優化實戰

頻繁的 DELETE/UPDATE 會產生碎片，導致查詢變慢甚至磁盤空間浪費。本文記錄生產環境排查慢查詢、定位碎片、重建表的完整過程。

排查入口

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time%';

分析慢查詢日誌發現：SQL 走索引但耗時，集中在幾張大表 → 懷疑碎片。

MyISAM 碎片整理

OPTIMIZE TABLE table_name;

InnoDB 碎片檢測與整理

檢測碎片空間

-- MySQL 5.1.21+ 使用 data_free 欄位
SELECT table_schema, table_name, 
       ROUND(data_free/1024/1024, 2) AS data_free_MB
FROM information_schema.tables
WHERE engine = 'InnoDB' AND data_free > 100*1024*1024;

重建表釋放空間

ALTER TABLE table_name ENGINE=InnoDB;

等同於 OPTIMIZE TABLE，會獲得讀鎖（非排他鎖），耗時取決於數據量而非文件大小。高頻寫入表建議每月/每周執行。

實戰結果

檢測出兩張表 data_free > 100MB，執行 ALTER TABLE ... ENGINE=InnoDB 重建後，慢查詢消失，性能恢復正常。

補充判斷

若表的 Data_length + Index_length 遠小於磁盤實際佔用，也需整理碎片。