MySQL Online DDL，还是要谨慎



导读

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

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

• 增加新列（ADD COLUMN）

• 修改列定义（MODIFY COLUMN）

• 增加/删除索引（ADD/DROP INDEX）

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

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

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

• COPY ，是指DDL时，会生成（临时）新表，将原表数据逐行拷贝到新表中，在此期间会阻塞DML

• INPLACE，无需拷贝全表数据到新表，但可能还是需要IN-PLACE方式（原地，无需生成新的临时表）重建整表。 这种情况下，在DDL的初始准备和最后结束两个阶段时通常需要加排他MDL锁（metadata lock，元数据锁），除此外，DDL期间不会阻塞DML

• INSTANT，只需修改数据字典中的元数据， 无需拷贝数据也无需重建整表 ，同样，也无需加排他MDL锁，原表数据也不受影响。 整个DDL过程几乎是瞬间完成的，也不会阻塞DML。 这个新特性是8.0.12引入的，再次感谢腾讯互娱DBA团队的贡献

执行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技术分享