mysql ddl注意_数据库在使用MySQL Online DDL要注意谨慎_数据库_数据分析_数据管理_课课家...

最新推荐文章于 2023-07-24 22:48:41 发布

樊反

最新推荐文章于 2023-07-24 22:48:41 发布

阅读量399

点赞数

文章标签： mysql ddl注意

本文链接：https://blog.csdn.net/weixin_28953877/article/details/113398944

版权

导读

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、最后用一个表格说明几种常见操作的模式

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

新增辅助索引

否

是

否

是

否

删除辅助索引

否

是