本文詳述了SQL優(yōu)化中針對count、表的連接順序、條件順序、in及exist的優(yōu)化,非常具有實用價值!詳述如下:
一、關(guān)于count
看過一些網(wǎng)上關(guān)于count(*)和count(列)的文章,count(列)的效率一定比count(*)高嗎?
其實個人覺得count(*)和count(列)根本就沒有可比性,count(*)統(tǒng)計的是表里面的總條數(shù),而count(列)統(tǒng)計的是當(dāng)列的非空記錄條數(shù)。
不過我們可以通過實驗來比較一下:
首先創(chuàng)建測試表:
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)耗時是一樣的,難道他們的效率其實是一樣的嗎?
我們在列object_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(*)高出一大截,難道是因為count(object_id)能用到索引,所以效率才提高了很多?
我們再修改下object_id的列屬性
alter table test modify object_id not null;
然后再執(zhí)行
select count(*) from test;
select count(object_id) from test;
發(fā)現(xiàn)其實他們的速度是一樣快的,count(*)也可用到索引。
其實效率比較的前提是兩個語句的寫法要等價,這兩種寫法根本就不等價,因此不具有可比性。
對于oracle優(yōu)化器來說,我們可以通過實驗發(fā)現(xiàn),count不同的列,統(tǒng)計的時間是不一樣的,大致趨勢是列越靠后,訪問的開銷越大,列的偏移量決定訪問的性能。而count(*)的開銷與偏移量無關(guān)。因此,在某些場合count(*)反而是最快的。
二、關(guān)于in和exist
關(guān)于in和exist的說法大都是說in的效率比exist高,所以有in的地方必需得換成exist等等。但是真的是這樣的嗎?
下面我們來做個試驗:
在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確實比in的效率高啊。這個說法貌似是成立的啊。
但是我們再執(zhí)行下面的語句
select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;
你會發(fā)現(xiàn)加上非空的約束條件后,in和exist的效率是一樣的。
查看三個語句的執(zhí)行計劃你就會發(fā)現(xiàn),沒有加上非空約束的in語句和exist語句走的都是ANTI半連接算法,所以效率是一樣的,而未加非空約束的in語句用的是filter,而不是ANTI算法,所以效率就差一些。
所以我們可以得出結(jié)論:在oracle 10g中,如果可以確保非空,則in約束可以用到ANTI的半連接算法,這時候的效率和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)兩個語句的效率是一樣的,查看執(zhí)行計劃也是一樣的。原來oracle在11g中已經(jīng)做了優(yōu)化,所以in和exist的效率是一樣的。
由此我們可以得出結(jié)論,在11g中,使用in和exist的效率是一樣的,因為他們走的都是比較高效的ANTI算法。
三、關(guān)于大小表的連接順序
在網(wǎng)上我們可以看到很多這樣的文章,在進行多表查詢的時候,用小表或者交叉表做基礎(chǔ)表,放在后面,大表放在from后面的位置,因為表的訪問順序是從右往左的。
但是真的是這樣的嗎?
我們可以做實驗驗證一下(此處測試環(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í)行計劃可以發(fā)現(xiàn),這兩個語句的效率是一樣的,難道多表查詢,表的順序和效率無關(guān)嗎?
我們在執(zhí)行下面的語句:
select /*+rule*/ count(*) from tab_big,tab_small ;
select /*+rule*/ count(*) from tab_small,tab_big ;
我們可以清楚的發(fā)現(xiàn),小表在右,大表在左的語句,查詢效率高很多。
其實,在基于規(guī)則時代,查詢效率是和表的連接順序相關(guān)的,小表或者交叉表在左,大表在右的執(zhí)行效率會高一些。但是現(xiàn)在基本上是基于代價的時代,所以大小表的順序和效率無關(guān),oracle優(yōu)化器會自動去進行效率優(yōu)化。
四、where子句中的連接條件順序
在基于規(guī)則時代,oracle采用自下而上的順序來解析where子句,根據(jù)這個原理,我們一般會將可能返回行數(shù)最少的表放在最后面,where子句中有過濾條件的子句放在最后面。
但是在現(xiàn)在基于代價時代,這種優(yōu)化都有oracle優(yōu)化器幫忙優(yōu)化了,所以關(guān)于表的順序和條件的順序已經(jīng)不會影響我們的查詢效率了。
您可能感興趣的文章:- 分析MySQL中優(yōu)化distinct的技巧
- mysql in語句子查詢效率慢的優(yōu)化技巧示例
- MySQL查詢優(yōu)化:連接查詢排序limit(join、order by、limit語句)介紹
- MySQL優(yōu)化之使用連接(join)代替子查詢
- SQL語句優(yōu)化之JOIN和LEFT JOIN 和 RIGHT JOIN語句的優(yōu)化
- MySQL中對于not in和minus使用的優(yōu)化
- 關(guān)于mysql中innodb的count優(yōu)化問題分享
- MySQL中insert語句的使用與優(yōu)化教程
- SQL優(yōu)化教程之in與range查詢