mysql onlineDDL介绍

背景

在实际项目中，我们经常需要对数据库进行变更。增加一个字段、增加一个索引、变更列长度等这些是我们经常执行的DDL。对于正在运行的大表来说，执行DDL耗时久、负载高、空间占用高，一不小心锁表则会造成严重的生产问题。mysql5.6之前的版本执行DDL使用copy算法：执行DDL过程中不能进行并发读写；mysql5.6开始引入inplace算法，DDL仅出现短暂的不能读写情况，因此对大表修改支持online ddl，但是并非所有的DDL都支持inplace算法。

各类算法介绍

5.5以下的mysql使用copy算法：mysql会建立一个临时表，把数据写入临时表，在此期间无法进行写操作，再用临时表替换原表。
5.6以上则使用inplace算法：核心思路分三步：

1. 准备阶段，算法根据你所使用的DDL判断是否需要重建表(或是在原表操作)，持有MDL锁(metadata lock)（如果此时有其他事务持有MDL锁，则该阶段会阻塞），禁止读写。
2. 执行阶段，该阶段降级MDL，允许进行读写。
3. 提交阶段，当快结束时，再升级MDL（如果此时有其他事务持有MDL锁，则该阶段会阻塞），禁止读写，提交事务，最后释放MDL。

因此，如果大表执行ddl使用copy算法，就会造成数据库不可用，会出大问题！而inplace算法只有短暂的禁止读写，是可接受的。但是并非所有你执行的DDL都可以使用inplace算法，接下来介绍几个常用的语句的算法使用情况。

常用DDL情况分析

以mysql 5.7版本为例，其他版本(或其他DDL)可查看官网mysql onlineDDL官网，列举项目中常用的DDL，其中带“星号”表示存在限制条件。

操作 inplace算法 重建表 运行可并发读写 (并发DML) 只修改元数据(meta-data)
创建索引 yes no yes no
添加列 yes yes yes* no
修改列名 yes no yes* yes
删除列 yes yes yes no
扩展varchar长度 yes* no yes yes
设置列默认值 yes no yes yes
注：元数据表示修饰表数据的数据，例如表名/字段名/字段长度等，部分DDL操作会修改这些数据。

重点介绍一下带星号的情况，因为这种情况下可能会运用了copy算法，导致锁表，无法并发读写，造成服务不可用。
1.添加列：添加自增列时不允许并发DML。
2. 修改列名时同时修改类型无法并发DML，如果将字段从int 转 varchar则报错，无法使用inplace算法。计算列(generated column generated column 定义)无法使用inplace算法修改列名。

ALTER TABLE test CHANGE test7 test19 VARCHAR(10), ALGORITHM=INPLACE, LOCK=NONE。(从int 转为varchar)
执行报错： ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

3.扩展varchar字段长度这个需要特别注意，有两个限制场景。① varchar只能从[0,255]和[256,∞]字节直接变更，即inplace算法不适用于255 ->256 bytes。②varchar长度不能减少，即不能从200字节减到199字节。

– varchar中是3个字节一个字符，因此255字节即varchar(85)，从varchar(85)-varchar(86) 即从255byte 到256byte
– 下例为 runoob_title 列此时为varchar(85)
ALTER TABLE test MODIFY runoob_title VARCHAR(86), ALGORITHM=INPLACE, LOCK=NONE;
– 执行报错:ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

– 如果将85字符->2字符，也是不能执行的
ALTER TABLE test MODIFY runoob_title VARCHAR(2), ALGORITHM=INPLACE, LOCK=NONE;
报错： ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

建议

1.在执行ddl的时候，可以查看一下自己mysql版本对应的DDL的执行算法，见 mysql-ddl-operations。
2.执行ddl可以加上语句，ALGORITHM=INPLACE, LOCK=NONE，可以在测试环境测试，如果报错说明无法使用onlineddl。
3.可以利用云上现有的工具ghost执行onlineDDL。https://confluence.sf-express.com/pages/viewpage.action?pageId=32894763