應用索引技術優化SQL 語句(Part 3)

六、有關索引的幾個問題

 

問題1，是否值得在identity欄位上建立聚集索引。答案取決於identity 欄位如何在語句中使用。如果你經常根據該欄位搜尋返回很少的行，那麼在其上建立索引是值得的。反之如果identity欄位根本很少在語句中使用，那麼就不應該對其建立任何索引。

 

問題2，一個表應該建立多少索引合適。如果表的80％以上的語句都是讀操作，那麼索引可以多些。但是不要太多。特別是不要對那些更新頻繁的表其建立很多的索引。很少表有超過5個以上的索引。過多的索引不但增加其佔用的磁碟空間，也增加了SQL Server 維護索引的開銷。

 

問題4：為什麼SQL Server 在執行計劃中沒有使用你認為應該使用的索引？原因是多樣的。一種原因是該語句返回的結果超過了表的20％資料，使得SQL Server 認為scan比seek更有效。

 

另一種原因可能是表欄位的statistics過期了，不能準確反映資料的分佈情況。你可以使用命令UPDATE STATISTICS tablename with FULLSCAN來更新它。只有同步的準確的statistics才能保證SQL Server 產生正確的執行計劃。過時的老的statistics常會導致SQL Server生成不夠優化的甚至愚蠢的執行計劃。所以如果你的表頻繁更新，而你又覺得和之相關的SQL語句執行緩慢，不妨試試UPDATE STATISTIC with FULLSCAN 語句。

 

你甚至可以使用Index hint比較不同索引的效能差異。比如對上面script. 4提到的兩個索引，可以這樣比較，

 

select 學生姓名, 入學時間 from tbl1 with (index= idx_年齡入學時間)

where  ……

或者：

select 學生姓名, 入學時間 from tbl1 with (index= idx_入學時間年齡)

where  ……

 

如果強制使用你的索引後logical reads大大減少，那麼就需要進一步研究為什麼SQL Server 不使用正確的索引。注意，不要總是將使用索引等同於好的效能，反之亦然。SQL Server只在索引能提高效能時才使用索引檢索。有時候使用Table scan的效能比使用某個索引反而要好。

 

問題5、什麼使用聚集索引，什麼時候使用非聚集索引

 

在SQL Server 中索引有聚集索引和非聚集索引兩種。它們的主要差別是前者的索引葉子就是資料本身，而後者的葉子節點包含的是指向資料的書籤（即資料行號或聚集索引的key）。

 

在上面的例子中我全部使用非聚集索引，原因是對一個表而言聚集索引只能有一個，而非聚集索引可以有多個。如果你把上面例子中的非聚集索引換成聚集索引，效果也是類似的，只是聚集索引沒有Bookmark Lookup操作。什麼時候應該使用聚集索引，什麼時候使用非聚集索引取決於應用程式的訪問模式。我的建議是在那些關鍵的欄位上使用聚集索引。一個表一般都需要建立一個聚集索引。對於什麼時候使用聚集索引，SQL Server 2000聯機手冊中有如下描述：

 

在建立聚集索引之前，應先了解您的資料是如何被訪問的。可考慮將聚集索引用於：

• 包含大量非重複值的列。
• 使用下列運算子返回一個範圍值的查詢：BETWEEN、>、>=、< 和 <=。
• 被連續訪問的列。
• 返回大型結果集的查詢。
• 經常被使用聯接或 GROUP BY 子句的查詢訪問的列；一般來說，這些是外來鍵列。對 ORDER BY 或 GROUP BY 子句中指定的列進行索引，可以使 SQL Server 不必對資料進行排序，因為這些行已經排序。這樣可以提高查詢效能。
• OLTP 型別的應用程式，這些程式要求進行非常快速的單行查詢（一般通過主鍵）。應在主鍵上建立聚集索引。

 

聚集索引不適用於：

• 頻繁更改的列

這將導致整行移動（因為 SQL Server 必須按物理順序保留行中的資料值）。這一點要特別注意，因為在大資料量事務處理系統中資料是易失的。

• 寬鍵

來自聚集索引的鍵值由所有非聚集索引作為查詢鍵使用，因此儲存在每個非聚集索引的葉條目內。

 

七、結束語

 

如何使一個效能緩慢的系統執行更快更高效，不但需要整體分析資料庫系統，找出系統的效能瓶頸，更需要優化資料庫系統發出的SQL 語句。一旦找出關鍵的SQL 語句並加與優化，效能問題就會迎刃而解。讀完本文，你應該知道建立索引的關鍵是什麼，以及如何分析SQL語句的執行計劃來建立索引。在優化了索引後大部分資料庫系統的效能都能夠得到不同程度的改善，有的系統甚至能夠獲得好幾倍以上的效能提升。本文並不能解決你在優化SQL語句中碰到的所有問題，但其中討論的內容或技巧對許多效能問題都有一定的參考意義。