MySQL效能最佳化之Block Nested-Loop Join(BNL)

MySQL效能最佳化之Block Nested-Loop Join(BNL)

一 介紹
相信許多開發/DBA在使用MySQL的過程中，對於MySQL處理多表關聯的方式或者說效能一直不太滿意。對於開發提交的含有join的查詢，一般比較抗拒，從而建議將join拆分，避免join可能帶來的效能問題，同時也增加了程式和DB的網路互動。
5.5 版本之前，MySQL本身只支援一種表間關聯方式，就是嵌套迴圈(Nested Loop)。如果關聯表的資料量很大，則join關聯的執行時間會非常長。在5.5以後的版本中，MySQL通過引入BNL演算法來最佳化嵌套執行,本文介紹兩種join演算法 Nested-Loop Join (NLJ) 和Block Nested-Loop Join(BNL) .

二 原理
2.1 Nested Loop Join演算法
  NLJ 演算法:將驅動表/外部表格的結果集作為迴圈基礎資料，然後迴圈從該結果集每次一條擷取資料作為下一個表的過濾條件查詢資料，然後合并結果。如果有多表join，則將前面的表的結果集作為迴圈資料，取到每行再到聯結的下一個表中迴圈匹配，擷取結果集返回給用戶端。
Nested-Loop 的偽演算法如下:

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

    }

  }

 }
  因為普通Nested-Loop一次只將一行傳入內層迴圈, 所以外層迴圈(的結果集)有多少行, 記憶體迴圈便要執行多少次.在內部表的串連上有索引的情況下，其掃描成本為O(Rn),若沒有索引,則掃描成本為O(Rn*Sn)。如果內部表S有很多記錄，則SimpleNested-Loops Join會掃描內部表很多次，執行效率非常差。

2.2 Block Nested-Loop Join演算法
  BNL 演算法:將外層迴圈的行/結果集存入join buffer, 內層迴圈的每一行與整個buffer中的記錄做比較，從而減少內層迴圈的次數.
舉例來說，外層迴圈的結果集是100行，使用NLJ 演算法需要掃描內部表100次，如果使用BNL演算法，先把對Outer Loop表(外部表格)每次讀取的10行記錄放到join buffer,然後在InnerLoop表(內部表)中直接匹配這10行資料，記憶體迴圈就可以一次與這10行進行比較, 這樣只需要比較10次，對內部表的掃描減少了9/10。所以BNL演算法就能夠顯著減少內層迴圈表掃描的次數.
前面描述的query, 如果使用join buffer, 那麼實際join示意如下:

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參與join的列長度只和為s, c為二者組合數, 那麼t3表被掃描的次數為

(S * C)/join_buffer_size + 1
掃描t3的次數隨著join_buffer_size的增大而減少, 直到join buffer能夠容納所有的t1, t2組合,  再增大join buffer size, query 的速度就不會再變快了.

2.3 MySQL使用Join Buffer有以下要點:
  1. join_buffer_size變數決定buffer大小。
  2. 只有在join類型為all, index, range的時候才可以使用join buffer。
  3. 能夠被buffer的每一個join都會分配一個buffer, 也就是說一個query最終可能會使用多個join buffer。
  4. 第一個nonconst table不會分配join buffer, 即便其掃描類型是all或者index。
  5. 在join之前就會分配join buffer, 在query執行完畢即釋放。
  6. join buffer中只會儲存參與join的列, 並非整個資料行。

三  如何使用
  5.6版本及以後，最佳化器管理參數optimizer_switch中中的block_nested_loop參數控制著BNL是否被用於最佳化器。預設條件下是開啟，若果設定為off，最佳化器在選擇 join方式的時候會選擇NLJ演算法。

 四 參考資料
      5.6 版本BNL 支援outer join and semi-join ，並且和其他的特性比如BKA 相關聯，後面會寫文章整理其他的最佳化點。
    《Nested-Loop Join Algorithms》
    《Block Nested-Loop and Batched Key Access Joins》
    《mysql的join buffer》

本文永久更新連結地址：