~/bGZo/notes

12 min read

千万级的大表如何新增字段？ - 苏三说技术

前言

线上千万级的大表在新增字段的时候，一定要小心，我见过太多团队在千万级大表上执行 DDL 时翻车的案例。

很容易影响到正常用户的使用。

本文将深入剖析大表加字段的核心难点，并给出可落地的解决方案。

希望对你会有所帮助。

1.为什么大表加字段如此危险？

核心问题：MySQL 的 DDL 操作会锁表。

当执行 ALTER TABLE ADD COLUMN 时：

  1. MySQL 5.6 之前：全程锁表（阻塞所有读写）
  2. MySQL 5.6+：仅支持部分操作的 Online DDL 通过实验验证锁表现象：

锁表时间计算公式：

对于 1000 万行、单行 1KB 的表，机械磁盘（100MB/s）需要 100 秒的不可用时间！

如果在一个高并发的系统中，这个问题简直无法忍受。

那么，我们要如何解决问题呢？

2.原生 Online DDL 方案

在 MySQL 5.6+ 版本中可以使用原生 Online DDL 的语法。

例如：

实现原理：

致命缺陷：

  1. 仍可能触发表锁（如添加全文索引）
  2. 磁盘空间需双倍（实测 500GB 表需要 1TB 空闲空间）
  3. 主从延迟风险（从库单线程回放）

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，替代触发器同步增量数据，显著降低对线上业务的影响。

与传统方案对比

  • \n 触发器方案（如 pt-osc）：\n 在源表上创建 INSERT/UPDATE/DELETE 触发器，在同一事务内将变更同步到影子表。\n 痛点：\n\n 触发器加重主库 CPU 和锁竞争，高并发时性能下降 30% 以上\n 无法暂停，失败需重头开始\n 外键约束支持复杂\n\n

  • 触发器加重主库 CPU 和锁竞争，高并发时性能下降 30% 以上

  • 无法暂停，失败需重头开始

  • 外键约束支持复杂

  • \ngh-ost 方案：\n\n 伪装为从库：直连主库或从库，拉取 ROW 格式的 binlog，解析 DML 事件（INSERT/UPDATE/DELETE）\n 异步应用：将增量数据通过独立连接应用到影子表（如 REPLACE INTO 处理 INSERT 事件），与主库事务解耦\n 优先级控制：binlog 应用优先级 > 全量数据拷贝，确保数据强一致\n\n

  • 伪装为从库：直连主库或从库，拉取 ROW 格式的 binlog，解析 DML 事件（INSERT/UPDATE/DELETE）

  • 异步应用：将增量数据通过独立连接应用到影子表（如 REPLACE INTO 处理 INSERT 事件），与主库事务解耦

  • 优先级控制：binlog 应用优先级 > 全量数据拷贝，确保数据强一致 触发器方案（如 pt-osc）：\n 在源表上创建 INSERT/UPDATE/DELETE 触发器，在同一事务内将变更同步到影子表。\n 痛点：

  • 触发器加重主库 CPU 和锁竞争，高并发时性能下降 30% 以上

  • 无法暂停，失败需重头开始

  • 外键约束支持复杂 gh-ost 方案：

  • 伪装为从库：直连主库或从库，拉取 ROW 格式的 binlog，解析 DML 事件（INSERT/UPDATE/DELETE）

  • 异步应用：将增量数据通过独立连接应用到影子表（如 REPLACE INTO 处理 INSERT 事件），与主库事务解耦

  • 优先级控制：binlog 应用优先级 > 全量数据拷贝，确保数据强一致

关键流程：

  • 全量拷贝：按主键分块（chunk-size 控制）执行 INSERT IGNORE INTO _table_gho SELECT …，避免重复插入
  • 增量同步：\n\nINSERT →REPLACE INTO\nUPDATE → 全行覆盖更新\nDELETE →DELETE\n\n
  • INSERT →REPLACE INTO
  • UPDATE → 全行覆盖更新
  • DELETE →DELETE
  • 原子切换（Cut-over）：\n\n 短暂锁源表（毫秒级）\n 执行原子 RENAME：RENAME TABLE source TO _source_del, _source_gho TO source\n 清理旧表（_source_del）\n\n
  • 短暂锁源表（毫秒级）
  • 执行原子 RENAME：RENAME TABLE source TO _source_del, _source_gho TO source
  • 清理旧表（_source_del）
  • INSERT →REPLACE INTO
  • UPDATE → 全行覆盖更新
  • DELETE →DELETE
  1. 短暂锁源表（毫秒级）
  2. 执行原子 RENAME：RENAME TABLE source TO _source_del, _source_gho TO source
  3. 清理旧表（_source_del）

典型命令示例：

2.监控与优化建议

  • 进度跟踪：
  • 延迟控制：\n\n 设置 —max-lag-millis=1500，超阈值自动暂停\n 从库延迟过高时切换为直连主库模式\n\n
  • 设置 —max-lag-millis=1500，超阈值自动暂停
  • 从库延迟过高时切换为直连主库模式
  • 切换安全：\n 使用 —postpone-cut-over-flag-file 人工控制切换时机
  • 设置 —max-lag-millis=1500，超阈值自动暂停
  • 从库延迟过高时切换为直连主库模式

7.分区表滑动窗口方案

适用场景：

  • 按时间分区的日志型大表
  • 需要频繁变更结构的监控表 核心原理：\n 通过分区表特性，仅修改最新分区结构。

操作步骤：

修改分区定义：

创建新分区（自动应用新结构）：

历史数据处理：

8.千万级表操作注意事项

  1. 主键必须存在（无主键将全表扫描）
  2. 磁盘空间监控（至少预留 1.5 倍表空间）
  3. 复制延迟控制
  4. \n 灰度验证步骤：\n\n 先在从库执行\n 检查数据一致性\n 低峰期切主库\n\n
  5. 先在从库执行
  6. 检查数据一致性
  7. 低峰期切主库
  8. \n 字段属性选择：\n\n 避免 NOT NULL（导致全表更新）\n 优先使用 ENUM 代替 VARCHAR\n 默认值用 NULL 而非空字符串\n\n
  9. 避免 NOT NULL（导致全表更新）
  10. 优先使用 ENUM 代替 VARCHAR
  11. 默认值用 NULL 而非空字符串 灰度验证步骤：

  • 先在从库执行

  • 检查数据一致性

  • 低峰期切主库 字段属性选择：

  • 避免 NOT NULL（导致全表更新）

  • 优先使用 ENUM 代替 VARCHAR

  • 默认值用 NULL 而非空字符串

9.各方案对比

以下是针对千万级 MySQL 表新增字段的 6 种方案的对比。

总结

  1. \n 常规场景（<1 亿行）：\n\n 首选 Online DDL（ALGORITHM=INSTANT，MySQL 8.0 秒级加字段）\n 备选 PT-OSC（兼容低版本 MySQL）\n\n
  2. 首选 Online DDL（ALGORITHM=INSTANT，MySQL 8.0 秒级加字段）
  3. 备选 PT-OSC（兼容低版本 MySQL）
  4. \n 高并发大表（>1 亿行）：\n\n 必选 gh-ost（无触发器设计，对写入影响<5%）\n\n
  5. 必选 gh-ost（无触发器设计，对写入影响<5%）
  6. \n 金融核心表：\n\n 双写方案是唯一选择（需 2-4 周开发周期）\n\n
  7. 双写方案是唯一选择（需 2-4 周开发周期）
  8. \n 日志型表：\n\n 分区滑动窗口最优（仅影响新分区）\n\n
  9. 分区滑动窗口最优（仅影响新分区）
  10. \n 紧急故障处理：\n\n 超百亿级表异常时，考虑停机维护 + 回滚预案\n\n
  11. 超百亿级表异常时，考虑停机维护 + 回滚预案 常规场景（<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 操作可能就是压垮骆驼的最后一根稻草。

最后说一句 (求关注，别白嫖我)

如果这篇文章对您有所帮助，或者有所启发的话，帮忙关注一下我的同名公众号：苏三说技术，您的支持是我坚持写作最大的动力。

求一键三连：点赞、转发、在看。

关注公众号：【苏三说技术】，在公众号中回复：进大厂，可以免费获取我最近整理的 10 万字的面试宝典，好多小伙伴靠这个宝典拿到了多家大厂的 offer。

本文收录于我的技术网站：http://www.susan.net.cn

www.susan.net.cn

Graph View