210_mysql_innodb_5_Innodb_online_ddl

网友投稿 577 2022-05-30

官网

https://dev.Mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-generated-column-operations

Mysql online DDL 原始方式

1. 按照表 A 的定义新建一个表 B 2. 对表 A 加写锁 3. 在表 B 上执行 DDL 指定的操作 4. 将 A 中的数据拷贝到 B 5. 释放 A 的写锁 6. 删除表 A 7. 将表 B 重命名为 A 在 2-4 的过程中,如果表 A 数据量比较大,拷贝到表 B 的过程会消耗大量时间,并占用额外的存储空间。此外,由于 DDL 操作占用了表 A 的写锁,所以表 A 上的 DDL 和 DML 都将阻塞无法提供服务

一 online DDL

1.1 Online DDL划分 在mysql 8.0上,对于Online DDL的讨论主要从两个角度进行了分类讨论,

1通过加锁范围来区分不同ddl与dml的并发程度;

2根据是否拷贝数据来划分不同的执行逻辑

SQL 方式

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

如果没有指定ALGORITHM子句,系统决定,选择最优的算法执行DDL。 用户可以选用上述算法来执行,但本身收到DDL类型限制,如果指定的算法无法执行DDL,则ALTER操作会报错

1 锁与并发度划分 Lock

通过LOCK关键字来指定DDL期间加锁程度。其可选择的值如下:

含义

NONE

对DML不加锁, 允许并发查询和DML

SHARED

允许并发查询,但阻塞DML

DEFAULT

由数据库决定选择最大并发的模式,指定该类型与不指定LOCK关键字含义相同

EXCLUSIVE

阻塞查询和DML

默认的情况下,MySQL在执行DDL操作期间尽可能少的使用锁,以提高并发。当然也可以通过LOCK子句,来指定更加严格的锁。但是,如果LOCK子句指定的锁定级别低于特定DDL操作所允许的限制级别,则语句将失败,并出现错误

2 拷贝数据,通过ALGORITHM关键字进行指定,值有如下几种:

含义

COPY

是指DDL时,会生成(临时)新表,将原表数据逐行拷贝到新表中,在此期间会阻塞DML

INPLACE

无需拷贝全表数据到新表,但可能还是需要IN-PLACE方式(原地,无需生成新的临时表)重建整表,过程中允许并发执行DML  这种情况下,在DDL的初始准备和最后结束两个阶段时通常需要加排他MDL锁(metadata lock,元数据锁),除此外,DDL期间不会阻塞DML

l  rebuild涉及表的重建,会在原表路径下生成新的.frm和.ibd文件,同时申请row log空间记录DDL执行期间的DML操作记录,最后再DDL提交阶段重做row log中的内容

l  no-rebuild不涉及表的重建,除了创建添加索引会产生二级索引的写入操作外,其余操作只修改元数据信息,不会生成.ibd文件,并且不会申请row log空间,这种场景消耗IO较少,速度较快

INSTANT

instant:该特性是MySQL8.0.12引入,只修改数据字典的元数据信息,无需拷贝数据也无需重建表,原表数据不受影响。整个DDL过程执行非常快,不会阻塞DML操作

DEFAULT

系统决定,选择最优的算法执行DDL

1.2 执行流程  Online DDL执行过程可以分为三个阶段:

alter操作MDL锁流程(copy方式 改主键/数据类型等) 1) Opening tables阶段,加共享锁 a) 加MDL_INTENTION_EXCLUSIVE锁 b) 加MDL_SHARED_UPGRADABLE锁,升级到MDL_SHARED_NO_WRITE SNRW锁 2) 操作数据,copy data,流程如下: a) 创建临时表tmp,重定义tmp为修改后的表结构 b) 从原表读取数据插入到tmp表 3) 将MDL_SHARED_NO_WRITE读锁升级到MDL_EXCLUSIVE锁 a) 删除原表,将tmp重命名为原表名 4) 提交阶段,释放MDL锁 a) 释放MDL_INTENTION_EXCLUSIVE锁 b) 释放MDL_EXCLUSIVE锁 Online DDL-- inplace 第一阶段 : Prepare阶段 创建新的临时frm文件(与InnoDB无关) 持有EXCLUSIVE-MDL X锁,禁止读写 根据alter类型,确定执行方式(copy,online-rebuild,online-norebuild) 假如是Add Index,则选择online-norebuild即INPLACE方式 更新数据字典的内存对象 分配row_log对象存储空间记录增量(仅rebuild类型需要,记录在row_log里记录执行阶段的记录增量) 生成新的临时ibd文件(仅rebuild类型需要) 第二阶段: ddl执行阶段 降级EXCLUSIVE-MDL锁为 MDL_SHARED_UPGRADABLE(SU) 允许读写 扫描old_table的聚集索引每一条记录rec 遍历新表的聚集索引和二级索引,逐一处理 根据rec构造对应的索引项 将构造索引项插入sort_buffer块排序 将sort_buffer块更新到新的索引上 记录ddl执行过程中产生的增量(仅rebuild类型需要) 重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的) 重放row_log间产生dml操作append到row_log最后一个Block 第三阶段: commit阶段 当前Block为row_log最后一个时,禁止读写,升级到EXCLUSIVE-MDL X锁 重做row_log中最后一部分增量 更新innodb的数据字典表 提交事务(刷事务的redo日志) 修改统计信息 rename临时idb文件,frm文件 变更完成

1.3 online ddl 支持范围

INSTANT DDL 是 MySQL 8.0 引入的新功能,当前支持的范围较小,

修改二级索引类型

新增列

修改列默认值

修改列 ENUM 值

重命名表

非online DDL

新增全文索引

新增空间索引

删除主键

修改列数据类型

指定表字符集

修改表字符集

操作

Instant

In Place

重建表

可并行DML

只修改元数据

新增辅助索引

删除辅助索引

修改索引名

新增主键

删除主键

删除同时新增主键

新增字段

是(追加)

删除字段

修改字段类型

扩展varchar长度

Yes

新增STORED虚拟列

YES

新增VIRTUAL虚拟列

转换字符集

Optimize table

修改表名

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

总表汇总

操作

版本

INSTANT

INPLACE

Rebuild

并发 DML

Only Modifies Metadata

二级索引

创建二级索引

MySQL 8.0

No

Yes

No

Yes

No

MySQL 5.7

Yes

No

Yes

No

MySQL 5.6

Yes

No

Yes

No

删除索引

MySQL 8.0

No

Yes

No

Yes

Yes

MySQL 5.7

Yes

No

Yes

Yes

MySQL 5.6

Yes

No

Yes

Yes

重命名索引

MySQL 8.0

No

Yes

No

Yes

Yes

MySQL 5.7

Yes

No

Yes

Yes

MySQL 5.6

增加全文索引

MySQL 8.0

No

Yes*

No*

No

No

MySQL 5.7

Yes*

No*

No

No

MySQL 5.6

Yes*

No*

No

No

增加空间索引

MySQL 8.0

No

Yes

No

No

No

MySQL 5.7

Yes

No

No

No

MySQL 5.6

修改索引类型

MySQL 8.0

Yes

Yes

No

Yes

Yes

MySQL 5.7

Yes

No

Yes

Yes

MySQL 5.6

Yes

No

Yes

Yes

主键

操作

版本

INSTANT

INPLACE

Rebuild

并发 DML

Only Modifies Metadata

增加主键

MySQL 8.0

No

Yes*

Yes*

Yes

No

MySQL 5.7

Yes*

Yes*

Yes

No

MySQL 5.6

Yes*

Yes*

Yes

No

删除主键

MySQL 8.0

No

No

Yes

No

No

MySQL 5.7

No

Yes

No

No

MySQL 5.6

No

Yes

No

No

重建主键

MySQL 8.0

No

Yes

Yes

Yes

No

MySQL 5.7

Yes

Yes

Yes

No

MySQL 5.6

Yes

Yes

Yes

No

列操作

操作

版本

INSTANT

INPLACE

Rebuild

并发 DML

Only Modifies Metadata

新增列

MySQL 8.0

Yes*

Yes

No*

Yes*

No

MySQL 5.7

Yes

Yes

Yes*

No

MySQL 5.6

Yes

Yes

Yes*

No

删除列

MySQL 8.0

No

Yes

Yes

Yes

No

MySQL 5.7

Yes

Yes

Yes

No

MySQL 5.6

Yes

Yes

Yes

No

重命名列

MySQL 8.0

No

Yes

No

Yes*

Yes

MySQL 5.7

Yes

No

Yes*

Yes

MySQL 5.6

Yes

No

Yes*

Yes

调整列顺序

MySQL 8.0

No

Yes

Yes

Yes

No

MySQL 5.7

Yes

Yes

Yes

No

MySQL 5.6

Yes

Yes

Yes

No

修改列默认值

MySQL 8.0

Yes

Yes

No

Yes

Yes

MySQL 5.7

Yes

No

Yes

Yes

MySQL 5.6

Yes

No

Yes

Yes

修改列数据类型

MySQL 8.0

No

No

Yes

No

No

MySQL 5.7

No

Yes

No

No

MySQL 5.6

No

Yes

No

No

扩展 VARCHAR 长度

MySQL 8.0

No

Yes

No

Yes

Yes

210_mysql_innodb_5_Innodb_online_ddl

MySQL 5.7

Yes

No

Yes

Yes

MySQL 5.6

删除列默认值

MySQL 8.0

Yes

Yes

No

Yes

Yes

MySQL 5.7

Yes

No

Yes

Yes

MySQL 5.6

Yes

No

Yes

Yes

修改自增值

MySQL 8.0

No

Yes

No

Yes

No*

MySQL 5.7

Yes

No

Yes

No*

MySQL 5.6

Yes

No

Yes

No*

修改列为空

MySQL 8.0

No

Yes

Yes*

Yes

No

MySQL 5.7

Yes

Yes*

Yes

No

MySQL 5.6

Yes

Yes*

Yes

No

修改列为非空

MySQL 8.0

No

Yes*

Yes*

Yes

No

MySQL 5.7

Yes*

Yes*

Yes

No

MySQL 5.6

Yes*

Yes*

Yes

No

修改列 ENUM 值

MySQL 8.0

Yes

Yes

No

Yes

Yes

MySQL 5.7

Yes

No

Yes

Yes

MySQL 5.6

Yes

No

Yes

Yes

表操作

操作

版本

INSTANT

INPLACE

Rebuild

并发 DML

Only Modifies Metadata

修改 ROW_FORMAT

MySQL 8.0

No

Yes

Yes

Yes

No

MySQL 5.7

Yes

Yes

Yes

No

MySQL 5.6

Yes

Yes

Yes

No

修改 KEY_BLOCK_SIZE

MySQL 8.0

No

Yes

Yes

Yes

No

MySQL 5.7

Yes

Yes

Yes

No

MySQL 5.6

Yes

Yes

Yes

No

指定字符集

MySQL 8.0

No

Yes

Yes*

No

No

MySQL 5.7

Yes

Yes*

No

No

MySQL 5.6

Yes

Yes*

No

No

修改字符集

MySQL 8.0

No

No

Yes*

No

No

MySQL 5.7

No

Yes*

No

No

MySQL 5.6

No

Yes

No

No

OPTIMIZE 表

MySQL 8.0

No

Yes*

Yes

Yes

No

MySQL 5.7

Yes*

Yes

Yes

No

MySQL 5.6

Yes*

Yes

Yes

No

重命名表

MySQL 8.0

Yes

Yes

No

Yes

Yes

MySQL 5.7

Yes

No

Yes

Yes

MySQL 5.6

Yes

No

Yes

Yes

MySQL

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

上一篇:springboot整合spring Cache(redis)
下一篇:华为云:边缘AI方案落地问题探讨及调研
相关文章