MySQL 連線查詢超全詳解

X先生發表於2020-07-08
MySql

1 作用

在資料庫中join操作被稱為連線，作用是能連線多個表的資料（通過連線條件），從多個表中獲取資料合併在一起作為結果集返回給客戶端。例如:

表A：

id name age
1 A 18
2 B 19
3 C 20

表B：

id uid gender
1 1 F
2 2 M

通過連線可以獲取到合併兩個表的資料：

select A.*,B.gender from  A left join B on A.id=B.uid
id name age gender
1 A 18 F
2 B 19 M
3 C 20 null

2 連線關鍵字

連線兩個表我們可以用兩個關鍵字：on，using。on可以指定具體條件，using則指定相同名字和資料型別的列作為等值判斷的條件，多個則通過逗號隔開。
如下:

on: select * from A join B on A.id=B.id and B.name=''

using: select * from A join B using(id,name) = select * from A join B on A.id=B.id and A.name=B.name

3 連線型別

3.1 內連線

內連線和交叉連線

  • 語法：A join | inner join | cross join B
  • 表現：A和B滿足連線條件記錄的交集，如果沒有連線條件，則是A和B的笛卡爾積
  • 特點：在MySQL中,cross join ，inner join和join所實現的功能是一樣的。因此在MySQL的官方文件中，指明瞭三者是等價的關係。

隱式連線

  • 語法：from A,B,C
  • 表現：相當於無法使用on和using的join
  • 特點：逗號是隱式連線運算子。 隱式連線是SQL92中的標準內容，而在SQL99中顯式連線才是標準，雖然很多人還在用隱私連線，但是它已經從標準中被移除。從使用的角度來說，還是推薦使用顯示連線，這樣可以更清楚的顯示出多個表之間的連線關係和連線依賴的屬性。

3.2 外連線

左外連線

  • 語法：A left join B
  • 表現：左表的資料全部保留，右表滿足連線條件的記錄展示，不滿足的條件的記錄則全是null

右外連線

  • 語法：A right join B
  • 表現：右表的資料全部保留，左表滿足連線條件的記錄展示，不滿足的條件的記錄則全是null

全外連線

MySQL不支援全外連線，只支援左外連線和右外連線。如果要獲取全連線的資料，要可以通過合併左右外連線的資料獲取到，如 select * from A left join B on A.name = B.name union select * from A right join B on B.name = B.name;。

這裡union會自動去重，這樣取到的就是全外連線的資料了。

3.3 自然連線

  • 語法：A natural join B ==== A natural left join B ==== A natural right join B
  • 表現：相當於不能指定連線條件的連線，MySQL會使用左右表內相同名字和型別的欄位作為連線條件。
  • 特點：自然連線也分自然內連線，左外連線，右外連線，其表現和上面提到的一致，只是連線條件由MySQL自動判定。

4 執行順序

在連線過程中，MySQL各關鍵字執行的順序如下：

from -> on|using -> where -> group by -> having -> select -> order by -> limit

可以看到，連線的條件是先於where的，也就是先連線獲得結果集後，才對結果集進行where篩選，所以在使用join的時候，我們要儘可能提供連線的條件，而少用where的條件，這樣才能提高查詢效能。

5 連線演算法

join有三種演算法，分別是Nested Loop Join，Hash join，Sort Merge Join。MySQL官方文件中提到，MySQL只支援Nested Loop Join這一種演算法。

具體來說Nested Loop Join又分三種細分的演算法：

  • SNLJ
  • BNLJ
  • INLJ

我們來看下對於連線語句select * from A left join B on A.id=B.tid，這三種演算法是怎麼連線的。

5.1 Simple Nested Loop Join（SNLJ）

SNLJ是在沒有使用到索引的情況下，通過兩層迴圈全量掃描連線的兩張表，得到符合條件的兩條記錄則輸出。也就是讓兩張表做笛卡爾積進行掃描，是比較暴力的演算法，會比較耗時。其過程如下：

for (a in A) {
     for (b in B) {
         if (a.id == b.tid) {
             output <a, b>;
         }
     }
 }

當然，MySQL即使在無索引可用，或者判斷全表掃描可能比使用索引更快的情況下，還是不會選擇使用過於粗暴的SNLJ演算法，而是採用下面的演算法。

5.2 Block Nested Loop Join（BNLJ）

INLJ是MySQL無法使用索引的時候採用的join演算法。會將外層迴圈的行分片存入join buffer, 內層迴圈的每一行與整個buffer中的記錄做比較，從而減少內層迴圈的次數，具體邏輯如下:

for (blockA in A.blocks) {
     for (b in B) {
         if (b.tid in blockA.id) {
             output <a, b>;
         }
     }
 }

相比於SNLJ演算法，BNLJ演算法通過外層迴圈的結果集的分塊，可以有效的減少內層迴圈的次數。

原理

舉例來說，外層迴圈的結果集是100行，使用SNLJ演算法需要掃描內部表100次，如果使用BNLJ演算法，假設每次分片的數量是10，則會先把對Outer Loop表(外部表)每次讀取的10行記錄放到join buffer,然後在InnerLoop表(內部表)中每次迴圈都直接匹配這10行資料，這樣內層迴圈只需要10次，對內部表的掃描減少了9/10，所以BNLJ演算法就能夠顯著減少內層迴圈表掃描的次數。

當然這裡，不管SNLJ還是BNLJ演算法，他們總的比較次數都是一樣的，都是要拿外層迴圈的每一行與內層迴圈的每一行進行比較。

BNLJ演算法減少的是總的掃描行數，SNLJ演算法是外層迴圈要一行行掃描A表的資料，然後取A.id去表B一行行掃描看是否匹配。而BNLJ演算法則是外層迴圈要一行行掃描A表的資料，然後放到記憶體分塊裡，然後去表B一行行掃描，掃描出來的B的一行資料與記憶體分塊裡的A的資料塊進行比較。這裡可以一次就是很多行A的資料與B的資料進行比較，而且是在記憶體中進行比較，速度更加快了。

影響因素

這裡BNLJ演算法總的掃描行數是由外層迴圈的資料量N，和分塊數量K還有內層迴圈的資料量M決定的。其中分塊數量K與外層迴圈的資料量N又是息息相關的，我們可以表示為λN，其中λ取值為(0~1)。則總掃描次數C=N+λNM。

可以看出，在這個式子裡，N和λ的大小都會影響掃描行數，但是λ才是影響掃描行數的關鍵因素，這個值越小越好（除非N和M的差值非常大，這時候N才會成為關鍵影響因素）。

那什麼會影響 λ 的大小呢？那就是 MySQL的join_buffer_size設定項的大小了。λ和join_buffer_size成倒數關係,join_buffer_size越大，分塊越大，λ越小，分塊數量也就越少，也就是外層迴圈的次數也越少。所以在使用不上索引的時候，我們要優先考慮擴大join_buffer_size的大小，這樣優化效果會更明顯。而在能使用上索引的時候，MySQL會使用以下演算法來進行join。

5.3 Index Nested Loop Join（INLJ）

INLJ是MySQL判斷能使用到被驅動表的索引的情況下采用的演算法。假設A表的資料行為10，B表的資料行為100，且B.tid建立了索引，則對於select * from A left join B on A.id=B.tid，MySQL會採用Index Nested Loop Join。其過程如下：

for (a in A) {
     if (a.id in B.tid.Index) {
        output <a, tid.Index所在行>;
     }
 }

總共需要迴圈10次A，每次迴圈的時候通過索引查詢一次B的資料。而如果我們反過來是B left join A的話，總共要迴圈100次B，由此可見如果使用join的話，需要讓小表做驅動表，這樣才能有效減少迴圈次數。但是需要注意的是，這個結論的前提是可以使用被驅動表的索引。

INLJ內層迴圈讀取的是索引，可以減少記憶體迴圈的次數，提高join效率，但是也有缺點的，就是如果掃描的索引是非聚簇索引，並且需要訪問非索引的資料，會產生一個回表讀取資料的操作，這就多了一次隨機的I/O操作。例如上面在索引裡匹配到了tid，還要去找tid所在的行在磁碟所在的位置，具體可以見我以前的文章：MySQL索引詳解之索引的儲存方式。

6 注意點

  • 儘量增加連線條件，減少join後資料集的大小
  • 用小結果集驅動大結果集，將篩選結果小的表首先連線，再去連線結果集比較大的表
  • 被驅動表的被join的欄位要建立索引，且使用上索引。使用上索引包括使用該欄位，且不會有索引失效的情況出現
  • 設定足夠大的join_buffer_size

7 外連線常見問題

Q：如果想篩選驅動表的資料，例如左連線篩選左表的資料，該在連線條件還是where篩選？
A：要通過where篩選，連線條件隻影響連線過程，不影響連線返回的結果數（某些情況下連線條件會影響連線返回的結果數，例如左連線中，右側匹配的資料不唯一的時候）

Q：被驅動表匹配的資料行不唯一導致最終連線資料超過驅動表資料量該怎麼辦？例如對於左連線，右表匹配的資料行不唯一。
A：join之前先對被驅動表去重，例如通過group by去重：A lef join (select * from B group by name)。

8 參考資料

www.jianshu.com/p/6864abb4d885
www.cnblogs.com/blueoverflow/p/471...
leokongwq.github.io/2019/12/13/mys...
zhuanlan.zhihu.com/p/81398139

本作品採用《CC 協議》，轉載必須註明作者和本文連結

X先生

相關文章