前言

在日常開發(fā)中，我們經(jīng)常需要為數(shù)據(jù)庫(kù)表加字段。對(duì)于一張只有幾千行的小表來說，一句 ALTER TABLE 瞬間完成，幾乎沒有任何感知。

但當(dāng)這張表的數(shù)據(jù)量達(dá)到千萬級(jí)甚至上億級(jí)時(shí)，事情就變得復(fù)雜起來了。

你可能會(huì)問：“我只是加個(gè)字段，又不是刪數(shù)據(jù)，至于大動(dòng)干戈嗎？”

至于。非常至于。

因?yàn)橐坏┠阍诖蟊砩蠄?zhí)行ALTER TABLE，數(shù)據(jù)庫(kù)可能會(huì)長(zhǎng)時(shí)間鎖表，導(dǎo)致：

• 讀寫阻塞：所有查詢和寫入請(qǐng)求被掛起
• 業(yè)務(wù)中斷：訂單無法提交、支付失敗、頁面卡頓
• 主從延遲加劇：主庫(kù)執(zhí)行 DDL 時(shí)，從庫(kù)復(fù)制延遲飆升，影響報(bào)表、備份甚至高可用切換
• 連接堆積：應(yīng)用連接池耗盡，服務(wù)雪崩

假設(shè)有一個(gè)用戶中心的核心表，數(shù)據(jù)量超過 5000 萬。如果直接執(zhí)行 ALTER TABLE 加字段，可能會(huì)導(dǎo)致服務(wù)中斷近幾分鐘。這期間可能會(huì)導(dǎo)致各種訂單流失、客服報(bào)警，于是老板震怒……

大表結(jié)構(gòu)變更，必須慎之又慎。

為什么大表ALTER TABLE會(huì)這么慢？

要解決問題，先理解原理。

MySQL 的 ALTER TABLE 操作本質(zhì)上是重建表（Rebuild Table）的過程：

1. 創(chuàng)建一個(gè)臨時(shí)的新結(jié)構(gòu)表
2. 將原表數(shù)據(jù)逐行拷貝到新表
3. 刪除原表，重命名新表
4. 重建索引

這個(gè)過程在早期版本（如 MySQL 5.5 及以前）中是全程鎖表的（LOCK=EXCLUSIVE），意味著整個(gè)操作期間，任何 DML（INSERT/UPDATE/DELETE）都會(huì)被阻塞。

雖然 MySQL5.6 引入了Online DDL，允許部分DDL操作在不鎖表的情況下進(jìn)行，但仍然存在性能影響和兼容性限制。

主流解決方案對(duì)比

針對(duì)大表加字段的問題，業(yè)界有幾種成熟方案。下面我們逐一分析其原理、優(yōu)缺點(diǎn)及適用場(chǎng)景。

方案一：低峰期直接ALTER TABLE（適用于小表）

最簡(jiǎn)單粗暴的方式：

ALTER TABLE user ADD COLUMN new_flag TINYINT DEFAULT 0;

適用場(chǎng)景：

• 表數(shù)據(jù)量較?。?lt; 100 萬）
• 業(yè)務(wù)容忍短暫不可用
• 無主從延遲要求

優(yōu)點(diǎn)：

• 操作簡(jiǎn)單，無需額外工具
• 成本最低

缺點(diǎn)：

• 鎖表時(shí)間不可控，大表風(fēng)險(xiǎn)極高
• 無法做到“無感變更”

建議：僅用于測(cè)試環(huán)境或極小表。

方案二：使用MySQL Online DDL（推薦用于中等表）

從 MySQL5.6 開始，支持Online DDL，允許在執(zhí)行DDL時(shí)不阻塞DML操作。

關(guān)鍵語法：

ALTER TABLE user 
ADD COLUMN new_flag TINYINT DEFAULT 0, 
ALGORITHM=INPLACE, 
LOCK=NONE;

ALGORITHM=INPLACE：使用原地修改算法，避免全表重建 LOCK=NONE：表示不加鎖，允許并發(fā)讀寫

支持情況（以加字段為例）：

注意：即使LOCK=NONE，也并非完全無影響。拷貝數(shù)據(jù)期間仍會(huì)占用I/O和CPU，可能影響性能。

優(yōu)點(diǎn)：

• 原生支持，無需外部工具
• 真正實(shí)現(xiàn)不停機(jī)變更

缺點(diǎn)：

• 不支持所有DDL類型（如修改列類型仍需重建）
• 大表執(zhí)行時(shí)間長(zhǎng)，仍可能引發(fā)主從延遲
• 需要足夠磁盤空間（臨時(shí)文件）

建議：適用于100萬~100萬行的中等表，且使用MySQL 5.7+。

方案三：使用PT-OSC（Percona Toolkit）——生產(chǎn)環(huán)境首選

PT-OSC（pt-online-schema-change） 是Percona 提供的開源工具，專為大表在線 DDL 設(shè)計(jì)。

工作原理：

1. 創(chuàng)建一個(gè)新表tbl_new，結(jié)構(gòu)包含新字段
2. 在原表上創(chuàng)建三個(gè)觸發(fā)器（INSERT/UPDATE/DELETE），同步變更到新表
3. 分批將原表數(shù)據(jù)拷貝到新表（每次只拷幾百條，減少壓力）
4. 數(shù)據(jù)同步完成后，原子性重命名：RENAME TABLE tbl TO tbl_old, tbl_new TO tbl
5. 刪除舊表

示例命令：

pt-online-schema-change \
--host=localhost \
--user=root \
--password=your_password \
--alter="ADD COLUMN membership_level TINYINT DEFAULT 0 COMMENT '會(huì)員等級(jí)'" \
D=ecdb,t=user \
--chunk-size=5000 \
--max-load="Threads_running=50" \
--critical-load="Threads_running=100" \
--sleep=0.5 \
--execute

參數(shù)說明：

• --chunk-size：每次拷貝的數(shù)據(jù)量
• --max-load：負(fù)載上限，超過則暫停
• --critical-load：致命負(fù)載，超過則終止
• --sleep：每批拷貝后休眠時(shí)間，降低壓力

優(yōu)點(diǎn)：

• 幾乎不影響線上業(yè)務(wù)
• 支持精細(xì)控制資源占用
• 成熟穩(wěn)定，被大量互聯(lián)網(wǎng)公司采用

缺點(diǎn)：

• 需要安裝 Percona Toolkit
• 需要額外磁盤空間（雙表并存）
• 觸發(fā)器帶來輕微性能開銷（通常 < 5%）
• 不支持有外鍵引用的表（除非用 --alter-foreign-keys-method）

建議：千萬級(jí)大表的首選方案。

方案四：手動(dòng)模擬 PT-OSC（無工具時(shí)的備選）

如果你無法使用 PT-OSC（如安全限制、權(quán)限問題），可以手動(dòng)實(shí)現(xiàn)類似流程。

-- 1. 創(chuàng)建新表
CREATE TABLE user_new LIKE user;
ALTER TABLE user_new ADD COLUMN new_flag TINYINT DEFAULT 0;
-- 2. 分批遷移數(shù)據(jù)
INSERT INTO user_new SELECT *, 0 FROM user WHERE id BETWEEN 1 AND 100000;
-- 循環(huán)執(zhí)行，逐步遷移
-- 3. 數(shù)據(jù)追平后，創(chuàng)建觸發(fā)器同步變更
DELIMITER $$
CREATE TRIGGER user_insert_trg AFTER INSERT ON user
FOR EACH ROW BEGIN
    INSERT INTO user_new VALUES (NEW.*, 0);
END$$
-- 同樣創(chuàng)建 UPDATE 和 DELETE 觸發(fā)器
DELIMITER ;
-- 4. 短暫停機(jī)，切換表名（秒級(jí)）
RENAME TABLE user TO user_old, user_new TO user;
-- 5. 驗(yàn)證無誤后刪除舊表
DROP TABLE user_old;

優(yōu)點(diǎn)：

• 不依賴外部工具
• 完全可控

缺點(diǎn)：

• 手動(dòng)操作易出錯(cuò)
• 切換瞬間仍有短暫鎖表（RENAME 是原子操作，但需獨(dú)占表名）
• 需精確控制觸發(fā)器邏輯

建議：僅作為 PT-OSC 不可用時(shí)的備選方案。

實(shí)戰(zhàn)案例

需求背景

電商平臺(tái)用戶表user，數(shù)據(jù)量 6200 萬，需添加membership_level字段用于會(huì)員體系升級(jí)。

技術(shù)選型

• MySQL 5.7.30
• 使用PT-OSC 實(shí)現(xiàn)在線變更
• 選擇凌晨2:00 執(zhí)行（業(yè)務(wù)低峰）

執(zhí)行步驟

1.前置檢查

• 磁盤剩余空間 ≥ 1.5 倍原表大?。s 120GB）
• 備份表結(jié)構(gòu)與數(shù)據(jù)（mysqldump + binlog）
• 準(zhǔn)備回滾腳本

2.執(zhí)行變更

pt-online-schema-change \
--alter="ADD COLUMN membership_level TINYINT DEFAULT 0 COMMENT '會(huì)員等級(jí)'" \
D=ecdb,t=user \
--chunk-size=10000 \
--max-load="Threads_running=40" \
--critical-load="Threads_running=80" \
--sleep=0.2 \
--print \
--execute

3.實(shí)時(shí)監(jiān)控

• SHOW PROCESSLIST; 查看拷貝進(jìn)度
• 監(jiān)控 CPU、I/O、主從延遲
• 應(yīng)用層監(jiān)控錯(cuò)誤率、響應(yīng)時(shí)間

MySQL 8.0的新變化

MySQL8.0對(duì)DDL進(jìn)行了重大優(yōu)化：

原子性 DDL：DDL 操作支持事務(wù)回滾（如失敗可自動(dòng)清理） 更快的加字段：新增字段默認(rèn)為“即時(shí)添加”（Instant Add Column），僅修改元數(shù)據(jù)，幾乎瞬間完成 支持更多 INPLACE 操作

例如：

ALTER TABLE user ADD COLUMN new_col VARCHAR(50) DEFAULT NULL, ALGORITHM=INSTANT;

注意：INSTANT算法僅支持在表末尾添加字段，且不能是主鍵或NOT NULL無默認(rèn)值的字段。

?? 建議：如果使用 MySQL 8.0+，優(yōu)先嘗試 ALGORITHM=INSTANT，性能極佳。

最佳實(shí)踐總結(jié)

補(bǔ)充建議

1.避免 NOT NULL 無默認(rèn)值的字段
加 NOT NULL 字段需全表初始化，代價(jià)極高。建議先加 DEFAULT NULL 或帶默認(rèn)值。

2.盡量在表末尾加字段
有助于觸發(fā) INSTANT 算法（MySQL 8.0+）

3.慎用外鍵
外鍵會(huì)增加 PT-OSC 的復(fù)雜度，建議業(yè)務(wù)層維護(hù)一致性

4.考慮影子表（Shadow Table）模式
對(duì)于極端敏感的系統(tǒng)，可采用雙寫影子表 + 流量切換的方式，實(shí)現(xiàn)零停機(jī)變更

5.替代工具推薦 gh-ost（GitHub 開源）：基于 binlog 同步，無需觸發(fā)器，更安全 AliSQL Online DDL：阿里云優(yōu)化版本，支持更多場(chǎng)景

技術(shù)無小事，細(xì)節(jié)定成敗。

選擇合適的方案，做好充分準(zhǔn)備，才能真正做到“變更無感，業(yè)務(wù)無憂”。

到此這篇關(guān)于淺談千萬級(jí)大表如何新增字段的文章就介紹到這了,更多相關(guān)千萬級(jí)大表新增字段內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論