作為一名資深後端開發，你有沒有遇到過這樣的場景：產品經理跑過來説："我們這個用户表要加個新字段，用來記錄用户的最後登錄時間，今天就要上線！"

你一看錶結構，好傢伙，用户表已經5000萬數據了，直接執行ALTER TABLE語句？那豈不是要鎖表半小時，整個系統都得癱瘓？

今天就來聊聊如何優雅地給千萬級大表新增字段，讓你的系統在不宕機的情況下完成表結構變更！

一、為什麼大表新增字段這麼難？

在開始講解決方案之前，我們先來理解一下為什麼給大表新增字段會這麼困難：

1.1 傳統ALTER TABLE的痛點

在MySQL 5.5及更早版本中，執行ALTER TABLE ADD COLUMN操作時會發生什麼？

  1. 鎖表：整個表會被鎖定，無法進行任何讀寫操作
  2. 重建表：MySQL會創建一個新表，將原表數據逐行復制到新表
  3. 時間長：數據量越大，複製時間越長，鎖表時間也越長

對於5000萬數據的表，這個過程可能需要幾十分鐘甚至幾小時，期間系統完全不可用！

1.2 現代MySQL的改進

MySQL 5.6引入了Online DDL功能，MySQL 8.0更是增加了INSTANT算法，大大改善了這個問題。但即使如此，在超大表上執行ALTER TABLE仍然存在風險。

二、主流解決方案對比

面對大表新增字段的需求，業界主要有以下幾種解決方案：

2.1 MySQL原生Online DDL

MySQL 5.6+版本支持Online DDL，可以通過以下方式執行：

ALTER TABLE user_table 
ADD COLUMN last_login_time DATETIME DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

優點：

  • 無需額外工具
  • 支持併發DML操作

缺點：

  • 仍需要雙倍磁盤空間
  • 某些操作不支持在線執行
  • 可能導致主從延遲

2.2 pt-online-schema-change工具

這是Percona公司開發的開源工具，專門用於在線表結構變更。

工作原理：

  1. 創建與原表結構相同的新表
  2. 在新表上執行ALTER操作
  3. 創建觸發器同步數據變更
  4. 逐步將原表數據複製到新表
  5. 原子性切換表名

2.3 gh-ost工具

GitHub開源的在線表結構變更工具，與pt-osc類似但實現方式不同。

三、pt-online-schema-change實戰詳解

接下來我們重點介紹pt-online-schema-change的使用方法，這是目前業界最主流的解決方案。

3.1 安裝Percona Toolkit

# CentOS/RHEL
yum install percona-toolkit

# Ubuntu/Debian
apt-get install percona-toolkit

# 或者直接下載安裝
wget https://www.percona.com/downloads/percona-toolkit/LATEST/binary/redhat/7/x86_64/percona-toolkit-3.4.0-x86_64-redhat-linux-gnu.tar.gz
tar -xzf percona-toolkit-3.4.0-x86_64-redhat-linux-gnu.tar.gz

3.2 基本使用方法

給用户表添加last_login_time字段的完整命令：

pt-online-schema-change \
--host=localhost \
--user=root \
--password=your_password \
--port=3306 \
--charset=utf8mb4 \
D=your_database,t=user_table \
--alter="ADD COLUMN last_login_time DATETIME DEFAULT NULL COMMENT '最後登錄時間'" \
--execute

3.3 關鍵參數詳解

# 安全參數
--no-version-check    # 跳過版本檢查
--dry-run            # 模擬執行，不真正修改表結構
--print              # 打印將要執行的SQL語句

# 性能參數
--chunk-size=1000    # 每次處理的行數，默認1000
--max-load="Threads_running=25"  # 最大負載限制
--critical-load="Threads_running=50"  # 危險負載閾值
--sleep=1            # 每次操作後休眠時間（秒）

# 複製參數
--check-slave-lag=h=slave_host  # 檢查從庫延遲
--max-lag=1          # 最大延遲時間（秒）

3.4 完整示例

pt-online-schema-change \
--host=127.0.0.1 \
--user=root \
--password=your_password \
--port=3306 \
--charset=utf8mb4 \
--no-version-check \
--chunk-size=2000 \
--max-load="Threads_running=25" \
--critical-load="Threads_running=50" \
--sleep=0.5 \
--check-slave-lag=h=192.168.1.100,P=3306 \
--max-lag=1 \
--recursion-method=processlist \
D=your_database,t=user_table \
--alter="ADD COLUMN last_login_time DATETIME DEFAULT NULL COMMENT '最後登錄時間', ADD INDEX idx_last_login_time (last_login_time)" \
--execute

四、執行過程詳解

pt-online-schema-change的執行過程可以分為以下幾個階段：

4.1 初始化階段

  1. 檢查表是否存在主鍵或唯一索引（必須有）
  2. 創建新表（表名格式：_原表名_new）
  3. 在新表上執行ALTER操作

4.2 數據複製階段

  1. 創建三個觸發器（INSERT、UPDATE、DELETE）
  2. 分批將原表數據複製到新表
  3. 通過觸發器同步複製期間的數據變更

4.3 切換階段

  1. 原子性重命名錶（RENAME操作）
  2. 刪除舊錶和觸發器
  3. 完成表結構變更

五、安全注意事項

使用pt-online-schema-change時需要注意以下安全事項：

5.1 前置檢查

  1. 必須有主鍵或唯一索引：否則工具會拒絕執行
  2. 磁盤空間：確保有足夠的磁盤空間（至少2倍表大小）
  3. 從庫延遲：監控從庫延遲，避免影響複製

5.2 執行監控

# 查看執行進度
ps aux | grep pt-online-schema-change

# 監控MySQL狀態
show processlist;

# 查看錶大小
SELECT 
  table_name,
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size in MB'
FROM information_schema.tables 
WHERE table_schema = 'your_database' 
AND table_name = 'user_table';

5.3 異常處理

如果執行過程中出現問題，工具會自動回滾：

  1. 刪除新表
  2. 刪除觸發器
  3. 保持原表不變

六、性能優化建議

6.1 選擇合適的執行時間

  • 避開業務高峯期
  • 選擇數據庫負載較低的時段
  • 考慮從庫的同步壓力

6.2 調整參數優化性能

# 根據服務器性能調整chunk-size
--chunk-size=5000    # 數據量大時可以適當增大

# 調整併發度
--concurrent=10      # 併發線程數

# 優化休眠時間
--sleep=0.1          # 減少休眠時間提高效率

6.3 監控關鍵指標

  1. CPU使用率：避免CPU過載
  2. IO等待：監控磁盤IO性能
  3. 內存使用：確保有足夠的內存
  4. 從庫延遲：監控複製延遲

七、總結

給千萬級大表新增字段看似是一個簡單的DDL操作，實際上卻藴含着很多技術細節和風險。通過合理選擇工具和方法，我們可以實現：

  1. 零停機時間：業務無感知的表結構變更
  2. 數據一致性：確保變更過程中數據不丟失
  3. 安全可靠：完善的異常處理和回滾機制
  4. 性能可控：可調節的執行參數和監控指標

掌握了這些技巧，相信你再面對大表結構變更時會更加從容不迫，讓你的系統穩如老狗！

今日思考：你們團隊在處理大表結構變更時都採用什麼方案？有沒有遇到過什麼坑？歡迎在評論區分享你的經驗！