一次查询优化中对MySQL MRR优化的理解

type

status

date

slug

summary

优化过程简要说明

业务表的定义如下

表的默认查询如下，其中时间是必填信息，默认分页查询，每页显示10条

由于时间字段并未设置索引，因此上述查询最终会通过全表扫描来获取数据。

因为我在任务的状态字段上已经设置了索引（可能会有人存在疑问，状态的区分度并不高为什么要在状态字段设置索引，这里我设置的原因是对于查询完成状态的语句来说，使用状态索引的选择性确实不高；但是查询其他状态的情况下，状态索引将会有非常高的选择性）；因此这里即使默认未选择状态，也可以认为是查询所有状态的任务数据，因此上述查询可以改写为如下语句

其中in语句中的状态值是业务系统的所有可能的状态枚举值，通过这样改写，上述查询将能够利用状态索引来查询数据

通过上述查询计划，我们可以得到几点信息:

该查询利用了任务状态二级索引来获取数据；

使用了索引条件下推（ICP）来优化查询，将状态值的对比下推到了InnoDB引擎执行，虽然这个案例中我们使用了全部的状态枚举，但是如果我们使用部分枚举值，将可以通过状态二级索引筛选数据，有效避免不必要的磁盘IO（没有ICP情况下需要先走索引，通过索引回表将数据行所在页加载到内存中做对比才能够做数据筛选）；

💡

我对ICP的理解

使查询能够直接利用二级索引来过滤WHERE条件，减少从磁盘加载数据页到内存中进行对比。

由于查询还指定了时间范围，因此查询中会使用时间来进行数据筛选；

最后一个信息Using MRR才是这次优化的重头戏，是因为之前我并没有对这个优化信息关注过，下面我说明下我认为的MRR优化是如何工作的。

MRR工作原理分析

MRR（Multi-Range Read）背景

在默认情况下我们通过二级索引范围查询筛选数据后，如果需要回表查询且数据页没有被存储在引擎的缓存区中时，会导致出现大量的随机磁盘IO。

MRR是如何工作的

通过MRR优化后，基于二级索引范围查询后，优化器会首先在内存中进行将主键排序（这意味着需要一定的内存空间来完成这一功能），之后再基于已排序的主键回表查询数据，这样就能够将大量的磁盘IO转为顺序磁盘IO来获取表数据。

MRR的优点

查询更有效率：将随机磁盘IO转为了顺序磁盘IO，通过牺牲一部分CPU提高了磁盘访问数据的效率；

批量处理：MRR相当于是把一批通过二级索引筛选过后的主键批量回表获取数据

参考链接

MySQL索引条件下推介绍（https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html）

MySQL官方对于MRR优化的介绍（https://dev.mysql.com/doc/refman/8.0/en/mrr-optimization.html）

MySQL使用MRR时缓冲区参数的设置（https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_rnd_buffer_size）

MySQL优化器相关配置（https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_optimizer_switch）