MySQL DELETE 後空間不釋放？OPTIMIZE TABLE 徹底解決碎片問題

現象：刪除資料後磁碟空間未縮減

執行 DELETE FROM table WHERE ... 後，資料筆數歸零，但 .ibd / .MYD 檔案大小不變。原因是 DELETE 僅標記刪除旗標，不整理資料檔案，留下的「洞」會被後續 INSERT 重複利用。

兩種 DELETE 行為差異

解決方案：OPTIMIZE TABLE

語法

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

最簡單用法：

OPTIMIZE TABLE phpernote_article;

適用場景

• 已刪除大量資料（特別是帶條件的 DELETE）。
• 含可變長度欄位（VARCHAR, BLOB, TEXT）的表經歷大量更新/刪除。
• 引擎為 MyISAM, InnoDB, BDB。

原理

• 重建表結構，消除碎片。
• 回收未使用空間給作業系統。
• 更新索引統計資訊。

注意事項

1. 執行期間會鎖表，建議在低峰期（如凌晨）執行。
2. 頻率：每週或每月一次即可，針對特定表執行，無需全庫。
3. InnoDB 實際上執行 ALTER TABLE ... ENGINE=InnoDB 重建。

相關優化建議

• 定期監控 Data_free 欄位（SHOW TABLE STATUS）評估碎片程度。
• 大量刪除考慮分批次 DELETE ... LIMIT 1000 避免長鎖表。
• 搭配 ANALYZE TABLE 更新統計資訊，優化查詢計畫。