MySQL--Online DDL三种算法原理详解

目录

一、COPY算法

二、INPLACE算法

三、INSTANT算法

四、总结

五、关于Online DDL的误区问题

---

Online DDL介绍：

        在5.6版本之后，对于部分alter table，加入新的执行算法，使得在进行DDL时，可以并行有DML操作，通过Alter Table时加入ALOGRITHM参数控制使用算法。目前可以支持的算法有三种：

        COPY 算法

        INPLACE 算法

        INSTANT 算法

        DDL操作，在执行时，不管何种算法，都会经历三个阶段：准备阶段，执行阶段，提交阶段。不同之处是在三个阶段中分别做了哪些处理。

一、COPY算法

        指DDL时，会生成临时新表，将原表数据逐行拷贝到新表中，在此期间会阻塞DML，是offline的。

1.准备阶段

        1.对表加元数据共享锁，读取fm元数据

        2.共享锁升级为排它锁，禁止其他update，delete操作

        3.在server层通过create like语句，创建临时表，引擎层也生成对应ibd，frm文件

2.执行、提交阶段

        1.修改临时表元数据（执行DDL操作）

        2.拷贝元数据到临时表（此操作耗时最长）

        3.重命名临时表和文件

        4.删除原表和文件

        5.提交事务，释放锁

3.COPY总结

        当采用copy模式时，这时表里的任何修改数据操作，DDL都会被阻塞，COPY模式下会生成临时新表，操作完成后原表会被删除，新表被重命名为原表名。当DDL开始后，原表上仅能只读，其他的DML操作都会被阻塞。

二、INPLACE算法

       无需拷贝全表数据到新表，但可能还是需要IN-PLACE（原地，无需生成新的临时表）重新建表。

        INPLACE在DDL的初始准备和最后结束两个阶段时通常需要加排它锁，除此外，DDL期间不会阻塞DML，是online的。

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

三、INSTANT算法

        8.0.12以后可以用

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

        当前，INNODB支持用于这些操作的INSTANT算法：

        添加列，更改索引选项，重命名表，设置\删除默认值，添加\删除虚拟列

        INSTANT的添加列只是修改元数据，当使用该列时才会真正操作该列，不使用时可以放置，是一个异步的过程

四、总结

        1.除了8.0以上版本，除了追加式新增列、表改名、新增虚拟列这几种支持INSTANT的操作可以直接跑DDL，其余的都采用pt-osc/gh-ost工具，相对更不容易出问题。

        gh-ost工具：online-DDL详细原理介绍及gh-ost讲解_online ddl-CSDN博客

        2.执行ALTER TABLE DDL时，不要指定ALGORITHM=？，LOCK=？选项，因为mysql会自己判断使用哪种方式，本来可以INPLACE的，可能不小心指定成COPY了

        3.怎么简易判断，某个DDL是否online？

               alter table t1 add column a int not null

               0 rows affected    ——>说明是online的

五.关于Online DDL的误区问题

1.Online DDL会不会锁表

        回答这个问题，首先要明确锁表的含义，很多mysql用户经常在表无法正常的进行DML时，就认为是锁表了，这种说法其实过于宽泛，实际上能影响DML操作的锁至少包括以下几种（INNODB表）： MDL锁、表锁、行锁、GAP锁

        所有的操作，不管是DDL还是DML还是查询语句，都要先拿Server层的MDL锁，然后再去那InnoDB层的某个需要的锁，一个DDL的基本过程是：

        a.首先在开始DDL时，需拿到对应表的MDL X锁，然后进行准备工作

        b.然后将MDL X锁降级为MDL S锁，进行DDL操作

        c.最后再将MDL S锁升级为MDL X锁，完成DDL操作，释放MDL锁

2.INPLACE算法的DDL一定是online的吗？

        COPY算法：肯定不是online

        INPLACE算法：第二阶段是online    

3.INPLACE DDL需不需要额外的数据空间

        很多INPLACE DDL都会创建临时数据文件，所以都会需要额外的数据空间，例如：增加主键、重建主键、新增列删除列调整列顺序、删除\增加列默认值，修改表的ROW_FORMAT

4.row_log空间的问题

        row_log空间每次申请的大小由innodb_sort_buffer_size决定，最大值由innodb_online_alter_log_max_size决定，该值默认为128m，支持动态修改。对于更新频繁的表俩将，如果预计在DDL期间对表的更新操作存储可能超过128m时，需为本次操作增大该值。

        如果不涉及rebuild操作时，不需要考虑该值。

        如果提示DB_ONLINE_LOG_TOO_BIG错误，则是由innodb_online_alter_log_max_size空间不足造成