MRR針對(duì)于輔助索引上的范圍查詢進(jìn)行優(yōu)化,收集輔助索引對(duì)應(yīng)主鍵rowid。進(jìn)行排序后回表查詢抽莱，隨機(jī)IO轉(zhuǎn)順序IO
當(dāng)我們需要對(duì)大表(基于輔助索引)進(jìn)行范圍掃描時(shí),會(huì)導(dǎo)致產(chǎn)生許多隨機(jī)/O剩膘。而對(duì)于普通磁盤來說,隨機(jī)的性能很差,會(huì)遇到瓶頸,在 MySQL 5.6/5.7和MariaDB5.3/5.5/10.0/10.1版本里對(duì)這種情況進(jìn)行了優(yōu)化,一個(gè)新的名詞 Multi Range Read(MRR)出現(xiàn)了,優(yōu)化器會(huì)先掃描輔助索引,然后收集每行的主鍵（rowid ）,并對(duì)主鍵進(jìn)行排序（排序結(jié)果存儲(chǔ)到read_rnd_buffer),此時(shí)就可以用主鍵順序訪問基表,即用順序IO代替隨機(jī)IO推穷。
而MRR的優(yōu)化在于慧耍，并不是每次通過輔助索引讀取到數(shù)據(jù)就回表去取記錄既绩，范圍掃描（range access）中MySQL將掃描到的數(shù)據(jù)存入由 read_rnd_buffer_size 變量定義的內(nèi)存大小中概龄，默認(rèn)256K。然后對(duì)其按照Primary Key（RowID）排序饲握，然后使用排序好的數(shù)據(jù)進(jìn)行順序回表私杜，因?yàn)槲覀冎繧nnoDB中葉子節(jié)點(diǎn)數(shù)據(jù)是按照PRIMARY KEY（ROWID）進(jìn)行順序排列的蚕键，所以我們可以認(rèn)為，如果按照主鍵的遞增順序查詢的話衰粹，對(duì)磁盤的讀比較接近順序讀锣光，能夠提升讀性能。這對(duì)于IO-bound類型的SQL查詢語句帶來性能極大的提升寄猩。
MRR 能夠提升性能的核心在于嫉晶，這條查詢語句在索引上做的是一個(gè)范圍查詢（也就是說，這是一個(gè)多值查詢）田篇，可以得到足夠多的主鍵id替废。這樣通過排序以后，再去主鍵索引查數(shù)據(jù)泊柬，才能體現(xiàn)出“順序性”的優(yōu)勢椎镣。所以MRR優(yōu)化可用于range，ref兽赁，eq_ref類型的查詢状答，工作方式如下圖：
簡單說：使用輔助索引進(jìn)行范圍查詢時(shí)，MRR會(huì)收集并排序好符合范圍查詢條件的rowid亮钦。然后通過順序的rowid去回表查詢數(shù)據(jù)記錄馆截。 MRR 通過把「隨機(jī)磁盤讀」，轉(zhuǎn)化為「順序磁盤讀」蜂莉，從而提高了索引查詢的性能蜡娶。
mysql中mrr相關(guān)操作
mysql默認(rèn)開啟MRR優(yōu)化。但是由優(yōu)化器決定是否真正使用MRR（mrr=on,mrr_cost_based=on）映穗，因?yàn)橛行r(shí)候優(yōu)化器認(rèn)為不使用MRR性能會(huì)更好窖张！查詢MRR的開啟狀態(tài)如下：
SHOW VARIABLES LIKE '%optimizer_switch%'
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=on,mrr_cost_based=on,block_nested_loop=off,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
關(guān)閉MRR優(yōu)化，不強(qiáng)制使用MRR
set optimizer_switch='mrr=off,mrr_cost_based=on';
k_1是sbtest1 表的一個(gè)輔助索引蚁滋，執(zhí)行計(jì)劃如下：
EXPLAIN SELECT * from sbtest1 force index(k_1) where k BETWEEN 1000 and 55555
再來看開啟MRR宿接，并強(qiáng)制使用≡迹可以看到執(zhí)行計(jì)劃的Extra列多了Using MRR
set optimizer_switch='mrr=on,mrr_cost_based=off';
mrr=on會(huì)開啟MRR優(yōu)化功能澄阳，mrr_cost_based 則是用來告訴優(yōu)化器，要不要基于使用 MRR 的成本踏拜，考慮使用 MRR 是否值得（cost-based choice），來決定具體的 sql 語句里要不要使用 MRR低剔。很明顯速梗，對(duì)于只返回一行數(shù)據(jù)的查詢肮塞，是沒有必要 MRR 的，而如果你把 mrr_cost_based 設(shè)為 off姻锁，那優(yōu)化器就會(huì)通通使用 MRR枕赵。建議這個(gè)配置還是設(shè)為 on，畢竟優(yōu)化器在絕大多數(shù)情況下都是正確的位隶。另外還有一個(gè)配置 read_rnd_buffer_size 拷窜，是用來設(shè)置用于給 rowid 排序的內(nèi)存的大小。顯然涧黄，MRR 在本質(zhì)上是一種用空間換時(shí)間的算法篮昧。MySQL 不可能給你無限的內(nèi)存來進(jìn)行排序，如果 read_rnd_buffer 滿了笋妥，就會(huì)先把滿了的 rowid 排好序去磁盤讀取懊昨，接著清空，然后再往里面繼續(xù)放 rowid春宣，直到 read_rnd_buffer 又達(dá)到 read_rnd_buffe 配置的上限酵颁，如此循環(huán)。
另外 MySQL 的其中一個(gè)分支 Mariadb 對(duì) MySQL 的 MRR 做了很多優(yōu)化月帝。
SHOW VARIABLES LIKE '%read_rnd_buffer%' -- 262144 字節(jié) 0.25M
注意：MRR 只是針對(duì)優(yōu)化回表查詢的速度躏惋，當(dāng)不需要回表訪問的時(shí)候，MRR就失去意義了（比如覆蓋索引）
optimizer_switch可以是全局的嚷辅，也可以是會(huì)話級(jí)的簿姨。當(dāng)然，除了調(diào)整參數(shù)外潦蝇，數(shù)據(jù)庫也提供了語句級(jí)別的開啟或關(guān)閉MRR款熬，使用方法如下：
EXPLAIN SELECT /*+ MRR(sbtest1)*/ * from sbtest1 force index(k_1) where k BETWEEN 1000 and 55555
