您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 大表線上修改表結構（Online DDL）風險與解決方案：pt-osc、gh-ost 實戰

編輯：MySQL 綜合教學

前言

互聯網業務迭代快，頻繁加欄位、加索引。MySQL 原生 ALTER TABLE 在大表上會觸發「拷貝表→重建索引→鎖表切換」流程，導致寫入阻塞、主從延遲、甚至服務不可用。本文深度剖析 MySQL DDL 執行流程、鎖行為、業界成熟解決方案（pt-online-schema-change、gh-ost）原理與實戰操作，助你實現真正的零停機變更。

MySQL 原生 DDL 執行流程（COPY/INPLACE 算法）

加鎖：獲取排他鎖（MDL 鎖），表變為唯讀，寫入阻塞。
建立臨時表：複製原表結構（.frm/.ibd）。
應用變更：在臨時表上執行 DDL（加欄位、建索引等）。
資料拷貝：INSERT INTO tmp SELECT * FROM orig，此階段最耗時，大表可達小時級。
鎖表切換：再次加排他鎖，應用增量變更（Binlog 回放），RENAME TABLE orig TO old, tmp TO orig。
釋放鎖、清理：Drop 舊表，刷新字典。

核心痛點：步驟 1、5 需排他鎖；步驟 4 耗時長，期間寫入全阻塞。表越大、索引越多，影響越劇烈。

業界三大解決方案對比

方案	原理	優點	缺點	適用場景
停機維護/滾動升級	停寫入→執行 DDL→恢復	簡單、無工具依賴	停機時間長、多實例滾動需數天、版本發布受阻	極小表、可接受停機窗口的傳統系統
pt-online-schema-change (Percona)	觸發器捕獲增量→影子表同步→原子切換	成熟穩定、支援過濾、限流、暫停、檢查從庫延遲	需建立觸發器（超級權限）、觸發器有微小效能開銷、不支援外鍵表	大多數線上大表變更，首選方案
gh-ost (GitHub)	解析 Binlog 捕獲增量→無觸發器→影子表同步→切換	零觸發器、不影響主庫寫入效能、可在從庫運行、支援切換前驗證	需啟用 Row 格式 Binlog、需 SUPER/REPLICATION CLIENT、學習曲線稍陡	極高寫入並發、不想加觸發器、跨機房遷移

pt-online-schema-change 實戰操作

1. 安裝 Percona Toolkit

# CentOS 7
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install -y percona-toolkit

2. 基本語法

pt-online-schema-change \
  --alter "ADD COLUMN phone VARCHAR(20) AFTER email" \
  --alter-foreign-keys-method=auto \
  --max-lag=10 \
  --chunk-time=0.5 \
  --chunk-size=1000 \
  --critical-load Threads_running=200 \
  --set-vars innodb_lock_wait_timeout=2 \
  --execute \
  D=db_name,t=table_name

3. 關鍵參數解析

--alter：DDL 陳述式（不含 ALTER TABLE）。
--max-lag：檢查從庫延遲秒數，超過則暫停，防止主從過度分離。
--chunk-time / --chunk-size：控制每批次複製行數與耗時，實現限流。
--critical-load：監控主庫負載（如 Threads_running），過高暫停。
--dry-run / --print：試跑/列印 SQL 不執行，上線前必跑。
--execute：真正執行。

4. 完整流程示意

# 1. 試跑
pt-online-schema-change --alter "ADD COLUMN phone VARCHAR(20)" D=db,t=user --dry-run --print

# 2. 正式執行（建議在 screen/tmux 中）
pt-online-schema-change --alter "ADD COLUMN phone VARCHAR(20)" \
  --max-lag=5 --chunk-time=0.5 --critical-load Threads_running=150 \
  --execute D=db,t=user

gh-ost 實戰操作

1. 安裝（Go 二進制，無依賴）

wget https://github.com/github/gh-ost/releases/download/v1.1.17/gh-ost-binary-linux-x86_64-20230101.tar.gz
tar -xzf gh-ost-*.tar.gz
mv gh-ost /usr/local/bin/

2. 基本語法

gh-ost \
  --user=root --password=xxx --host=127.0.0.1 \
  --database=db_name --table=table_name \
  --alter="ADD COLUMN phone VARCHAR(20)" \
  --max-lag-millis=5000 \
  --chunk-size=2000 \
  --concurrent-rowcount=true \
  --execute

3. 關鍵差異優勢

在**從庫**運行，讀取主庫 Binlog 同步，完全不觸碰主庫表、不建觸發器。
--concurrent-rowcount：並發統計行數，加速進度估算。
--test-on-replica：切換前在另一從庫驗證影子表正確性。
支援 --panic-flag-file：建立檔案即觸發緊急暫停/回滾。

生產環境檢查清單

✅ 測試環境完整演練（含回滾）
✅ 確認表無外鍵（pt-osc 不支援，gh-ost 支援有限）
✅ 確認主庫 Binlog 格式為 ROW（gh-ost 必需）
✅ 磁碟空間 ≥ 2× 表大小（影子表 + Binlog 增量）
✅ 低峰期執行，通知相關方
✅ 監控儀表板就緒：Threads_running、Replication Lag、Disk Usage、工具進度日誌
✅ 備份驗證可用（最近 24h 內）

總結

MySQL 大表線上變更，**pt-online-schema-change 是目前最通用、生態最成熟的選擇**；gh-ost 在極高併發、跨機房、不想動主庫觸發器場景更優。無論選哪款，核心原理都是「影子表 + 增量同步 + 原子切換」。掌握工具參數、建立標準化變更流程（申請→評估→試跑→執行→驗證→歸檔），才能從容應對業務高速迭代帶來的結構變更壓力。