SQL等價改寫核心思想概述

1、SQL等價改寫核心思想概述技術(shù)創(chuàng)新，變革未來SQL優(yōu)化可不僅僅是：收集一下統(tǒng)計信息 創(chuàng)建一些索引添加一些HINT綁定一下執(zhí)行計劃SQL優(yōu)化還需要能夠?qū)QL進行等價改寫SQL等價改寫往往是SQL優(yōu)化所有環(huán)節(jié)中最難的(特別是幾百行的SQL)不管是Oracle，MySQL，PostGre SQL，Oceanbase，Hadoop Hive都離不開SQL等價改寫 對于某些SQL，往往只有進行等價改寫才能徹底優(yōu)化概述1表與表之間關(guān)系2半連接3反連接4標量子查詢5Filter目錄CONTENTS1：1 關(guān)系兩表關(guān)聯(lián)返回1的關(guān)系，常見于對表做垂直拆分之后的兩表關(guān)聯(lián)(主鍵關(guān)聯(lián))1：N 關(guān)系兩表關(guān)聯(lián)返回N的關(guān)

2、系最常見的應(yīng)用，比如一個商品(1)對應(yīng)多個訂單(N)N：N 關(guān)系兩表關(guān)聯(lián)返回局部范圍笛卡爾積，常見于半連接和反連接中(in,exists/not in,not exists)表與表之間關(guān)系select count(*) from a left join b on a.id=b.id;某運營商，2012年，Oracle10gR2a：b是1：1關(guān)系，a和b都是上千萬條數(shù)據(jù)，原始SQL要跑12秒因為是left join，不管a和b是否能關(guān)聯(lián)上，都會返回a的所有數(shù)據(jù)，b屬于1的關(guān)系 不管有沒有關(guān)聯(lián)上，不影響最終count(*)的結(jié)果，所以SQL被改寫為：select count(*) from a;最

3、終0.05秒就能返回結(jié)果其實，只要b是1的關(guān)系，不管a是屬于1的關(guān)系還是N的關(guān)系，都能這樣改寫注：到了Oracle11g之后，CBO會自動改寫上面SQL1：1關(guān)系優(yōu)化案例1表與表之間關(guān)系2半連接3反連接4標量子查詢5Filter目錄CONTENTS半連接：兩表關(guān)聯(lián)只返回主表的數(shù)據(jù)，并且只返回主表與子表關(guān)聯(lián)上的數(shù)據(jù) 這種連接就叫半連接(SEMI JOIN)，半連接一般就是指的IN和EXISTS下面兩條等價的SQL語句就是典型的半連接寫法：select * from dept where deptno in (select deptno from emp); select * from deptw

4、here exists (select null from emp where dept.deptno = emp.deptno);半連接select * from dept where deptno in (select deptno from emp);dept與emp是1：N關(guān)系，上面查詢只返回dept表的數(shù)據(jù)，也就是說返回的是1的關(guān)系將上面查詢改寫為內(nèi)連接的時候，可以將emp變成1的關(guān)系，再與dept關(guān)聯(lián)-1：1select d.*from dept d, (select deptno from emp group by deptno) ewhere d.deptno = e.dept

5、no;d是1的關(guān)系，子查詢e也是1的關(guān)系(group by 連接列之后從N變成了1)，1：1關(guān)聯(lián)返回1將半連改寫為內(nèi)連接(一)select * from dept where deptno in (select deptno from emp);dept與emp是1：N，上面查詢只返回dept表的數(shù)據(jù)，也就是說返回的是1的關(guān)系可以先讓dept與emp關(guān)聯(lián)變成N，再對N GROUP BY，將N變?yōu)?select d.deptno, d.dname, d.loc from dept d, emp e-1：N where d.deptno = e.deptnogroup by d.deptno, d

6、.dname, d.loc; -GROUP BY N 變成 1將半連改寫為內(nèi)連接(二)select * from dept where deptno in (select deptno from emp);select d.deptno,d.dname,d.locfrom (select d.*,(select 1from emp ewhere d.deptno = e.deptnoand rownum = 1) statusfrom dept d) d where status = 1;注：在實際工作中不要這樣改寫，這里只是為了演示將半連改寫為標量子查詢select * from dept

7、where deptno in (select deptno from emp);select d.*from dept dleft join (select deptno from emp group by deptno) e on d.deptno = e.deptnowhere e.deptno is not null;注：在實際工作中不要這樣改寫，這里只是為了演示將半連改寫為外連接create table a as select * from dba_objects;-a 大概7w行，10MB數(shù)據(jù)create table b as select * from dba_objects;-

8、b 大概7w行，10MB數(shù)據(jù)下面SQL要跑幾十分鐘(Oracle11g)：select count(distinct owner), count(distinct object_name) from awhere owner in (select owner from b);按理7w行的表與7w行的表關(guān)聯(lián)應(yīng)該秒出，不應(yīng)該跑幾十分鐘經(jīng)典的半連接優(yōu)化案例| Id| Operation| Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time|0|SELECT STATEMENT|1 |100|3749(74)|00:00:45|1|SORT GROUP BY|

9、1 |100|*2|HASH JOIN|556M|51G|2040K|3749(74)|00:00:45|3|TABLE ACCESSFULL|B|71979 |1194K|290(1)|00:00:04|4|TABLE ACCESSFULL|A|69607 |5641K|290(1)|00:00:04|經(jīng)典的半連接優(yōu)化案例SQL select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUTPlan hash value: 2142135107半連接執(zhí)行計劃應(yīng)該有SEMI關(guān)鍵字Predicate Information (identifie

10、d by operation id):2 - access(OWNER=OWNER)沒有SEMI關(guān)鍵字，說明SQL被CBO改寫為內(nèi)連接了select count(distinct owner), count(distinct object_name) from awhere owner in (select owner from b);通過分析執(zhí)行計劃發(fā)現(xiàn)原始SQL被CBO改寫為如下SQL：select count(distinct a.owner), count(distinct a.object_name) from a, bwhere a.owner = b.owner;經(jīng)典的半連接優(yōu)化

11、案例select count(distinct a.owner), count(distinct a.object_name) from a, bwhere a.owner = b.owner;兩個7W行的表關(guān)聯(lián)要跑幾十分鐘只有一種可能，就是兩個表是N：N關(guān)系(N：N關(guān)聯(lián)返回局部范圍笛卡爾積) 兩表關(guān)聯(lián)列是owner，確實是N：N關(guān)系，兩表關(guān)聯(lián)之后數(shù)據(jù)量有幾十億條對幾十億條數(shù)據(jù)進行count(distinct)肯定要跑幾十分鐘經(jīng)典的半連接優(yōu)化案例為避免出現(xiàn)N：N，將半連接從N的關(guān)系變成1的關(guān)系，所以將原始SQL改寫：select count(distinct owner), count(dis

12、tinct object_name) from awhere owner in (select owner from b group by owner);select count(distinct a.owner), count(distinct a.object_name)from a, (select owner from b group by owner) b where a.owner = b.owner;這個問題已經(jīng)在Oracle12c及之后的版本中修正經(jīng)典的半連接優(yōu)化案例如果半連接中主表屬于1的關(guān)系，子表(子查詢中的表)屬于N的關(guān)系，在改寫為內(nèi)連接的時候，子表需要加GROUP BY

13、去重 注意：這個時候半連接性能高于內(nèi)連接如果半連接中主表屬于1或者N的關(guān)系，子表(子查詢中的表)屬于1的關(guān)系，在改寫為內(nèi)連接的時候，子表不需要去重 注意：這個時候半連接與內(nèi)連接性能一樣如果半連接中主表屬于N的關(guān)系，子表(子查詢中的表)也屬于N的關(guān)系，這時要先對子查詢?nèi)ブ兀瑢⒆颖磙D(zhuǎn)換為1的關(guān)系 然后再關(guān)聯(lián)，千萬不能先關(guān)聯(lián)再去重半連接等價改寫為內(nèi)連接總結(jié)1表與表之間關(guān)系2半連接3反連接4標量子查詢5Filter目錄CONTENTS反連接：兩表關(guān)聯(lián)只返回主表的數(shù)據(jù)，并且只返回主表與子表沒關(guān)聯(lián)上的 數(shù)據(jù)，這種連接就叫反連接(ANTI JOIN)反連接一般就是指的NOT IN和NOT EXISTS下面兩

14、條等價的SQL語句就是典型的反連接：select * from deptwhere deptno not in (select deptno from emp where deptno is not null);select * from dept dwhere not exists (select null from emp e where d.deptno = e.deptno);反連接select * from deptwhere deptno not in (select deptno from emp where deptno is not null);反連接改寫為外連接的時候不用注

15、意子表是N的關(guān)系還是1的關(guān)系 為什么不需要考慮是N的關(guān)系還是1的關(guān)系？因為是求沒關(guān)聯(lián)上的數(shù)據(jù)，數(shù)據(jù)既然沒關(guān)聯(lián)上，即使是N的關(guān)系也不翻番select d.* from dept dleft join emp e on d.deptno = e.deptno where e.deptno is null;將反連改寫為外連接select * from deptwhere deptno not in (select deptno from emp where deptno is not null);select deptno, dname, loc from (select d.*,(select 1

16、from emp ewhere e.deptno = d.deptno and rownum = 1) statusfrom dept d) where status is null;注：在實際工作中不要這樣改寫，這里只是為了演示將反連改寫為標量子查詢1表與表之間關(guān)系2半連接3反連接4標量子查詢5Filter目錄CONTENTS標量子查詢：介于select與from之間的子查詢就叫標量子查詢select e.ename,(select dname from dept d where d.deptno =e.deptno) from emp e;標量子查詢算法：主表對連接列(e.deptno)去

17、重，然后傳值給子查詢的連接列(d.deptno)count(distinct nvl(deptno,0)有多少行，標量子查詢就要被執(zhí)行多少次子查詢的連接列最好要有索引，如果沒有索引，子查詢中的表就會被反復(fù)全表掃描標量子查詢一般情況下，標量子查詢應(yīng)該被改寫為外連接，特別是主表連接列基數(shù)很高，主表經(jīng)過where條件過濾之后仍然返回大量數(shù)據(jù)的情況下，必須將標量子查詢改寫為外連接也有將外連接改寫為標量子查詢的情況，當外連接是VIEW，并且主表返回數(shù)據(jù)少，VIEW不能被主表連接列謂詞推入的情況下，可以將外連接改寫為標量子查詢，這個時候可以謂詞推入標量子查詢標量子查詢中如果子查詢的表屬于N的關(guān)系，應(yīng)該將N

18、的關(guān)系變?yōu)?，否則會報錯SQL select d.dname,234d.loc,(select e.sal from emp e where e.deptno = d.deptno) from dept d;(select e.sal from emp e where e.deptno = d.deptno)*第 3 行出現(xiàn)錯誤:ORA-01427: 單行子查詢返回多個行可以用MAX,MIN,SUM,COUNT,AVG聚合函數(shù)將N變?yōu)?,也可以用ROWNUM將N變?yōu)?標量子查詢select e.ename,(select dname from dept d where d.deptno =e.

19、deptno) from emp e;emp與dept是N：1關(guān)系，標量子查詢中dept表是1的關(guān)系，改寫的時候不需要去重select e.ename, dnamefrom emp e left join dept d on e.deptno = d.deptno;將標量子查詢改寫為外連接一select d.dname,d.loc,(select max(e.sal) from emp e where e.deptno = d.deptno) max_salfrom dept d;dept與emp是1：N關(guān)系，標量子查詢中的emp是N的關(guān)系，改寫的時候要將N變?yōu)? select dname,

20、loc, max_salfrom dept dleft join (select deptno, max(sal) max_sal from emp group by deptno) e on d.deptno = e.deptno;select dname, loc, max(sal) max_sal from dept dleft join emp e on d.deptno = e.deptnogroup by dname, loc將標量子查詢改寫為外連接二select d.dname,d.loc,(select sal from emp e where e.deptno = d.dep

21、tno and rownum=1)from dept d;業(yè)務(wù)邏輯不嚴謹有rownum，但是沒有order by，這時無法等價改寫為外連接，只能做非等價改寫無法改寫的標量子查詢非等值關(guān)聯(lián)的標量子查詢改寫如果a和b是同一個表，可以用開窗函數(shù)改寫，a訪問一次如果a和b不是同一個表，改寫的時候a需要訪問2次select a.empno, a.ename, a.sal, b.sum_sal from aleft join (select a.rowid rid, sum(b.sal)sum_sal from a, bwhere b.sal = a.sal - 100 and b.sal = a.sal

22、 - 100 and b.sal = a.sal) sum_salfrom a;SELECT A.LXR_ID,A.SR,(SELECT C.JGID | | C.DLS_BM | | C.DLS_MC FROM KHGL_DLSJBXX C, KHGL_ZJKJ ZJKJWHERE C.JGID = ZJKJ.JGID AND EXISTS (SELECT 1FROM LXR_YH YHWHERE YH.KH_ID = ZJKJ.KJ_ID AND YH.KHLX = 2AND YH.LXR_ID = A.LXR_ID) AS ZJJGXXFROM LXR_JBXX A WHERE A.ST

23、ATUS = 1AND A.GRDM = :v1AND EXISTS (SELECT 1FROM LXR_YH YH, KHGL_GRDLXX GRDL WHERE YH.FZGS_DM = :v2AND YH.KHLX = 2AND YH.LXR_ID = A.LXR_ID AND GRDL.GRDL_ID = YH.KH_ID AND GRDL.STATUS = 1)AND ROWNUM 21;SELECT A.LXR_ID, A.SR, B.MSG AS ZJJGXX FROM LXR_JBXX A,(SELECT C.JGID | | C.DLS_BM | | C.DLS_MC AS

24、MSG, YH.LXR_IDFROM KHGL_DLSJBXX C, KHGL_ZJKJ ZJKJ, (SELECT LXR_ID, KH_IDFROM LXR_YH WHERE KHLX = 2GROUP BY LXR_ID, KH_ID) YH WHERE C.JGID = ZJKJ.JGIDAND YH.KH_ID = ZJKJ.KJ_ID) B WHERE A.LXR_ID = B.LXR_ID(+)AND A.STATUS = 1 AND A.GRDM = :v1 AND EXISTS (SELECT 1FROM LXR_YH YH, KHGL_GRDLXX GRDL WHERE Y

25、H.FZGS_DM = :v2AND YH.KHLX = 2AND YH.LXR_ID = A.LXR_ID AND GRDL.GRDL_ID = YH.KH_ID AND GRDL.STATUS = 1)AND ROWNUM 2000 and ename like %E%) and not exists (select 1from dept dwhere e.deptno = d.deptnoand d.dname like %N%);復(fù)雜的Filter改寫(先將子查詢改寫為標量)select ename, sal, deptno from (select ename,sal, deptno

26、, casewhen (select deptnofrom dept dwhere e.deptno = d.deptnoand dname like %C%) is not null then 數(shù)據(jù)來自existswhen sal 2000 and ename like %E% and (select deptnofrom dept dwhere e.deptno = d.deptnoand dname like %N%) is null then數(shù)據(jù)來自or后面 end noticefrom emp e) where notice is not null;where條件中有or子查詢，一般都會走Filter， 可以將半連接，反連接先改寫為標量子查詢再利用標量子查詢等價改寫方式改寫為外連接select ename, sal, deptno from (select ename,sal, deptno, casewhen (select deptnofrom dept dwhere e.deptno = d.deptnoand dname like %C%) is not null then 數(shù)據(jù)來自existswhen sal 2000 and ename like %E% and (select