SQL Server 使用索引來對數據訪問進行優化
以下的文章主要描述的是SQL Server 使用索引來實現數據訪問優化的實際操作步驟，我前兩天在相關網站看見SQL Server 使用索引來實現數據訪問優化的實際操作步驟的資料，覺得挺好，就拿出來供大家分享。
第一步：在列上采用正確的索引
有些人可能爭論實施正確的索引是否是數據庫優化過程的第一步。但是我認為在數據庫應用正確的索引是第一位的。原因有兩點：
1.在一個產品系統里，它將使你在很快的時間內提高盡可能大的性能。
2.創建數據庫索引不需要你做任何的系統修改，因此不需要任何重新編譯和部署
假如你發現有當前的數據庫沒有很好的處理索引，你建了索引，結果就是性能的快速提升。
（1）首先，創建了聚焦索引（即主鍵），接著數據頁在物理文件里按照主鍵的值被排序。
（2）其次，在非主鍵列上創建一個非聚焦索引，將該列和主鍵綁定在一起，但是要按照該列進行排序。
策略：
（1）確保數據庫的每個表有一個主健，這么做會確保每個表有一個聚焦索引，通過主健的值，表的數據頁通按物理順序排列在磁盤上。所以任何使用主健的數據檢索操作,任何在主健字段的排序操作都能非常迅速的檢索數據。
（2）在這些列上創建非聚焦索引：A： 經常被作為搜索憑證的列； B：用來聯合其它表的列； C：用來作為外健的列；D：用來排序的列；E：高選擇性列； F：Xml類型
下面是一個創建索引的命令的例子：
- CREATE INDEX NCLIX_OrderDetails_ProductID ON dbo.OrderDetails(ProductID)
你也可以使用SQL Server控制臺在需要的列上創建索引。
第二步：創建正確的復合索引
下面是創建復合索引的例子：
- CREATE INDEX NCLIX_Sales_ProductID ON dbo.Sales(ProductID) INCLUDE(SalesDate,SalesPersonID)
請注重，創建復合索引應當包含少數幾個列，并且這些列經常在select查詢里使用。在復合索引里包含太多的列不僅不會給你帶來太多好處。而且由于使用相當多的內存來存儲復合索引的列的值，其后果是內存溢出和性能降低。
第三步：假如有碎片發生，重新整理它
1、什么是索引碎片
索引碎片是這樣一種情形：由于在表里大量的插入、修改、刪除操作而使索引頁分裂。假如索引有了高的碎片，有兩種情況，一種情況是掃描索引需要花費很多的時間，另一種情況是在查詢的時候索引根本不SQL Server 使用索引,都會導致性能降低。
2、有兩種類型的碎片：
內部破碎：由于索引頁里的數據插入或修改操作而發生，以數據作為稀疏矩陣的形式的分布而結束，這將導致數據頁的增加，從而增加查詢時間。
外部破碎：由于索引/數據頁的數據插入或修改而發生，以頁碼分離和在文件系統里不連貫的新的索引頁的分配而結束，數據庫服務器不能利用預讀操作的優點，因為：下一個相關聯的數據頁不臨近，而且這些相關連的下面的頁碼可能在數據文件的任何地方。
3、如何知道索引破碎是否已經發生?
在數據庫執行下面的SQL語句(下面的語句在SQLserver2005及以后的版本運行正常，以你的目標數據庫的名字取代AdventureWorks)
- SELECT object_name(dt.object_id) Tablename,si.name IndexName,dt.avg_fragmentation_in_percent AS ExternalFragmentation,
- dt.avg_page_space_used_in_percent AS InternalFragmentation
- FROM
- (
- SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
- FROM sys.dm_db_index_physical_stats(db_id('AdventureWorks'),null,null,null,'DETAILED')
- WHERE index_id<>0
- )
- AS dt INNER JOIN sys.indexes as si ON si.object_id=dt.object_id
- AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
- AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC
分析查詢結果，你就能發現在哪里出現了索引碎片，應用下面的規則：A ：ExternalFragmentation的值>10，預示對應的索引出現外部碎片。B ： InternalFragmentation的值<75，預示對應的索引出現內部碎片。
4、什么時候重組和重建索引?
當外部碎片的值在10-15，內部碎片的值在60-75，對于這樣的索引，你應該重組索引。否則，你應該重建索引。
關于索引重建的一個重要的事情是：一旦在一個特定的表上重建索引，表就會被鎖定(重組的時候不會發生)。所以，對于一個產品數據庫的一個大的表，因為在一個大表上的索引重建往往需要花費數個小時，我們不希望這種鎖定。幸運的是，在SQL2005有一個解決方法，你可以在重建一個表的索引的時候，把ONLINE選項的值設為ON，這樣會使重建索引和表上的數據事務同樣進行。
5、怎樣重新整理索引碎片。有兩種方式：
索引重組：執行下面的命令：
- ALTER INDEX ALL ON TableName RECOGNIZE
索引重建：
- ALTER INDEX ALL ON TableName REBUILD WITH(FILLFACTOR=90,ONONLINE=ON)
通過使用具體索引的名字代替ALL,你能重組或重建單個的索引。你也可以使用數據庫控制臺來重建/重組索引。以上的相關內容就是對SQL Server 使用索引實現數據訪問優化的介紹，望你能有所收獲。
【編輯推薦】
