大家好，我是苏三，又跟大家见面了。
前言
线上千万级的大表在新增字段的时候，一定要小心，我见过太多团队在千万级大表上执行DDL时翻车的案例。
很容易影响到正常用户的使用。
这篇文章跟大家一起聊聊线上千万级的大表新增字段的6种方案，希望对你会有所帮助。
点击这里获取：100万QPS短链系统、商城微服务、苏三AI项目、秒杀系统、商城系统、秒杀系统、代码生成工具等 7 个项目的 源代码、开发教程和技术答疑，本月底要涨价了！
1.为什么大表加字段如此危险？
核心问题：MySQL的DDL操作会锁表 。
当执行ALTER TABLE ADD COLUMN时：
- MySQL 5.6之前：全程锁表（阻塞所有读写）
- MySQL 5.6+：仅支持部分操作的Online DDL
通过实验验证锁表现象：
-- 会话1：执行DDL操作
ALTER TABLE user ADD COLUMN age INT;
-- 会话2：尝试查询（被阻塞）
SELECT * FROM user WHERE id=1; -- 等待DDL完成
锁表时间计算公式：
锁表时间 ≈ 表数据量 / 磁盘IO速度
对于1000万行、单行1KB的表，机械磁盘（100MB/s）需要100秒 的不可用时间！
如果在一个高并发的系统中，这个问题简直无法忍受。
那么，我们要如何解决问题呢？
2.原生Online DDL方案
在MySQL 5.6+版本中可以使用原生Online DDL的语法。
例如：
ALTER TABLE user
ADD COLUMN age INT,
ALGORITHM=INPLACE,
LOCK=NONE;
实现原理 ：
致命缺陷 ：
- 仍可能触发表锁（如添加全文索引）
- 磁盘空间需双倍（实测500GB表需要1TB空闲空间）
- 主从延迟风险（从库单线程回放）
3.停机维护方案
适用场景 ：
- 允许停服时间（如凌晨3点）
- 数据量小于100GB（减少导入时间）
- 有完整回滚预案
4.使用PT-OSC工具方案
Percona Toolkit的pt-online-schema-change 这个是我比较推荐的工具。
工作原理：
操作步骤：
# 安装工具
sudo yum install percona-toolkit
# 执行迁移（添加age字段）
pt-online-schema-change \
--alter "ADD COLUMN age INT" \
D=test,t=user \
--execute
5.逻辑迁移 + 双写方案
还有一个金融级安全的方案是：逻辑迁移 + 双写方案。
适用场景 ：
- 字段变更伴随业务逻辑修改（如字段类型变更）
- 要求零数据丢失的金融场景
- 超10亿行数据的表
实施步骤 ：
1. 创建新表结构
-- 创建包含新字段的副本表
CREATE TABLE user\_new (
id BIGINT PRIMARY KEY,
name VARCHAR(50),
-- 新增字段
age INT DEFAULT 0,
-- 增加原表索引
KEY idx\_name(name)
) ENGINE=InnoDB;
2. 双写逻辑实现（Java示例）
// 数据写入服务
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());
// 新字段处理（从其他系统获取或默认值）
userNew.setAge(getAgeFromCache(old.getId()));
return userNew;
}
}
3. 数据迁移（分批处理）
-- 分批迁移脚本
SET @start\_id = 0;
WHILE EXISTS(SELECT1FROMuserWHEREid > @start\_id) DO
INSERTINTO user\_new (id, name, age)
SELECTid, name,
COALESCE(age\_cache, 0) -- 从缓存获取默认值
FROMuser
WHEREid > @start\_id
ORDERBYid
LIMIT10000;
SET @start\_id = (SELECTMAX(id) FROM user\_new);
COMMIT;
-- 暂停100ms避免IO过载
SELECTSLEEP(0.1);
ENDWHILE;
4. 灰度切换流程
这套方案适合10亿上的表新增字段，不过操作起来比较麻烦，改动有点大。
6.使用gh-ost方案
gh-ost（GitHub's Online Schema Transmogrifier）是GitHub开源的一种无触发器的MySQL在线表结构变更方案 。
专为解决大表DDL（如新增字段、索引变更、表引擎转换）时锁表阻塞、主库负载高等问题而设计。
其核心是通过异步解析binlog，替代触发器同步增量数据，显著降低对线上业务的影响。
与传统方案对比
- 触发器方案（如pt-osc） ：
在源表上创建INSERT/UPDATE/DELETE触发器，在同一事务内将变更同步到影子表。
痛点 ：
- 触发器加重主库CPU和锁竞争，高并发时性能下降30%以上
- 无法暂停，失败需重头开始
- 外键约束支持复杂
- gh-ost方案 ：
- 伪装为从库 ：直连主库或从库，拉取ROW格式的binlog，解析DML事件（INSERT/UPDATE/DELETE）
- 异步应用
：将增量数据通过独立连接应用到影子表（如
REPLACE INTO处理INSERT事件），与主库事务解耦 - 优先级控制 ：binlog应用优先级 > 全量数据拷贝，确保数据强一致
关键流程：
- 全量拷贝
：按主键分块（
chunk-size控制）执行INSERT IGNORE INTO \_table\_gho SELECT ...，避免重复插入 - 增量同步 ：
- INSERT →
REPLACE INTO - UPDATE → 全行覆盖更新
- DELETE →
DELETE
- 原子切换（Cut-over） ：
- 短暂锁源表（毫秒级）
- 执行原子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 '用户年龄'" \
--host=主库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. 监控与优化建议
- 进度跟踪 ：
echo status | nc -U /tmp/gh-ost.sock # 查看实时进度
- 延迟控制 ：
- 设置
--max-lag-millis=1500，超阈值自动暂停 - 从库延迟过高时切换为
直连主库模式
- 切换安全 ：
使用
--postpone-cut-over-flag-file
人工控制切换时机
7.分区表滑动窗口方案
适用场景：
- 按时间分区的日志型大表
- 需要频繁变更结构的监控表
核心原理： 通过分区表特性，仅修改最新分区结构。
操作步骤 ：
修改分区定义：
-- 原分区表定义
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'))
);
-- 添加新字段（仅影响新分区）
ALTERTABLElogsADDCOLUMN log\_level VARCHAR(10) DEFAULT'INFO';
创建新分区（自动应用新结构）：
-- 创建包含新字段的分区
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'))
);
历史数据处理：
-- 仅对最近分区做数据初始化
UPDATE logs PARTITION (p202302)
SET log\_level = parse\_log\_level(content);
8.千万级表操作注意事项
- 主键必须存在 （无主键将全表扫描）
- 磁盘空间监控 （至少预留1.5倍表空间）
- 复制延迟控制
SHOW SLAVE STATUS;
-- 确保Seconds\_Behind\_Master < 10
- 灰度验证步骤 ：
- 先在从库执行
- 检查数据一致性
- 低峰期切主库
- 字段属性选择 ：
- 避免NOT NULL（导致全表更新）
- 优先使用ENUM代替VARCHAR
- 默认值用NULL而非空字符串
9.各方案对比
以下是针对千万级MySQL表新增字段的6种方案的对比。
| 方案 | 锁表时间 | 业务影响 | 数据一致性 | 适用场景 | 复杂度 |
|---|---|---|---|---|---|
| 原生Online DDL | |||||
| 秒级~分钟级 | |||||
| 中（并发DML受限） | |||||
| 强一致 | |||||
| <1亿的小表变更 | |||||
| 低 | |||||
| 停机维护 | |||||
| 小时级 | |||||
| 高（服务中断） | |||||
| 强一致 | |||||
| 允许停服+数据量<100GB | |||||
| 中 | |||||
| PT-OSC | |||||
| 毫秒级（仅cut-over） | |||||
| 中（触发器开销） | |||||
| 最终一致 | |||||
| 无外键/触发器的常规表 | |||||
| 中 | |||||
| 逻辑迁移+双写 | |||||
| 0 | |||||
| 低（需改代码） | |||||
| 强一致 | |||||
| 金融级核心表（10亿+） | |||||
| 高 | |||||
| gh-ost | |||||
| 毫秒级（仅cut-over） | |||||
| 低（无触发器） | |||||
| 最终一致 | |||||
| 高并发大表（TB级） | |||||
| 中高 | |||||
| 分区滑动窗口 | |||||
| 仅影响新分区 | |||||
| 低 | |||||
| 分区级一致 | |||||
| 按时间分区的日志表 | |||||
| 中 | |||||
总结
- 常规场景（<1亿行） ：
- 首选
Online DDL
（
ALGORITHM=INSTANT，MySQL 8.0秒级加字段） - 备选 PT-OSC （兼容低版本MySQL）
- 高并发大表（>1亿行） ：
- 必选 gh-ost （无触发器设计，对写入影响<5%）
- 金融核心表 ：
- 双写方案 是唯一选择（需2-4周开发周期）
- 日志型表 ：
- 分区滑动窗口 最优（仅影响新分区）
- 紧急故障处理 ：
- 超百亿级表异常时，考虑 停机维护
- 回滚预案
给大家一些建议 ：
加字段前优先使用 JSON字段预扩展 （
ALTER TABLE user ADD COLUMN metadata JSON）万亿级表建议 分库分表 而非直接DDL
所有方案执行前必须 全量备份 （
mysqldump + binlog）流量监测（Prometheus+Granfa实时监控QPS）
在千万级系统的战场上，一次草率的ALTER操作可能就是压垮骆驼的最后一根稻草。
最后欢迎加入苏三的星球，你将获得：100万QPS短链系统、复杂的商城微服务系统、苏三AI项目、刷题吧小程序、秒杀系统、商城系统、秒杀系统、代码生成工具等8个项目的源代码、开发教程和技术答疑。
系统设计、性能优化、技术选型、底层原理、Spring源码解读、工作经验分享、痛点问题、面试八股文等多个优质专栏。
还有1V1免费修改简历、技术答疑、职业规划、送书活动、技术交流。
目前星球已经更新了5800+篇优质内容，还在持续爆肝中.....
扫描下方二维码即可加入星球：
现在加入是非常合适的，本月底要涨价了。