Multi Range Read与Covering Index是如何优化回表的？
上篇文章末尾我们提出一个问题：有没有什么办法可以尽量避免回表或让回表的开销变小呢？
本篇文章围绕这个问题提出解决方案，一起来看看MySQL是如何优化的
回表
为什么会发生回表？
因为使用的索引并没有整条记录的所有信息，因此使用索引后不满足查询列表需要的列，就要回表查询聚簇索引
回表查询聚簇索引时，由于主键值是乱序的这样就会导致随机IO
什么是随机IO呢？
MySQL查询时，需要将磁盘的数据加载到缓冲池中，与磁盘交互的单位是页，页中存在多条记录
由于获取的是聚簇索引的页，那么该页中的主键值是有序的，但在二级索引上的记录主键值可能并不是有序的
比如图中第一条记录主键值为24记录在页A中，第二条记录主键值为82546记录在页C中
当遍历到第一条记录时需要去加载页A，当遍历下一条记录时需要去加载页C
当这种随机IO过多时，可能每查一条记录相当于要去加载一个页，成本非常大
不要小瞧回表的开销，当查询数据量大，使用二级索引都要回表的话，性能还不如全表扫描（扫描聚簇索引），这通常也是索引失效的一大场景（后续文章再来聊聊这块）
Multi Range Read 多范围读取
那有没有什么办法降低成本呢？
回表成本大的原因主要是产生随机IO，那能不能先在索引上查出多条记录，要回表时对主键值进行排序，让随机IO变成顺序IO呢
对主键值排序后每个加载的页，页中可能存在多条需要回表查询的记录就减少回表随机IO的开销
MySQL中另一个优化回表的手段是：Multi Range Read 多范围读取 MRR
MRR使用缓冲区对需要回表的记录根据主键值进行排序，将随机IO优化为顺序IO
使用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
除了将随机IO优化为顺序IO，还有没有什么方式可以降低回表的开销呢？
我们从另一个角度分析，如果减少查询的数据量，是不是也可以减少回表次数，降低回表开销
那如何减少数据量呢？实际上上篇文章说过的ICP就可以减低回表次数
Covering Index 覆盖索引
回表无论如何优化都会存在一定的开销，那有没有可能避免回表呢？
要避免回表问题，那就要知道为什么会回表？
由于使用的二级索引不包含查询需要的字段，因此需要回表查询聚簇索引获取需要的字段
那如果使用的二级索引包含需要的查询字段是不是就避免回表的呢！
因此可以通过修改查询需要的字段select xx1,xx2或 增加二级索引包含的列（变成联合索引）来避免出现回表
注意：如果你想通过增加二级索引的列来避免回表时，需要评估二级索引存在列太多的维护成本
MySQL中的覆盖索引指的是使用二级索引时不需要回表，在执行计划中的附加信息显示Using index
将查询列表从 * 改为 age,student_name ,使用二级索引时不需要回表
总结
当使用的二级索引不满足查询需要的列时，会进行回表查询聚簇索引获取完整记录
回表不仅需要再查一次聚簇索引，而且在二级索引中主键值可能是乱序的，因此查询聚簇索引会出现随机IO
查询随机IO时可能每条记录都在不同的页中，这会导致每查询一条记录就需要将磁盘中的页加载到缓冲池，随机IO开销很大
优化回表有两种思路：一种是降低回表的开销，另一种是避免回表
Index Condition Push 索引条件下推（上篇文章说的）可以减少回表次数，降低回表的开销
Multi Range Read 多范围读取在某些场景下使用缓冲池排序主机，将读取的随机IO转换为顺序IO，降低回表开销
修改查询需要的字段或者给二级索引上增加列，使用覆盖索引的方式来避免回表
原文地址：https://blog.csdn.net/s44359487yad/article/details/140720451
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！