文日小栈

1. 完整回答

大表 DDL 指的是对数据量较大的数据库表执行结构变更，比如新增字段、修改字段类型、添加索引或者调整约束。主要需要解决的问题是：在业务持续读写的情况下，尽量降低表结构变更对数据库性能和可用性的影响。

以 MySQL InnoDB 为例，原生 DDL 主要有三种执行算法：COPY、INPLACE 和 INSTANT。

COPY 会复制原表数据，执行期间通常会阻塞写入；INPLACE 可以避免传统的整表复制，部分操作允许并发读写，但仍然可能扫描或重建整张表；INSTANT 只修改元数据，不需要遍历原表数据，执行速度通常很快。

需要注意，Online DDL 也可能发生阻塞。因为 DDL 在执行过程中需要获取元数据锁，也就是 MDL 锁。如果存在长事务，DDL 可能一直等待锁，后续业务请求也可能排队，最终造成数据库连接堆积。

对于 MySQL 原生 DDL 无法低影响完成的操作，可以使用 pt-online-schema-change 或 gh-ost。它们通过创建新表、分批复制历史数据、同步增量变更，最后切换新旧表，降低长时间阻塞业务的风险。

实际生产中，一般会先确认 MySQL 版本和 DDL 支持的算法。如果支持 INSTANT，优先使用；如果需要重建大表，就评估原生 Online DDL 或在线变更工具，同时控制数据库负载、主从延迟和切换阶段的锁等待。

2. 核心原理与关键概念

2.1 MySQL 原生 DDL 的执行机制

MySQL InnoDB 主要提供三种 DDL 算法。

算法	核心机制	是否扫描或重建大量数据	对业务的影响
INSTANT	修改数据字典中的元数据	通常不需要	影响最小，但仍可能等待 MDL
INPLACE	在存储引擎内部执行变更	取决于具体操作，可能重建整表	部分操作允许并发读写，资源消耗可能较大
COPY	创建新表结构并复制原表数据	需要复制数据	通常允许并发查询，但会阻塞写入

核心区别在于是否需要处理原有数据，以及执行期间允许多大程度的并发访问。

例如：

新增普通字段：符合条件时可以使用 INSTANT。
新增普通二级索引：通常使用 INPLACE，需要扫描数据并构建索引。
修改字段数据类型：通常需要 COPY，可能长时间阻塞写入。

具体支持情况取决于 MySQL 版本、存储引擎、表结构和变更类型。

2.2 Online DDL 的核心流程

以支持并发 DML 的 InnoDB 在线创建二级索引为例：

初始化 → 构建索引 → 记录并应用增量变更 → 提交表结构

初始化：检查执行算法和并发能力，获取必要的 MDL，保护当前表结构。
构建索引：扫描原表数据，排序并构建新的二级索引，同时允许业务继续读写。
增量同步：通过在线变更日志记录执行期间的相关 DML，并在完成前应用这些变化，保证索引数据一致。
提交变更：获取短暂的排他 MDL，完成表结构切换，使新索引正式生效。

这里最重要的两个问题是：

性能问题：扫描数据、构建索引和处理增量变更会消耗 CPU、I/O 和磁盘空间。
锁等待问题：初始化和提交阶段可能需要排他 MDL，长事务可能使短暂的锁操作变成长时间等待。

2.3 在线变更工具的核心流程

对于需要重建大表的 DDL，pt-online-schema-change 和 gh-ost 使用相似的迁移思路：

创建影子表 → 修改新表结构 → 分批复制数据 → 同步增量 → 切换新旧表

两种工具的核心区别是增量同步方式：

pt-online-schema-change（pt-osc）：通过触发器同步 INSERT、UPDATE、DELETE。
gh-ost：通过解析 Row Based Binary Log（行格式二进制日志）获取增量变更，再异步应用到影子表。

两种方案都需要处理数据一致性、复制进度、主从延迟以及最终切换时的 MDL 问题。

2.4 关键概念

概念	含义
DDL	数据定义语言，用于创建、修改和删除数据库对象
Online DDL	尽量允许业务并发访问的表结构变更
MDL	元数据锁，保护表结构与并发访问之间的一致性
DML	INSERT、UPDATE、DELETE 等数据操作
影子表（Ghost Table）	按目标结构创建的新表，用于迁移原表数据
Cut-over	增量同步完成后，将新表切换为正式业务表的过程

3. 高频面试追问

3.1 为什么大表 DDL 容易造成线上故障？

主要有三个原因：锁等待、资源消耗和复制延迟。

首先，DDL 需要 MDL。如果长事务一直持有表的元数据锁，DDL 申请排他锁时就可能被阻塞，后续查询也可能排队，造成连接数快速增加。

其次，对于重建表或者创建索引的操作，需要扫描大量数据，消耗 CPU、磁盘 I/O 和临时空间，可能影响正常业务查询。

最后，在主从复制架构中，大型 DDL 可能导致从库复制长时间无法推进，产生明显的主从延迟。

所以大表 DDL 的主要风险既包括锁表，也包括执行期间的持续资源压力。

3.2 Online DDL 为什么还可能阻塞读写？

因为 Online DDL 仍然需要 MDL，并发 DML 能否执行和 MDL 能否及时获取是两个不同的问题。

MySQL 在提交新表结构时，需要获取排他 MDL。如果此时存在尚未提交的长事务，DDL 就需要等待。

例如：

事务 A 查询表后未提交 → DDL 申请排他 MDL 并等待 → 后续事务 B 查询同一张表也可能被阻塞

这种情况下，即使 DDL 本身允许并发 DML，也可能导致业务请求堆积。

因此，执行 DDL 前需要排查长事务，执行时要监控 MDL 等待，并设置合理的锁等待超时。

继续追问：如果线上已经出现大量 Waiting for table metadata lock，怎么处理？

首先通过 SHOW FULL PROCESSLIST 和 performance_schema.metadata_locks 确认阻塞关系。

如果发现 DDL 正在等待长事务，优先评估取消等待中的 DDL，让业务恢复正常，再处理长事务并重新安排变更。

处理时需要确认会话身份和事务影响，避免直接终止重要业务事务。

3.3 INPLACE 一定不会重建表吗？

不一定。INPLACE 并不代表完全不处理原表数据。

INPLACE 表示避免使用传统 COPY 算法进行整表复制，但部分操作仍然需要重建表的数据结构。

例如：

添加普通二级索引：通常不需要重建聚簇索引，但需要扫描数据并构建新索引。
修改字段是否允许 NULL：通常需要重建表。
调整字段位置：通常需要重建表。

因此，判断 DDL 成本时，除了看执行算法，还要确认是否需要重建表、是否允许并发 DML。

3.4 如何给一张上亿行的订单表添加索引？

首先确认索引是否必要，检查查询条件、现有索引和执行计划，避免创建重复或收益有限的索引。

如果是 MySQL InnoDB 支持在线创建的普通二级索引，可以采用：

ALTER TABLE orders
ADD INDEX idx_user_id (user_id),
ALGORITHM=INPLACE,
LOCK=NONE;

这里通过 ALGORITHM=INPLACE 避免退化为 COPY，通过 LOCK=NONE 要求支持并发读写。如果无法满足这些条件，语句会报错。

不过，上亿行数据仍然需要扫描并构建索引，所以要提前评估磁盘空间、I/O、执行时间和主从延迟。

如果业务负载较高、无法承受原生 DDL 的资源压力，可以进一步评估 gh-ost 等可限速的在线变更方案。

3.5 pt-online-schema-change 和 gh-ost 有什么区别？

核心区别是增量数据的同步方式。

pt-osc 在原表创建触发器，业务发生写入时同步维护新表，所以增量同步比较直接，但会增加原表写入开销。

gh-ost 通过 Binlog 捕获增量，再异步写入新表，避免了触发器带来的同步写入压力，并且能够更灵活地暂停、限速和控制切换时间。

两者都需要复制原有数据，也都需要在最终切换阶段处理元数据锁。

选择时还需要检查限制：gh-ost 通常要求存在可用于数据遍历和匹配的主键或唯一键，并且不支持外键和触发器相关的表结构；pt-osc 对触发器和外键也有相应限制，需要提前验证。

3.6 线上执行大表 DDL，一般怎么设计完整流程？

一般会按变更评估 → 预演 → 正式执行 → 验证 → 收尾来处理。

首先确认变更类型、MySQL 版本、表大小、索引、外键，以及是否存在长事务，判断能否使用 INSTANT 或在线变更。

接着在测试环境或从库进行预演，评估执行时间、磁盘空间和性能影响，明确失败处理方案。

正式执行时选择业务低峰期，设置合理的锁等待超时，监控数据库负载、主从延迟、DDL 进度和业务错误率。如果采用 gh-ost，可以通过限速控制迁移压力，并安排合适的 Cut-over 时间。

完成后检查表结构、索引状态和数据一致性，同时观察业务 SQL 是否正常。

如果涉及字段删除或者不兼容的字段修改，还会提前完成应用兼容性改造，避免新旧版本应用在发布期间发生冲突。

3.7 大表新增字段、修改字段类型和添加索引，处理方式一样吗？

不一样，关键取决于变更是否需要处理已有数据。

新增普通字段，在支持 INSTANT 的版本及表结构条件下，只需要调整元数据，通常可以快速完成。

添加二级索引，需要读取已有数据并构建索引，虽然可以支持并发读写，但执行时间会随数据规模增长。

修改字段数据类型通常涉及存储格式转换，需要复制或重建数据，风险更高。

例如，将订单表的 INT 字段修改为 BIGINT，通常需要 COPY 算法。对于上亿行数据，需要考虑在线迁移工具，或者通过新增字段、分批回填、应用兼容和后续清理进行分阶段迁移。

继续追问：MySQL 8.0 的 INSTANT 新增字段有什么版本区别？

MySQL 8.0.12 开始支持 INSTANT 新增字段，但最初只能在表末尾添加；8.0.29 开始支持在任意位置快速加列，并支持快速删除列。

INSTANT 也存在行版本数量、表结构类型等限制。执行前需要结合实际版本确认，不能认为所有新增和删除字段操作都能瞬间完成。

3.8 大表 DDL 执行期间需要监控什么？

主要关注四个方面。

第一是 MDL 等待，检查是否存在长事务、DDL 等待和业务请求排队。

第二是数据库负载，包括 CPU、磁盘 I/O、活跃连接数、查询延迟和错误率。

第三是磁盘与变更日志空间。原生 Online DDL 可能产生临时排序文件和在线变更日志，日志超过 innodb_online_alter_log_max_size 限制时，DDL 可能失败。

第四是主从复制延迟。原生 DDL 在主库执行完成后才会在从库执行对应变更，可能导致从库复制停滞；在线迁移工具的数据复制也可能增加复制压力。

如果使用 gh-ost 或 pt-osc，还需要监控数据复制进度、增量追赶情况和限速状态。

3.9 为什么大表 DDL 通常需要主键或者唯一键？

主要是为了高效定位数据、分批复制以及正确同步增量变化。

例如，在线迁移工具复制订单表时，可以根据主键范围分批读取，降低单次操作的数据量。

当某条记录发生 UPDATE 或 DELETE 时，也可以利用主键或者稳定的唯一键找到新表中的对应记录。

缺少合适的唯一标识，会增加数据匹配、增量同步和一致性保障的难度。

因此，gh-ost 要求变更前后的表具有可共同使用的主键或者唯一键；pt-osc 在多数情况下也要求存在主键或唯一索引。

3.10 DDL 执行失败后可以直接回滚吗？

需要区分 DDL 执行失败恢复和业务变更回退。

MySQL 8.0 的 InnoDB 支持原子 DDL，能够保证受支持的 DDL 在执行失败或数据库崩溃时，不会留下部分提交的表结构变更。

但原子 DDL 不代表可以像普通事务一样执行 ROLLBACK 撤销已经成功提交的结构变更。

如果 DDL 已经成功执行，想恢复旧结构通常需要再次执行反向 DDL，或者使用提前准备的迁移回退方案。

对于影子表迁移，部分工具可以在切换后保留旧表，便于核查和制定回退方案。但如果切换后新表继续产生写入，直接切回旧表可能丢失新数据，所以仍需考虑增量数据处理和应用兼容性。

4. 易混淆概念对比

4.1 INSTANT、INPLACE、COPY

对比维度	INSTANT	INPLACE	COPY
修改方式	更新元数据	存储引擎内部完成变更	复制数据到新表
数据处理成本	通常很低	低到很高，取决于操作	通常较高
是否可能重建整表	否	是	复制整表数据
并发 DML	支持，但可能等待 MDL	部分操作支持	通常不支持
适合场景	支持快速变更的列操作	创建二级索引等	必须转换数据格式的操作

4.2 ALGORITHM 与 LOCK

参数	控制内容	示例
`ALGORITHM`	DDL 采用什么算法执行	`INPLACE`、`COPY`、`INSTANT`
`LOCK`	DDL 执行期间允许什么程度的并发访问	`NONE`、`SHARED`、`EXCLUSIVE`、`DEFAULT`

ALGORITHM=INPLACE, LOCK=NONE 表示要求使用 INPLACE 算法，并且允许并发读写。它仍然需要处理 MDL。

另外，ALGORITHM=INSTANT 只允许搭配 LOCK=DEFAULT，不能显式指定 LOCK=NONE。

4.3 原生 Online DDL、pt-osc、gh-ost

维度	原生 Online DDL	pt-osc	gh-ost
实现方式	MySQL 存储引擎处理	影子表 + 触发器	影子表 + Binlog
增量同步	InnoDB 内部维护	触发器同步	Binlog 异步应用
负载控制	缺少直接的暂停和限速机制	可控制分批复制速度	支持更灵活的暂停和限速
额外写入压力	取决于变更操作	触发器增加同步写入	异步维护影子表
主要限制	算法和并发能力受变更类型限制	受触发器、外键等条件限制	依赖 Binlog 和唯一键，不支持外键及触发器
适用场景	原生支持的低成本变更	需要在线复制迁移的场景	需要灵活控制迁移负载的场景

4.4 MDL 锁与行锁

维度	MDL 锁	行锁
保护对象	表结构及相关元数据	表中的数据记录或索引记录
主要作用	协调 DDL 与并发访问	协调事务之间的数据读写
常见阻塞	DDL 等待长事务，后续查询排队	多个事务更新相同记录
排查方式	`metadata_locks`	InnoDB 事务及数据锁信息

MDL 的特点是影响范围可能扩展到整张表，因此即使 DDL 只需要短暂的排他锁，也可能因等待而引发大面积业务阻塞。

4.5 Online DDL 与原子 DDL

概念	解决的问题	核心特点
Online DDL	变更期间的业务可用性	尽量允许并发访问
原子 DDL	变更失败时的结构一致性	成功整体提交，失败整体撤销
事务性 DDL	在用户事务中控制结构变更提交与回滚	MySQL 原子 DDL 不提供这种能力

5. 总结

大表 DDL 的核心问题，是在修改表结构的同时控制业务阻塞、数据库负载和数据一致性风险。MySQL InnoDB 提供 INSTANT、INPLACE 和 COPY 三种算法，能够只修改元数据时优先使用 INSTANT，需要扫描或重建数据时再评估 Online DDL 的执行成本。如果原生变更风险较高，可以使用 pt-osc 或 gh-ost，通过影子表、分批复制和增量同步完成迁移。需要特别关注的是，Online DDL 不代表完全无锁，长事务可能导致 MDL 等待，进而阻塞业务请求。因此，生产环境的大表 DDL 需要提前评估执行算法，做好预演、限速、监控和回退设计。