SQL Server 索引使用和最佳化

索引的使用說明

題外話：
     昨天比較鬱悶，維護一客戶的資料庫系統時候，發現系統處理資料就出現假死。
開始因為是軟體問題，拷得亂忙一下午還沒有拷定，後來一直弄到晚上7點，突然想起索引問題。
後來就使用了一命令：

dbcc dbreindex('TableName','',80)

即可實現。比較鬱悶和暈，自己犯這樣的錯誤。
     這錯誤，正好是我想寫這帖的原因，希望能為大家解決一些實際問題吧。

正題：
為什麼要建立合適的索引，重要的目的就是提高查詢效率。
這裡簡單說一下索引的兩種類型：

協助的說明 聚集
叢集索引基於資料行的索引值在表內排序和儲存這些資料行。由於資料行按基於叢集索引鍵的排序次序儲存，因此叢集索引對尋找行很有效。每個表只能有一個叢集索引，因為資料行本身只能按一個順序儲存。資料行本身構成叢集索引的最低層級。
只有當表包含叢集索引時，表內的資料行才按排序次序儲存。如果表沒有叢集索引，則其資料行按堆集方式儲存。
非聚集
非叢集索引具有完全獨立於資料行的結構。非叢集索引的最低行包含非叢集索引的索引值，並且每個索引值項都有指標指向包含該索引值的資料行。資料行不按基於非聚集鍵的次序儲存。
在非叢集索引內，從索引行指向資料行的指標稱為行定位器。行定位器的結構取決於資料頁的儲存方式是堆集還是聚集。對於堆集，行定位器是指向行的指標。對於有叢集索引的表，行定位器是叢集索引鍵。

來的例子說明最能體現建立索引的好處。

 --測試表DTest (無索引)
SELECT IDENTITY(int,1,1) AS [id],B.[id] AS TKey,B.[Name] AS [TName] INTO DTest 
FROM Master..syscolumns AS A ,Master..sysobjects AS B
--測試表TTest (建立索引)
SELECT IDENTITY(int,1,1) AS [id],B.[id] AS TKey,B.[Name] AS [TName] INTO TTest 
FROM Master..syscolumns AS A ,Master..sysobjects AS B

在我的測試中，DTest，TTest表有500多萬條記錄，可以滿足測試要求。

操作
1、設定表TTest的欄位ID為主鍵。
 

2、對錶TTest中的列TName建立一個非叢集索引。
 

 

3、測試查詢語句的效率。
在查詢分析器中輸入：

SELECT * FROM  DTest WHERE TName='sp_ActiveDirectory_Obj'

按鍵盤Ctrl+L，顯示預計的執行計畫，可以查看執行效率。
 

預計的成本是31.8.

再在查詢分析器中輸入：SELECT * FROM  TTest WHERE TName='sp_ActiveDirectory_Obj'

按鍵盤Ctrl+L，顯示預計的執行計畫，可以查看執行效率。
 

預計的成本是28.5 .
從這圖可以看出，使用索引後，查詢效率差不多提高了10的百分點。

下面我們再看實際查詢的結果用時：
沒有索引時候，用時：26秒。

 

 

對列Tname加索引，用時：0秒。

呵呵，明顯看出效率的提高。

這裡寫幾點注意：
1、將更新儘可能多的行的查詢寫入單個語句內，而不要使用多個查詢更新相同的行。僅使用一個語句，就可以利用最佳化的索引維護。
2、對叢集索引使用整型鍵。另外，在唯一列、非空列或 IDENTITY 列上建立叢集索引可以獲得效能收益
3、在查詢經常用到的所有列上建立非叢集索引。這可以最大程度地利用隱蔽查詢
4、重建索引可以重新組織索引資料（對於叢集索引還包括表資料)的儲存，清除片段。如我開始提到的使用

 dbcc dbreindex('TableName','',80)

來重構索引。

在實際的應用中，會發現很多問題，得具體問題具體分析，這裡只是簡單一個例子說明使用索引的好處。