在 MySQL 运维和开发面试中，“如何在线修改大表结构” 几乎是一道必考题。MySQL 5.6 引入 Online DDL 后，很多原本需要锁表的操作变得“看似”可以在线完成；而 Percona Toolkit 中的 pt-online-schema-change（简称 pt-osc）则一直是社区里最常用的在线改表工具。两者到底有什么区别？面试时又该如何回答？这篇文章从原理、适用场景、限制和选型几个角度做一个系统对比。
一、为什么需要在线 DDL
在 MySQL 5.5 及更早版本中，执行 ALTER TABLE 基本意味着：
- 创建一张新的临时表；
- 把原表数据逐行复制到临时表；
- 整个过程中对原表加写锁，阻塞所有 DML。
对于几千万甚至上亿行的大表，这意味着数小时甚至数天的业务不可写，显然无法接受。于是出现了两类解决方案：
- MySQL 原生 Online DDL：由 InnoDB 存储引擎和 Server 层配合，在 ALTER 期间尽量允许并发 DML。
- 外部工具方案：以 pt-osc 为代表，通过“影子表 + 触发器 + 分批拷贝”的方式模拟在线改表。
二、MySQL Online DDL 原理
Online DDL 的核心是把 ALTER 操作拆成三个阶段：
- Prepare 阶段：持有 MDL 写锁，检查表结构、准备新表定义或临时文件，时间很短。
- Execute 阶段：真正执行数据变更。根据操作类型不同，可能允许并发 DML。
- Commit 阶段：再次持有 MDL 写锁，提交变更、更新数据字典，时间同样很短。
Online DDL 支持的程度取决于具体操作，主要分三档：
- INPLACE + 允许并发 DML：如添加二级索引、修改索引、设置默认值等。执行期间不阻塞读写。
- INPLACE + 仅允许并发查询：如添加全文索引、空间索引等。
- COPY + 阻塞 DML：如修改列类型、修改字符集、添加主键等，仍需重建表。
可以通过 ALGORITHM=INPLACE, LOCK=NONE 显式指定，如果 MySQL 无法满足就会直接报错，而不是悄悄降级，这对生产环境非常重要。
Online DDL 的优点
- 原生支持，无需额外工具，一条 SQL 即可完成。
- 不依赖触发器，对主从复制、外键、触发器等场景更友好。
- 性能通常优于 pt-osc，因为它直接操作 InnoDB 内部结构，不产生额外的触发器开销。
Online DDL 的局限
- 并非所有操作都支持，很多变更仍会锁表或重建表。
- 需要持有 MDL 锁，如果长事务未提交，Prepare 阶段会阻塞，进而阻塞后续所有请求，这是生产事故的常见原因。
- 对大表仍可能产生较大 IO 和主从延迟，尤其是 COPY 模式下。
- 元数据锁等待难以控制，没有内置的“暂停/限速”机制。
三、pt-online-schema-change 原理
pt-osc 的思路非常直观：
- 创建一张与原表结构相同的新表
_table_new； - 在新表上执行 ALTER；
- 在原表上创建三个触发器（INSERT、UPDATE、DELETE），把变更同步到新表；
- 分批（chunk）把原表数据拷贝到新表；
- 拷贝完成后，用
RENAME TABLE原子地把新表替换为原表； - 删除触发器和旧表。
整个过程原表始终可读写，只有最后 RENAME 的瞬间需要短暂的元数据锁。
pt-osc 的优点
- 几乎支持所有 ALTER 操作，不受 Online DDL 支持矩阵限制。
- 可控性强：可以指定 chunk 大小、限速、暂停、检查主从延迟、检查外键等。
- 对主从复制友好：分批拷贝降低单条大事务带来的延迟。
- 失败可回滚：中途失败只需删除新表和触发器，原表不受影响。
pt-osc 的局限
- 依赖触发器：触发器本身有性能开销，且如果原表已有触发器会冲突（需
--preserve-triggers）。 - 需要主键或唯一索引：否则无法分批拷贝，只能一次性拷贝，失去意义。
- 外键限制：涉及外键的表需要额外处理，可能阻塞或失败。
- 磁盘空间翻倍：需要同时容纳原表和新表。
- RENAME 瞬间仍需 MDL 锁，长事务同样会导致等待。
- 触发器同步存在延迟，如果拷贝时间过长，触发器积累的变更可能成为瓶颈。
四、核心对比
| 维度 | Online DDL | pt-online-schema-change |
|---|---|---|
| 实现方式 | InnoDB 原生 | 影子表 + 触发器 + 分批拷贝 |
| 支持范围 | 部分操作 | 几乎全部 |
| 并发 DML | 视操作而定 | 全程支持 |
| 额外开销 | 较低 | 触发器 + 双倍写入 |
| 磁盘空间 | 部分操作需要 | 始终需要双倍 |
| 主从延迟 | 可能较大 | 可控、分批 |
| 限速/暂停 | 不支持 | 支持 |
| 外键/触发器 | 原生支持 | 受限 |
| 失败回滚 | 自动 | 需手动清理 |
| 使用复杂度 | 一条 SQL | 需安装工具、参数调优 |
五、面试常见追问
Q1：Online DDL 一定不锁表吗？
不是。Online DDL 只在 Prepare 和 Commit 阶段持有 MDL 写锁，Execute 阶段是否锁表取决于操作类型。像修改列类型这类操作仍然是 COPY 模式，全程阻塞 DML。
Q2：为什么 Online DDL 会被长事务阻塞？
因为 Prepare 阶段需要获取 MDL 写锁，而 MDL 写锁与已有的事务持有的 MDL 读锁互斥。如果有一个长事务一直未提交，ALTER 就会等待，后续所有请求也会排队，形成“锁等待雪崩”。
Q3：pt-osc 为什么要求有主键？
分批拷贝需要根据主键或唯一索引切分 chunk，否则无法定位数据边界，只能全表一次性拷贝，失去在线改表的意义。
Q4：如何选择？
- 如果操作被 Online DDL 支持且允许并发 DML，优先用 Online DDL，简单、原生、开销小。
- 如果操作不被支持（如修改列类型、修改字符集），或者需要限速、暂停、精细控制主从延迟，选 pt-osc。
- 在 MySQL 8.0 中，很多原本需要 pt-osc 的场景已被 Online DDL 覆盖，但 pt-osc 在可控性上仍有优势。
六、总结
Online DDL 和 pt-osc 并不是互相替代的关系，而是互补的两种方案。Online DDL 胜在原生、轻量、无触发器开销，但受限于支持矩阵和 MDL 锁；pt-osc 胜在通用、可控、可限速，但引入触发器开销和双倍磁盘占用。
面试时如果能说清楚 “Online DDL 的 INPLACE/COPY 区别”“MDL 锁导致的事故场景”“pt-osc 的触发器和主键依赖” 这三点，基本就能体现对在线改表的深入理解。实际生产中，建议结合 MySQL 版本、表大小、业务容忍度和主从架构综合选型，必要时还可以考虑 gh-ost 等更现代的替代方案。
未经允许不得转载：任鹏个人博客 » MySQL 中 online DDL 与 pt-online-schema-change 对比
