SQL最佳化之count，表的串連順序、條件順序，in和exist，countexist

SQL最佳化之count，表的串連順序、條件順序，in和exist，countexist

一、關於count

看過一些關於count(*)和count(列)的文章，count(列)的效率一定比count(*)高嗎？

其實個人覺得count(*)和count(列)根本就沒有可比性，count(*)統計的是表裡面的總條數，而count(列)統計的是當列的非空記錄條數。

不過我們可以通過實驗來比較一下：

首先建立測試表：

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 

執行

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

發現耗時是一樣的，難道他們的效率其實是一樣的嗎？

我們在列object_id上建立索引試試看

create index idx_object_id on test(object_id);

然後再執行 

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

發現count(object_id)的速度明顯比count(*)高出一大截，難道是因為count(object_id)能用到索引，所以效率才提高了很多？

我們再修改下object_id的列屬性

alter table test modify object_id  not  null;

然後再執行 

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

發現其實他們的速度是一樣快的，count(*)也可用到索引。

其實效率比較的前提是兩個語句的寫法要等價，這兩種寫法根本就不等價，因此不具有可比性。

對於oracle最佳化器來說，我們可以通過實驗發現，count不同的列，統計的時間是不一樣的，大致趨勢是列越靠後，訪問的開銷越大，列的位移量絕地訪問的效能。而count(*)的開銷與位移量無關。因此，在某些場合count(*)反而是最快的。

二、關於in和exist

關於in和exist的說法大都是說in的效率比exist高，所以有in的地方必需得換成exist等等。但是真的是這樣的嗎？

我們可以做個實驗：

在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) ;

我們發現，exist確實比in的效率高啊。這個說法貌似是成立的啊。

但是我們再執行下面的語句

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

你會發現加上非空的約束條件後，in和exist的效率是一樣的。

查看三個語句的執行計畫你就會發現，沒有加上非空約束的in語句和exist語句走的都是ANTI半串連演算法，所以效率是一樣的，而未加非空約束的in語句用的是filter，而不是ANTI演算法，所以效率就差一些。

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

在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) ;

我們發現兩個語句的效率是一樣的，查看執行計畫也是一樣的。原來oracle在11g中已經做了最佳化，所以in和exist的效率是一樣的。

由此我們可以得出結論，在11g中，使用in和exist的效率是一樣的，因為他們走的都是比較高效的ANTI演算法。

三、關於大小表的串連順序

在網上我們可以看到很多這樣的文章，在進行多表查詢的時候，用小表或者交叉表做基礎資料表，放在後面，大表放在from後面的位置，因為表的訪問順序是從右往左的。

但是真的是這樣的嗎？

我們可以做實驗驗證一下（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   ;

我們查看執行計畫可以發現，這兩個語句的效率是一樣的，難道多表查詢，表的順序和效率無關嗎？

我們在執行下面的語句：

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

我們可以清楚的發現，小表在右，大表在左的語句，查詢效率高很多。

其實，在基於規則時代，查詢效率是和表的串連順序相關的，小表或者交叉表在左，大表在右的執行效率會高一些。但是現在基本上是基於代價的時代，所以大小表的順序和效率無關，oracle最佳化器會自動去進行效率最佳化。

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

在基於規則時代，oracle採用自下而上的順序來解析where子句，根據這個原理，我們一般會將可能返回行數最少的表放在最後面，where子句中有過濾條件的子句放在最後面。

但是在現在基於代價時代，這種最佳化都有oracle最佳化器幫忙最佳化了，所以關於表的順序和條件的順序已經不會影響我們的查詢效率了。

sql server 多表串連最佳化，大表放在前邊好些，還是後邊？where條件放前邊表關聯那裡會不會快些？

sql server 不像Oracle，它的查詢是自己分析並決定先後的，所以你不用考慮where的先後。
但是有一些where條件的最佳化還是要考慮的，比如 =或exists 比 in 效率高等，具體你可以上網查。

SQL有一個功能叫“顯示估計的執行計畫”（在分析的藍色勾右面，仔細找便可找到）
它會幫你分析執行計畫，並給出相關的最佳化建議
你也可以根據分析結果，相應建INDEX等方式最佳化查詢。
 
sql串連查詢語句中from子句中表的順序有什要

子句，
從左向右寫即可，如：
select W.* from wc W left join cw C on W.number = C.number;