MySQL-5.7-Multi-Range Read Optimization

本文主要是介绍MySQL-5.7-Multi-Range Read Optimization，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

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.

使用disk - sweep Multi-Range Read (MRR)优化，MySQL尝试通过首先只扫描索引并收集相关行的键来减少范围扫描的随机磁盘访问数量。

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.

disk -sweep 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.

两个optimizer_switch系统变量标志提供了一个使用MRR优化的接口。mrr标志控制是否启用mrr。

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.

如果mrr被启用(开启)，mrr_cost_based标志将控制优化器是否尝试在使用和不使用mrr(开启)或尽可能使用mrr(关闭)之间做出基于成本的选择。缺省情况下，mrr为on, mrr_cost_based为on。

See Section 8.9.2, “Switchable Optimizations”.

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.

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

The engine uses up to read_rnd_buffer_size bytes and determines the number of ranges to process in a single pass.

引擎最多使用read_rnd_buffer_size字节，并确定在一次传递中要处理的范围的数量。
​

这篇关于MySQL-5.7-Multi-Range Read Optimization的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！