前言

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

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

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

至于。非常至于。

因為一旦你在大表上執(zhí)行ALTER TABLE，數(shù)據(jù)庫可能會長時間鎖表，導(dǎo)致：

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

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

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

為什么大表ALTER TABLE會這么慢？

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

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

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

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

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

主流解決方案對比

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

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

最簡單粗暴的方式：

ALTER TABLE user ADD COLUMN new_flag TINYINT DEFAULT 0;

適用場景：

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

優(yōu)點：

• 操作簡單，無需額外工具
• 成本最低

缺點：

• 鎖表時間不可控，大表風險極高
• 無法做到“無感變更”

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

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

從 MySQL5.6 開始，支持Online DDL，允許在執(zhí)行DDL時不阻塞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ù)期間仍會占用I/O和CPU，可能影響性能。

優(yōu)點：

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

缺點：

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

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

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

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

工作原理：

1. 創(chuàng)建一個新表tbl_new，結(jié)構(gòu)包含新字段
2. 在原表上創(chuàng)建三個觸發(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 '會員等級'" \
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：負載上限，超過則暫停
• --critical-load：致命負載，超過則終止
• --sleep：每批拷貝后休眠時間，降低壓力

優(yōu)點：

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

缺點：

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

建議：千萬級大表的首選方案。

方案四：手動模擬 PT-OSC（無工具時的備選）

如果你無法使用 PT-OSC（如安全限制、權(quán)限問題），可以手動實現(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. 短暫停機，切換表名（秒級）
RENAME TABLE user TO user_old, user_new TO user;
-- 5. 驗證無誤后刪除舊表
DROP TABLE user_old;

優(yōu)點：

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

缺點：

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

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

實戰(zhàn)案例

需求背景

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

技術(shù)選型

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

執(zhí)行步驟

1.前置檢查

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

2.執(zhí)行變更

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

3.實時監(jiān)控

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

MySQL 8.0的新變化

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

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

例如：

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

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

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

最佳實踐總結(jié)

補充建議

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

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

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

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

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

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

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

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

最新評論