MySQL 執行 DDL（ALTER TABLE）歷來是生產環境事故高發區：表鎖導致業務阻塞、主從延遲飆升、甚至觸發主從切換。MySQL 5.6 引入 Online DDL，5.7/8.0 持續擴展 Instant DDL 範圍，但「不鎖表」並非無條件。本文系統分析 DDL 鎖表機制、版本演進、Percona Toolkit (pt-osc) 與 GitHub gh-ost 兩大無鎖變更工具原理、關鍵參數調優、觸發器/外鍵限制、實戰避坑清單，助你實現零停機變更。

一、DDL 鎖表機制演進史

版本	算法	鎖模式	適用操作	備註
< 5.6	COPY	排他鎖 (全程)	所有 DDL	建臨時表→拷貝數據→重命名→刪舊表，全程阻塞讀寫
5.6	INPLACE	元數據鎖 (MDL) 短時	添加/刪除二級索引、修改列默認值、擴展 VARCHAR 長度等	不重建表數據，但需排他 MDL 鎖定義變更瞬間
5.6	COPY	排他鎖	修改列類型、調整主鍵、更改字符集、添加全文/空間索引等	仍需重建表，全程阻塞寫入
5.7	INSTANT	幾乎無鎖 (僅 MDL 幾毫秒)	添加列(末尾、含默認值)、添加/刪除虛擬列、設置/刪除默認值、擴展 VARCHAR、修改 ENUM/SET 列表(末尾)	僅修改元數據，不讀寫數據頁，秒級完成
8.0	INSTANT	幾乎無鎖	5.7 全部 + 刪除列、重排列、更改列類型(兼容)、添加/刪除主鍵(特定條件)	元數據版本機制，真正的秒級 DDL

關鍵認知：「Online DDL ≠ 無鎖」。INPLACE 算法仍需在準備/提交階段獲取排他 MDL 鎖，若長事務持有表鎖，DDL 會等待並阻塞後續查詢（Metadata Lock 爭用）。

二、pt-online-schema-change (pt-osc) 原理與關鍵參數

核心流程（7 步）

1. 創建新表：複製結構，應用 DDL 變更。
2. 清空新表：TRUNCATE（極快）。
3. 創建觸發器：在原表上建立 INSERT/UPDATE/DELETE 觸發器，同步增量變更到新表 （獲取排他 MDL 鎖，極短）。
4. 拷貝歷史數據：分塊 (chunk) 讀取原表數據 INSERT INTO 新表（可暫停/限流，不鎖表）。
5. 同步追趕：循環檢查觸發器同步延遲，直到追平 （需排他 MDL 鎖重命名表，關鍵風險點）。
6. 重命名表：RENAME TABLE old TO old_ghost, new TO old （排他 MDL 鎖，阻塞所有訪問）。
7. 清理：刪除舊表、觸發器、臨時表。

關鍵風險點：步驟 3/5/6 的 MDL 鎖爭用

高併發場景下，長事務（如報表查詢、大事務）持有 MDL 共享鎖，導致 pt-osc 無法獲取排他鎖，進而阻塞所有新請求（MDL 隊列 FIFO），造成「假死」。

Percona Toolkit 2.2+ 關鍵參數調優（必配）

pt-online-schema-change \
  --alter "ADD COLUMN col_c INT" \
  --execute \
  --max-load "Threads_running=100" \
  --critical-load "Threads_running=200" \
  --chunk-time 0.5 \
  --chunk-size 1000 \
  --tries "swap_tables:5:0.5,drop_triggers:5:0.5" \
  --set-vars "wait_timeout=10000,innodb_lock_wait_timeout=1,lock_wait_timeout=60" \
  D=db,t=tbl

參數	作用	推薦值/策略
--tries swap_tables:N:interval	重命名表重試次數與間隔	5:0.5（最多重試 5 次，每次間隔 0.5s）
--tries drop_triggers:N:interval	刪除觸發器重試	5:0.5
--set-vars lock_wait_timeout=60	會話級鎖等待超時	60s，避免無限等待阻塞應用
--set-vars innodb_lock_wait_timeout=1	InnoDB 行鎖等待超時	1s，快速失敗觸發重試
--max-load / --critical-load	負載保護：超過閾值暫停拷貝	根據實例 Threads_running 基線設定
--pause-file	外部暫停文件，實現人工/自動化熔斷	配合監控系統：延遲飆升時 touch 暫停文件

三、gh-ost：無觸發器、無鎖重命名的新一代方案

核心創新

• 無觸發器：通過解析 Binlog (Row 格式) 異步回放增量，零觸發器開銷，不佔用原表鎖資源。
• 無鎖重命名：利用 RENAME TABLE 原子性，但在無長事務阻塞前提下瞬間完成；若檢測到長事務，自動延遲切換，不阻塞新請求（異於 pt-osc 的強制等待）。
• 可中斷恢復：斷點續傳，狀態持久化到元數據表。
• HTTP API / Web UI：實時監控進度、動態調整流控、一鍵切換/取消。

適用條件

• MySQL 5.6+、Binlog Format = ROW、啟用 GTID（推薦）。
• 表有主鍵/唯一非空索引（用於分塊定位）。
• 無外鍵約束（或外鍵僅引用該表，不被其他表引用）。

典型命令

gh-ost \
  --host=127.0.0.1 --port=3306 --user=root --password=xxx \
  --database=db --table=tbl \
  --alter="MODIFY COLUMN col_b BIGINT NOT NULL" \
  --max-load=Threads_running=100 \
  --critical-load=Threads_running=200 \
  --chunk-size=1000 \
  --cut-over=default \
  --default-retries=60 \
  --execute

四、觸發器與外鍵：pt-osc/gh-ost 的硬性限制

業務邏輯下沉應用層

遷移前臨時禁用觸發器（需停寫窗口）

使用原生 Online DDL (INPLACE/INSTANT)

維護窗口手動 DROP FK → 變更 → ADD FK

或遷移至應用層完整性檢查

同上

限制類型	pt-osc	gh-ost	應對策略
表上有觸發器	不支持（會衝突）	不支持
被其他表引用 (FK 父表)	支持 --alter-foreign-keys-method=rebuild_constraints (重建子表 FK)	不支持（需手動處理）
引用其他表 (FK 子表)	支持自動更新 FK 定義	不支持

五、決策流程圖：如何選擇變更策略

1. 評估操作類型：是否支持 Instant/Inplace（查閱版本矩陣）。
2. 評估表大小/業務峰值：< 1GB 且低峰期 → 原生 DDL 可接受；> 1GB 或核心業務 → 無鎖工具。
3. 檢查約束：有觸發器/外鍵 → 原生 DDL 或維護窗口手動處理。
4. 選擇工具：Binlog ROW + GTID + 無 FK → gh-ost（首選）；否則 pt-osc（配置 --tries）。
5. 制定回滾預案：備份驗證、切換腳本演練、數據一致性校驗 (pt-table-checksum)。

六、實戰案例：大表添加索引不停機

場景：核心訂單表 500GB，需添加複合索引 idx_buyer_status (buyer_id, status)。

1. 確認：MySQL 5.7，操作支持 Inplace（添加二級索引）。
2. 風險：Inplace 仍需構建索引（掃描全表、排序、寫入），耗時數小時，期間產生大量重做日志、可能觸發 Purge 線程阻塞、主從延遲。
3. 決策：使用 gh-ost 異步構建，限流控制主從延遲 < 5s。
4. 執行：gh-ost --alter="ADD INDEX idx_buyer_status (buyer_id, status)" --max-load=Threads_running=50 --critical-load=Threads_running=100 --execute。
5. 監控：Web UI 觀察 copy phase 進度、replication lag、binlog sync。
6. 切換：業務低峰期人工確認 cut-over，耗時 < 1s。
7. 驗證：pt-table-checksum 驗證主從一致性。

結果：零業務感知，主從延遲峰值 3s，總耗時 4.5h。

七、避坑清單：生產環境 DDL 十大鐵律

1. ❌ 無測試環境驗證直上生產。
2. ❌ 未評估表大小、行長、索引大小、磁盤空間（需 2-3 倍表空間）。
3. ❌ 未檢查長事務（SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60）。
4. ❌ 未配置 --max-load/--critical-load 熔斷機制。
5. ❌ 未設置 --tries swap_tables 重試參數（pt-osc）。
6. ❌ 未驗證備份可恢復性（PITR 演練）。
7. ❌ 未通知業務方、未制定回滾 SOP。
8. ❌ 變更期間無人值守監控儀表盤。
9. ❌ 變更後未執行 ANALYZE TABLE 更新統計信息。
10. ❌ 未記錄變更詳情納入變更管理系統 (CMS/CMDB)。

結語

MySQL DDL 變更已從「深夜停機維護」進化為「白天無感變更」。掌握版本特性邊界、精通 pt-osc/gh-ost 核心參數、建立標準化變更流程與熔斷機制，是高級 DBA 的核心競爭力。記住：沒有無風險的變更，只有可控風險的工程。將每一次 DDL 視為手術，術前評估、術中監控、術後復查，方能守護數據庫穩健航行。