MySQL OPTIMIZE TABLE 深度解析：原理、適用場景與操作指南

為什麼需要 OPTIMIZE TABLE？

大量 DELETE、UPDATE 可變長度列（VARCHAR/BLOB/TEXT）後，資料檔案會產生碎片，檔案大小不減反增，讀寫效能下降。Discuz! 等系統內建「資料表優化」功能即呼叫此命令。

語法

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

• LOCAL / NO_WRITE_TO_BINLOG：不寫入二進位日誌，不複寫到從庫。

運作原理

1. 建立原表的臨時副本。
2. 將資料重新整理寫入副本（重建索引、壓縮碎片）。
3. 原子替換原表。

全程鎖表，大表操作時間較長，建議低峰期執行。

引擎別行為差異

適用場景

• 刪除大量資料後。
• 頻繁更新可變長度列（VARCHAR/BLOB/TEXT）的表。
• 定期維護：每週/每月一次，僅針對碎片嚴重表。
• 不需 對唯讀、固定長度、少量變動的表頻繁執行。

碎片檢測：判斷是否需要優化

SELECT
    TABLE_NAME,
    DATA_LENGTH,
    INDEX_LENGTH,
    DATA_FREE,
    ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) AS frag_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db'
  AND DATA_FREE > 0
ORDER BY frag_pct DESC;

frag_pct > 20% 建議排程優化。

線上優化替代方案（減少鎖表影響）

• pt-online-schema-change (Percona Toolkit)：觸發器+臨時表，近零停機重建。
• gh-ost (GitHub)：無觸發器，基於 binlog 同步，更安全。
• MySQL 8.0+ ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE 部分操作支援線上。

複寫注意事項

預設寫入 binlog 並複寫到從庫。大表優化會導致從庫同步延遲。跨機房複寫建議加 NO_WRITE_TO_BINLOG，改由人工在從庫各自執行。

總結檢查清單

1. 確認表引擎支援（MyISAM/InnoDB）。
2. 檢測碎片率 > 20%。
3. 選擇低峰期，預估鎖表時間（大表可用線上工具）。
4. 決定是否複寫（NO_WRITE_TO_BINLOG）。
5. 執行 OPTIMIZE TABLE 或線上替代方案。
6. 驗證 DATA_FREE 歸零、檔案縮小、查詢效能提升。

1. 上一篇文章： MySQL 自定義函數與存儲過程完整開發指南
2. 下一篇文章： MySQL 顯示表資訊完整指令大全：SHOW 系列與 mysqlshow

MySQL 資料庫相關知識

MySQL 客戶端啟動參數十大必知選項實戰指南

前言 MySQL 客戶端 mysql 擁有豐富的啟動選項，善

MySQL 主從數據不一致？三種恢復策略與跳過錯誤事務實戰指南

現象：從庫復制中斷，報主鍵衝突 SHOW SLAVE ST

Linux MySQL 簡易教程（一）：安裝、啟動、建庫建表、切換庫

安裝與啟動 sudo apt-get install -y

Windows 環境下編譯 MySQL 原始碼完整指南：工具鏈、資源獲取與建置流程

為何要自行編譯？ 官方手冊多描述開發版特性，欲同步驗證需自行