SQL Server索引優化系列之二：索引效能考慮 (轉)

在前面說過了索引能極大的提高資料的檢索速度，那為什麼不在每一個列上建索引呢？初學者可能會困惑這個問題，而且通常不知道哪些列該建索引，哪些不該建， 甚至於會把like模糊查詢的列也作為索引列，其實like是不使用索引的，只有等於,大於,IN等操作符會使用索引。SQLSERVER對於資料的插入，更新和刪除，都要更新相應的索引。這無疑會大大增加更新時間。另外，如果某個資料頁已滿，這時如果要在該頁插入資料時，就會造成頁分裂產生碎片(後面還會說到)，而影響效能。所以僅當查詢的效能比更新的效能更重要時才建索引。

考慮建索引的列

1. 主鍵
2. 外來鍵
3. 頻繁檢索的列和按排序順序頻繁檢索的列

通常where 後面的條件引用的列都是考慮建索引的列，模糊查詢除外(如like查詢)
不考慮建索引的列

1.很少或從來不在查詢中引用的列
2.只有兩個或若干個值的列(比如只有男和女兩個值的列)
3.小表(行數很少的表，這時候SQL SERVER花費在索引上的時間比直接掃描表的時間還更長)

SQL SERVER對於建立索引的列，都要付出一定的代價來維護這個索引。另外SQLSERVER會自動分析是否使用該列的索引，比如對於只有男和女兩個值的列，如果給它建立索引，SQLSERVER自行分析後，會認為改列使用索引查詢的效率不大，因為返回結果集的百分比比較大，於是SQLSERVER會將統計資料記錄下來，當下次查詢該列時，就會根據該統計資料來決定是否要使用改列的索引。

對於返回結果集百分比比較大的列(比如有100萬的資料，而查詢的結果將返回50萬)，SQLSERVER就可能不會使用該列上的索引，而採用全表掃描的方法。可自行測試，插入2000條資料，有1999條資料是一樣的，比如ForumID為2的有1999條，ForumID為3的只有一條，這時使用

SET SHOWPLAN_TEXT ON –顯示執行計劃，可檢視查詢語句使用了哪些索引
GO

SELECT * FROM Posts WHERE ForumID=2
會發現沒有使用ForumID列的索引。

SELECT * FROM Posts WHERE ForumID=3
則使用了ForumID列的索引

進行大批量插入或更新應先刪除索引最後再重建索引，避免每插入或更新一條資料時都要更新相應的索引，而影響更新速度。
複合索引(指兩列或多列組成的索引，通常where後面由多個列組成的條件時，可以把這些列建成一個複合索引)

1) 只有當WHERE子句中指定索引鍵的第一列時才使用該索引。
例子:
CREATE INDEX Posts_INDEX
ON Posts(ThreadID,ForumID)

如果SELECT * FROM Posts WHERE ForumID=2 則查詢不會使用Posts_INDEX索引
而 SELECT * FROM Posts WHERE ThreadID=10 則會使用Posts_INDEX索引

2) 索引不應過大(<= 8個位元組為最好，int型相當於4個位元組，SmallInt相當於2個位元組)。
3) 首先定義最具唯一性的列(順序不一樣，索引是不一樣的)
比如:A列有30%的資料是重複的，B列有10%的列是重複的，C列有25%的資料是重複的，這時候建立索引的列的順序應當是 B C A

建立索引還有一個比較重要的選項:填充因子。下一篇繼續。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-675092/，如需轉載，請註明出處，否則將追究法律責任。