多表關(guān)聯(lián)查詢過濾條件寫在On與Where后的區(qū)別
本文轉(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 (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)，沒有意義。或許你會(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í)與分享。
