8.2.1.10 MySQL MRR 多范围读优化

8.2.1.10 Multi-Range Read Optimization 8.2.1.10多范围读优化

Reading rows using a range scan on a secondary index can result in many random disk accesses to the base table when the table is large and not stored in the storage engine's cache. With the Disk-Sweep Multi-Range Read (MRR) optimization, MySQL tries to reduce the number of random disk access for range scans by first scanning the index only and collecting the keys for the relevant rows. Then the keys are sorted and finally the rows are retrieved from the base table using the order of the primary key. The motivation for Disk-sweep MRR is to reduce the number of random disk accesses and instead achieve a more sequential scan of the base table data.

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

The Multi-Range Read optimization provides these benefits:

多范围读优化提供了以下优点:

• MRR enables data rows to be accessed sequentially rather than in random order, based on index tuples. The server obtains a set of index tuples that satisfy the query conditions, sorts them according to data row ID order, and uses the sorted tuples to retrieve data rows in order. This makes data access more efficient and less expensive.

  MRR 支持基于索引元组顺序访问数据行，而不是随机访问。服务器获取一组满足查询条件的索引元组，根据数据行 ID 顺序对它们进行排序，并使用排序元组按顺序检索数据行。这使得数据访问更有效率，更便宜。

• MRR enables batch processing of requests for key access for operations that require access to data rows through index tuples, such as range index scans and equi-joins that use an index for the join attribute. MRR iterates over a sequence of index ranges to obtain qualifying index tuples. As these results accumulate, they are used to access the corresponding data rows. It is not necessary to acquire all index tuples before starting to read data rows.

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

The MRR optimization is not supported with secondary indexes created on virtual generated columns. InnoDB supports secondary indexes on virtual generated columns.

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

The following scenarios illustrate when MRR optimization can be advantageous:

下面的场景说明了 MRR 优化在什么情况下是有利的:

Scenario A: MRR can be used for InnoDB and MyISAM tables for index range scans and equi-join operations.

场景 a: MRR 可用于 InnoDB 和 MyISAM 表，用于索引范围扫描和等量连接操作。

1. A portion of the index tuples are accumulated in a buffer.

   部分索引元组被累积在缓冲区中。

2. The tuples in the buffer are sorted by their data row ID.

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

3. Data rows are accessed according to the sorted index tuple sequence.

   根据排序的索引元组序列访问数据行。

Scenario B: MRR can be used for NDB tables for multiple-range index scans or when performing an equi-join by an attribute.

场景 b: MRR 可用于 NDB 表，用于进行多范围索引扫描或通过属性执行等量连接。

1. A portion of ranges, possibly single-key ranges, is accumulated in a buffer on the central node where the query is submitted.

   部分范围(可能是单键范围)累积在提交查询的中心节点上的缓冲区中。

2. The ranges are sent to the execution nodes that access data rows.

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

3. The accessed rows are packed into packages and sent back to the central node.

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

4. The received packages with data rows are placed in a buffer.

   带有数据行的接收包被放置在缓冲区中。

5. Data rows are read from the buffer.

   从缓冲区读取数据行。

When MRR is used, the Extra column in EXPLAIN output shows Using MRR.

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

InnoDB and MyISAM do not use MRR if full table rows need not be accessed to produce the query result. This is the case if results can be produced entirely on the basis on information in the index tuples (through a covering index); MRR provides no benefit.

如果不需要访问完整的表行来生成查询结果，InnoDB 和 MyISAM 不使用 MRR。如果可以完全根据索引元组中的信息(通过覆盖索引)生成结果，则属于这种情况; MRR 不提供任何好处。

Two optimizer_switch system variable flags provide an interface to the use of MRR optimization. The mrr flag controls whether MRR is enabled. If mrr is enabled (on), the mrr_cost_based flag controls whether the optimizer attempts to make a cost-based choice between using and not using MRR (on) or uses MRR whenever possible (off). By default, mrr is on and mrr_cost_based is on. See Section 8.9.2, “Switchable Optimizations”.

两个 optimizer_switch 系统变量标志提供了一个使用 MRR 优化的接口。mrr 标志控制是否启用 MRR。如果启用了 mrr (on) ，那么 mrr_cost_based 标志将控制优化器是否试图在使用和不使用 MRR (on)或尽可能使用 MRR (off)之间做出基于成本的选择。默认情况下，mrr 为 on，mrr_cost_based 为 on。参见8.9.2节，“可切换优化”。

For MRR, a storage engine uses the value of the read_rnd_buffer_size system variable as a guideline for how much memory it can allocate for its buffer. The engine uses up to read_rnd_buffer_size bytes and determines the number of ranges to process in a single pass.

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

————————————————

翻译原文：https://dev.mysql.com/doc/refman/5.7/en/mrr-optimization.html