千万级的大表如何新增字段?
前言
线上千万级的大表在新增字段的时候,一定要小心,我见过太多团队在千万级大表上执行 DDL 时翻车的案例。
很容易影响到正常用户的使用。
本文将深入剖析大表加字段的核心难点,并给出可落地的解决方案。
希望对你会有所帮助。
1.为什么大表加字段如此危险?
核心问题:MySQL 的 DDL 操作会锁表。
当执行ALTER TABLE ADD COLUMN
时:
MySQL 5.6 之前:全程锁表(阻塞所有读写)
MySQL 5.6+:仅支持部分操作的 Online DDL
通过实验验证锁表现象:
锁表时间计算公式:
对于 1000 万行、单行 1KB 的表,机械磁盘(100MB/s)需要 100 秒的不可用时间!
如果在一个高并发的系统中,这个问题简直无法忍受。
那么,我们要如何解决问题呢?

2.原生 Online DDL 方案
在 MySQL 5.6+版本中可以使用原生 Online DDL 的语法。
例如:
实现原理:

致命缺陷:
仍可能触发表锁(如添加全文索引)
磁盘空间需双倍(实测 500GB 表需要 1TB 空闲空间)
主从延迟风险(从库单线程回放)
3.停机维护方案

适用场景:
允许停服时间(如凌晨 3 点)
数据量小于 100GB(减少导入时间)
有完整回滚预案
4.使用 PT-OSC 工具方案
Percona Toolkit 的 pt-online-schema-change 这个是我比较推荐的工具。
工作原理:

操作步骤:
5.逻辑迁移 + 双写方案
还有一个金融级安全的方案是:逻辑迁移 + 双写方案。
适用场景:
字段变更伴随业务逻辑修改(如字段类型变更)
要求零数据丢失的金融场景
超 10 亿行数据的表
实施步骤:
1. 创建新表结构
2. 双写逻辑实现(Java 示例)
3. 数据迁移(分批处理)
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
)
典型命令示例:
2. 监控与优化建议
进度跟踪:
延迟控制:设置
--max-lag-millis=1500
,超阈值自动暂停从库延迟过高时切换为直连主库模式
切换安全:
使用
--postpone-cut-over-flag-file
人工控制切换时机
7.分区表滑动窗口方案
适用场景:
按时间分区的日志型大表
需要频繁变更结构的监控表
核心原理:通过分区表特性,仅修改最新分区结构。
操作步骤:
修改分区定义:
创建新分区(自动应用新结构):
历史数据处理:
8.千万级表操作注意事项
主键必须存在(无主键将全表扫描)
磁盘空间监控(至少预留 1.5 倍表空间)
复制延迟控制
4.灰度验证步骤:
先在从库执行
检查数据一致性
低峰期切主库
5.字段属性选择:
避免 NOT NULL(导致全表更新)
优先使用 ENUM 代替 VARCHAR
默认值用 NULL 而非空字符串
9.各方案对比
以下是针对千万级 MySQL 表新增字段的 6 种方案的对比。

总结
1、常规场景(<1 亿行):
首选 Online DDL(ALGORITHM=INSTANT
,MySQL 8.0 秒级加字段)
备选 PT-OSC(兼容低版本 MySQL)
2、高并发大表(>1 亿行):
必选 gh-ost(无触发器设计,对写入影响<5%)
3、金融核心表:
双写方案 是唯一选择(需 2-4 周开发周期)
4、日志型表:
分区滑动窗口 最优(仅影响新分区)
5、紧急故障处理:
超百亿级表异常时,考虑 停机维护 + 回滚预案
给大家一些建议:
加字段前优先使用 JSON 字段预扩展(
ALTER TABLE user ADD COLUMN metadata JSON
)万亿级表建议 分库分表 而非直接 DDL
所有方案执行前必须 全量备份(
mysqldump + binlog
)流量监测(Prometheus+Granfa 实时监控 QPS)
在千万级系统的战场上,一次草率的 ALTER 操作可能就是压垮骆驼的最后一根稻草。
文章转载自:苏三说技术
评论