MySQL DDL简析（1）：inplace DDL 主要stage介绍

作者简介：高鹏，笔名八怪。《深入理解MySQL主从原理》图书作者，同时运营个人公众号“MySQL学习”，持续分享遇到的有趣case以及代码解析！

本文只是描述DDL中最慢的一个环节是在完成哪些工作，深入程度有限，如果有误请谅解。主要从DBA的角度来看这个问题，也是我一直比较迷惑的地方。基于代码8.0.23。

本文术语：

• DML log：使用官方文档的描述，用于表示在进行online DDL时，对现有表进行的DML操作记录的日志。
• sort buffer：本文中sort buffer是innodb_sort_buffer_size，不是我们平时说的用于语句返回记录排序的sort buffer。
• 阶段：本文用阶段代替stage

一、主要阶段概述

我们在做DDL的时候，经常会用到online DDL（inplace），当然某些online DDL是比较慢的比如：

• 增加索引（新建二级索引）
• 增加字段（5.7）（重建主键，重建所有二级索引）
• 增加字段并且指定顺序（8.0）（重建主键，重建所有二级索引）

这是因为，这些操作会导致读取全表的主键数据，同时需要新建二级索引或者重建整个表。

在官方文档中我们可以看到有如下一些主要的阶段（做了删减，否则太长）：

• stage/innodb/alter table (read PK and internal sort): This stage is active when ALTER TABLE is in the reading-primary-key phase.
• stage/innodb/alter table (merge sort): This stage is repeated for each index added by the ALTER TABLE operation.
• stage/innodb/alter table (insert): This stage is repeated for each index added by the ALTER TABLE operation.
• stage/innodb/alter table (log apply index): This stage includes the application of DML log generated while ALTER TABLE was running.
• stage/innodb/alter table (flush): Before this stage begins, WORK_ESTIMATED is updated with a more accurate estimate, based on the length of the flush list.
• stage/innodb/alter table (log apply table): This stage includes the application of concurrent DML log generated while ALTER TABLE was running.
• stage/innodb/alter table (end): Includes any remaining work that appeared after the flush phase.

我们可以通过如下语句在执行DDL时候观察具体达到了哪个阶段：

select *from performance_schema.threads where PROCESSLIST_INFO like '%alter%' \G
*************************** 1. row ***************************
          THREAD_ID: 52               
          NAME: thread/sql/one_connection               
          TYPE: FOREGROUND     
          PROCESSLIST_ID: 12   
          PROCESSLIST_USER: root   
          PROCESSLIST_HOST: localhost     
          PROCESSLIST_DB: testPROCESSLIST_COMMAND: Query   
          PROCESSLIST_TIME: 2  PROCESSLIST_STATE: alter table (read PK and internal sort)   
          PROCESSLIST_INFO: alter table sales1 add llll int   
          PARENT_THREAD_ID: NULL               
          ROLE: NULL       
          INSTRUMENTED: YES            
          HISTORY: YES    
          CONNECTION_TYPE: Socket       
          THREAD_OS_ID: 10653

接下来我们就对这些阶段进行描述，实际上上面提到了几个阶段，除了alter table (end)以外都存在于DDL语句的执行阶段，执行阶段通常来讲是最费时间的，因为要做的事情本来很多任务也很重。当然整个inplace DDL对应3个阶段的函数如下：

 mysql_inplace_alter_table
    -> handler::ha_prepare_inplace_alter_table   
    -> handler::ha_inplace_alter_table   
    -> handler::ha_commit_inplace_alter_table 

本文的分析的步骤，也几乎集中在handler::ha_inplace_alter_table函数下。

二、DML log作用和表现形式

用于表示在进行online DDL时，对现有表进行的DML操作记录的日志，这里简单提一下。实际上DML log是以行为单位写入，但是对于Insert/update和delete操作记录的行差别很大，因为delete语句我通常记录主键信息就能做删除操作了，但是insert/update 则需要记录更多的字段。函数参考（row_log_table_delete、row_log_table_update、row_log_table_insert）

同时DML log在内存的写入量是由innodb_sort_buffer_size（8.0.23默认4M）控制的，内部叫做一个block，如果一个block写满后需要写入到临时文件，言外之意每次临时文件的增量为innodb_sort_buffer_size大小。在每行DML log进行写入的时候会判断总的大小是否大于了参数innodb_online_alter_log_max_size（8.0.23默认1G）参数设置大小，如果超过了则会报错:

Creating index '%-.192s' required more than 'innodb_online_alter_log_max_size' bytes of 
modification log. Please try again

其次重建主键只需要建立一个主键上的DML log就可以了，但是新建索引需要为每个新建的索引都分配一个DML log。评判是否需要重建主键的标准可能是，是否更改了主键的字段和顺序（先不考虑instant DDL，prepare_inplace_alter_table_dict函数）。

我们还需要注意的是重建主键需要建立一个临时文件放到data目录下，类似“#sql-ib1408-817418612.ibd”的形式。但是新建索引则不需要，将新加入的所以你放到原有的数据文件就可以了。

下面是我观察到的这种临时文件。我的方法对一个大表做了如下到操作：

• alter table tttoo add ipp3 int after io;

这个操作会重建主键，注意即便是8.0这个操作也不是instant DDL，因为它改变了字段顺序。我是将断点放到row_log_tmpfile函数上，查看返回临时文件的文件描述符，然后通过lsof找到这个临时文件，也可以在/prod/pid/fd下看到，如下：

(gdb) p log->fd
$4 = 54
[root@mgr4 fd]# lsof -p 5517|grep delete
....
mysqld  5517 mysql   54u      REG               8,16         0   284059 /newdata/mysql/mysql8023/tmp/#284059 (deleted)
（DML log临时文件）
mysqld  5517 mysql   55u      REG               8,16  29360128   284065 /newdata/mysql/mysql8023/tmp/#284065 (deleted)
mysqld  5517 mysql   56u      REG               8,16  29360128   284066 /newdata/mysql/mysql8023/tmp/#284066 (deleted)

这里/newdata/mysql/mysql8023/tmp/#284059就是为主键上的DML log临时文件

[root@mgr4 fd]# lsof -p 5517|grep delete
....
mysqld  5517 mysql   54u      REG               8,16   4194304   284059 /newdata/mysql/mysql8023/tmp/#284059 (deleted)
（DML log临时文件）
mysqld  5517 mysql   56u      REG               8,16  29360128   284066 /newdata/mysql/mysql8023/tmp/#284066 (deleted)
mysqld  5517 mysql   57u      REG               8,16  29360128   284067 /newdata/mysql/mysql8023/tmp/#284067 (deleted)

注意这里有其他的临时文件，这些文件是每个索引做merge操作的临时文件。我们稍微观察一下不管DML log还是merge排序的临时文件这里大小都是4M的整数倍。其实除了DML log，实际上做索引merge操作的时候同样是以innodb_sort_buffer_size（8.0.23默认4M）为block的，因此临时文件大小也是4M的整数倍。

三、阶段read PK and internal sort（srv_stage_alter_table_read_pk_internal_sort）

这个阶段是重建的第一个阶段（row_merge_build_indexes开头进入这个状态），主要是在函数row_merge_read_clustered_index中完成逻辑。本阶段完成任务包含：

1. 循环每一个需要重建的索引，为其分配sort buffer相关的结构，分配的大小也是innodb_sort_buffer_size（8.0.23默认4M）指定的。当然如果是增加索引这里通常只有一个sort buffer，如果是重建整个主键（比如增加字段并且指定了顺序），那么每个索引都会包含一个sort buffer，循环结束。
2. 读取主键每个page每一行记录进行循环
3. 循环每一个需要重建的索引，这里到底有多少个索引如步骤1所述
4. 将记录分别插入到各个二级索引的的sort buffer中（row_merge_buf_add）
5. 如果sort buffer满、或者表比较小不会占满sort buffer，如果是主键直接插入数据主键数据到新表中。因为主键是不需要排序的，因为新老主键顺序是一样的（row_merge_insert_index_tuples），插入记录的时候带入stage指针为nullptr，因此不会进入另外一个状态alter table (insert)
6. 如果sort buffer满、或者表比较小不会占满sort buffer，如果是二级索引显然需要根据键值排序了（row_merge_buf_sort），如果表比较小不涉及到临时文件，则直接排序后直接插入到新的二级索引中（row_merge_insert_index_tuples），插入记录的时候带入stage指针为nullptr，因此不会进入另外一个状态alter table (insert)
7. 对于二级索引而言，如果表比较大，需要多个sort buffer的话，每个sort buffer排序（row_merge_buf_sort）完成后不会直接插入到新的二级索引中，而是写入到物理临时文件中，等待做磁盘merge排序。通常每个二级索引会拥有一个这样的临时文件。

注意这里有一个名场面btr_bulk->insert(dtuple)，往新的主键或者索引中插入数据的时候是通过bulk方式插入的，这有别于我们平时插入数据的改变索引结构的方式，速度更快，但是能力有限不做讨论，有感兴趣的朋友可以看看。

这一个阶段，有几点注意的地方：

• 涉及的排序也不会我们说的磁盘merge sort，所以不会改变状态到alter table (merge sort)（srv_stage_alter_table_merge_sort）。
• 对于小表的二级索引而言（重建索引的行不超过innodb_sort_buffer_size的大小的表），这里实际上直接就完成了数据插入，状态会在扫描完全部主键行数据然后做完排序后直接插入。
• 对于主键而言总是每次sort buffer满后进行一次插入，当然也可能是小表装不满一个sort buffer。
• 对于二级索引的临时文件大小也是分block的，一个innodb_sort_buffer_size大小就是一个block。

因此这个阶段除了读取老的主键数据以外，总会将主键数据插入到新的主键中，而二级索引完成排序后插入到新的二级索引或者临时文件中，使用需要使用到临时文件这取决于你的表大小（是否重建的记录超过sort buffer大小），同时每个需要重建的二级索引均有一个临时文件。

四、阶段alter table (merge sort) （srv_stage_alter_table_merge_sort）

####从这一步开始下面的步骤是循环每个需要重建的索引， LOOP

1. 首先判断判断排序是否使用了临时文件，如果是则进行2、3
2. 进入状态alter table (merge sort) （srv_stage_alter_table_merge_sort）
3. 如果使用了临时文件那么进行merge sort（row_merge_sort）

关于磁盘merge排序的具体步骤不做研究了，简单的说就是读取临时文件中每一个block，注意每一个block是一个sort buffer的大小，然后通过归并的方式，最终排序完成。

五、阶段alter table (insert)（srv_stage_alter_table_insert）

1. 首先判断排序是否使用了临时文件，如果是则进行2、3、4
2. 进入状态alter table (insert)（srv_stage_alter_table_insert）
3. 将merge排序好的数据全部通过bulk的方式插入到新的二级索引中
4. 丢弃merge后的临时文件（row_merge_file_destroy），释放空间

显然alter table (merge sort) 和alter table (insert)阶段，对于小表而言通常是不存在的，因为在read PK and internal sort阶段就完成了主键和二级索引的插入操作。这里都是通过是否打开了临时文件判断的。

六、阶段alter table (flush)（srv_stage_alter_table_flush）

1. 判断是否重建了主键，如果没有重建则完成如下步骤2、3、4、5
2. 进入阶段alter table (flush)（srv_stage_alter_table_flush）由FlushObserver::flush调入
3. 循环每个pool instance，对操作的对象的space id查看需要刷新块
4. 刷新类型为BUF_REMOVE_FLUSH_WRITE，为脏数据回写磁盘，但是不从pool instance去掉。
5. 写入redo 日志类型MLOG_INDEX_LOAD，提醒本二级索引做了bulk插入操作，没有写redo，记录中包含space id和index的root节点信息（notify that an index tree is being loaded without writing redo log about individual pages）

如果没有重建主键，那么新的二级索引是直接bulk插入到原有space中，但是这个步骤没有记录redo，因此做一次脏数据刷盘。压力小的时候脏数据就少。

七、阶段alter table (log apply table)（srv_stage_alter_table_log_index）

1. 判断是否重建了主键，如果没有重建则完成如下步骤 2、3 、4、5、6
2. 进入阶段alter table (log apply index)（srv_stage_alter_table_log_index）由row_log_apply调入
3. 对二级索引加锁，防止写入
4. 进行日志应用，这个逻辑和后面的table apply很像，函数少了table关键字，后面了我看row_log_table_apply的大体逻辑的时候就会比较清楚。同时应用的时候比如insert就直接调用了底层的索引插入的函数btr_cur_optimistic_insert/btr_cur_pessimistic_insert。这个过程会解锁这个二级索引，直到最后一个block，才会对二级索引上锁防止写入。
5. 设置二级索引状态为ONLINE_INDEX_COMPLETE，一旦这个操作完成后就是正常的二级索引了。
6. 对二级索引解锁

如果不重建主键（比如add index,add index操作），那么每个增加的二级索引都需要包含一个这样的DML log，对于每个二级索引的DML log应用只有这里的一次，commit阶段不会再次应用了，这有别于主键上的DML log。这里我们也看到实际插入就是类似我们inset操作做的插入，因此也是比较耗时的操作。压力小的时候的DML log就小，DDL也会更快。

到这里循环结束 ，也就是四、五、六、七 部分在一个循环里面，END LOOP

###八、阶段alter table (log apply table)（srv_stage_alter_table_log_table）

1. 判断是否重建了主键，如果重建了则完成如下步骤 2、3、4、5、6、7、8
2. 进入阶段alter table (log apply table)（srv_stage_alter_table_log_table）由row_log_table_apply调入
3. 先对老主键index加锁，防止写入
4. 访问每一个block(sort buffer大小)
5. 判断是否当前block(sort buffer 大小)已经是最后一个block,如果是则直接加锁应用最后一个block。
6. 如果不是则需要读取物理文件，读取block(sort buffer大小)同时解锁 ，不影响正常的写入。
7. 循环获取block中的每行记录，应用，这里有不同类型ROW_T_INSERT/ROW_T_DELETE/ROW_T_UPDATE
8. 这里以insert为例，插入为先主键插入，然后循环每个二级索引插入（row_ins_clust_index_entry_low/row_ins_sec_index_entry_low）

这里的应用是几乎不会影响到正常的写入的，也就是生产者还在向DML log写入，而消费者也就是DDL线程也可以消费，直到达到最后一个block。

九、alter table (end)（srv_stage_alter_table_end）

来到这一步就是已经到了DDL的commit阶段了，在之前会上MDL 的独占锁，代码注释Upgrade to EXCLUSIVE before commit。这里先不考虑（实际上我也还没学习）所有的commit流程，我们只考虑和DML log相关的部分。

在这个阶段会判断是否重建了主键，如果重建了则会调用row_log_table_apply进行最后的DML log应用，同时阶段会切换为alter table (log apply table)（srv_stage_alter_table_log_table）且一直保持到结束。不过这个阶段一般很快就完成了，因此很难观察到。

十、总结

我们还是以2个SQL为例进行总结描述：

1. alter table test add index (a),add index(b);（新二级建索引）
2. alter table test add d int after a;（重建主键，并且会重建所有的二级索引）

这里面语句1不会重建主键，而语句2由于增加字段且改变了顺序需要重建主键。

1. 重建主键操作的代价和空间要求通常远高于新建二级索引，原因如下：

• 重建主键会新建临时文件放到data目录下类似#sql-ib1408-817418612.ibd开头，而新建索引则不需要，这意味着需要额外的一倍空间。
• 重建主键对每个二级索引做merge排序，会为每个二级索引建立临时文件用于排序。而新建索引只会新加入的索引进行merge排序。这又是额外的空间消耗
• 重建主键和新建索引虽然都需要DML log，但是重建主键建立在主键上，而新建索引建立在索引上，空间消耗不同，并且应用DML log的时候 重建主键从了主键本身需要更改每个二级索引，这是新建索引不需要的。

因此重建主键需要的空间往往比2倍本表空间还要大，需要注意。

1. 即便是online DDL也会需要选择低峰期进行

• MDL lock是需要考虑的
• DML log高压力下会比较大，这会导致应用比较久
• 高压力下脏数据较多

1. innodb_sort_buffer_size参数的作用有两个

• 定义DML log 内存大小，以innodb_sort_buffer_size为block写入到临时文件
• 定义merge sort 内存大小，以innodb_sort_buffer_size为block写入到临时文件

1. DML log来讲delete操作的log小于insert/update
2. 只有在需要重建主键的DDL才需要新建临时文件#sql-ib**放到数据目录中，如果是增加索引是不需要的
3. DDL中临时文件可能包含

• data目录下的#sql-ib**文件这个要看是否重建主键
• tmp目录下的DML log临时文件，如果不重建主键，则每个二级索引都有一个。当然如果DML log很小，小于innodb_sort_buffer_size大小则不需要。
• tmp目录下的merge sort临时文件，这是需要重建的每个二级索引都需要的。当然如果表数据量很小，小于innodb_sort_buffer_size大小则不需要。

十一、流程图和笔记

最后一个张流程图，将这些复杂步骤串联起来，也有利于后期复习（高清图在https://www.jianshu.com/p/746610cb5f5b）。

DDL STAGE.png

我的笔记放到另外一个文章，供参考：

• https://www.jianshu.com/p/a04d77b5a0e1

参考资料：

• https://cloud.tencent.com/developer/article/1006513
• https://zhuanlan.zhihu.com/p/115285170