一、Join查詢原理

查詢原理：MySQL內(nèi)部采用了一種叫做 nested loop join（嵌套循環(huán)連接）的算法。

Nested Loop Join 實際上就是通過驅(qū)動表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，然后一條一條的通過該結(jié)果集中的數(shù)據(jù)作為過濾條件到下一個表中查詢數(shù)據(jù)，然后合并結(jié)果。

如果還有第三個參與 Join，則再通過前兩個表的 Join 結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，再一次通過循環(huán)查詢條件到第三個表中查詢數(shù)據(jù)，如此往復(fù)，基本上MySQL采用的是最容易理解的算法來實現(xiàn)join。

所以驅(qū)動表的選擇非常重要，驅(qū)動表的數(shù)據(jù)小可以顯著降低掃描的行數(shù)。

一般情況下參與聯(lián)合查詢的兩張表都會一大一小，如果是join，在沒有其他過濾條件的情況下MySQL會自動選擇小表作為驅(qū)動表。

簡單來說，驅(qū)動表就是主表，left join 中的左表就是驅(qū)動表，right join 中的右表是驅(qū)動表。

二.Nested-Loop Join

在Mysql中，使用Nested-Loop Join的算法思想去優(yōu)化join，Nested-Loop Join翻譯成中文則是“嵌套循環(huán)連接”。

舉個例子：

select * from t1 inner join t2 on t1.id=t2.tid

（1）t1稱為外層表，也可稱為驅(qū)動表。

（2）t2稱為內(nèi)層表，也可稱為被驅(qū)動表。

//偽代碼表示：
List<Row> result = new ArrayList<>();
for(Row r1 in List<Row> t1){
	for(Row r2 in List<Row> t2){
		if(r1.id = r2.tid){
			result.add(r1.join(r2));
		}
	}
}

mysql只支持一種join算法：Nested-Loop Join（嵌套循環(huán)連接），但Nested-Loop Join有三種變種：

• Simple Nested-Loop Join：SNLJ，簡單嵌套循環(huán)連接
• Index Nested-Loop Join：INLJ，索引嵌套循環(huán)連接
• Block Nested-Loop Join：BNLJ，緩存塊嵌套循環(huán)連接

在選擇Join算法時，會有優(yōu)先級，理論上會優(yōu)先判斷能否使用INLJ、BNLJ：

Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join

三. Simple Nested-Loop Join：

如下圖，r為驅(qū)動表，s為匹配表，可以看到從r中分別取出r1、r2、…、rn去匹配s表的左右列，然后再合并數(shù)據(jù)，對s表進行了rn次訪問，對數(shù)據(jù)庫開銷大。

如果table1有1萬條數(shù)據(jù)，table2有1萬條數(shù)據(jù)，那么數(shù)據(jù)比較的次數(shù)=1萬 * 1萬 =1億次，這種查詢效率會非常慢。

所以Mysql繼續(xù)優(yōu)化，然后衍生出Index Nested-LoopJoin、Block Nested-Loop Join兩種NLJ算法。

在執(zhí)行join查詢時mysql會根據(jù)情況選擇兩種之一進行join查詢。

四.Index Nested-LoopJoin（減少內(nèi)層表數(shù)據(jù)的匹配次數(shù)）

• 索引嵌套循環(huán)連接是基于索引進行連接的算法，索引是基于內(nèi)層表的，通過外層表匹配條件直接與內(nèi)層表索引進行匹配，避免和內(nèi)層表的每條記錄進行比較， 從而利用索引的查詢減少了對內(nèi)層表的匹配次數(shù)，優(yōu)勢極大的提升了 join的性能：

原來的匹配次數(shù) = 外層表行數(shù) * 內(nèi)層表行數(shù) 優(yōu)化后的匹配次數(shù)= 外層表的行數(shù) * 內(nèi)層表索引的高度

• 使用場景：只有內(nèi)層表join的列有索引時，才能用到Index Nested-LoopJoin進行連接。
• 由于用到索引，如果索引是輔助索引而且返回的數(shù)據(jù)還包括內(nèi)層表的其他數(shù)據(jù)，則會回內(nèi)層表查詢數(shù)據(jù)，多了一些IO操作。

這個要求非驅(qū)動表（匹配表s）上有索引，可以通過索引來減少比較，加速查詢。

在查詢時，驅(qū)動表（r）會根據(jù)關(guān)聯(lián)字段的索引進行查找，當(dāng)在索引上找到符合的值，再回表進行查詢，也就是只有當(dāng)匹配到索引以后才會進行回表查詢。

如果非驅(qū)動表（s）的關(guān)聯(lián)健是主鍵的話，性能會非常高，如果不是主鍵，要進行多次回表查詢，先關(guān)聯(lián)索引，然后根據(jù)二級索引的主鍵ID進行回表操作，性能上比索引是主鍵要慢。

五.Block Nested-Loop Join（減少內(nèi)層表數(shù)據(jù)的循環(huán)次數(shù)）

1. 緩存塊嵌套循環(huán)連接通過一次性緩存多條數(shù)據(jù)，把參與查詢的列緩存到Join Buffer 里，然后拿join buffer里的數(shù)據(jù)批量與內(nèi)層表的數(shù)據(jù)進行匹配，從而減少了內(nèi)層循環(huán)的次數(shù)（遍歷一次內(nèi)層表就可以批量匹配一次Join Buffer里面的外層表數(shù)據(jù)）。
2. 當(dāng)不使用Index Nested-Loop Join的時候(內(nèi)層表查詢不適用索引)，默認(rèn)使用Block Nested-Loop Join。

什么是Join Buffer？

（1）Join Buffer會緩存所有參與查詢的列而不是只有Join的列。

（2）可以通過調(diào)整join_buffer_size緩存大小

（3）join_buffer_size的默認(rèn)值是256K，join_buffer_size的最大值在MySQL 5.1.22版本前是4G-1，而之后的版本才能在64位操作系統(tǒng)下申請大于4G的Join Buffer空間。

（4）使用Block Nested-Loop Join算法需要開啟優(yōu)化器管理配置的optimizer_switch的設(shè)置block_nested_loop為on，默認(rèn)為開啟。

六.如何優(yōu)化Join速度

用小結(jié)果集驅(qū)動大結(jié)果集，減少外層循環(huán)的數(shù)據(jù)量：

如果小結(jié)果集和大結(jié)果集連接的列都是索引列，mysql在內(nèi)連接時也會選擇用小結(jié)果集驅(qū)動大結(jié)果集，因為索引查詢的成本是比較固定的，這時候外層的循環(huán)越少，join的速度便越快。

為匹配的條件增加索引：爭取使用INLJ，減少內(nèi)層表的循環(huán)次數(shù)

增大join buffer size的大?。寒?dāng)使用BNLJ時，一次緩存的數(shù)據(jù)越多，那么外層表循環(huán)的次數(shù)就越少

減少不必要的字段查詢：

（1）當(dāng)用到BNLJ時，字段越少，join buffer 所緩存的數(shù)據(jù)就越多，外層表的循環(huán)次數(shù)就越少；

（2）當(dāng)用到INLJ時，如果可以不回表查詢，即利用到覆蓋索引，則可能可以提示速度。

實例：

假設(shè)兩張表a 和 b

其中b的關(guān)聯(lián)有comments_id，所以有索引。

1.join

使用的是Index Nested-Loop Join，先對驅(qū)動表a的主鍵篩選，得到一條，然后對非驅(qū)動表b的索引進行seek匹配，預(yù)計得到一條數(shù)據(jù)。

下面這種情況沒用到索引：

使用Block Nested-Loop Join，如果b表數(shù)據(jù)少，作為驅(qū)動表，將b的需要的數(shù)據(jù)緩存到j(luò)oin buffer中，批量對a表掃描

2.left join：

這里用到了索引，所以會采用Index Nested-Loop Join，因為沒有篩選條件，會選擇一張表作為驅(qū)動表去進行join，去關(guān)聯(lián)非驅(qū)動表的索引。

如果加了條件

就會從驅(qū)動表篩選出一條來進行對非驅(qū)動表的匹配。

left join：會保全左表數(shù)據(jù)，如果右表沒相關(guān)數(shù)據(jù)，會顯示null

light join：會保全右表數(shù)據(jù)，如果左表沒相關(guān)數(shù)據(jù)，會顯示null

inner join：部分主從表，結(jié)果會取兩個表針對on條件相匹配的最小集

使用原則

盡量使用inner join,避免left join 和NULL。

on和where的使用區(qū)別

首先要明確一點，where中的條件拿到on里去出來的結(jié)果是不同的，如下：

ON 條件（“A LEFT JOIN B ON 條件表達式”中的ON）用來決定如何從 B 表中檢索數(shù)據(jù)行。如果 B 表中沒有任何一行數(shù)據(jù)匹配 ON 的條件,將會額外生成一行所有列為 NULL 的數(shù)據(jù),在匹配階段 WHERE 子句的條件都不會被使用。僅在匹配階段完成以后，WHERE 子句條件才會被使用。它將從匹配階段產(chǎn)生的數(shù)據(jù)中檢索過濾。

所以我們要注意：在使用Left (right) join的時候，一定要在先給出盡可能多的匹配滿足條件，減少Where的執(zhí)行。如：

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name
left join D on D.id = C.id
where C.status>1 and D.status=1;

Great:

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name and C.status>1
left join D on D.id = C.id and D.status=1

總結(jié)

連表操作時：先根據(jù)查詢條件和查詢字段確定驅(qū)動表，確定驅(qū)動表之后就可以開始連表操作了，然后再在緩存結(jié)果中根據(jù)查詢條件找符合條件的數(shù)據(jù)

1、找出所有在左表，不在右表的紀(jì)錄？

我們可以用右表沒有on匹配則顯示null的規(guī)律, 來找出所有在左表，不在右表的紀(jì)錄， 注意用來判斷的那列必須聲明為not null的。

如：

select id, name, action from user as u left join user_action a on u.id = a.user_id where a.user_id is NULL

注意:

列值為null應(yīng)該用is null 而不能用=NULL 2.這里a.user_id 列必須聲明為 NOT NULL 的.

2、using()如何使用？

on a.c1 = b.c1 等同于 using(c1)

3、連表時候，INNER JOIN 和 , (逗號) 的關(guān)系是什么？

INNER JOIN 和 , (逗號) 在語義上是等同的

到此這篇關(guān)于Mysql中LEFT JOIN和JOIN查詢區(qū)別及原理詳解的文章就介紹到這了,更多相關(guān)LEFT JOIN和JOIN查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！