在SQL Server中使用索引的技巧

在SQL Server中，為了查詢效能的最佳化，有時我們就需要對資料表通過建立索引的方式，目的主要是根據查詢要求，迅速縮小查詢範圍，避免全表掃描。

    
    索引有兩種類型，分別是叢集索引(clustered index，也稱聚類索引、簇集索引)和非叢集索引(nonclustered index，也稱非聚類索引、非簇集索引)。

　　叢集索引在一個表中只能有一個，預設情況下在主鍵建立的時候建立，它是規定資料在表中的實體儲存體順序，我們也可以取消主鍵的叢集索引，所以必須考慮資料庫可能用到的查詢類型以及使用的最為頻繁的查詢類型，對其最常用的一個欄位或者多個欄位建立叢集索引或者組合的叢集索引，它就是SQL Server會在物理上按升序(預設)或者降序重排資料列，這樣就可以迅速的找到被查詢的資料。

　　非聚集索主要是資料存放區在一個地方，索引儲存在另一個地方，索引帶有指標指向資料的儲存位置。索引中的項目按索引索引值的順序儲存，而表中的資訊按另一種順序儲存。可以在一個表格中使用高達249個非聚集的索引，在查詢的過程中先對非叢集索引進行搜尋，找到資料值在表中的位置，然後從該位置直接檢索資料。這使非叢集索引成為精確匹配查詢的最佳方法，因為索引包含描述查詢所搜尋的資料值在表中的精確位置的條目。

　　所以我們在選擇建立叢集索引的時候要注意以下幾個方面：

　　1) 對錶建立主鍵時，就會為主鍵自動添加了叢集索引，如自動編號欄位，而我們沒有必要把叢集索引浪費在主鍵上，除非你只按主鍵查詢，所以會把叢集索引設定在按條件查詢頻率最高的那個欄位或者組合的欄位。

　　2) 索引的建立要根據實際應用的需求來進行，並非是在任何欄位上建立索引就能提高查詢速度。叢集索引建立遵循下面幾個原則：

　　l 包含大量非重複值的列。

　　l 使用下列運算子返回一個範圍值的查詢：BETWEEN、>、>=、< 和 <=。

　　l 被連續訪問的列。

　　l 返回大型結果集的查詢。

　　l 經常被使用聯結或 GROUP BY 子句的查詢訪問的列;一般來說，這些是外鍵列。對ORDER BY 或 GROUP BY 子句中指定的列進行索引，可以使 SQL Server 不必對資料進行排序，因為這些行已經排序。這樣可以提高查詢效能。

　　l OLTP 類型的應用程式，這些程式要求進行非常快速的單行尋找(一般通過主鍵)。應在主鍵上建立叢集索引。

　　舉例來說，銀行交易日誌中對交易日期建立彙總索引，資料物理上按順序存於資料頁上，重複值也排列在一起，因而在範圍尋找時，可以先找到這個範圍的起末點，且只在這個範圍內掃描資料頁，避免了大範圍掃描，提高了查詢速度。而如果我們對員工的基本資料表中性別的欄位列上建立叢集索引，就完全沒有必要，因為內容裡只涉及到 “男”與“女”兩個不同值。

　　3) 在叢集索引中按常用的組合欄位建立索引，形成複合索引，一般在為表建立多個主鍵的時候就會產生，如果一個表中的資料在查詢時有多個欄位總是同時出現則這些欄位就可以作為複合索引,這樣能形成索引覆蓋,提高where語句的查詢效率。

　　4)索引對查詢有一這的最佳化，但由於改變一個表的內容，將會引起索引的變化。頻繁的對資料操作如insert,update,delete語句將導致系統花費較大的代價進行索引更新，引起整體效能的下降。一般來講，在對查詢效能的要求高於對資料維護效能要求時，應該盡量使用索引，有時在這種操作資料庫比較頻繁的某些極端情況下，可先刪除索引，再對資料庫表更新大量資料，最後再重建索引，建立立的索引總是比較好用。

　　索引在使用了長久的時候，就會產生很多的片段，查詢的效能就會受到影響，這時候有兩種方法解決，一是利用DBCC INDEXDEFRAG整理索引片段，還有就是利用DBCC DBREINDEX重建索引。

　　DBCC INDEXDEFRAG 命令是聯機操作，所以索引只有在該命令正在運行時才可用。而且可以在不丟失已完成工作的情況下中斷該操作。這種方法的缺點是在重新組織資料方面沒有叢集索引的除去/重新建立操作有效。

　　重新建立叢集索引將對資料進行重新組織，其結果是使資料頁填滿。填滿程度可以使用 FILLFACTOR 選項進行配置。這種方法的缺點是索引在除去/重新建立周期內為離線狀態，並且操作屬原子級。如果中斷索引建立，則不會重新建立該索引。

　　我們來看看索引重建使用的方法：

　　文法 DBCC DBREINDEX ( [ ‘TableName’ [ , index_name [ , fillfactor ] ] ] )

　　參數 ‘TableName’

　　是要重建其指定的索引的表名。資料庫、所有者和表名必須符合標識符的規則。有關更多資訊，請參見使用標識符。如果提供 database 或 owner 部分，則必須使用單引號 (‘)

　　將整個 database.owner.table_name 括起來。如果只指定 table_name，則不需要單引號。

　　index_name 是要重建的索引名。索引名必須符合標識符的規則。如果未指定 index_name 或指定為 ‘ ‘，就要對錶的所有索引進行重建。

　　fillfactor 是建立索引時每個索引頁上要用於儲存資料的空間百分比。fillfactor

　　替換起始填滿因數以作為索引或任何其它重建的非叢集索引(因為已重建叢集索引)的新默

　　認值。如果 fillfactor 為 0，DBCC DBREINDEX 在建立索引時將使用指定的起始fillfactor。

　　我們在查詢分析器中輸入如下的命令：

　　DBCC DBREINDEX (‘MyTable,’’,80)

　　這樣就會索引重建了。