mysql — 生产环境发布DDL之避坑操作onlineDDL

一、Mysql onLineDDL特性

1、Mysql 5.6 DDL

MySQL 的 DDL(Data Definition Language) 包括增减字段、增减索引等操作。MySQL Online DDL 功能从 5.6 版本开始正式引入，发展到现在的 8.0 版本，那么在 MySQL 5.6 之前，MySQL 的 DDL 操作会按照原来的表复制一份，并做相应的修改，例如，对表 A 进行 DDL 的具体过程如下：

按照表 A 的定义新建一个表 B
对表 A 加写锁
在表 B 上执行 DDL 指定的操作
将 A 中的数据拷贝到 B
释放 A 的写锁
删除表 A
将表 B 重命名为 A

如果表 A 数据量比较大，拷贝到表 B 的过程会消耗大量时间，并占用额外的存储空间。此外，由于 DDL 操作占用了表 A 的写锁，所以表 A 上的 DDL 和 DML 都将阻塞无法提供服务，如果在生产环境中执行此类ddl语句，那么UIOC事件必然大概率会发生。

因此，MySQL 5.6 增加了 Online DDL，允许在不中断数据库服务的情况下进行 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指的是在DDL期间，允许用户进行DML操作。并非所有DDL操作都支持Online DDL，官方文档 https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html 详细展示了所有DDL在执行期间是否允许进行DML操作。

2、Online DDL优势

• 提高响应性与可用性,减少锁等待
• 减少磁盘I/O与CPU负载
• 减少了缓冲池的使用

优势就不用再次强调,在生产环境中,数据量大的情况是不能直接用DDL,造成锁表的几率还是比较高的。

3、onlineDDL用法

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

ALTER 语句中可以指定参数 ALGORITHM 和 LOCK ，分别用于指定 DDL 执行的方式和 DDL 期间 DML 的并发控制算法。

ALGORITHM：指定 DDL 执行的方式

• ALGORITHM=INPLACE 表示执行DDL的过程中不发生表拷贝，过程中允许并发执行DML（INPLACE不需要像COPY一样占用大量的磁盘I/O和CPU，减少了数据库负载。同时减少了buffer pool的使用，避免 buffer pool 中原有的查询缓存被大量删除而导致的性能问题）。

• ALGORITHM=COPY，DDL 就会按 MySQL 5.6 之前的方式，采用表拷贝的方式进行，过程中会阻塞所有的DML。另外也可以设置 ALGORITHEM=DAFAULT，让 MySQL 以尽量保证 DML 并发操作的原则选择执行方式。这里还有一个区别是：COPY 是在 Server 层的操作，INPLACE 是在 InnoDB 层的操作。

• ALGORITHM=INSTANT，INSTANT DDL 是 MySQL 8.0 引入的新功能，当前支持的范围较小，包括：修改二级索引类型、新增列、修改列默认值、修改列 ENUM 值、重命名表。

LOCK：指定DDL操作时,所用的锁级别

默认情况下，MySQL 在 DDL 操作期间使用尽可能少的锁定.但是我们可以通过LOCK字段来精确控制DDL的锁级别.注意,如果当前DDL操作需要的锁级别达不到要求,那么将无法执行,比如某些特殊场景操作一定要锁表的。这个key提供的几个value如下所示:

• NONE:允许并发查询和DML.表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。此外还有 EXCLUSIVE（持有排它锁，阻塞所有的请求，适用于需要尽快完成DDL或者服务库空闲的场景）、SHARED（允许SELECT，但是阻塞INSERT UPDATE DELETE，适用于数据仓库等可以允许数据写入延迟的场景）和 DEFAULT（根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值）

• SHARED:只允许并发查询.

• DEFAULT:尽量可能多的并发,这是这个key的缺省参数.如果确认DDL的操作不会对业务造成影响的时候会使用这个参数.

• EXCLUSIVE:阻塞并发查询和DML.使用这个选项相等于就是锁表更新了,这样会使DDL操作起来会更快.

4、onlineDDL执行过程

DDL执行的三个阶段,了解这些原理可以让我们更好的理解DDL是怎么执行的:

1. 初始化

在初始化阶段，服务器会根据存储引擎能力、语句中指定的操作以及用户指定ALGORITHM和LOCK 选项来确定操作期间允许的并发量 。在此阶段，使用共享的可升级元数据锁来保护当前表定义。

2. 执行

在这个阶段中,语句准备和执行,根据初始化阶段的预判考虑是否使用排他锁。

3. 提交表定义

在提交表定义阶段，元数据锁升级为独占锁,然后进行新旧表的元数据信息替换。元数据独占锁占用的时间会比较少。

二、总结

Online DDL 的方式是可以考虑在业务低峰期使用的，而 MySQL 5.5 及之前的版本，这个命令是会阻塞 DML 的，需要特别小心。
Online DDL 使用的是INPLACE算法，有rebuild table 和 no-rebuild table两种方式，INPLACE的rebuild table方式和COPY的rebuild table方式类似，都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，要很小心地控制操作时间。如果想要比较安全的操作的话，可以使用 GitHub ghost 来做。INPLACE的rebuild table方式和COPY的rebuild table方式一个不同点在于 INPLACE的rebuild仍然可以达到与DML操作并发执行，从而达到"online"的状态 - Online DDL过程会使用到MDL的读锁和写锁。

当我们需要执行Online DDL的时候我们可以根据以下步骤来评估该操作对生产环境的影响:

• 准备好DDL语句
• 根据DDL语句查阅算法表,看下当前的DDL语句是否支持INPLACE。
• 在测试环境做好测试,观察结果
• 评估内存空间与磁盘空间是否不足
• 执行操作,并观察业务情况