千萬級(jí)的大表如何新增字段？

前言

線上千萬級(jí)的大表在新增字段的時(shí)候，一定要小心，我見過太多團(tuán)隊(duì)在千萬級(jí)大表上執(zhí)行DDL時(shí)翻車的案例。

很容易影響到正常用戶的使用。

本文將深入剖析大表加字段的核心難點(diǎn)，并給出可落地的解決方案。

希望對(duì)你會(huì)有所幫助。

1.為什么大表加字段如此危險(xiǎn)？

核心問題：MySQL的DDL操作會(huì)鎖表。

當(dāng)執(zhí)行ALTER TABLE ADD COLUMN時(shí)：

MySQL 5.6之前：全程鎖表（阻塞所有讀寫）
MySQL 5.6+：僅支持部分操作的Online DDL

通過實(shí)驗(yàn)驗(yàn)證鎖表現(xiàn)象：

-- 會(huì)話1：執(zhí)行DDL操作
ALTER TABLE user ADD COLUMN age INT;

-- 會(huì)話2：嘗試查詢（被阻塞）
SELECT * FROM user WHERE id=1; -- 等待DDL完成

鎖表時(shí)間計(jì)算公式：

鎖表時(shí)間 ≈ 表數(shù)據(jù)量 / 磁盤IO速度

對(duì)于1000萬行、單行1KB的表，機(jī)械磁盤（100MB/s）需要100秒的不可用時(shí)間！

如果在一個(gè)高并發(fā)的系統(tǒng)中，這個(gè)問題簡直無法忍受。

那么，我們要如何解決問題呢？

2.原生Online DDL方案

在MySQL 5.6+版本中可以使用原生Online DDL的語法。

例如：

ALTER TABLE user 
ADD COLUMN age INT,
ALGORITHM=INPLACE, 
LOCK=NONE;

實(shí)現(xiàn)原理：

致命缺陷：

仍可能觸發(fā)表鎖（如添加全文索引）
磁盤空間需雙倍（實(shí)測500GB表需要1TB空閑空間）
主從延遲風(fēng)險(xiǎn)（從庫單線程回放）

3.停機(jī)維護(hù)方案

適用場景：

允許停服時(shí)間（如凌晨3點(diǎn)）
數(shù)據(jù)量小于100GB（減少導(dǎo)入時(shí)間）
有完整回滾預(yù)案

4.使用PT-OSC工具方案

Percona Toolkit的pt-online-schema-change這個(gè)是我比較推薦的工具。

工作原理：

操作步驟：

# 安裝工具
sudo yum install percona-toolkit

# 執(zhí)行遷移（添加age字段）
pt-online-schema-change \
--alter "ADD COLUMN age INT" \
D=test,t=user \
--execute

5.邏輯遷移 + 雙寫方案

還有一個(gè)金融級(jí)安全的方案是：邏輯遷移 + 雙寫方案。

適用場景：

字段變更伴隨業(yè)務(wù)邏輯修改（如字段類型變更）
要求零數(shù)據(jù)丟失的金融場景
超10億行數(shù)據(jù)的表

實(shí)施步驟：

1. 創(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;

2. 雙寫邏輯實(shí)現(xiàn)（Java示例）

// 數(shù)據(jù)寫入服務(wù)
public class 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;
    }
}

3. 數(shù)據(jù)遷移（分批處理）

-- 分批遷移腳本
SET @start_id = 0;
WHILE EXISTS(SELECT 1 FROM user WHERE id > @start_id) DO
    INSERT INTO user_new (id, name, age)
    SELECT id, name, 
        COALESCE(age_cache, 0) -- 從緩存獲取默認(rèn)值
    FROM user
    WHERE id > @start_id
    ORDER BY id
    LIMIT 10000;
    
    SET @start_id = (SELECT MAX(id) FROM user_new);
    COMMIT;
    -- 暫停100ms避免IO過載
    SELECT SLEEP(0.1); 
END WHILE;

4. 灰度切換流程

這套方案適合10億上的表新增字段，不過操作起來比較麻煩，改動(dòng)有點(diǎn)大。

6.使用gh-ost方案

gh-ost（GitHub's Online Schema Transmogrifier）是GitHub開源的一種無觸發(fā)器的MySQL在線表結(jié)構(gòu)變更方案。

專為解決大表DDL（如新增字段、索引變更、表引擎轉(zhuǎn)換）時(shí)鎖表阻塞、主庫負(fù)載高等問題而設(shè)計(jì)。

其核心是通過異步解析binlog，替代觸發(fā)器同步增量數(shù)據(jù)，顯著降低對(duì)線上業(yè)務(wù)的影響。

與傳統(tǒng)方案對(duì)比

觸發(fā)器方案（如pt-osc）：
在源表上創(chuàng)建INSERT/UPDATE/DELETE觸發(fā)器，在同一事務(wù)內(nèi)將變更同步到影子表。
痛點(diǎn)：
- 觸發(fā)器加重主庫CPU和鎖競爭，高并發(fā)時(shí)性能下降30%以上
- 無法暫停，失敗需重頭開始
- 外鍵約束支持復(fù)雜
gh-ost方案：
- 偽裝為從庫：直連主庫或從庫，拉取ROW格式的binlog，解析DML事件（INSERT/UPDATE/DELETE）
- 異步應(yīng)用：將增量數(shù)據(jù)通過獨(dú)立連接應(yīng)用到影子表（如REPLACE INTO處理INSERT事件），與主庫事務(wù)解耦
- 優(yōu)先級(jí)控制：binlog應(yīng)用優(yōu)先級(jí) > 全量數(shù)據(jù)拷貝，確保數(shù)據(jù)強(qiáng)一致

關(guān)鍵流程：

全量拷貝：按主鍵分塊（chunk-size控制）執(zhí)行INSERT IGNORE INTO _table_gho SELECT ...，避免重復(fù)插入
增量同步：
- INSERT → REPLACE INTO
- UPDATE → 全行覆蓋更新
- DELETE → DELETE
原子切換（Cut-over）：
1. 短暫鎖源表（毫秒級(jí)）
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-seconds=5 \  # 超時(shí)重試
--execute \               # 實(shí)際執(zhí)行
--allow-on-master         # 直連主庫模式

2. 監(jiān)控與優(yōu)化建議

進(jìn)度跟蹤：

echo status | nc -U /tmp/gh-ost.sock  # 查看實(shí)時(shí)進(jìn)度

延遲控制：
- 設(shè)置--max-lag-millis=1500，超閾值自動(dòng)暫停
- 從庫延遲過高時(shí)切換為直連主庫模式
切換安全：
使用--postpone-cut-over-flag-file人工控制切換時(shí)機(jī)

7.分區(qū)表滑動(dòng)窗口方案

適用場景：

按時(shí)間分區(qū)的日志型大表
需要頻繁變更結(jié)構(gòu)的監(jiān)控表

核心原理：
通過分區(qū)表特性，僅修改最新分區(qū)結(jié)構(gòu)。

操作步驟：

修改分區(qū)定義：

-- 原分區(qū)表定義
CREATE TABLE logs (
    id BIGINT,
    log_time DATETIME,
    content TEXT
) PARTITION BY RANGE (TO_DAYS(log_time)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01'))
);

-- 添加新字段（僅影響新分區(qū)）
ALTER TABLE logs ADD COLUMN log_level VARCHAR(10) DEFAULT 'INFO';

創(chuàng)建新分區(qū)（自動(dòng)應(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ù)處理：

-- 僅對(duì)最近分區(qū)做數(shù)據(jù)初始化
UPDATE logs PARTITION (p202302) 
SET log_level = parse_log_level(content);

8.千萬級(jí)表操作注意事項(xiàng)

主鍵必須存在（無主鍵將全表掃描）
磁盤空間監(jiān)控（至少預(yù)留1.5倍表空間）
復(fù)制延遲控制

SHOW SLAVE STATUS; 
-- 確保Seconds_Behind_Master < 10

灰度驗(yàn)證步驟：
- 先在從庫執(zhí)行
- 檢查數(shù)據(jù)一致性
- 低峰期切主庫
字段屬性選擇：
- 避免NOT NULL（導(dǎo)致全表更新）
- 優(yōu)先使用ENUM代替VARCHAR
- 默認(rèn)值用NULL而非空字符串

9.各方案對(duì)比

以下是針對(duì)千萬級(jí)MySQL表新增字段的6種方案的對(duì)比。

方案	鎖表時(shí)間	業(yè)務(wù)影響	數(shù)據(jù)一致性	適用場景	復(fù)雜度
原生Online DDL	秒級(jí)~分鐘級(jí)	中（并發(fā)DML受限）	強(qiáng)一致	<1億的小表變更	低
停機(jī)維護(hù)	小時(shí)級(jí)	高（服務(wù)中斷）	強(qiáng)一致	允許停服+數(shù)據(jù)量<100GB	中
PT-OSC	毫秒級(jí)（僅cut-over）	中（觸發(fā)器開銷）	最終一致	無外鍵/觸發(fā)器的常規(guī)表	中
邏輯遷移+雙寫	0	低（需改代碼）	強(qiáng)一致	金融級(jí)核心表（10億+）	高
gh-ost	毫秒級(jí)（僅cut-over）	低（無觸發(fā)器）	最終一致	高并發(fā)大表（TB級(jí)）	中高
分區(qū)滑動(dòng)窗口	僅影響新分區(qū)	低	分區(qū)級(jí)一致	按時(shí)間分區(qū)的日志表	中

總結(jié)

常規(guī)場景（<1億行）：
- 首選 Online DDL（ALGORITHM=INSTANT，MySQL 8.0秒級(jí)加字段）
- 備選 PT-OSC（兼容低版本MySQL）
高并發(fā)大表（>1億行）：
- 必選 gh-ost（無觸發(fā)器設(shè)計(jì)，對(duì)寫入影響<5%）
金融核心表：
- 雙寫方案 是唯一選擇（需2-4周開發(fā)周期）
日志型表：
- 分區(qū)滑動(dòng)窗口 最優(yōu)（僅影響新分區(qū)）
緊急故障處理：
- 超百億級(jí)表異常時(shí)，考慮 停機(jī)維護(hù) + 回滾預(yù)案

給大家一些建議：

加字段前優(yōu)先使用 JSON字段預(yù)擴(kuò)展（ALTER TABLE user ADD COLUMN metadata JSON）

萬億級(jí)表建議 分庫分表 而非直接DDL

所有方案執(zhí)行前必須 全量備份（mysqldump + binlog）

流量監(jiān)測（Prometheus+Granfa實(shí)時(shí)監(jiān)控QPS）

在千萬級(jí)系統(tǒng)的戰(zhàn)場上，一次草率的ALTER操作可能就是壓垮駱駝的最后一根稻草。

最后說一句(求關(guān)注，別白嫖我)

如果這篇文章對(duì)您有所幫助，或者有所啟發(fā)的話，幫忙關(guān)注一下我的同名公眾號(hào)：蘇三說技術(shù)，您的支持是我堅(jiān)持寫作最大的動(dòng)力。

求一鍵三連：點(diǎn)贊、轉(zhuǎn)發(fā)、在看。

關(guān)注公眾號(hào)：【蘇三說技術(shù)】，在公眾號(hào)中回復(fù)：進(jìn)大廠，可以免費(fèi)獲取我最近整理的10萬字的面試寶典，好多小伙伴靠這個(gè)寶典拿到了多家大廠的offer。

本文收錄于我的技術(shù)網(wǎng)站：http://www.susan.net.cn

posted @ 2025-07-28 11:16 蘇三說技術(shù) 閱讀(2468) 評(píng)論(5) 收藏舉報(bào)

刷新頁面返回頂部

微信搜：蘇三說技術(shù)

關(guān)注公眾號(hào)：【蘇三說技術(shù)】，回復(fù)：進(jìn)大廠，免費(fèi)領(lǐng)取10萬字面試寶典。