MySQL Multi-range Read

MySQL version 5.6 provides a number of performance optimization features, one of which is multi-range read multi-range reading (MRR), its role for secondary/second index-based queries, reduce random io, and the random io into sequential io, improve query efficiency.
"Multi-range Read"
The query strategy for the secondary index based on MySQL is as follows: Before MRR, or when the MRR feature is not turned on.
Select Non_key_column from TB wherekey_column=x;

MySQL executes the pseudo-code of the query the first step is to obtain a collection of secondary and primary keys based on the secondary index in the where condition, and the result set is rest.  Selectfromwhere key_column=x ORDER by Key_column The second step obtains the corresponding value by the primary key obtained by the first step.  for in does:Selectfromwhere pk_column=val

Because MySQL stores data in a way that the 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.

In the case of the MRR optimization feature, MySQL is targeting the secondary index-based query strategy:

The first step is to get a collection of secondary and primary keys based on the secondary index in the where condition,      and the result set is rest Select  from where key_column = x ORDER by Key_column The second step puts the result set rest in buffer (read_rnd_buffer_size size until buffer is full), Then the result set rest is sorted by pk_column, and the result set is Rest_sort the third step is to access the data in the table using the sorted result set, which is       the sequential io. Select where inch (Rest_sort)

From the diagram MRR principle, MySQL will be based on the secondary index to obtain the result set according to the primary key to order, will be disorderly order, you can use the primary key sequence to access the base table, the random read into sequential read, multi-page data records can be read in one time or according to the primary key range of sub-read, in order to reduce IO

• Related parameters

we can control whether MRR is used with the flag of the parameter optimizer_switch, which means MRR optimization is enabled when the Mrr=on is set. Mrr_cost_based Indicates whether MRR is enabled by cost base. If you select Mrr=on,mrr_cost_based=off, the MRR optimization is always turned on, and the parameters Read_rnd_buffer_ The size is used to control key-value buffers.

• Case Introduction

When MRR is turned on

MySQL > ExplainSelect* fromTblwhereTbl.key1 between +and -;+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------- ---------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len |ref| Rows | Extra |+----+-------------+-------+-------+---------------+------+---------+------+--- ---+-------------------------------------------+|1| Simple | TBL | Range | Key1 | Key1 |5| NULL |960| Using index condition; Using MRR |+----+-------------+-------+-------+---------------+------+---------+------+------+------------------ -------------------------+1Rowinch Set(0.03Sec

• MRR Application Scenarios

The MRR is suitable for the following two situations.

1. Range Access
2. Ref and EQ_REF access, when they is using batched Key access

MySQL Multi-range Read