导读
随着业务的快速增长，用户中心的用户表user单表数据量越来越大，此时，如果我们想给user表添加索引，数据规模对添加过程的影响势必要考虑在内，但是，单表数据规模对添加索引会产生什么样的影响呢，我们在什么样的数据库请求状态下给大表添加索引比较好呢？
今天，我就详细回答一下上面两个问题：
- 单表数据规模对添加索引会产生什么样的影响？
- 在什么样的数据库请求状态下给大表添加索引比较好？
我们先来看下第一个问题，当我们回答了第一个问题，那么，第二个问题的答案也就浮出水面了。
Row Log
我们先来看一个结构，它叫Row Log，用于在DDL过程中记录DML操作的日志文件。
我以user表为例，讲解一下Row Log。它有如下特点：
- 每个索引对应一个Row Log，如上图为user表的索引
index_age_birth对应的Row Log。
Row Log在逻辑上由多个Block组成，每个Block可以存储多个DML操作、一个DML操作也会落在多个Block中。如上图中的Log代表DML操作：
- 最前面两个Log存在第二个Block中
- 第3个Log和第4个Log的前半部分存在第三个Block中
- 第4个Log的后半部分和第5个Log存在最后一个Block中
在物理存储上Row Log分为两部分：
- 内存日志：内存中会存放一个总大小等于
inndob_sort_buffer_size的Block，用于写入DML操作 - 文件日志：当内存中的Block写满，也就是大小大于
innodb_sort_buffer_size，且小于innodb_online_alter_log_max_size时，写满的Block会刷到磁盘上，空出内存中的Block给后续的Log写入，日志文件中，所有Block总大小如果超过innodb_online_alter_log_max_size，写入就会报错
Row Log的核心结构如下：
- Log：表示DML操作日志，它的结构为
操作flag + 事务id + 操作记录，其中，操作flag包含两种：INSERT和DELETE，UPDATE看作是先DELETE，再INSERT。比如，上图第一个Log中包含一条记录<0x61 + 1234 + <25, 1998-01-02, 1>>，其中，0x61代表这是一个插入操作，1234表示这个操作的事务id，<25, 1998-01-02, 1>表示操作的记录。 - head：这是用于将Block中的Log回放到索引树时，用来扫描Block中Log的指针，扫完一个Log，head指针向后移到下一个Log。如上图，因为从Block的头部开始扫描，head指针在回放前处在Block的第一个Log的位置。
- tail：这是用于将DML操作写入一个Block时，用来定位Block中Log插入位置的指针，插入完一个Log，tail指针向后移动到新插入的Log。如上图，因为从Block的头部开始插入Log，所以，tail指针在插入前处在Block的第一个Log的位置。
- blocks：无论是head还是tail指针，都包含一个blocks字段，表示Row Log日志文件中包含的Block数量
Row Log追加
下面我们再来看下Log是如何追加到Row Log的？我以user表的index_age_birth索引的Row Log为例来说明：
见上图，从上到下，我们来看下这个追加的过程：
- 如果内存中没有Block，创建一个
innodb_sort_buffer_size大小的Block，tail指针指向Block中的第一个Log，如果有Block，tail指针指向Block中最后一个Log。如上图，内存中有Block，tail指向Block中最后一个Log，也就是虚线框前面那个Log - 根据即将插入的DML操作日志大小，得到Block中下一个Log相对最后一个Log的偏移量。如上图中的offset，这里分两种情况：
(1) 如果DML操作日志大小 >= innodb_sort_buffer_size - 当前Block中已有Log的总大小，则偏移量为innodb_sort_buffer_size - 当前Block中已有Log的总大小
(2) 如果DML操作日志大小 < innodb_sort_buffer_size - 当前Block中已有Log的总大小，则偏移量为DML操作日志大小
- 根据tail指针和偏移量，将插入的DML操作日志拷贝到内存的Block。这里同样分两种情况：
(1) 半拷贝
a. 如果DML操作日志大小 >= innodb_sort_buffer_size - 当前Block中已有Log的总大小，拷贝DML操作日志的前面部分到tail后面偏移量大小的空间。如上图半拷贝里的上半部分，将DML日志<0x61 + 3355 + <25, 1998-01-02, 1>>的前半部分拷贝到末尾Log，然后，将tail移到被拷贝的Log上
b. 将内存中整个Block写入Row Log日志文件。如上图，半拷贝里上半部分大括号包含了整个Block，同时将该Block通过箭头，写入row_log_file
c. 重新将tail移到内存空Block的头部，将DML操作的后半部分拷贝到tail后面偏移量大小的空间。如上图半拷贝里的下半部分，将DML日志<0x61 + 3355 + <25, 1998-01-02, 1>>的后半部分拷贝到Block的头部
d. 如上图，tail.blocks + 1，代表Row Log日志文件中新增了一个Block。
(2) 全拷贝
a. 如果DML操作日志大小 < innodb_sort_buffer_size - 当前Block中已有Log的总大小，将DML操作日志全部拷贝到Block中末尾Log。如上图，全拷贝最右侧，将DML日志<0x61 + 3355 + <25, 1998-01-02, 1>>完整拷贝到末尾Log，然后，将tail移到被拷贝的Log上
Row Log回放
MySQL将DML日志写到Row Log只是为了在执行DDL期间，可以并行执行DML，最后，这些DML日志还是要更新到索引树上的，所以，同样以索引index_age_birth为例，我们再来看下Row Log中的日志是如何更新到索引树的？
从上到下，我们来看上面这张图：
- MySQL先扫描磁盘上的Row Log文件，遍历文件中的Block，如上图，文件扫描部分为一个Block的遍历：
(1) head指针指向Block的头部Log，从该Log开始，将头部Log写入索引树。如上图，文件扫描中的最上面部分，将DML日志0x61 + 3355 + <25, 1998-01-02, 1>>中的记录写入索引树index_age_birth的第一个叶子节点。
(2) 头部Log清空，将head指针移到后面一个Log。如上图，文件扫描中的第二块长方框。
(3) 重复(1)和(2)两步，直到head指针移到Block中最后一个Log，然后，将该Log中的记录写入索引树index_age_birth。如上图，文件扫描中的第三个长方框及方框中最后一个Log中的记录写入索引树index_age_birth的第二个叶子节点。。
- 重复步骤1，将Row Log文件中所有Block内的Log全部写入索引树
index_age_birth，至此，Row Log文件清空。如上图，文件扫描中最后一个虚线长方框，表示Row Log文件清空。 - 由于DML日志写Row Log和DDL同时进行，结合《Row Log追加》中的过程，我们会发现大部分Block写入了Row Log文件，但是，还会存在小部分DML日志留存在内存的Block中，所以，MySQL需要将这部分留存的Log再写入索引树中，具体过程如下：
(1) 对数据字典加排它锁，禁止新的DML操作，ps：如果不加锁，会导致内存中Block不断更新，无法判断DML操作何时结束。
(2) 执行步骤1，将内存Block中的Log全部写入索引树index_age_birth，如上图，内存扫描部分。
Bulk Load
在讲解添加索引的过程之前，还有一个概念再讲解一下，这就是Bulk Load，在添加索引的过程中，会将已排序的记录批量插入索引树的叶子节点中，这个批量插入的过程就叫做Bulk Load，我以索引index_age_birth为例，讲解一下这个过程，见下图：
- 从已排序的记录集中分多批写入内存的bulk中。如上图，MySQL将最左边已排序的记录集拆分成两批写入2个bulk中，上面的bulk包含
15, 2008-02-03, 2和15, 2008-02-06, 5两条记录，下面的bulk包含16, 2007-06-06, 6、17, 2006-03-03, 4和18, 2002-06-07, 3三条记录。 - 以bulk为单位，将bulk中的记录集一次插入索引树中。如上图，上面的bulk记录集插入到索引树
index_age_birth的第三个叶子节点，下面的bulk记录集插入到索引树index_age_birth的倒数第二个叶子节点。
添加索引
Row Log的追加和回放，以及Bulk Load是添加索引过程中的核心步骤，讲完这三个步骤，下面我再来看一下InnoDB引擎中MySQL添加索引的过程就比较容易理解了，该过程主要分三个阶段，我以user表为例详细讲解一下：
Prepare阶段：
- 根据旧表user的表结构文件frm，创建一个副本表结构frm文件，将新索引添加到副本中
- 获得MDL排他锁，禁止读写数据字典及旧user表，关于MDL锁，我会在《MySQL锁全解析》详细讲解
- 根据alter类型，确定执行方式，一共两种执行方式：COPY、INPLACE
- 更新内存中的数据字典，标记user表所有索引
online_status为ONLINE_INDEX_CREATION，表示该表索引都处在在线DDL状态。关于数据字典的结构，我在《我们可以干预MySQL选择正确的执行计划吗？》中有讲解过。 - 根据旧表user的ibd文件，创建副本ibd文件
DDL执行阶段：
- 降级MDL锁为共享锁，允许读写数据字典及旧user表
- 扫描旧表user的聚集索引中叶子节点每一条记录
(1) 申请一个sort_buffer，大小为innodb_sort_buffer_size/索引叶子节点中最小的记录的大小
(2) 将每一条记录写入sort_buffer
(3) sort_buffer写满后对里面的记录进行升序排序
(4) sort_buffer写满了，如果临时文件不存在，就创建一个临时文件
(5) 遍历sort_buffer记录，将sort_buffer中的记录写入文件中
a. 生成一个block，将记录添加到block
(6) 将block写入临时文件
- 遍历旧表聚簇索引的记录完成后，临时文件中就包含多个block，每个block包含已排序的记录
- 使用归并排序对临时文件中的block内记录进行排序
- 遍历副本frm中的聚集索引和辅助索引
(1) 搜索索引树，定位到树种最右边的叶子节点
(2) 判断该节点是否可以有足够空间批量插入记录，如果没有就创建一个新的叶子节点，执行步骤(3)，否则，执行步骤(4)
(3) 将新节点接到索引树的右下角，执行步骤(4)
(4) 遍历临时文件中的记录，将记录通过bulk load方式写入叶子节点
(5) 调整插入记录的叶子节点内记录的slot信息，关于slot，我在《InnoDB是顺序查找B-Tree叶子节点的吗？》中详细讲解过。
- 在这个阶段，与此同时，user表的所有DML操作日志写入Row Log，即《Row Log追加》中讲解的过程
- 重放该阶段产生的user表的Row Log日志到索引中，直到Row Log中的最后一个block，即《Row Log回放》中讲解的过程。
Commit阶段：
- 升级MDL锁为排它锁，禁止读写数据字典及旧user表
- 将Row Log中最后一个block，即内存中Block对应的DML日志插入索引树，过程参见DDL执行阶段中的步骤(7)
- 更新内存中的数据字典，关于数据字典的结构，我在《我们可以干预MySQL选择正确的执行计划吗？》中有讲解过。
- 将DDL执行操作记录redo日志
- rename副本ibd文件和frm文件为旧表名，即原user表的frm和ibd文件名
在讲解完添加索引的过程后，我们发现影响业务DML操作的环节包含：
- 循环遍历旧表聚簇索引叶子节点的所有记录，如果表记录非常多，非常消耗CPU，如果DDL长时间占用CPU资源，势必会影响MySQL的连接数，导致MySQL处理DML操作的并发请求数下降
- 归并排序使用的磁盘临时文件做记录排序，如果文件中的已排序记录集非常多，那么，归并排序过程中产生大量的磁盘IO，在MySQL处理查询时，如果内存中没有查询的结果，此时，
buffer pool又满了，触发刷脏行为，这时就会出现查询请求等待刷脏结束，查询响应变慢。
可能这时候你会问，Prepare阶段和Commit阶段都加了排它锁，为什么这两个环节不影响DML操作呢？因为虽然这两个阶段都加了排它锁，但是，加锁后的操作都是小数据规模的操作，所以，加锁时间很短，对DML的影响不大，所以，可以忽略不计。
那么，我们看看上面两个问题怎么解决呢？
针对第一个问题，由于表中的原有记录的数量是由业务发展决定的，业务发展快，记录数就会多，这点我们无法控制，所以，针对表数据量大导致扫描聚簇索引变慢，我们只能规避DDL带来的风险，规避方法如下：
- 评估表中的数据量
- 观察MySQL的CPU使用率
结合上面两个因素，如果数据量不大，那么，只要在非极端高峰期执行DDL，对DML的 影响是不大的。如果数据量很大，建议找到MySQL的CPU使用率比较低的情况下做DDL，保证不影响DML操作.。
针对第二个问题，我们可以通过调整参数innodb_sort_buffer_size，将其调大，使归并排序来源的临时文件中已排序的block数量尽可能少，减少大量block的合并，从而降低磁盘IO
主从模式下的问题
平时我们用的最多的MySQL架构就是主从模式，所以，我们来看一下在这种模式下，在线DDL的过程是怎么样的呢？
- 结合《添加索引》中的过程，我们知道DDL和DML并行阶段，DDL一边执行，DML一边写入Row Log。如上图，左边在master中，DDL和INSERT，以及UPDATE并行执行，DDL在执行的同时，INSERT和UPDATE并行写入Row Log
- DDL和DML并行过程中，将DDL操作和并行的DML按序写入binlog。如上图，左边master将DDL和INSERT、UPDATE操作按序写入binlog，DDL第一、其次是INSERT，最后是UPDATE
- DDL执行结束，将master的binlog同步到slave上。如上图，将左边master的binlog中的三条操作同步到slave上
- 在slave上依次回放DDL和DML。如上图，右边在slave中依次执行DDL、INSERT和UPDATE
通过上面这个过程，你应该已经想到，在DDL和DML并行的阶段，如果产生大量的DML操作，那么，在slave端回放这些DML操作会耗费大量的时间，会影响从库读的数据一致性。所以，这就是主从模式下，在线DDL的问题和风险。
小结
通过本章的讲解，我想你应该对MySQL的在线DDL的机制有了清晰的认识，同时，通过在线DDL机制的讲解，我们也发现了一些优化的方法：
| 目的 | 解决方法 |
|---|---|
| 减少业务影响 | 调大innodb_sort_buffer_size，降低磁盘IO |
| 避免DDL过程中写Row Log溢出 | 调大innodb_online_alter_log_max_size |
| 一定要在高峰期做DDL | 建议使用第三方工具，比如，gh-ost，它是通过binlog完成DDL的，避免了扫描聚簇索引带来的CPU开销 |
欢迎小伙伴们加入知识星球《看透MySQL》，提出宝贵的意见：
https://t.zsxq.com/EQrBAYrt.zsxq.com
扫一扫
2990
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
