Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of InnoDB MRR optimization in MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Preface

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

The principle of implementation:

1. After searching the secondary index, find out the required data from the primary key to the clustered index.

2. The order of the primary keys found by the secondary index is uncertain, because the order of the secondary index is not necessarily the same as that of the clustered index.

3. If there is no MRR, it is possible to read data pages out of order when searching in a clustered index, which is very unfriendly to mechanical hard drives.

4. The optimization of MRR:

Put the found secondary index key value in a cache

Sort the key values in the cache by primary key

According to the sorted primary key to cluster the index to access 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 secondary index used by the query itself is the same as the order of the result set, then you need to sort the resulting result set after using MRR.

Using MRR can also reduce the number of page replacements in the buffer pool and batch process query operations on key values.

You can use the command select @ @ optimizer_switch; to 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 to choose to use MRR through cost based.

Turn MRR on or off with the set @ @ optimizer_switch='mrr=on/off'; command.

The select @ @ read_rnd_buffer_size; parameter is used to control the buffer size of the key value, which defaults to 256k. When it is greater 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.

This is the end of this article on "sample Analysis of InnoDB MRR Optimization in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.