在 MySQL 运维和面试中，“大表加字段、加索引”是一个高频且经典的场景。一张几千万甚至上亿行的表，直接执行 ALTER TABLE 往往意味着长时间锁表、主从延迟飙升，甚至业务不可用。因此，理解不同在线 DDL 方案的原理、适用场景和风险，是后端开发与 DBA 的必备技能。
本文将从面试题的角度出发，系统对比 MySQL 大表加字段、加索引的几种主流在线 DDL 方案，包括原生 Online DDL、pt-online-schema-change、gh-ost 以及 MySQL 8.0 的 Instant Add Column，并给出选型建议。
一、为什么大表 DDL 如此危险
在讨论方案之前，先明确问题的根源。MySQL 5.6 之前的 ALTER TABLE 几乎都是 Copy 方式：创建新表、逐行拷贝数据、重建索引，全程持有表锁，阻塞写操作。对于大表，这个过程可能持续数小时。
即使 MySQL 5.6 引入了 Online DDL，也并非所有操作都能真正做到“在线”。例如：
- 加字段：通常支持 In Place，但早期版本仍可能重建表。
- 加索引：一般支持 In Place，但需要扫描全表构建索引，期间仍可能短暂锁表。
- 修改字段类型：往往只能 Copy，锁表时间长。
因此，面试中常问：“一张 5000 万行的表要加一个字段和索引，你会怎么做？”这实际上是在考察你对在线 DDL 工具和原理的掌握。
二、方案一：MySQL 原生 Online DDL
2.1 原理
MySQL 5.6 及以上版本支持 Online DDL，通过 ALGORITHM=INPLACE 和 LOCK=NONE 尽量在不阻塞 DML 的情况下完成。其核心机制是：
- 在 DDL 执行期间，允许并发 DML 操作。
- 将 DML 产生的增量日志记录到 Online Log 中。
- DDL 完成后，应用增量日志，保证数据一致。
2.2 加字段
对于 ALTER TABLE t ADD COLUMN c INT，在 MySQL 5.7 中通常支持 ALGORITHM=INPLACE, LOCK=NONE，但依然需要重建表（因为行格式变化）。在 MySQL 8.0 中，如果使用 ALGORITHM=INSTANT，则只需修改数据字典，瞬间完成，不重建表。
-- MySQL 8.0 瞬间加字段
ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT;
2.3 加索引
ALTER TABLE t ADD INDEX idx_c (c), ALGORITHM=INPLACE, LOCK=NONE;
加二级索引通常支持 In Place 且不阻塞 DML，但需要扫描全表并排序，对 IO 和 CPU 压力较大。
2.4 优缺点
- 优点：无需额外工具，原生支持，操作简单。
- 缺点：大表加字段仍可能重建表，耗时久；加索引会消耗大量 IO；主从延迟难以避免；对业务高峰不友好。
三、方案二：pt-online-schema-change
3.1 原理
pt-online-schema-change（简称 pt-osc）是 Percona Toolkit 中的工具。其核心流程：
- 创建一张与原表结构相同的新表，并在新表上执行 DDL。
- 在原表上创建触发器，将 INSERT、UPDATE、DELETE 操作同步到新表。
- 分批拷贝原表数据到新表。
- 拷贝完成后，用 RENAME 交换表名。
3.2 使用示例
pt-online-schema-change \
--alter "ADD COLUMN c INT, ADD INDEX idx_c (c)" \
D=test,t=t \
--execute
3.3 优缺点
- 优点：不锁原表，支持加字段、加索引、修改字段等；可限速，降低对主库压力。
- 缺点：依赖触发器，有额外写入开销；触发器可能与业务逻辑冲突；外键场景受限；需要额外磁盘空间；RENAME 瞬间仍会短暂锁表。
四、方案三：gh-ost
4.1 原理
gh-ost 是 GitHub 开源的在线 DDL 工具，设计上摒弃了触发器。其流程：
- 创建新表并执行 DDL。
- 伪装成从库，读取原表的 binlog，将增量变更应用到新表。
- 分批拷贝原表数据。
- 通过 RENAME 交换表名。
4.2 使用示例
gh-ost \
--host=127.0.0.1 \
--database=test \
--table=t \
--alter="ADD COLUMN c INT, ADD INDEX idx_c (c)" \
--execute
4.3 优缺点
- 优点：无触发器，对原表侵入小；支持动态调整限速；可暂停、恢复；主从延迟可控。
- 缺点：需要 binlog 为 ROW 格式；需要额外磁盘空间；RENAME 瞬间锁表；学习成本略高。
五、方案四：MySQL 8.0 Instant Add Column
5.1 原理
MySQL 8.0.12 引入 Instant Add Column，8.0.29 进一步支持 Instant Add/Drop Column 任意位置。其核心是只修改数据字典中的元数据，不重建表、不拷贝数据。对于已有行，新增列的值通过默认值在读取时动态填充。
5.2 限制
- 只能加列，不能加索引。
- 新增列必须是默认值或允许 NULL。
- 不支持压缩表、临时表等部分场景。
- 如果表已有 INSTANT 列，再加列可能仍为 INSTANT，但有版本限制。
5.3 使用示例
ALTER TABLE t ADD COLUMN c INT DEFAULT 0, ALGORITHM=INSTANT;
5.4 优缺点
- 优点：秒级完成，几乎无锁，对主从延迟无影响。
- 缺点：仅限加字段，不能加索引；有版本和场景限制。
六、方案对比与选型建议
| 方案 | 加字段 | 加索引 | 锁表风险 | 主从延迟 | 额外空间 | 适用场景 |
|---|---|---|---|---|---|---|
| 原生 Online DDL | 支持，可能重建表 | 支持 | 低到中 | 可能较高 | 可能需 | 小表或低峰期 |
| pt-osc | 支持 | 支持 | 低 | 可控 | 需要 | 无外键、可接受触发器 |
| gh-ost | 支持 | 支持 | 低 | 可控 | 需要 | 大表、高并发、无触发器 |
| Instant Add Column | 支持 | 不支持 | 极低 | 无 | 不需要 | MySQL 8.0 加字段 |
选型建议
- 如果只是加字段且使用 MySQL 8.0，优先使用
ALGORITHM=INSTANT。 - 如果需要加索引或修改字段，且表非常大、业务不能停，优先选择 gh-ost。
- 如果环境不允许使用 gh-ost，可考虑 pt-osc，但需注意触发器和外键限制。
- 如果表不大，或可在低峰期操作，原生 Online DDL 足够。
- 无论哪种方案，都应先在从库或测试环境验证，并监控主从延迟。
七、面试常见追问
-
问：gh-ost 和 pt-osc 的核心区别是什么？
答：gh-ost 无触发器，通过 binlog 同步增量；pt-osc 依赖触发器。gh-ost 对原表侵入更小，但要求 ROW 格式 binlog。 -
问：Instant Add Column 为什么快？
答：只修改数据字典，不重建表、不拷贝数据，已有行读取时动态填充默认值。 -
问：加索引一定会锁表吗？
答：MySQL 5.6+ 加二级索引通常支持LOCK=NONE，但 DDL 开始和结束阶段仍可能短暂持有元数据锁。 -
问：如何降低主从延迟？
答：使用 gh-ost 或 pt-osc 的限速功能，分批拷贝，避开高峰，必要时先扩展从库。
八、总结
大表加字段、加索引没有“银弹”，需要根据 MySQL 版本、表大小、业务容忍度、运维工具链综合选择。MySQL 8.0 的 Instant Add Column 让加字段变得前所未有的轻量，但加索引仍需依赖 Online DDL 或第三方工具。掌握 pt-osc 和 gh-ost 的原理与差异，是应对面试和实际生产问题的关键。
在实际操作中，永远记住：先备份、先在从库验证、先限速、先监控。DDL 不是简单的 SQL 执行，而是一次需要精心策划的在线变更。
未经允许不得转载：任鹏个人博客 » MySQL 大表加字段、加索引的在线 DDL 方案对比
