MySQL Online DDL

1.Online DDL 定义

在MySQL5.5以及之前的版本，通常更改数据表结构操作(DDL)会阻塞对表数据的增删改操作(DML)

MySQL5.6提供Online DDL之后可支持DDL与DML操作同时执行，也就是降低了DDL期间对业务延迟带来的影响

Online DDL定义：指业务正常状态下进行数据表结构操作，不会影响不会阻塞表数据的增删改操作。

2.Online DDL特点

1.DDL操作可与应用的DML操作并发执行，改进在繁忙生产环境的响应和可用性，因为对于这种业务系统几分钟或几小时不可用是难以忍受的可以通过调整DDL的lock模式来平衡与DML操作的性能问题

2.Online DDL使用的是In-place方式，相较于table-copy方式能更少的使用I/O资源，在DDL期间也能有较高的吞吐量

3.In-place相较于table-copy还有一个优点是：table-copy读取数据多，频繁的使用buffer pool导致有效缓存数据被调出，影响缓存击中率 降低效率

  在5.6版本之后，对于部分alter table ，加入新的执行算法，可以进行DDL时，“并行”有业务（DML操作）。
  可以通过ALter table 时添加 ALGORITHM参数控制使用算法。
  ALter table t1 add a int ALGORITHM=?;
  目前可以支持的算法有三种：
  COPY
  INPLACE
  INSTANT
  DDL操作，在执行时，不管何种算法，都会经历三个阶段：准备阶段、执行阶段、提交阶段。不同之处是在三个阶段中分别作了不同的处理

 

online DDL in mysql 5.5
在mysql5.5版本中已经增加了in-place方式，但依然会阻塞insert,update,delete操作。

mysql 5.5 online ddl 原理：

a. 按照原表的定义创建临时表；

b. 对原表进行加写锁；

c. 对新的临时表进行ddl操作；

d. 将原表中的数据copy到临时表中；

e. 释放原表的写锁；

f. 将旧表删除，临时表重命名

存在的问题：

a. 在进行copy data的过程中消耗的时间长，和消耗大量的存储空间；

b. 在原表进行加锁时，业务会中断访问

 

online DDL in mysql5.6

在mysql5.6版本中，引入了新特性，Fast Index Create(FIC特性)，支持更多的alter table语句来避免copy data同时支持了在线上DDL的过程中不阻塞DML操作。

mysql5.6参数设置：

innodb_online_alter_log_max_size参数，默认为128M，但是在生产场景中512M会适合。在进行在线索引添加操作时，数据库性能有20-30％的下降

 

online DDL in mysql5.7

mysql5.7支持的新特性：

a. 增加了alter table rename index 的语法支持，同时也继续支撑online DDL特性；

b. 修改varchar列长度操作支持online特性。

mysql5.7 online DDL的原理：

PREPARE：

a. 创建新的临时.frm 文件；

b. 持有排他－MDL锁，禁止读写；

c. 根据alter 类型确定执行方式；

d. 更新数据字典的内存对象；

e. 分配row_log对象用来记录增量；

f. 生成新的临时ibd文件；

DDL：
g. 降级排他锁为S锁；
h. 扫描old_table的聚簇索引每一条记录reo，并遍历新表的索引进行处理；
i. 根据reo构造对应的索引项对应的索引项，将构造索引项插入soft_buffer块排序；
j. 将sort_buffer块更新到新的索引上；
k. 记录ddl执行过程中产生的增量并在新表上重放；
l. 记录ddl执行过程中产生的增量（仅rebuild类型需要）；
m. 重放row_log间产生dml操作append到row_log最后一个block;

COMMIT；
n. 当前block为row_log最后一个时，禁止读写，升级到排他－MDL锁；
o.重做row_log中最后一部分增量；
p.更新innodb的数据字典表；
q. 提交事务（刷事务的redo日志）
r. 修改统计信息；
s. rename临时ibd文件，.frm文件
online DDL使用限制与问题：
a. 仍然存在排他锁，有锁等待的风险；
b. 跟5.6一样，增量日志大小是有限制的；
c. 有可能造成主从延迟；
d. 无法暂停，只能中断。

3.online DDL过程介绍

ddl包含了copy和inplace方式，对于不支持online的ddl操作采用copy方式。对于inplace方式，mysql内部以“是否修改记录格式”为基准也分为两类，一类需要重建表(重新组织记录)，比如optimize table、添加索引、添加/删除列、修改列NULL/NOT NULL属性等；另外一类是只需要修改表的元数据，比如删除索引、修改列名、修改列默认值、修改列自增值等。Mysql将这两类方式分别称为rebuild方式和no-rebuild方式。更多关于哪些DDL是否可以inplace的内容可以参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html。

online ddl主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段，rebuild方式比no-rebuild方式实质多了一个ddl执行阶段，prepare阶段和commit阶段类似。ddl执行过程中包括三个阶段。

Prepare阶段：
创建新的临时frm文件
持有EXCLUSIVE-MDL锁，禁止读写
根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)
更新数据字典的内存对象
分配row_log对象记录增量
生成新的临时ibd文件

ddl执行阶段：
降级EXCLUSIVE-MDL锁，允许读写
扫描old_table的聚集索引每一条记录rec
遍历新表的聚集索引和二级索引，逐一处理
根据rec构造对应的索引项
将构造索引项插入sort_buffer块
将sort_buffer块插入新的索引
处理ddl执行过程中产生的增量(仅rebuild类型需要)

commit阶段
升级到EXCLUSIVE-MDL锁，禁止读写
重做最后row_log中最后一部分增量
更新innodb的数据字典表
提交事务(刷事务的redo日志)
修改统计信息
rename临时idb文件，frm文件
变更完成

4.Copy 算法原理

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

准备阶段：
1、对表加元数据共享锁，读取frm元数据（此时DDL不能不行）
2、共享锁升级为排他锁；（此时DDL、DML都不能并行）
3、在Server层通过Create like语句，创建临时表，Engine层也生成对应ibd、frm文件

执行、提交阶段：
1、修改临时表元数据
2、拷贝原表数据到临时表
3、重命令临时表及文件
4、删除原表及文件
5、提交事务，释放锁

5.INPLACE 算法原理

无需拷贝全表数据到新表，但可能还是需要IN-PLACE方式（原地，无需生成新的临时表）重建整表。这种情况下，在DDL的初始准备和最后结束两个阶段时通常需要加排他MDL锁（metadata lock，元数据锁），除此外，DDL期间不会阻塞DML
准备阶段：
1、对表加元数据共享升级锁，并升级为排他锁；（此时DML不能并行）
2、判断语句rebuild table,no-rebuild，no-rebuild 在原表所在的路径下创建.frm和.ibd临时中转文件；

no-rebuild除创建二级索引外只创建.frm文件，其中添加二级索引操作最为特殊，该操作属于no-rebuild不会生成.ibd，但实际上对.ibd文件却做了修改，该操作会在参数tmpdir指定路径下生成临时文件，用于存储索引排序结果，然后再合并到.ibd文件中.

3、申请row log空间，用于存放DDL执行阶段产生的DML操作。（no-rebuild不需要）
执行阶段： （online）
1、释放排他锁，保留元数据共享升级锁；（此时DML可以并行）
2、扫描原表主键以及二级索引的所有数据页，生成 B+ 树，存储到临时文件中；
3、将所有对原表的DML操作记录在日志文件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的，也就是在从库本地直接写入数据是不会阻塞的，也会像主库一样产生rowlog。但是对于主库同步过来DML，此时会被阻塞，是offline的，DDL是排他锁的在复制线程中也是一样，所以不只会阻塞该表，而是后续所有从主库同步过来的操作（主要是在复制线程并行时会排他，同一时间只有他自己在执行）。所以大表的DDL操作，会造成同步延迟。

PREPARE：
a. 创建新的临时.frm 文件； 
b. 持有排他－MDL锁，禁止读写；
c. 根据alter 类型确定执行方式； 
d. 更新数据字典的内存对象； 
e. 分配row_log对象用来记录增量； 
f. 生成新的临时ibd文件；

DDL：
g. 降级排他锁为S锁； 
h. 扫描old_table的聚簇索引每一条记录reo，并遍历新表的索引进行处理； 
i. 根据reo构造对应的索引项对应的索引项，将构造索引项插入soft_buffer块排序； 
j. 将sort_buffer块更新到新的索引上； 
k. 记录ddl执行过程中产生的增量并在新表上重放； 
l. 记录ddl执行过程中产生的增量（仅rebuild类型需要）；
m. 重放row_log间产生dml操作append到row_log最后一个block;

COMMIT；
n. 当前block为row_log最后一个时，禁止读写，升级到排他－MDL锁； 
o.重做row_log中最后一部分增量； 
p.更新innodb的数据字典表； 
q. 提交事务（刷事务的redo日志）
r. 修改统计信息； 
s. rename临时ibd文件，.frm文件

online DDL使用限制与问题：
a. 仍然存在排他锁，有锁等待的风险； 
b. 跟5.6一样，增量日志大小是有限制的；
c. 有可能造成主从延迟；
d. 无法暂停，只能中断。 

6.INPLACE 与COPY区别

ALGORITHM=COPY是MySQL5.5以及之前的方式

ALGORITHM=INPLACE是MySQL5.6引入的方式

COPY算法，由service层创建一个临时表用于copy数据，然后用新表替换旧表

INPLACE算法，“原位替换” 其实主要是指在InnoDB内部完成的DDL操作，在InnoDB内部创建临时文件。整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。

因此对于INPLACE其实分为非重建表和重建表两类方式，非重建表方式直接在原表基础上更新，效率最高；重建表同样需要copy数据(比如新增字段) 详情请参考mysql5.7