MySQL Online DDL 原理

MySQL Online DDL 原理

语法：
增加列：

alter table 表名 add column 列名 数据类型, ALGORITHM 算法;

alter table hank add column name1 varchar(16),ALGORITHM INPLACE/COPY/DEFAULT;

不指定算法：

alter table hank add column name1 varchar(16);

添加索引：

alter table 表名 add index 索引名(列名), ALGORITHM INPLACE;

alter table hank add index idx_name(name1) , ALGORITHM INPLACE/COPY/DEFAULT;

不指定算法：

alter table hank add index idx_name(name1);

注释：COPY 是 Offline 。 默认情况下，不需要指定算法，数据库会自动选择。

一、MySQL Online DDL COPY

1. alter table hank add column c varchar(122), ALGORITHM=COPY;

2.online ddl copy方式三个阶段：
准备阶段 -> 执行阶段 -> 提交阶段

(2.1) 准备阶段：

1. 对元数据进行添加共享锁(MDL-S)[Meta Lock Share]，读取原表结构（不能进行DDL，不阻塞DML，过程很短暂）
2. S锁升级为X锁（排它锁），此时阻塞DDL,DML
3. 创建和原表一样的表结构，server层会执行类似crate table语句创建和原表一致的表结构，在engine层生成frm和ibd文件

(2.2) 执行阶段：

1. 修改新创建的临时表的表结构
2. 临时表的表结构修改完，server层copy原表数据到临时表（阻塞DML）
3. server层替换两个表，rename临时表
4. 删除原表

(2.3) 提交阶段：

commit，释放所有锁

注： 

1. 开始执行到结束，都是上锁(MDL-X)，阻塞DDL,DML，不阻塞SELECT
2. 此类操作不是online DDL，执行阶段都是阻塞业务的

二、MySQL Online DDL INPLACE

1. online ddl inplace执行三个阶段：

准备阶段 -> 执行阶段 -> 提交阶段

mysql 5.6 开始支持inplace，整个过程都阻塞其它DDL，不阻塞DML

 

 

 2. inplace 准备阶段

(2.1) 准备阶段：（准备阶段阻塞DML）

1. 在进入prepare阶段前，对元数据持有"可升级的S锁"(MDL-S锁)，在此阶段不允许DML，不允许部分DDL，如drop操作
2. 在预备阶段MDL-S锁升级为X锁(排它锁)，此时会判断操作是否需要rebuild table
3. 判断是需要rebuild table还是no rebuild table，判断完之后进行下一步，如果需要rebuild table，则申请row log空间

row log作用：记录ONLINE DDL执行阶段，对原表的DML操作（row log由innodb_sort_buffer_size决定）
ebuild table：则需要在engine层生成原表的转储文件(比如:ibd，frm文件，DDL阶段执行)
no reduild table：则要在engine层则只需要生成frm文件（比如加索引就是no rebuild table，只需要生成frm，DDL阶段执行

(2.2) 执行阶段：（DDL执行阶段不阻塞DML）

1. 执行阶段 会把X锁降级为S锁，该阶段不阻塞dml操作 ，这个阶段被称为online阶段
2. MDL-X锁降级为MDL-S锁,将原表存储的数据读取到prepare阶段创建的ibd文件中（engine层完成，直接分析数据页，内部结构，将原表的数据记录逐行取出后进行处理，且会执行ddl修改表结构，并应用到新的ibd文件中）

(2.3) 提交阶段：（commit阶段阻塞DML）

1. 提交阶段engine层应用row log中的操作到新的ibd文件中直到最后一个，系统会自动判断进行截断，避免源源不断的DML操作
2. 此时MDL-S锁再此升级到MDL-X锁（拒绝所的DML），然后把row log中剩余的数据应用完
3. 删除原表，替换新表为原表
4. 最后提交

三、Online DDL 会不会锁表？

1. 用户角度看online ddl，在执行ddl期间，不阻塞DML操作

2. DBA角度看：

准备阶段 持有X锁         --> 阻塞DML
执行阶段 X锁降级为S锁 --> 不阻塞DML
提交阶段 S锁升级为X锁 --> 阻塞DML

四、COPY 和 INPLACE 区别：

COPY是在server层，INPLACE在engine层（inplace中，不需要通过server层的create语句重建表，但是依然需要在engine层，生成ibd转储文件）

五、如何判断是offline还是online ？

mysql> alter table hank modify column c varchar(121);
Query OK, 5000 rows affected (0.28 sec)                # 如果不是0 rows 则是offline DDL

mysql> alter table hank modify column c varchar(122);
Query OK, 0 rows affected (0.00 sec)                   # 如果是0 rows affected 则是online DDL
Records: 0  Duplicates: 0  Warnings: 0

 

转自：https://www.cnblogs.com/dtxdm/p/15828624.html