Mysql表連接的誤區與原理是什么

這篇文章主要講解了“Mysql表連接的誤區與原理是什么”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“Mysql表連接的誤區與原理是什么”吧！

前言

搞后端的肯定要經常接觸到數據庫,搞數據庫一個避免不了的地方就是 join, join的語法很簡單,但是在使用時常常陷入一下兩種誤區:

• 誤區一: 業務至上,管他三七二十一,再復雜的查詢一個連接語句搞定

• 誤區二: 敬而遠之,上次寫的慢查詢sql就是使用了join導致的,以后再也不敢用了

先來舉個栗子:

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

現在我們對這張表進行連接:

mysql> SELECT * FROM t1, t2;
+------+------+------+------+
| 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)

這個過程看起來就是把t1表的記錄和t2的記錄連起來組成新的更大的記錄，所以這個查詢過程稱之為連接查詢。連接查詢的結果集中包含一個表中的每一條記錄與另一個表中的每一條記錄相互匹配的組合，像這樣的結果集就可以稱之為笛卡爾積。因為表t1中有3條記錄，表t2中也有3條記錄，所以這兩個表連接之后的笛卡爾積就有3×3=9行記錄。

連接過程簡介

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

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

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'

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

• 首先確定第一個需要查詢的表，這個表稱之為驅動表。只需要選取代價最小的那種訪問方法去執行單表查詢語句就好了。此處假設使用t1作為驅動表，那么就需要到t1表中找滿足t1.m1 > 1的記錄，因為表中的數據太少，我們也沒在表上建立二級索引，所以此處查詢t1表的訪問方法就是全表掃描。

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

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

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

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

內連接與外連接

如果驅動表中的記錄即使在被驅動表中沒有匹配的記錄，但我們也仍然需要加入到結果集。為了解決這個問題，就有了內連接和外連接的概念：

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

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

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

• 左外連接

  選取左側的表為驅動表。

• 右外連接

  選取右側的表為驅動表。

where 與 on

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

• WHERE子句中的過濾條件

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

• ON子句中的過濾條件

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

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

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

左外連接和右外連接簡稱左連接和右連接，所以下邊提到的左外連接和右外連接中的外字都用括號擴起來，以表示這個字兒可有可無。

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

感謝各位的閱讀，以上就是“Mysql表連接的誤區與原理是什么”的內容了，經過本文的學習后，相信大家對Mysql表連接的誤區與原理是什么這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！