【MySQL】Online DDL详解

目录

• 前言
• 一、分类
• 二、Copy
• 三、Inplace
• 四、Instant
• 五、一些补充
• 六、总结

一天，一位许久未见的澳同学，一见面先不是打招呼，直接给我当面一问
澳同学问我：小涛小涛，我这一个表有1T的数据，这时我想给这个表加一个列，如果才能最大程度不影响业务呢？
此刻，我陷入了沉思，赶紧去学习学习

在MySQL5.6版本之后，对于部分alter table，加入新的执行算法，可以进行DDL时，“并行”现有业务（DML操作）。
可以通过aLter table 时添加 ALGORITHM参数控制使用算法。

#例如这种写法alter  table  t1 add a int ALGORITHM=?;

目前可以支持的主流算法有三种：
COPY —— MySQL 5.6之前非Online，都是执行这种算法
INPLACE —— MySQL 5.6出现的
INSTANT —— MySQL 8.0.12出现的

这时就有疑问了，这么多算法头都大了

别急，小涛同学这就来通俗易懂的解答一下这几种算法：
我们知道，把澳同学关进冰箱需要三步：打开、放入、关门。

而DDL操作，在执行时，不管何种算法，都会经历三个阶段：准备阶段【prepare】、执行阶段【DDL】、提交阶段【commit】。不同之处是在三个阶段中分别作了不同的处理，下面咱们来好好聊聊。

一听名词就秒懂，单纯的拷贝忍者。

名词解释：指DDL时，会生成（临时）新表，将原表数据逐行拷贝到新表中，在此期间会阻塞DML,offline(离线的而非在线的)例如执行此语句：alter table t1 add age a int;三个阶段：准备、执行、提交。准备： 1、对表加元数据共享锁，读取frm元数据（此时DDL不能并行，DML可以并行）2、共享锁升级为排他锁；（此时DDL、DML都不能并行）3、在Server层通过create like语句，创建临时表，Engine层也生成对应ibd、frm文件（8.0之后没.frm文件）执行：1、修改临时表元数据(加列)2、拷贝原表数据到临时表【最耗时，1T的数据一行一行copy】3、删除原表及文件4、重命令临时表及文件提交：1、提交事务，释放锁

澳同学：这个好理解，但现在却不实用吧，因为在整个流程期间，都是加上锁的，此时既不能写入数据，也不能查询数据，容易造成数据库堵塞
卑微涛：嗯嗯，真聪明
澳同学：那怎么个Oline DDL啊，你是不是在忽悠我啊🤔
卑微涛：别急，这不是先易后难吗，下面介绍下一种算法

在MySQL5.6 出现的新算法，无需拷贝全表数据到新表，但可能还是需要IN-PLACE方式（原地，无需生成新的临时表）重建整表。这种情况下，在DDL的初始准备和最后结束两个阶段时通常需要加排他MDL锁（metadata lock，元数据锁），除此外，DDL期间不会阻塞DML。

准备：1、对表加元数据共享升级锁，并升级为排他锁；（此时DML不能并行）2、判断使用inplace算法3、判断语句是“rebuild table” 还是 “no-rebuild”，rebuild 在原表所在的路径下创建.frm和.ibd临时中转文件；【在引擎层克隆，而不是像copy那样，在server层创建（create like）】注意：加列的操作是需要rebuild table的【rebuild table】---生成中转文件表++++no-rebuild的情况：除创建二级索引外只创建.frm文件，其中添加二级索引操作最为特殊，该操作属于no-rebuild不会生成.ibd，但实际上对.ibd文件却做了修改，该操作会在参数tmpdir指定路径下生成临时文件，用于存储索引排序结果，然后再合并到.ibd文件中++++4、申请row log空间，用于存放DDL执行阶段产生的DML操作。（no-rebuild不需要）【在innodb_sort_buffer块中】执行：（online）1、释放排他锁，保留元数据共享升级锁；（此时DML可以并行）2、扫描原表主键以及二级索引的所有数据页，生成 B+ 树，存储到临时文件中；【在引擎层扫描，最耗时】3、将所有对原表的DML操作记录在日志文件row log中，并回放部分row_log。提交阶段：1、升级元数据共享升级锁，产生排他锁锁表；（此时DML不能并行了）2、重做row log中的内容；（no-rebuild不需要）3、重命名原表文件，将临时文件改名为原表文件名，删除原表文件；4、提交事务，变更完成。

注意：

• 在DDL期间产生的数据，会按照正常操作一样，写入原表，记redolog、undolog、binlog，并同步到从库去执行，只是额外会记录在row log中，并且写入row log的操作本身也会记录redolog
• 而在提交阶段才进行row log重做，此阶段会锁表，此时主库（新表空间+row log）和从库（表空间）数据是一致的，在主库DDL操作执行完成并提交，这个DDL才会写入binlog传到从库执行，在从库执行该DDL时，这个DDL对于从库本地来讲仍然是online的，也就是在从库本地直接写入数据是不会阻塞的，也会像主库一样产生row log。
• 但是对于主库同步过来DML，此时会被阻塞，是offline的，DDL是排他锁的在复制线程中也是一样，所以不只会阻塞该表，而是后续所有从主库同步过来的操作（主要是在复制线程并行时会排他，同一时间只有他自己在执行）。所以大表的DDL操作，会造成同步延迟。

此时，澳同学又提问了：不错，那为什么是Online呢？
卑微涛：虽然三个阶段（准备、执行、提交）中，有两个阶段（准备、提交）都无法进行CRUD，但实际上，整个DDL中执行阶段时间占比最长，例如30分钟的DDL，准备+提交阶段只占用1分钟的时间，剩下的29分钟都在执行，那么对于业务层来说，绝大部分时间都是能正常访问的，所以就做到了Oline DDL了。
澳同学：诶，你这不是还要1分钟会堵塞嘛，我就是想在那一分钟插入大批量数据呢，你这瓜不熟啊🤪

卑微涛：行行行，那下面介绍下一种算法

只需修改数据字典中的元数据，无需拷贝数据也无需重建整表，同样，也无需加排他MDL锁，原表数据也不受影响。整个DDL过程几乎是瞬间完成的，也不会阻塞DML。

这个新特性是8.0.12引入的（腾讯DBA团队贡献）。执行DDL操作时，ALGORITHM选项可以不指定，这时候MySQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT，也是同样的效果。如果指定了ALGORITHM选项，但不支持的话，会直接报错。

当采用COPY模式时，这时表里任何的修改数据操作，DDL都会被阻塞。COPY模式下会生成临时新表，操作完成后原表会被删除，新表被重命名为原表名。当DDL开始后，原表上仅能只读，其他的DML操作也都会被阻塞。COPY过程中，唯一会阻塞只读的时机是在清理旧表结构和表定义缓存时。

MySQL 8.0.12 才提出的新算法，目前只支持添加列等少量操作（还不是太成熟，企业中一般都是5.6、5.7版本），利用 8.0 新的表结构设计，可以直接修改表的 metadata 数据，省掉了 rebuild 的过程，极大的缩短了 DDL 语句的执行时间。

澳同学：那支持 Inplace算法的 DDL 一定是 Online 的吗？
1、从概念上来说，INPLACE 和 Online 是两个不同维度的事情；
2、Ccopy 和Inplace指的是DDL内部的执行算法，可以理解成：COPY 是在 Server层（服务层）的操作，INPLACE 是在 InnoDB层（存储层）的操作；
3、其实，对于用户来说，关心 Online 与否，通常只与一个问题有关：是否允许并发 DML；
4、用一句哲学经典术语：Copy 算法执行的 DDL肯定不是 Online 的，Inplace 算法执行的 DDL 不一定是 Online 的。

COPY —— 5.6之前默认适应这种方法，全程无法并行DML
INPLACE —— MySQL 5.6出现的，在开始和提交的短租时间里，不能并行DML
INSTANT —— MySQL 8.0.12出现的，唯一会阻塞只读的时机是在清理旧表结构和表定义缓存时。

以上纯属节目效果，澳同学可是我的铁哥们，一起毕业了😄
这次，也是真的毕业了，大学四年时间过得真快
＂所以，别回头，迈步离开吧。
我大概会怀念林荫道斑驳的树影，
怀念撒在南区篮球场的斜阳，
怀念夜里的灯，
雨里的伞，
清晨的雾，
我大概会怀念每一声笑和每一滴泪。
我原想收获一缕春风，你们却给了我整个春天；
我原想撷取一枚红叶，你们却给了我整片枫林＂​
我是励志成为一名优秀DBA的小涛同学，有疑问的小伙伴欢迎在评论区讨论，咱们一起努力，冲冲冲！咱们下篇博文再见了~

来源地址：https://blog.csdn.net/weixin_45238761/article/details/125343029