自学内容网 自学内容网

Multi Range Read与Covering Index是如何优化回表的?

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

本篇文章围绕这个问题提出解决方案,一起来看看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的优化器开关参数:mrrmrr_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,降低回表开销

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


原文地址:https://blog.csdn.net/s44359487yad/article/details/140720451

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!