詳解SQL中幾種常用的表連接方式
本文轉(zhuǎn)載自微信公眾號「數(shù)倉寶貝庫」，作者葉樺 等。轉(zhuǎn)載本文請聯(lián)系數(shù)倉寶貝庫公眾號。
數(shù)據(jù)庫性能優(yōu)化最主要的就是SQL優(yōu)化，SQL優(yōu)化的關(guān)鍵離不開三點(diǎn)：表的連接方式、訪問路徑和執(zhí)行順序，本文重點(diǎn)介紹幾種常見的連接方式。
多表關(guān)聯(lián)查詢，查詢優(yōu)化器的執(zhí)行步驟具體如下。
1)訪問路徑：查詢語句中涉及多個(gè)對象，可以基于成本確定每一個(gè)對象數(shù)據(jù)的檢索方式，是選擇全表掃描還是索引訪問等。
2)連接方式：結(jié)果集之間的關(guān)聯(lián)方式，主要包括嵌套循環(huán)、哈希連接、排序合并連接等。優(yōu)化器對結(jié)果集之間連接方式的判斷尤為重要，因?yàn)榕袛嘟Y(jié)果將會直接影響SQL的執(zhí)行效率。
3)關(guān)聯(lián)順序：當(dāng)關(guān)聯(lián)對象超過2個(gè)時(shí)，首先選取兩個(gè)對象關(guān)聯(lián)得到的結(jié)果集，再與第三個(gè)結(jié)果集相關(guān)聯(lián)。
下面我們重點(diǎn)介紹幾種常見的連接方式。
01嵌套循環(huán)連接
圖1所示的是嵌套循環(huán)連接示意圖。
圖1 嵌套循環(huán)連接示意圖
嵌套循環(huán)查詢流程具體如下。
1)兩表關(guān)聯(lián)，優(yōu)化器首先會確定驅(qū)動表，也稱外部表(outer table)，另一張則是被驅(qū)動的表，也稱為內(nèi)部表(inner table)。一般情況下，優(yōu)化器會把數(shù)據(jù)量小的定義為驅(qū)動表，執(zhí)行計(jì)劃中，驅(qū)動表在上，被驅(qū)動表在下。
2)驅(qū)動表確認(rèn)之后，會從其中提取一行有效數(shù)據(jù)，在被驅(qū)動表(內(nèi)部表)中查找和匹配有效數(shù)據(jù)并提取。
3)將數(shù)據(jù)返回給客戶端。
從以上步驟中我們可以看出，驅(qū)動表返回的行數(shù)直接影響了被驅(qū)動表的訪問次數(shù)，比如，驅(qū)動表根據(jù)篩選條件最終返回了10行有效數(shù)據(jù)，每返回一條就會傳值給被驅(qū)動表進(jìn)行匹配，驅(qū)動表一共需要循環(huán)訪問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為驅(qū)動表，Starts為1，說明只訪問1次，返回2行有效數(shù)據(jù)(A-Rows為實(shí)際返回的行數(shù))，EMPLOYEES為被驅(qū)動表，Starts為2，說明訪問2次。
學(xué)過C++編程的同學(xué)應(yīng)該記得，C++中的嵌套循環(huán)與下面的循環(huán)有些類似：
- #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的循環(huán)次數(shù)取決于i的取值范圍，我們可以將i看作驅(qū)動表，j看作被驅(qū)動表。
- 嵌套循環(huán)連接性能主要受限于以下幾點(diǎn)。
- 驅(qū)動表的返回行數(shù)。
- 被驅(qū)動表的訪問方式：如果被驅(qū)動表的連接列基數(shù)小且選擇性差，會導(dǎo)致全表掃描的訪問方式，其效率變得非常低，所以我們建議連接列存在索引，且基數(shù)大選擇性高。
- 驅(qū)動表篩選后將返回少量數(shù)據(jù)。
- 被驅(qū)動表關(guān)聯(lián)字段需要有索引(連接列基數(shù)較大或選擇性較高)。
- 兩表關(guān)聯(lián)后將返回少量數(shù)據(jù)。
- 適合于OLTP系統(tǒng)。
Tips
如果優(yōu)化器選擇了錯誤的連接方式，那么我們可以使用提示(hint)強(qiáng)制執(zhí)行使用嵌套循環(huán)的連接方式：“/*+ USE_NL(TABLE1,TABLE2) LEADING(TABLE1) */”，其中TABLE1和TABLE2為關(guān)聯(lián)表的別名，LEADING(TABLE1)用于將TABLE1指定為驅(qū)動表。
02哈希連接
圖2所示的是哈希連接示意圖。
圖2 哈希連接示意圖
嵌套循環(huán)連接適用于兩表關(guān)聯(lián)后將返回少量數(shù)據(jù)的情況，那么返回大量數(shù)據(jù)時(shí)該采用哪種連接方式呢?答案是采用哈希連接。
哈希連接的查詢流程具體如下。
1)兩表等值關(guān)聯(lián)。
2)優(yōu)化器將數(shù)據(jù)量小的表作為驅(qū)動表，在PGA的SQL 工作區(qū)域(work areas)中，將驅(qū)動表的連接列構(gòu)建成一張哈希表。
3)讀取大表，對連接列進(jìn)行哈希運(yùn)算(檢查哈希表，以查找連接的行)。
4)將數(shù)據(jù)返回給客戶端。
從以上步驟中我們可以看出，通過哈希值進(jìn)行匹配的方式，更適用于兩表等值關(guān)聯(lián)。示例代碼如下：
- 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為驅(qū)動表，Starts為1，說明訪問1次，返回105行有效數(shù)據(jù)(A-Rows為實(shí)際返回的行數(shù))，ORDER_ITEMS為被驅(qū)動表，Starts也為1，說明僅訪問1次。其中，OMem、1Mem為執(zhí)行所需的PGA評估值，Used-Mem為實(shí)際執(zhí)行時(shí)PGA中SQL工作區(qū)域消耗的內(nèi)存(即發(fā)生磁盤交換的次數(shù))，當(dāng)驅(qū)動表較大，PGA的SQL 工作區(qū)域無法完全容納時(shí)，就會溢出到臨時(shí)表空間產(chǎn)生磁盤交互，進(jìn)而影響性能。
哈希連接性能主要受限于以下兩點(diǎn)。
- 等值連接。
- PGA SQL工作區(qū)域較小，且驅(qū)動表為大表時(shí)，容易出現(xiàn)性能問題。
當(dāng)同時(shí)滿足以下條件時(shí)，哈希連接方式將會非常有用。
- 兩表等值關(guān)聯(lián)后返回大量數(shù)據(jù)。
- 不同于嵌套循環(huán)連接，哈希連接被驅(qū)動表的連接字段時(shí)不需要有索引。
Tips
同樣，我們也可以使用提示強(qiáng)制執(zhí)行使用哈希連接的方式：“/*+ USE_HASH (TABLE1,TABLE2) LEADING(TABLE1) */”。
03排序合并連接
圖3所示的是排序合并連接示意圖。
圖3 排序合并連接示意圖
哈希連接適用于兩表等值關(guān)聯(lián)后返回大量數(shù)據(jù)的情況，那么非等值關(guān)聯(lián)返回大量數(shù)據(jù)的情況又該采用哪種連接方式呢?答案是排序合并連接。
同時(shí)滿足以下條件時(shí)，排序合并連接的性能要比哈希連接得好。
- 兩表非等值關(guān)聯(lián)(>、>=、<、<=、<>)。
- 數(shù)據(jù)源自身有序。
- 不必額外執(zhí)行排序操作。
排序合并連接方式中沒有驅(qū)動表的概念，連接查詢流程具體如下。
1)兩表根據(jù)關(guān)聯(lián)列各自排序。
2)在內(nèi)存中進(jìn)行合并處理。
從以上實(shí)現(xiàn)步驟中我們可以看出，由于匹配的對象是連接列各自排序后的值，因此排序合并連接方式更適用于兩表非等值關(guān)聯(lián)的情形，示例代碼如下：
- 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 | | | |
- ------------------------------------------------------------------------------------------------------------
從上述示例所示的執(zhí)行計(jì)劃中我們可以看出，ID=3的ORDERS表Starts為1，說明訪問1次，返回105行有效數(shù)據(jù)(A-Rows為實(shí)際返回行數(shù))，ORDER_ITEMS表的Starts為1，說明也只訪問1次，但I(xiàn)D=4的SORT JOIN表Starts為105，說明在內(nèi)存中進(jìn)行了105次匹配。其中，OMem、1Mem為執(zhí)行排序操作所需的PGA評估值，Used-Mem為實(shí)際執(zhí)行時(shí)PGA中SQL工作區(qū)域消耗的內(nèi)存(即發(fā)生磁盤交換的次數(shù))。
從以上步驟中我們可以看出，由于比較對象是兩張表的連接列order_id，所以需要各自的連接列先完成排序(ID=2和ID=4)，之后再進(jìn)行匹配。如果此時(shí)連接列上存在索引，那么索引返回的數(shù)據(jù)就是有序的，此時(shí)不需要再進(jìn)行額外的排序操作。
Tips
同樣，我們也可以使用提示強(qiáng)制執(zhí)行選擇排序合并連接的方式：“/*+ USE_MERGE(TABLE1,TABLE2) */”。
04笛卡爾連接
當(dāng)一個(gè)或多個(gè)表連接沒有任何連接條件時(shí)，數(shù)據(jù)庫將使用笛卡兒連接。優(yōu)化器將一個(gè)數(shù)據(jù)源的每一行與另一個(gè)數(shù)據(jù)源的每一行連接在一起，以創(chuàng)建兩組數(shù)據(jù)集的笛卡兒積。示例代碼如下：
- 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 | | | |
- -----------------------------------------------------------------------------------------------
從以上執(zhí)行計(jì)劃中我們可以看出，先對表order_items進(jìn)行排序，然后進(jìn)行兩表的笛卡兒乘積操作，由于沒有過濾條件，當(dāng)數(shù)據(jù)量很大的時(shí)候，返回的行數(shù)將會非常多，因此若無特殊情況，不建議使用沒有任何連接條件的查詢。
本文摘編于《DBA攻堅(jiān)指南：左手Oracle，右手MySQL》，經(jīng)出版方授權(quán)發(fā)布。
