欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Mysql中LEFT JOIN和JOIN查詢區(qū)別及原理詳解

 更新時(shí)間:2023年08月01日 10:19:32   作者:Young丶  
這篇文章主要介紹了Mysql中LEFT JOIN和JOIN查詢區(qū)別及原理詳解,Nested Loop Join 實(shí)際上就是通過驅(qū)動(dòng)表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù),然后一條一條的通過該結(jié)果集中的數(shù)據(jù)作為過濾條件到下一個(gè)表中查詢數(shù)據(jù),然后合并結(jié)果,需要的朋友可以參考下

一、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)勢(shì)極大的提升了 join的性能:

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

  • 使用場(chǎng)景:只有內(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ù))

  1. 緩存塊嵌套循環(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ù))。
  2. 當(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 version Unknown-0.0問題

    Mysql數(shù)據(jù)庫連接失敗SSLException: Unsupported record 

    這篇文章主要介紹了Mysql數(shù)據(jù)庫連接失敗SSLException: Unsupported record version Unknown-0.0問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2024-06-06
  • mysql中coalesce()的使用技巧小結(jié)

    mysql中coalesce()的使用技巧小結(jié)

    在mysql中,其實(shí)有不少方法和函數(shù)是很有用的,這次介紹一個(gè)叫coalesce的,拼寫十分麻煩,但其實(shí)作用是將返回傳入的參數(shù)中第一個(gè)非null的值,下面這篇文章主要給大家介紹了在mysql中coalesce()使用技巧的相關(guān)資料,需要的朋友可以參考下。
    2017-06-06
  • Mysql Sql語句注釋大全

    Mysql Sql語句注釋大全

    這篇文章主要介紹了Mysql Sql語句注釋大全,需要的朋友可以參考下
    2017-07-07
  • 如何解決mysql導(dǎo)入sql文件慢、錯(cuò)等問題

    如何解決mysql導(dǎo)入sql文件慢、錯(cuò)等問題

    這篇文章主要介紹了如何解決mysql導(dǎo)入sql文件慢、錯(cuò)等問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2024-09-09
  • mysql使用from與join兩表查詢的區(qū)別總結(jié)

    mysql使用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-12
  • 詳解 MySQL的FreeList機(jī)制

    詳解 MySQL的FreeList機(jī)制

    這篇文章主要介紹了MySQL的FreeList機(jī)制的相關(guān)資料,幫助大家更好的理解和使用MySQL 數(shù)據(jù)庫,感興趣的朋友可以了解下
    2020-11-11
  • MySQL慢SQL語句常見誘因以及解決方法

    MySQL慢SQL語句常見誘因以及解決方法

    在本篇文章里小編給大家整理的關(guān)于MySQL慢SQL語句常見誘因以及解決方法,有需要的朋友們可以學(xué)習(xí)下。
    2019-08-08
  • MySQL基礎(chǔ)教程第一篇 mysql5.7.18安裝和連接教程

    MySQL基礎(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

    這篇文章主要介紹了解決MySql版本問題sql_mode=only_full_group_by,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2022-11-11
  • 查看本地MYSQL數(shù)據(jù)庫IP地址的三種方法

    查看本地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

最新評(píng)論