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

最新評論