MySQL 5.6 New Features--Multi-range Read

If the base table is large and the data is not cached, reading rows using a range scan on a level two index can result in a large number of random disk accesses. Using the Multi-range read new feature, MySQL can reduce the number of random reads to the disk: First, MySQL simply scans the index, collects keys for the relevant rows, and then sorts the collected keys, and finally accesses the base table through an orderly primary key.

The purpose of the Multi-range read feature is to reduce random access to the disk, which in turn performs more sequential scans of the base table.

After using MRR, the extra section of the execution plan displays "using MRR"

-InnoDB and MyISAM do not use MRR if a table is required for full table scanning.
-The MRR feature is not available when the index can be overwritten.
-multi-range Read (MRR) can be used for queries of Range, ref, EQ_REF type.

before MRR: because of how MySQL stores data: Secondary indexes are not stored in the same order as the primary key, it can be seen that accessing the data in the table based on the primary key obtained from the secondary index results in random IO. A different primary key is not in the same page, which inevitably results in multiple IO and random reads.

After using MRR:

turn MRR on/off:

Set optimizer_switch= ' Mrr=on mrr_cost_based=on '; #mrr_cost_based表示开启mrr后 whether the optimizer decides whether to use Mrrset oprimizer_switch= ' Mrr=off ' based on cost;

The default is to turn on MRR.

For MRR, the parameter read_rnd_buffer_size is used to control the size of the key-value buffer.

MySQL 5.6 New Features--Multi-range Read