詳解SQL中幾種常用的表連接方式

作者：葉樺等 2021-08-06 18:37:50

運維數據庫運維

數據庫性能優化最主要的就是SQL優化，SQL優化的關鍵離不開三點：表的連接方式、訪問路徑和執行順序，本文重點介紹幾種常見的連接方式。

[[415934]]

本文轉載自微信公眾號「數倉寶貝庫」，作者葉樺等。轉載本文請聯系數倉寶貝庫公眾號。

數據庫性能優化最主要的就是SQL優化，SQL優化的關鍵離不開三點：表的連接方式、訪問路徑和執行順序，本文重點介紹幾種常見的連接方式。

多表關聯查詢，查詢優化器的執行步驟具體如下。

1)訪問路徑：查詢語句中涉及多個對象，可以基于成本確定每一個對象數據的檢索方式，是選擇全表掃描還是索引訪問等。

2)連接方式：結果集之間的關聯方式，主要包括嵌套循環、哈希連接、排序合并連接等。優化器對結果集之間連接方式的判斷尤為重要，因為判斷結果將會直接影響SQL的執行效率。

3)關聯順序：當關聯對象超過2個時，首先選取兩個對象關聯得到的結果集，再與第三個結果集相關聯。

下面我們重點介紹幾種常見的連接方式。

01嵌套循環連接

圖1所示的是嵌套循環連接示意圖。

圖1 嵌套循環連接示意圖

嵌套循環查詢流程具體如下。

1)兩表關聯，優化器首先會確定驅動表，也稱外部表(outer table)，另一張則是被驅動的表，也稱為內部表(inner table)。一般情況下，優化器會把數據量小的定義為驅動表，執行計劃中，驅動表在上，被驅動表在下。

2)驅動表確認之后，會從其中提取一行有效數據，在被驅動表(內部表)中查找和匹配有效數據并提取。

3)將數據返回給客戶端。

從以上步驟中我們可以看出，驅動表返回的行數直接影響了被驅動表的訪問次數，比如，驅動表根據篩選條件最終返回了10行有效數據，每返回一條就會傳值給被驅動表進行匹配，驅動表一共需要循環訪問10次。示例代碼如下：

SQL> SELECT /*+ USE_NL(e d) */ e.first_name, e.last_name, e.salary, d.department_name 
 
  FROM hr.employees e, hr.departments d 
 
 WHERE d.department_name IN ('Marketing', 'Sales') 
 
   AND e.department_id = d.department_id; 
 
 
 
SQL>  select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST')); 
 
SQL_ID  3nsqhdh150bx5, child number 0 
 
------------------------------------- 
 
SELECT /*+ USE_NL(e d) */ e.first_name, e.last_name, e.salary, 
 
d.department_name   FROM hr.employees e, hr.departments d  WHERE 
 
d.department_name IN ('Marketing', 'Sales')    AND e.department_id = 
 
d.department_id 
 
 
 
Plan hash value: 2968905875 
 
------------------------------------------------------------------------------------- 
 
| Id  | Operation          |Name       |Starts|E-Rows|A-Rows |   A-Time   | Buffers | 
 
------------------------------------------------------------------------------------- 
 
|   0 | SELECT STATEMENT   |           |    1 |      |    36 |00:00:00.01 |      23 | 
 
|   1 |  NESTED LOOPS      |           |    1 |  19  |    36 |00:00:00.01 |      23 | 
 
|*  2 |   TABLE ACCESS FULL|DEPARTMENTS|    1 |  2   |     2 |00:00:00.01 |      8 | 
 
|*  3 |   TABLE ACCESS FULL|EMPLOYEES  |    2 |  10  |    36 |00:00:00.01 |     15 | 
 
-------------------------------------------------------------------------------------

從上述示例代碼中我們可以看出，DEPARTMENTS為驅動表，Starts為1，說明只訪問1次，返回2行有效數據(A-Rows為實際返回的行數)，EMPLOYEES為被驅動表，Starts為2，說明訪問2次。

學過C++編程的同學應該記得，C++中的嵌套循環與下面的循環有些類似：

#include <stdio.h> 
 
int main () 
 
{ 
 
   int i, j; 
 
   for(i=1; i<100; i++) { 
 
      for(j=1; j <= 100; j++) 
 
        if(!(i%j)) break; 
 
      if(j > (i/j)) printf("%d \n", i); 
 
   } 
 
   return 0; 
 
}

j的循環次數取決于i的取值范圍，我們可以將i看作驅動表，j看作被驅動表。

嵌套循環連接性能主要受限于以下幾點。
驅動表的返回行數。
被驅動表的訪問方式：如果被驅動表的連接列基數小且選擇性差，會導致全表掃描的訪問方式，其效率變得非常低，所以我們建議連接列存在索引，且基數大選擇性高。
驅動表篩選后將返回少量數據。
被驅動表關聯字段需要有索引(連接列基數較大或選擇性較高)。
兩表關聯后將返回少量數據。
適合于OLTP系統。

Tips

如果優化器選擇了錯誤的連接方式，那么我們可以使用提示(hint)強制執行使用嵌套循環的連接方式：“/*+ USE_NL(TABLE1,TABLE2) LEADING(TABLE1) */”，其中TABLE1和TABLE2為關聯表的別名，LEADING(TABLE1)用于將TABLE1指定為驅動表。

02哈希連接

圖2所示的是哈希連接示意圖。

圖2　哈希連接示意圖

嵌套循環連接適用于兩表關聯后將返回少量數據的情況，那么返回大量數據時該采用哪種連接方式呢?答案是采用哈希連接。

哈希連接的查詢流程具體如下。

1)兩表等值關聯。

2)優化器將數據量小的表作為驅動表，在PGA的SQL 工作區域(work areas)中，將驅動表的連接列構建成一張哈希表。

3)讀取大表，對連接列進行哈希運算(檢查哈希表，以查找連接的行)。

4)將數據返回給客戶端。

從以上步驟中我們可以看出，通過哈希值進行匹配的方式，更適用于兩表等值關聯。示例代碼如下：

SQL> SELECT /*+ USE_HASH(o l) */o.customer_id, l.unit_price * l.quantity 
 
  2    FROM oe.orders o, oe.order_items l 
 
  3   WHERE l.order_id = o.order_id; 
 
 
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST')); 
 
SQL_ID  cu980xxpu0mmq, child number 0 
 
------------------------------------- 
 
SELECT /*+ USE_HASH(o l) */o.customer_id, l.unit_price * l.quantity 
 
FROM oe.orders o, oe.order_items l  WHERE l.order_id = o.order_id 
 
 
Plan hash value: 864676608 
 
------------------------------------------------------------------------------------------------------------- 
 
| Id  | Operation          |Name       |Starts|E-Rows|A-Rows|A-Time      |Buffers|Reads|OMem |1Mem |Used-Mem| 
 
------------------------------------------------------------------------------------------------------------- 
 
|   0 | SELECT STATEMENT   |           |   1  |      | 665  |00:00:00.04 |   57  |   5 |     |     |         | 
 
|*  1 |  HASH JOIN         |           |   1  |  665 | 665  |00:00:00.04 |   57  |   5 |1888K|1888K|1531K (0)| 
 
|   2 |   TABLE ACCESS FULL|ORDERS     |   1  |  105 | 105  |00:00:00.04 |   6   |   5 |     |     |         | 
 
|   3 |   TABLE ACCESS FULL|ORDER_ITEMS|   1  |  665 | 665  |00:00:00.01 |   51  |   0 |     |     |         | 
 
-------------------------------------------------------------------------------------------------------------

從上述示例代碼中我們可以看出，ORDERS為驅動表，Starts為1，說明訪問1次，返回105行有效數據(A-Rows為實際返回的行數)，ORDER_ITEMS為被驅動表，Starts也為1，說明僅訪問1次。其中，OMem、1Mem為執行所需的PGA評估值，Used-Mem為實際執行時PGA中SQL工作區域消耗的內存(即發生磁盤交換的次數)，當驅動表較大，PGA的SQL 工作區域無法完全容納時，就會溢出到臨時表空間產生磁盤交互，進而影響性能。

哈希連接性能主要受限于以下兩點。

等值連接。
PGA SQL工作區域較小，且驅動表為大表時，容易出現性能問題。

當同時滿足以下條件時，哈希連接方式將會非常有用。

兩表等值關聯后返回大量數據。
不同于嵌套循環連接，哈希連接被驅動表的連接字段時不需要有索引。

Tips

同樣，我們也可以使用提示強制執行使用哈希連接的方式：“/*+ USE_HASH (TABLE1,TABLE2) LEADING(TABLE1) */”。

03排序合并連接

圖3所示的是排序合并連接示意圖。

圖3 排序合并連接示意圖

哈希連接適用于兩表等值關聯后返回大量數據的情況，那么非等值關聯返回大量數據的情況又該采用哪種連接方式呢?答案是排序合并連接。

同時滿足以下條件時，排序合并連接的性能要比哈希連接得好。

兩表非等值關聯(>、>=、<、<=、<>)。
數據源自身有序。
不必額外執行排序操作。

排序合并連接方式中沒有驅動表的概念，連接查詢流程具體如下。

1)兩表根據關聯列各自排序。

2)在內存中進行合并處理。

從以上實現步驟中我們可以看出，由于匹配的對象是連接列各自排序后的值，因此排序合并連接方式更適用于兩表非等值關聯的情形，示例代碼如下：

SQL> SELECT o.customer_id, l.unit_price * l.quantity 
 
  FROM oe.orders o, oe.order_items l 
 
 WHERE l.order_id > o.order_id; 
 
32233 rows selected.. 
 
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST')); 
 
SQL_ID  ajyppymnhwfyf, child number 1 
 
------------------------------------- 
 
SELECT o.customer_id, l.unit_price * l.quantity   FROM oe.orders o, 
 
oe.order_items l  WHERE l.order_id > o.order_id 
 
 
 
Plan hash value: 2696431709 
 
----------------------------------------------------------------------------------------------------------- 
 
| Id  | Operation         |Name       |Starts| E-Rows | A-Rows | A-Time     |Buffers|OMem |1Mem | Used-Mem | 
 
----------------------------------------------------------------------------------------------------------- 
 
|   0 | SELECT STATEMENT  |           |    1 |        |  32233 |00:00:00.10 |  21   |     |     |          | 
 
|   1 | MERGE JOIN        |           |    1 | 3 4580 |  32233 |00:00:00.10 |  21   |     |     |          | 
 
|   2 | SORT JOIN         |           |    1 |    105 |    105 |00:00:00.01 |   4   |11264|11264|10240  (0)| 
 
|   3 | TABLE ACCESS FULL |ORDERS     |    1 |    105 |    105 |00:00:00.01 |   4   |     |     |          | 
 
|*  4 | SORT JOIN         |           |  105 |    665 |  32233 |00:00:00.05 |  17   |59392|59392|53248  (0)| 
 
|   5 | TABLE ACCESS FULL |ORDER_ITEMS|    1 |    665 |    665 |00:00:00.01 |  17   |     |     |          | 
 
------------------------------------------------------------------------------------------------------------

從上述示例所示的執行計劃中我們可以看出，ID=3的ORDERS表Starts為1，說明訪問1次，返回105行有效數據(A-Rows為實際返回行數)，ORDER_ITEMS表的Starts為1，說明也只訪問1次，但ID=4的SORT JOIN表Starts為105，說明在內存中進行了105次匹配。其中，OMem、1Mem為執行排序操作所需的PGA評估值，Used-Mem為實際執行時PGA中SQL工作區域消耗的內存(即發生磁盤交換的次數)。

從以上步驟中我們可以看出，由于比較對象是兩張表的連接列order_id，所以需要各自的連接列先完成排序(ID=2和ID=4)，之后再進行匹配。如果此時連接列上存在索引，那么索引返回的數據就是有序的，此時不需要再進行額外的排序操作。

Tips

同樣，我們也可以使用提示強制執行選擇排序合并連接的方式：“/*+ USE_MERGE(TABLE1,TABLE2) */”。

04笛卡爾連接

當一個或多個表連接沒有任何連接條件時，數據庫將使用笛卡兒連接。優化器將一個數據源的每一行與另一個數據源的每一行連接在一起，以創建兩組數據集的笛卡兒積。示例代碼如下：

SQL> SELECT o.customer_id, l.unit_price * l.quantity 
 
  FROM oe.orders o, oe.order_items l; 
 
69825 rows selected. 
 
 
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST')); 
 
SQL_ID  d3xygy88uqzny, child number 0 
 
------------------------------------- 
 
SELECT o.customer_id, l.unit_price * l.quantity   FROM oe.orders o, 
 
oe.order_items l 
 
Plan hash value: 2616129901 
 
----------------------------------------------------------------------------------------------- 
 
| Id  | Operation            | Name      |Starts | E-Rows | Buffers |  OMem |  1Mem | Used-Mem | 
 
----------------------------------------------------------------------------------------------- 
 
|   0 | SELECT STATEMENT     |           |     1 |        |     125 |       |       |          | 
 
|   1 |  MERGE JOIN CARTESIAN|           |     1 |  69825 |     125 |       |       |          | 
 
|   2 |   TABLE ACCESS FULL  |ORDERS     |     1 |    105 |     108 |       |       |          | 
 
|   3 |   BUFFER SORT        |           |   105 |    665 |      17 | 27648 | 27648 |24576  (0)| 
 
|   4 |    TABLE ACCESS FULL |ORDER_ITEMS|     1 |    665 |      17 |       |       |          | 
 
-----------------------------------------------------------------------------------------------

從以上執行計劃中我們可以看出，先對表order_items進行排序，然后進行兩表的笛卡兒乘積操作，由于沒有過濾條件，當數據量很大的時候，返回的行數將會非常多，因此若無特殊情況，不建議使用沒有任何連接條件的查詢。

本文摘編于《DBA攻堅指南：左手Oracle，右手MySQL》，經出版方授權發布。

責任編輯：武曉燕來源：數倉寶貝庫

SQL 表連接方式