Lecture
Reading rows using a range scan on a secondary index can lead to a large number of random disk accesses to the underlying table when the table is large and not stored in the storage engine cache. With the Disk-Sweep Multi-Range Read (MRR) optimization, MySQL tries to reduce the number of random disk accesses for a range scan by first scanning only the index and collecting the keys for the matching rows. The keys are then sorted, and finally the rows are retrieved from the underlying table in primary key order. The motivation behind Disk-Sweep MRR is to reduce the number of random disk accesses and instead achieve more sequential scanning of the underlying table's data.
The Multi-Range Read optimization provides the following benefits:
MRR allows data rows to be accessed sequentially rather than in random order, based on index tuples. The server obtains the set of index tuples satisfying the query conditions, sorts them according to the order of the data row identifiers, and uses the sorted tuples to retrieve the data rows in order. This makes data access more efficient and less costly.
MRR allows batching of key-access requests for operations that require accessing data rows via index tuples, such as index range scans and equi-joins that use an index for the join attribute. MRR iterates over a sequence of index ranges to obtain the matching index tuples. As these results accumulate, they are used to access the corresponding data rows. There is no need to obtain all the index tuples before reading of the data rows can begin.
The following scenarios show when the MRR optimization can be beneficial:
Scenario A: MRR can be used for InnoDB and MyISAM tables for an index range scan and equi-join operations.
A portion of the index tuples accumulates in a buffer.
The tuples in the buffer are sorted by data row identifier.
The data rows are accessed according to the ordered sequence of index tuples.
Scenario B: MRR can be used for NDB tables for a multi-range index scan or when performing an equi-join on an attribute.
A portion of the ranges, possibly single-key ranges, accumulates in a buffer on the central node to which the request is sent.
The ranges are sent to the executing nodes, which access the data rows.
The retrieved rows are packed into batches and sent back to the central node.
The received batches of data rows are placed into a buffer.
The data rows are read from the buffer.
When MRR is used, the Extra column in the EXPLAIN output shows Using MRR.
For InnoDB and MyISAM , MRR is not used if there is no need to access the full table rows in order to obtain the query result. This is the case if the results can be obtained entirely from the information in the index tuples (via a covering index); MRR provides no benefit in that case.
Two optimizer_switch system variable flags provide an interface for using the MRR optimization. The mrrflag controls whether MRR is enabled. If mrr is enabled ( on), the mrr_cost_basedflag determines whether the optimizer tries to make a cost-based choice between using and not using MRR ( on), or uses MRR whenever possible ( off). By default, mrris onand mrr_cost_based is on. See «Switchable Optimizations» .
For MRR, the storage engine uses the value of the read_rnd_buffer_sizesystem variable as a guideline for determining how much memory it can allocate for its buffer. The engine uses up to read_rnd_buffer_sizebytes and determines the number of ranges to process in a single pass.
Fig. Example of obtaining information about Using MRR in EXPLAIN in MySQL
As is well known, InnoDB secondary indexes reference the primary key value, and physically on disk the data is stored next to the primary key and sorted by that same primary key. This means that if the index says you need ids 9, 6, 50, 8, and 7, it's more efficient to read them from disk not in that order, but instead re-sort them and read them in two requests: 6-9 and 50. That gives us 2 random-read requests instead of 5. This is very useful for HDDs with their slow mechanics, but it also brings its own, albeit more modest, dividends for SSDs — even though they are orders of magnitude faster than HDDs at random reads, sequential reads are still more convenient for them than random ones.
This is exactly what MRR does. It first obtains the list of needed keys from the index, sorts that list, and requests data from disk not one record at a time in random locations, but in larger sequential blocks.
Note that MRR comes into play when there is a lot to read from disk. That is, a cold read, where most of the data for this selection is not in memory. Naturally, this is far from a fast operation.
One more point: if your query has no order by but has a limit, that means you don't care which specific rows are selected. In that case, MySQL will return any suitable rows in whatever order it sees fit.
Comments