如果一张大表需要在线添加索引/列，应该如何设计来避免长时间锁表（pt-online-schema-change / gh-ost 等）？

考察要点

这道题考察的是候选人对数据库变更管理（Schema Change）的深度理解，尤其是在大规模、高并发生产环境下的实践能力。面试官想看你是否懂：

1. MySQL DDL 的锁机制：是否清楚 ALTER TABLE 在不同版本、不同操作下的锁行为（共享锁、排他锁、元数据锁）。
2. 在线与离线变更的差异：是否理解原生 Online DDL 的原理、优势与局限性。
3. 高级工具的实现原理：是否掌握 pt-online-schema-change 和 gh-ost 等业界主流工具的核心思想（如“影子表+触发器”或“影子表+Binlog”）。

核心概念

要实现在线无锁变更，核心是避免对原表进行长时间的独占锁定。主流方案分为两类：MySQL 原生 Online DDL 和基于“复制-交换”思想的外部工具。

1. MySQL 原生 Online DDL

从 MySQL 5.6 开始，InnoDB 引擎引入了 Online DDL，允许在执行某些 DDL 操作时，并发执行 DML（INSERT, UPDATE, DELETE）。

• 语法：通过 ALGORITHM 和 LOCK 子句控制。
  sql

  ⧉⛶

   1ALTER TABLE your_table ADD INDEX idx_name (column_name), ALGORITHM=INPLACE, LOCK=NONE;

• ALGORITHM 选项:
  • COPY: 老方法。创建临时表，拷贝数据，然后替换原表。全程锁表，性能最差。
  • INPLACE: 优选。不创建临时表，在原表基础上修改。根据操作不同，可能需要重建表，但能减少锁的持有时间。
  • INSTANT (MySQL 8.0+): 极速。只修改数据字典中的元数据，不涉及表数据。例如，在表末尾添加列。
• LOCK 选项:
  • NONE: 允许并发读写。
  • SHARED: 允许并发读，阻塞写。
  • EXCLUSIVE: 阻塞所有读写。

版本与操作支持情况（以 InnoDB 为例）

注意：即使是 LOCK=NONE 的 Online DDL，在操作开始和结束时仍需要短暂的元数据锁（Metadata Lock）来更新表结构定义，在高并发下可能导致等待。此外，INPLACE 的重建表过程会消耗大量 I/O 和 CPU，可能引发主从延迟。

2. 外部工具原理：“复制-交换”（Copy-and-Swap）

当原生 Online DDL 无法满足需求（如版本太低、操作不支持、对性能影响不可控）时，使用 pt-online-schema-change 或 gh-ost 等工具。它们的核心思想一致：

1. 创建影子表（Ghost Table）：基于原表结构创建一个新的空表，并在这个新表上执行所需的 DDL 变更。
2. 同步增量数据：在原表上创建触发器（pt-osc）或监听 Binlog（gh-ost），将 DDL 期间原表的增量数据（INSERT/UPDATE/DELETE）同步到影子表。
3. 拷贝存量数据：分批次（chunk-by-chunk）将原表中的数据拷贝到影子表。这个过程是可控的，可以根据服务器负载自动调节速率。
4. 原子交换：当存量数据拷贝完成，且影子表通过增量同步追上原表后，执行一个原子性的 RENAME TABLE 操作，用影子表替换原表。这是一个极短的锁定过程。
5. 清理：删除旧表和触发器等辅助对象。

工具对比

ASCII 图示：gh-ost 工作原理

 1+-----------+       +-----------+       +-------------+
 2|           |------>|           |------>|             |
 3|  Master   |       |  Replica  |       |   gh-ost    |
 4| (Binlog)  |       | (Reads    |       |  (Applies   |
 5|           |<------|  Binlog)  |       |   Changes)  |
 6+-----------+       +-----------+       +-------------+
 7     ^   |                                    |
 8     |   | 1. Writes to original_table        | 3. Applies changes
 9     |   |    (generates binlog)              |    to _ghost_table
10     |   +------------------------------------+
11     |
12     | 2. Copies chunks from original_table
13     +----------------------------------------+

常见低分回答

• “直接 ALTER TABLE 就行了”：完全没有意识到大表 DDL 的锁问题和性能影响，是初级开发者的典型回答。
• “在业务低峰期做，比如半夜”：对于 24/7 全球化服务，不存在绝对的低峰期。而且大表 DDL 可能持续数小时甚至数天，无法在一个“窗口期”内完成。
• “MySQL 8.0 都能在线加列，不需要外部工具了”：回答片面。只知道 ALGORITHM=INSTANT 的优点，却不知道它仅适用于“在表末尾添加列”等少数场景，对于修改类型、修改顺序等重量级操作依然无能为力。
• 混淆 pt-online-schema-change 和 gh-ost 的原理：例如，说 gh-ost 使用触发器，或者说 pt-osc 读取 Binlog。这表明对工具的理解停留在表面。
• 认为 Online DDL 或外部工具完全无影响：忽略了元数据锁、I/O 与 CPU 消耗、主从延迟等潜在风险，缺乏生产环境的敬畏心。

工程实践与优化

• 前置检查：执行任何 DDL 前，必须检查：
  • 磁盘空间：影子表会占用与原表相当的空间。
  • 主从延迟：SHOW SLAVE STATUS 确保延迟在可接受范围（如 1s 内）。
  • 外键：外键是 Online DDL 的噩梦。pt-osc 和 gh-ost 对外键的处理都有限制。最佳实践是：先删除外键 -> 执行 schema change -> 再重新创建外键。
• 负载控制：精细化控制工具的运行负载是关键。
  • 使用 pt-osc 的 --max-lag 选项，当主从延迟超过阈值时自动暂停。
  • 使用 --chunk-time 控制每个数据块的拷贝时间，避免单次 I/O 过高。
  • gh-ost 提供了更灵活的节流 (--max-load) 机制，可以基于 Threads_running 等状态变量进行控制。
• 执行计划与监控：
  • SHOW PROCESSLIST：在原生 Online DDL 期间，可以看到 State 为 altering table 或 copy to tmp table 的进程。
  • Performance Schema：MySQL 8.0 中，可以通过 performance_schema.events_stages_current 表来监控 DDL 的进度。
  • 工具日志：pt-osc 和 gh-ost 都会打印详细的执行日志，包括拷贝进度、延迟监控等，是排查问题的首要信息源。
• 紧急预案：
  • gh-ost 的“幽灵船”：gh-ost 可以在不执行最终 RENAME 的情况下运行（--test-on-replica），验证整个流程的正确性。
  • 中断与恢复：工具被中断后，会留下影子表 (_your_table_new) 和触发器。可以手动清理，也可以让工具尝试从断点恢复。原表数据始终是安全的。

常见追问与延伸题目

1. 追问：gh-ost 相比 pt-online-schema-change 的核心优势是什么？为什么大厂更倾向于 gh-ost？

   • 回答要点 1 (核心优势 - 性能)：gh-ost 的最大优势是无触发器。它通过监听从库的 Binlog 来获取增量更新，避免了在主库上执行触发器带来的额外开销。对于写密集型的核心业务表，触发器会显著增加主库的 CPU 负担和事务响应时间，而 gh-ost 则将这部分压力转移。
   • 回答要点 2 (架构优势 - 可控性与安全性)：gh-ost 的架构更灵活、更安全。它允许暂停、动态调整速率、交互式命令控制，并且可以在从库上进行演练 (--test-on-replica)。这种非侵入式、可审计的特性，在对稳定性要求极高的金融、电商等核心系统中至关重要。

2. 追问：如果变更过程中，主库发生故障切换（HA Switchover），gh-ost 会发生什么？如何处理？

   • 回答要点 1 (故障场景)：gh-ost 正在运行时，如果主库发生故障切换，新的主库并不知道 gh-ost 的存在。gh-ost 会因为连接中断或无法写入而失败。此时，原表依然是旧结构，数据是完整的，但会留下一个未完成的影子表 (_your_table_new) 在旧主库（现在的新从库）上。
   • 回答要点 2 (处理方案)：首先，业务不受影响，因为原表结构未变。恢复后，需要手动介入。最佳做法是中止并清理。连接到新的主库，找到并删除遗留的影子表。然后，在一个新的、稳定的主从拓扑下，重新发起一次 gh-ost 任务。尝试在故障切换后“恢复”一个旧的 gh-ost 任务是极其复杂且风险很高的，不推荐。

3. 追问：MySQL 8.0 的 ALGORITHM=INSTANT 如此高效，是否意味着未来不再需要 gh-ost 这类工具了？

   • 回答要点 1 (INSTANT 的局限性)：ALGORITHM=INSTANT 是一个革命性的功能，但它的应用场景非常有限。它主要支持：在表末尾添加列、添加/删除虚拟列、设置/删除列的默认值、修改 ENUM/SET 的定义。对于修改列数据类型、改变列顺序、添加主键、将普通列改为 NOT NULL 等需要重写数据的操作，INSTANT 无能为力，仍然需要 INPLACE（重建表）甚至 COPY 算法。
   • 回答要点 2 (工具的不可替代性)：只要存在需要“重建表”的 DDL 需求，gh-ost 等工具的价值就依然存在。它们提供了原生 DDL 无法比拟的负载控制能力和操作安全性。在 TB 级别的大表上，即使是 ALGORITHM=INPLACE, LOCK=NONE 的原生 DDL，其产生的 I/O 风暴和主从延迟也可能是生产环境无法接受的。因此，gh-ost 仍然是大规模数据库运维的瑞士军刀。