- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

210_mysql_innodb_5_Innodb_online_ddl

alexsully 发表于 2021/10/27 20:27:27 2021/10/27

【摘要】 online ddl

官网

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
	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

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

210_mysql_innodb_5_Innodb_online_ddl

一 online DDL

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

1 锁与并发度划分 Lock

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

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

1.3 online ddl 支持范围

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

非online DDL

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

210_mysql_innodb_5_Innodb_online_ddl

一 online DDL

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

1 锁与并发度划分 Lock

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

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

1.3 online ddl 支持范围

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

非online DDL

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

推荐阅读

相关产品

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