在數(shù)據(jù)庫(kù)的應(yīng)用中，我們經(jīng)常需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行多表查詢(xún)，然而當(dāng)數(shù)據(jù)量非常大時(shí)多表查詢(xún)會(huì)對(duì)執(zhí)行效率產(chǎn)生非常大的影響，因此我們?cè)谑褂肑OIN和LEFT JOIN 和 RIGHT JOIN語(yǔ)句時(shí)要特別注意；

SQL語(yǔ)句的join原理：

數(shù)據(jù)庫(kù)中的join操作，實(shí)際上是對(duì)一個(gè)表和另一個(gè)表的關(guān)聯(lián)，而很多錯(cuò)誤理解為，先把這兩個(gè)表來(lái)一個(gè)迪卡爾積，然后扔到內(nèi)存，用where和having條件來(lái)慢慢篩選，其實(shí)數(shù)據(jù)庫(kù)沒(méi)那么笨的，那樣會(huì)占用大量的內(nèi)存，而且效率不高，比如，我們只需要的一個(gè)表的一些行和另一個(gè)表的一些行，如果全表都做迪卡爾積，這開(kāi)銷(xiāo)也太大了，真正的做法是，根據(jù)在每一個(gè)表上的條件，遍歷一個(gè)表的同時(shí)，遍歷其他表，找到滿(mǎn)足最后的條件后，就發(fā)送到客戶(hù)端，直到最后的數(shù)據(jù)全部查完，叫做嵌套循環(huán)查詢(xún)。

1、LEFT JOIN 和 RIGHT JOIN優(yōu)化

在MySQL中，實(shí)現(xiàn)如 A LEFT JOIN B join_condition 如下:

1、表B依賴(lài)賴(lài)與表A及所有A依賴(lài)的表

2、表A依賴(lài)于所有的表，除了LEFT JOIN 的表(B)

3、join_condition決定了怎樣來(lái)讀取表B，where條件對(duì)B是沒(méi)有用的

4、標(biāo)準(zhǔn)的where會(huì)和LEFT JOIN聯(lián)合優(yōu)化

5、如果在A中的一行滿(mǎn)足where和having條件，B中沒(méi)有，會(huì)被填充null

RIGHT JOIN 與LEFT JOIN類(lèi)似，這個(gè)位置是可以互換的

LEFT JOIN 與 正常JOIN之間的轉(zhuǎn)換原則上當(dāng)where條件，對(duì)于生成的null行總返回false時(shí)，可以直接轉(zhuǎn)化為正常的join

如：

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

將被轉(zhuǎn)換為：

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

注：因?yàn)樵O(shè)置了條件t2.column2 =  5,那么對(duì)于所有的生成的t2為null的行都是不成立的

這樣的優(yōu)化將非常快速，因?yàn)檫@樣相當(dāng)于把外連接轉(zhuǎn)換為等值連接，少了很多行的掃描和判斷。

嵌套循環(huán)JOIN算法----Nested-Loop Join

簡(jiǎn)單的嵌套循環(huán)算法就是從一個(gè)表開(kāi)始，通過(guò)對(duì)表的條件找到一行，然后找下一個(gè)表的數(shù)據(jù)，找完后，又回到第一個(gè)表來(lái)尋找滿(mǎn)足條件的行

例如，有三個(gè)表t1, t2, t3，他們的join類(lèi)型為：

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

即，t1表通過(guò)范圍掃描，t2關(guān)聯(lián)t1，t3為全表掃描

注：先根據(jù)對(duì)t1表的條件范圍找到一行，和t2匹配，然后尋找t3的滿(mǎn)足條件的行

塊嵌套循環(huán)JOIN算法 ---- Block Nested-Loop Join

這個(gè)算法的應(yīng)用為：由于之前的嵌套算法每讀一個(gè)表的一行后，就會(huì)讀下表，這樣內(nèi)部的表會(huì)被讀很多次，所以，數(shù)據(jù)庫(kù)利用了join緩存(join buffer)來(lái)存儲(chǔ)中間的結(jié)果，然后讀取內(nèi)部表的時(shí)候，找到一行，都和這個(gè)緩存中的數(shù)據(jù)比較，以此來(lái)提高效率。例如：一次從外表讀10行，然后讀內(nèi)部表時(shí)，都和這10行數(shù)據(jù)進(jìn)行比較。

MySQL使用join buffer的條件為：

1、join_buffer_size系統(tǒng)變量決定了每個(gè)join使用的buffer大小

2、join類(lèi)型為index或all時(shí)，join buffer才能被使用

3、每一個(gè)join都會(huì)分配一個(gè)join buffer，即一個(gè)sql可能使用多個(gè)join buffer

4、join buffer 不會(huì)分配給第一個(gè)非常量表

5、只有需要引用的列會(huì)被放到j(luò)oin 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使用的列存到j(luò)oin 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 
  } 
 } 
} 

注：在第二個(gè)循環(huán)才把數(shù)據(jù)存在join buffer中，這正好印證了上面的第4點(diǎn)