Mysql中LEFT JOIN和JOIN查詢區(qū)別及原理詳解
一、Join查詢?cè)?/h2>
查詢?cè)恚?strong>MySQL內(nèi)部采用了一種叫做 nested loop join(嵌套循環(huán)連接)的算法。
Nested Loop Join 實(shí)際上就是通過驅(qū)動(dòng)表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù),然后一條一條的通過該結(jié)果集中的數(shù)據(jù)作為過濾條件到下一個(gè)表中查詢數(shù)據(jù),然后合并結(jié)果。
如果還有第三個(gè)參與 Join,則再通過前兩個(gè)表的 Join 結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù),再一次通過循環(huán)查詢條件到第三個(gè)表中查詢數(shù)據(jù),如此往復(fù),基本上MySQL采用的是最容易理解的算法來實(shí)現(xiàn)join。
所以驅(qū)動(dòng)表的選擇非常重要,驅(qū)動(dòng)表的數(shù)據(jù)小可以顯著降低掃描的行數(shù)。
一般情況下參與聯(lián)合查詢的兩張表都會(huì)一大一小,如果是join,在沒有其他過濾條件的情況下MySQL會(huì)自動(dòng)選擇小表作為驅(qū)動(dòng)表。
簡單來說,驅(qū)動(dòng)表就是主表,left join 中的左表就是驅(qū)動(dòng)表,right join 中的右表是驅(qū)動(dòng)表。
二.Nested-Loop Join
在Mysql中,使用Nested-Loop Join的算法思想去優(yōu)化join,Nested-Loop Join翻譯成中文則是“嵌套循環(huán)連接”。
舉個(gè)例子:
select * from t1 inner join t2 on t1.id=t2.tid
(1)t1稱為外層表,也可稱為驅(qū)動(dòng)表。
(2)t2稱為內(nèi)層表,也可稱為被驅(qū)動(dòng)表。
//偽代碼表示: 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算法時(shí),會(huì)有優(yōu)先級(jí),理論上會(huì)優(yōu)先判斷能否使用INLJ、BNLJ:
Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join
三. Simple Nested-Loop Join:
如下圖,r為驅(qū)動(dòng)表,s為匹配表,可以看到從r中分別取出r1、r2、…、rn去匹配s表的左右列,然后再合并數(shù)據(jù),對(duì)s表進(jìn)行了rn次訪問,對(duì)數(shù)據(jù)庫開銷大。
如果table1有1萬條數(shù)據(jù),table2有1萬條數(shù)據(jù),那么數(shù)據(jù)比較的次數(shù)=1萬 * 1萬 =1億次,這種查詢效率會(huì)非常慢。
所以Mysql繼續(xù)優(yōu)化,然后衍生出Index Nested-LoopJoin、Block Nested-Loop Join兩種NLJ算法。
在執(zhí)行join查詢時(shí)mysql會(huì)根據(jù)情況選擇兩種之一進(jìn)行join查詢。
四.Index Nested-LoopJoin(減少內(nèi)層表數(shù)據(jù)的匹配次數(shù))
- 索引嵌套循環(huán)連接是基于索引進(jìn)行連接的算法,索引是基于內(nèi)層表的,通過外層表匹配條件直接與內(nèi)層表索引進(jìn)行匹配,避免和內(nèi)層表的每條記錄進(jìn)行比較, 從而利用索引的查詢減少了對(duì)內(nèi)層表的匹配次數(shù),優(yōu)勢極大的提升了 join的性能:
原來的匹配次數(shù) = 外層表行數(shù) * 內(nèi)層表行數(shù) 優(yōu)化后的匹配次數(shù)= 外層表的行數(shù) * 內(nèi)層表索引的高度
- 使用場景:只有內(nèi)層表join的列有索引時(shí),才能用到Index Nested-LoopJoin進(jìn)行連接。
- 由于用到索引,如果索引是輔助索引而且返回的數(shù)據(jù)還包括內(nèi)層表的其他數(shù)據(jù),則會(huì)回內(nèi)層表查詢數(shù)據(jù),多了一些IO操作。
這個(gè)要求非驅(qū)動(dòng)表(匹配表s)上有索引,可以通過索引來減少比較,加速查詢。
在查詢時(shí),驅(qū)動(dòng)表(r)會(huì)根據(jù)關(guān)聯(lián)字段的索引進(jìn)行查找,當(dāng)在索引上找到符合的值,再回表進(jìn)行查詢,也就是只有當(dāng)匹配到索引以后才會(huì)進(jìn)行回表查詢。
如果非驅(qū)動(dòng)表(s)的關(guān)聯(lián)健是主鍵的話,性能會(huì)非常高,如果不是主鍵,要進(jìn)行多次回表查詢,先關(guān)聯(lián)索引,然后根據(jù)二級(jí)索引的主鍵ID進(jìn)行回表操作,性能上比索引是主鍵要慢。
五.Block Nested-Loop Join(減少內(nèi)層表數(shù)據(jù)的循環(huán)次數(shù))
- 緩存塊嵌套循環(huán)連接通過一次性緩存多條數(shù)據(jù),把參與查詢的列緩存到Join Buffer 里,然后拿join buffer里的數(shù)據(jù)批量與內(nèi)層表的數(shù)據(jù)進(jìn)行匹配,從而減少了內(nèi)層循環(huán)的次數(shù)(遍歷一次內(nèi)層表就可以批量匹配一次Join Buffer里面的外層表數(shù)據(jù))。
- 當(dāng)不使用Index Nested-Loop Join的時(shí)候(內(nèi)層表查詢不適用索引),默認(rèn)使用Block Nested-Loop Join。
什么是Join Buffer?
(1)Join Buffer會(huì)緩存所有參與查詢的列而不是只有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)下申請(qǐ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ū)動(dòng)大結(jié)果集,減少外層循環(huán)的數(shù)據(jù)量:
如果小結(jié)果集和大結(jié)果集連接的列都是索引列,mysql在內(nèi)連接時(shí)也會(huì)選擇用小結(jié)果集驅(qū)動(dòng)大結(jié)果集,因?yàn)樗饕樵兊某杀臼潜容^固定的,這時(shí)候外層的循環(huán)越少,join的速度便越快。
為匹配的條件增加索引:爭取使用INLJ,減少內(nèi)層表的循環(huán)次數(shù)
增大join buffer size的大?。寒?dāng)使用BNLJ時(shí),一次緩存的數(shù)據(jù)越多,那么外層表循環(huán)的次數(shù)就越少
減少不必要的字段查詢:
(1)當(dāng)用到BNLJ時(shí),字段越少,join buffer 所緩存的數(shù)據(jù)就越多,外層表的循環(huán)次數(shù)就越少;
(2)當(dāng)用到INLJ時(shí),如果可以不回表查詢,即利用到覆蓋索引,則可能可以提示速度。
實(shí)例:
假設(shè)兩張表a 和 b
其中b的關(guān)聯(lián)有comments_id,所以有索引。
1.join
使用的是Index Nested-Loop Join,先對(duì)驅(qū)動(dòng)表a的主鍵篩選,得到一條,然后對(duì)非驅(qū)動(dòng)表b的索引進(jìn)行seek匹配,預(yù)計(jì)得到一條數(shù)據(jù)。
下面這種情況沒用到索引:
使用Block Nested-Loop Join,如果b表數(shù)據(jù)少,作為驅(qū)動(dòng)表,將b的需要的數(shù)據(jù)緩存到j(luò)oin buffer中,批量對(duì)a表掃描
2.left join:
這里用到了索引,所以會(huì)采用Index Nested-Loop Join,因?yàn)闆]有篩選條件,會(huì)選擇一張表作為驅(qū)動(dòng)表去進(jìn)行join,去關(guān)聯(lián)非驅(qū)動(dòng)表的索引。
如果加了條件
就會(huì)從驅(qū)動(dòng)表篩選出一條來進(jìn)行對(duì)非驅(qū)動(dòng)表的匹配。
left join:會(huì)保全左表數(shù)據(jù),如果右表沒相關(guān)數(shù)據(jù),會(huì)顯示null
light join:會(huì)保全右表數(shù)據(jù),如果左表沒相關(guān)數(shù)據(jù),會(huì)顯示null
inner join:部分主從表,結(jié)果會(huì)取兩個(gè)表針對(duì)on條件相匹配的最小集
使用原則
盡量使用inner join,避免left join 和NULL。
on和where的使用區(qū)別
首先要明確一點(diǎn),where中的條件拿到on里去出來的結(jié)果是不同的,如下:
ON 條件(“A LEFT JOIN B ON 條件表達(dá)式”中的ON)用來決定如何從 B 表中檢索數(shù)據(jù)行。如果 B 表中沒有任何一行數(shù)據(jù)匹配 ON 的條件,將會(huì)額外生成一行所有列為 NULL 的數(shù)據(jù),在匹配階段 WHERE 子句的條件都不會(huì)被使用。僅在匹配階段完成以后,WHERE 子句條件才會(huì)被使用。它將從匹配階段產(chǎn)生的數(shù)據(jù)中檢索過濾。
所以我們要注意:在使用Left (right) join的時(shí)候,一定要在先給出盡可能多的匹配滿足條件,減少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é)
連表操作時(shí):先根據(jù)查詢條件和查詢字段確定驅(qū)動(dòng)表,確定驅(qū)動(dòng)表之后就可以開始連表操作了,然后再在緩存結(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、連表時(shí)候,INNER JOIN 和 , (逗號(hào)) 的關(guān)系是什么?
INNER JOIN 和 , (逗號(hào)) 在語義上是等同的
到此這篇關(guān)于Mysql中LEFT JOIN和JOIN查詢區(qū)別及原理詳解的文章就介紹到這了,更多相關(guān)LEFT JOIN和JOIN查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Mysql數(shù)據(jù)庫連接失敗SSLException: Unsupported record
這篇文章主要介紹了Mysql數(shù)據(jù)庫連接失敗SSLException: Unsupported record version Unknown-0.0問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-06-06如何解決mysql導(dǎo)入sql文件慢、錯(cuò)等問題
這篇文章主要介紹了如何解決mysql導(dǎo)入sql文件慢、錯(cuò)等問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-09-09mysql使用from與join兩表查詢的區(qū)別總結(jié)
這篇文章主要給大家介紹了關(guān)于mysql使用from與join兩表查詢的區(qū)別的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-12-12MySQL基礎(chǔ)教程第一篇 mysql5.7.18安裝和連接教程
這篇文章主要為大家詳細(xì)介紹了MySQL基礎(chǔ)教程第一篇,mysql5.7.18安裝和連接教程,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2017-05-05解決MySql版本問題sql_mode=only_full_group_by
這篇文章主要介紹了解決MySql版本問題sql_mode=only_full_group_by,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-11-11查看本地MYSQL數(shù)據(jù)庫IP地址的三種方法
本文介紹了多種方法來查看連接到本地MySQL服務(wù)器的IP地址,括使用SQL查詢從`information_schema.processlist`獲取IP地址,并通過`group by`進(jìn)行統(tǒng)計(jì),以及通過命令行工具如`mysql`和`awk`進(jìn)行過濾和計(jì)數(shù),這些方法有助于監(jiān)控和管理數(shù)據(jù)庫連接,需要的朋友可以參考下2024-10-10