MySQL 定期維護三板斧：ANALYZE、CHECK、OPTIMIZE 表操作全解析與生產環境最佳實踐

MySQL 表在長期增刪改後會產生統計信息過期、碎片堆積、甚至頁損壞。本文系統講解 ANALYZE TABLE、CHECK TABLE、OPTIMIZE TABLE 三大維護命令的原理、適用引擎、輸出解讀、加鎖行為，並給出生產環境排程建議與避坑指南。

一、ANALYZE TABLE — 更新統計信息，優化執行計劃

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

• 作用：掃描表/索引頁面，更新「鍵值分布」統計信息，供優化器生成最優 JOIN 順序、索引選擇。
• 適用引擎：MyISAM、InnoDB、BDB。
• 加鎖：讀鎖（阻塞寫入，允許並發讀）。
• 輸出解讀：Msg_type=status, Msg_text='Table is already up to date' 表示統計信息已最新，無需重建。
• 觸發時機：大量 INSERT/DELETE/UPDATE 後（如 > 10% 行變動）、SHOW TABLE STATUS 顯示 Rows 與實際 COUNT(*) 差距大、執行計劃突然走全表掃描。

二、CHECK TABLE — 檢測錯誤、驗證視圖依賴

CHECK TABLE tbl_name [, tbl_name] ... [QUICK | FAST | MEDIUM | EXTENDED | CHANGED]

• 選項：
  • QUICK：僅檢查索引樹結構（最快）。
  • FAST：僅檢查未正確關閉的表。
  • MEDIUM（默認）：掃描行校驗校驗和，檢查索引鍵順序。
  • EXTENDED：全行掃描，極慢但最徹底。
  • CHANGED：僅檢查上次檢查後被修改的表。
• 適用引擎：MyISAM、InnoDB；也可檢查視圖（依賴表是否存在）。
• 加鎖：讀鎖。
• 輸出：Msg_type=status, Msg_text='OK' 正常；Error/Corrupt 需修復。
• 視圖驗證示例：DROP TABLE base_table; CHECK TABLE view_name; 會報 Table 'base_table' doesn't exist。

三、OPTIMIZE TABLE — 整理碎片、回收空間

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

• 觸發條件：大量刪除/更新可變長列（VARCHAR/BLOB/TEXT）導致「空洞碎片」；Data_free 佔比 > 20% 或文件體積明顯大於邏輯數據量。
• MyISAM 行為：修復表 → 排序索引頁 → 更新統計信息（相當於 myisamchk -r）。
• InnoDB 行為：5.6 前不支援（提示 Table does not support optimize, doing recreate + analyze instead），實質執行 ALTER TABLE ... ENGINE=InnoDB 重建表 + ANALYZE。
• 加鎖：排他鎖（阻塞所有讀寫），耗時與表大小成正比。
• 頻率建議：核心大表每月 1 次，非核心表每季度 1 次，嚴禁業務高峰期執行。

四、生產環境排程模板

# /etc/cron.d/mysql-maintain
# 每天 03:00 分析核心表（統計信息輕量）
0 3 * * * mysql -uroot -p'xxx' -e "ANALYZE TABLE db.core_orders, db.core_users;" >> /var/log/mysql/analyze.log 2>&1

# 每周日 04:00 檢查所有表（輕量 QUICK）
0 4 * * 0 mysql -uroot -p'xxx' -e "CHECK TABLE db.core_orders, db.core_users FAST;" >> /var/log/mysql/check.log 2>&1

# 每月 1 號 05:00 優化碎片重表（排他鎖，預留維護窗口）
0 5 1 * * mysql -uroot -p'xxx' -e "OPTIMIZE TABLE db.core_orders;" >> /var/log/mysql/optimize.log 2>&1

五、輔助工具：mysqlcheck 一鍵批量

# 分析所有庫所有表
mysqlcheck -A -a -u root -p
# 檢查所有表（MEDIUM）
mysqlcheck -A -c -u root -p
# 優化所有表
mysqlcheck -A -o -u root -p
# 修復損壞表
mysqlcheck -A -r -u root -p

六、避坑清單

1. InnoDB 不支援原生 OPTIMIZE：實為重建表，需預留 1.5 倍磁盤空間，大表慎用，優先考慮 pt-online-schema-change 在線重建。
2. ANALYZE/CHECK/OPTIMIZE 皆加鎖：務必在低峰窗口執行，監控 Threads_running 避免業務堵塞。
3. 統計信息不準不一定要 ANALYZE：InnoDB 8.0 引入 innodb_stats_persistent 持久化統計，重啟不丟失，可適當降低分析頻率。
4. REPAIR TABLE 僅限 MyISAM：InnoDB 損壞需從備份恢復或 innodb_force_recovery 導出重建。

將三板斧納入常態化運維體系，配合監控告警（Data_free 告警、執行計劃變更告警），可將「表級故障」壓縮至最低概率。