讲解Mysql OnlineDDL的算法
MySQL 5.6 及以后版本（尤其是 InnoDB 存储引擎）引入的一项极其重要的功能，它允许数据库管理员在执行 ALTER TABLE 操作时，最大程度地减少对表锁定和应用程序可用性的影响。
核心目标： 在 DDL 操作进行时，允许对表进行并发的读取（SELECT） 和写入（INSERT, UPDATE, DELETE） 操作。
一、传统的 DDL (Copy Algorithm)
在 Online DDL 出现之前，主要的 DDL 操作算法是 COPY：
创建临时表： 根据新的表结构创建一个空的新临时表。
锁表： 对原表施加一个排他锁 (
X Lock)。此时，任何对该表的读写操作都会被阻塞。复制数据： 将原表中的数据逐行复制到新临时表中。
应用修改： 在复制过程中或复制完成后，应用 DDL 语句定义的更改（如添加索引、修改列等）。
交换表名： 数据复制完成后，将原表删除（或重命名），并将新临时表重命名为原表名。
释放锁： 完成重命名后，释放排他锁。
优点： 实现简单，兼容性好。
致命缺点： 在步骤 2 到步骤 5 期间，表完全不可用。对于大表，这可能导致长时间的停机窗口，对在线业务是灾难性的。需要双倍存储空间（原表 + 新表）。
二、Online DDL 的三种主要算法
2.1 COPY (Online-Capable with Locking)
本质： 是传统 COPY 算法的变种，但努力减少锁定时间。
过程：
创建临时表。
在原表上加
SHARED锁（允许读，阻塞写）。将原表数据复制到临时表。
在临时表上应用 DDL 更改。
短暂申请
EXCLUSIVE锁（阻塞读写）。交换表名。
释放锁。
锁时间： 大部分时间持有
SHARED锁（阻塞写），最后交换时持有短暂的EXCLUSIVE锁（阻塞读写）。空间： 需要双倍存储空间。
并发性： 允许读，阻塞写。
适用场景： 通常是一些无法使用 INPLACE 的操作（如删除主键、修改某些列类型）。MySQL 会尽量优先选择 INPLACE 或 INSTANT。在
ALGORITHM=COPY或old_alter_table=ON时强制使用。
2.2 INPLACE (Online-Capable)
本质： 操作尽可能在原表空间内进行，避免重建整个表。这是 Online DDL 的主力算法。
过程：
准备阶段：
对表加
SHARED_NO_WRITE锁（或类似元数据锁 MDL）。此时允许读，阻塞写。创建新的临时文件（如
.ibd,.frm临时文件），准备所需资源（如新索引的 B+树结构）。
执行阶段：
降级为
SHARED锁（MySQL 5.6）或更宽松的元数据锁（MySQL 5.7+）。此时允许读写！（这是关键进步）在后台应用更改（如逐行读取数据构建新索引、合并 B+树页、原地修改页结构等）。这个过程通常是最耗时的，但允许并发读写。
提交阶段：
短暂申请
EXCLUSIVE锁（阻塞读写）。将执行阶段产生的更改（日志、临时文件）应用到原表，更新数据字典元数据。
清理临时文件。
释放锁。
锁时间： 准备和提交阶段有短暂的锁定（主要是阻塞写），执行阶段允许并发读写。整体锁定时间远小于 COPY 算法。
空间： 通常只需要额外的日志空间（用于记录并发 DML）和临时排序文件空间（如建索引时），不需要完整的双倍表空间（除非操作本身需要，如
OPTIMIZE TABLE）。但磁盘 IO 和 CPU 开销仍然较大。并发性： 执行阶段允许并发读写，这是其被称为 "Online" 的关键。
适用场景： 绝大多数 DDL 操作都支持 INPLACE，如添加/删除二级索引、修改默认值、添加/删除列（8.0 前）、修改
VARCHAR列长度（不缩小）、OPTIMIZE TABLE、表重建等。
2.3 INSTANT (Online-Capable)
INSTANT (Online-Capable)
本质： MySQL 8.0 引入的革命性算法。操作只修改数据字典元数据（metadata），几乎不修改表数据本身。速度极快（毫秒级）。
过程：
对表施加一个极其短暂的
EXCLUSIVE元数据锁。在数据字典中记录变更信息（如在表的元数据中记录新增列的定义、默认值；或在每行记录的头部信息中标记该行是否应用了新的默认值）。
释放锁。
对于新增列：
如果指定了默认值（包括
NULL），DDL 完成后立即查询该列，对于 DDL 执行前存在的行 (old rows)，MySQL 在读取时会即时计算 (INSTANT) 并返回默认值（或NULL）。该默认值不会物理存储在这些旧行中。对于 DDL 执行后插入的新行 (
new rows)，默认值会物理存储在行记录中。可以通过
ALTER TABLE ... ALGORITHM=INSTANT强制尝试使用，但如果不支持会自动回退到 INPLACE 或 COPY。
锁时间： 极其短暂的元数据锁（毫秒级）。
空间： 几乎不占用额外空间（仅少量元数据）。是空间效率最高的算法。
并发性： 几乎不影响读写，锁定时间可忽略不计。
适用场景 (MySQL 8.0+):
添加列（需满足条件，如加在最后、不支持
FULLTEXT/SPATIAL等特殊索引、某些数据类型限制等）。删除列（需满足条件，MySQL 8.0.29+）。
添加或删除虚拟列 (Virtual Column)。
添加或删除列默认值 (
SET DEFAULT,DROP DEFAULT)。修改
ENUM或SET列的定义（在末尾添加新选项）。修改索引类型（如
BTREE转HASH，反之亦然，但 InnoDB 只支持 BTREE）。重命名表 (
RENAME TABLE)。设置/删除表的
PERSISTENT统计信息选项。更多操作在后续 MySQL 版本中不断加入（如
DROP索引 8.0.12+,RENAME索引 8.0.13+ 等）。务必查阅对应版本的官方文档。
2.4 关键区别总结
| 特性 | COPY Algorithm | INPLACE Algorithm | INSTANT Algorithm (8.0+) |
|---|---|---|---|
| 核心方式 | 重建整个表 | 原地修改，避免重建整个表 | 仅修改元数据 |
| 锁表时间 | 长 (全程锁或长写锁) | 短 (准备/提交锁) | 极短 (毫秒级元数据锁) |
| 执行阶段 | 不允许读写 | 允许并发读写 | 允许并发读写 |
| 空间占用 | 双倍表空间 | 额外日志/临时文件空间 | 几乎无额外空间 |
| 速度 | 慢 | 中等 (取决于操作复杂度) | 极快 (毫秒级) |
| 并发影响 | 高 (停机) | 低 (短暂阻塞写) | 极低 (几乎无感知) |
| 主要优势 | 兼容性 | 平衡性能和并发 | 瞬时完成，零感知 |
| 典型操作 | 部分无法 INPLACE 的操作 (如删除主键) | 添加/删除索引、修改列属性等 | 添加/删除列 (有条件)、改默认值 |
2.5 如何指定和查看算法
指定算法： 在
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=INPLACE, LOCK=NONE并加上NO_WRITE_TO_BINLOG和COMMIT子句通常不会真正执行，MySQL 会检查并报告它将使用的算法和锁。更好的方法是查询INFORMATION_SCHEMA.INNODB_TABLES或使用SHOW CREATE TABLE观察进度（对于长时间操作），或者直接执行后观察输出信息（很多客户端会显示使用的算法）。最准确的是查看官方文档对具体操作的支持矩阵。
2.6 重要注意事项
并非所有 DDL 都是 Online 的： 即使使用 INPLACE 算法，部分操作在准备或提交阶段也需要短暂的排他锁 (
EXCLUSIVE)。一些操作（如修改主键、修改某些列的数据类型、更改表字符集等）可能仍然需要 COPY 算法或更长时间的锁。务必查阅官方文档对应版本的 Online DDL 支持矩阵。空间与性能： INPLACE 操作虽然避免了重建整个表，但可能涉及大量的数据重组、日志记录、排序操作，仍然会消耗大量 I/O 和 CPU 资源，可能影响系统性能。INSTANT 操作在这方面开销最小。
复制： Online DDL 在 MySQL 复制环境（主从）中的行为也需要考虑。通常在主库上执行的 Online DDL，其效果也会在从库上以类似的方式应用（可能也是 Online 的，取决于从库版本和设置）。
元数据锁 (MDL)： 即使算法本身允许并发 DML，长时间的 DDL 操作也可能因为持有 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和LOCK子句（如ALGORITHM=INSTANT, LOCK=NONE），让 MySQL 在无法满足要求时报错，而不是默默使用低效的方式。对于大表操作，即使使用 INPLACE，也应在业务低峰期进行，并监控服务器资源（I/O, CPU, Memory）。
充分利用
INSTANT算法进行高频次的表结构变更（如快速加列）。