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 = 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表了。也就是說在兩表連線查詢中，驅動表只需要存取一次，被驅動表可能被存取多次。

內連線與外連線

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

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

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

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

  • 左外連線

    選取左側的表為驅動表。

  • 右外連線

    選取右側的表為驅動表。

where 與 on

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

• WHERE子句中的過濾條件

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

• ON子句中的過濾條件

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

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

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

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

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

總結

到此這篇關於Mysql表連線的誤區與原理的文章就介紹到這了,更多相關Mysql表連線原理內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！