Nested-Loop Join Algorithms

MySQL使用嵌套迴圈演算法來實現多表之間的聯結。 Nested-Loop Join Algorithms

一個簡單的巢狀迴圈聯結（NLJ）演算法，迴圈從第一個表中依次讀取行，取到每行再到聯結的下一個表中迴圈匹配。這個過程會重複多次直到剩餘的表都被聯結了。
假設表t1、t2、t3用下面的聯結類型進行聯結：

Table   Join Typet1      ranget2      reft3      ALL

如果使用的是簡單NLJ演算法，那麼聯結的過程像這樣：

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

因為NLJ演算法是通過外迴圈的行去匹配內迴圈的行，所以內迴圈的表會被掃描多次。 Block Nested-Loop Join Algorithm

一個塊巢狀迴圈聯結（BNL）演算法，將外迴圈的行緩衝起來，讀取緩衝中的行，減少內迴圈的表被掃描的次數。例如，如果10行讀入緩衝區並且緩衝區傳遞給下一個內迴圈，在內迴圈讀到的每行可以和緩衝區的10行做比較。這樣使內迴圈表被掃描的次數減少了一個數量級。
MySQL使用聯結緩衝區時，會遵循下面這些原則：

join_buffer_size系統變數的值決定了每個聯結緩衝區的大小。

聯結類型為ALL、index、range時（換句話說，聯結的過程會掃描索引或資料時），MySQL會使用聯結緩衝區。

緩衝區是分配給每一個能被緩衝的聯結，所以一個查詢可能會使用多個聯結緩衝區。

聯結緩衝區永遠不會分配給第一個表，即使該表的查詢類型為ALL或index。

聯結緩衝區聯結之前分配，查詢完成之後釋放。

使用到的列才會放到聯結緩衝區中，並不是所有的列。

上面的例子使用的是NLJ演算法（沒有使用緩衝），使用緩衝的聯結方式像下面這樣：

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

對上面的過程解釋如下：
1. 將t1、t2的聯結結果放到緩衝區，直到緩衝區滿為止；
2. 遍曆t3，內部再迴圈緩衝區，並找到匹配的行，發送到用戶端；
3. 清空緩衝區；
4. 重複上面步驟，直至緩衝區不滿；
5. 處理緩衝區中剩餘的資料，重複步驟2。

設S是每次儲存t1、t2組合的大小，C是組合的數量，則t3被掃描的次數為：

(S * C)/join_buffer_size + 1

由此可見，隨著join_buffer_size的增大，t3被掃描的次數會較少，如果join_buffer_size足夠大，大到可以容納所有t1和t2聯結產生的資料，t3隻會被掃描1次。

英文地址：http://dev.mysql.com/doc/refman/5.5/en/nested-loop-joins.html
本文來自：高爽|Coder，原文地址：http://blog.csdn.net/ghsau/article/details/43762027，轉載請註明。