在线 DDL（gh-ost / pt-osc）的工作原理？

考察要点

这道题面试官想看你是否理解：

1. 传统 ALTER TABLE 在生产环境中的巨大风险，即长时间的表锁导致服务中断。
2. 在线 DDL (Online Schema Change) 的核心思想，即“影子表拷贝”+“增量数据同步”机制。
3. 主流工具 pt-online-schema-change 和 gh-ost 在实现增量同步上的关键差异（触发器 vs. Binlog），以及各自的优缺点和适用场景。

核心概念

在线 DDL 的核心目标是在不阻塞（或极短时间阻塞）线上业务写入的情况下，完成对大表的结构变更（Schema Change）。传统 ALTER TABLE 会对表加上元数据锁（Metadata Lock），在数据量大时，可能导致整个服务在数小时内无法对该表进行写操作，这是不可接受的。

为了解决这个问题，社区开发了以 pt-online-schema-change (简称 pt-osc) 和 gh-ost 为代表的工具，它们都遵循“影子表”的核心思想。

核心流程（以 ALTER TABLE t1 ADD COLUMN c2 INT 为例）：

1. 创建影子表 (Ghost Table)：根据原表 t1 的结构，创建一个新的空表 _t1_new，并在这个新表上执行 DDL 语句（如 ADD COLUMN c2 INT）。
2. 全量数据拷贝：将原表 t1 的数据分块（chunk by chunk）拷贝到影子表 _t1_new 中。
3. 增量数据同步：在拷贝过程中，线上业务仍在向原表 t1 写入数据。必须捕获这些增量变更（INSERT/UPDATE/DELETE），并应用到影子表 _t1_new 中，以保证最终数据一致。这是不同工具实现原理的核心差异点。
4. 最终切换 (Cut-over)：当全量数据拷贝完成，且增量数据基本追平后，通过一个原子性的 RENAME TABLE 操作将原表和影子表的名字对调。
   sql

   ⧉⛶

    1RENAME TABLE `t1` TO `_t1_old`, `_t1_new` TO `t1`;

5. 清理工作：删除旧表 _t1_old。

pt-online-schema-change (from Percona Toolkit)

• 增量同步机制：触发器 (Triggers)
• 工作原理：
  1. 在原表 t1 上创建 INSERT, UPDATE, DELETE 三个触发器。
  2. 当客户端对 t1 进行写操作时，这些触发器会被激活。
  3. 触发器会将相应的变更写入到影子表 _t1_new 中。例如，ON t1 AFTER INSERT 的触发器会向 _t1_new 中 INSERT 同样的数据。
• 图示:
  text

  ⧉⛶

   1+--------+     DML     +--------------+      (同步执行)      +----------------+
   2| Client | --------> | Original Table | ----------------> |     Trigger    |
   3+--------+           +--------------+                     +----------------+
   4                                                                 |
   5                                                                 | (写入变更)
   6                                                                 v
   7                                                          +-------------+
   8                                                          | Ghost Table |
   9                                                          +-------------+

• 优点：
  • 实现相对简单，不依赖于数据库的 Binlog 配置。
  • 数据同步逻辑封装在数据库内部，较为可靠。
• 缺点：
  • 性能开销大：触发器是在原始 DML 事务中同步执行的，会增加原表写操作的响应时间，对主库压力较大。
  • 侵入性强：需要在生产表上创建和删除触发器，如果原表已有触发器，则无法使用。
  • 对主从延迟敏感：若在从库执行，主从延迟可能导致数据不一致。

gh-ost (from GitHub)

• 增量同步机制：二进制日志 (Binary Log)
• 工作原理：
  1. gh-ost 将自己伪装成一个 MySQL 从库，连接到主库（或指定的上游服务器）。
  2. 它监听主库的 Binlog 事件流。
  3. 解析 Binlog，筛选出只针对原表 t1 的行变更事件（Row-based events）。
  4. 将这些变更事件在 gh-ost 进程中转换为对影子表 _t1_new 的 DML 操作并执行。
• 图示:
  text

  ⧉⛶

   1+--------+     DML     +--------------+
   2| Client | --------> | Original Table |
   3+--------+           +--------------+
   4                             |
   5                             | (写入 Binlog)
   6                             v
   7+----------------+     (异步读取)      +--------+     (应用变更)      +-------------+
   8|   Binary Log   | <---------------- | gh-ost | ----------------> | Ghost Table |
   9+----------------+                   +--------+                   +-------------+

• 优点：
  • 对主库性能影响小：增量同步是异步读取 Binlog 完成的，不会增加线上写事务的负担。
  • 非侵入性：不需要在原表上创建触发器。
  • 更强的控制和安全性：可以随时暂停、节流（根据主库负载或从库延迟），甚至可以在从库上执行变更，对主库影响降到最低。
• 缺点：
  • 环境要求高：
    • MySQL/MariaDB 必须开启 Binlog。
    • binlog_format 必须为 ROW。
    • binlog_row_image 必须为 FULL (或 NOBLOB，取决于表结构)。
  • 架构更复杂：引入了一个外部进程，需要管理其连接、权限和状态。

原生 Online DDL (MySQL 5.6+)

MySQL 自身也在不断增强原生 DDL 的能力。通过 ALGORITHM=INPLACE, LOCK=NONE 语法，许多 DDL 操作（如添加索引、添加允许 NULL 的列）可以实现真正的在线操作，不拷贝数据，性能极高。

 1ALTER TABLE t1 ADD INDEX idx_c1(c1), ALGORITHM=INPLACE, LOCK=NONE;

边界条件：并非所有 DDL 都支持 INPLACE 和 LOCK=NONE。例如，修改列类型、改变列顺序、将列改为 NOT NULL 等通常仍需要拷贝表数据，此时 pt-osc 或 gh-ost 依然是必要的。

对比总结

常见低分回答

1. 混淆 pt-osc 和 gh-ost 的原理：“gh-ost 是通过触发器来同步数据的”，这是最致命的错误，直接表明对核心原理理解不清。
2. 只说“拷贝表”，不提增量同步：“在线 DDL 就是建个新表，把数据拷过去，然后改名”。这个回答遗漏了最关键的一环：在拷贝期间，如何处理线上新的写入？这正是 Online DDL 的技术核心。
3. 忽略原生 Online DDL：完全不提 MySQL 5.6+ 的 ALGORITHM=INPLACE, LOCK=NONE，表明知识体系陈旧，不知道优先选择成本最低、风险最小的官方方案。
4. 对缺点和风险一无所知：只说优点，不提风险，如 pt-osc 的性能损耗、gh-ost 的环境要求、以及两者共同面对的外键和磁盘空间问题。这会让面试官觉得你缺乏生产实践经验。
5. 认为 RENAME 过程绝对无阻塞：声称 RENAME TABLE 是“绝对的零停机”，忽略了它需要获取元数据锁，可能会被长事务阻塞，导致应用请求短暂堆积。

工程实践与优化

• 工具选择：首选原生 Online DDL (ALGORITHM=INPLACE, LOCK=NONE)。如果不支持，对于高并发、核心业务，强烈推荐 gh-ost，因为它对主库影响最小。pt-osc 可作为备选或用于无法满足 gh-ost 环境要求的场景。
• 安全第一：节流与监控：
  • gh-ost 和 pt-osc 都支持节流（throttling）机制。务必配置 --max-lag 来监控并控制主从延迟，以及 --critical-load 来监控主库负载（如 Threads_running），当超过阈值时自动暂停，避免压垮数据库。
  • 执行期间，需持续监控主库 CPU、IO、连接数以及主从延迟。
• 外键（Foreign Key）的坑：外键是 Online DDL 的一大难题。gh-ost 不支持有外键的表，pt-osc 对外键的处理也极其复杂且风险高。通常的最佳实践是：在 DDL 前手动禁用外键约束，DDL 完成后在新表上重新创建并启用。这个过程需要业务侧的评估和配合。
• 磁盘空间：执行 Online DDL 需要额外的磁盘空间来存放影子表，空间大小约等于原表大小。在执行前必须检查磁盘余量。
• 执行时机：选择业务低峰期执行，即使工具对性能影响可控，也应保留安全余地。
• gh-ost 的交互式特性：gh-ost 可以在运行时通过 echo 'throttle' | socat - /tmp/gh-ost.sock 等命令进行交互，实现动态节流、暂停、恢复，非常适合人工值守下的精细化操作。

常见追问与延伸题目

1. 追问：最后的 RENAME TABLE 切换是完全无锁、零停机的吗？

   • 回答要点：RENAME TABLE t1 TO _t1_old, _t1_new TO t1 这个操作本身是原子的。但它需要获取表上的元数据锁（MDL）。如果在执行 RENAME 的瞬间，有长事务或长查询正在访问 t1 表，RENAME 操作就会被阻塞，直到那个长事务/查询结束。在 RENAME 阻塞期间，后续所有对 t1 表的查询和DML都会被阻塞，从而导致应用层出现短暂的请求堆积或超时。所以，它并非绝对的“零停机”，而是“近乎零停机”，切换窗口通常在毫秒到秒级，但有被长事务放大的风险。

2. 追问：为什么 gh-ost 强制要求 binlog_format=ROW？用 STATEMENT 格式不行吗？

   • 回答要点：不行。STATEMENT 格式记录的是原始的 SQL 语句，它存在不确定性。例如 UPDATE t1 SET ts=NOW() WHERE id > 100，在主库和从库（或 gh-ost）上执行时，NOW() 的值会不同。gh-ost 作为一个“伪装的从库”，必须精确地复制主库上发生的数据行的变更，而不是重新执行可能产生不同结果的语句。ROW 格式的 Binlog 记录了每一行数据变更前后的镜像（image），gh-ost 读取这些行镜像，就能在影子表上精确地重放数据变更，从而保证最终数据的一致性。

3. 追问：如果我的表上已经有触发器了，我还能用这些工具吗？

   • 回答要点：
     • 对于 pt-osc：不能。pt-osc 的工作原理本身就是依赖于创建自己的触发器。如果表上已有触发器，pt-osc 会因为无法创建同名同事件的触发器而报错并退出。这是 pt-osc 的一个核心限制。
     • 对于 gh-ost：可以。gh-ost 的工作原理是读取 Binlog，完全不关心表上是否有触发器。触发器产生的变更会和原始 DML 一起被写入 Binlog，gh-ost 会忠实地将这些最终的行变更应用到影子表。因此，gh-ost 与现有触发器是兼容的，这也是它相比 pt-osc 的一大优势。