New Features of MySQL5.6 Multi-Range Read

New Features of MySQL5.6 Multi-Range Read

1. Introduction
MySQL 5.6 provides many performance optimization features, one of which is Multi-Range Read (MRR). It is used for queries based on secondary/secondary indexes, reduce random IO and convert random IO into sequential IO to improve query efficiency.
Principle 2
Before MRR is available or the MRR feature is not enabled, MySQL uses the following query policy for secondary indexes:

Select non_key_column from tb wherekey_column = x;
Pseudocode for MySQL to execute the query

The first step is to obtain the set of secondary indexes and primary keys based on the secondary indexes in the where condition. The result set is rest.
 
Select key_column, pk_column from tb where key_column = x order by key_column

Step 2: obtain the corresponding value through the primary key obtained in step 1.

For each pk_column value in rest do:

Select non_key_column from tb where pk_column = val

Because MySQL stores data: the storage sequence of secondary indexes is not the same as that of primary keys, it can be seen that accessing table data based on the primary keys obtained by secondary indexes will lead to random IO. when different primary keys are not in the same page, multiple IO and random reads will inevitably occur.

When MRR optimization is used, MySQL adopts the following query policy for secondary indexes:

The first step is to obtain a set of secondary indexes and primary keys based on the secondary indexes in the where condition. The result set is rest.
 
Select key_column, pk_column from tb where key_column = x order by key_column

Step 2: place the result set rest in the buffer (read_rnd_buffer_size until the buffer is full), and sort the result set rest by pk_column. The result set is rest_sort.

Step 3: Use the sorted result set to access the data in the table. This is sequential IO.

Select non_key_column fromtb where pk_column in (rest_sort)

According to the MRR principle in the figure below, MySQL sorts the result set obtained by secondary indexes according to the primary key, and orders the result set in disorder. You can use the primary key to access the base table and convert random reads to sequential reads, multi-page data records can be read at one time or divided based on the primary key range to reduce IO operations and improve query efficiency.

Three Parameters
We can use the flag of the optimizer_switch parameter to determine whether to use MRR. When mrr = on is set, MRR optimization is enabled. Mrr_cost_based indicates whether to enable MRR through cost base. If mrr = on and mrr_cost_based = off is selected, MRR optimization is always enabled.
The read_rnd_buffer_size parameter controls the size of the key-value buffer.

4. Case Study
When MRR is enabled

MySQL> explain select * from tbl where tbl. key1 between 1000 and 2000;
 
+ ---- + ------------- + ------- + --------------- + ------ + --------- + ------ + --------------------------------------------- +

| 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 |

+ ---- + ------------- + ------- + --------------- + ------ + --------- + ------ + --------------------------------------------- +

1 row in set (0.03 sec)
Limits of MRR
MRR applies to the following two scenarios.
1 range access
2 ref and eq_ref access, when they are using Batched Key Access

This article permanently updates the link address: