快捷導(dǎo)航

Mysql表連接的誤區(qū)與原理詳析

更新時(shí)間：2022年09月18日 08:01:51 投稿：daisy

在使用MySQL數(shù)據(jù)庫(kù)過程中,left?join?基本是必用的語法,下面這篇文章主要給大家介紹了關(guān)于Mysql表連接的誤區(qū)與原理的相關(guān)資料,需要的朋友可以參考下

前言

搞后端的肯定要經(jīng)常接觸到數(shù)據(jù)庫(kù),搞數(shù)據(jù)庫(kù)一個(gè)避免不了的地方就是 join, join的語法很簡(jiǎn)單,但是在使用時(shí)常常陷入一下兩種誤區(qū):

誤區(qū)一: 業(yè)務(wù)至上,管他三七二十一,再?gòu)?fù)雜的查詢一個(gè)連接語句搞定
誤區(qū)二: 敬而遠(yuǎn)之,上次寫的慢查詢sql就是使用了join導(dǎo)致的,以后再也不敢用了

先來舉個(gè)栗子:

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)

現(xiàn)在我們對(duì)這張表進(jìn)行連接:

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)

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

連接過程簡(jiǎn)介

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

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

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

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

t1.m1 > 1
t1.m1 = t2.m2
t2.n2 < 'd'

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

首先確定第一個(gè)需要查詢的表，這個(gè)表稱之為驅(qū)動(dòng)表。只需要選取代價(jià)最小的那種訪問方法去執(zhí)行單表查詢語句就好了。此處假設(shè)使用t1作為驅(qū)動(dòng)表，那么就需要到t1表中找滿足t1.m1 > 1的記錄，因?yàn)楸碇械臄?shù)據(jù)太少，我們也沒在表上建立二級(jí)索引，所以此處查詢t1表的訪問方法就是全表掃描。
針對(duì)上一步驟中從驅(qū)動(dòng)表產(chǎn)生的結(jié)果集中的每一條記錄，分別需要到t2表中查找匹配的記錄，所謂匹配的記錄，指的是符合過濾條件的記錄。因?yàn)槭歉鶕?jù)t1表中的記錄去找t2表中的記錄，所以t2表也可以被稱之為被驅(qū)動(dòng)表。上一步驟從驅(qū)動(dòng)表中得到了2條記錄，所以需要查詢2次t2表。此時(shí)涉及兩個(gè)表的列的過濾條件t1.m1 = t2.m2就派上用場(chǎng)了：
- 當(dāng)t1.m1 = 2時(shí)，過濾條件t1.m1 = t2.m2就相當(dāng)于t2.m2 = 2，所以此時(shí)t2表相當(dāng)于有了t2.m2 = 2、t2.n2 < 'd'這兩個(gè)過濾條件，然后到t2表中執(zhí)行單表查詢。
- 當(dāng)t1.m1 = 3時(shí)，過濾條件t1.m1 = t2.m2就相當(dāng)于t2.m2 = 3，所以此時(shí)t2表相當(dāng)于有了t2.m2 = 3、t2.n2 < 'd'這兩個(gè)過濾條件，然后到t2表中執(zhí)行單表查詢。

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

內(nèi)連接與外連接

如果驅(qū)動(dòng)表中的記錄即使在被驅(qū)動(dòng)表中沒有匹配的記錄，但我們也仍然需要加入到結(jié)果集。為了解決這個(gè)問題，就有了內(nèi)連接和外連接的概念：

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

在MySQL中，根據(jù)選取驅(qū)動(dòng)表的不同，外連接仍然可以細(xì)分為2種：
- 左外連接
  
  選取左側(cè)的表為驅(qū)動(dòng)表。
- 右外連接
  
  選取右側(cè)的表為驅(qū)動(dòng)表。

where 與 on

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

WHERE子句中的過濾條件

WHERE子句中的過濾條件就是我們平時(shí)見的那種，不論是內(nèi)連接還是外連接，凡是不符合WHERE子句中的過濾條件的記錄都不會(huì)被加入最后的結(jié)果集。
ON子句中的過濾條件

對(duì)于外連接的驅(qū)動(dòng)表的記錄來說，如果無法在被驅(qū)動(dòng)表中找到匹配ON子句中的過濾條件的記錄，那么該記錄仍然會(huì)被加入到結(jié)果集中，對(duì)應(yīng)的被驅(qū)動(dòng)表記錄的各個(gè)字段使用NULL值填充。

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

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

左外連接和右外連接簡(jiǎn)稱左連接和右連接，所以下邊提到的左外連接和右外連接中的外字都用括號(hào)擴(kuò)起來，以表示這個(gè)字兒可有可無。

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