MySQL InnoDB MRR Optimization Guide

Preface

MRR, short for Multi-Range Read, is intended to reduce disk random access and convert random access to more sequential access. Applicable to queries of type range/ref/eq_ref.

Implementation principle:

1. After searching the second-level index, find the required data in the cluster index according to the obtained primary key.

The order of the primary keys found by the 2-level index is uncertain, because the order of the 2-level index is different from that of the clustered index;

3. Without MRR, out-of-order reading of data pages may occur during clustered index lookup, which is extremely unfriendly to mechanical hard disks.

4. Optimization mode of MRR:

Put the found level 2 index key value in one cache; Sorting the key values in the cache according to the primary key; According to the sorted primary key, the de-clustering index accesses the actual data file.

5. When the optimizer uses MRR, "Using MRR" appears in the Extra column of the execution plan.

6. If the order of the 2-level index used by the query itself is the same as the order of the result set, then the resulting result set needs to be sorted after using MRR.

Using MRR can also reduce the number of pages being replaced in the buffer pool and batch query operations on key values.

You can use the command select @@optimizer_switch; See if MRR is turned on:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=off,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on

mrr_cost_based=on Indicates whether MRR is selected by cost based.

Use set @@optimizer_switch='mrr=on/off'; Command to turn MRR on or off.

select @@read_rnd_buffer_size ; Parameter is used to control the buffer size of the key value, and the default is 256K. When it is larger than this parameter value, the executor sorts the cached data according to the primary key, and then obtains the row data through the primary key.

Summarize