在探索數(shù)據(jù)庫優(yōu)化的廣闊領(lǐng)域中，我們不可避免地會(huì)遇到一系列獨(dú)特的概念和技術(shù)。其中之一就是MySQL的多范圍讀取(Multi-Range Read, MRR)。

這種技術(shù)為我們提供了在處理大量數(shù)據(jù)時(shí)提高查詢效率的強(qiáng)大手段。它通過改變數(shù)據(jù)檢索的順序，并利用操作系統(tǒng)緩存進(jìn)行預(yù)讀，從而顯著減少I/O操作數(shù)量，提高查詢速度。本文將深入探討MRR的內(nèi)部工作原理，以及如何在日常數(shù)據(jù)庫管理中有效地應(yīng)用這種技術(shù)。

什么是MRR

MRR 是優(yōu)化器將隨機(jī) IO 轉(zhuǎn)化為順序 IO 以降低查詢過程中 IO 開銷的一種手段。

了解MRR之前，我們先來了解下「回表」。

回表是MySQL在執(zhí)行查詢時(shí)的一個(gè)步驟，它通常發(fā)生在使用索引進(jìn)行搜索之后。當(dāng)MySQL在索引中找到了需要的數(shù)據(jù)，但這些數(shù)據(jù)并不完全滿足查詢需求時(shí)（比如，索引沒有包含所有需要的列），MySQL就需要回到主表中去獲取完整的行數(shù)據(jù)，這個(gè)過程就被稱為"回表"。

舉例來說，如果查詢語句中有一些列沒有被包含在索引中，那么即使從索引中能查到部分信息，也還需要回到原始表中獲取其他列的信息，這就是所謂的"回表"操作。為了提高查詢效率，我們可以盡量減少回表操作，例如通過使用「覆蓋索引（Covering Index）」。

我們知道二級(jí)索引是有回表的過程的，由于二級(jí)索引上引用的主鍵值不一定是有序的，因此就有可能造成大量的隨機(jī) IO，如果回表前把主鍵值在內(nèi)存中給它排一下序，那么在回表的時(shí)候就可以用順序 IO 取代原本的隨機(jī) IO。

在沒有MRR的情況下，MySQL會(huì)按照索引順序來訪問行數(shù)據(jù)，而索引順序并不一定與磁盤上的物理存儲(chǔ)順序一致，這就可能產(chǎn)生大量的隨機(jī)磁盤I/O。

當(dāng)啟用MRR后，MySQL會(huì)先按照索引掃描記錄，但并不立即去獲取行數(shù)據(jù)，而是將每個(gè)需要訪問的行位置（例如主鍵）保存到一個(gè)緩沖區(qū)中。

然后，MySQL會(huì)根據(jù)這些行位置，按照物理存儲(chǔ)的順序（通常也就是主鍵順序）去獲取行數(shù)據(jù)。這樣就能避免大量的隨機(jī)I/O，因?yàn)閿?shù)據(jù)現(xiàn)在是按照它們?cè)诖疟P上的物理存儲(chǔ)順序被訪問的。

比如，當(dāng)我執(zhí)行這個(gè)語句時(shí)：

select * from t1 where a>=1 and a<=100;

主鍵索引是一棵B+樹，在這棵樹上，每次只能根據(jù)一個(gè)主鍵id查到一行數(shù)據(jù)。因此，回表肯定是一行行搜索主鍵索引的，基本流程如圖所示。

如果隨著a的值遞增順序查詢的話，id的值就變成隨機(jī)的，那么就會(huì)出現(xiàn)隨機(jī)訪問，性能相對(duì)較差。雖然“按行查”這個(gè)機(jī)制不能改，但是調(diào)整查詢的順序，還是能夠加速的。

因?yàn)榇蠖鄶?shù)的數(shù)據(jù)都是按照主鍵遞增順序插入得到的，所以我們可以認(rèn)為，如果按照主鍵的遞增順序查詢的話，對(duì)磁盤的讀比較接近順序讀，能夠提升讀性能。

這，就是MRR優(yōu)化的設(shè)計(jì)思路。此時(shí)，語句的執(zhí)行流程變成了這樣：

• 根據(jù)索引a，定位到滿足條件的記錄，將id值放入read_rnd_buffer中。
• 將read_rnd_buffer中的id進(jìn)行遞增排序。
• 排序后的id數(shù)組，依次到主鍵id索引中查記錄，并作為結(jié)果返回。

這里，read_rnd_buffer的大小是由read_rnd_buffer_size參數(shù)控制的。

如果步驟1中，read_rnd_buffer放滿了，就會(huì)先執(zhí)行完步驟2和3，然后清空read_rnd_buffer。之后繼續(xù)找索引a的下個(gè)記錄，并繼續(xù)循環(huán)。

下面兩幅圖就是使用了MRR優(yōu)化后的執(zhí)行流程和explain結(jié)果。

從explain結(jié)果中，我們可以看到Extra字段多了「Using MRR」，表示的是用上了MRR優(yōu)化。而且，由于我們?cè)趓ead_rnd_buffer中按照id做了排序，所以最后得到的結(jié)果集也是按照主鍵id遞增順序的，也就是與圖1結(jié)果集中行的順序相反。

MRR能夠提升性能的核心在于，這條查詢語句在索引a上做的是一個(gè)范圍查詢（也就是說，這是一個(gè)多值查詢），可以得到足夠多的主鍵id。這樣通過排序以后，再去主鍵索引查數(shù)據(jù)，才能體現(xiàn)出“順序性”的優(yōu)勢(shì)。

簡單來說：MRR 的核心思想就是通過把「隨機(jī)磁盤讀」，轉(zhuǎn)化為「順序磁盤讀」，從而提高了索引查詢的性能。

順序讀帶來了兩個(gè)好處：

• 磁盤和磁頭不再需要來回做機(jī)械運(yùn)動(dòng)。
• 可以充分利用磁盤預(yù)讀。

所謂的磁盤預(yù)讀，比如說在客戶端請(qǐng)求一頁的數(shù)據(jù)時(shí)，可以把后面幾頁的數(shù)據(jù)也一起返回，放到數(shù)據(jù)緩沖池中，這樣如果下次剛好需要下一頁的數(shù)據(jù)，就不再需要到磁盤讀取。這樣做的理論依據(jù)是計(jì)算機(jī)科學(xué)中著名的局部性原理：當(dāng)一個(gè)數(shù)據(jù)被用到時(shí)，其附近的數(shù)據(jù)也通常會(huì)馬上被使用。

MRR 在本質(zhì)上是一種用「空間換時(shí)間」的做法。

MySQL 不可能給你無限的內(nèi)存來進(jìn)行排序，這塊內(nèi)存的大小就由參數(shù)read_rnd_buffer_size來控制，如果read_rnd_buffer滿了，就會(huì)先把滿了的 rowid 排好序去磁盤讀取，接著清空，然后再往里面繼續(xù)放 rowid，直到 read_rnd_buffer 又達(dá)到 read_rnd_buffe 配置的上限，如此循環(huán)。

MRR如何使用

MRR相關(guān)參數(shù)如下：

//如果你不打開，是一定不會(huì)用到 MRR 的。
set optimizer_switch='mrr=on';
set optimizer_switch ='mrr_cost_based=off';
set read_rnd_buffer_size = 32 * 1024 * 1024;

mrr_cost_based: on/off，則是用來告訴優(yōu)化器，要不要基于使用 MRR 的成本，考慮使用 MRR 是否值得（cost-based choice），來決定具體的 SQL 語句里要不要使用 MRR。

很明顯，對(duì)于只返回一行數(shù)據(jù)的查詢，是沒有必要 MRR 的，而如果你把 mrr_cost_based 設(shè)為 off，那優(yōu)化器就會(huì)通通使用 MRR，這在有些情況下是很 stupid 的，所以建議這個(gè)配置還是設(shè)為 on，畢竟優(yōu)化器在絕大多數(shù)情況下都是正確的。

通過本文我們可以了解到，MySQL的多范圍讀取（MRR）優(yōu)化提供了一個(gè)高效的方式來處理和加速查詢性能。特別是在處理大量數(shù)據(jù)、聯(lián)接操作或者需要處理大量行的復(fù)雜查詢時(shí)，MRR都會(huì)展現(xiàn)出其強(qiáng)大的優(yōu)勢(shì)。

然而，我們也要注意到，不是所有情況下啟用MRR都會(huì)提升性能，一些具體的場景可能會(huì)產(chǎn)生額外的磁盤I/O開銷。因此，理解其工作原理并合適地運(yùn)用在恰當(dāng)?shù)膱鼍埃攀怯行褂眠@個(gè)優(yōu)化策略的關(guān)鍵。