MySQL Online DDL ALGORITHM区别

导读：MySQL的Online DDL长期饱受诟病，8.0之后有没有好一些呢…

本文重点讨论常见的几种Online DDL需求：

• 增加新列（ADD COLUMN）
• 修改列定义（MODIFY COLUMN）
• 增加/删除索引（ADD/DROP INDEX）

其他的DDL操作相对比较少，所以本文就不讨论了。

此外，本文也不讨论非InnoDB引擎以及非普通索引（如全文索引、空间索引）的场景。

我们先看下ALTER TABLE时ALGORITHM可以指定的几种方式：

1. COPY ，是指DDL时，会生成（临时）新表，将原表数据逐行拷贝到新表中，在此期间会阻塞DML
2. INPLACE，无需拷贝全表数据到新表，但可能还是需要IN-PLACE方式（原地，无需生成新的临时表）重建整表。这种情况下，在DDL的初始准备和最后结束两个阶段时通常需要加排他MDL锁（metadata lock，元数据锁），除此外，DDL期间不会阻塞DML
3. INSTANT，只需修改数据字典中的元数据，无需拷贝数据也无需重建整表，同样，也无需加排他MDL锁，原表数据也不受影响。整个DDL过程几乎是瞬间完成的，也不会阻塞DML。这个新特性是8.0.12引入的。

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

当采用COPY模式时，这时表里任何的修改数据操作，DDL都会被阻塞。COPY模式下会生成临时新表，操作完成后原表会被删除，新表被重命名为原表名。当DDL开始后，原表上仅能只读，其他的DML操作也都会被阻塞。COPY过程中，唯一会阻塞只读的时机是在清理旧表结构和表定义缓存时。

1、以下是支持INPLACE模式的几种情况：

• 本身是InnoDB表支持的online DDL操作
• 表重命名
• 以下几种只修改表元数据而不修改表数据的操作
• 字段类型为CHAR、VARCHAR、TEXT、ENUM
• 字符集从UTF8MB3变成UTF8MB4，或者从其他任何字符集改成binary
• 修改字符集的字段上没有索引
• 字段重命名
• 修改字段默认值
• 索引重命名
• 新增、删除辅助索引
• 修改索引的可见性
• ENUM/SET类型字段追加新值，例如(‘a’,‘b’)后面增加’c’，变成(‘a’,‘b’,‘c’)
• 从8.0.14开始，下面几种只修改字符集的情况
• 从8.0.14开始，InnoDB表虚拟列（generated column）上不修改定义类型、表达式、是否允许为NULL约束，例如只修改字段备注内容（特么的这有卵用啊）
• 修改字段的默认值，且该字段不包含在生成虚拟列的表达式内，例如只修改字段的NULL属性

2、以下是支持INSTANT模式的几种情况：

• 在表最后新增一个字段
• 新增或删除虚拟列
• 新增或删除字段默认值
• 修改ENUM/SET字段定义，上面也有提到过
• 修改索引类型
• 表重命名

3、几种需要用COPY模式的情况
当执行ALTER TABLE ADD COLUMN、CHANGE COLUMN、MODIFY COLUMN、ADD INDEX、FORCE 等操作时，会将5.5版本之前的时间类型相关字段强制升级到高版本，这个升级需要重建整个表，只能用COPY方式。这时如果指定 ALGORITHM=INPLACE 就会报错了。

当有联合索引并用于表分区时，如果修改了联合索引列顺序的话，也需要用COPY模式。

4、最后用一个表格说明几种常见操作的模式

操作	Instant	In Place	重建表	可并行DML	只修改元数据
新增辅助索引	否	是	否	是	否
删除辅助索引	否	是	否	是	是
修改索引名	否	是	否	是	是
新增主键	否	是	是	是	否
删除主键	否	否	是	否	否
删除并同时新增主键	否	是	是	是	否
新增字段	是（追加式）	是	否	是	否
删除字段	否	是	是	是	否
修改字段数据类型	否	否	是	否	否
扩展VARCHAR列长度	否	是	否	是	是
新增STORED虚拟列	否	否	是	否	否
新增VIRTUAL虚拟列	是	是	否	是	是
转换表字符集	否	否	是	否	否
opitmize table	否	是	是	是	否
修改表名	是	是	否	是	是

最后有两个提醒

1. 一般DDL操作最好都采用pt-osc或gh-ost这样的工具来实施，并且实施之前务必要先检查当前目标表上是否有事务或大查询未结束，避免严重的MDL锁等待
2. 除了8.0以上版本，除了追加式新增列、表改名、新增虚拟列这三种支持INSTANT的操作可以直接跑DDL，其余的都统统采用pt-osc/gh-osc工具，相对更不容易出状
3. 执行ALTER TABLE DDL时，不要节外生枝指定ALGORITHM=?, LOCK=?选项，因为MySQL会自行判断该采用哪种方式。本来可以INPLACE的，可能不小心给指定成COPY就悲剧了

延伸阅读
MySQL Reference Manual, 15.12.1 Online DDL Operations
MySQL Reference Manual, 13.1.9 ALTER TABLE Syntax, Performance and Space Requirements
MySQL 8.0: InnoDB now supports Instant ADD COLUMN | MySQL Server Blog
杨奇龙：MySQL Online DDL思维导图 最后，欢迎扫码订阅《乱弹MySQL》专栏，快人一步获取我最新的MySQL技术分享