SQL語句最佳化之JOIN和LEFT JOIN 和 RIGHT JOIN語句的最佳化，sqljoin

SQL語句最佳化之JOIN和LEFT JOIN 和 RIGHT JOIN語句的最佳化，sqljoin

在資料庫的應用中，我們經常需要對資料庫進行多表查詢，然而當資料量非常大時多表查詢會對執行效率產生非常大的影響，因此我們在使用JOIN和LEFT JOIN 和 RIGHT JOIN語句時要特別注意；

SQL語句的join原理：

資料庫中的join操作，實際上是對一個表和另一個表的關聯，而很多錯誤理解為，先把這兩個表來一個迪卡爾積，然後扔到記憶體，用where和having條件來慢慢篩選，其實資料庫沒那麼笨的，那樣會佔用大量的記憶體，而且效率不高，比如，我們只需要的一個表的一些行和另一個表的一些行，如果全表都做迪卡爾積，這開銷也太大了，真正的做法是，根據在每一個表上的條件，遍曆一個表的同時，遍曆其他表，找到滿足最後的條件後，就發送到用戶端，直到最後的資料全部查完，叫做嵌套迴圈查詢。

1、LEFT JOIN 和 RIGHT JOIN最佳化

在MySQL中，實現如 A LEFT JOIN B join_condition 如下:

1、表B依賴賴與表A及所有A依賴的表

2、表A依賴於所有的表，除了LEFT JOIN 的表(B)

3、join_condition決定了怎樣來讀取表B，where條件對B是沒有用的

4、標準的where會和LEFT JOIN聯合最佳化

5、如果在A中的一行滿足where和having條件，B中沒有，會被填充null

RIGHT JOIN 與LEFT JOIN類似，這個位置是可以互換的

LEFT JOIN 與 正常JOIN之間的轉換原則上當where條件，對於產生的null行總返回false時，可以直接轉化為正常的join

如：

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

將被轉換為：

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

註：因為設定了條件t2.column2 =  5,那麼對於所有的產生的t2為null的行都是不成立的

這樣的最佳化將非常快速，因為這樣相當於把外串連轉換為等值串連，少了很多行的掃描和判斷。

嵌套迴圈JOIN演算法----Nested-Loop Join

簡單的嵌套迴圈演算法就是從一個表開始，通過對錶的條件找到一行，然後找下一個表的資料，找完後，又回到第一個表來尋找滿足條件的行

例如，有三個表t1, t2, t3，他們的join類型為：

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

即，t1表通過範圍掃描，t2關聯t1，t3為全表掃描

註：先根據對t1表的條件範圍找到一行，和t2匹配，然後尋找t3的滿足條件的行

塊嵌套迴圈JOIN演算法 ---- Block Nested-Loop Join

這個演算法的應用為：由於之前的嵌套演算法每讀一個表的一行後，就會讀下表，這樣內部的表會被讀很多次，所以，資料庫利用了join緩衝(join buffer)來儲存中間的結果，然後讀取內部表的時候，找到一行，都和這個緩衝中的資料比較，以此來提高效率。例如：一次從外表讀10行，然後讀內部表時，都和這10行資料進行比較。

MySQL使用join buffer的條件為：

1、join_buffer_size系統變數決定了每個join使用的buffer大小

2、join類型為index或all時，join buffer才能被使用

3、每一個join都會分配一個join buffer，即一個sql可能使用多個join buffer

4、join buffer 不會分配給第一個非常量表

5、只有需要引用的列會被放到join 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     這裡將t1和t2使用的列存到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   }  } } 

註：在第二個迴圈才把資料存在join buffer中，這正好印證了上面的第4點