MySQL 維護語句實戰：ANALYZE、OPTIMIZE、CHECK、REPAIR 與 CHECKSUM 完整指南

前言

MySQL 長期運行後，索引統計資訊過期、資料碎片累積、頁面損壞等問題會嚴重影響效能與資料完整性。MySQL 提供 ANALYZE、OPTIMIZE、CHECK、REPAIR、CHECKSUM 五大維護語句，本文系統解析其原理、適用場景、執行策略與自動化建議，助您建立標準化資料庫維護體系。

一、ANALYZE TABLE：更新索引統計資訊

核心作用

重新掃描表頁面，更新 Cardinality (基數)——即索引欄位的不重複值數量。優化器依賴此統計資訊判斷索引選擇性，Cardinality 嚴重偏離實際值會導致優化器誤判，放棄索引改走全表掃描。

何時執行

• 大量 INSERT/UPDATE/DELETE 後 (建議變更超過 10% 時)。
• SHOW INDEX 顯示 Cardinality 與實際不重複值差距大。
• 查詢計劃突然從 ref 變為 ALL。
• 資料庫升級後。

語法與範例

-- 基本用法
ANALYZE TABLE players;

-- 不寫入 Binlog (避免主從同步延遲、備份膨脹)
ANALYZE LOCAL TABLE players;

-- 批次分析多表
ANALYZE TABLE t1, t2, t3;

驗證效果

SHOW INDEX FROM players;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| players| 0          | PRIMARY  | 1            | playerno    | A         | 1000        | NULL     | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
-- Cardinality 從 14 更新為 1000，索引恢復有效

二、OPTIMIZE TABLE：消除碎片、回收空間

核心作用

針對 MyISAM、InnoDB、ARCHIVE 引擎：

• 重建表結構，消除刪除/更新產生的資料碎片。
• 回收未使用的磁碟空間 (MyISAM 釋放 .MYD 空洞，InnoDB 縮減 .ibd 檔案)。
• 重新排序索引頁面，提升範圍掃描效能。
• 隱含執行 ANALYZE TABLE 更新統計資訊。

適用場景

• 頻繁 DELETE 或 UPDATE 變長欄位 (VARCHAR/TEXT/BLOB) 的表。
• SHOW TABLE STATUS 顯示 Data_free 偏大 (超過資料大小 20%)。
• 定期維護窗口 (如每週凌晨)。

語法與注意事項

OPTIMIZE TABLE orders;
OPTIMIZE LOCAL TABLE orders;  -- 不寫 Binlog

-- InnoDB 實際執行邏輯等同於：
ALTER TABLE orders ENGINE=InnoDB;  -- 重建表
ANALYZE TABLE orders;              -- 更新統計

鎖表警告：OPTIMIZE 期間會持有排他鎖，阻塞所有讀寫。大表 (超過 10GB) 執行時間可能達數小時，生產環境必須在低峰期或使用 pt-online-schema-change 類工具線上重建。

三、CHECK TABLE：檢測損壞與不一致

檢測範圍

• 索引鍵值順序正確性。
• 資料頁與索引頁連結完整性。
• 資料列校驗和 (若啟用 CHECKSUM=1 建表選項)。
• .frm 結構定義與儲存引擎元數據一致性。

模式選項 (由快到慢、由淺入深)

語法與結果解讀

CHECK TABLE players MEDIUM;

-- 結果欄位說明
+-----------------+-----+----------+----------+
| Table           | Op  | Msg_type | Msg_text |
+-----------------+-----+----------+----------+
| tennis.players  | check| status   | OK       |  -- 正常
| tennis.players  | check| error    | Corrupt  |  -- 損壞，需 REPAIR
| tennis.players  | check| warning  | ...      |  -- 警告，如統計資訊過期
+-----------------+-----+----------+----------+

查詢上次檢查時間

SELECT TABLE_NAME, CHECK_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'tennis' AND TABLE_NAME = 'players';

四、REPAIR TABLE：修復損壞表

關鍵限制：僅支援 MyISAM、ARCHIVE、CSV 引擎。InnoDB 損壞需透過 innodb_force_recovery 或從備份恢復。

修復模式

語法

REPAIR TABLE players QUICK;
REPAIR LOCAL TABLE players EXTENDED;  -- 不寫 Binlog

五、CHECKSUM TABLE：資料一致性驗證

原理

計算表資料的 CRC32 校驗和。MyISAM 若建表指定 CHECKSUM=1，會維護即時校驗和 (Live Checksum)，寫入時自動更新。

語法

CHECKSUM TABLE players;          -- 預設 EXTENDED，重新計算
CHECKSUM TABLE players QUICK;    -- 直接讀取儲存的 Live Checksum (極快)

應用場景

• 主從一致性校驗：對比 Master/Slave 同表 Checksum。
• 備份驗證：備份前後 Checksum 對比。
• 跨環境遷移驗證：生產/測試/開發環境資料一致性確認。

六、自動化維護策略建議

排程範例

# crontab -e (mysql 用戶)
# 每日 02:30 快速檢查核心表
30 2 * * * mysql -e "CHECK TABLE core_db.orders, core_db.users FAST;" >> /var/log/mysql_check.log 2>&1

# 每週日 03:00 中等深度檢查 + 碎片評估
0 3 * * 0 /usr/local/bin/mysql_maintenance.sh --medium --analyze >> /var/log/mysql_maint.log 2>&1

# 每月 01 號 04:00 優化高碎片表 (Data_free > 20%)
0 4 1 * * /usr/local/bin/mysql_optimize.sh --threshold=20 >> /var/log/mysql_optimize.log 2>&1

維護腳本核心邏輯 (mysql_optimize.sh 片段)

#!/bin/bash
THRESHOLD=20
mysql -NBe "SELECT TABLE_SCHEMA, TABLE_NAME, DATA_FREE, DATA_LENGTH+INDEX_LENGTH AS total_size 
FROM INFORMATION_SCHEMA.TABLES 
WHERE ENGINE='InnoDB' AND DATA_FREE > 0 AND TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
HAVING (DATA_FREE / total_size) * 100 > $THRESHOLD" |
while read db table free total; do
    echo "[$(date)] OPTIMIZE $db.$table (fragment: $((free*100/total))%)"
    mysql -e "OPTIMIZE LOCAL TABLE \`$db\`.\`$table\`;"
done

七、總結對照表

八、避坑指南

1. 大表 OPTIMIZE 必須線上工具：使用 pt-online-schema-change、gh-ost 或 MySQL 8.0 INSTANT DDL 避免長時間鎖表。
2. Binlog 膨脹：維護操作預設寫 Binlog，大表會產生巨大 Binlog，務必加 LOCAL 或設定 sql_log_bin=0 (需 SUPER 權限)。
3. InnoDB 不支援 REPAIR：遇到 InnoDB 損壞，請啟用 innodb_force_recovery=1..6 導出資料重建，或從備份恢復。
4. 統計資訊採樣偏差：InnoDB 預設採樣 8 頁，大表可能不準。可調大 innodb_stats_persistent_sample_pages (預設 20) 或 innodb_stats_transient_sample_pages (預設 8)。