【MySQL精通之路】SQL优化(1)-查询优化(11)-多范围查询优化

主博客：

【MySQL精通之路】SQL优化(1)-查询优化-CSDN博客

上一篇：

【MySQL精通之路】SQL优化(1)-查询优化(10)-外部联接简化-CSDN博客

下一篇：

当基表很大且未存储在存储引擎的缓存中时，使用辅助索引上的范围扫描读取行可能会导致对基表的多次随机磁盘访问。通过磁盘扫描多范围读取（MRR）优化，MySQL试图通过首先仅扫描索引并收集相关行的key来减少范围扫描的随机磁盘访问次数。然后对键进行排序，最后使用主键的顺序从基表中检索行。磁盘扫描MRR的动机是减少随机磁盘访问的次数，而不是实现对基表数据的更有序的扫描。

多范围读取优化提供了以下好处：

MRR使数据行能够基于索引元组按顺序访问，而不是按随机顺序访问。服务器获取一组满足查询条件的索引元组，根据数据行ID顺序对其进行排序，并使用排序后的元组按顺序检索数据行。这使得数据访问更加高效且成本更低。

MRR允许批量处理需要通过索引元组访问数据行的操作的密钥访问请求，例如范围索引扫描和使用联接属性索引的等联接。MRR对一系列索引范围进行迭代，以获得合格的索引元组。随着这些结果的积累，它们被用来访问相应的数据行。在开始读取数据行之前，不必获取所有索引元组。

在虚拟生成列上创建的辅助索引不支持MRR优化。InnoDB支持虚拟生成列的二级索引。

以下场景说明了MRR优化何时是有利的：

场景A：MRR可用于InnoDB和MyISAM表，用于索引范围扫描和等联接操作。

1.索引元组的一部分被累积在缓冲器中。

2.缓冲区中的元组按其数据行ID进行排序。

3.根据排序后的索引元组序列来访问数据行。

场景B:MRR可用于NDB表，用于多个范围索引扫描或按属性执行等联接。

1.一部分范围，可能是单键范围，被累积在提交查询的中心节点上的缓冲区中。

2.范围被发送到访问数据行的执行节点。

3.访问的行被打包到包中，并发送回中心节点。

4.接收到的具有数据行的包被放置在缓冲区中。

5.从缓冲区读取数据行。

当使用MRR时，EXPLAIN输出中的Extra列显示Using MRR。

如果不需要访问完整的表行来生成查询结果，那么InnoDB和MyISAM不使用MRR。

如果结果可以完全基于索引元组中的信息（通过覆盖索引）产生，则是这种情况；MRR没有任何好处。

两个优化器开关系统变量标志为MRR优化的使用提供了接口。mrr标志控制是否启用mrr。如果启用了mrr（启用），则基于mrr_cost_based标志控制优化器是尝试使用和不使用mrr（启用）之间进行基于成本的选择，还是尽可能使用mrr。

默认情况下，mrr处于启用状态，mrr_cost_based处于启用状态。

请参阅“可切换优化”。

对于MRR，存储引擎使用read_rnd_buffer_size系统变量的值作为可以为其缓冲区分配多少内存的准则。

引擎最多使用read_rnd_buffer_size字节，并确定单次处理的范围数。