【MySQL系列--优化3】——多范围读取优化

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

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

1. 基于索引元组，MRR使数据行可以顺序访问，而不是以随机顺序访问。服务器获取一组满足查询条件的索引元组，根据数据行ID顺序进行排序，并使用排序的元组依次检索数据行。这使得数据访问更加高效和更便宜。
2. MRR允许对需要通过索引元组访问数据行的操作进行密钥访问请求的批处理，例如范围索引扫描和使用连接属性的索引的等式连接。 MRR遍历一系列索引范围以获得符合条件的索引元组。当这些结果累积时，它们被用于访问相应的数据行。在开始读取数据行之前，不必获取所有索引元组。

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

以下情况说明MRR优化可能是有利的：

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

1. 索引元组的一部分被累积在缓冲器中。
2. 缓冲区中的元组按其数据行ID排序
3. 根据排序的索引元组序列访问数据行。

场景B：MRR可用于多范围索引扫描的NDB表，或者通过属性执行等连接时。

1. 范围的一部分，可能是单键范围，累积在提交查询的中心节点上的缓冲区中。
2. 范围被发送到访问数据行的执行节点。
3. 所访问的行被打包到包中并发送回中心节点.
4. 接收到的包含数据行的包将放置在缓冲区中。
5. 从缓冲区中读取数据行

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

如果不需要访问完整的表行以产生查询结果，InnoDB和MyISAM不使用MRR。如果结果可以完全基于索引元组中的信息（通过覆盖索引）生成，则是这种情况; MRR没有任何好处。

假设在（key_part1，key_part2）上有一个索引，可以使用哪个MRR的示例查询：

SELECT * FROM t
  WHERE key_part1 >= 1000 AND key_part1 < 2000
  AND key_part2 = 10000;

索引由（key_part1，key_part2）值的元组组成，首先由key_part1，然后由key_part2排序。

没有MRR，索引扫描覆盖了key_part1范围从1000到2000的所有索引元组，而不管这些元组中的key_part2值。该扫描执行额外的工作，范围内的元组包含除10000之外的key_part2值。

使用MRR，扫描被分解成多个范围，每个范围分别为key_part1（1000,1001，…，1999）的单个值。这些扫描中的每一个都只需要查找key_part2 = 10000的元组。如果索引包含许多key_part2不是10000的元组，则MRR会导致读取的索引元组少得多。

为了使用间隔符号表示，非MRR扫描必须检查索引范围[{1000，10000}，{2000，MIN_INT}），其可能包括除了key_part2 = 10000之外的许多元组.MRR扫描检查多个单点间隔[{1000，10000}]，…，[{1999，10000}]，其中仅包含key_part2 = 10000的元组。

两个optimizer_switch系统变量标志提供了使用MRR优化的接口。 mrr标志控制是否启用MRR。如果mrr启用（on），那么mrr_cost_based标志控制优化器是否尝试在使用和不使用MRR（on）之间进行基于成本的选择，或者尽可能使用MRR（关闭）。默认情况下，mrr为on，mrr_cost_based为on。请参见第8.9.3节“可切换优化”。

对于MRR，存储引擎使用read_rnd_buffer_size系统变量的值作为其缓冲区可以分配多少内存的指导。引擎最多使用read_rnd_buffer_size字节，并确定单次传递中要处理的范围数。