連接的原理（待修改）

搞數據庫?個避不開的概念就是Join，翻譯成中?就是連接。

相信很多?伙伴在初學連接的時候有些?臉懵逼，理解了連接的語義之后?可能不明?各個表中的記錄到底是怎么連起來的，以?于在使?的時候常常陷?下邊兩種誤區：

誤區?：業務?上，管他三七???，再復雜的查詢也?在?個連接語句中搞定。

誤區?：敬?遠之，上次 DBA 那給報過來的慢查詢就是因為使?了連接導致的，以后再也不敢?了。

連接簡介連接的本質為了故事的順利發展，我們先建?兩個簡單的表并給它們填充?點數據：

mysql> CREATE TABLE t1 (m1 int, n1 char(1));

Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t2 (m2 int, n2 char(1));

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');

Query OK, 3 rows affected (0.00 sec) Records: 3? Duplicates: 0? Warnings: 0

mysql> INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

Query OK, 3 rows affected (0.00 sec) Records: 3? Duplicates: 0? Warnings: 0

我們成功建?了t1、t2兩個表，這兩個表都有兩個列，?個是INT類型的，?個是CHAR(1)類型的，填充好數據的兩個表?這樣：

mysql> SELECT * FROM t1;

+------+------+

| m1?? | n1?? |

+------+------+

|??? 1 | a??? |

|??? 2 | b??? |

|??? 3 | c??? |

+------+------+

3 rows inset (0.00 sec)

mysql> SELECT * FROM t2;

+------+------+

| m2?? | n2?? |

+------+------+

|??? 2 | b??? |

|??? 3 | c??? |

|??? 4 | d??? |

+------+------+

3 rows inset (0.00 sec)

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

這個過程看起來就是把t1表的記錄和t2的記錄連起來組成新的更?的記錄，所以這個查詢過程稱之為連接查詢。連接查詢的結果集中包含?個表中的每?條記錄與另?個表中的每?條記錄相互匹配的組合，像這樣的結果集就可以稱之為笛卡爾積。因為表t1中有3條記錄，表t2中也有3條記錄，所以這兩個表連接之后的笛卡爾積就有3×3=9?記錄。在MySQL中，連接查詢的語法也很隨意，只要在FROM語句后邊跟多個表名就好了，?如我們把t1表和t2表連接起來的查詢語句可以寫成這樣：

連接過程簡介

如果我們樂意，我們可以連接任意數量張表，但是如果沒有任何限制條件的話，這些表連接起來產?的笛卡爾積可能是?常巨?的。??說3個100?記錄的表連接起來產?的笛卡爾積就有100×100×100=1000000?數據！所以在連接的時候過濾掉特定記錄組合是有必要的，在連接查詢中的過濾條件可以分成兩種：

涉及單表的條件

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

涉及兩表的條件

這種過濾條件我們之前沒?過，?如t1.m1 = t2.m2、t1.n1 > t2.n2等，這些條件中涉及到了兩個表，我們稍后會仔細分析這種過濾條件是如何使?的哈。下邊我們就要看?下攜帶過濾條件的連接查詢的?致執?過程了，??說下邊這個查詢語句

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

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

t1.m1 > 1

t1.m1 = t2.m2

t2.n2 < 'd'

那么這個連接查詢的?致執?過程如下：

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

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

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

當t1.m1 = 2時，過濾條件t1.m1 = t2.m2就相當于t2.m2 = 2，所以此時t2表相當于有了t2.m2 = 2、t2.n2 < 'd'這兩個過濾條件，然后到t2表中執?單表查詢。

當t1.m1 = 3時，過濾條件t1.m1 = t2.m2就相當于t2.m2 = 3，所以此時t2表相當于有了t2.m2 = 3、t2.n2 < 'd'這兩個過濾條件，然后到t2表中執?單表查詢。

所以整個連接查詢的執?過程就如下圖所示：?

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

內連接和外連接

為了?家更好理解后邊內容，我們先創建兩個有現實意義的表，

CREATE TABLE student ( ???

number INT NOT NULL AUTO_INCREMENT COMMENT '學號', ???

name VARCHAR(5) COMMENT '姓名', ???

major VARCHAR(30) COMMENT '專業', ???

PRIMARY KEY (number) ) Engine=InnoDB CHARSET=utf8 COMMENT '學?信息表';

CREATE TABLE score ( ???

number INT COMMENT '學號', ???

subject VARCHAR(30) COMMENT '科?', ???

score TINYINT COMMENT '成績', ???

PRIMARY KEY (number, score) ) Engine=InnoDB CHARSET=utf8 COMMENT '學?成績表';

我們新建了?個學?信息表，?個學?成績表，然后我們向上述兩個表中插??些數據，為節省篇幅，具體插?過程就不嘮叨了，插?后兩表中的數據如下：

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

mysql> SELECT * FROM student, score WHERE student.number = score.number;

?字段有點多哦，我們少查詢?個字段：

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

對于內連接的兩個表，驅動表中的記錄在被驅動表中找不到匹配的記錄，該記錄不會加?到最后的結果集，我們上邊提到的連接都是所謂的內連接。

對于外連接的兩個表，驅動表中的記錄即使在被驅動表中沒有匹配的記錄，也仍然需要加?到結果集。

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

左外連接

選取左側的表為驅動表。

右外連接

選取右側的表為驅動表。

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

WHERE?句中的過濾條件

WHERE?句中的過濾條件就是我們平時?的那種，不論是內連接還是外連接，凡是不符合WHERE?句中的過濾條件的記錄都不會被加?最后的結果集。

ON?句中的過濾條件

對于外連接的驅動表的記錄來說，如果?法在被驅動表中找到匹配ON?句中的過濾條件的記錄，那么該記錄仍然會被加?到結果集中，對應的被驅動表記錄的各個字段使?NULL值填充。