一文帶你了解MySQL之連接原理
搞數(shù)據(jù)庫一個(gè)避不開的概念就是Join,翻譯成中?就是連接。相信很多小伙伴初學(xué)連接的時(shí)候有些一臉懵,理解了連接的語義之后又可能不明白各個(gè)表中的記錄到底是怎么連起來的,以至于在使用的時(shí)候常常陷入下邊兩種誤區(qū):
誤區(qū)一:業(yè)務(wù)至上,管他三七二十一,再復(fù)雜的查詢也用在一個(gè)連接語句中搞定誤區(qū)二:敬而遠(yuǎn)之,慢查詢可能就是因?yàn)槭褂昧诉B接導(dǎo)致的,以后再也不敢亂用了
所以本章就來學(xué)習(xí)連接的原理??紤]到一部分小伙伴可能忘了連接是個(gè)啥或者壓根就不知道,為了節(jié)省他們百度或者看其他書的寶貴時(shí)間,我們先來介紹一下 MySQL 中支持的一些連接語法。
一、連接簡介
1.1 連接的本質(zhì)
為了學(xué)習(xí),我們創(chuàng)建兩個(gè)簡單的表,并給它們插入一些數(shù)據(jù):
mysql> create table demo9 (m1 int, n1 char(1)); Query OK, 0 rows affected (0.01 sec) mysql> insert into demo9 values(1, 'a'), (2, 'b'), (3, 'c'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> create table demo10 (m2 int, n2 char(1)); Query OK, 0 rows affected (0.03 sec) mysql> insert into demo10 values(2, 'b'), (3, 'c'), (4, 'd'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
我們成功創(chuàng)建了demo9、demo10兩個(gè)表,這兩個(gè)表都有兩個(gè)列,一個(gè)是int類型的,一個(gè)是char(1)類型的,填充好數(shù)據(jù)的兩個(gè)表是這樣:
mysql> select * from demo9; +------+------+ | m1 | n1 | +------+------+ | 1 | a | | 2 | b | | 3 | c | +------+------+ 3 rows in set (0.00 sec) mysql> select * from demo10; +------+------+ | m2 | n2 | +------+------+ | 2 | b | | 3 | c | | 4 | d | +------+------+ 3 rows in set (0.00 sec)
連接的本質(zhì)就是把各個(gè)連接表中的記錄都取出來依次匹配的組合加?結(jié)果集并返回給用戶。所以我們把demo9和demo10兩個(gè)表連接起來的過程如下圖所示:
這個(gè)過程看起來就是把demo9表的記錄和demo10的記錄連起來組成新的更大的記錄,所以這個(gè)查詢過程稱之為連接查詢。連接查詢的結(jié)果集中包含一個(gè)表中的每一條記錄與另一個(gè)表中的每一條記錄相互匹配的組合,像這樣的結(jié)果集就可以稱之為笛卡爾積。因?yàn)楸韉emo9中有3條記錄,表demo10中也有3條記錄,所以這兩個(gè)表連接之后的笛卡爾積就有3×3=9條記錄。在MySQL中,連接查詢的語法也很隨意,只要在from語句后邊跟多個(gè)表名就好了,比如我們把demo9表和demo10表連接起來的查詢語句可以寫成這樣:
mysql> select * from demo9,demo10; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 1 | a | 2 | b | | 2 | b | 2 | b | | 3 | c | 2 | b | | 1 | a | 3 | c | | 2 | b | 3 | c | | 3 | c | 3 | c | | 1 | a | 4 | d | | 2 | b | 4 | d | | 3 | c | 4 | d | +------+------+------+------+ 9 rows in set (0.00 sec)
1.2 連接過程簡介
如果我們樂意,我們可以連接任意數(shù)量張表,但是如果沒有任何限制條件的話,這些表連接起來產(chǎn)生的笛卡爾積可能是非常巨大的。比如說3個(gè)100條記錄的表連接起來產(chǎn)生的笛卡爾積就有100×100×100=1000000條數(shù)據(jù)!所以在連接的時(shí)候過濾掉特定記錄組合是有必要的,在連接查詢中的過濾條件可以分成兩種:
- 涉及單表的條件
這種只設(shè)計(jì)單表的過濾條件我們之前都提到過千萬萬遍了,我們之前也一直稱為搜索條件,比如demo9.m1 > 1是只針對t1表的過濾條件,demo10.n2 < 'd’是只針對t2表
的過濾條件。
- 涉及兩表的條件
這種過濾條件我們之前沒說過,比如demo9.m1 = demo10.m2、demo9.n1 > demo10.n2等,這些條件中涉及到了兩個(gè)表,我們稍后會(huì)仔細(xì)分析這種過濾條件是如何使用的。
下邊我們就要看一下攜帶過濾條件的連接查詢的大致執(zhí)行過程了,比如說下邊這個(gè)查詢語句:
mysql> select * from demo9, demo10 where demo9.m1 > 1 and demo9.m1 = demo10.m2 and demo10.n2 < 'd';
在這個(gè)查詢中,我們指明了這三個(gè)過濾條件:
- demo9.m1 > 1
- demo9.m1 = demo10.m2
- demo10.n2 < ‘d’
那么這個(gè)查詢的大致執(zhí)行過程如下:
步驟一:
首先確定第一個(gè)需要查詢的表,這個(gè)表稱之為驅(qū)動(dòng)表。怎樣在單表中執(zhí)行查詢語句我們在前一章都講過了,只需要選取代價(jià)最小的那種訪問方法去執(zhí)行單表查詢語句就好了(就是說從const、ref、ref_or_null、range、index、all這些執(zhí)行方法中選取代價(jià)最小的去執(zhí)行查詢)。此處假設(shè)使用demo9作為驅(qū)動(dòng)表,那么就需要到demo9表中找滿足demo9.m1>1的記錄,因?yàn)楸碇械臄?shù)據(jù)太少,我們也沒在表上建立二級索引,所以此處查詢demo9表的訪問方法就設(shè)定為all吧,也就是采用全表掃描的方式執(zhí)行單表查詢。關(guān)于如何提升連接查詢的性能我們之后再說,現(xiàn)在先把基本概念捋清楚。所以查詢過程就如下圖所示:
我們可以看到,demo9表中符合demo9.m1 > 1的記錄有兩條。
步驟二:
針對上一步驟中從驅(qū)動(dòng)表產(chǎn)生的結(jié)果集中的每一條記錄,分別需要到demo10表中查找匹配的記錄,所謂匹配的記錄,指的是符合過濾條件的記錄。因?yàn)槭歉鶕?jù)demo9表中的記錄去找demo10表中的記錄,所以demo10表也可以被稱之為被驅(qū)動(dòng)表。上一步驟從驅(qū)動(dòng)表中得到了2條記錄,所以需要查詢2次demo10表。此時(shí)涉及兩個(gè)表的列的過濾條件demo9.m1=demo10.m2就派上用場了:
- 當(dāng)demo9.m1 = 2時(shí),過濾條件demo9.m1 = demo10.m2就相當(dāng)于demo10.m2 = 2,所以此時(shí)demo10表相當(dāng)于有了demo10.m2 = 2、demo10.n2 < 'd’這兩個(gè)過濾條件,然后到demo10表中執(zhí)行單表查詢
- 當(dāng)demo9.m1 = 3時(shí),過濾條件demo9.m1 = demo10.m2就相當(dāng)于demo10.m2 = 3,所以此時(shí)demo10表相當(dāng)于有了demo10.m2 = 3、demo10.n2<'d’這兩個(gè)過濾條件,然后到demo10表中執(zhí)行單表查詢
所以整個(gè)連接查詢的執(zhí)行過程就如下圖所示:
也就是說整個(gè)連接查詢最后的結(jié)果只有兩條符合過濾條件的記錄:
mysql> select * from demo9, demo10 where demo9.m1 > 1 and demo9.m1 = demo10.m2 and demo10.n2 < 'd'; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | +------+------+------+------+ 2 rows in set (0.00 sec)
從上邊兩個(gè)步驟可以看出來,我們上邊的這個(gè)兩表連接查詢共需要查詢1次demo9表,2次demo10表。當(dāng)然這是在特定的過濾條件下的結(jié)果,如果我們把demo9.m1 > 1這個(gè)條件去掉,那么從demo9表中查出的記錄就有3條,就需要查詢3次demo10表了。也就是說在兩表連接查詢中,驅(qū)動(dòng)表只需要訪問?次,被驅(qū)動(dòng)表可能被訪問多次。
1.3 內(nèi)連接和外連接
為了更好的學(xué)習(xí)后邊的內(nèi)容,我們先創(chuàng)建兩個(gè)有現(xiàn)實(shí)意義的表:
mysql> create table student ( number int not null auto_increment comment '學(xué)號(hào)', name varchar(5) comment '姓名', major varchar(30) comment '專業(yè)', primary key (number) ) comment '學(xué)生信息表'; Query OK, 0 rows affected (0.02 sec) mysql> create table score ( number int comment '學(xué)號(hào)', subject varchar(30) comment '科目', score tinyint comment '成績', primary key (number, score) ) comment '學(xué)生成績表'; Query OK, 0 rows affected (0.02 sec) mysql> insert into student values(1,'張三','軟件學(xué)院'),(2,'李四','計(jì)算機(jī)科學(xué)與工程'),(3,'王五','計(jì)算機(jī)科學(xué)與工程'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into score values(1,'MySQL是怎樣運(yùn)行的',78),(1,'MySQL實(shí)戰(zhàn)45講',88),(2,'MySQL是怎樣運(yùn)行的',78),(2,'MySQL實(shí)戰(zhàn)45講',100); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
我們新建了一個(gè)學(xué)?信息表,一個(gè)學(xué)生成績表,然后我們向上述兩個(gè)表中插入一些數(shù)據(jù),插入后兩表中的數(shù)據(jù)如下:
mysql> select * from student; +--------+--------+--------------------------+ | number | name | major | +--------+--------+--------------------------+ | 1 | 張三 | 軟件學(xué)院 | | 2 | 李四 | 計(jì)算機(jī)科學(xué)與工程 | | 3 | 王五 | 計(jì)算機(jī)科學(xué)與工程 | +--------+--------+--------------------------+ 3 rows in set (0.00 sec) mysql> select * from score; +--------+-------------------------+-------+ | number | subject | score | +--------+-------------------------+-------+ | 1 | MySQL是怎樣運(yùn)行的 | 78 | | 1 | MySQL實(shí)戰(zhàn)45講 | 88 | | 2 | MySQL是怎樣運(yùn)行的 | 98 | | 2 | MySQL實(shí)戰(zhàn)45講 | 100 | +--------+-------------------------+-------+ 4 rows in set (0.00 sec)
現(xiàn)在我們想把每個(gè)學(xué)生的考試成績都查詢出來就需要進(jìn)行兩表連接了(因?yàn)閟core中沒有姓名信息,所以不能單純只查詢score表)。連接過程就是從student表中取出記錄,在score表中查找number相同的成績記錄,所以過濾條件就是student.number =socre.number
,整個(gè)查詢語句就是這樣:
mysql> select * from student,score where student.number=score.number; +--------+--------+--------------------------+--------+-------------------------+-------+ | number | name | major | number | subject | score | +--------+--------+--------------------------+--------+-------------------------+-------+ | 1 | 張三 | 軟件學(xué)院 | 1 | MySQL是怎樣運(yùn)行的 | 78 | | 1 | 張三 | 軟件學(xué)院 | 1 | MySQL實(shí)戰(zhàn)45講 | 88 | | 2 | 李四 | 計(jì)算機(jī)科學(xué)與工程 | 2 | MySQL是怎樣運(yùn)行的 | 98 | | 2 | 李四 | 計(jì)算機(jī)科學(xué)與工程 | 2 | MySQL實(shí)戰(zhàn)45講 | 100 | +--------+--------+--------------------------+--------+-------------------------+-------+ 4 rows in set (0.00 sec)
字段有點(diǎn)多,我們可以少查詢幾個(gè)字段:
mysql> select s1.number,s1.name,s2.subject,s2.score from student s1 ,score s2 where s1.number=s2.number; +--------+--------+-------------------------+-------+ | number | name | subject | score | +--------+--------+-------------------------+-------+ | 1 | 張三 | MySQL是怎樣運(yùn)行的 | 78 | | 1 | 張三 | MySQL實(shí)戰(zhàn)45講 | 88 | | 2 | 李四 | MySQL是怎樣運(yùn)行的 | 98 | | 2 | 李四 | MySQL實(shí)戰(zhàn)45講 | 100 | +--------+--------+-------------------------+-------+ 4 rows in set (0.00 sec)
從上述查詢結(jié)果中我們可以看到,各個(gè)同學(xué)對應(yīng)的各科成績就都被查出來了,可是有個(gè)問題,王五同學(xué),也就是學(xué)號(hào)為3的同學(xué)因?yàn)槟承┰驔]有參加考試,所以在score表中沒有對應(yīng)的成績記錄。那如果老師想查看所有同學(xué)的考試成績,即使是缺考的同學(xué)也應(yīng)該展示出來,但是到目前為止我們介紹的連接查詢是無法完成這樣的需求的。我們稍微思考一下這個(gè)需求,其本質(zhì)是想:驅(qū)動(dòng)表中的記錄即使在被驅(qū)動(dòng)表中沒有匹配的記錄,也仍然需要加入到結(jié)果集。為了解決這個(gè)問題,就有了內(nèi)連接和外連接的概念:
- 對于內(nèi)連接的兩個(gè)表,驅(qū)動(dòng)表中的記錄在被驅(qū)動(dòng)表中找不到匹配的記錄,該記錄不會(huì)加入到最后的結(jié)果集,我們上邊提到的連接都是所謂的內(nèi)連接
- 對于外連接的兩個(gè)表,驅(qū)動(dòng)表中的記錄即使在被驅(qū)動(dòng)表中沒有匹配的記錄,也仍然需要加入到結(jié)果集
在MySQL中,根據(jù)選取驅(qū)動(dòng)表的不同,外連接仍然可以細(xì)分為2種:
- 左外連接:選取左側(cè)的表為驅(qū)動(dòng)表
- 右外連接:選取右側(cè)的表為驅(qū)動(dòng)表
可是這樣仍然存在問題,即使對于外連接來說,有時(shí)候我們也并不想把驅(qū)動(dòng)表的全部記錄都加入到最后的結(jié)果集。這就犯難了,有時(shí)候匹配失敗要加入結(jié)果集,有時(shí)候又不要加入結(jié)果集,這咋辦,把過濾條件分為兩種不就解決了這個(gè)問題了么,所以放在不同地方的過濾條件是有不同語義的:
- where子句中的過濾條件:where子句中的過濾條件就是我們平時(shí)見的那種,不論是內(nèi)連接還是外連接,凡是不符合where子句中的過濾條件的記錄都不會(huì)被加入最后的結(jié)果集。
- ON子句中的過濾條件:對于外連接的驅(qū)動(dòng)表的記錄來說,如果無法在被驅(qū)動(dòng)表中找到匹配ON子句中的過濾條件的記錄,那么該記錄仍然會(huì)被加入到結(jié)果集中,對應(yīng)的被驅(qū)動(dòng)表記錄的各個(gè)字段使用NULL值填充。
需要注意的是,這個(gè)ON子句是專門為外連接驅(qū)動(dòng)表中的記錄在被驅(qū)動(dòng)表找不到匹配記錄時(shí)應(yīng)不應(yīng)該把該記錄加入結(jié)果集這個(gè)場景下提出的,所以如果把ON子句放到內(nèi)連接中,MySQL會(huì)把它和where子句一樣對待,也就是說:內(nèi)連接中的where子句和ON子句是等價(jià)的。
一般情況下,我們都把只涉及單表的過濾條件放到where子句中,把涉及兩表的過濾條件都放到ON子句中,我們也一般把放到ON子句中的過濾條件也稱之為連接條件。
小提示:
左外連接和右外連接簡稱左連接和右連接。
1.4 左外連接
左外連接的語法還是挺簡單的,比如我們要把demo9和demo10兩個(gè)表進(jìn)行左外連接,可以這么寫:
select * from demo9 left [outer] join demo10 on 連接條件 [where 普通過濾條件]
其中中括號(hào)里的outer單詞是可以省略的。對于left join類型的連接來說,我們把放在左邊的表稱之為外表或者驅(qū)動(dòng)表,右邊的表稱之為內(nèi)表或者被驅(qū)動(dòng)表。所以上述例子中demo9就是外表或者驅(qū)動(dòng)表,demo10就是內(nèi)表或者被驅(qū)動(dòng)表。需要注意的是,對于左外連接和右外連接來說,必須使用on子句來指出連接條件。了解了左外連接的基本語法之后,再次回到我們上邊那個(gè)現(xiàn)實(shí)問題中來,看看怎樣寫查詢語句才能把所有的學(xué)生的成績信息都查詢出來,即使是缺考的考生也應(yīng)該被放到結(jié)果集中:
mysql> select s1.number,s1.name,s2.subject,s2.score from student s1 left join score s2 on s1.number=s2.number; +--------+--------+-------------------------+-------+ | number | name | subject | score | +--------+--------+-------------------------+-------+ | 1 | 張三 | MySQL是怎樣運(yùn)行的 | 78 | | 1 | 張三 | MySQL實(shí)戰(zhàn)45講 | 88 | | 2 | 李四 | MySQL是怎樣運(yùn)行的 | 98 | | 2 | 李四 | MySQL實(shí)戰(zhàn)45講 | 100 | | 3 | 王五 | NULL | NULL | +--------+--------+-------------------------+-------+ 5 rows in set (0.01 sec)
從結(jié)果集中可以看出來,雖然王五并沒有對應(yīng)的成績記錄,但是由于采用的是連接類型為左外連接,所以仍然把她放到了結(jié)果集中,只不過在對應(yīng)的成績記錄的各列使用null值填充而已。
1.5 右外連接
右外連接和左外連接的原理是一樣一樣的,語法也只是把left換成right而已:
select * from demo9 right [outer] join demo10 on 連接條件 [where 普通過濾條件]
只不過驅(qū)動(dòng)表是右邊的表,被驅(qū)動(dòng)表是左邊的表,具體就不嘮叨了。
1.6 內(nèi)連接
內(nèi)連接和外連接的根本區(qū)別就是在驅(qū)動(dòng)表中的記錄不符合on子句中的連接條件時(shí)不會(huì)把該記錄加入到最后的結(jié)果集,我們最開始嘮叨的那些連接查詢的類型都是內(nèi)連接。不過之前僅僅提到了一種最簡單的內(nèi)連接語法,就是直接把需要連接的多個(gè)表都放到from子句后邊。其實(shí)針對內(nèi)連接,mysql提供了好多不同的語法,我們以demo9和demo10表為例瞅瞅:
select * from demo9 [inner|cross] join demo10 [on 連接條件] [where 普通過濾條件];
也就是說在mysql中,下邊這?種內(nèi)連接的寫法都是等價(jià)的:
select * from demo9 join demo10; select * from demo9 inner join demo10; select * from demo9 cross join demo10;
上邊的這些寫法和直接把需要連接的表名放到from語句之后,用逗號(hào),分隔開的寫法是等價(jià)的:
select * from demo9,demo10;
現(xiàn)在我們雖然介紹了很多種內(nèi)連接的書寫方式,不過熟悉一種就好了,這里我們推薦inner join的形式書寫內(nèi)連接(因?yàn)閕nner join語義很明確嘛,可以和left join和right join很輕松的區(qū)分開)。這里需要注意的是,由于在內(nèi)連接中on子句和where子句是等價(jià)的,所以內(nèi)連接中不要求強(qiáng)制寫明on子句。
我們前邊說過,連接的本質(zhì)就是把各個(gè)連接表中的記錄都取出來依次匹配的組合加入結(jié)果集并返回給用戶。不論哪個(gè)表作為驅(qū)動(dòng)表,兩表連接產(chǎn)生的笛卡爾積肯定是一樣的。而對于內(nèi)連接來說,由于凡是不符合on子句或where子句中的條件的記錄都會(huì)被過濾掉,其實(shí)也就相當(dāng)于從兩表連接的笛卡爾積中把不符合過濾條件的記錄給踢出去,所以對于內(nèi)連接來說,驅(qū)動(dòng)表和被驅(qū)動(dòng)表是可以互換的,并不會(huì)影響最后的查詢結(jié)果。但是對于外連接來說,由于驅(qū)動(dòng)表中的記錄即使在被驅(qū)動(dòng)表中找不到符合on子句連接條件的記錄,所以此時(shí)驅(qū)動(dòng)表和被驅(qū)動(dòng)表的關(guān)系就很重要了,也就是說左外連接和右外連接的驅(qū)動(dòng)表和被驅(qū)動(dòng)表不能輕易互換。
小結(jié)
上邊說了很多,給大家的感覺不是很直觀,我們直接把表demo9和demo10的三種連接方式寫在一起,這樣大家理解起來就很easy了:
mysql> select * from demo9 inner join demo10 on demo9.m1 = demo10.m2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | +------+------+------+------+ 2 rows inset (0.00 sec) mysql> select * from demo9 left join demo10 on demo9.m1 = demo10.m2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | | 1 | a | null | null | +------+------+------+------+ 3 rows inset (0.00 sec) mysql> select * from demo9 right join demo10 on demo9.m1 = demo10.m2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | | null | null | 4 | d | +------+------+------+------+ 3 rows inset (0.00 sec)
二、連接的原理
上邊的介紹都只是為了喚醒大家對連接、內(nèi)連接、外連接這些概念的記憶,這些基本概念是為了真正進(jìn)入本章主題做的鋪墊。真正的重點(diǎn)是MySQL采用了什么樣的算法來進(jìn)行表與表之的連接,了解了這個(gè)之后,大家才能明白為啥有的連接查詢運(yùn)行的快如閃電,有的卻慢如蝸牛。
2.1 嵌套循環(huán)連接(Nested-Loop Join)
我們前邊說過,對于兩表連接來說,驅(qū)動(dòng)表只會(huì)被訪問一遍,但被驅(qū)動(dòng)表卻要被訪問到好多遍,具體訪問幾遍取決于對驅(qū)動(dòng)表執(zhí)行單表查詢后的結(jié)果集中的記錄條數(shù)。對于內(nèi)連接來說,選取哪個(gè)表為驅(qū)動(dòng)表都沒關(guān)系,而外連接的驅(qū)動(dòng)表是固定的,也就是說左(外)連接的驅(qū)動(dòng)表就是左邊的那個(gè)表,右(外)連接的驅(qū)動(dòng)表就是右邊的那個(gè)表。我們上邊已經(jīng)大致介紹過demo9表和demo10表執(zhí)行內(nèi)連接查詢的大致過程,我們溫習(xí)一下:
- 選取驅(qū)動(dòng)表,使用與驅(qū)動(dòng)表相關(guān)的過濾條件,選取代價(jià)最低的單表訪問方法來執(zhí)行對驅(qū)動(dòng)表的單表查詢。
- 對上述步驟中查詢驅(qū)動(dòng)表得到的結(jié)果集中每一條記錄,都分別到被驅(qū)動(dòng)表中查找匹配的記錄。
通過的兩表連接過程如下圖所示:
如果有3個(gè)表進(jìn)行連接的話,那么步驟2中得到的結(jié)果集就像是新的驅(qū)動(dòng)表,然后第三個(gè)表就成為了被驅(qū)動(dòng)表,重復(fù)上邊過程,也就是步驟2中得到的結(jié)果集中的每一條記錄都需要到demo11表中找一找有沒有匹配的記錄,用偽代碼表示一下這個(gè)過程就是這樣:
for each row in demo9 { #此處表示遍歷滿足對demo9單表查詢結(jié)果集中的每一條記錄 for each row in demo10 { #此處表示對于某條demo9表的記錄來說,遍歷滿足對demo10單表查詢結(jié)果集中的每一條記 for each row in demo11 { #此處表示對于某條demo9和demo10表的記錄組合來說,對demo11表進(jìn)行單表查詢 if row satisfies join conditions, send to client } } }
這個(gè)過程就像是一個(gè)嵌套的循環(huán),所以這種驅(qū)動(dòng)表只訪問一次,但被驅(qū)動(dòng)表卻可能被多次訪問,訪問次數(shù)取決于對驅(qū)動(dòng)表執(zhí)行單表查詢后的結(jié)果中的記錄條數(shù)的連接執(zhí)行方式稱之為嵌套循環(huán)連接(Nested-Loop Join),這是最簡單,也是最笨拙的一種連接查詢算法。
2.2 使用索引加快連接速度
我們知道在嵌套循環(huán)連接的步驟2中可能需要訪問多次被驅(qū)動(dòng)表,如果訪問被驅(qū)動(dòng)表的方式都是全表掃描的話,那得要掃描好多次呀~但是別忘了,查詢demo10表其實(shí)就相當(dāng)于一次單表掃描,我們可以利用索引來加快查詢速度?;仡櫼幌伦铋_始介紹的demo9表和demo10表進(jìn)行內(nèi)連接的例子:
mysql> select * from demo9, demo10 where demo9.m1 > 1 and demo9.m1 = demo10.m2 and demo10.n2 < 'd';
我們使用的其實(shí)是嵌套循壞連接算法執(zhí)行的連接查詢,再把上邊那個(gè)查詢執(zhí)行過程表拉下來給大家看一下:
查詢驅(qū)動(dòng)表demo9后的結(jié)果集有兩條記錄,嵌套循壞連接算法需要對被驅(qū)動(dòng)表查詢兩次:
第一次:
當(dāng)demo9.m1 = 2時(shí),去查詢一遍demo10表,對demo10查詢的語句相當(dāng)于:
select * from demo10 where demo10.m2 = 2 and demo10.m2 < 'd';
第二次:
當(dāng)demo9.m1 =3時(shí),去查詢一遍demo10表,對demo10查詢的語句相當(dāng)于:
select * from demo10 where demo10.m2 = 3 and demo10.m2 < 'd';
可以看到,原來的demo9.m1 = demo10.m2這個(gè)涉及兩個(gè)表的過濾條件在針對demo10表做查詢時(shí)關(guān)于demo9表的條件就已經(jīng)確定了,所以我們只需要單單優(yōu)化對demo10表的查詢了,上述兩個(gè)對demo10表的查詢語句中利用到的列是m2和n2列,我們可以:
- 在m2列上建立索引,因?yàn)閷2列的條件是等值查找,比如demo10.m2 = 2、demo10.m2 = 3等,所以可能使用到ref的訪問方法,假設(shè)使用ref的訪問方法去執(zhí)行對demo10表的查詢的話,需要回表之后再判斷demo10.n2 < d這個(gè)條件是否成立。
- 這里有一個(gè)比較特殊的情況,就是假設(shè)m2列是demo10表的主鍵或者唯一二級索引列,那么使用demo10.m2 = 常數(shù)值這樣的條件從demo10表中查找記錄的過程的代價(jià)就是常數(shù)級別的。我們知道在單表中使用主鍵值或者唯一二級索引列的值進(jìn)行等值查找的方式稱之為const,MySQL把在連接查詢中對被驅(qū)動(dòng)表使用主鍵值或者唯一二級索引列的值進(jìn)行等值查找的查詢執(zhí)行方式稱之為:eq_ref。
- 在n2列上建立索引,涉及到的條件是demo10.n2 < ‘d’,可能用到range的訪問方法,假設(shè)使用range的訪問方法對demo10表的查詢的話,需要回表之后再判斷在m2
列上的條件是否成立。
假設(shè)m2和n2列上都存在索引的話,那么就需要從這兩個(gè)里邊挑一個(gè)代價(jià)更低的去執(zhí)行對demo10表的查詢。當(dāng)然,建立了索引不一定使用索引,只有在二級索引 +回表的代價(jià)比全表掃描的代價(jià)更低時(shí)才會(huì)使用索引。
另外,有時(shí)候連接查詢的查詢列表和過濾條件中可能只涉及被驅(qū)動(dòng)表的部分列,而這些列都是某個(gè)索引的一部分,這種情況下即使不能使用eq_ref、ref、ref_or_null或者range這些訪問方法執(zhí)行對被驅(qū)動(dòng)表的查詢的話,也可以使用索引掃描,也就是index的訪問方法來查詢被驅(qū)動(dòng)表。所以我們建議在真實(shí)工作中最好不要使用*作為查詢列表,最好把真實(shí)用到的列作為查詢列表。
2.3 基于塊的嵌套循環(huán)連接(Block Nested-Loop Join)
掃描一個(gè)表的過程其實(shí)是先把這個(gè)表從磁盤上加載到內(nèi)存中,然后從內(nèi)存中比較匹配條件是否滿足。現(xiàn)實(shí)生活中的表可不像demo9、demo11這種只有3條記錄,成千上萬條記錄都是少的,幾百萬、幾千萬甚幾億條記錄的表到處都是。內(nèi)存里可能并不能完全存放的下表中所有的記錄,所以在掃描表前邊記錄的時(shí)候后邊的記錄可能還在磁盤上,等掃描到后邊記錄的時(shí)候可能內(nèi)存不足,所以需要把前邊的記錄從內(nèi)存中釋放掉。我們前邊說過,采用嵌套循環(huán)連接算法的兩表連接過程中,被驅(qū)動(dòng)表可是要被訪問好多次的,如果這個(gè)被驅(qū)動(dòng)表中的數(shù)據(jù)特別多而且不能使用索引進(jìn)行訪問,那就相當(dāng)于要從磁盤上讀好多次這個(gè)表,這個(gè)I/O代價(jià)就非常大了,所以我們得想辦法:盡量減少訪問被驅(qū)動(dòng)表的次數(shù)。
當(dāng)被驅(qū)動(dòng)表中的數(shù)據(jù)?常多時(shí),每次訪問被驅(qū)動(dòng)表,被驅(qū)動(dòng)表的記錄會(huì)被加載到內(nèi)存中,在內(nèi)存中的每一條記錄只會(huì)和驅(qū)動(dòng)表結(jié)果集的一條記錄做匹配,之后就會(huì)被從內(nèi)存中清除掉。然后再從驅(qū)動(dòng)表結(jié)果集中拿出另一條記錄,再一次把被驅(qū)動(dòng)表的記錄加載到內(nèi)存中一遍,周而復(fù)始,驅(qū)動(dòng)表結(jié)果集中有多少條記錄,就得把被驅(qū)動(dòng)表從磁盤上加載到內(nèi)存中多少次。所以我們可不可以在把被驅(qū)動(dòng)表的記錄加載到內(nèi)存的時(shí)候,一次性和多條驅(qū)動(dòng)表中的記錄做匹配,這樣就可以大大減少重復(fù)從磁盤上加載被驅(qū)動(dòng)表的代價(jià)了。所以MySQL的提出了一個(gè)join buffer的概念,join buffer就是執(zhí)行連接查詢前申請的一塊固定大小的內(nèi)存,先把若干條驅(qū)動(dòng)表結(jié)果集中的記錄裝在這個(gè)join buffer中,然后開始掃描被驅(qū)動(dòng)表,每一條被驅(qū)動(dòng)表的記錄一次性和join buffer中的多條驅(qū)動(dòng)表記錄做匹配,因?yàn)槠ヅ涞倪^程都是在內(nèi)存中完成的,所以這樣可以顯著減少被驅(qū)動(dòng)表的I/O代價(jià)。使用join buffer的過程如下圖所示:
最好的情況是join buffer足夠大,能容納驅(qū)動(dòng)表結(jié)果集中的所有記錄,這樣只需要訪問一次被驅(qū)動(dòng)表就可以完成連接操作了。MySQL把這種加入了join buffer的嵌套循環(huán)連接算法稱之為基于塊的嵌套連接(Block Nested-Loop Join)算法。
這個(gè)join buffer的大小是可以通過啟動(dòng)參數(shù)或者系統(tǒng)變量join_buffer_size進(jìn)?配置,默大小為262144字節(jié)(也就是256KB),最小可以設(shè)置為128字節(jié)。當(dāng)然,對于優(yōu)化被驅(qū)動(dòng)表的查詢來說,最好是為被驅(qū)動(dòng)表加上效率高的索引,如果實(shí)在不能使用索引,并且自己的機(jī)器的內(nèi)存也比較大可以嘗試調(diào)大join_buffer_size的值來對連接查詢進(jìn)行優(yōu)化。
mysql> show variables like 'join_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | join_buffer_size | 262144 | +------------------+--------+ 1 row in set (0.01 sec) mysql> set persist join_buffer_size=524288; Query OK, 0 rows affected (0.01 sec)
小提示:
不建議在系統(tǒng)級別對該值設(shè)置過大,一般可以設(shè)置512K以內(nèi),因?yàn)樽罱K解決方案還是要依靠索引來解決,當(dāng)然不排除有時(shí)候兩個(gè)表關(guān)聯(lián),的確是沒有索引可用
另外需要注意的是,驅(qū)動(dòng)表的記錄并不是所有列都會(huì)被放到j(luò)oin buffer中,只有查詢列表中的列和過濾條件中的列才會(huì)被放到j(luò)oin buffer中,所以再次提醒我們,最好不要把*作為查詢列表,只需要把我們關(guān)心的列放到查詢列表就好了,這樣還可以在join buffer中放置更多的記錄。
總結(jié)
今天我們學(xué)習(xí)了有關(guān)連接的知識(shí)。知道了連接的本質(zhì)、連接的過程、內(nèi)連接、外連接的使用方法及連接的原理。在原始NLJ算法的基礎(chǔ)上,MySQL又設(shè)計(jì)出了更優(yōu)BNL算法,被驅(qū)動(dòng)表我們可以通過添加關(guān)聯(lián)字段索引的方式來提高查詢效率,如果實(shí)在不能使用索引的情況,可以嘗試調(diào)大Join Buffer
的值(join_buffer_size
)。在使用內(nèi)連接時(shí),需要注意:
- on子句和where子句是等價(jià)的,所以內(nèi)連接中不要求強(qiáng)制寫明ON子句
- 對于內(nèi)連接來說,由于凡是不符合on子句或where子句中的條件的記錄都會(huì)被過濾掉,其實(shí)也就相當(dāng)于從兩表連接的笛卡爾積中把不符合過濾條件的記錄給踢出去,所以對于內(nèi)連接來說,驅(qū)動(dòng)表和被驅(qū)動(dòng)表是可以互換,并不會(huì)影響最后的查詢結(jié)果。
以上就是一文帶你了解MySQL之連接原理的詳細(xì)內(nèi)容,更多關(guān)于MySQL 連接原理的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
navicat不能創(chuàng)建函數(shù)解決方法分享
這篇文章主要介紹了navicat不能創(chuàng)建函數(shù)解決方法分享,小編覺得還是挺不錯(cuò)的,這里分享給大家,供需要的朋友參考。2017-10-10MySQL分支和循環(huán)結(jié)構(gòu)方式
在MySQL中,IF函數(shù)用于根據(jù)條件返回不同的值,類似于Java的三目運(yùn)算符,CASE語句則提供了兩種形式:簡單CASE函數(shù)和搜索CASE函數(shù),分別類似于Java中的switch-case結(jié)構(gòu)和多重if判斷,這些控制流函數(shù)在數(shù)據(jù)庫查詢和數(shù)據(jù)處理中非常有用,可以實(shí)現(xiàn)復(fù)雜的邏輯判斷2024-10-10MYSQL中 TYPE=MyISAM 錯(cuò)誤的解決方法
這篇文章主要介紹了MYSQL中 TYPE=MyISAM 錯(cuò)誤的解決方法,需要的朋友可以參考下2014-08-08mysql復(fù)制中臨時(shí)表的運(yùn)用技巧
數(shù)據(jù)庫損壞,也沒有備份。剛好二進(jìn)制日志全部保存完好,幸福了。導(dǎo)入二進(jìn)制日志到MYSQL2012-09-09Jaspersoft?Studio添加mysql數(shù)據(jù)庫配置步驟
這篇文章主要為大家介紹了Jaspersoft?Studio添加mysql數(shù)據(jù)庫配置的步驟過程詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步2022-02-02