搞數(shù)據(jù)庫一個避不開的概念就是Join，翻譯成中?就是連接。相信很多小伙伴初學(xué)連接的時候有些一臉懵，理解了連接的語義之后又可能不明白各個表中的記錄到底是怎么連起來的，以至于在使用的時候常常陷入下邊兩種誤區(qū)：

誤區(qū)一：業(yè)務(wù)至上，管他三七二十一，再復(fù)雜的查詢也用在一個連接語句中搞定誤區(qū)二：敬而遠之，慢查詢可能就是因為使用了連接導(dǎo)致的，以后再也不敢亂用了

所以本章就來學(xué)習(xí)連接的原理?？紤]到一部分小伙伴可能忘了連接是個啥或者壓根就不知道，為了節(jié)省他們百度或者看其他書的寶貴時間，我們先來介紹一下 MySQL 中支持的一些連接語法。

一、連接簡介

1.1 連接的本質(zhì)

為了學(xué)習(xí)，我們創(chuàng)建兩個簡單的表，并給它們插入一些數(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兩個表，這兩個表都有兩個列，一個是int類型的，一個是char(1)類型的，填充好數(shù)據(jù)的兩個表是這樣：

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ì)就是把各個連接表中的記錄都取出來依次匹配的組合加?結(jié)果集并返回給用戶。所以我們把demo9和demo10兩個表連接起來的過程如下圖所示：

這個過程看起來就是把demo9表的記錄和demo10的記錄連起來組成新的更大的記錄，所以這個查詢過程稱之為連接查詢。連接查詢的結(jié)果集中包含一個表中的每一條記錄與另一個表中的每一條記錄相互匹配的組合，像這樣的結(jié)果集就可以稱之為笛卡爾積。因為表demo9中有3條記錄，表demo10中也有3條記錄，所以這兩個表連接之后的笛卡爾積就有3×3=9條記錄。在MySQL中，連接查詢的語法也很隨意，只要在from語句后邊跟多個表名就好了，比如我們把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個100條記錄的表連接起來產(chǎn)生的笛卡爾積就有100×100×100=1000000條數(shù)據(jù)！所以在連接的時候過濾掉特定記錄組合是有必要的，在連接查詢中的過濾條件可以分成兩種：

• 涉及單表的條件

這種只設(shè)計單表的過濾條件我們之前都提到過千萬萬遍了，我們之前也一直稱為搜索條件，比如demo9.m1 > 1是只針對t1表的過濾條件，demo10.n2 < 'd’是只針對t2表
的過濾條件。

• 涉及兩表的條件

這種過濾條件我們之前沒說過，比如demo9.m1 = demo10.m2、demo9.n1 > demo10.n2等，這些條件中涉及到了兩個表，我們稍后會仔細分析這種過濾條件是如何使用的。

下邊我們就要看一下攜帶過濾條件的連接查詢的大致執(zhí)行過程了，比如說下邊這個查詢語句：

mysql> select * from demo9, demo10 where demo9.m1 > 1 and demo9.m1 = demo10.m2 and demo10.n2 < 'd';

在這個查詢中，我們指明了這三個過濾條件：

• demo9.m1 > 1
• demo9.m1 = demo10.m2
• demo10.n2 < ‘d’

那么這個查詢的大致執(zhí)行過程如下：

步驟一：

首先確定第一個需要查詢的表，這個表稱之為驅(qū)動表。怎樣在單表中執(zhí)行查詢語句我們在前一章都講過了，只需要選取代價最小的那種訪問方法去執(zhí)行單表查詢語句就好了（就是說從const、ref、ref_or_null、range、index、all這些執(zhí)行方法中選取代價最小的去執(zhí)行查詢）。此處假設(shè)使用demo9作為驅(qū)動表，那么就需要到demo9表中找滿足demo9.m1>1的記錄，因為表中的數(shù)據(jù)太少，我們也沒在表上建立二級索引，所以此處查詢demo9表的訪問方法就設(shè)定為all吧，也就是采用全表掃描的方式執(zhí)行單表查詢。關(guān)于如何提升連接查詢的性能我們之后再說，現(xiàn)在先把基本概念捋清楚。所以查詢過程就如下圖所示：

我們可以看到，demo9表中符合demo9.m1 > 1的記錄有兩條。

步驟二：

針對上一步驟中從驅(qū)動表產(chǎn)生的結(jié)果集中的每一條記錄，分別需要到demo10表中查找匹配的記錄，所謂匹配的記錄，指的是符合過濾條件的記錄。因為是根據(jù)demo9表中的記錄去找demo10表中的記錄，所以demo10表也可以被稱之為被驅(qū)動表。上一步驟從驅(qū)動表中得到了2條記錄，所以需要查詢2次demo10表。此時涉及兩個表的列的過濾條件demo9.m1=demo10.m2就派上用場了：

• 當(dāng)demo9.m1 = 2時，過濾條件demo9.m1 = demo10.m2就相當(dāng)于demo10.m2 = 2，所以此時demo10表相當(dāng)于有了demo10.m2 = 2、demo10.n2 < 'd’這兩個過濾條件，然后到demo10表中執(zhí)行單表查詢
• 當(dāng)demo9.m1 = 3時，過濾條件demo9.m1 = demo10.m2就相當(dāng)于demo10.m2 = 3，所以此時demo10表相當(dāng)于有了demo10.m2 = 3、demo10.n2<'d’這兩個過濾條件，然后到demo10表中執(zhí)行單表查詢

所以整個連接查詢的執(zhí)行過程就如下圖所示：

也就是說整個連接查詢最后的結(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)

從上邊兩個步驟可以看出來，我們上邊的這個兩表連接查詢共需要查詢1次demo9表，2次demo10表。當(dāng)然這是在特定的過濾條件下的結(jié)果，如果我們把demo9.m1 > 1這個條件去掉，那么從demo9表中查出的記錄就有3條，就需要查詢3次demo10表了。也就是說在兩表連接查詢中，驅(qū)動表只需要訪問?次，被驅(qū)動表可能被訪問多次。

1.3 內(nèi)連接和外連接

為了更好的學(xué)習(xí)后邊的內(nèi)容，我們先創(chuàng)建兩個有現(xiàn)實意義的表：

mysql> create table student (
    number int not null auto_increment comment '學(xué)號',
    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é)號',
    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,'李四','計算機科學(xué)與工程'),(3,'王五','計算機科學(xué)與工程');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into score values(1,'MySQL是怎樣運行的',78),(1,'MySQL實戰(zhàn)45講',88),(2,'MySQL是怎樣運行的',78),(2,'MySQL實戰(zhàn)45講',100);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

我們新建了一個學(xué)?信息表，一個學(xué)生成績表，然后我們向上述兩個表中插入一些數(shù)據(jù)，插入后兩表中的數(shù)據(jù)如下：

mysql> select * from student;
+--------+--------+--------------------------+
| number | name   | major                    |
+--------+--------+--------------------------+
|      1 | 張三   | 軟件學(xué)院                 |
|      2 | 李四   | 計算機科學(xué)與工程         |
|      3 | 王五   | 計算機科學(xué)與工程         |
+--------+--------+--------------------------+
3 rows in set (0.00 sec)

mysql> select * from score;
+--------+-------------------------+-------+
| number | subject                 | score |
+--------+-------------------------+-------+
|      1 | MySQL是怎樣運行的       |    78 |
|      1 | MySQL實戰(zhàn)45講           |    88 |
|      2 | MySQL是怎樣運行的       |    98 |
|      2 | MySQL實戰(zhàn)45講           |   100 |
+--------+-------------------------+-------+
4 rows in set (0.00 sec)

現(xiàn)在我們想把每個學(xué)生的考試成績都查詢出來就需要進行兩表連接了（因為score中沒有姓名信息，所以不能單純只查詢score表）。連接過程就是從student表中取出記錄，在score表中查找number相同的成績記錄，所以過濾條件就是student.number =socre.number，整個查詢語句就是這樣：

mysql> select * from student,score where student.number=score.number;
+--------+--------+--------------------------+--------+-------------------------+-------+
| number | name   | major                    | number | subject                 | score |
+--------+--------+--------------------------+--------+-------------------------+-------+
|      1 | 張三   | 軟件學(xué)院                 |      1 | MySQL是怎樣運行的       |    78 |
|      1 | 張三   | 軟件學(xué)院                 |      1 | MySQL實戰(zhàn)45講           |    88 |
|      2 | 李四   | 計算機科學(xué)與工程         |      2 | MySQL是怎樣運行的       |    98 |
|      2 | 李四   | 計算機科學(xué)與工程         |      2 | MySQL實戰(zhàn)45講           |   100 |
+--------+--------+--------------------------+--------+-------------------------+-------+
4 rows in set (0.00 sec)

字段有點多，我們可以少查詢幾個字段：

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是怎樣運行的       |    78 |
|      1 | 張三   | MySQL實戰(zhàn)45講           |    88 |
|      2 | 李四   | MySQL是怎樣運行的       |    98 |
|      2 | 李四   | MySQL實戰(zhàn)45講           |   100 |
+--------+--------+-------------------------+-------+
4 rows in set (0.00 sec)

從上述查詢結(jié)果中我們可以看到，各個同學(xué)對應(yīng)的各科成績就都被查出來了，可是有個問題，王五同學(xué)，也就是學(xué)號為3的同學(xué)因為某些原因沒有參加考試，所以在score表中沒有對應(yīng)的成績記錄。那如果老師想查看所有同學(xué)的考試成績，即使是缺考的同學(xué)也應(yīng)該展示出來，但是到目前為止我們介紹的連接查詢是無法完成這樣的需求的。我們稍微思考一下這個需求，其本質(zhì)是想：驅(qū)動表中的記錄即使在被驅(qū)動表中沒有匹配的記錄，也仍然需要加入到結(jié)果集。為了解決這個問題，就有了內(nèi)連接和外連接的概念：

• 對于內(nèi)連接的兩個表，驅(qū)動表中的記錄在被驅(qū)動表中找不到匹配的記錄，該記錄不會加入到最后的結(jié)果集，我們上邊提到的連接都是所謂的內(nèi)連接
• 對于外連接的兩個表，驅(qū)動表中的記錄即使在被驅(qū)動表中沒有匹配的記錄，也仍然需要加入到結(jié)果集

在MySQL中，根據(jù)選取驅(qū)動表的不同，外連接仍然可以細分為2種：

• 左外連接：選取左側(cè)的表為驅(qū)動表
• 右外連接：選取右側(cè)的表為驅(qū)動表

可是這樣仍然存在問題，即使對于外連接來說，有時候我們也并不想把驅(qū)動表的全部記錄都加入到最后的結(jié)果集。這就犯難了，有時候匹配失敗要加入結(jié)果集，有時候又不要加入結(jié)果集，這咋辦，把過濾條件分為兩種不就解決了這個問題了么，所以放在不同地方的過濾條件是有不同語義的：

• where子句中的過濾條件：where子句中的過濾條件就是我們平時見的那種，不論是內(nèi)連接還是外連接，凡是不符合where子句中的過濾條件的記錄都不會被加入最后的結(jié)果集。
• ON子句中的過濾條件：對于外連接的驅(qū)動表的記錄來說，如果無法在被驅(qū)動表中找到匹配ON子句中的過濾條件的記錄，那么該記錄仍然會被加入到結(jié)果集中，對應(yīng)的被驅(qū)動表記錄的各個字段使用NULL值填充。

需要注意的是，這個ON子句是專門為外連接驅(qū)動表中的記錄在被驅(qū)動表找不到匹配記錄時應(yīng)不應(yīng)該把該記錄加入結(jié)果集這個場景下提出的，所以如果把ON子句放到內(nèi)連接中，MySQL會把它和where子句一樣對待，也就是說：內(nèi)連接中的where子句和ON子句是等價的。

一般情況下，我們都把只涉及單表的過濾條件放到where子句中，把涉及兩表的過濾條件都放到ON子句中，我們也一般把放到ON子句中的過濾條件也稱之為連接條件。

小提示：
左外連接和右外連接簡稱左連接和右連接。

1.4 左外連接

左外連接的語法還是挺簡單的，比如我們要把demo9和demo10兩個表進行左外連接，可以這么寫：

select * from demo9 left [outer] join demo10 on 連接條件 [where 普通過濾條件]

其中中括號里的outer單詞是可以省略的。對于left join類型的連接來說，我們把放在左邊的表稱之為外表或者驅(qū)動表，右邊的表稱之為內(nèi)表或者被驅(qū)動表。所以上述例子中demo9就是外表或者驅(qū)動表，demo10就是內(nèi)表或者被驅(qū)動表。需要注意的是，對于左外連接和右外連接來說，必須使用on子句來指出連接條件。了解了左外連接的基本語法之后，再次回到我們上邊那個現(xiàn)實問題中來，看看怎樣寫查詢語句才能把所有的學(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是怎樣運行的       |    78 |
|      1 | 張三   | MySQL實戰(zhàn)45講           |    88 |
|      2 | 李四   | MySQL是怎樣運行的       |    98 |
|      2 | 李四   | MySQL實戰(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ū)動表是右邊的表，被驅(qū)動表是左邊的表，具體就不嘮叨了。

1.6 內(nèi)連接

內(nèi)連接和外連接的根本區(qū)別就是在驅(qū)動表中的記錄不符合on子句中的連接條件時不會把該記錄加入到最后的結(jié)果集，我們最開始嘮叨的那些連接查詢的類型都是內(nèi)連接。不過之前僅僅提到了一種最簡單的內(nèi)連接語法，就是直接把需要連接的多個表都放到from子句后邊。其實針對內(nèi)連接，mysql提供了好多不同的語法，我們以demo9和demo10表為例瞅瞅：

select * from demo9 [inner|cross] join demo10 [on 連接條件] [where 普通過濾條件];

也就是說在mysql中，下邊這?種內(nèi)連接的寫法都是等價的：

select * from demo9 join demo10;
select * from demo9 inner join demo10;
select * from demo9 cross join demo10;

上邊的這些寫法和直接把需要連接的表名放到from語句之后，用逗號,分隔開的寫法是等價的：

select * from demo9,demo10;

現(xiàn)在我們雖然介紹了很多種內(nèi)連接的書寫方式，不過熟悉一種就好了，這里我們推薦inner join的形式書寫內(nèi)連接（因為inner join語義很明確嘛，可以和left join和right join很輕松的區(qū)分開）。這里需要注意的是，由于在內(nèi)連接中on子句和where子句是等價的，所以內(nèi)連接中不要求強制寫明on子句。

我們前邊說過，連接的本質(zhì)就是把各個連接表中的記錄都取出來依次匹配的組合加入結(jié)果集并返回給用戶。不論哪個表作為驅(qū)動表，兩表連接產(chǎn)生的笛卡爾積肯定是一樣的。而對于內(nèi)連接來說，由于凡是不符合on子句或where子句中的條件的記錄都會被過濾掉，其實也就相當(dāng)于從兩表連接的笛卡爾積中把不符合過濾條件的記錄給踢出去，所以對于內(nèi)連接來說，驅(qū)動表和被驅(qū)動表是可以互換的，并不會影響最后的查詢結(jié)果。但是對于外連接來說，由于驅(qū)動表中的記錄即使在被驅(qū)動表中找不到符合on子句連接條件的記錄，所以此時驅(qū)動表和被驅(qū)動表的關(guān)系就很重要了，也就是說左外連接和右外連接的驅(qū)動表和被驅(qū)動表不能輕易互換。

小結(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)連接、外連接這些概念的記憶，這些基本概念是為了真正進入本章主題做的鋪墊。真正的重點是MySQL采用了什么樣的算法來進行表與表之的連接，了解了這個之后，大家才能明白為啥有的連接查詢運行的快如閃電，有的卻慢如蝸牛。

2.1 嵌套循環(huán)連接（Nested-Loop Join）

我們前邊說過，對于兩表連接來說，驅(qū)動表只會被訪問一遍，但被驅(qū)動表卻要被訪問到好多遍，具體訪問幾遍取決于對驅(qū)動表執(zhí)行單表查詢后的結(jié)果集中的記錄條數(shù)。對于內(nèi)連接來說，選取哪個表為驅(qū)動表都沒關(guān)系，而外連接的驅(qū)動表是固定的，也就是說左（外）連接的驅(qū)動表就是左邊的那個表，右（外）連接的驅(qū)動表就是右邊的那個表。我們上邊已經(jīng)大致介紹過demo9表和demo10表執(zhí)行內(nèi)連接查詢的大致過程，我們溫習(xí)一下：

• 選取驅(qū)動表，使用與驅(qū)動表相關(guān)的過濾條件，選取代價最低的單表訪問方法來執(zhí)行對驅(qū)動表的單表查詢。
• 對上述步驟中查詢驅(qū)動表得到的結(jié)果集中每一條記錄，都分別到被驅(qū)動表中查找匹配的記錄。

通過的兩表連接過程如下圖所示：

如果有3個表進行連接的話，那么步驟2中得到的結(jié)果集就像是新的驅(qū)動表，然后第三個表就成為了被驅(qū)動表，重復(fù)上邊過程，也就是步驟2中得到的結(jié)果集中的每一條記錄都需要到demo11表中找一找有沒有匹配的記錄，用偽代碼表示一下這個過程就是這樣：

for each row in demo9 {   #此處表示遍歷滿足對demo9單表查詢結(jié)果集中的每一條記錄
    for each row in demo10 {   #此處表示對于某條demo9表的記錄來說，遍歷滿足對demo10單表查詢結(jié)果集中的每一條記
        for each row in demo11 {   #此處表示對于某條demo9和demo10表的記錄組合來說，對demo11表進行單表查詢
            if row satisfies join conditions, send to client
        }
    }
}

這個過程就像是一個嵌套的循環(huán)，所以這種驅(qū)動表只訪問一次，但被驅(qū)動表卻可能被多次訪問，訪問次數(shù)取決于對驅(qū)動表執(zhí)行單表查詢后的結(jié)果中的記錄條數(shù)的連接執(zhí)行方式稱之為嵌套循環(huán)連接（Nested-Loop Join），這是最簡單，也是最笨拙的一種連接查詢算法。

2.2 使用索引加快連接速度

我們知道在嵌套循環(huán)連接的步驟2中可能需要訪問多次被驅(qū)動表，如果訪問被驅(qū)動表的方式都是全表掃描的話，那得要掃描好多次呀～但是別忘了，查詢demo10表其實就相當(dāng)于一次單表掃描，我們可以利用索引來加快查詢速度?；仡櫼幌伦铋_始介紹的demo9表和demo10表進行內(nèi)連接的例子：

mysql> select * from demo9, demo10 where demo9.m1 > 1 and demo9.m1 = demo10.m2 and demo10.n2 < 'd';

我們使用的其實是嵌套循壞連接算法執(zhí)行的連接查詢，再把上邊那個查詢執(zhí)行過程表拉下來給大家看一下：

查詢驅(qū)動表demo9后的結(jié)果集有兩條記錄，嵌套循壞連接算法需要對被驅(qū)動表查詢兩次：

第一次：

當(dāng)demo9.m1 = 2時，去查詢一遍demo10表，對demo10查詢的語句相當(dāng)于：

select * from demo10 where demo10.m2 = 2 and demo10.m2 < 'd';

第二次：

當(dāng)demo9.m1 =3時，去查詢一遍demo10表，對demo10查詢的語句相當(dāng)于：

select * from demo10 where demo10.m2 = 3 and demo10.m2 < 'd';

可以看到，原來的demo9.m1 = demo10.m2這個涉及兩個表的過濾條件在針對demo10表做查詢時關(guān)于demo9表的條件就已經(jīng)確定了，所以我們只需要單單優(yōu)化對demo10表的查詢了，上述兩個對demo10表的查詢語句中利用到的列是m2和n2列，我們可以：

• 在m2列上建立索引，因為對m2列的條件是等值查找，比如demo10.m2 = 2、demo10.m2 = 3等，所以可能使用到ref的訪問方法，假設(shè)使用ref的訪問方法去執(zhí)行對demo10表的查詢的話，需要回表之后再判斷demo10.n2 < d這個條件是否成立。
• 這里有一個比較特殊的情況，就是假設(shè)m2列是demo10表的主鍵或者唯一二級索引列，那么使用demo10.m2 = 常數(shù)值這樣的條件從demo10表中查找記錄的過程的代價就是常數(shù)級別的。我們知道在單表中使用主鍵值或者唯一二級索引列的值進行等值查找的方式稱之為const，MySQL把在連接查詢中對被驅(qū)動表使用主鍵值或者唯一二級索引列的值進行等值查找的查詢執(zhí)行方式稱之為：eq_ref。
• 在n2列上建立索引，涉及到的條件是demo10.n2 < ‘d’，可能用到range的訪問方法，假設(shè)使用range的訪問方法對demo10表的查詢的話，需要回表之后再判斷在m2

列上的條件是否成立。

假設(shè)m2和n2列上都存在索引的話，那么就需要從這兩個里邊挑一個代價更低的去執(zhí)行對demo10表的查詢。當(dāng)然，建立了索引不一定使用索引，只有在二級索引 +回表的代價比全表掃描的代價更低時才會使用索引。

另外，有時候連接查詢的查詢列表和過濾條件中可能只涉及被驅(qū)動表的部分列，而這些列都是某個索引的一部分，這種情況下即使不能使用eq_ref、ref、ref_or_null或者range這些訪問方法執(zhí)行對被驅(qū)動表的查詢的話，也可以使用索引掃描，也就是index的訪問方法來查詢被驅(qū)動表。所以我們建議在真實工作中最好不要使用*作為查詢列表，最好把真實用到的列作為查詢列表。

2.3 基于塊的嵌套循環(huán)連接（Block Nested-Loop Join）

掃描一個表的過程其實是先把這個表從磁盤上加載到內(nèi)存中，然后從內(nèi)存中比較匹配條件是否滿足?，F(xiàn)實生活中的表可不像demo9、demo11這種只有3條記錄，成千上萬條記錄都是少的，幾百萬、幾千萬甚幾億條記錄的表到處都是。內(nèi)存里可能并不能完全存放的下表中所有的記錄，所以在掃描表前邊記錄的時候后邊的記錄可能還在磁盤上，等掃描到后邊記錄的時候可能內(nèi)存不足，所以需要把前邊的記錄從內(nèi)存中釋放掉。我們前邊說過，采用嵌套循環(huán)連接算法的兩表連接過程中，被驅(qū)動表可是要被訪問好多次的，如果這個被驅(qū)動表中的數(shù)據(jù)特別多而且不能使用索引進行訪問，那就相當(dāng)于要從磁盤上讀好多次這個表，這個I/O代價就非常大了，所以我們得想辦法：盡量減少訪問被驅(qū)動表的次數(shù)。

當(dāng)被驅(qū)動表中的數(shù)據(jù)?常多時，每次訪問被驅(qū)動表，被驅(qū)動表的記錄會被加載到內(nèi)存中，在內(nèi)存中的每一條記錄只會和驅(qū)動表結(jié)果集的一條記錄做匹配，之后就會被從內(nèi)存中清除掉。然后再從驅(qū)動表結(jié)果集中拿出另一條記錄，再一次把被驅(qū)動表的記錄加載到內(nèi)存中一遍，周而復(fù)始，驅(qū)動表結(jié)果集中有多少條記錄，就得把被驅(qū)動表從磁盤上加載到內(nèi)存中多少次。所以我們可不可以在把被驅(qū)動表的記錄加載到內(nèi)存的時候，一次性和多條驅(qū)動表中的記錄做匹配，這樣就可以大大減少重復(fù)從磁盤上加載被驅(qū)動表的代價了。所以MySQL的提出了一個join buffer的概念，join buffer就是執(zhí)行連接查詢前申請的一塊固定大小的內(nèi)存，先把若干條驅(qū)動表結(jié)果集中的記錄裝在這個join buffer中，然后開始掃描被驅(qū)動表，每一條被驅(qū)動表的記錄一次性和join buffer中的多條驅(qū)動表記錄做匹配，因為匹配的過程都是在內(nèi)存中完成的，所以這樣可以顯著減少被驅(qū)動表的I/O代價。使用join buffer的過程如下圖所示：

最好的情況是join buffer足夠大，能容納驅(qū)動表結(jié)果集中的所有記錄，這樣只需要訪問一次被驅(qū)動表就可以完成連接操作了。MySQL把這種加入了join buffer的嵌套循環(huán)連接算法稱之為基于塊的嵌套連接（Block Nested-Loop Join）算法。

這個join buffer的大小是可以通過啟動參數(shù)或者系統(tǒng)變量join_buffer_size進?配置，默大小為262144字節(jié)（也就是256KB），最小可以設(shè)置為128字節(jié)。當(dāng)然，對于優(yōu)化被驅(qū)動表的查詢來說，最好是為被驅(qū)動表加上效率高的索引，如果實在不能使用索引，并且自己的機器的內(nèi)存也比較大可以嘗試調(diào)大join_buffer_size的值來對連接查詢進行優(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)，因為最終解決方案還是要依靠索引來解決，當(dāng)然不排除有時候兩個表關(guān)聯(lián)，的確是沒有索引可用

另外需要注意的是，驅(qū)動表的記錄并不是所有列都會被放到j(luò)oin buffer中，只有查詢列表中的列和過濾條件中的列才會被放到j(luò)oin buffer中，所以再次提醒我們，最好不要把*作為查詢列表，只需要把我們關(guān)心的列放到查詢列表就好了，這樣還可以在join buffer中放置更多的記錄。

總結(jié)

今天我們學(xué)習(xí)了有關(guān)連接的知識。知道了連接的本質(zhì)、連接的過程、內(nèi)連接、外連接的使用方法及連接的原理。在原始NLJ算法的基礎(chǔ)上，MySQL又設(shè)計出了更優(yōu)BNL算法，被驅(qū)動表我們可以通過添加關(guān)聯(lián)字段索引的方式來提高查詢效率，如果實在不能使用索引的情況，可以嘗試調(diào)大Join Buffer的值（join_buffer_size）。在使用內(nèi)連接時，需要注意：

• on子句和where子句是等價的，所以內(nèi)連接中不要求強制寫明ON子句
• 對于內(nèi)連接來說，由于凡是不符合on子句或where子句中的條件的記錄都會被過濾掉，其實也就相當(dāng)于從兩表連接的笛卡爾積中把不符合過濾條件的記錄給踢出去，所以對于內(nèi)連接來說，驅(qū)動表和被驅(qū)動表是可以互換，并不會影響最后的查詢結(jié)果。

以上就是一文帶你了解MySQL之連接原理的詳細內(nèi)容，更多關(guān)于MySQL 連接原理的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論