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