5.6新特性之NL,BNL,MRR和BKA

NLJ(nested-loop join):
從第一個表每次讀一行資料，傳遞到一個巢狀迴圈（處理join中的下一個表）。
這個處理重複次數跟join中涉及的表相同?
例子：
Table   Join Type
t1      range
t2      ref
t3      ALL
邏輯處理：
for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions,
      send to client
    }
  }
}

BNL（Block Nested-Loop Join）：
通過快取外層迴圈讀的行，來降低內層表的讀取次數。比如： 10行資料讀入到buffer中，
然後buffer被傳遞到內層迴圈，內層表讀出的每一行都要跟這個快取的10行依次做對比，
這樣就降低了內層表資料的讀取次數。
使用條件：
1] join_buffer_size決定了每一個join buffer的大小
2] 只有當join type是 all or index（沒有合適的索引，使用全索引或者全表掃描的場景）,
   range的時候才會使用。5.6中，外連線也可以用buffer了。
3] 每一個需要buffer的join都會申請一個獨立的buffer，也就是說一個查詢可能使用多個join buffer。
4] 第一個非常量表是不會使用join buffer的。
5] join buffer在執行join之前申請，在查詢完成後釋放。
6] join buffer只儲存跟join有關的列，而不是整行

explain 列顯示：Using join buffer (Block Nested Loop)

邏輯處理：
for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions,
          send to client
        }
      }
      empty buffer
    }
  }
}

if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions,
      send to client
    }
  }
}

MRR(Multi-Range Read):
當一個表很大，不能儲存到儲存引擎的快取的時候，使用二級索引做範圍掃描會引起大量磁碟隨機讀。
MRR的存在就是為了優化這些隨機讀。mysql開始只掃描跟行相關的索引和收集key，然後把這些key排序，
最後根據排好序的primary key來從基礎表獲取資料。  MRR的目的是，降低隨機的磁碟IO，替換成相對更
有順序的IO。
MRR的好處：
1、隨機IO轉換成順序IO。
2、批量處理請求

優化場景：
A： MRR可以用來做innodb，myiasm的索引範圍掃描和等值join操作。
1、索引元組累積到一個buffer
2、buffer中的元組根據rowid排序
3、根據排序好的索引元組順序去獲取資料行
4、當不需要回表訪問的時候，MRR就失去意義了（比如覆蓋索引）

當使用MRR的時候 explain出現：Using MRR標誌

儲存引擎使用read_rnd_buffer_size 的值來確定MRR時的buffer大小。

 

BKA(Batched Key Access):
當使用索引訪問第二個join物件的時候，跟BNL類似，BKA使用一個join buffer
來收集第一個操作物件生成的相關列值。BKA構建好key後，批量傳給引擎層做索引
查詢。key是通過MRR介面提交給引擎的，這樣，MRR使得查詢更有效率。
BKA使用join buffer size來確定buffer的大小，buffer越大，訪問右側表就越
順序。
使用BAK的條件：
SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

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