03.01 MySQL 的 MRR 到底是什麼?

MySQL 裡有個東西叫 MRR,全稱「Multi-Range Read Optimization」。

簡單說,MRR 通過:

  • 把「隨機磁盤讀」,轉化為「順序磁盤讀」

從而:

  • 提高了磁盤讀取的性能

至於:

  • 為什麼要把隨機讀轉化為順序讀?
  • 怎麼轉化的?
  • 為什麼順序讀就能提升讀取性能?

咱們開始吧。

磁盤:苦逼的底層勞動人民

執行一個範圍查詢:

<code>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 |
+----+-------------+-------+-------+----------------+------+------+-----------------------+/<code>

當這個 sql 被執行時,MySQL 會按照下圖的方式,去磁盤讀取數據(假設數據不在數據緩衝池裡):

MySQL 的 MRR 到底是什麼?

這張圖是按照 Myisam 的索引結構畫的,不過對於 Innodb 也同樣適用。

對於 Myisam,左邊就是字段 age 的二級索引,右邊是存儲完整行數據的地方。

先到左邊的二級索引找,找到第一條符合條件的記錄(實際上每個節點是一個頁,一個頁可以有很多條記錄,這裡我們假設每個頁只有一條),接著到右邊去讀取這條數據的完整記錄。

讀取完後,回到左邊,繼續找下一條符合條件的記錄,找到後,再到右邊讀取,這時發現這條數據跟上一條數據,在物理存儲位置上,離的賊遠!

咋辦,沒辦法,只能讓磁盤和磁頭一起做機械運動,去給你讀取這條數據。

第三條、第四條,都是一樣,每次讀取數據,磁盤和磁頭都得跑好遠一段路。

圖中紅色線就是整個的查詢過程,藍色線則是磁盤的運動路線。

磁盤的簡化結構可以看成這樣:

MySQL 的 MRR 到底是什麼?

MySQL 的 MRR 到底是什麼?

可以想象一下,為了執行你這條 sql 語句,磁盤要不停的旋轉,磁頭要不停的移動,這些機械運動,都是很費時的。

10,000 RPM 的機械硬盤,每秒大概可以執行 167 次磁盤讀取,所以在極端情況下,MySQL 每秒只能給你返回 167 條數據,這還不算上 CPU 排隊時間。

上面講的都是機械硬盤,SSD 的土豪,請隨意 - -

對於 Innodb,也是一樣的。 Innodb 是聚簇索引(cluster index),所以只需要把右邊也換成一顆葉子節點帶有完整數據的 B+ tree 就可以了。

順序讀:一場狂風暴雨般的革命

到這裡你知道了磁盤隨機訪問是多麼奢侈的事了,所以,很明顯,要把隨機訪問轉化成順序訪問:

<code>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 |
+----+-------------+-------+-------+------+---------+------+------+----------------+/<code>

我們開啟了 MRR,重新執行 sql 語句,發現 Extra 裡多了一個「Using MRR」。

這下 MySQL 的查詢過程會變成這樣:

MySQL 的 MRR 到底是什麼?

在去磁盤獲取完整數據之前,會先按照 rowid 排好序,再去順序的讀取磁盤。

順序讀帶來了幾個好處:

1、磁盤和磁頭不再需要來回做機械運動;

2、可以充分利用磁盤預讀

比如在客戶端請求一頁的數據時,可以把後面幾頁的數據也一起返回,放到數據緩衝池中,這樣如果下次剛好需要下一頁的數據,就不再需要到磁盤讀取。這樣做的理論依據是計算機科學中著名的局部性原理:

當一個數據被用到時,其附近的數據也通常會馬上被使用。

3、在一次查詢中,每一頁的數據只會從磁盤讀取一次

MySQL 從磁盤讀取頁的數據後,會把數據放到數據緩衝池,下次如果還用到這個頁,就不需要去磁盤讀取,直接從內存讀。

但是如果不排序,可能你在讀取了第 1 頁的數據後,會去讀取第2、3、4頁數據,接著你又要去讀取第 1 頁的數據,這時你發現第 1 頁的數據,已經從緩存中被剔除了,於是又得再去磁盤讀取第 1 頁的數據。

而轉化為順序讀後,你會連續的使用第 1 頁的數據,這時候按照 MySQL 的緩存剔除機制,這一頁的緩存是不會失效的,直到你利用完這一頁的數據,由於是順序讀,在這次查詢的餘下過程中,你確信不會再用到這一頁的數據,可以和這一頁數據說告辭了。

順序讀就是通過這三個方面,最大的優化了索引的讀取。

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

尾聲

和 MRR 相關的配置 有兩個:

  • mrr: onoff
  • mrr_cost_based: onoff

第一個就是上面演示時用到的,用來打開 MRR 的開關:

<code>mysql > set optimizer_switch='mrr=on';/<code>

如果你不打開,是一定不會用到 MRR 的。

另一個,則是用來告訴優化器,要不要基於使用 MRR 的成本,考慮使用 MRR 是否值得(cost-based choice),來決定具體的 sql 語句裡要不要使用 MRR。

很明顯,對於只返回一行數據的查詢,是沒有必要 MRR 的,而如果你把 mrr_cost_based 設為 off,那優化器就會通通使用 MRR,這在有些情況下是很 stupid 的,所以建議這個配置還是設為 on,畢竟優化器在絕大多數情況下都是正確的。

另外還有一個配置 read_rnd_buffer_size ,是用來設置用於給 rowid 排序的內存的大小,顯然, MRR 在本質上是一種用空間換時間的算法 ,MySQL 不可能給你無限的內存來進行排序,如果 read_rnd_buffer 滿了,就會先把滿了的 rowid 排好序去磁盤讀取,接著清空,然後再往裡面繼續放 rowid,直到 read_rnd_buffer 又達到 read_rnd_buffe 配置的上限,如此循環。


分享到:


相關文章: