前言
線(xiàn)上千萬(wàn)級(jí)的大表在新增字段的時(shí)候，一定要小心，我見(jiàn)過(guò)太多團(tuán)隊(duì)在千萬(wàn)級(jí)大表上執(zhí)行DDL時(shí)翻車(chē)的案例。
很容易影響到正常用戶(hù)的使用。
本文將深入剖析大表加字段的核心難點(diǎn)，并給出可落地的解決方案。
希望對(duì)你會(huì)有所幫助。
1.為什么大表加字段如此危險(xiǎn)？
核心問(wèn)題：MySQL的DDL操作會(huì)鎖表。
當(dāng)執(zhí)行ALTER TABLE ADD COLUMN時(shí)：
- MySQL 5.6之前：全程鎖表（阻塞所有讀寫(xiě)）
- MySQL 5.6+：僅支持部分操作的Online DDL
通過(guò)實(shí)驗(yàn)驗(yàn)證鎖表現(xiàn)象：
ALTER TABLE user ADD COLUMN age INT;
SELECT * FROM user WHERE id=1;
鎖表時(shí)間計(jì)算公式：
鎖表時(shí)間 ≈ 表數(shù)據(jù)量 / 磁盤(pán)IO速度
對(duì)于1000萬(wàn)行、單行1KB的表，機(jī)械磁盤(pán)（100MB/s）需要100秒的不可用時(shí)間！
如果在一個(gè)高并發(fā)的系統(tǒng)中，這個(gè)問(wèn)題簡(jiǎn)直無(wú)法忍受。
那么，我們要如何解決問(wèn)題呢？
2.原生Online DDL方案
在MySQL 5.6+版本中可以使用原生Online DDL的語(yǔ)法。
例如：
ALTER TABLE user
ADD COLUMN age INT,
ALGORITHM=INPLACE,
LOCK=NONE;
實(shí)現(xiàn)原理：
致命缺陷：
- 仍可能觸發(fā)表鎖（如添加全文索引）
- 磁盤(pán)空間需雙倍（實(shí)測(cè)500GB表需要1TB空閑空間）
- 主從延遲風(fēng)險(xiǎn)（從庫(kù)單線(xiàn)程回放）
3.停機(jī)維護(hù)方案
適用場(chǎng)景：
- 允許停服時(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
pt-online-schema-change \
--alter "ADD COLUMN age INT" \
D=test,t=user \
--execute
5.邏輯遷移 + 雙寫(xiě)方案
還有一個(gè)金融級(jí)安全的方案是：邏輯遷移 + 雙寫(xiě)方案。
適用場(chǎng)景：
- 字段變更伴隨業(yè)務(wù)邏輯修改（如字段類(lèi)型變更）
- 要求零數(shù)據(jù)丟失的金融場(chǎng)景
- 超10億行數(shù)據(jù)的表
實(shí)施步驟：
1. 創(chuàng)建新表結(jié)構(gòu)
CREATE TABLE user_new (
id BIGINT PRIMARY KEY,
name VARCHAR(50),
age INT DEFAULT 0,
KEY idx_name(name)
) ENGINE=InnoDB;
2. 雙寫(xiě)邏輯實(shí)現(xiàn)（Java示例）
public class UserService {
@Transactional
public void addUser(User user) {
userOldDAO.insert(user);
userNewDAO.insert(convertToNew(user));
}
private UserNew convertToNew(User old) {
UserNew userNew = new UserNew();
userNew.setId(old.getId());
userNew.setName(old.getName());
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)
FROM user
WHERE id > @start_id
ORDER BY id
LIMIT 10000;
SET @start_id = (SELECT MAX(id) FROM user_new);
COMMIT;
SELECT SLEEP(0.1);
END WHILE;
4. 灰度切換流程
這套方案適合10億上的表新增字段，不過(guò)操作起來(lái)比較麻煩，改動(dòng)有點(diǎn)大。
6.使用gh-ost方案
gh-ost（GitHub's Online Schema Transmogrifier）是GitHub開(kāi)源的一種無(wú)觸發(fā)器的MySQL在線(xiàn)表結(jié)構(gòu)變更方案。
專(zhuān)為解決大表DDL（如新增字段、索引變更、表引擎轉(zhuǎn)換）時(shí)鎖表阻塞、主庫(kù)負(fù)載高等問(wèn)題而設(shè)計(jì)。
其核心是通過(guò)異步解析binlog，替代觸發(fā)器同步增量數(shù)據(jù)，顯著降低對(duì)線(xiàn)上業(yè)務(wù)的影響。
與傳統(tǒng)方案對(duì)比
關(guān)鍵流程：
- 全量拷貝：按主鍵分塊（
chunk-size控制）執(zhí)行INSERT IGNORE INTO _table_gho SELECT ...，避免重復(fù)插入 - 增量同步：
- INSERT →
REPLACE INTO - UPDATE → 全行覆蓋更新
- DELETE →
DELETE
- 原子切換（Cut-over）：
- 短暫鎖源表（毫秒級(jí)）
- 執(zhí)行原子RENAME：
RENAME TABLE source TO _source_del, _source_gho TO source - 清理舊表（
_source_del）
典型命令示例：
gh-ost \
--alter="ADD COLUMN age INT NOT NULL DEFAULT 0 COMMENT '用戶(hù)年齡'" \
--host=主庫(kù)IP --port=3306 --user=gh_user --password=xxx \
--database=test --table=user \
--chunk-size=2000 \
--max-load=Threads_running=80 \
--critical-load=Threads_running=200 \
--cut-over-lock-timeout-seconds=5 \
--execute \
--allow-on-master
2. 監(jiān)控與優(yōu)化建議
echo status | nc -U /tmp/gh-ost.sock
- 延遲控制：
- 設(shè)置
--max-lag-millis=1500，超閾值自動(dòng)暫停 - 從庫(kù)延遲過(guò)高時(shí)切換為
直連主庫(kù)模式
- 切換安全：
使用--postpone-cut-over-flag-file人工控制切換時(shí)機(jī)
7.分區(qū)表滑動(dòng)窗口方案
適用場(chǎng)景：
- 按時(shí)間分區(qū)的日志型大表
- 需要頻繁變更結(jié)構(gòu)的監(jiān)控表
核心原理：
通過(guò)分區(qū)表特性，僅修改最新分區(qū)結(jié)構(gòu)。
操作步驟：
修改分區(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'))
);
ALTER TABLE logs ADD COLUMN log_level VARCHAR(10) DEFAULT 'INFO';
創(chuàng)建新分區(qū)（自動(dòng)應(yīng)用新結(jié)構(gòu)）：
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ù)處理：
UPDATE logs PARTITION (p202302)
SET log_level = parse_log_level(content);
8.千萬(wàn)級(jí)表操作注意事項(xiàng)
- 主鍵必須存在（無(wú)主鍵將全表掃描）
- 磁盤(pán)空間監(jiān)控（至少預(yù)留1.5倍表空間）
- 復(fù)制延遲控制
SHOW SLAVE STATUS;
灰度驗(yàn)證步驟：
- 先在從庫(kù)執(zhí)行
- 檢查數(shù)據(jù)一致性
- 低峰期切主庫(kù)
字段屬性選擇：
- 避免NOT NULL（導(dǎo)致全表更新）
- 優(yōu)先使用ENUM代替VARCHAR
- 默認(rèn)值用NULL而非空字符串
9.各方案對(duì)比
以下是針對(duì)千萬(wàn)級(jí)MySQL表新增字段的6種方案的對(duì)比。
| 方案 | 鎖表時(shí)間 | 業(yè)務(wù)影響 | 數(shù)據(jù)一致性 | 適用場(chǎng)景 | 復(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ā)器開(kāi)銷(xiāo)） | 最終一致 | 無(wú)外鍵/觸發(fā)器的常規(guī)表 | 中 |
| 邏輯遷移+雙寫(xiě) | 0 | 低（需改代碼） | 強(qiáng)一致 | 金融級(jí)核心表（10億+） | 高 |
| gh-ost | 毫秒級(jí)（僅cut-over） | 低（無(wú)觸發(fā)器） | 最終一致 | 高并發(fā)大表（TB級(jí)） | 中高 |
| 分區(qū)滑動(dòng)窗口 | 僅影響新分區(qū) | 低 | 分區(qū)級(jí)一致 | 按時(shí)間分區(qū)的日志表 | 中 |
總結(jié)
常規(guī)場(chǎng)景（<1億行）：
- 首選 Online DDL（
ALGORITHM=INSTANT，MySQL 8.0秒級(jí)加字段） - 備選 PT-OSC（兼容低版本MySQL）
高并發(fā)大表（>1億行）：
- 必選 gh-ost（無(wú)觸發(fā)器設(shè)計(jì)，對(duì)寫(xiě)入影響<5%）
金融核心表：
- 雙寫(xiě)方案 是唯一選擇（需2-4周開(kāi)發(fā)周期）
日志型表：
- 分區(qū)滑動(dòng)窗口 最優(yōu)（僅影響新分區(qū)）
緊急故障處理：
- 超百億級(jí)表異常時(shí)，考慮 停機(jī)維護(hù) + 回滾預(yù)案
給大家一些建議：
- 加字段前優(yōu)先使用 JSON字段預(yù)擴(kuò)展（
ALTER TABLE user ADD COLUMN metadata JSON） - 萬(wàn)億級(jí)表建議 分庫(kù)分表 而非直接DDL
- 所有方案執(zhí)行前必須 全量備份（
mysqldump + binlog） - 流量監(jiān)測(cè)（Prometheus+Granfa實(shí)時(shí)監(jiān)控QPS）
在千萬(wàn)級(jí)系統(tǒng)的戰(zhàn)場(chǎng)上，一次草率的ALTER操作可能就是壓垮駱駝的最后一根稻草。
?轉(zhuǎn)自https://www.cnblogs.com/12lisu/p/19008591
該文章在 2025/8/1 9:48:49 編輯過(guò)
400 186 1886
