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

一文帶你了解MySQL之連接原理

 更新時(shí)間:2023年05月21日 14:20:00   作者:multis  
搞數(shù)據(jù)庫一個(gè)避不開的概念就是Join,翻譯成中?就是連接,相信很多小伙伴初學(xué)連接的時(shí)候有些一臉懵,理解了連接的語義之后又可能不明白各個(gè)表中的記錄到底是怎么連起來的,所以本章就來學(xué)習(xí)連接的原理,需要的朋友可以參考下

搞數(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)文章

  • 詳解MySQL性能優(yōu)化(一)

    詳解MySQL性能優(yōu)化(一)

    本文對MySQL性能優(yōu)化進(jìn)行了詳細(xì)的總結(jié)與介紹,需要的朋友可以參考下
    2015-08-08
  • MySQL 使用索引掃描進(jìn)行排序

    MySQL 使用索引掃描進(jìn)行排序

    mysql可以使用同一個(gè)索引既滿足排序,又用于查找行,因此,如果可能,設(shè)計(jì)索引時(shí)應(yīng)該盡可能地同時(shí)滿足這兩種任務(wù),這樣是最好的。本文將介紹如何利用索引來進(jìn)行排序
    2021-06-06
  • 使用sql語句insert之前判斷是否已存在記錄

    使用sql語句insert之前判斷是否已存在記錄

    這篇文章主要介紹了使用sql語句insert之前判斷是否已存在記錄,具有很好的參考價(jià)值,希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2021-12-12
  • MySQL字符集亂碼及解決方案分享

    MySQL字符集亂碼及解決方案分享

    這篇文章主要給大家介紹了關(guān)于MySQL字符集亂碼及解決方案的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧
    2019-04-04
  • navicat不能創(chuàng)建函數(shù)解決方法分享

    navicat不能創(chuàng)建函數(shù)解決方法分享

    這篇文章主要介紹了navicat不能創(chuàng)建函數(shù)解決方法分享,小編覺得還是挺不錯(cuò)的,這里分享給大家,供需要的朋友參考。
    2017-10-10
  • MySQL分支和循環(huán)結(jié)構(gòu)方式

    MySQL分支和循環(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-10
  • MYSQL中 TYPE=MyISAM 錯(cuò)誤的解決方法

    MYSQL中 TYPE=MyISAM 錯(cuò)誤的解決方法

    這篇文章主要介紹了MYSQL中 TYPE=MyISAM 錯(cuò)誤的解決方法,需要的朋友可以參考下
    2014-08-08
  • mysql復(fù)制中臨時(shí)表的運(yùn)用技巧

    mysql復(fù)制中臨時(shí)表的運(yùn)用技巧

    數(shù)據(jù)庫損壞,也沒有備份。剛好二進(jìn)制日志全部保存完好,幸福了。導(dǎo)入二進(jìn)制日志到MYSQL
    2012-09-09
  • Jaspersoft?Studio添加mysql數(shù)據(jù)庫配置步驟

    Jaspersoft?Studio添加mysql數(shù)據(jù)庫配置步驟

    這篇文章主要為大家介紹了Jaspersoft?Studio添加mysql數(shù)據(jù)庫配置的步驟過程詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步
    2022-02-02
  • Mysql中常用的幾種join連接方式總結(jié)

    Mysql中常用的幾種join連接方式總結(jié)

    join語句是面試中經(jīng)常會(huì)讓你現(xiàn)場寫出來的語句,下面這篇文章主要給大家介紹了關(guān)于Mysql中常用的幾種join連接方式,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2022-05-05

最新評論