大表 DDL 方案：Online DDL 与 gh-ost 原理

在 500GB 的表上 ALTER TABLE 加字段，会发生什么？某电商平台曾因此造成主库锁表 15 分钟、主从延迟 2 小时，最终靠 gh-ost 在 3 小时内完成迁移且延迟控制在 5 秒以内。本文从 Online DDL 的底层机制讲到 gh-ost 的 binlog 同步原理，给出大表 DDL 的选型决策。

大表 DDL 的核心风险

MySQL 中 DDL 操作（ALTER TABLE、CREATE INDEX 等）对生产系统的威胁集中在三个维度：

• 锁表阻塞读写：MySQL 5.6 之前的 DDL 会对整表加 MDL 排他锁，期间所有 DML 被阻塞。5.6+ 引入 Online DDL 后，部分操作可以做到不锁表，但仍有隐形的锁等待风险。
• 磁盘 IO 和空间翻倍：ALGORITHM=COPY 模式会复制整张表到临时文件，磁盘占用临时翻倍。500GB 的表需要额外 500GB 空闲空间。
• 主从延迟：DDL 在主库执行完成后，会在从库上回放。如果 DDL 耗时很长，从库的 SQL 线程（单线程）会阻塞在回放上，导致主从延迟飙升。

Online DDL 的三种算法

MySQL 5.6+ 的 DDL 支持三种算法，由 ALGORITHM 子句指定：

ALTER TABLE orders ADD COLUMN `discount` DECIMAL(10,2) DEFAULT 0.00,
    ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=COPY

最古老的方式，本质是建新表 → 拷贝数据 → 删旧表 → 重命名。整个过程中原表被 MDL 排他锁保护，无法写入。适用场景：修改列类型、DROP PRIMARY KEY、重排列顺序等需要重建表结构的操作。

ALGORITHM=INPLACE

不在磁盘上创建完整副本，而是直接在原表的数据页上进行修改。内部三个阶段：

1. Prepare：分配临时日志文件，加 MDL 共享锁（允许读，阻塞 DDL 和 DML 的元数据变更），时间极短（毫秒级）。
2. Execute：执行 DDL 操作，同时记录并发的 DML 变更到临时日志中。这个阶段不锁表，DML 可以正常执行。但如果是 INPLACE 但 LOCK=SHARED，则写入操作会被阻塞，只有读可以。
3. Commit：应用临时日志中的增量变更，加 MDL 排他锁（STW 极短，毫秒级），完成切换。

INPLACE 的适用条件：只有某些 DDL 操作支持 INPLACE。比如加索引（CREATE INDEX）、加字段（ADD COLUMN，且不涉及列顺序重排）、DROP INDEX 等。而修改列类型、DROP PRIMARY KEY 等操作强制走 COPY。

ALGORITHM=INSTANT（MySQL 8.0.12+）

MySQL 8.0 引入的"即时"模式，只在元数据层面做变更，不修改数据文件。当前支持的操作为：ADD COLUMN（非 NOT NULL 且无默认值 DEFAULT 的列）和 DROP COLUMN（与 ADD COLUMN 配合使用的一些场景）。INSTANT 模式几乎零开销，但有限制——每个表最多支持 64 次 INSTANT ADD COLUMN 操作。

Online DDL 的坑

Online DDL 不是银弹，生产环境有几个常见陷坑：

1. 临时日志撑爆磁盘：INPLACE 模式下，并发 DML 产生的变更会写入临时日志（存放于 tmpdir）。如果 ALTER TABLE 执行时间长且写入量大，日志文件可能撑爆磁盘。
2. MDL 锁等待：当有长事务未提交时，DDL 的 Prepare 阶段拿不到 MDL 共享锁，会阻塞后续所有查询。经典场景：一个 SELECT 长查询在跑，ALTER TABLE 被阻塞，阻塞后续所有读写。
3. 主从延迟：INPLACE 在主库上可能很快，但在从库上回放时仍然是单线程的。

gh-ost：GitHub 的无触发器 DDL 方案

gh-ost（GitHub Online Schema Migration）是 GitHub 开源的 DDL 工具，核心思路是不依赖 MySQL 内部的 Online DDL 机制，而是通过 Binlog 同步来模拟增量迁移。

工作流程

1. 创建影子表 _orders_gho（与 orders 结构相同，加上目标变更）。
2. 从原表拷贝历史数据到影子表：INSERT INTO _orders_gho SELECT * FROM orders，分块拷贝（默认每块 1000 行），通过 --chunk-size 控制。
3. 启动 Binlog 监听器，解析原表的 Row-Based Binlog 事件，实时应用到影子表。
4. 数据拷贝完成后，执行原子切换：RENAME TABLE orders TO _orders_del, _orders_gho TO orders。

流量控制

gh-ost 通过 --max-load 和 --critical-load 参数监控主库负载：

# 典型生产命令
gh-ost \
  --host=127.0.0.1 \
  --database=production \
  --table=orders \
  --alter="ADD COLUMN discount DECIMAL(10,2) DEFAULT 0.00" \
  --max-load=Threads_running=30 \
  --critical-load=Threads_running=50 \
  --chunk-size=1000 \
  --dml-batch-size=10 \
  --default-retries=3 \
  --execute

当 Threads_running 超过 30 时，gh-ost 自动降低拷贝速度；超过 50 时直接暂停。这种机制保证了 DDL 操作不会压垮主库。

gh-ost 的限制

• 必须 binlog_format=ROW：基于 Row-Based Binlog 解析，Statement 格式不支持。
• 不支持外键和触发器：自动检测到有外键或触发器的表会退出（--allow-on-master 可绕过，但不推荐）。
• 需要 SUPER 权限：创建 Binlog 解析器需要 SUPER 或 REPLICATION SLAVE、REPLICATION CLIENT 权限。
• 切表阶段可能短暂阻塞：RENAME 操作本身是原子操作，但如果存在长事务，RENAME 会等待 MDL 锁，导致毫秒级到秒级的阻塞。

选型决策：Online DDL vs gh-ost

维度	Online DDL	gh-ost
适用表大小	< 100GB	> 100GB
锁表风险	有（MDL 排队）	极低（Binlog 同步）
对主库压力	中（临时日志 + IO）	低（可控 chunk 大小）
主从延迟	可能高	低（可控延迟）
部署复杂度	内置，无需额外工具	需安装，配置参数较多
支持的操作	有限（INPLACE/INSTANT 子集）	几乎所有 DDL
外键/触发器	无限制	不支持

简单决策规则：

• 小表（< 100GB）+ 简单 DDL（加索引、加字段）→ 直接 Online DDL，ALGORITHM=INPLACE, LOCK=NONE
• 大表（> 100GB）或无法容忍任何锁表风险 → gh-ost
• 修改列类型、DROP PRIMARY KEY 等强制 COPY 的操作 → gh-ost 优先
• 有外键/触发器的表 → 只能用 Online DDL（或先移除外键）

生产案例：一个 500GB 表的 DDL 事故

某电商平台需要在 500GB 的 order_item 表上加一个索引 (status, create_time)。DBA 直接在从库上执行 ALTER TABLE order_item ADD INDEX idx_status_time (status, create_time)，Online DDL 执行了约 40 分钟。问题出在：

1. 从库的 SQL 线程单线程回放，DDL 执行期间，主库的 DML 变更在从库上堆积。
2. 40 分钟后 DDL 完成，但 Relay Log 中堆积了 2 小时的增量变更，从库回放这些变更又花了 1 小时 20 分钟。
3. 最终主从延迟 2 小时，导致读写分离架构中的读请求大面积返回旧数据。

修复方案：使用 gh-ost 在从库上重新执行 DDL（通过 --allow-on-master 控制），gh-ost 的 Binlog 同步机制做到了 DDL 过程中主从延迟始终 < 5 秒。

总结

大表 DDL 的核心不是"能不能做"，而是"怎么做才能不影响业务"。Online DDL 适合小表和简单操作，gh-ost 适合大表和对锁表零容忍的场景。生产环境禁止直接 ALTER TABLE，这是 MySQL 运维的底线之一。无论选哪种方案，都必须在灰度环境先验证，并做好回滚预案（备份 + 表结构快照）。