MySQL Online DDL详解:从历史演进到原理及使用

一、Online DDL的发展历史

MySQL Online DDL 功能从 5.6 版本开始正式引入，发展到现在的 8.0 版本，经历了多次的调整和完善。本文主要就 Online DDL 的发展过程，以及各版本的区别进行总结。其实早在 MySQL 5.5 版本中就加入了 INPLACE DDL 方式，但是因为实现的问题，依然会阻塞 INSERT、UPDATE、DELETE 操作，这也是 MySQL 早期版本长期被吐槽的原因之一。

 

在 MySQL 5.6 中，官方开始支持更多的 ALTER TABLE 类型操作来避免数据拷贝，同时支持了在线上 DDL 的过程中不阻塞 DML 操作，真正意义上的实现了 Online DDL。然而并不是所有的 DDL 操作都支持在线操作，后面会附上 MySQL 官方文档对于 DDL 操作的总结。

 

到了 MySQL 5.7，在 5.6 的基础上又增加了一些新的特性，比如：增加了重命名索引支持，支持了数值类型长度的增大和减小，支持了 VARCHAR 类型的在线增大等。但是基本的实现逻辑和限制条件相比 5.6 并没有大的变化。

 

MySQL 8.0 对 DDL 的实现重新进行了设计，其中一个最大的改进是 DDL 操作支持了原子特性。另外，Online DDL 的 ALGORITHM 参数增加了一个新的选项：INSTANT，只需修改数据字典中的元数据，无需拷贝数据也无需重建表，同样也无需加排他 MDL 锁，原表数据也不受影响。整个 DDL 过程几乎是瞬间完成的，也不会阻塞 DML。

二、Online DDL的算法

了解 Online DDL 先了解一下之前 DDL 的 2 种算法 copy 和 inplace。

Copy算法

1. 按照原表定义创建一个新的临时表
2. 对原表加写锁（禁止 DML，允许 select）
3. 步骤 1）建立的临时表执行 DDL
4. 将原表中的数据 copy 到临时表
5. 释放原表的写锁
6. 将原表删除，并将临时表重命名为原表

可见，采用 copy 方式期间需要锁表，禁止 DML，因此是非 Online 的。比如：删除主键、修改列类型、修改字符集，这些操作会导致行记录格式发生变化（无法通过全量 + 增量实现 Online）。

Inplace算法

在原表上进行更改，不需要生成临时表，不需要进行数据 copy 的过程。根据是否变更行记录格式，分为两类：

1. rebuild：需要重建表（重新组织聚簇索引）。比如 optimize table、添加索引、添加/删除列、修改列 NULL/NOT NULL 属性等；
2. no-rebuild：不需要重建表，只需要修改表的元数据，比如删除索引、修改列名、修改列默认值、修改列自增值等。

对于 rebuild 方式实现 Online 是通过缓存 DDL 期间的 DML，待 DDL 完成之后，将 DML 应用到表上来实现的。

说明：

1. 在 copy 数据到新表期间，在原表上是加的 MDL 读锁（允许 DML，禁止 DDL）
2. 在应用增量期间对原表加 MDL 写锁（禁止 DML 和 DDL）
3. 根据表A重建出来的数据是放在 tmp_file 里的，这个临时文件是 InnoDB 在内部创建出来的，整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个原地操作，这就是“inplace”名称的来源。

MySQL中，表级别的锁有2种

一种是我们通常说的表锁，由InnoDB引擎实现，如lock tables …

read/write，表锁影响较大，不常用。另一种表级别的锁是MDL( metadata lock

)，由Server层实现，MDL我们不显式使用，是在访问一个表时由数据库自动加的，对表记录增删改查时，加MDL读锁；对表结构进行变更时，加MDL写锁。MDL锁，读读不互斥，读写、写写互斥。

哪些常用操作“锁表”

创建二级索引（二级索引是指除主键索引之外的索引）、删除索引、重命名索引、改变索引类型——不“锁表”。

添加字段、删除字段、重命名字段、调整字段顺序、设置字段默认值、删除字段默认值、修改auto-increment值、调整字段允许NULL、调整字段不允许NULL

—— 不“锁表”。

扩展Varchar字段大小——不“锁表”。

更改字段数据类型，如varchar改成text——“锁表”

三、Online DDL过程中的锁

默认情况下，MySQL就是支持online的DDL操作的，在online的DDL语句执行的过程中，MySQL会尽量少使用锁的限制，我们不需要特殊的操作来启用它。

MySQL在选择的时候，尽量少使用锁，但是不排除它会选择使用锁。而如果我担心它选择了锁而导致我们的表不能读也不能写，显然这不是我们想要的结果，我们希望：如果选择了锁就不要执行，直接退出执行；如果没有选择锁就执行。想要达到我们希望的这个效果，该怎么做呢？

可以在执行我们的online DDL语句的时候，使用ALGORITHM和LOCK关键字，这两个关键字在我们的DDL语句的最后面，用逗号隔开即可。示例如下：

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;

 

ALGORITHM的选项

• INPLACE：替换：直接在原表上面执行DDL的操作。
• COPY：复制：使用一种临时表的方式，克隆出一个临时表，在临时表上执行DDL，然后再把数据导入到临时表中，在重命名等。这期间需要多出一倍的磁盘空间来支撑这样的 操作。执行期间，表不允许DML的操作。

• DEFAULT：默认方式，有MySQL自己选择，优先使用INPLACE的方式。

LOCK的选项

• SHARE：共享锁，执行DDL的表可以读，但是不可以写。
• NONE：没有任何限制，执行DDL的表可读可写。
• EXCLUSIVE：排它锁，执行DDL的表不可以读，也不可以写。
• DEFAULT：默认值，也就是在DDL语句中不指定LOCK子句的时候使用的默认值。如果指定LOCK的值为DEFAULT，那就是交给MySQL子句去觉得锁还是不锁表。不建议使用，如果你确定你的DDL语句不会锁表，你可以不指定lock或者指定它的值为default，否则建议指定它的锁类型。

执行DDL操作时，ALGORITHM选项可以不指定，这时候MySQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT，也是同样的效果。如果指定了ALGORITHM选项，但不支持的话，会直接报错。

注意:

在执行OnlineDDL之前，要在非业务高峰期去执行，并要确认待执行的表上面没有未提交的事务、锁等信息。可以通过如下的SQL语句查看是否有事务和锁等信息。

select * from information_schema.innodb_locks;
select * from information_schema.innodb_trx;
select * from information_schema.innodb_lock_waits;
select * from information_schema.processlist;

四、理解DDL操作的需求和挑战

DDL操作涉及对数据库表结构的修改，例如添加/删除列、修改列定义、添加/删除索引等。在以往的版本中，执行这些DDL操作时需要锁定整个表，对数据库的可用性产生了负面影响。因此，实现在线DDL成为了提高系统灵活性和性能的重要需求。

五、MySQL 5.7的在线DDL功能特点

MySQL 5.7通过InnoDB存储引擎实现了在线DDL功能的改进。以下是该功能的主要特点：

• 支持添加辅助索引：可以在运行中的表上添加辅助索引，而不会对整个表进行锁定。
• 支持修改列定义：可以在线修改列的数据类型、长度等定义。
• 支持修改字符集和排序规则：可以在线修改表的字符集和排序规则设置。
• 支持重命名列：可以在不影响正在进行的读写操作的情况下，对表中的列进行重命名。

六、实现原理和优化

在线DDL功能的实现涉及以下关键步骤和优化：

• 1 创建临时表：通过创建临时表来存储将要进行的DDL操作所需的新结构。这样，旧表仍然可用于读写操作。
• 2 数据复制和同步：将旧表中的数据逐步复制到临时表中，并保持旧表数据与临时表数据的同步。这一过程确保了数据在DDL操作期间的完整性和一致性。
• 3 变更捕获和重放：通过使用日志和重做日志等机制，捕获在执行DDL操作期间发生的数据变更，并将其重放到临时表中。这确保了DDL操作完成后数据的一致性。

• 4 最终切换：当DDL操作完成时，数据库引擎将在适当的时机切换到临时表，使其成为新的表结构，并且对新表进行后续的读写操作。

七、使用限制和注意事项

尽管MySQL 5.7的在线DDL功能提供了一种近似在线的体验，但仍然有一些限制和注意事项：

• 并非所有DDL操作都支持在线执行，某些操作仍然需要锁定整个表。

• 在进行DDL操作期间，可能会占用较多的系统资源，因此在高负载时应谨慎使用。
• 进行在线DDL操作时，需要对操作进行充分的评估和测试，以确保数据的完整性和一致性。

八、各版本支持的详细情况

本文数据全部来自 MySQL 官方文档，此处进行一个集中的整理和总结：

https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

九、 DDL 的执行模式

结合上面的表格，对 MySQL 当前 DDL 的执行模式总结如下：

1. INSTANT DDL 是 MySQL 8.0 引入的新功能，当前支持的范围较小，包括：

• 修改二级索引类型
• 新增列
• 修改列默认值
• 修改列 ENUM 值
• 重命名表

2. 在执行 DDL 操作时，MySQL 内部对于 ALGORITHM 的选择策略：

如果用户显式指定了 ALGORITHM，那么使用用户指定的选项；

如果用户未指定，那么如果该操作支持 INPLACE 则优先选择 INPLACE，否则选择 COPY；

当前不支持 INPLACE 的操作主要有：

• 删除主键
• 修改列数据类型
• 修改表字符集

3. 我们常说的 Online DDL，其实是从 DML 操作的角度描述的，如果 DDL 操作不阻塞 DML 操作，那么这个 DDL 就是 Online 的。当前非 Online 的 DDL 其实已经比较少了，主要有：

• 新增全文索引
• 新增空间索引
• 删除主键

• 修改列数据类型
• 指定表字符集
• 修改表字符集

更多详细的示例请参考上面的官方文档的地址。

 

引用地址：

https://developer.aliyun.com/article/1549109