Shulou(Shulou.com)06/01 Report--

An introduction

MySQL version 5.6 provides many performance optimization features, one of which is Multi-Range Read Multi-range read (MRR), which aims at queries based on secondary / second index, reduces random IO, and converts random IO to sequential IO to improve query efficiency.

Two principles

Before MRR, or when the MRR feature was not enabled, MySQL's query strategy based on secondary indexes was as follows:

Pseudo code for select non_key_column from tb wherekey_column=x;MySQL to execute the query

The first step is to get the collection of secondary index and primary key according to the secondary index in the where condition, and the result set is rest.

Select key_column, pk_column from tb where key_column=x order by key_column

The second step is to obtain the corresponding value through the primary key obtained in the first step.

For each pk_column value in rest do:

Select non_key_column from tb where pk_column=val

Because of the way MySQL stores data: the storage order of the secondary index is not the same as the order of the primary key, it can be seen from the figure that accessing the data in the table according to the primary key obtained by the secondary index will lead to random IO. When different primary keys are not in the same page, it will inevitably lead to multiple IO and random reads.

When using the MRR optimization feature, the MySQL query strategy based on the secondary index is as follows:

The first step is to obtain the set of secondary index and primary key according to the secondary index in the where condition, and the result set is rest

Select key_column, pk_column from tb where key_column = x order by key_column

The second step is to put the result set rest in the buffer (read_rnd_buffer_size size until buffer is full), then sort the result set rest according to pk_column, and get the result set rest_sort.

The third step is to access the data in the table using the sorted result set, which is sequential IO.

Select non_key_column fromtb where pk_column in (rest_sort)

According to the principle of MRR shown in the diagram, MySQL sorts the result sets obtained according to the secondary index according to the primary key and turns disorder into order. The base table can be accessed sequentially with the primary key, random reading can be converted into sequential reading, and multi-page data records can be read in one time or in stages according to the primary key range, so as to reduce IO operations and improve query efficiency.

Three related parameters

We can control whether or not to use MRR by marking the parameter optimizer_switch, which means that MRR optimization is enabled when mrr=on is set. Mrr_cost_based indicates whether to enable MRR through cost base. If mrr=on,mrr_cost_based=off is selected, MRR optimization is always turned on.

The parameter read_rnd_buffer_size is used to control the size of the key buffer.

Introduction of four cases

When MRR is turned on

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 |

+-+-

Usage limit of 1 row in set (0.03 sec) five MRR

MRR applies to the following two situations.

1 range access

2 ref and eq_ref access, when they are using Batched Key Access

Six reference articles

"MariaDB Multi-Range Read Optimization"

"MySQL Multi-Range Read Optimization"

"Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.