mysql online ddl遇到的问题记录

背景

同事在线上执行两条DDL语句的时候，时间相差很大，第一个的DDL甚至执行了5分钟，引起了一些业务报警。而第二个执行了30秒，没有任何报警。

于是我们开始讨论了DDL的时候是否会锁表，block住DML。

分析

经过一番搜索，得到了一下结论。

MySQL从5.6版本开始执行了online ddl， 然后在大部分修改column情况下是支持并发的。

核心是支持了ALTER时候指定算法，支持INPLACE和COPY.

首先判断版本，使用SHOW VARIABLES LIKE "%version%";查看使用的数据库版本，当前是5.7.

其次查看一下online ddl 支持的column 操作:

从这张表中可以看到，对于column data type的修改是不支持并发DML，所以会影响5分钟，但是新增一个column是支持并发DML的。

其次如果执行算法为inplace还需要注意table是在哪个版本build的，如果当前版本是5.6以上，但是要操作的表包含日期类型的column并且是在5.6之前的版本build的话，是不支持inplace算法的，需要先rebuild。

20200804更新:
在新建index的时候，发现即便是online和inplace模式的新建非主键索引，仍然会有短暂的加锁时间。
online ddl具体的过程具体分成三步，在commit阶段肯定会加上metadata的排他锁，其他两个阶段不一定加排他锁。接下来让我们一起看一下怎么加锁的。

Online DDL过程

Phase 1: Initialization

In the initialization phase, the server determines how much concurrency is permitted during the operation, taking into account storage engine capabilities, operations specified in the statement, and user-specified ALGORITHM and LOCK options. During this phase, a shared upgradeable metadata lock is taken to protect the current table definition.

阶段1: 初始化
在初始化这个阶段，server会根据存储引擎的能力、执行的更改类型，用户指定的DDL算法和锁选项，来决定允许多少个并发。在这个阶段中，用一个共享可更新的metadata lock来保护表的定义元数据。

Phase 2: Execution

In this phase, the statement is prepared and executed. Whether the metadata lock is upgraded to exclusive depends on the factors assessed in the initialization phase. If an exclusive metadata lock is required, it is only taken briefly during statement preparation.

阶段2: 执行阶段
在这个阶段，语句将会prepare和execution。上个阶段的共享可更新的metalock，这个阶段是否会升级为exclusive metadata lock，决定于上个阶段的一些因素。即便是exclusive metadata lock，也只是会在prepare阶段加锁。

Phase 3: Commit Table Definition

In the commit table definition phase, the metadata lock is upgraded to exclusive to evict the old table definition and commit the new one. Once granted, the duration of the exclusive metadata lock is brief.

阶段3: 确定更新表定义
在这个阶段，metadata lock将会被更新成排它锁，直到提交新的表定义成功。

资料来自: mysql Online DDL and Metadata Locks

资料:

1. mysql online ddl文档
2. mysql alter table文档
3. MySQL zero downtime schema update without algorithm inplace(解决包含日期类型并且表示5.5版本build的问题)
4. mysql innoDB在线ddl文章