MySQL 修改表結構 (DDL) 在 5.6 前**全程鎖表**，5.6+ 雖引入 Online DDL 但**並非所有操作無鎖**，且仍存在元數據鎖 (MDL) 爭用導致業務掛起風險。Percona Toolkit pt-online-schema-change (pt-osc) 是業界標準無鎖變更工具，但其內部仍有**三次短暫鎖表**窗口，若不當使用極易引發生產事故。本文深度拆解 pt-osc 原理、鎖表步驟、實戰參數調優、決策流程圖。
| 版本 | 操作類型 | 是否鎖表 | 說明 |
|---|---|---|---|
| ≤ 5.5 | 所有 DDL | **全程排他鎖** | 建臨時表、拷貝數據、切換、刪舊，全程阻塞讀寫 |
| 5.6 / 5.7 | Instant DDL (重命名索引、修改默認值等) | **無鎖** | 僅修改元數據，極快 |
| Inplace DDL (加索引、擴展列長度等) | **構建階段無鎖，準備/提交階段需 MDL 排他鎖** | 短暫阻塞 DDL/DML，大表構建階段佔用 IO/CPU | |
| Copy DDL (修改列類型、主鍵、分區等) | **全程鎖表** | 同 5.5，必須重建表 | |
| 8.0 | 更多 Instant/Inplace | 持續改進 | 如瞬間加列 (有默認值)、列重排序等 |
Step 1: 建立新表 (同結構 + 目標變更)
Step 2: 修改新表 (執行 ALTER TABLE) -- 僅新表，無業務影響
Step 3: **創建觸發器 (INSERT/UPDATE/DELETE) 捕獲原表變更** <-- 【鎖表 1: MDL 共享鎖】
Step 4: 分塊拷貝數據 (INSERT IGNORE ... SELECT) -- 邊拷貝邊應用觸發器增量
Step 5: **重命名表 (RENAME TABLE old TO old_osc, new TO old)** <-- 【鎖表 2: MDL 排他鎖】
Step 6: 更新外鍵 (若為父表) <-- 【鎖表 3: 逐個子表 MDL 排他鎖】
Step 7: 刪除舊表、觸發器LOCK TABLES ... WRITE 等價 MDL 共享鎖，**阻塞所有 DDL、阻塞寫入等待觸發器創建完成** (通常毫秒級，但高併發下可積累隊列)。RENAME TABLE 需 **MDL 排他鎖**，**阻塞所有讀寫 (含 SELECT)**。若當前有長事務/大查詢持有表的 MDL 共享鎖，RENAME 將等待，**所有後續訪問該表的查詢排隊等待 RENAME 釋放**，形成「閃電式全庫掛起」。ALTER TABLE ... DROP FOREIGN KEY, ADD FOREIGN KEY，**每個子表都需 MDL 排他鎖**，子表多/大時風險放大。pt-online-schema-change \
--alter "ADD INDEX idx_name (name)" \
--tries "swap_tables:10:0.5,drop_triggers:10:0.5" \
--set-vars "wait_timeout=10000,innodb_lock_wait_timeout=1,lock_wait_timeout=60" \
--max-load "Threads_running=100" \
--critical-load "Threads_running=200" \
--chunk-time 0.5 \
--progress time,10 \
D=db,t=tbl| 參數 | 作用 | 推薦值/策略 |
|---|---|---|
--tries | 關鍵步驟重試次數:間隔秒 | swap_tables:10:0.5 重試 10 次、每次 0.5s，極大提高搶鎖成功率 |
--set-vars | 會話級變量 | innodb_lock_wait_timeout=1 快速失敗避免長等待；lock_wait_timeout=60 MDL 等待上限 |
--max-load | 暫停拷貝閾值 | Threads_running=100 超過暫停，避免加劇主庫壓力 |
--critical-load | 中止任務閾值 | Threads_running=200 超過直接退出，保護業務 |
--chunk-time | 目標塊拷貝耗時 | 0.5 秒，動態調整塊大小，平滑 IO |
--no-swap-tables | 不重命名 (手動切換) | 極高併發核心表，**人工低峰切換**，規避 RENAME 風險 |
--no-drop-old-table | 保留舊表 | 回滾保障，事後手動驗證刪除 |
--dry-run | 演練模式 | **生產前必跑**，檢查腳本、權限、觸發器衝突 |
場景：高併發核心表執行 pt-osc 加索引。
過程：Step 5 RENAME 等待長事務釋放 MDL → 所有新查詢排隊等 RENAME →Threads_running飆升 →max_connections打滿 → 應用報錯「Too many connections」。
根因：未設置--max-load、--critical-load保護；未識別長事務風險；未預留人工干預窗口。
修正：事前SHOW PROCESSLIST殺長事務；設置保護閾值；使用--no-swap-tables人工低峰切換。
**DDL 無鎖是相對的，風險可控才是絕對的。** 理解 pt-osc 三次鎖表本質、配置 --tries 重試、--max-load 熔斷、--no-swap-tables 手動切換，結合 gh-ost 無觸發器架構，才能在生產環境安全完成大表結構變更。決策流程圖 (原文) 請打印貼在工位：有觸發器→原生/遷移；Instant→原生；Inplace→評估；Copy→pt-osc/gh-ost；核心巨表→gh-ost+人工。
