如何准确判断什么时候可以给大表加索引 - 崔笑颜的博客

导读

以社交平台的用户表为例，随着业务的快速增长，用户表user单表数据量越来越大，此时，如果我们想给user表添加索引，数据规模对添加过程的影响势必要考虑在内，但是，单表数据规模对添加索引会产生什么样的影响呢，我们在什么样的数据库请求状态下给大表添加索引比较好呢？

今天，我就详细回答一下上面两个问题：

1. 单表数据规模对添加索引会产生什么样的业务影响？
2. 在什么样的数据库请求状态下给大表添加索引比较好？

我们先来看下第一个问题，当我们回答了第一个问题，那么，第二个问题的答案也就浮出水面了。

Row Log

我们先来看一个结构，它叫Row Log，用于在DDL过程中记录DML操作的日志文件。

image-20210102173304826.png

我以user表为例，讲解一下Row Log。它有如下特点：

1. 每个索引对应一个Row Log，如上图为user表的索引index_age_birth对应的Row Log。

Row Log在逻辑上由多个Block组成，每个Block可以存储多个DML操作、一个DML操作也会落在多个Block中。如上图中的Log代表DML操作：

1. 最前面两个Log存在第二个Block中
2. 第3个Log和第4个Log的前半部分存在第三个Block中
3. 第4个Log的后半部分和第5个Log存在最后一个Block中

在物理存储上Row Log分为两部分：

1. 内存日志：内存中会存放一个总大小等于inndob_sort_buffer_size的Block，用于写入DML操作
2. 文件日志：当内存中的Block写满，也就是大小大于innodb_sort_buffer_size，且小于innodb_online_alter_log_max_size时，写满的Block会刷到磁盘上，空出内存中的Block给后续的Log写入，日志文件中，所有Block总大小如果超过innodb_online_alter_log_max_size，写入就会报错

Row Log的核心结构如下：

1. 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>表示操作的记录。
2. head：这是用于将Block中的Log回放到索引树时，用来扫描Block中Log的指针，扫完一个Log，head指针向后移到下一个Log。如上图，因为从Block的头部开始扫描，head指针在回放前处在Block的第一个Log的位置。
3. tail：这是用于将DML操作写入一个Block时，用来定位Block中Log插入位置的指针，插入完一个Log，tail指针向后移动到新插入的Log。如上图，因为从Block的头部开始插入Log，所以，tail指针在插入前处在Block的第一个Log的位置。
4. blocks：无论是head还是tail指针，都包含一个blocks字段，表示Row Log日志文件中包含的Block数量

Row Log追加

下面我们再来看下Log是如何追加到Row Log的？我以user表的index_age_birth索引的Row Log为例来说明：

image-20210102173453682.png

见上图，从上到下，我们来看下这个追加的过程：

1. 如果内存中没有Block，创建一个innodb_sort_buffer_size大小的Block，tail指针指向Block中的第一个Log，如果有Block，tail指针指向Block中最后一个Log。如上图，内存中有Block，tail指向Block中最后一个Log，也就是虚线框前面那个Log
2. 根据即将插入的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操作日志大小
3. 根据tail指针和偏移量，将插入的DML操作日志拷贝到内存的Block。这里同样分两种情况： (1) 全拷贝
   • 如果DML操作日志大小 < innodb_sort_buffer_size - 当前Block中已有Log的总大小，将DML操作日志全部拷贝到Block中末尾Log。如上图，全拷贝最右侧，将DML日志<0x61 + 3355 + <25, 1998-01-02, 1>>完整拷贝到末尾Log，然后，将tail移到被拷贝的Log上

   (2) 半拷贝

   • 如果DML操作日志大小 >= innodb_sort_buffer_size - 当前Block中已有Log的总大小，拷贝DML操作日志的前面部分到tail后面偏移量大小的空间。如上图半拷贝里的上半部分，将DML日志<0x61 + 3355 + <25, 1998-01-02, 1>>的前半部分拷贝到末尾Log，然后，将tail移到被拷贝的Log上
   • 将内存中整个Block写入Row Log日志文件。如上图，半拷贝里上半部分大括号包含了整个Block，同时将该Block通过箭头，写入row_log_file
   • 重新将tail移到内存空Block的头部，将DML操作的后半部分拷贝到tail后面偏移量大小的空间。如上图半拷贝里的下半部分，将DML日志<0x61 + 3355 + <25, 1998-01-02, 1>>的后半部分拷贝到Block的头部
   • 如上图，tail.blocks + 1，代表Row Log日志文件中新增了一个Block。

Row Log回放

MySQL将DML日志写到Row Log只是为了在执行DDL期间，可以并行执行DML，最后，这些DML日志还是要更新(回放)到索引树上的，所以，同样以索引index_age_birth为例，我们再来看下Row Log中的日志是如何更新到索引树的？

image-20210102173617242.png

从上到下，我们来看上面这张图：

1. 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的第二个叶子节点。。
2. 重复步骤1，将Row Log文件中所有Block内的Log全部写入索引树index_age_birth，至此，Row Log文件清空。如上图，文件扫描中最后一个虚线长方框，表示Row Log文件清空。
3. 由于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为例，讲解一下这个过程，见下图：

image-20210102173655135.png

1. 从已排序的记录集中分多批写入内存的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 三条记录。
2. 以bulk为单位，将bulk中的记录集一次插入索引树中。如上图，上面的bulk记录集插入到索引树index_age_birth的第三个叶子节点，下面的bulk记录集插入到索引树index_age_birth的倒数第二个叶子节点。

添加索引

Row Log的追加和回放，以及Bulk Load是添加索引过程中的核心步骤，讲完这三个步骤，下面我再来看一下InnoDB引擎中MySQL添加索引的过程就比较容易理解了，该过程主要分三个阶段，我以user表为例详细讲解一下：

Prepare阶段：

1. 根据旧表user的表结构文件frm，创建一个副本表结构frm文件，将新索引添加到副本中
2. 获得MDL排他锁，禁止读写数据字典及旧user表，关于MDL锁，我会在《MySQL锁全解析》详细讲解
3. 根据alter类型，确定执行方式，一共两种执行方式：COPY、INPLACE
4. 更新内存中的数据字典，标记user表所有索引online_status为ONLINE_INDEX_CREATION，表示该表索引都处在在线DDL状态。关于数据字典的结构，我在《我们可以干预MySQL选择正确的执行计划吗？》中有讲解过。
5. 根据旧表user的ibd文件，创建副本ibd文件

DDL执行阶段：

1. 降级MDL锁为共享锁，允许读写数据字典及旧user表
2. 扫描旧表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写入临时文件
3. 遍历旧表聚簇索引的记录完成后，临时文件中就包含多个block，每个block包含已排序的记录
4. 使用归并排序对临时文件中的block内记录进行排序
5. 遍历副本frm中的聚集索引和辅助索引 (1) 搜索索引树，定位到树种最右边的叶子节点 (2) 判断该节点是否可以有足够空间批量插入记录，如果没有就创建一个新的叶子节点，执行步骤(3)，否则，执行步骤(4) (3) 将新节点接到索引树的右下角，执行步骤(4) (4) 遍历临时文件中的记录，将记录通过bulk load方式写入叶子节点 (5) 调整插入记录的叶子节点内记录的slot信息，关于slot，我在《InnoDB是顺序查找B-Tree叶子节点的吗？》中详细讲解过。
6. 在这个阶段，与此同时，user表的所有DML操作日志写入Row Log，即《Row Log追加》中讲解的过程
7. 重放该阶段产生的user表的Row Log日志到索引中，直到Row Log中的最后一个block，即《Row Log回放》中讲解的过程。

Commit阶段：

1. 升级MDL锁为排它锁，禁止读写数据字典及旧user表
2. 将Row Log中最后一个block，即内存中Block对应的DML日志插入索引树，过程参见DDL执行阶段中的步骤(7)
3. 更新内存中的数据字典，关于数据字典的结构，我在《我们可以干预MySQL选择正确的执行计划吗？》中有讲解过。
4. 将DDL执行操作记录redo日志
5. rename副本ibd文件和frm文件为旧表名，即原user表的frm和ibd文件名

在讲解完添加索引的过程后，我们发现影响业务DML操作的环节包含：

1. 循环遍历旧表聚簇索引叶子节点的所有记录，如果表记录非常多，非常消耗CPU，如果DDL长时间占用CPU资源，势必会影响MySQL的连接数，导致MySQL处理DML操作的并发请求数下降
2. 归并排序使用的磁盘临时文件做记录排序，如果文件中的已排序记录集非常多，那么，归并排序过程中产生大量的磁盘IO，在MySQL处理查询时，如果内存中没有查询的结果，此时，buffer pool又满了，触发刷脏行为，这时就会出现查询请求等待刷脏结束，查询响应变慢。

可能这时候你会问，Prepare阶段和Commit阶段都加了排它锁，为什么这两个环节不影响DML操作呢？因为虽然这两个阶段都加了排它锁，但是，加锁后的操作都是小数据规模的操作，所以，加锁时间很短，对DML的影响不大，所以，可以忽略不计。

那么，我们看看上面两个问题怎么解决呢？ 针对第一个问题，由于表中的原有记录的数量是由业务发展决定的，业务发展快，记录数就会多，这点我们无法控制，所以，针对表数据量大导致扫描聚簇索引变慢，我们只能规避DDL带来的风险，规避方法如下：

1. 评估表中的数据量
2. 观察MySQL的CPU使用率

结合上面两个因素，如果数据量不大，那么，只要在非极端高峰期执行DDL，对DML的 影响是不大的。如果数据量很大，建议找到MySQL的CPU使用率比较低的情况下做DDL，保证不影响DML操作.。 针对第二个问题，我们可以通过调整参数innodb_sort_buffer_size，将其调大，使归并排序来源的临时文件中已排序的block数量尽可能少，减少大量block的合并，从而降低磁盘IO

主从模式下的问题

平时我们用的最多的MySQL架构就是主从模式，所以，我们来看一下在这种模式下，在线DDL的过程是怎么样的呢？

image-20210102165130582.png

1. 结合《添加索引》中的过程，我们知道DDL和DML并行阶段，DDL一边执行，DML一边写入Row Log。如上图，左边在master中，DDL和INSERT，以及UPDATE并行执行，DDL在执行的同时，INSERT和UPDATE并行写入Row Log
2. DDL和DML并行过程中，将DDL操作和并行的DML按序写入binlog。如上图，左边master将DDL和INSERT、UPDATE操作按序写入binlog，DDL第一、其次是INSERT，最后是UPDATE
3. DDL执行结束，将master的binlog同步到slave上。如上图，将左边master的binlog中的三条操作同步到slave上
4. 在slave上依次回放DDL和DML。如上图，右边在slave中依次执行DDL、INSERT和UPDATE

通过上面这个过程，你应该已经想到，在DDL和DML并行的阶段，如果产生大量的DML操作，那么，在slave端回放这些DML操作会耗费大量的时间，会影响从库读的数据一致性。所以，这就是主从模式下，在线DDL的问题和风险。

小结

通过本章的讲解，我想你应该对MySQL的在线DDL的机制有了清晰的认识，同时，通过在线DDL机制的讲解，我们也发现了一些优化的方法：