国内最专业的IT技术学习网

Mysql数据库

当前位置:主页 > Mysql数据库 >

MySQL Online DDL,还是要谨慎

发布时间:2019/08/28标签:   数据库      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时,会生成(临时)新表,将原表数据逐行拷贝到新表中,在此期间会阻塞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模式时,这时表里任何的修改数据操作,芜湖ui设计师,DDL都会被阻塞。COPY模式下会生成临时新表,郑州seo公司,操作完成后原表会被删除,新表被重命名为原表名。当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字段定义,ui设计苹方体,上面也有提到过

修改索引类型

表重命名

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            
修改表名            

最后有两个提醒

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

版权信息Copyright © IT技术教程 版权所有    ICP备案编号:鲁ICP备09013610号