[[421307]]

本文轉(zhuǎn)載自微信公眾號(hào)「數(shù)據(jù)和云」，作者于志軍 。轉(zhuǎn)載本文請(qǐng)聯(lián)系數(shù)據(jù)和云公眾號(hào)。

SQL優(yōu)化過程中，發(fā)現(xiàn)開發(fā)人員在寫多表關(guān)聯(lián)查詢的時(shí)候，對(duì)于謂詞過濾條件的寫法很隨意，寫在on后面與where后面的情況均有，這可能會(huì)導(dǎo)致沒有理解清楚其真正的含義而無法得到期望的結(jié)果。

多表關(guān)聯(lián)連接方式有inner join、left join、right join、full join四種，下面通過實(shí)驗(yàn)來說明不同連接方式謂詞放在on與where后的效果與影響。

初始化測(cè)試數(shù)據(jù)

create table t1(id number(10),name varchar2(30),status varchar2(2)); 
create table t2(id number(10),mobile varchar2(30)); 
insert into t1 values(1,'a','1'); 
insert into t1 values(2,'b','1'); 
insert into t1 values(3,'c','1'); 
insert into t1 values(4,'d','1'); 
insert into t1 values(5,'e','1'); 
insert into t1 values(6,'f','0'); 
insert into t1 values(7,'g','0'); 
insert into t1 values(8,'h','0'); 
insert into t1 values(9,'i','0'); 
insert into t1 values(10,'j','0'); 
insert into t2 values(1,'12345'); 
insert into t2 values(2,'23456'); 
insert into t2 values(3,'34567'); 
insert into t2 values(6,'67890'); 
insert into t2 values(7,'78901'); 

1.Inner join

SQL>select * from t1 inner join t2 on t1.id=t2.id and t1.status=‘1’;

ID NAME                           ST         ID MOBILE 
    1 a                              1           1 12345 
    2 b                              1           2 23456 
    3 c                              1           3 34567 

SQL> select * from t1 inner join t2 on t1.id=t2.id where t1.status=‘1’;

ID NAME                           ST         ID MOBILE 
     1 a                              1           1 12345 
     2 b                              1           2 23456 
     3 c                              1           3 34567 

我們發(fā)現(xiàn)謂詞t1.status=’1’放在on后與where后結(jié)果一樣，它們的執(zhí)行計(jì)劃相同，說明CBO對(duì)這兩種情況做了相同處理。

執(zhí)行計(jì)劃如下圖所示：

Inner join時(shí)謂詞不管放在哪個(gè)位置，CBO都先對(duì)t1表過濾，再與t2表關(guān)聯(lián)。

2.left join

(1)左右表謂詞過濾都放在on后面：

SQL> select * from t1 left join t2 on t1.id=t2.id and t1.status=‘1’ and t2.id<3;

ID NAME                           ST         ID MOBILE 
    1 a                              1           1 12345  
    2 b                              1           2 23456 
    3 c                              1 
    8 h                              0 
    5 e                              1 
    9 i                              0 
   10 j                              0 
    7 g                              0 
    6 f                              0 
    4 d                              1 

執(zhí)行計(jì)劃如下：

從執(zhí)行計(jì)劃可以看出，t1.status=’1’放在on后面，t1表并沒有對(duì)謂詞status進(jìn)行過濾，結(jié)果集顯示t1的全表數(shù)據(jù)。這是由left join的特性決定的，左表會(huì)顯示全部數(shù)據(jù)。t2.id<3是先對(duì)t2表進(jìn)行過濾再進(jìn)行連接，而t1.status=’1’是作為連接條件存在，對(duì)連接時(shí)產(chǎn)生的笛卡爾積數(shù)據(jù)做連接過濾。

(2)左右表謂詞過濾都放在where后面:

SQL>select * from t1 left join t2 on t1.id=t2.id where t1.status=‘1’ and t2.id<3;

ID NAME                           ST         ID MOBILE 
     1 a                              1           1 12345 
     2 b                              1           2 23456 

從執(zhí)行計(jì)劃可以看出，謂詞放在where后面，是先對(duì)表進(jìn)行過濾，然后再對(duì)過濾后的數(shù)據(jù)進(jìn)行連接。而且我們發(fā)現(xiàn)t1表上自動(dòng)加上了id<3的過濾條件，這是因?yàn)橛衪1.id=t2.id等值連接，如果t1表上id列有索引，性能就能看出差別來了。注意連接方式變成了hash join，這是因?yàn)橛冶淼闹^詞過濾條件寫在where后面，CBO會(huì)把左連接等價(jià)為內(nèi)連接。

(3)右表的謂詞寫在on后面，左表的謂詞寫在where后面：

SQL>select * from t1 left join t2 on t1.id=t2.id and t2.id<3

where t1.status=‘1’; 2

ID NAME                           ST         ID MOBILE 
    1 a                              1           1 12345  
    2 b                              1           2 23456 
    5 e                              1 
    4 d                              1 
    3 c                              1 

當(dāng)把對(duì)右表的過濾寫在on后面，先對(duì)兩表進(jìn)行過濾，再進(jìn)行l(wèi)eft join，顯示結(jié)果集與寫在where后面是不同的，連接方式還是左外連接，顯示t1過濾后的全部數(shù)據(jù)。

(4)右表的謂詞寫在where后面，左表的謂詞寫在on后面：

SQL> select * from t1 left join t2 on t1.id=t2.id and t1.status=‘1’ where t2.id<7;

ID NAME                           ST         ID MOBILE 
     1 a                              1           1 12345 
     2 b                              1           2 23456 
     3 c                              1           3 34567 

從執(zhí)行計(jì)劃看這種情況左連接轉(zhuǎn)換為內(nèi)連接，左表的謂詞條件寫在哪個(gè)位置都一樣。而且因?yàn)閠2表過濾后數(shù)據(jù)比t1表少，CBO把t2表當(dāng)成了驅(qū)動(dòng)表。

接下來我們?cè)倏匆粋€(gè)語句：

SQL> select * from t1 left join t2 on t1.id=t2.id and t1.status=‘1’

where t1.status=‘0’ ;

ID NAME                           ST         ID MOBILE 
   8 h                              0 
   6 f                              0 
   9 i                              0 
  10 j                              0 
   7 g                              0 

從執(zhí)行計(jì)劃看出，雖然t2表返回0行，步驟3上的filter條件肯定不成立，但有邏輯讀消耗，所以推斷它依然進(jìn)行了全表掃描，所以這種語句對(duì)t2表的掃描是對(duì)資源的一種浪費(fèi)，沒有意義?；蛟S你會(huì)覺得誰會(huì)這么無聊寫這種SQL，但是在開發(fā)過程中，SQL語句經(jīng)常是各種過濾條件組合經(jīng)過拼接而成，因?yàn)榉祷亟Y(jié)果是對(duì)的，他們意識(shí)不到會(huì)出現(xiàn)這種問題，在此說明此種情況主要是想說明一件事：不要總想著用一個(gè)語句來解決所有的功能需求，適當(dāng)?shù)牟鸱謱?duì)性能的提升是很有必要的。

3.right join

右連接與左連接是相似的，只不過是右表顯示全部數(shù)據(jù)，寫在on后面謂詞過濾對(duì)右表不起作用，在此不再舉例說明。

4.full join

全連接在應(yīng)用中似乎很少碰到，但是存在即合理，只是自己沒有遇到而已。

(1)兩個(gè)表的謂詞都放在on的后面：

這種情況不會(huì)先對(duì)兩個(gè)表過濾，而是作為連接條件過濾，符合連接就匹配上，不符合的就把左右兩表的數(shù)據(jù)都顯示出來，另一表的字段以空顯示。

(2)兩個(gè)表的謂詞都放在where后面：

這種情況CBO將其轉(zhuǎn)換為內(nèi)連接，先過濾再關(guān)聯(lián)。

(3)左表謂詞放在on后面，右表放在where后面：

這種情況轉(zhuǎn)換為右外連接，但是也是先對(duì)兩表過濾后再關(guān)聯(lián)。

(4)左表謂詞放在where后面，右表放在on后面：

這種情況轉(zhuǎn)換為左外連接，也是先對(duì)兩表過濾后再關(guān)聯(lián)。

總結(jié)

1.對(duì)于內(nèi)連接inner join，兩個(gè)表的謂詞條件放在on與where后面相同。

2.對(duì)于left join：

左表謂詞放在on后不會(huì)對(duì)左表數(shù)據(jù)進(jìn)行過濾，依然顯示左表全部數(shù)據(jù)，放在where后面才會(huì)對(duì)左表進(jìn)行過濾

右表謂詞不管放在on后還是where后都會(huì)對(duì)右表先過濾再連接，但是放在where后left join會(huì)轉(zhuǎn)換為inner join。

3.對(duì)于外連接，謂詞條件放的位置不同，結(jié)果集也不同，可以根據(jù)自己的需求斟酌使用。

關(guān)于作者

于志軍，云和恩墨技術(shù)顧問，Oracle 12c OCM。擁有OCM、OBCA證書，曾在某大型國企做過多年數(shù)據(jù)庫運(yùn)維工作，現(xiàn)駐場(chǎng)于某銀行，專門從事SQL性能優(yōu)化工作，熱衷于運(yùn)維故障處理、備份恢復(fù)、升級(jí)遷移、性能優(yōu)化的學(xué)習(xí)與分享。