MySQL-optimized MRR (Multi-Range Read: secondary index merged back to table), mrrmulti-range

MySQL-optimized MRR (Multi-Range Read: secondary index merged back to table), mrrmulti-range

MRR is introduced in MySQL5.6 for optimization:List index range scan and need to return to the table. The principle is to sort multiple secondary indexes that need to be returned to the table based on the primary key, and then return them to the table together to convert the random I/O performed during the original return to the table into sequential I/O. Document address: http://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html

Reading rows usingA range scan on a secondary indexCan result in 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 scansFirst 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.

First, perform a range scan on the secondary index. For keys that meet the criteria, sort them by the primary key, and then read the base table together based on the key.

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 enables batch processing of requests for key access for operations that require access to data rows through index tuples, suchRange 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.

The main advantage of MRR is to convert random IO into sequential IO, which is used for index range scanning and index join;

The following scenarios extends strate when MRR optimization can be advantageous:

Scenario A: MRR can be usedInnoDBAndMyISAMTables for index range scans and equi-join operations.

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.

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

When MRR is used,ExtraColumn inEXPLAINOutput showsUsing MRR.

Example query for which MRR can be used, assuming that there is an index on(key_part1, key_part2):

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

The index consists of tuples(key_part1, key_part2)Values, ordered firstkey_part1And thenkey_part2.

Without MRR, an index scan covers all index tuples forkey_part1Range from 1000 up to 2000, regardless ofkey_part2Value in these tuples. The scan does extra work to the extent that tuples in the range containkey_part2Values other than 10000.

With MRR, the scan is broken up into multiple ranges, each for a single valuekey_part1(1000,100 1,..., 1999). Each of these scans need look only for tupleskey_part2= 10000. If the index contains extends tuples for whichkey_part2Is not 10000, MRR results in your fewer index tuples being read.

To express this using interval notation, the non-MRR scan must examine the index range[{1000, 10000}, {2000, MIN_INT}), Which may include effectuples other than those for whichkey_part2= 10000. The MRR scan examines multiple single-point intervals[{1000, 10000}],...,[{1999, 10000}], Which includes only tupleskey_part2= 10000.

Twooptimizer_switchSystem variable flags provide an interface to the use of MRR optimization.mrrFlag controls whether MRR is enabled. IfmrrIs enabled (on),mrr_cost_basedFlag 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,mrrIsonAndmrr_cost_basedIson. See Section 8.9.2, "Controlling Switchable Optimizations ".

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

The number of secondary indexes scanned by MySQL MRR at a time and then returned to the table. The memory used is determined by referring to the value of read_rnd_buffer_size.

Summary:

MRR only appliesRange scanning of secondary IndexesAndJoin using secondary Indexes.

The advantage of MRR is to convert multiple random IO into a small number of sequential IO. So there are still some values for SSD, but it is less meaningful than mechanical disks.