在SQL Server中為了查詢性能的優化有時我們就需要對數據表通過建立索引的方式目的主要是根據查詢要求迅速縮小查詢范圍避免全表掃描
索引有兩種類型分別是聚集索引(clustered index也稱聚類索引簇集索引)和非聚集索引(nonclustered index也稱非聚類索引非簇集索引)
聚集索引在一個表中只能有一個默認情況下在主鍵建立的時候創建它是規定數據在表中的物理存儲順序我們也可以取消主鍵的聚集索引所以必須考慮數據庫可能用到的查詢類型以及使用的最為頻繁的查詢類型對其最常用的一個字段或者多個字段建立聚集索引或者組合的聚集索引它就是SQL Server會在物理上按升序(默認)或者降序重排數據列這樣就可以迅速的找到被查詢的數據
非聚集索主要是數據存儲在一個地方索引存儲在另一個地方索引帶有指針指向數據的存儲位置索引中的項目按索引鍵值的順序存儲而表中的信息按另一種順序存儲可以在一個表格中使用高達個非聚集的索引在查詢的過程中先對非聚集索引進行搜索找到數據值在表中的位置然後從該位置直接檢索數據這使非聚集索引成為精確匹配查詢的最佳方法因為索引包含描述查詢所搜索的數據值在表中的精確位置的條目
所以我們在選擇創建聚集索引的時候要注意以下幾個方面
) 對表建立主鍵時就會為主鍵自動添加了聚集索引如自動編號字段而我們沒有必要把聚集索引浪費在主鍵上除非你只按主鍵查詢所以會把聚集索引設置在按條件查詢頻率最高的那個字段或者組合的字段
) 索引的建立要根據實際應用的需求來進行並非是在任何字段上建立索引就能提高查詢速度聚集索引建立遵循下面幾個原則
包含大量非重復值的列
使用下列運算符返回一個范圍值的查詢BETWEEN>>=< 和 <=
被連續訪問的列
返回大型結果集的查詢
經常被使用聯接或 GROUP BY 子句的查詢訪問的列;一般來說這些是外鍵列對ORDER BY 或 GROUP BY 子句中指定的列進行索引可以使 SQL Server 不必對數據進行排序因為這些行已經排序這樣可以提高查詢性能
OLTP 類型的應用程序這些程序要求進行非常快速的單行查找(一般通過主鍵)應在主鍵上創建聚集索引
舉例來說銀行交易日志中對交易日期建立聚合索引數據物理上按順序存於數據頁上重復值也排列在一起因而在范圍查找時可以先找到這個范圍的起末點且只在這個范圍內掃描數據頁避免了大范圍掃描提高了查詢速度而如果我們對員工的基本信息表中性別的字段列上建立聚集索引就完全沒有必要因為內容裡只涉及到 男與女兩個不同值
) 在聚集索引中按常用的組合字段建立索引形成復合索引一般在為表建立多個主鍵的時候就會產生如果一個表中的數據在查詢時有多個字段總是同時出現則這些字段就可以作為復合索引這樣能形成索引覆蓋提高where語句的查詢效率
)索引對查詢有一這的優化但由於改變一個表的內容將會引起索引的變化頻繁的對數據操作如insertupdatedelete語句將導致系統花費較大的代價進行索引更新引起整體性能的下降一般來講在對查詢性能的要求高於對數據維護性能要求時應該盡量使用索引有時在這種操作數據庫比較頻繁的某些極端情況下可先刪除索引再對數據庫表更新大量數據最後再重建索引新建立的索引總是比較好用
索引在使用了長久的時候就會產生很多的碎片查詢的性能就會受到影響這時候有兩種方法解決一是利用DBCC INDEXDEFRAG整理索引碎片還有就是利用DBCC DBREINDEX重建索引
DBCC INDEXDEFRAG 命令是聯機操作所以索引只有在該命令正在運行時才可用而且可以在不丟失已完成工作的情況下中斷該操作這種方法的缺點是在重新組織數據方面沒有聚集索引的除去/重新創建操作有效
重新創建聚集索引將對數據進行重新組織其結果是使數據頁填滿填滿程度可以使用 FILLFACTOR 選項進行配置這種方法的缺點是索引在除去/重新創建周期內為脫機狀態並且操作屬原子級如果中斷索引創建則不會重新創建該索引
我們來看看索引重建使用的方法
語法 DBCC DBREINDEX ( [ TableName [ index_name [ fillfactor ] ] ] )
參數 TableName
是要重建其指定的索引的表名數據庫所有者和表名必須符合標識符的規則有關更多信息請參見使用標識符如果提供 database 或 owner 部分則必須使用單引號 ()
將整個 databaseownertable_name 括起來如果只指定 table_name則不需要單引號
index_name 是要重建的索引名索引名必須符合標識符的規則如果未指定 index_name 或指定為 就要對表的所有索引進行重建
fillfactor 是創建索引時每個索引頁上要用於存儲數據的空間百分比fillfactor替換起始填充因子以作為索引或任何其它重建的非聚集索引(因為已重建聚集索引)的新默認值如果 fillfactor 為 DBCC DBREINDEX 在創建索引時將使用指定的起始fillfactor
我們在查詢分析器中輸入如下的命令
DBCC DBREINDEX (MyTable)
這樣就會索引重建了
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22210.html
