在數(shù)據(jù)庫的應(yīng)用中，我們經(jīng)常需要對數(shù)據(jù)庫進(jìn)行多表查詢，然而當(dāng)數(shù)據(jù)量非常大時多表查詢會對執(zhí)行效率產(chǎn)生非常大的影響，因此我們在使用JOIN和LEFT JOIN 和 RIGHT JOIN語句時要特別注意；
SQL語句的join原理：
數(shù)據(jù)庫中的join操作，實(shí)際上是對一個表和另一個表的關(guān)聯(lián)，而很多錯誤理解為，先把這兩個表來一個迪卡爾積，然后扔到內(nèi)存，用where和having條件來慢慢篩選，其實(shí)數(shù)據(jù)庫沒那么笨的，那樣會占用大量的內(nèi)存，而且效率不高，比如，我們只需要的一個表的一些行和另一個表的一些行，如果全表都做迪卡爾積，這開銷也太大了，真正的做法是，根據(jù)在每一個表上的條件，遍歷一個表的同時，遍歷其他表，找到滿足最后的條件后，就發(fā)送到客戶端，直到最后的數(shù)據(jù)全部查完，叫做嵌套循環(huán)查詢。
1、LEFT JOIN 和 RIGHT JOIN優(yōu)化
在MySQL中，實(shí)現(xiàn)如 A LEFT JOIN B join_condition 如下:
1、表B依賴賴與表A及所有A依賴的表
2、表A依賴于所有的表，除了LEFT JOIN 的表(B)
3、join_condition決定了怎樣來讀取表B，where條件對B是沒有用的
4、標(biāo)準(zhǔn)的where會和LEFT JOIN聯(lián)合優(yōu)化
5、如果在A中的一行滿足where和having條件，B中沒有，會被填充null
RIGHT JOIN 與LEFT JOIN類似，這個位置是可以互換的
LEFT JOIN 與 正常JOIN之間的轉(zhuǎn)換原則上當(dāng)where條件，對于生成的null行總返回false時，可以直接轉(zhuǎn)化為正常的join
如：
|
1
|
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5; |
將被轉(zhuǎn)換為：
|
1
|
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1; |
注：因?yàn)樵O(shè)置了條件t2.column2 = 5,那么對于所有的生成的t2為null的行都是不成立的
這樣的優(yōu)化將非常快速，因?yàn)檫@樣相當(dāng)于把外連接轉(zhuǎn)換為等值連接，少了很多行的掃描和判斷。
嵌套循環(huán)JOIN算法----Nested-Loop Join
簡單的嵌套循環(huán)算法就是從一個表開始，通過對表的條件找到一行，然后找下一個表的數(shù)據(jù)，找完后，又回到第一個表來尋找滿足條件的行
例如，有三個表t1, t2, t3，他們的join類型為：
|
1
2
3
4
|
Table Join Typet1 ranget2 reft3 ALL |
最終生成的偽代碼為
|
1
2
3
4
5
6
7
8
|
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關(guān)聯(lián)t1，t3為全表掃描
注：先根據(jù)對t1表的條件范圍找到一行，和t2匹配，然后尋找t3的滿足條件的行
塊嵌套循環(huán)JOIN算法 ---- Block Nested-Loop Join
這個算法的應(yīng)用為：由于之前的嵌套算法每讀一個表的一行后，就會讀下表，這樣內(nèi)部的表會被讀很多次，所以，數(shù)據(jù)庫利用了join緩存(join buffer)來存儲中間的結(jié)果，然后讀取內(nèi)部表的時候，找到一行，都和這個緩存中的數(shù)據(jù)比較，以此來提高效率。例如：一次從外表讀10行，然后讀內(nèi)部表時，都和這10行數(shù)據(jù)進(jìn)行比較。
MySQL使用join buffer的條件為：
1、join_buffer_size系統(tǒng)變量決定了每個join使用的buffer大小
2、join類型為index或all時，join buffer才能被使用
3、每一個join都會分配一個join buffer，即一個sql可能使用多個join buffer
4、join buffer 不會分配給第一個非常量表
5、只有需要引用的列會被放到j(luò)oin buffer中，不是整行
最終生成偽代碼為：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
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 } } } |
注：在第二個循環(huán)才把數(shù)據(jù)存在join buffer中，這正好印證了上面的第4點(diǎn)
