要理解 MySQL 的 Online DDL，我们不能只看语法，必须从第一性原理出发，理解数据库在"修改结构"和"读写数据"这两个核心需求之间的矛盾与平衡。

简单来说，Online DDL 的核心目标是：在修改表结构（DDL）的同时，不阻塞业务对表的读写操作（DML）。

1. 宏观概述

1.1 背景与痛点

为什么我们需要 Online DDL？

在 MySQL 5.6 之前，大多数 DDL 操作（如添加索引、添加列）的本质是暴力重构。MySQL 会执行以下步骤：

  1. 创建一个新的临时表（新结构）。
  2. 锁住原表（禁止写入）。
  3. 将原表数据一行行复制到新表。
  4. 删除原表，重命名新表。

这种方式被称为 Copy Table 方式。它的痛点极其明显：在数据量大的表中，DDL 可能运行数小时，期间业务无法写入，这对于高并发互联网应用是灾难性的。

为了解决这个问题，MySQL 逐步引入了 In-Place 和 Instant 算法，统称为 Online DDL。

1.2 从 Copy 到 Instant

理解 Online DDL 的关键在于理解三种算法的演进，这是一个由重到轻的过程：

1.2.1 COPY（MySQL 5.6 之前）

  • 原理：在 Server 层处理，新建表 -> 导数据 -> 删旧表。
  • 代价：极高。I/O 飙升，占用双倍磁盘空间，全程锁表（无法写入）。

1.2.2 INPLACE（MySQL 5.6 引入，成熟于 5.7）

  • 原理：不再通过 Server 层复制数据，而是由 InnoDB 引擎内部在“原地”进行操作。
  • 关键机制：虽然叫 In-Place（原地），但对于重建表的操作（如添加主键），它实际上还是在引擎内部重建了数据文件，但它引入了一个Row Log（增量日志）。
  • 并发性：允许 DML（增删改）操作。
    • 在 DDL 执行期间，业务产生的新数据写入 Row Log。
    • DDL 完成数据重组后，再重放 Row Log 中的变更应用到新表空间。

1.2.3 INSTANT（MySQL 8.0 引入并持续增强）

  • 原理：只修改数据字典（Metadata）中的元数据，完全不触碰底层数据文件（B+ 树）。
  • 代价：几乎为零。耗时通常在毫秒级。
  • 场景：MySQL 8.0 支持瞬间添加列，不需要重建表。

2. 底层原理

2.1 INPLACE

MySQL 5.6 引入，5.7 成熟。它的核心变革在于：将 DDL 操作下沉到 InnoDB 引擎层，并引入了 Row Log 来暂存并发写入。

INPLACE 分为两类：需要重建表（Rebuild）和不需要重建表（No-Rebuild）。我们重点讲最复杂的 Rebuild 场景（例如：添加主键、删除列、修改列字符集）。

第一阶段：初始化 (Prepare Phase)

  1. 对表加 MDL 写锁（极短时间）。
  2. 获取原表的元数据快照。
  3. 降级锁：将 MDL 写锁降级为 MDL 读锁。此时业务可以正常读写（SELECT/INSERT/UPDATE/DELETE）。

第二阶段：执行 (Execution Phase) —— 最漫长

这是真正的 Online 阶段。InnoDB 引擎内部做两件事：

1. 数据重组 (Rebuild Data)：

  • InnoDB 建立一个新的临时表空间文件（.ibd）。
  • 扫描原表的 B+ 树叶子节点，按顺序将数据写入新的 B+ 树中。

2. 增量捕获 (Row Log)：

  • 在数据重组期间，所有业务产生的 DML（增删改）操作，除了修改原表数据外，还会被记录到一个专门的内存缓冲区：Row Log 。如果内存存不下，会溢出写入到临时文件中。

第三阶段：提交 (Commit Phase)

  1. 升级锁：再次将 MDL 读锁升级为 MDL 写锁（此时业务写入再次短暂阻塞）。
  2. 日志回放 (Apply Log)：将 Row Log 中积累的增量变更，应用到新的表空间中。因为 Row Log 通常比全量数据小得多，所以这个过程很快。
  3. 文件交换：用新的 .ibd 文件替换旧文件。
  4. 释放锁。

总结：

  • 核心机制：快照数据（基线） + Row Log（增量）。
  • 代价：虽然不阻塞写入，但会消耗大量 CPU 和 IO（同时写原表和 Log），且若 DDL 耗时过长，Row Log 可能撑爆磁盘。

注意

修改列的数据类型（例如从 CHAR 改为INT）通常必须使用 COPY算法。因为底层二进制存储格式变了，必须重写每一行数据，无法In-Place。

2.2 INSTANT

MySQL 8.0 引入，并在 8.0.12 和 8.0.29 版本中大幅增强。它的核心在于：彻底放弃修改物理数据文件，只修改数据字典（Metadata）。

  • MySQL 8.0.12：仅支持追加列。原理很简单，在表定义的元数据中记录一个"列数"标记。旧数据读出来列数不够，就自动补默认值。
  • MySQL 8.0.29：支持任意位置加列/删列。原理更加精妙，利用了行版本控制。

底层原理流程：

  1. 加锁：加 MDL 写锁（极短，仅用于修改元数据）。
  2. 修改元数据：
    • 在 .SDI (System Data Index) 或数据字典中，更新表的定义。
    • 关键点：给表分配一个新的 INSTANT_COLUMN_ID。
    • 设置新列的 Default Value（存储在元数据中，不存磁盘行内）。
  3. 释放锁。

当业务执行 SELECT * 时，InnoDB 引擎读取磁盘上的行记录（Row）：

  • 旧行（DDL 之前写入的）：行头部的版本信息或列数标记表明它是"老版本"。InnoDB 发现这一行缺少新列，于是从元数据中读取该列的默认值，动态拼接并返回给应用。
  • 新行（DDL 之后写入的）：按照新的结构物理存储，包含新列的数据。

3. 风险隐患

3.1 INPLACE

虽然 INPLACE 是 "Online"，但在生产环境（特别是大表）直接执行 ALTER TABLE 依然极其危险。

3.1.1 MDL 锁风暴

这是最容易被忽视的风险。

  • 现象：你发起一个 Online DDL，理论上不锁表。但是，如果有另一个长事务（比如一个正在运行的慢查询）正在引用这张表，DDL 就会挂起，等待获取 MDL 锁。
  • 连锁反应：MySQL 的锁等待队列通常是 FCFS (First Come First Served) 或写锁优先级高于读锁。DDL 一旦开始等待 MDL 锁，它后面进来的所有正常业务请求（SELECT/UPDATE）全都会被阻塞！瞬间导致连接池爆满，业务挂掉。
  • 对策：在执行 DDL 前，务必检查 information_schema.processlist，确保没有长事务或慢查询在操作该表。

INSTANT 也有这个问题。

3.1.2 主从延迟

这是 MySQL 主从架构的经典痛点。

  • Master：Online DDL 可以开启多线程（MySQL 8.0+ 支持 parallel DDL），或者只是 Master 机器性能强，跑了 10 分钟做完了。
  • Binlog：DDL 完成后，Master 会往 Binlog 写一句 ALTER TABLE ...。
  • Slave：
    • Slave 的 SQL Thread 是单线程回放 DDL 的（在很多版本配置下）。
    • Slave 收到这个 DDL，也开始跑。Master 跑了 10 分钟，Slave 可能也要跑 10 分钟（甚至更久，因为 Slave 配置通常低）。
    • 后果：在这 10 分钟内，Slave 忙着做 DDL，无法处理 Master 传过来的其他 Update/Insert Binlog。
    • Seconds_Behind_Master 飙升。读写分离的业务读不到最新数据，产生逻辑错误。

3.1.3 资源争抢与 Buffer Pool 污染

Online DDL（In-Place Rebuild）本质上是一次全量的读 + 全量的写。

  • Buffer Pool 污染：
    • DDL 扫描原表所有数据页。如果表很大，会把 Buffer Pool 里的热数据（业务正在用的数据）挤出去。
    • DDL 结束后，业务查询必须重新从磁盘加载数据，导致 RT (响应时间) 抖动。
  • IO 争抢：虽然 DDL 设置了并发度，但它依然会占用大量的磁盘 IOPS 和 CPU。如果是云盘或机械盘，业务正常的 CRUD 可能会因为 IO 等待而变慢。

3.1.4 Apply Log 阻塞

MySQL 为了保证新表和旧表数据严格一致，在 Switch（切换）的那一刻，必须保证 Row Log 里的数据全部回放完毕。

  • 理想情况：DDL 执行期间，业务写入很少。Row Log 只有几 KB。Step B 在毫秒级完成，用户无感知。
  • 糟糕情况：DDL 跑了 1 个小时（全表重组）。这 1 小时内，业务疯狂写入（TPS 很高）。Row Log 积压了 500MB 甚至更多。
    • 到了 Commit 阶段，MySQL 获取写锁。
    • 开始回放这 500MB 的日志。
    • 整个回放过程，业务全部被堵在外面！
    • 如果回放需要 1 分钟，你的系统就挂了 1 分钟。

第一性原理视角：这就是 追赶模型 (Catch-up Model) 的固有缺陷。如果"增量产生的速度"接近"回放的速度"，或者存量太大，最后的同步阶段就会拉长阻塞窗口。

MySQL 其实做了一些优化（Iterative Apply），在 Execute 阶段末尾会尝试预先回放一部分日志。但为了保证最终一致性，最后的一小截尾巴，必须在写锁保护下回放。如果这截尾巴处理不掉，阻塞就不可避免。

3.1.5 磁盘空间爆满

DDL 执行期间的并发写入（Insert/Update/Delete）不会直接修改正在重建的表，而是暂存在 innodb_online_alter_log 中（内存+磁盘临时文件）。该日志的大小受到 innodb_online_alter_log_max_size 参数的硬性限制（默认仅 128MB）。

如果 DDL 耗时很久，或者业务写入量很大，填满了这个 Log。MySQL 会直接报错并回滚整个 DDL 操作，之前几个小时的 CPU/IO 资源白费。更严重的是，触发溢出的那个用户事务会被强制回滚，导致业务报错。

3.2 INSTANT

天下没有免费的午餐。INSTANT 算法虽然写得快，但它把成本转移到了读的时候。

3.2.1 读放大

  • Copy/Inplace：数据在物理上已经重写好了。读取时，拿出来的就是完整的行。
  • Instant：物理上的行数据可能只有 3 列，但表定义里有 4 列。每次 SELECT 读取该行时，InnoDB 引擎必须在内存中判断并加上缺失行的默认值。这增加了一点点 CPU 的开销（微乎其微，但存在）。

3.2.2 数据腐烂风险

如果你对一张表连续做了 50 次 Instant Add Column，再删几列。表里的数据行就会变得五花八门：有的行是 2023 年的版本，有的行是 2024 年的版本。

表结构的元数据会变得非常复杂，解析成本变高。

建议：如果一张表经过了极其频繁的 Instant 变更，在低峰期做一次 OPTIMIZE TABLE（这会强制触发 In-Place Rebuild）来把物理数据规整化，是有好处的。

4. 什么时候用哪个

什么时候 INSTANT，什么时候 INPLACE，什么时候 COPY？怎么判断呢？

判断这三者的核心逻辑，依然遵循第一性原理：看数据的物理存储是否需要改变。

MySQL 在执行 DDL 时，内部遵循一个懒惰原则：能偷懒就偷懒。优先级是： Instant (最懒/只改元数据) > In-Place (勤快/原地修整) > Copy (笨重/推倒重来)。

4.1 INSTANT (首选，快乐路径)

原理：只修改 .SDI (元数据) 或 数据字典。不触碰 B+ 树叶子节点的数据。

适用场景：

  • 新增列 (Add Column)：
    • MySQL 8.0.12+ 支持（只能加在最后）。
    • MySQL 8.0.29+ 支持（任意位置 AFTER column）。
  • 删除列 (Drop Column)：MySQL 8.0.29+ 支持。
  • 重命名 (Rename)：表名、列名。
  • 修改默认值 (Set/Drop Default)。
  • 虚拟列 (Virtual Column)：添加或删除。
  • 扩展 VARCHAR 长度：且扩展后字节数 < 256 (长度前缀保持 1 字节)。

4.2 INPLACE (主流，需谨慎)

原理：引擎层"原地"重建 B+ 树或索引树。利用 Row Log 记录执行期间的并发写入，最后回放。

适用场景：

  • 添加索引 (Add Index)：最常见的场景。
  • 删除索引 (Drop Index)：实际上改元数据即可，极快（但在官方分类中常归为 Inplace/Metadata）。
  • 整理表碎片 (Optimize Table)：强制触发生效。
  • 修改 Row Format：如 Compact -> Dynamic。

风险提示：虽不锁表，但消耗大量 IO 和 CPU，且最后 Apply Log 阶段如果追不上，可能短暂阻塞。务必避开业务高峰。

4.3 COPY (禁区，保底方案)

原理：数据的二进制存储格式发生了根本变化，引擎无法处理，必须由 Server 层建立新表，一行行读出来清洗、转换、写入。

适用场景：

  • 修改字段数据类型：如 INT -> BIGINT, VARCHAR -> INT。

  • 修改字符集：如 utf8 -> utf8mb4。

  • 删除主键 (Drop Primary Key)。

  • 扩展 VARCHAR 长度：当从 < 256 变为 ≥ 256 字节时（字节记录长度发生变化）。

    MySQL 8.0.29+ 的 INSTANT 甚至支持跨越 256 字节的 VARCHAR 扩展（只要不修改数据本身）。

  • MySQL 5.6 之前的大部分操作。

警告：全程锁写入。线上大表严禁直接执行，必须使用 gh-ost 或 pt-osc。

4.4 最佳实践

不要让 MySQL 帮你选，显式指定算法，让潜在的 COPY 暴露出来：

1
2
3
4
5
-- 你的防御性写法
ALTER TABLE your_table 
ADD INDEX idx_xxx (xxx), 
ALGORITHM=INPLACE,  -- 强制要求原地执行
LOCK=NONE;          -- 强制要求不锁表
  • 如果 MySQL 能够满足（是 Instant 或 Inplace），它就执行。
  • 如果 MySQL 发现这事儿必须 COPY（要锁表），它会直接报错。

如下所示：

1
2
3
mysql> ALTER TABLE t_online_ddl MODIFY COLUMN val BIGINT, ALGORITHM=INPLACE, LOCK=NONE;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

5. 其他工具

原生 Online DDL 还是存在一切痛点，尤其是 INPLACE 和 COPY。所以出现了 pt-online-schema-change (pt-osc) 和 github-online-schema-change (gh-ost) 这两个工具。

它们的核心思想都是 影子表（Shadow Table）策略：建一张新表，同步数据，最后切换。

区别在于：怎么同步增量数据？

5.1 pt-osc

pt-osc 是 Percona Toolkit 的一部分，是老牌的方案。

核心原理

数据库触发器 (Database Triggers)。

工作流程：

  1. 创建影子表：创建一个和原表结构一样的新表 _table_new。
  2. 修改结构：在新表上执行 ALTER TABLE（比如加字段）。
  3. 创建触发器 (关键)：在原表上创建 3 个触发器（AFTER INSERT, AFTER UPDATE, AFTER DELETE）。这意味着：每当业务向原表写入一行数据，MySQL 会自动触发一个动作，把这行数据的变更同步写入到新表中。
  4. 拷贝存量数据：工具开始分批次（Chunk）把原表的历史数据 INSERT IGNORE 到新表中。
  5. 原子切换：数据拷贝完后，利用 RENAME TABLE 原子操作，把原表改名，新表上位。

优点：

  • 可靠：基于数据库内部机制，数据一致性强。
  • 兼容性：支持所有 Binlog 格式（Statement/Row/Mixed）。

缺点：

  • 同步阻塞：触发器是和业务 SQL 在同一个事务 里执行的。
    • 如果你的业务 SQL 执行需 1ms，加上触发器写新表可能变成 2ms。这直接导致写性能下降。
    • 如果触发器写新表失败（比如锁等待），你的业务 SQL 也会回滚失败！
  • 元数据锁 (MDL)：创建和删除触发器的瞬间，需要锁表（MDL 写锁），在高并发下可能导致拥堵。

5.2 gh-ost

核心原理

模拟从库 (Binlog Simulation)。

GitHub 在被 pt-osc 的触发器搞了几次故障后，开发了 gh-ost。它彻底抛弃了触发器。

工作流程：

  1. 创建影子表：同上，建新表、改结构。
  2. 伪装成从库：gh-ost 进程把自己伪装成一个 MySQL Slave，连接到 Master（或者真实的 Slave）。它请求 Dump Binlog 流。
  3. 监听 Binlog：业务在原表写入数据，产生 Binlog。gh-ost 读取 Binlog，将解析出来的 Binlog 事件（Insert/Update/Delete），转换成 SQL 语句，并在影子表上回放。
  4. 拷贝存量数据：同上，分批拷贝历史数据。
  5. 原子切换：因为 gh-ost 是异步的，所以切换的时候必须有"同步"辅助方案。所以它使用一种特殊的机制（Cut-over）进行表名切换。

Cut-over 过程：

  1. 制造阻塞：gh-ost 会建立一个连接 C1，执行 LOCK TABLES tbl WRITE;。这时候，所有业务线程想写这张表，都会被堵塞住（Blocked）。原表被冻结了，不再会有新数据写入（这就消除了追不上的问题）。
  2. 发起改名：gh-ost 建立另一个连接 C2，执行 RENAME TABLE tbl TO tbl_old, ghost_tbl TO tbl;。这条 SQL 也会被 C1 的锁堵塞住，卡在 MySQL 的执行队列里。
  3. 插队机制：此时，MySQL 的锁等待队列里可能排着一堆请求。但是！RENAME 等 DDL 操作的优先级高于 INSERT/UPDATE 等 DML 操作。 所以，虽然大家都在排队，但 MySQL 会把 C2 的 Rename 请求提到最前面（仅次于持有锁的 C1）。
  4. 瞬间释放：gh-ost 确认 C2 已经乖乖排在队首后，C1 执行 UNLOCK TABLES;。锁一释放，排在队首的 C2（Rename）瞬间执行。因为 C2 优先级最高，没有任何业务写入能插到 C1 释放和 C2 执行这两个动作中间。

优点：

  • 解耦：gh-ost 是在应用层（或者说外部工具层）回放数据。它和业务的写操作完全异步。
  • 轻量：业务 SQL 写入原表后就结束了，不需要等新表写入。不影响业务响应时间。
  • 可暂停/限速：这是 gh-ost 的杀手锏。如果发现数据库负载高了，gh-ost 可以瞬间暂停读取 Binlog，甚至在拷贝数据的过程中随时停下来睡觉。这是触发器做不到的。

缺点：

  • 依赖 Row 格式：必须开启 Binlog row 模式（现代 MySQL 标配）。
  • 复杂性：架构比 pt-osc 复杂，需要处理 Binlog 解析。

6. 总结

回到第一性原理：Online DDL 的本质，是在"修改表结构"与"保持业务可用"之间寻找平衡点。

算法 原理 代价 场景
COPY Server 层新建表 → 复制数据 → 删旧表 极高，全程锁表 数据类型变更、字符集修改
INPLACE 引擎层原地重建 + Row Log 增量 中等，不锁表但耗 IO 加索引、优化表碎片
INSTANT 只改元数据，不碰数据 几乎为零，毫秒级 加/删列、改默认值

无论哪种算法，MDL 锁始终是最隐蔽的杀手。一个长事务就能让整个 DDL 链路瘫痪。

  • INPLACE：主从延迟、Buffer Pool 污染、Apply Log 阻塞、磁盘撑爆。
  • INSTANT：读放大、数据腐烂（频繁变更后需定期 OPTIMIZE）。

工具选型：

场景 推荐方案
小表 + 低峰期 原生 ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE
大表 + 主从架构 gh-ost（无触发器、可暂停、可限速）
兼容性要求高 pt-osc（支持所有 Binlog 格式）

生产环境黄金法则：

  1. 永远显式指定算法：ALGORITHM=INPLACE, LOCK=NONE，让潜在的 COPY 暴露出来。
  2. 执行前检查长事务：SELECT * FROM information_schema.processlist WHERE TIME > 10;
  3. 避开高峰期：再 Online 的 DDL，也会消耗资源。
  4. 大表用工具：超过 100 万行，优先考虑 gh-ost。
  5. 监控主从延迟：Seconds_Behind_Master 是你的生命线。

一句话总结：

Online DDL 不是银弹。它只是把"长时间锁表"变成了"短时间锁表 + 长时间后台作业"。理解它的边界，才能用好它。

许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。

分享文章

快来参与讨论吧~