深入理解Mysql OnlineDDL的算法

MySQL 5.6 及以后版本（尤其是 InnoDB 存储引擎）引入的一项极其重要的功能，它允许数据库管理员在执行 ALTER TABLE 操作时，最大程度地减少对表锁定和应用程序可用性的影响。

核心目标： 在 DDL 操作进行时，允许对表进行并发的读取（SELECT） 和写入（INSERT, UPDATE, DELETE） 操作。

一、Online DDL 是什么？

Online DDL 是 MySQL 5.6 版本引入，并在后续版本中不断增强的一项功能。它允许你在执行数据定义语言（DDL）操作时（如 ALTER TABLE），尽可能地减少对表的锁定时问，使得：

• 写操作（DML）：在 DDL 操作进行的同时，应用程序依然可以对表执行 INSERT, UPDATE, DELETE 等操作，最大程度保证业务的连续性。
• 读操作：SELECT 查询通常可以正常进行，不受影响。

这与早期的 Copy Table 机制形成鲜明对比，早期方式需要全程锁表，直到操作完成，对于大表来说意味着长时间的停机。

二、Online DDL 的三种主要算法

MySQL 在执行 DDL 时，根据操作类型的不同，底层主要采用三种算法。理解这些算法是理解 Online DDL 的关键。

2.1COPY（复制法）

过程：

• 创建一个与原始表结构相同的临时表（.frm，.ibd 等文件）。
• 在新的临时表上执行 DDL 操作。
• 将原始表的数据逐行复制到临时表中。
• 在此期间，对原始表的写操作会被阻塞（通常只在数据拷贝的最后阶段有短暂锁表）。
• 数据复制完成后，用新的临时表替换原始表，并删除旧的表。

特点：

• 需要两倍的存储空间。
• 过程中大部分时间会阻塞写操作，影响业务。
• 是 MySQL 5.5 及之前版本的主要方式。

2.2 INPLACE （原地法）

过程：

无需创建临时表文件，直接在原始表的存储文件（如 InnoDB 的 .ibd 文件）上进行操作。

通常分为两个阶段：

• 准备阶段（Prepare）：创建新的.frm文件，准备数据字典更改。可能需要短暂的排他锁（X锁）。
• 执行阶段（Execute）：应用更改到存储引擎，这通常是操作中最耗时的部分。在此阶段，允许并发的DML操作。

特点：

• 所需磁盘空间远少于 COPY 算法（通常只需要日志文件的空间）。
• 允许在执行阶段进行并发 DML，大大减少了锁表时间。

2.3INSTANT (即刻法，MySQL 8.0+)

过程：

• 操作只修改数据字典（元数据），而不触及表中的实际数据或索引。
• 例如，添加一个可为 NULL 且有默认值的列，只需要在数据字典中记录一下“这个表有这个列，默认值是什么”，而不需要重建表或复制数据。

特点：

• 速度极快，通常能在毫秒级完成。
• 完全不阻塞任何 DML 操作，是真正的“Online”。
• 对存储空间没有额外要求。

三、Online DDL 的锁机制

即使是 INPLACE 算法，也并非全程无锁。Online DDL 涉及两种主要的锁：

• SHARED锁（读锁）：在 DDL 的准备阶段，可能会短暂地获取。允许其他会话读，但阻塞写。
• EXCLUSIVE锁（写锁/排他锁）：在 DDL 的开始（准备阶段）和结束（提交阶段）可能会短暂地获取。此时会阻塞所有其他的读和写操作。

关键点：Online DDL 的“Online”体现在其耗时的数据拷贝/重建阶段（Execute阶段）是不锁表的，而只在元数据变更的瞬间需要短暂的排他锁。这个瞬间通常非常短，可以忽略不计。

四 关键区别

4.1 生动的比喻：给飞行中的飞机换引擎

想象一下，你要给一架正在飞行的飞机更换引擎（这相当于对数据库表做 ALTER TABLE）。

COPY 算法：让所有乘客下飞机（阻塞 DML），把飞机拖进机库，拆下旧引擎，换上新引擎，最后再让乘客登机。在此期间，飞机完全停运。

INPLACE 算法：

• 准备阶段 (Prepare)：工程师们做好所有准备工作：新引擎运到机场，所有工具就位。这需要飞机短暂地保持静止（短暂的排他锁）。
• 执行阶段 (Execute)：飞机保持飞行状态（允许并发 DML）。工程师们挂在机翼上，开始拆卸旧引擎，同时安装新引擎。乘客们（DML 操作）仍然可以在机舱内正常走动、点餐（INSERT, UPDATE, DELETE）。
• 提交阶段：新引擎安装完毕，最后进行一个极其快速的切换和检查，确保新引擎完全接管工作。这又需要飞机瞬间的静止（短暂的排他锁）。

4.2 如何指定和查看算法

指定算法： 在 ALTER TABLE 语句中使用 ALGORITHM 子句。

ALTER TABLE your_table ADD COLUMN new_col INT, ALGORITHM=INSTANT; -- 尝试强制使用 INSTANT
ALTER TABLE your_table ADD INDEX idx_name (col_name), ALGORITHM=INPLACE, LOCK=NONE; -- 尝试强制 INPLACE 且无锁

• ALGORITHM=DEFAULT：让 MySQL 选择它认为最高效的可用算法。
• ALGORITHM=COPY | INPLACE | INSTANT：强制使用特定算法。如果该算法不支持此操作，语句会报错。

指定锁策略： 使用 LOCK 子句。

ALTER TABLE ... LOCK=NONE; -- 尽可能允许并发读写 (最高并发)
ALTER TABLE ... LOCK=SHARED; -- 允许读，阻塞写
ALTER TABLE ... LOCK=EXCLUSIVE; -- 阻塞读写 (传统方式)
ALTER TABLE ... LOCK=DEFAULT; -- 让 MySQL 选择最小必要的锁策略

指定的 LOCK 级别必须兼容于操作本身支持的级别。例如，一个操作在 INPLACE 执行阶段允许 LOCK=NONE，但你强制指定 LOCK=EXCLUSIVE 是允许的（虽然不推荐）。反之，如果操作本身在某个阶段必须短暂加 EXCLUSIVE 锁，你指定 LOCK=NONE 会导致语句失败。

查看算法和锁： 执行 ALTER TABLE 前，使用 ALTER TABLE ... ALGORITHM=Ihttp://www.chinasem.cnNPLACE, LOCK=NONE 并加上 NO_WRITE_TO_BINLOG 和 COMMIT 子句通常不会真正执行，MySQpythonL 会检查并报告它将使用的算法和锁。更好的方法是查询 INFORMATION_SCHEMA.INNODB_TABLES 或使用 SHOW CREATE TABLE 观察进度（对于长时间操作），或者直接执行后观察输出信息（很多客户端会显示使用的算法）。最准确的是查看官方文档对具体操作的支持矩阵。

4.3 重要注意事项

• 并非所有 DDL 都是 Online 的： 即使使用 INPLACE 算法，部分操作在准备或提交阶段也需要短暂的排他锁 (EXCLUSIVE)。一些操作（如修改主键、修改某些列的数据类型、更改表字符集等）可能仍然需要 COPY 算法或更长时间的锁。务必查阅官方文档对应版本的 Online DDL 支持矩阵。
• 空间与性能： INPLACE 操作虽然避免了重建整个表，但可能涉及大量的数据重组、日志记录、排序操作，仍然会消耗大量 I/O 和 CPU 资源，可能影响系统性能。INSTANT 操作在这方面开销最小。
• 复制： Online DDL 在 MySQL 复制环境（主从）中的行为也需要考虑。通常在主库上执行的 Online DDL，其效果也会在从库上以类似的方式应用（可能也是 Online 的，取决于从库版本和设置）。
• 元数据锁 (MDL)： 即使算法本身允许并发 DML，长时间的 DDL 操python作也可能因为持有 MDL 而阻塞后续需要获取冲突 MDL 的其他 DDL 或某些事务。LOCK=NONE 的目标就是最小化 MDL 冲突。
• INSTANT 的限制： INSTANT 算法虽然强大，但有诸多限制（列的位置、数据类型、索引类型、表格式等），且限制随版本更新而变化。使用前务必确认操作是否支持 ALGORITHM=INSTANT。
• 版本差异： Online DDL 的支持程度和具体行为在不同 MySQL 版本（5.6, 5.7, 8.0）和 InnoDB 版本中有显著差异。强烈建议参考对应版本的官方文档。

三、总结

MySQL 的 Online DDL 通过 COPY, INPLACE, INSTANT 三种算法，极大地提升了 DDL 操作的并发性和可用性。尤其是 INSTANT 算法（MySQL 8.0+）对于支持的列操作实现了近乎瞬时的变更，对在线业务影响最小。INPLACE 算法则是大多数索引和列操作的主力，在执行阶段允许并发读写。COPY 算法作为最后的选择，应尽量避免。

最佳实践：

• 优先使用 MySQL 8.0+ 以获得最完善的 INSTANT 支持。
• 在执行 DDL 前，务必查阅官方文档，明确该操作在你的 MySQL 版本上支持的算法和锁定行为。
• 在 ALTER TABLE 语句中显式指定 ALGORITHM 和 LOCUdRTfzK 子句（如 ALGORITHM=INSTANT, LOCK=NONE），让 MySQL 在无法满足要求时报错，而不是默默使用低效的方式。
• 对于大表操作，即使使用 INPLACE，也应在业务低峰期进行，并监控服务器资源（I/O, CPU, Memory）。
• 充分利用 INSTANT 算法进行高频次的表结构变更（如快速加编程列）。

到此这篇关于深入理解Mysql OnlineDDL的算法的文章就介绍到这了,更多相关Mysql OnlineDDL 内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)！