深入理解Mysql OnlineDDL的算法

目录

• 一、Online DDL 是什么？
• 二、Online DDL 的三种主要算法
• 2.1COPY（复制法）
• 2.2 INPLACE （原地法）
• 2.3INSTANT (即刻法，mysql 8.0+)
• 三、Online DDL 的锁机制
• 四 关键区别
• 4.1 生动的比喻：给飞行中的飞机换引擎
• 4.2 如何指定和查看算法
• 4.3 重要注意事项
• 三、总结

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.devze.comNPLACE, 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 内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！