MySQL MRR介紹

  • 2019 年 10 月 4 日
  • 筆記

這個文章的原始出處找不到了。

什麼是MRR？

MRR：multi range read。不好解釋，先來看個例子：

select * from tb where key_column = x

在沒有MRR的情況下，它是這樣得到結果的：

1.  select key_column, pk_column from tb where key_column=x order by key_column   ---> 假設這個結果集是t

2.  for each row in t ;   select non_key_column from tb where pk_column = pk_column_value。(在Oracle里第2步叫回表？)

在有MRR的情況下，它是這樣執行的：

1.  select key_column, pk_column from tb where key_column = x  order by key_column ---> 假設這個結果集是t

2.  將結果集t放在buffer裡面(直到buffer滿了)，然後對結果集t按照pk_column排序      ---> 假設排序好的結果集是t_sort

3.  select non_key_column fromtb where pk_column in (select pk_column from t_sort)

兩者的區別主要是兩點：

1. 沒有MRR的情況下，隨機IO增加，因為從二級索引裡面得到的索引元組是有序，但是他們在主鍵索引裡面卻是無序的，所以每次去主鍵索引裡面得到non_key_column的時候都是隨機IO。（如果索引覆蓋，那也就沒必要利用MRR的特性了，直接從索引裡面得到所有數據）

2. 沒有MRR的情況下，訪問主鍵索引的次數也會增加。沒有MRR的情況下，二級索引裡面得到多少行，那麼就要去訪問多少次主鍵索引(也不能完全這樣說，因為MySQL實現了BNL)，而有了MRR的時候，次數就大約減少為之前次數t/buffer_size。

 所以說MRR主要解決的就是這兩個問題。

此外，MRR還可以將某些範圍查詢，拆分為鍵值對，以此來進行批量的數據查詢。這樣做的好處是可以在拆分過程中，直接過濾一些不符合查詢條件的數據。

如：

官方文檔：https://dev.mysql.com/doc/refman/5.7/en/mrr-optimization.html

> SELECT * FROM t WHERE key_part1 >=1000 AND key_part1 < 2000 AND key_part2 = 1000;

表t有(key_part1,key_part2)的聯合索引，因此索引根據key_part1,key_part2的位置關係進行排序。若沒有MRR，此時查詢類型為Range，SQL優化器會先將key_part1大於1000且小於2000的數據都取出來，即便key_part2不等於1000。取出後再根據key_part2的條件進行過濾。這會導致無用的數據被取出。

如果啟用MRR優化器會使性能有巨大的提升，優化器會先將查詢條件拆分為(1000,1000),(1001,1000),(1002,1000)....(1999,1000) 最後再根據這些拆分出的條件進行數據的查詢。

是否啟用MRR優化，可以通過參數optimizer_switch中的flag來控制。當MRR為on時，表示啟用MRR優化。mrr_cost_based表示是否通過costbased的方式來選擇是否啟用mrr。若設置mrr=on,mrr_cost_based=off，則總是啟用MRR優化。如下：

> SET GLOBAL optimizer_switch='mrr=on,mrr_cost_based=off';

參數read_rnd_buffer_size用來控制鍵值的緩衝區大小。當大於該值時，則執行器對已經快取的數據根據RowID進行排序，並通過RowID來取得行數據，該值默認是256KB

>show VARIABLES like 'read_rnd_buffer_size';  +----------------------+---------+  | Variable_name        |   Value |  |----------------------+---------|  | read_rnd_buffer_size |  262144 |  +----------------------+---------+

Tags:

VirMach 便宜 VPS