MySQL 的 MRR 到底是什么?

本文公眾號(hào)來(lái)源：柳樹(shù)的絮叨叨作者：靠發(fā)型吃飯的柳樹(shù)本文已收錄至我的GitHub

MRR，全稱「Multi-Range Read Optimization」。

簡(jiǎn)單說(shuō)：MRR 通過(guò)把「隨機(jī)磁盤讀」，轉(zhuǎn)化為「順序磁盤讀」，從而提高了索引查詢的性能。

至于：

• 為什么要把隨機(jī)讀轉(zhuǎn)化為順序讀？

• 怎么轉(zhuǎn)化的？

• 為什么順序讀就能提升讀取性能？

咱們開(kāi)始吧。

磁盤：苦逼的底層勞動(dòng)人民

執(zhí)行一個(gè)范圍查詢：

mysql > explain select * from stu where age between 10 and 20;+----+-------------+-------+-------+------+---------+------+------+-----------------------+| id | select_type | table | type  | key  | key_len | ref  | rows | Extra                 |+----+-------------+-------+-------+----------------+------+------+-----------------------+|  1 | SIMPLE      |  stu  | range | age  | 5       | NULL |  960 | Using index condition |+----+-------------+-------+-------+----------------+------+------+-----------------------+

當(dāng)這個(gè) sql 被執(zhí)行時(shí)，MySQL 會(huì)按照下圖的方式，去磁盤讀取數(shù)據(jù)（假設(shè)數(shù)據(jù)不在數(shù)據(jù)緩沖池里）：

圖中紅色線就是整個(gè)的查詢過(guò)程，藍(lán)色線則是磁盤的運(yùn)動(dòng)路線。

這張圖是按照 Myisam 的索引結(jié)構(gòu)畫的，不過(guò)對(duì)于 Innodb 也同樣適用。

對(duì)于 Myisam，左邊就是字段 age 的二級(jí)索引，右邊是存儲(chǔ)完整行數(shù)據(jù)的地方。

先到左邊的二級(jí)索引找，找到第一條符合條件的記錄（實(shí)際上每個(gè)節(jié)點(diǎn)是一個(gè)頁(yè)，一個(gè)頁(yè)可以有很多條記錄，這里我們假設(shè)每個(gè)頁(yè)只有一條），接著到右邊去讀取這條數(shù)據(jù)的完整記錄。

讀取完后，回到左邊，繼續(xù)找下一條符合條件的記錄，找到后，再到右邊讀取，這時(shí)發(fā)現(xiàn)這條數(shù)據(jù)跟上一條數(shù)據(jù)，在物理存儲(chǔ)位置上，離的賊遠(yuǎn)！

咋辦，沒(méi)辦法，只能讓磁盤和磁頭一起做機(jī)械運(yùn)動(dòng)，去給你讀取這條數(shù)據(jù)。

第三條、第四條，都是一樣，每次讀取數(shù)據(jù)，磁盤和磁頭都得跑好遠(yuǎn)一段路。

磁盤的簡(jiǎn)化結(jié)構(gòu)可以看成這樣：

可以想象一下，為了執(zhí)行你這條 sql 語(yǔ)句，磁盤要不停的旋轉(zhuǎn)，磁頭要不停的移動(dòng)，這些機(jī)械運(yùn)動(dòng)，都是很費(fèi)時(shí)的。

10,000 RPM（Revolutions Per Minute，即轉(zhuǎn)每分） 的機(jī)械硬盤，每秒大概可以執(zhí)行 167 次磁盤讀取，所以在極端情況下，MySQL 每秒只能給你返回 167 條數(shù)據(jù)，這還不算上 CPU 排隊(duì)時(shí)間。

上面講的都是機(jī)械硬盤，SSD 的土豪，請(qǐng)隨意 - -

對(duì)于 Innodb，也是一樣的。 Innodb 是聚簇索引（cluster index），所以只需要把右邊也換成一顆葉子節(jié)點(diǎn)帶有完整數(shù)據(jù)的 B+ tree 就可以了。

順序讀：一場(chǎng)狂風(fēng)暴雨般的革命

到這里你知道了磁盤隨機(jī)訪問(wèn)是多么奢侈的事了，所以，很明顯，要把隨機(jī)訪問(wèn)轉(zhuǎn)化成順序訪問(wèn)：

mysql > set optimizer_switch='mrr=on';Query OK, 0 rows affected (0.06 sec)
mysql > explain select * from stu where age between 10 and 20;+----+-------------+-------+-------+------+---------+------+------+----------------+| id | select_type | table | type  | key  | key_len | ref  | rows | Extra          |+----+-------------+-------+-------+------+---------+------+------+----------------+|  1 | SIMPLE      | tbl   | range | age  |    5    | NULL |  960 | ...; Using MRR |+----+-------------+-------+-------+------+---------+------+------+----------------+

我們開(kāi)啟了 MRR，重新執(zhí)行 sql 語(yǔ)句，發(fā)現(xiàn) Extra 里多了一個(gè)「Using MRR」。

這下 MySQL 的查詢過(guò)程會(huì)變成這樣：

對(duì)于 Myisam，在去磁盤獲取完整數(shù)據(jù)之前，會(huì)先按照 rowid 排好序，再去順序的讀取磁盤。

對(duì)于 Innodb，則會(huì)按照聚簇索引鍵值排好序，再順序的讀取聚簇索引。

順序讀帶來(lái)了幾個(gè)好處：

1、磁盤和磁頭不再需要來(lái)回做機(jī)械運(yùn)動(dòng)；

2、可以充分利用磁盤預(yù)讀

比如在客戶端請(qǐng)求一頁(yè)的數(shù)據(jù)時(shí)，可以把后面幾頁(yè)的數(shù)據(jù)也一起返回，放到數(shù)據(jù)緩沖池中，這樣如果下次剛好需要下一頁(yè)的數(shù)據(jù)，就不再需要到磁盤讀取。這樣做的理論依據(jù)是計(jì)算機(jī)科學(xué)中著名的局部性原理：

當(dāng)一個(gè)數(shù)據(jù)被用到時(shí)，其附近的數(shù)據(jù)也通常會(huì)馬上被使用。

3、在一次查詢中，每一頁(yè)的數(shù)據(jù)只會(huì)從磁盤讀取一次

MySQL 從磁盤讀取頁(yè)的數(shù)據(jù)后，會(huì)把數(shù)據(jù)放到數(shù)據(jù)緩沖池，下次如果還用到這個(gè)頁(yè)，就不需要去磁盤讀取，直接從內(nèi)存讀。

但是如果不排序，可能你在讀取了第 1 頁(yè)的數(shù)據(jù)后，會(huì)去讀取第2、3、4頁(yè)數(shù)據(jù)，接著你又要去讀取第 1 頁(yè)的數(shù)據(jù)，這時(shí)你發(fā)現(xiàn)第 1 頁(yè)的數(shù)據(jù)，已經(jīng)從緩存中被剔除了，于是又得再去磁盤讀取第 1 頁(yè)的數(shù)據(jù)。

而轉(zhuǎn)化為順序讀后，你會(huì)連續(xù)的使用第 1 頁(yè)的數(shù)據(jù)，這時(shí)候按照 MySQL 的緩存剔除機(jī)制，這一頁(yè)的緩存是不會(huì)失效的，直到你利用完這一頁(yè)的數(shù)據(jù)，由于是順序讀，在這次查詢的余下過(guò)程中，你確信不會(huì)再用到這一頁(yè)的數(shù)據(jù)，可以和這一頁(yè)數(shù)據(jù)說(shuō)告辭了。

順序讀就是通過(guò)這三個(gè)方面，最大的優(yōu)化了索引的讀取。

別忘了，索引本身就是為了減少磁盤 IO，加快查詢，而 MRR，則是把索引減少磁盤 IO 的作用，進(jìn)一步放大。

一些關(guān)于這場(chǎng)革命的配置

和 MRR 相關(guān)的配置有兩個(gè)：

• mrr: on/off

• mrr_cost_based: on/off

第一個(gè)就是上面演示時(shí)用到的，用來(lái)打開(kāi) MRR 的開(kāi)關(guān)：

mysql > set optimizer_switch='mrr=on';

如果你不打開(kāi)，是一定不會(huì)用到 MRR 的。

另一個(gè)，則是用來(lái)告訴優(yōu)化器，要不要基于使用 MRR 的成本，考慮使用 MRR 是否值得（cost-based choice），來(lái)決定具體的 sql 語(yǔ)句里要不要使用 MRR。

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

另外還有一個(gè)配置?read_rnd_buffer_size?，是用來(lái)設(shè)置用于給 rowid 排序的內(nèi)存的大小。

顯然，MRR 在本質(zhì)上是一種用空間換時(shí)間的算法。MySQL 不可能給你無(wú)限的內(nèi)存來(lái)進(jìn)行排序，如果 read_rnd_buffer 滿了，就會(huì)先把滿了的 rowid 排好序去磁盤讀取，接著清空，然后再往里面繼續(xù)放 rowid，直到 read_rnd_buffer 又達(dá)到 read_rnd_buffe 配置的上限，如此循環(huán)。

尾聲

你也看出來(lái)了，MRR 跟索引有很大的關(guān)系。

索引是 MySQL 對(duì)查詢做的一個(gè)優(yōu)化，把原本雜亂無(wú)章的數(shù)據(jù)，用有序的結(jié)構(gòu)組織起來(lái)，讓全表掃描變成有章可循的查詢。

而我們講的 MRR，則是 MySQL 對(duì)基于索引的查詢做的一個(gè)的優(yōu)化，可以說(shuō)是對(duì)優(yōu)化的優(yōu)化了。

要優(yōu)化 MySQL 的查詢，就得先知道 MySQL 的查詢過(guò)程；而要優(yōu)化索引的查詢，則要知道 MySQL 索引的原理。

就像之前在「如何學(xué)習(xí) MySQL」里說(shuō)的，要優(yōu)化一項(xiàng)技術(shù)、學(xué)會(huì)調(diào)優(yōu)，首先得先弄懂它的原理，這兩者是不同的 Level。

戳：百萬(wàn)字長(zhǎng)文帶你學(xué)習(xí)「Java」

掃碼或者微信搜Java3y?免費(fèi)領(lǐng)取原創(chuàng)思維導(dǎo)圖、精美PDF。在公眾號(hào)回復(fù)「888」領(lǐng)取，PDF內(nèi)容純手打有任何不懂歡迎來(lái)問(wèn)我。