为超过10亿条记录的订单表新增字段

如何为 10 亿行 MySQL 大表安全新增字段？

之前在一家餐饮公司待过几年。由于这家公司已经营业了很多年，MySQL 5.6 的订单表数据早已超过 10 亿行。这就带来一个经典难题：

如何为这张大表新增字段？

当时的处理方式

假设业务需求的上线时间是 2025 年 10 月 23 日，我们会安排组内同事在 10 月 22 日或 23 日凌晨 提交数据库变更：

1ALTER TABLE order_items ADD COLUMN new_flag TINYINT DEFAULT 0;
2

• 变更通常需要 4～6 小时 才能完成；
• 期间表会被锁住，无法写入；
• 但因为是 凌晨闭店时段，门店基本无流量，影响可控。

这是当时的最佳方案吗？

答案是：对的。

原因如下：

• 我们使用的是 MySQL 5.6，没有 Instant ADD COLUMN 功能（该功能从 MySQL 8.0.12 才引入）；
• 当时使用的是 腾讯云 MySQL，其“无锁结构变更”功能尚未对外开放；
• 团队（开发 + DBA）不具备使用 gh-ost 等工具的能力和经验；
• 业务低峰期（凌晨闭店）提供了操作窗口。

因此，只能使用 MySQL 原生 DDL，注定缓慢但安全。

那现在呢？有更好的方案吗？

当然有！ 主要分为两种情况：

方案一：利用 MySQL 8 的 Instant ADD COLUMN（即时加列）

原理

从 MySQL 8.0.12 开始，支持 Instant ADD COLUMN，其核心原理是：

• 不重建表（no table rebuild）
• 不复制数据（no data copy）
• 仅修改表元数据（.sdi 文件）
• 新增列的默认值通过 隐藏的默认值记录 实现，读取旧行时动态填充

✅ 仅适用于：在表末尾添加可为空或带默认值的列

传统加列 vs 即时加列：根本区别

即时加列的底层原理

核心思想：延迟填充（Lazy Evaluation）

MySQL 不在 DDL 时写默认值到每一行，而是在 读取旧行时动态填充默认值。

实现机制：

1. 表的元数据中记录“新增列的默认值”
   • 存储在数据字典（MySQL 8.0 使用 InnoDB 存储数据字典）
   • 每个 Instant 列都有一个“隐藏的默认值记录”
2. 物理数据行（.ibd 文件）完全不变
   • 旧数据行仍保持原来的格式和长度
   • 不占用额外磁盘空间
3. 查询时动态补值
   • 当 SELECT 读取一行时，InnoDB 发现该行“缺少新列”
   • 自动从元数据中取出默认值，返回给 Server 层
   • 应用层完全无感

📌 举例：

1-- 原表有 10 亿行，无 new_flag 列
2ALTER TABLE order_items ADD COLUMN new_flag TINYINT NULL DEFAULT 0; -- Instant
3
4SELECT new_flag FROM order_items WHERE id = 1;
5-- InnoDB 返回：0（即使物理行中根本没有这个字段）
6

元数据如何记录？

MySQL 8.0 引入了 “Instant Column Metadata”：

• 在 INFORMATION_SCHEMA.INNODB_TABLES.instant_cols 中记录 Instant 列数量
• 在表的 SDI（Serialized Dictionary Information） 中存储每个 Instant 列的：
  • 名称
  • 类型
  • 默认值
  • 是否可空

💡 这些信息只占几 KB，修改成本极低。

新写入的行怎么办？

• 新插入的行会包含新列的实际值（或显式默认值）
• 物理格式会更新，但只影响新行
• 旧行仍保持原格式，读取时动态补值

✅ 这就是“混合格式”共存，InnoDB 能正确解析。

为什么这么快？关键点总结

支持条件（必须全部满足）

示例

1-- ✅ 支持 Instant ADD
2ALTER TABLE order_items ADD COLUMN new_flag TINYINT NULL DEFAULT 0;
3
4-- ❌ 不支持 Instant（会触发全表重建）
5ALTER TABLE order_items ADD COLUMN new_flag TINYINT NOT NULL;        -- 无默认值
6ALTER TABLE order_items ADD COLUMN new_flag TINYINT AFTER user_id;  -- 插入中间
7

✅ 如果你的业务数据库已升级到 MySQL 8.0.12+，且满足上述条件，那么在 10 亿行大表上加字段，1 秒内即可完成！

方案二：使用 gh-ost（适用于 MySQL 5.6/5.7/8.0 但不满足 Instant 条件）

如果无法使用 Instant DDL（如仍在用 MySQL 5.7），强烈建议使用云厂商的“无锁结构变更”功能（如阿里云 DMS、腾讯云数据库管理），其底层正是基于 gh-ost。

gh-ost 原理详解：如何在业务持续写入下安全完成大表结构变更

gh-ost（GitHub Online Schema Change） 是 GitHub 开源的 MySQL 在线 DDL 工具，专为 10 亿级大表 设计，能在 业务系统持续高并发写入 的情况下，无锁、安全、可控地完成表结构变更。

一、核心思想

gh-ost 不依赖 MySQL 原生 DDL，而是通过 “应用层数据迁移 + binlog 实时同步” 的方式，将危险的 DDL 转化为一个 可中断、可限速、可监控的数据同步任务。

二、整体流程（含持续写入场景）

假设对 order_items 表执行：

1gh-ost --alter="ADD COLUMN new_flag TINYINT DEFAULT 0" ...
2

gh-ost 执行步骤如下：

1graph TD
2    A[1. 创建影子表 _order_items_gho] --> B[2. 应用新表结构]
3    B --> C[3. 全量分页拷贝原表数据]
4    C --> D[4. 实时监听 binlog]
5    D --> E[5. 将 DML 转换后重放到影子表]
6    E --> F[6. 持续追平延迟]
7    F --> G[7. 毫秒级原子切换]
8    G --> H[8. 清理临时表，任务完成]
9

三、关键机制详解

3.1 影子表（Ghost Table）

• 名称：_order_items_gho
• 创建方式：

1CREATE TABLE `_order_items_gho` LIKE `order_items`;  
2ALTER TABLE `_order_items_gho` ADD COLUMN new_flag TINYINT DEFAULT 0;  

• ✅ 不锁原表，瞬间完成

3.2 全量拷贝（Chunked Copy）

• 按主键分页，逐块拷贝：

1INSERT INTO _order_items_gho (id, order_id, ...)  
2SELECT id, order_id, ...  
3FROM order_items  
4WHERE id BETWEEN 1 AND 10000;  

• 可限速（如每秒 1000 行），避免打爆 I/O
• ⚠️ 拷贝期间，原表仍在被业务写入！

3.3 binlog 实时同步（核心！）

gh-ost 以 从库身份连接 MySQL，解析 ROW 格式 binlog，捕获所有对原表的 DML：

✅ 所有新写入的数据，实时同步到影子表，确保不丢数据

3.4 如何处理“拷贝中 + 写入”的冲突？

场景：全量拷贝进行到 id=5亿，此时业务更新了 id=100。

gh-ost 处理逻辑：

1. binlog 捕获到 UPDATE ... id=100
2. 立即重放到影子表（即使 id=100 还未被全量拷贝）
3. 后续全量拷贝到 id=100 时，会覆盖旧值
4. 但 binlog 重放会再次修正为最新值

🔒 通过“最终重放”机制，保证影子表 = 原表最新状态

3.5 最终切换（Cut-over）

当全量拷贝完成 + binlog 延迟 < 1 秒时，gh-ost 发起切换：

1. 短暂获取排他 MDL 锁（通常 < 100ms）
2. 执行原子操作：

1RENAME TABLE  
2  order_items TO _order_items_del,  
3  _order_items_gho TO order_items;  

3. 删除 _order_items_del

🎯 切换瞬间，所有新请求自动打到新结构表上，业务无感

四、为什么能保证“最终一致”？

• 原表始终是唯一真实数据源
• 影子表 = 全量快照 + 实时 binlog 重放
• 切换前确保影子表与原表几乎一致
• 切换是原子 RENAME，无中间状态

五、失败如何处理？

• 若 gh-ost 崩溃：
  • 原表 毫发无损
  • 影子表 _order_items_gho 可手动删除
  • 重新启动 gh-ost，支持 断点续传（resume）

六、与原生 DDL 对比优势

七、总结

gh-ost 的本质：用“数据迁移”绕过 MySQL DDL 的锁与重建问题。

即使系统 每秒写入 1 万条订单，只要 binlog 能跟上，gh-ost 就能：

• 不锁表
• 不丢数据
• 最终一致
• 安全完成 10 亿级大表变更

⚠️ 注意：gh-ost 虽然“无锁”，但仍需数小时完成数据迁移（取决于数据量和限速策略），只是不影响业务写入。它解决的是“可用性”问题，而非“速度”问题。

最终建议

技术在进步，曾经的“无奈之举”，如今已有优雅解法。

《为超过10亿条记录的订单表新增字段》 是转载文章，点击查看原文。