MySQL InnoDB MRR優(yōu)化指南

前言

MRR 是 Multi-Range Read 的簡(jiǎn)寫(xiě)，目的是減少磁盤(pán)隨機(jī)訪問(wèn)，將隨機(jī)訪問(wèn)轉(zhuǎn)化為較為順序的訪問(wèn)。適用于 range/ref/eq_ref 類(lèi)型的查詢。

實(shí)現(xiàn)原理：

1、在二級(jí)索引查找后，根據(jù)得到的主鍵到聚簇索引找出需要的數(shù)據(jù)。

2、二級(jí)索引查找得到的主鍵的順序是不確定的，因?yàn)槎?jí)索引的順序與聚簇索引的順序不一定一致；

3、如果沒(méi)有 MRR，那么在聚簇索引查找時(shí)就可能出現(xiàn)亂序讀取數(shù)據(jù)頁(yè)，這對(duì)于機(jī)械硬盤(pán)是及其不友好的。

4、MRR 的優(yōu)化方式：

• 將查找到的二級(jí)索引鍵值放在一個(gè)緩存中；
• 將緩存中的鍵值按照 主鍵 進(jìn)行排序；
• 根據(jù)排序后的主鍵去聚簇索引訪問(wèn)實(shí)際的數(shù)據(jù)文件。

5、當(dāng)優(yōu)化器使用了 MRR 時(shí)，執(zhí)行計(jì)劃的 Extra 列會(huì)出現(xiàn) “Using MRR” 。

6、如果查詢使用的二級(jí)索引的順序本身與結(jié)果集的順序一致，那么使用 MRR 后需要對(duì)得到的結(jié)果集進(jìn)行排序。

使用 MRR 還可以減少緩沖池中頁(yè)被替換的次數(shù)，批量處理對(duì)鍵值的查詢操作。

可以使用命令 select @@optimizer_switch; 查看是否開(kāi)啟了 MRR：

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 表示是否通過(guò) cost based 的方式來(lái)選擇使用 MRR 。

用 set @@optimizer_switch='mrr=on/off'; 命令開(kāi)啟或關(guān)閉 MRR 。

select @@read_rnd_buffer_size ; 參數(shù)用來(lái)控制鍵值的緩沖區(qū)大小，默認(rèn) 256K，當(dāng)大于該參數(shù)值時(shí)，執(zhí)行器根據(jù)主鍵對(duì)已緩存的數(shù)據(jù)進(jìn)行排序，然后再通過(guò)主鍵取得行數(shù)據(jù)。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。