MySQL的优化利器⭐️Multi Range Read与Covering Index是如何优化回表的？

前言

上篇文章MySQL的优化利器：索引条件下推，千万数据下性能提升273%🚀，我们说到MySQL中server层与存储引擎层的交互、索引、回表、ICP等知识（有不理解的概念可以看上篇文章哈~）

上篇文章末尾我们提出一个问题：有没有什么办法可以尽量避免回表或让回表的开销变小呢？

本篇文章围绕这个问题提出解决方案，一起来看看MySQL是如何优化的

回表

为什么会发生回表？

因为使用的索引并没有整条记录的所有信息，因此使用索引后不满足查询列表需要的列，就要回表查询聚簇索引

image.png

回表查询聚簇索引时，由于主键值是乱序的这样就会导致随机IO

什么是随机IO呢？

MySQL查询时，需要将磁盘的数据加载到缓冲池中，与磁盘交互的单位是页，页中存在多条记录

由于获取的是聚簇索引的页，那么该页中的主键值是有序的，但在二级索引上的记录主键值可能并不是有序的

image.png

比如图中第一条记录主键值为24记录在页A中，第二条记录主键值为82546记录在页C中

当遍历到第一条记录时需要去加载页A，当遍历下一条记录时需要去加载页C

当这种随机IO过多时，可能每查一条记录相当于要去加载一个页，成本非常大

不要小瞧回表的开销，当查询数据量大，使用二级索引都要回表的话，性能还不如全表扫描（扫描聚簇索引），这通常也是索引失效的一大场景（后续文章再来聊聊这块）

Multi Range Read 多范围读取

那有没有什么办法降低成本呢？

回表成本大的原因主要是产生随机IO，那能不能先在索引上查出多条记录，要回表时对主键值进行排序，让随机IO变成顺序IO呢

对主键值排序后每个加载的页，页中可能存在多条需要回表查询的记录就减少回表随机IO的开销

MySQL中另一个优化回表的手段是：Multi Range Read 多范围读取 MRR

MRR使用缓冲区对需要回表的记录根据主键值进行排序，将随机IO优化为顺序IO

image.png

使用MRR优化后图中第二条记录id为25回表时就可以直接在缓冲池的页A中获取完整记录

查看MRR缓冲池大小show variables like '%read_rnd_buffer_size%';

可以使用查看相关优化器的参数SHOW VARIABLES LIKE 'optimizer_switch';

有关MRR的优化器开关参数：mrr，mrr_cost_based

mrr 表示是否开启MRR

MRR还需要在缓冲池中排序的开销，因此并不是所有场景都用MRR，默认情况下启动mrr_cost_based基于成本判断是否要使用MRR

SET optimizer_switch='mrr=on,mrr_cost_based=off';关闭根据成本判断是否用MRR

附加信息携带Using MRR说明使用MRR

image.png

除了将随机IO优化为顺序IO，还有没有什么方式可以降低回表的开销呢？

我们从另一个角度分析，如果减少查询的数据量，是不是也可以减少回表次数，降低回表开销

那如何减少数据量呢？实际上上篇文章说过的ICP就可以减低回表次数

Covering Index 覆盖索引

回表无论如何优化都会存在一定的开销，那有没有可能避免回表呢？

要避免回表问题，那就要知道为什么会回表？

由于使用的二级索引不包含查询需要的字段，因此需要回表查询聚簇索引获取需要的字段

那如果使用的二级索引包含需要的查询字段是不是就避免回表的呢！

因此可以通过修改查询需要的字段select xx1,xx2或 增加二级索引包含的列（变成联合索引）来避免出现回表

注意：如果你想通过增加二级索引的列来避免回表时，需要评估二级索引存在列太多的维护成本

MySQL中的覆盖索引指的是使用二级索引时不需要回表，在执行计划中的附加信息显示Using index

image.png

将查询列表从 * 改为 age,student_name ,使用二级索引时不需要回表

总结

当使用的二级索引不满足查询需要的列时，会进行回表查询聚簇索引获取完整记录

回表不仅需要再查一次聚簇索引，而且在二级索引中主键值可能是乱序的，因此查询聚簇索引会出现随机IO

查询随机IO时可能每条记录都在不同的页中，这会导致每查询一条记录就需要将磁盘中的页加载到缓冲池，随机IO开销很大

优化回表有两种思路：一种是降低回表的开销，另一种是避免回表

Index Condition Push 索引条件下推（上篇文章说的）可以减少回表次数，降低回表的开销

Multi Range Read 多范围读取在某些场景下使用缓冲池排序主机，将读取的随机IO转换为顺序IO，降低回表开销

修改查询需要的字段或者给二级索引上增加列，使用覆盖索引的方式来避免回表

最后（不要白嫖，一键三连求求拉~）

本篇文章被收入专栏 由点到线，由线到面，构建MySQL知识体系，感兴趣的同学可以持续关注喔

本篇文章笔记以及案例被收入 gitee-StudyJava、 github-StudyJava 感兴趣的同学可以stat下持续关注喔~

有什么问题可以在评论区交流，如果觉得菜菜写的不错，可以点赞、关注、收藏支持一下~

关注菜菜，分享更多干货，公众号：菜菜的后端私房菜

我正在参与2024腾讯技术创作特训营最新征文，快来和我瓜分大奖！