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

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

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

一、連接簡介

1.1 連接的本質

為了學習，我們創(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)

連接的本質就是把各個連接表中的記錄都取出來依次匹配的組合加?結果集并返回給用戶。所以我們把demo9和demo10兩個表連接起來的過程如下圖所示：

這個過程看起來就是把demo9表的記錄和demo10的記錄連起來組成新的更大的記錄，所以這個查詢過程稱之為連接查詢。連接查詢的結果集中包含一個表中的每一條記錄與另一個表中的每一條記錄相互匹配的組合，像這樣的結果集就可以稱之為笛卡爾積。因為表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ù)量張表，但是如果沒有任何限制條件的話，這些表連接起來產生的笛卡爾積可能是非常巨大的。比如說3個100條記錄的表連接起來產生的笛卡爾積就有100×100×100=1000000條數(shù)據(jù)！所以在連接的時候過濾掉特定記錄組合是有必要的，在連接查詢中的過濾條件可以分成兩種：

• 涉及單表的條件

這種只設計單表的過濾條件我們之前都提到過千萬萬遍了，我們之前也一直稱為搜索條件，比如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í)行過程如下：

步驟一：

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

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

步驟二：

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

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

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

也就是說整個連接查詢最后的結果只有兩條符合過濾條件的記錄：

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表。當然這是在特定的過濾條件下的結果，如果我們把demo9.m1 > 1這個條件去掉，那么從demo9表中查出的記錄就有3條，就需要查詢3次demo10表了。也就是說在兩表連接查詢中，驅動表只需要訪問?次，被驅動表可能被訪問多次。

1.3 內連接和外連接

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

mysql> create table student (
    number int not null auto_increment comment '學號',
    name varchar(5) comment '姓名',
    major varchar(30) comment '專業(yè)',
    primary key (number)
) comment '學生信息表';
Query OK, 0 rows affected (0.02 sec)

mysql> create table score (
    number int comment '學號',
    subject varchar(30) comment '科目',
    score tinyint comment '成績',
    primary key (number, score)
) comment '學生成績表';
Query OK, 0 rows affected (0.02 sec)

mysql> insert into student values(1,'張三','軟件學院'),(2,'李四','計算機科學與工程'),(3,'王五','計算機科學與工程');
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

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

mysql> select * from student;
+--------+--------+--------------------------+
| number | name   | major                    |
+--------+--------+--------------------------+
|      1 | 張三   | 軟件學院                 |
|      2 | 李四   | 計算機科學與工程         |
|      3 | 王五   | 計算機科學與工程         |
+--------+--------+--------------------------+
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)在我們想把每個學生的考試成績都查詢出來就需要進行兩表連接了（因為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 | 張三   | 軟件學院                 |      1 | MySQL是怎樣運行的       |    78 |
|      1 | 張三   | 軟件學院                 |      1 | MySQL實戰(zhàn)45講           |    88 |
|      2 | 李四   | 計算機科學與工程         |      2 | MySQL是怎樣運行的       |    98 |
|      2 | 李四   | 計算機科學與工程         |      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)

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

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

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

• 左外連接：選取左側的表為驅動表
• 右外連接：選取右側的表為驅動表

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

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

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

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

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

1.4 左外連接

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

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

其中中括號里的outer單詞是可以省略的。對于left join類型的連接來說，我們把放在左邊的表稱之為外表或者驅動表，右邊的表稱之為內表或者被驅動表。所以上述例子中demo9就是外表或者驅動表，demo10就是內表或者被驅動表。需要注意的是，對于左外連接和右外連接來說，必須使用on子句來指出連接條件。了解了左外連接的基本語法之后，再次回到我們上邊那個現(xiàn)實問題中來，看看怎樣寫查詢語句才能把所有的學生的成績信息都查詢出來，即使是缺考的考生也應該被放到結果集中：

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)

從結果集中可以看出來，雖然王五并沒有對應的成績記錄，但是由于采用的是連接類型為左外連接，所以仍然把她放到了結果集中，只不過在對應的成績記錄的各列使用null值填充而已。

1.5 右外連接

右外連接和左外連接的原理是一樣一樣的，語法也只是把left換成right而已：

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

只不過驅動表是右邊的表，被驅動表是左邊的表，具體就不嘮叨了。

1.6 內連接

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

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

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

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

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

select * from demo9,demo10;

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

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

小結

上邊說了很多，給大家的感覺不是很直觀，我們直接把表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)

二、連接的原理

上邊的介紹都只是為了喚醒大家對連接、內連接、外連接這些概念的記憶，這些基本概念是為了真正進入本章主題做的鋪墊。真正的重點是MySQL采用了什么樣的算法來進行表與表之的連接，了解了這個之后，大家才能明白為啥有的連接查詢運行的快如閃電，有的卻慢如蝸牛。

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

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

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

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

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

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

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

2.2 使用索引加快連接速度

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

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

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

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

第一次：

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

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

第二次：

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

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

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

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

列上的條件是否成立。

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

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

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

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

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

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

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

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

總結

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

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

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

最新評論