共計 2394 個字符，預(yù)計需要花費 6 分鐘才能閱讀完成。
行業(yè)資訊
數(shù)據(jù)庫
SQL 語句優(yōu)化之 JOIN 和 LEFT JOIN 和 RIGHT JOIN 語句的示例分析
丸趣 TV 小編給大家分享一下 SQL 語句優(yōu)化之 JOIN 和 LEFT JOIN 和 RIGHT JOIN 語句的示例分析，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！
在數(shù)據(jù)庫的應(yīng)用中，我們經(jīng)常需要對數(shù)據(jù)庫進行多表查詢，然而當數(shù)據(jù)量非常大時多表查詢會對執(zhí)行效率產(chǎn)生非常大的影響，因此我們在使用 JOIN 和 LEFT JOIN 和 RIGHT JOIN 語句時要特別注意；
SQL 語句的 join 原理：
數(shù)據(jù)庫中的 join 操作，實際上是對一個表和另一個表的關(guān)聯(lián)，而很多錯誤理解為，先把這兩個表來一個迪卡爾積，然后扔到內(nèi)存，用 where 和 having 條件來慢慢篩選，其實數(shù)據(jù)庫沒那么笨的，那樣會占用大量的內(nèi)存，而且效率不高，比如，我們只需要的一個表的一些行和另一個表的一些行，如果全表都做迪卡爾積，這開銷也太大了，真正的做法是，根據(jù)在每一個表上的條件，遍歷一個表的同時，遍歷其他表，找到滿足最后的條件后，就發(fā)送到客戶端，直到最后的數(shù)據(jù)全部查完，叫做嵌套循環(huán)查詢。
1、LEFT JOIN 和 RIGHT JOIN 優(yōu)化
在 MySQL 中，實現(xiàn)如 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 聯(lián)合優(yōu)化
5、如果在 A 中的一行滿足 where 和 having 條件，B 中沒有，會被填充 null
RIGHT JOIN 與 LEFT JOIN 類似，這個位置是可以互換的
LEFT JOIN 與 正常 JOIN 之間的轉(zhuǎn)換原則上當 where 條件，對于生成的 null 行總返回 false 時，可以直接轉(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;注：因為設(shè)置了條件 t2.column2 = 5, 那么對于所有的生成的 t2 為 null 的行都是不成立的
這樣的優(yōu)化將非常快速，因為這樣相當于把外連接轉(zhuǎn)換為等值連接，少了很多行的掃描和判斷。
嵌套循環(huán) JOIN 算法 —-Nested-Loop Join
簡單的嵌套循環(huán)算法就是從一個表開始，通過對表的條件找到一行，然后找下一個表的數(shù)據(jù)，找完后，又回到第一個表來尋找滿足條件的行
例如，有三個表 t1, t2, t3，他們的 join 類型為：
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 表通過范圍掃描，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ù)進行比較。
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、只有需要引用的列會被放到 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
}
}
}注：在第二個循環(huán)才把數(shù)據(jù)存在 join buffer 中，這正好印證了上面的第 4 點
看完了這篇文章，相信你對“SQL 語句優(yōu)化之 JOIN 和 LEFT JOIN 和 RIGHT JOIN 語句的示例分析”有了一定的了解，如果想了解更多相關(guān)知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝各位的閱讀！
