Mysql Nested-Loop Join Algorithms

標籤：

 MySQL在多表之間執行join時，利用一種nested-loop algorithm 或者其變種；（嵌套迴圈）

 Nested-Loop Join Algorithm

     一個簡單的嵌套迴圈串連（NLJ）演算法 從第一個表讀取一行，然後傳遞給Join中的其他表依次讀取，過程一直重複剩餘的表都被Join。

     假設在三個表t1,t2,t3做串連時：

Table   Join Typet1      ranget2      reft3      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    }  }}

因為嵌套迴圈串連每次傳遞一行到內表中，通常要在內表處理好多次。

 

Block Nested-Loop Join Algorithm（塊嵌套迴圈串連演算法）

      Block Nested-loop(BNL)串連演算法利用緩衝外部迴圈讀取的資料行來減少在內表中讀取的次數。e.g.假如外表讀取並緩衝10行資料，緩衝傳遞給以下個內表，內表可以在緩衝中直接比較這10行資料，而不用再重新讀取外表的10行資料，以數量級的方式減少內表讀取次數。

MYSQL 用 join Buffer有一下幾種條件：

     1： join buffer size 系統變數決定了每個join buffer的大小。

     2：join buffer可以用於all, index,range表訪問類型。

     3： 每個join分配一個buffer,所以一個查詢可以用多個join buffer.

     4:  如果第一個表為noconst table,則join buffer不會被分配，即使他的訪問類型為all或者index.

     5:  Join buffer在執行是分配，查詢完成後釋放。

     6:  join Buffer只緩衝感興趣的（需要返回給client）的列，並不是一整行資料都緩衝。

 

比較之前的NLJ演算法（沒有Buffer）：

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    }  }}

     如果t1,t2儲存在串連緩衝的組合大小為S，組合個數為C，那麼t3掃描次數是：

(S * C)/join_buffer_size + 1

    t3的掃描次數會隨著join_buffer_si增加而減少，當串連緩衝增加到了可以包含所有記錄，在大也不能減少掃描次數。

Mysql Nested-Loop Join Algorithms