「數倉寶貝庫」，帶你學資料！
導讀：資料庫性能優化最主要的就是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）強制執行使用嵌套循環的連接配接方式：“”，其中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
同樣，我們也可以使用提示強制執行使用哈希連接配接的方式：“”。
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
同樣，我們也可以使用提示強制執行選擇排序合并連接配接的方式：“”。
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》，經出版方授權釋出。