MySQL 線上修改表結構（Online DDL）避坑指南：從鎖表風險到 pt-osc 實戰

在生產環境對大表執行 DDL（如加欄位、加索引、修改型別）是高風險操作：MySQL 5.6 前全程鎖表，5.6+ 雖支援 Online DDL 但仍有鎖表場景，甚至 pt-online-schema-change（pt-osc）等工具也無法完全避免短暫鎖表。本文深入剖析鎖表成因、工具原理、實戰參數調優與決策流程，助您安全完成結構變更。

一、為什麼修改表結構會導致停機？

• MySQL ≤ 5.5：所有 DDL 需重建表，全程持有排他鎖，讀寫全阻塞。
• MySQL 5.6/5.7：大部分 DDL 支援 ALGORITHM=INPLACE，但修改資料型別、刪除主鍵、轉換字符集等仍需 ALGORITHM=COPY（全表重建＋鎖表）。
• pt-osc / gh-ost：採用「建影子表→同步資料→切換」機制，但建立觸發器、重命名表、更新外鍵等步驟仍需元數據鎖（MDL），若長事務未提交會阻塞切換，導致連線堆積、服務不可用。

二、pt-osc 關鍵步驟與鎖表風險點

Step 1: 建立新表（無鎖）
Step 2: 修改新表結構（無鎖）
Step 3: 建立觸發器捕獲舊表變更 ← 需元數據鎖
Step 4: 分批複製資料（無鎖，可調節速率）
Step 5: 重命名表（新舊互換） ← 需元數據鎖，最危險
Step 6: 更新外鍵（若為子表） ← 循環操作，避免隱性重建
Step 7: 刪除舊表

實戰血淚：高併發系統執行 Step 5 時，若有長事務持有 MDL，重命名操作排隊，所有後續查詢阻塞於 MDL 後，瞬間達到 max_connections，服務癱瘓。

三、pt-osc 關鍵參數調優（Percona Toolkit 2.2+）

# 降低會話鎖等待超時，快速失敗重試
--set-vars="wait_timeout=10000,innodb_lock_wait_timeout=1,lock_wait_timeout=60"

# 針對關鍵步驟設定重試次數與間隔
--tries="swap_tables:5:0.5,drop_triggers:5:0.5,create_triggers:5:0.5,update_foreign_keys:3:1"

# 其他實用參數
--max-load="Threads_running=100"          # 超過負載暫停
--critical-load="Threads_running=200"     # 達到臨界值中止
--chunk-time=0.5                           # 每塊複製目標時間
--pause-file=/tmp/pt-osc-pause             # 外部暫停控制
--execute                                  # 實際執行（預設只列印）

四、決策流程：如何選擇變更策略？

1. 表 < 500 萬行 / 低峰期可接受鎖表：直接 ALTER TABLE 最簡單。
2. 大表、高併發、不可停機：首選 pt-osc 或 gh-ost（無觸發器，負載更低）。
3. 表有觸發器：pt-osc 不支援，需評估 ALGORITHM=INPLACE 是否適用，或採用雙寫/遷移方案。
4. 極大表（> 500GB）或極高寫入：考慮影子表雙寫+同步遷移，或利用主從切換在從庫執行 DDL 再提升為主。

五、生產環境檢查清單

• ✅ 備份已驗證可恢復
• ✅ 確認無長事務（SHOW ENGINE INNODB STATUS / SELECT * FROM information_schema.innodb_trx WHERE trx_started < NOW() - INTERVAL 1 MINUTE）
• ✅ 磁碟空間 ≥ 2× 表大小（影子表+二進制日誌）
• ✅ 已設定 --max-load、--critical-load 保護
• ✅ 準備回滾腳本（RENAME TABLE 切回）
• ✅ 變更窗口在業務低峰，並通知相關方

沒有銀彈，只有對業務負載、資料量、工具特性的深度理解，才能在變更中游刃有餘。建議先在測試環境全流程演練，量化鎖表時間、複製速率、主從延遲，再上生產。