MySQL InnoDB MRR Optimization Guide

Preface

MRR is the abbreviation of Multi-Range Read, which aims to reduce random disk access and convert random access into more sequential access. Applicable to range/ref/eq_ref type queries.

Implementation principle:

1. After searching the secondary index, find the required data based on the obtained primary key in the clustered index.

2. The order of the primary keys obtained by secondary index search is uncertain, because the order of the secondary index is not necessarily consistent with the order of the clustered index;

3. If there is no MRR, data pages may be read out of order during clustered index lookup, which is extremely unfriendly to mechanical hard drives.

4. MRR optimization methods:

• Put the found secondary index key value in a cache;
• Sort the key values ​​in the cache by primary key;
• The actual data file is accessed based on the sorted primary key declustered index.

5. When the optimizer uses MRR, “Using MRR” will appear in the Extra column of the execution plan.

6. If the order of the secondary index used in the query is consistent with the order of the result set, then the result set needs to be sorted after using MRR.

Using MRR can also reduce the number of times pages in the buffer pool are replaced and batch process key value query operations.

You can use the command select @@optimizer_switch; to check whether MRR is enabled:

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 to use MRR in a cost based manner.

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

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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.