MySQL Online DDL，还是要谨慎

MySQL Online DDL，还是要谨慎

导读

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

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

增加新列(ADD COLUMN)修改列定义(MODIFY COLUMN)增加/删除索引(ADD/DROP INDEX)

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

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

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

COPY ，是指DDL时，会生成(临时)新表，将原表数据逐行拷贝到新表中，在此期间会阻塞DMLINPLACE，无需拷贝全表数据到新表，但可能还是需要IN-PLACE方式(原地，无需生成新的临时表)重建整表。这种情况下，在DDL的初始准备和最后结束两个阶段时通常需要加排他MDL锁(metadata lock，元数据锁)，除此外，DDL期间不会阻塞DMLINSTANT，只需修改数据字典中的元数据，无需拷贝数据也无需重建整表，同样，也无需加排他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、最后用一个表格说明几种常见操作的模式

操作InstantIn Place重建表可并行DML只修改元数据

新增辅助索引 否 是 否 是 否 删除辅助索引 否 是 否 是 是 修改索引名 否 是 否 是 是 新增主键 否 是 是 是 否 删除主键 否 否 是 否 否 删除并同时新增主键 否 是 是 是 否 新增字段 是（追加式） 是 否 是 否 删除字段 否 是 是 是 否 修改字段数据类型 否 否 是 否 否 扩展VARCHAR列长度 否 是 否 是 是 新增STORED虚拟列 否 否 是 否 否 新增VIRTUAL虚拟列 是 是 否 是 是 转换表字符集 否 否 是 否 否 opitmize table 否 是 是 是 否 修改表名 是 是 否 是 是

最后有两个提醒

一般DDL操作最好都采用pt-osc或gh-ost这样的工具来实施，并且实施之前务必要先检查当前目标表上是否有事务或大查询未结束，避免严重的MDL锁等待

除了8.0以上版本，除了追加式新增列、表改名、新增虚拟列这三种支持INSTANT的操作可以直接跑DDL，其余的都统统采用pt-osc/gh-osc工具，相对更不容易出状

执行ALTER TABLE DDL时，不要节外生枝指定ALGORITHM=?, LOCK=?选项，因为MySQL会自行判断该采用哪种方式。本来可以INPLACE的，可能不小心给指定成COPY就悲剧了

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。