千萬級的大表如何新增字段？
前言
線上千萬級的大表在新增字段的時候，一定要小心，我見過太多團隊在千萬級大表上執(zhí)行DDL時翻車的案例。
很容易影響到正常用戶的使用。
這篇文章跟大家一起聊聊線上千萬級的大表新增字段的6種方案，希望對你會有所幫助。
1.為什么大表加字段如此危險？
核心問題：MySQL的DDL操作會鎖表。
當(dāng)執(zhí)行ALTER TABLE ADD COLUMN時：
- MySQL 5.6之前：全程鎖表（阻塞所有讀寫）
- MySQL 5.6+：僅支持部分操作的Online DDL
通過實驗驗證鎖表現(xiàn)象：
-- 會話1：執(zhí)行DDL操作
ALTER TABLE user ADD COLUMN age INT;
-- 會話2：嘗試查詢（被阻塞）
SELECT * FROM user WHERE id=1; -- 等待DDL完成鎖表時間計算公式：
鎖表時間 ≈ 表數(shù)據(jù)量 / 磁盤IO速度對于1000萬行、單行1KB的表，機械磁盤（100MB/s）需要100秒的不可用時間！
如果在一個高并發(fā)的系統(tǒng)中，這個問題簡直無法忍受。
那么，我們要如何解決問題呢？
圖片
2.原生Online DDL方案
在MySQL 5.6+版本中可以使用原生Online DDL的語法。
例如：
ALTER TABLE user
ADD COLUMN age INT,
ALGORITHM=INPLACE,
LOCK=NONE;實現(xiàn)原理：
圖片
致命缺陷：
- 仍可能觸發(fā)表鎖（如添加全文索引）
- 磁盤空間需雙倍（實測500GB表需要1TB空閑空間）
- 主從延遲風(fēng)險（從庫單線程回放）
3.停機維護方案
圖片
適用場景：
- 允許停服時間（如凌晨3點）
- 數(shù)據(jù)量小于100GB（減少導(dǎo)入時間）
- 有完整回滾預(yù)案
4.使用PT-OSC工具方案
Percona Toolkit的pt-online-schema-change這個是我比較推薦的工具。
工作原理：
圖片
操作步驟：
# 安裝工具
sudo yum install percona-toolkit
# 執(zhí)行遷移（添加age字段）
pt-online-schema-change \
--alter "ADD COLUMN age INT" \
D=test,t=user \
--execute5.邏輯遷移 + 雙寫方案
還有一個金融級安全的方案是：邏輯遷移 + 雙寫方案。
適用場景：
- 字段變更伴隨業(yè)務(wù)邏輯修改（如字段類型變更）
- 要求零數(shù)據(jù)丟失的金融場景
- 超10億行數(shù)據(jù)的表
實施步驟：
- 創(chuàng)建新表結(jié)構(gòu)
-- 創(chuàng)建包含新字段的副本表
CREATE TABLE user_new (
id BIGINT PRIMARY KEY,
name VARCHAR(50),
-- 新增字段
age INT DEFAULT 0,
-- 增加原表索引
KEY idx_name(name)
) ENGINE=InnoDB;- 雙寫邏輯實現(xiàn)（Java示例）
// 數(shù)據(jù)寫入服務(wù)
publicclass UserService {
@Transactional
public void addUser(User user) {
// 寫入原表
userOldDAO.insert(user);
// 寫入新表（包含age字段）
userNewDAO.insert(convertToNew(user));
}
private UserNew convertToNew(User old) {
UserNew userNew = new UserNew();
userNew.setId(old.getId());
userNew.setName(old.getName());
// 新字段處理（從其他系統(tǒng)獲取或默認(rèn)值）
userNew.setAge(getAgeFromCache(old.getId()));
return userNew;
}
}- 數(shù)據(jù)遷移（分批處理）
-- 分批遷移腳本
SET @start_id = 0;
WHILE EXISTS(SELECT1FROMuserWHEREid > @start_id) DO
INSERTINTO user_new (id, name, age)
SELECTid, name,
COALESCE(age_cache, 0) -- 從緩存獲取默認(rèn)值
FROMuser
WHEREid > @start_id
ORDERBYid
LIMIT10000;
SET @start_id = (SELECTMAX(id) FROM user_new);
COMMIT;
-- 暫停100ms避免IO過載
SELECTSLEEP(0.1);
ENDWHILE;- 灰度切換流程
圖片
這套方案適合10億上的表新增字段，不過操作起來比較麻煩，改動有點大。
6.使用gh-ost方案
gh-ost（GitHub's Online Schema Transmogrifier）是GitHub開源的一種無觸發(fā)器的MySQL在線表結(jié)構(gòu)變更方案。
專為解決大表DDL（如新增字段、索引變更、表引擎轉(zhuǎn)換）時鎖表阻塞、主庫負(fù)載高等問題而設(shè)計。
其核心是通過異步解析binlog，替代觸發(fā)器同步增量數(shù)據(jù)，顯著降低對線上業(yè)務(wù)的影響。
與傳統(tǒng)方案對比
- 觸發(fā)器方案（如pt-osc）：在源表上創(chuàng)建INSERT/UPDATE/DELETE觸發(fā)器，在同一事務(wù)內(nèi)將變更同步到影子表。痛點：
觸發(fā)器加重主庫CPU和鎖競爭，高并發(fā)時性能下降30%以上
無法暫停，失敗需重頭開始
外鍵約束支持復(fù)雜
- gh-ost方案：
- 偽裝為從庫：直連主庫或從庫，拉取ROW格式的binlog，解析DML事件（INSERT/UPDATE/DELETE）
- 異步應(yīng)用：將增量數(shù)據(jù)通過獨立連接應(yīng)用到影子表（如
REPLACE INTO處理INSERT事件），與主庫事務(wù)解耦 - 優(yōu)先級控制：binlog應(yīng)用優(yōu)先級 > 全量數(shù)據(jù)拷貝，確保數(shù)據(jù)強一致
關(guān)鍵流程：
圖片
- 全量拷貝：按主鍵分塊（
chunk-size控制）執(zhí)行INSERT IGNORE INTO _table_gho SELECT ...，避免重復(fù)插入 - 增量同步：
INSERT → REPLACE INTO
UPDATE → 全行覆蓋更新
DELETE → DELETE
- 原子切換（Cut-over）：
1)短暫鎖源表（毫秒級）
2)執(zhí)行原子RENAME：RENAME TABLE source TO _source_del, _source_gho TO source
3)清理舊表（_source_del）
典型命令示例：
gh-ost \
--alter="ADD COLUMN age INT NOT NULL DEFAULT 0 COMMENT '用戶年齡'" \
--host=主庫IP --port=3306 --user=gh_user --password=xxx \
--database=test --table=user \
--chunk-size=2000 \ # 增大批次減少事務(wù)數(shù)
--max-load=Threads_running=80 \
--critical-load=Threads_running=200 \
--cut-over-lock-timeout-secnotallow=5 \ # 超時重試
--execute \ # 實際執(zhí)行
--allow-on-master # 直連主庫模式監(jiān)控與優(yōu)化建議
- 進(jìn)度跟蹤：
echo status | nc -U /tmp/gh-ost.sock # 查看實時進(jìn)度- 延遲控制：
設(shè)置--max-lag-millis=1500，超閾值自動暫停
從庫延遲過高時切換為直連主庫模式
- 切換安全：使用
--postpone-cut-over-flag-file人工控制切換時機
7.分區(qū)表滑動窗口方案
適用場景：
- 按時間分區(qū)的日志型大表
- 需要頻繁變更結(jié)構(gòu)的監(jiān)控表
核心原理： 通過分區(qū)表特性，僅修改最新分區(qū)結(jié)構(gòu)。
操作步驟：
修改分區(qū)定義：
-- 原分區(qū)表定義
CREATETABLElogs (
idBIGINT,
log_time DATETIME,
contentTEXT
) PARTITIONBYRANGE (TO_DAYS(log_time)) (
PARTITION p202301 VALUESLESSTHAN (TO_DAYS('2023-02-01')),
PARTITION p202302 VALUESLESSTHAN (TO_DAYS('2023-03-01'))
);
-- 添加新字段（僅影響新分區(qū)）
ALTERTABLElogsADDCOLUMN log_level VARCHAR(10) DEFAULT'INFO';創(chuàng)建新分區(qū)（自動應(yīng)用新結(jié)構(gòu)）：
-- 創(chuàng)建包含新字段的分區(qū)
ALTER TABLE logs REORGANIZE PARTITION p202302 INTO (
PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01'))
);歷史數(shù)據(jù)處理：
-- 僅對最近分區(qū)做數(shù)據(jù)初始化
UPDATE logs PARTITION (p202302)
SET log_level = parse_log_level(content);8.千萬級表操作注意事項
- 主鍵必須存在（無主鍵將全表掃描）
- 磁盤空間監(jiān)控（至少預(yù)留1.5倍表空間）
- 復(fù)制延遲控制
SHOW SLAVE STATUS;
-- 確保Seconds_Behind_Master < 10- 灰度驗證步驟：
先在從庫執(zhí)行
檢查數(shù)據(jù)一致性
低峰期切主庫
- 字段屬性選擇：
避免NOT NULL（導(dǎo)致全表更新）
優(yōu)先使用ENUM代替VARCHAR
默認(rèn)值用NULL而非空字符串
9.各方案對比
以下是針對千萬級MySQL表新增字段的6種方案的對比。
方案 | 鎖表時間 | 業(yè)務(wù)影響 | 數(shù)據(jù)一致性 | 適用場景 | 復(fù)雜度 |
原生Online DDL | 秒級~分鐘級 | 中（并發(fā)DML受限） | 強一致 | <1億的小表變更 | 低 |
停機維護 | 小時級 | 高（服務(wù)中斷） | 強一致 | 允許停服+數(shù)據(jù)量<100GB | 中 |
PT-OSC | 毫秒級（僅cut-over） | 中（觸發(fā)器開銷） | 最終一致 | 無外鍵/觸發(fā)器的常規(guī)表 | 中 |
邏輯遷移+雙寫 | 0 | 低（需改代碼） | 強一致 | 金融級核心表（10億+） | 高 |
gh-ost | 毫秒級（僅cut-over） | 低（無觸發(fā)器） | 最終一致 | 高并發(fā)大表（TB級） | 中高 |
分區(qū)滑動窗口 | 僅影響新分區(qū) | 低 | 分區(qū)級一致 | 按時間分區(qū)的日志表 | 中 |
總結(jié)
- 常規(guī)場景（<1億行）：
首選 Online DDL（ALGORITHM=INSTANT，MySQL 8.0秒級加字段）
備選 PT-OSC（兼容低版本MySQL）
- 高并發(fā)大表（>1億行）：
- 必選 gh-ost（無觸發(fā)器設(shè)計，對寫入影響<5%）
- 金融核心表：
雙寫方案 是唯一選擇（需2-4周開發(fā)周期）
- 日志型表：
分區(qū)滑動窗口 最優(yōu)（僅影響新分區(qū)）
- 緊急故障處理：
- 超百億級表異常時，考慮 停機維護 + 回滾預(yù)案
給大家一些建議：
- 加字段前優(yōu)先使用 JSON字段預(yù)擴展（
ALTER TABLE user ADD COLUMN metadata JSON）- 萬億級表建議 分庫分表 而非直接DDL
- 所有方案執(zhí)行前必須 全量備份（
mysqldump + binlog）- 流量監(jiān)測（Prometheus+Granfa實時監(jiān)控QPS）
在千萬級系統(tǒng)的戰(zhàn)場上，一次草率的ALTER操作可能就是壓垮駱駝的最后一根稻草。
