詳解SQL中幾種常用的表連接方式
本文轉載自微信公眾號「數倉寶貝庫」，作者葉樺 等。轉載本文請聯系數倉寶貝庫公眾號。
數據庫性能優化最主要的就是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》，經出版方授權發布。
