MySQL5.6新特性之Multi-Range Read

張衝andy發表於2018-10-01
MySql

一 介紹
    MySQL 5.6版本提供了很多效能優化的特性，其中之一就是 Multi-Range Read 多範圍讀(MRR) , 它的作用針對基於輔助/第二索引的查詢，減少隨機IO，並且將隨機IO轉化為順序IO，提高查詢效率。
二 原理 
  在沒有MRR之前 ,或者沒有開啟MRR特性時，MySQL 針對基於輔助索引的查詢策略是這樣的：

  1. select  non_key_column  from  tb wherekey_column = x ;

MySQL 執行查詢的虛擬碼

  1. 第一步 先根據where條件中的輔助索引獲取輔助索引與主鍵的集合，結果集為rest。

  2.       select key_column ,  pk_column from tb where key_column = x order by key_column 

  3. 第二步 通過第一步獲取的主鍵來獲取對應的值。

  4.       for  each pk_column value  in  rest do :

  5.        select non_key_column from tb where pk_column = val


由於MySQL儲存資料的方式： 輔助索引的儲存順序並非與主鍵的順序一致，從圖中可以看出,根據輔助索引獲取的主鍵來訪問表中的資料會導致隨機的IO . 不同主鍵不在同一個page 裡面時必然導致多次IO 和隨機讀。

在使用MRR優化特性 的情況下，MySQL 針對基於輔助索引的查詢策略是這樣的：

  1. 第一步 先根據where條件中的輔助索引獲取輔助索引與主鍵的集合，結果集為rest

  2.       select key_column ,  pk_column from tb where key_column  =  x order by key_column 

  3. 第二步 將結果集rest放在buffer裡面 ( read_rnd_buffer_size 大小直到buffer滿了 ) ，然後對結果集rest按照pk_column排序，得到結果集是rest_sort

  4. 第三步 利用已經排序過的結果集，訪問表中的資料，此時是順序IO .

  5.        select non_key_column fromtb where pk_column  in   (  rest_sort  )


從圖示MRR原理， MySQL 將根據輔助索引獲取的結果集根據主鍵進行排序，將亂序化為有序，可以用主鍵順序訪問基表，將隨機讀轉化為順序讀，多頁資料記錄可一次性讀入或根據此次的主鍵範圍分次讀入，以減少IO操作 ，提高查詢效率。

三 相關引數
    我們可以通過引數 optimizer_switch 的標記來控制是否使用MRR，當設定mrr=on時，表示啟用MRR優化。mrr_cost_based 表示是否通過 cost base的方式來啟用MRR.如果選擇mrr=on,mrr_cost_based=off,則表示總是開啟MRR優化。
    引數read_rnd_buffer_size 用來控制鍵值緩衝區的大小。
    
四  案例介紹
當開啟MRR時

  1. MySQL  >  explain select  *  from tbl where tbl . key1 between 1000  and  2000 ;

  2. + - - - - + - - - - - - - - - - - - - + - - - - - - - + - - - - - - - + - - - - - - - - - - - - - - - + - - - - - - + - - - - - - - - - + - - - - - - + - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +

  3. |  id  |  select_type  |  table  |   type    |  possible_keys  |  key   |  key_len  |  ref   |  rows  |  Extra                                      |

  4. + - - - - + - - - - - - - - - - - - - + - - - - - - - + - - - - - - - + - - - - - - - - - - - - - - - + - - - - - - + - - - - - - - - - + - - - - - - + - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +

  5. |  1   |  SIMPLE       |  tbl    |   range  |  key1           |  key1  |  5        |  NULL  |  960   |  Using index condition ;   Using MRR            |

  6. + - - - - + - - - - - - - - - - - - - + - - - - - - - + - - - - - - - + - - - - - - - - - - - - - - - + - - - - - - + - - - - - - - - - + - - - - - - + - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +

  7. 1 row  in  set  ( . 03 sec )

五 MRR的使用限
   MRR 適用於以下兩種情況。
   1 range access
   2 ref and eq_ref access, when they are using Batched Key Access

六  參考文章 
 《MariaDB Multi-Range Read Optimization》
  《MySQL Multi-Range Read Optimization》
  《Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5》  


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31383567/viewspace-2215385/，如需轉載，請註明出處，否則將追究法律責任。

相關文章