SQL Server的複合索引學習【轉載】

概要
什麼是單一索引,什麼又是複合索引呢? 何時建立複合索引，複合索引又需要注意些什麼呢？本篇文章主要是對網上一些討論的總結。

一.概念

單一索引是指索引列為一列的情況,即建立索引的語句只實施在一列上。

使用者可以在多個列上建立索引，這種索引叫做複合索引(複合式索引)。複合索引的建立方法與建立單一索引的方法完全一樣。但複合索引在資料庫操作期間所需的開銷更小，可以代替多個單一索引。當表的行數遠遠大於索引鍵的數目時，使用這種方式可以明顯加快表的查詢速度。

同時有兩個概念叫做窄索引和寬索引，窄索引是指索引列為1-2列的索引,如果不特殊說明的話一般是指單一索引。寬索引也就是索引列超過2列的索引。

設計索引的一個重要原則就是能用窄索引不用寬索引，因為窄索引往往比複合式索引更有效。擁有更多的窄索引，將給最佳化程式提供更多的選擇餘地，這通常有助於提高效能。

二.使用

建立索引 
create index idx1 on table1(col1,col2,col3)  
查詢
select * from table1 where col1= A and col2= B and col3 = C

這時候查詢最佳化工具，不在掃描表了，而是直接的從索引中拿資料，因為索引中有這些資料，這叫覆蓋式查詢，這樣的查詢速度非常快。   

三.注意事項

1.何時是用複合索引
在where條件中欄位用索引，如果用多欄位就用複合索引。一般在select的欄位不要建什麼索引(如果是要查詢select col1 ,col2, col3 from mytable,就不需要上面的索引了)。根據where條件建索引是極其重要的一個原則。注意不要過多用索引，否則對錶更新的效率有很大的影響，因為在動作表的時候要化大量時間花在建立索引中.

2.對於複合索引，在查詢使用時，最好將條件順序按找索引的順序，這樣效率最高。如：  
  IDX1:create   index   idx1   on   table1(col2,col3,col5)  
  select   *   from   table1   where   col2=A   and   col3=B   and   col5=D  

如果是"select   *   from   table1   where   col3=B   and   col2=A   and   col5=D"
或者是"select   *   from   table1   where   col3=B"將不會使用索引，或者效果不明顯

3.複合索引會替代單一索引麼?
很多人認為只要把任何欄位加進叢集索引，就能提高查詢速度，也有人感到迷惑：如果把複合的叢集索引欄位分開查詢，那麼查詢速度會減慢嗎？帶著這個問題，我們來看一下以下的查詢速度（結果集都是25萬條資料）：（日期列fariqi首先排在複合叢集索引的起始列，使用者名稱neibuyonghu排在後列）

IDX1:create   index   idx1   on   Tgongwen(fariqi,neibuyonghu)  

（1）select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi>'2004-5-5'

查詢速度：2513毫秒

（2）select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi>'2004-5-5' and neibuyonghu='辦公室'

查詢速度：2516毫秒

（3）select gid,fariqi,neibuyonghu,title from Tgongwen
where neibuyonghu='辦公室'

查詢速度：60280毫秒

從以上實驗中，我們可以看到如果僅用叢集索引的起始列作為查詢條件和同時用到複合叢集索引的全部列的查詢速度是幾乎一樣的，甚至比用上全部的複合索引列還要略快（在查詢結果集數目一樣的情況下）；而如果僅用複合叢集索引的非起始列作為查詢條件的話，這個索引是不起任何作用的。當然，語句1、2的查詢速度一樣是因為查詢的條目數一樣，如果複合索引的所有列都用上，而且查詢結果少的話，這樣就會形成“索引覆蓋”，因而效能可以達到最優。同時，請記住：無論您是否經常使用彙總索引的其他列，但其前置列一定要是使用最頻繁的列。

[參考: 查詢最佳化及分頁演算法方案 http://blog.csdn.net/chiefsailor/archive/2007/05/28/1628339.aspx]

4.需要在同一列上同時建單一索引和複合索引麼?
實驗: sysbase   5.0   表table1   欄位：col1,col2,col3  

實驗步驟：  
（1）建立索引idx1   on   col1  
  執行select   *   from   table1   where   col1=A     使用idx1  
  執行select   *   from   table1   where   col1=A   and   col2=B   也使用idx1  

（2）刪除索引idx1,然後建立idx2   on   (col1,col2)複合索引  
  執行以上兩個查詢，也都使用idx2  

（3）如果兩個索引idx1,idx2都存在  
  並不是   where   col1='A'用idx1；where   col1=A   and   col2=B  用idx2。  
  其查詢最佳化工具使用其中一個以前常用索引。要麼都用idx1,要麼都用idx2.  
   
由此可見，
（1）對一張表來說，如果有一個複合索引 on   (col1,col2)，就沒有必要同時建立一個單索引 on col1。
（2）如果查詢條件需要，可以在已有單索引 on col1的情況下，添加複合索引on   (col1,col2)，對於效率有一定的提高。
（3）同時建立多欄位(包含5、6個欄位)的複合索引沒有特別多的好處，相對而言，建立多個窄欄位(僅包含一個，或頂多2個欄位)的索引可以達到更好的效率和靈活性。



5. 一定需要覆蓋性查詢嗎？
通常最好不要採用一個強調完全覆蓋查詢的策略。如果Select子句中的所有列都被一個非群集索引覆蓋，最佳化程式會識別出這一點，並提供很好的效能。不過，這通常會導致索引過寬，並會過度依賴於最佳化程式使用該策略的可能性。通常，是用數量更多的窄索引，這對於大量查詢來說可以提供更好的效能。