什么是 Online Schema Change？为什么直接 ALTER TABLE 在大表上会锁库？

考察要点

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

1. MySQL DDL 操作的底层原理：特别是 ALTER TABLE 在不同 MySQL 版本和存储引擎下的实现机制，以及它为什么会成为生产环境中的高危操作。
2. 锁机制与并发性能：理解 DDL 操作对线上读写请求（DML）的阻塞关系，以及这种阻塞对业务可用性的致命影响。
3. 大规模数据下的工程解决方案：是否具备处理大表（TB 级别）结构变更的实战经验，知道业界主流的 Online Schema Change (OSC) 工具及其原理。

核心概念

1. 为什么直接 ALTER TABLE 会锁库？

在生产环境中，尤其是在处理百万、千万甚至上亿行的大表时，直接执行 ALTER TABLE 语句通常是极其危险的。其根本原因在于 MySQL 处理 DDL 的机制，这在不同版本中有所演进。

a. 早期机制：Table-Copy (ALGORITHM=COPY)

在 MySQL 5.5 及更早版本，或者在某些 INPLACE 算法不支持的操作中，ALTER TABLE 的默认行为是“拷贝表”：

1. 创建临时表：根据新的表结构（例如，增加一个字段）创建一个新的临时表。
2. 逐行拷贝数据：将原表中的所有数据逐行拷贝到这个新的临时表中。在此期间，原表通常会被加上元数据读锁（Metadata Read Lock），允许 DML（INSERT, UPDATE, DELETE）操作，但这些变更不会同步到新表。
3. 应用变更：在拷贝完成后，MySQL 需要一个短暂的时间窗口来应用在拷贝期间发生的 DML 变更。
4. 原子重命名：在最后阶段，MySQL 需要获取一个排他的元数据写锁（Exclusive Metadata Write Lock），阻塞所有对该表的读写请求。然后，它会删除原表，并将临时表重命名为原表名。

锁库的关键点在于第 2 步和第 4 步。对于大表，第 2 步（数据拷贝）会消耗非常长的时间（几小时甚至几天），虽然期间允许 DML，但会产生大量 I/O 和 CPU 负载。而第 4 步的排他锁虽然短暂，但必须等待所有正在访问该表的事务结束后才能获取，一旦获取，所有新的请求都会被阻塞，直到重命名完成。如果数据拷贝时间过长，累积的 DML 变更也需要时间应用，最终导致锁表时间不可控，造成服务长时间不可用。

ASCII 图示：Table-Copy 过程

 1Original Table (T_old)            +-----------------+
 2  - DMLs are happening ->         |  New Table (T_new) with new schema  |
 3                                  +-----------------+
 4                                          ^
 5                                          |
 6Step 1: Copy all rows from T_old --------+ (Takes a long time)
 7
 8Step 2: Apply DMLs that occurred during copy (Replay)
 9
10Step 3: LOCK T_old, RENAME T_new to T_old, DROP T_old (Short but blocking)

b. 现代机制：Online DDL (ALGORITHM=INPLACE / INSTANT)

从 MySQL 5.6 开始，InnoDB 引入了 Online DDL，允许某些 ALTER TABLE 操作避免全量拷贝数据，通过 ALGORITHM=INPLACE 实现。

• ALGORITHM=INPLACE：操作在原表空间内完成，不创建临时表。例如，添加或删除二级索引。虽然避免了数据拷贝，但仍可能需要重建聚簇索引或修改表元数据，在某些阶段仍需要加锁。
• ALGORITHM=INSTANT (MySQL 8.0.12+): 这是一个巨大的进步。对于某些操作（如在表末尾添加列），它只修改数据字典中的元数据，几乎是瞬间完成，且不涉及数据文件重写，也无需加锁。

不同算法与锁级别的关系

结论：即使有了 INPLACE 和 INSTANT，也并非所有操作都能在线完成。像修改列类型这种需要重写所有数据的操作，依然会退化到 COPY 算法，导致长时间锁表。因此，依赖原生 Online DDL 仍有风险。

2. 什么是 Online Schema Change (OSC)？

Online Schema Change (OSC) 是一套外部工具和方法论，旨在实现在不阻塞或极少阻塞线上业务（DML）的情况下，完成对大表的结构变更。业界最主流的两个工具是 Percona 的 pt-online-schema-change 和 GitHub 的 gh-ost。

它们的核心思想与早期的 COPY 算法类似，但通过精巧的设计将对业务的影响降到最低。

OSC 工具通用工作流程 (以 pt-online-schema-change 为例)

1. 创建幽灵表 (Ghost Table)：创建一个与原表结构相同，但应用了新 schema 的空表 _new_table。
2. 创建触发器 (Triggers)：在原表上创建 INSERT, UPDATE, DELETE 三个触发器。这些触发器的作用是，在后续数据拷贝过程中，将所有对原表的 DML 操作实时同步到幽灵表。
3. 拷贝数据 (Chunk Copy)：以小批量（chunk）的方式，从原表中查询数据并插入到幽灵表中。每次拷贝一小批数据后会短暂 sleep，避免对主库造成过大 I/O 和 CPU 压力。
4. 数据追平 (Catch-up)：当所有存量数据拷贝完成后，持续应用触发器捕获的增量数据，直到幽灵表与原表数据基本一致。
5. 原子交换 (Atomic Swap)：这是最关键的一步。
   • 获取一个短暂的排他锁。
   • 执行 RENAME TABLE original_table TO _old_table, _new_table TO original_table。这个 RENAME 操作是原子的，在 MySQL 中执行极快。
   • 释放锁，并删除 _old_table 和触发器。

Mermaid 图示：OSC 流程

 1graph TD
 2    A[原表 Original_Table] -->|1. DML 写入| A
 3    subgraph "OSC 工具执行"
 4        B(创建幽灵表 New_Table)
 5        C(在原表上创建 Triggers)
 6        D(分块拷贝数据 Chunk Copy)
 7        E(数据追平 Catch-up)
 8        F(原子交换 RENAME)
 9    end
10    A -- 2. Triggers 捕获 DML --> C
11    C -- 3. DML 变更写入 --> B
12    A -- 4. 分块读取存量数据 --> D
13    D -- 5. 写入存量数据 --> B
14    E -- 6. 确保数据同步 --> F
15    F -- 7. 交换表名 --> G[新表成为主表]
16    F -- 8. 删除旧表和 Triggers --> H[完成]
17
18    style F fill:#f9f,stroke:#333,stroke-width:2px

通过这种方式，唯一的阻塞点就是最后原子交换的瞬间，通常在毫秒到秒级，对业务几乎无感知。

常见低分回答

1. 回答过于笼统：“ALTER TABLE 会锁表，所以要用 pt-online-schema-change。” —— 没有解释为什么会锁表（COPY 算法），也没有说明 OSC 工具的原理，缺乏深度。
2. 知识停留在旧版本：“ALTER TABLE 总是会拷贝全表数据，导致锁表。” —— 忽略了 MySQL 5.6+ 的 INPLACE 和 8.0+ 的 INSTANT 算法，表明知识体系没有更新。
3. 混淆原生 Online DDL 和 OSC 工具：“MySQL 8.0 的 Online DDL 已经很好了，不需要外部工具了。” —— 这是不准确的。虽然原生 Online DDL 进步很大，但对于修改列类型等重量级操作依然无力。此外，OSC 工具提供了更精细的负载控制（如限速、检查从库延迟），这是原生 DDL 不具备的。
4. 认为 OSC 完全不锁表：“gh-ost 可以做到完全无锁。” —— 错误。任何 OSC 工具在最后的 RENAME 阶段都需要一个短暂的排他锁来保证数据一致性。高分回答应明确指出这个锁的存在，并强调其时间极短。
5. 忽略 OSC 工具的成本：“直接用 pt-osc 就行了。” —— 没有提及 OSC 工具的缺点，如：触发器会带来额外的写开销；需要额外的磁盘空间存放幽灵表；整个过程会增加主库的 CPU 和 I/O 负载。

工程实践与优化

• 工具选择：

  • pt-online-schema-change：基于触发器，实现简单可靠，是大多数场景的首选。缺点是触发器在高并发写入场景下可能成为性能瓶颈。
  • gh-ost (GitHub Online Schema Migration)：不使用触发器，而是通过伪装成一个从库，解析主库的 binary log 来同步增量数据。对主库的侵入性更小，性能开销更低，尤其适合高并发写入的场景。但架构更复杂，依赖于 binlog。

• 安全执行：

  • --dry-run：执行前务必使用此参数进行演练。它会创建幽灵表、生成触发器 SQL，但不会真正执行，用于检查权限、表结构和潜在问题。
  • --execute：确认无误后，再使用此参数正式执行。
  • 负载控制：使用 --max-lag 控制从库延迟，当延迟超过阈值时自动暂停，防止主从同步问题。使用 --critical-load 设置服务器负载阈值（如 Threads_running），超过即暂停，保护主库。
  • 执行时机：选择业务低峰期执行，并使用 screen 或 tmux 等工具防止 SSH 会话中断。

• 执行计划分析： OSC 工具本身没有复杂的执行计划，但其内部的分块拷贝 SQL 值得关注。它通常是类似 SELECT ... FROM original_table WHERE id > ? AND id <= ? 的范围查询。确保这个查询能走上主键索引，否则会变成全表扫描，给数据库带来灾难。

• 监控： 在 OSC 执行期间，需要密切监控数据库的关键指标：CPU 使用率、I/O Wait、Threads_running、Threads_connected、从库延迟 (Seconds_Behind_Master)。

常见追问与延伸题目

1. 追问：如果一张表有 10TB，使用 OSC 工具需要注意什么？

   • 回答要点 1 (时间与空间)：执行时间会极长（可能数周），需要有充分的心理和资源准备。磁盘空间必须至少是原表大小的 1.5 倍以上（1 倍给幽灵表，0.5 倍给 binlog 和 undo log 增长）。
   • 回答要点 2 (风险管理)：长时间运行增加了失败的风险（如服务器重启、网络中断）。gh-ost 和 pt-osc 都支持断点续传，但仍需做好预案。可以考虑在独立的、专门用于 OSC 的从库上执行 gh-ost，变更完成后再将其与主库进行切换，这是对主库影响最小的方案。
   • 回答要点 3 (替代方案)：对于如此巨大的表，应首先考虑是否可以通过分区表来解决。如果能按时间或业务逻辑分区，未来的 DDL 可能只需在新的、较小的分区上进行，或者通过分区交换技术实现秒级 DDL。

2. 追问：如果 OSC 工具在执行过程中（比如数据拷贝到一半时）崩溃了，会发生什么？如何恢复？

   • 回答要点 1 (状态)：原表数据是完整且可用的，业务不受影响。会留下一个未完成的幽灵表 (_new_table) 和原表上的触发器（如果是 pt-osc）。
   • 回答要点 2 (恢复)：工具设计了恢复机制。pt-osc 会在幽灵表名中记录拷贝进度，重启后可以从断点继续。如果决定中止，需要手动清理：DROP TABLE _new_table; 和 DROP TRIGGER ...;。工具通常会提供清理指令。gh-ost 也是类似的，它会记录 binlog 的位点，可以从上次的位置继续同步。
   • 回答要点 3 (关键)：最重要的是，原表始终是“黄金副本”，直到最后 RENAME 的一刻。所以中间过程的失败是安全的，不会损坏数据。

3. 追问：PostgreSQL 在处理 Schema Change 时和 MySQL 有什么不同？也需要类似的 OSC 工具吗？

   • 回答要点 1 (架构差异)：PostgreSQL 的 DDL 具有事务性。大多数 ALTER TABLE 操作（如添加带 DEFAULT 值的列）在一个事务中完成，并且是非阻塞的。例如，在 PostgreSQL 11+ 中，ALTER TABLE ... ADD COLUMN ... DEFAULT 'value' 只是修改了系统目录，不会重写整个表，几乎是瞬间完成。
   • 回答要点 2 (锁机制)：PostgreSQL 的锁粒度更精细。它会请求一个 ACCESS EXCLUSIVE 锁，但通常只在事务提交的极短时间内持有，对并发操作的影响远小于 MySQL 的 COPY 算法。
   • 回答要点 3 (工具需求)：因此，在 PostgreSQL 中，对 OSC 工具的需求远没有 MySQL 那么迫切。只有在极少数需要重写表且无法接受短暂锁定的场景（如修改列类型），或者在非常老旧的 PG 版本上，才可能需要借助类似 pg_repack 这样的工具，其原理与 MySQL 的 OSC 工具相似。