230_mysql_binlog结构组成（mysql binlog详解）
606
2022-05-30
210_mysql_innodb_5_Innodb_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
MySQL
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
推荐文章
最近发表
