SQL優(yōu)化之針對(duì)count、表的連接順序、條件順序、in及exist的優(yōu)化

本文詳述了SQL優(yōu)化中針對(duì)count、表的連接順序、條件順序、in及exist的優(yōu)化，非常具有實(shí)用價(jià)值！詳述如下：

一、關(guān)于count

看過(guò)一些網(wǎng)上關(guān)于count(*)和count(列)的文章，count(列)的效率一定比count(*)高嗎？

其實(shí)個(gè)人覺(jué)得count(*)和count(列)根本就沒(méi)有可比性，count(*)統(tǒng)計(jì)的是表里面的總條數(shù)，而count(列)統(tǒng)計(jì)的是當(dāng)列的非空記錄條數(shù)。

不過(guò)我們可以通過(guò)實(shí)驗(yàn)來(lái)比較一下：

首先創(chuàng)建測(cè)試表：

drop table test purge;
create table test as select * from dba_objects;

update test set object_id =rownum ;
set timing on 
set linesize 1000
set autotrace on 

執(zhí)行

select count(*) from test;
select count(object_id) from test;

發(fā)現(xiàn)耗時(shí)是一樣的，難道他們的效率其實(shí)是一樣的嗎？

我們?cè)诹衞bject_id上創(chuàng)建索引試試看

create index idx_object_id on test(object_id);

然后再執(zhí)行

select count(*) from test;
select count(object_id) from test;

發(fā)現(xiàn)count(object_id)的速度明顯比count(*)高出一大截，難道是因?yàn)閏ount(object_id)能用到索引，所以效率才提高了很多？

我們?cè)傩薷南耾bject_id的列屬性

alter table test modify object_id not null;

然后再執(zhí)行

select count(*) from test;
select count(object_id) from test;

發(fā)現(xiàn)其實(shí)他們的速度是一樣快的，count(*)也可用到索引。
其實(shí)效率比較的前提是兩個(gè)語(yǔ)句的寫法要等價(jià)，這兩種寫法根本就不等價(jià)，因此不具有可比性。

對(duì)于oracle優(yōu)化器來(lái)說(shuō)，我們可以通過(guò)實(shí)驗(yàn)發(fā)現(xiàn)，count不同的列，統(tǒng)計(jì)的時(shí)間是不一樣的，大致趨勢(shì)是列越靠后，訪問(wèn)的開(kāi)銷越大，列的偏移量決定訪問(wèn)的性能。而count(*)的開(kāi)銷與偏移量無(wú)關(guān)。因此，在某些場(chǎng)合count(*)反而是最快的。

二、關(guān)于in和exist

關(guān)于in和exist的說(shuō)法大都是說(shuō)in的效率比exist高，所以有in的地方必需得換成exist等等。但是真的是這樣的嗎？

下面我們來(lái)做個(gè)試驗(yàn)：

在Oracle 10g中;

select * from dept where deptno NOT IN ( select deptno from emp ) ;
select * from dept where not exists ( select deptno from emp where emp.deptno=dept.deptno) ;

我們發(fā)現(xiàn)，exist確實(shí)比in的效率高啊。這個(gè)說(shuō)法貌似是成立的啊。

但是我們?cè)賵?zhí)行下面的語(yǔ)句

select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;

你會(huì)發(fā)現(xiàn)加上非空的約束條件后，in和exist的效率是一樣的。

查看三個(gè)語(yǔ)句的執(zhí)行計(jì)劃你就會(huì)發(fā)現(xiàn)，沒(méi)有加上非空約束的in語(yǔ)句和exist語(yǔ)句走的都是ANTI半連接算法，所以效率是一樣的，而未加非空約束的in語(yǔ)句用的是filter，而不是ANTI算法，所以效率就差一些。

所以我們可以得出結(jié)論：在oracle 10g中，如果可以確保非空，則in約束可以用到ANTI的半連接算法，這時(shí)候的效率和exist是一樣的。

在Oracle 11g中：

select * from dept where deptno NOT IN ( select deptno from emp ) ;
select * from dept where not exists ( select deptno from emp where emp.deptno=dept.deptno) ;

我們發(fā)現(xiàn)兩個(gè)語(yǔ)句的效率是一樣的，查看執(zhí)行計(jì)劃也是一樣的。原來(lái)oracle在11g中已經(jīng)做了優(yōu)化，所以in和exist的效率是一樣的。

由此我們可以得出結(jié)論，在11g中，使用in和exist的效率是一樣的，因?yàn)樗麄冏叩亩际潜容^高效的ANTI算法。

三、關(guān)于大小表的連接順序

在網(wǎng)上我們可以看到很多這樣的文章，在進(jìn)行多表查詢的時(shí)候，用小表或者交叉表做基礎(chǔ)表，放在后面，大表放在from后面的位置，因?yàn)楸淼脑L問(wèn)順序是從右往左的。

但是真的是這樣的嗎？

我們可以做實(shí)驗(yàn)驗(yàn)證一下（此處測(cè)試環(huán)境為 Oracle 11g）：

create table tab_big as select * from dba_objects where rownum=30000;
create table tab_small as select * from dba_objects where rownum=10;
set autotrace traceonly
set linesize 1000
set timing on 
select count(*) from tab_big,tab_small ; 
select count(*) from tab_small,tab_big ;

我們查看執(zhí)行計(jì)劃可以發(fā)現(xiàn)，這兩個(gè)語(yǔ)句的效率是一樣的，難道多表查詢，表的順序和效率無(wú)關(guān)嗎？

我們?cè)趫?zhí)行下面的語(yǔ)句：

select /*+rule*/ count(*) from tab_big,tab_small ; 
select /*+rule*/ count(*) from tab_small,tab_big ;

我們可以清楚的發(fā)現(xiàn)，小表在右，大表在左的語(yǔ)句，查詢效率高很多。

其實(shí)，在基于規(guī)則時(shí)代，查詢效率是和表的連接順序相關(guān)的，小表或者交叉表在左，大表在右的執(zhí)行效率會(huì)高一些。但是現(xiàn)在基本上是基于代價(jià)的時(shí)代，所以大小表的順序和效率無(wú)關(guān)，oracle優(yōu)化器會(huì)自動(dòng)去進(jìn)行效率優(yōu)化。

四、where子句中的連接條件順序

在基于規(guī)則時(shí)代，oracle采用自下而上的順序來(lái)解析where子句，根據(jù)這個(gè)原理，我們一般會(huì)將可能返回行數(shù)最少的表放在最后面，where子句中有過(guò)濾條件的子句放在最后面。

但是在現(xiàn)在基于代價(jià)時(shí)代，這種優(yōu)化都有oracle優(yōu)化器幫忙優(yōu)化了，所以關(guān)于表的順序和條件的順序已經(jīng)不會(huì)影響我們的查詢效率了。