以下的文章主要描述的是SQL Server 使用索引來實現(xiàn)數(shù)據(jù)訪問優(yōu)化的實際操作步驟，我前兩天在相關(guān)網(wǎng)站看見SQL Server 使用索引來實現(xiàn)數(shù)據(jù)訪問優(yōu)化的實際操作步驟的資料，覺得挺好，就拿出來供大家分享。

第一步：在列上采用正確的索引

有些人可能爭論實施正確的索引是否是數(shù)據(jù)庫優(yōu)化過程的第一步。但是我認(rèn)為在數(shù)據(jù)庫應(yīng)用正確的索引是第一位的。原因有兩點：

1.在一個產(chǎn)品系統(tǒng)里，它將使你在很快的時間內(nèi)提高盡可能大的性能。

2.創(chuàng)建數(shù)據(jù)庫索引不需要你做任何的系統(tǒng)修改，因此不需要任何重新編譯和部署

假如你發(fā)現(xiàn)有當(dāng)前的數(shù)據(jù)庫沒有很好的處理索引，你建了索引，結(jié)果就是性能的快速提升。

（1）首先，創(chuàng)建了聚焦索引（即主鍵），接著數(shù)據(jù)頁在物理文件里按照主鍵的值被排序。

（2）其次，在非主鍵列上創(chuàng)建一個非聚焦索引，將該列和主鍵綁定在一起，但是要按照該列進行排序。

策略：

（1）確保數(shù)據(jù)庫的每個表有一個主健，這么做會確保每個表有一個聚焦索引，通過主健的值，表的數(shù)據(jù)頁通按物理順序排列在磁盤上。所以任何使用主健的數(shù)據(jù)檢索操作,任何在主健字段的排序操作都能非常迅速的檢索數(shù)據(jù)。

（2）在這些列上創(chuàng)建非聚焦索引：A： 經(jīng)常被作為搜索憑證的列； B：用來聯(lián)合其它表的列； C：用來作為外健的列；D：用來排序的列；E：高選擇性列； F：Xml類型

下面是一個創(chuàng)建索引的命令的例子：

CREATE INDEX NCLIX_OrderDetails_ProductID ON dbo.OrderDetails(ProductID) 

你也可以使用SQL Server控制臺在需要的列上創(chuàng)建索引。

第二步：創(chuàng)建正確的復(fù)合索引

下面是創(chuàng)建復(fù)合索引的例子：

CREATE INDEX NCLIX_Sales_ProductID ON dbo.Sales(ProductID) INCLUDE(SalesDate,SalesPersonID) 

請注重，創(chuàng)建復(fù)合索引應(yīng)當(dāng)包含少數(shù)幾個列，并且這些列經(jīng)常在select查詢里使用。在復(fù)合索引里包含太多的列不僅不會給你帶來太多好處。而且由于使用相當(dāng)多的內(nèi)存來存儲復(fù)合索引的列的值，其后果是內(nèi)存溢出和性能降低。

第三步：假如有碎片發(fā)生，重新整理它

1、什么是索引碎片

索引碎片是這樣一種情形：由于在表里大量的插入、修改、刪除操作而使索引頁分裂。假如索引有了高的碎片，有兩種情況，一種情況是掃描索引需要花費很多的時間，另一種情況是在查詢的時候索引根本不SQL Server 使用索引,都會導(dǎo)致性能降低。

2、有兩種類型的碎片：

內(nèi)部破碎：由于索引頁里的數(shù)據(jù)插入或修改操作而發(fā)生，以數(shù)據(jù)作為稀疏矩陣的形式的分布而結(jié)束，這將導(dǎo)致數(shù)據(jù)頁的增加，從而增加查詢時間。

外部破碎：由于索引/數(shù)據(jù)頁的數(shù)據(jù)插入或修改而發(fā)生，以頁碼分離和在文件系統(tǒng)里不連貫的新的索引頁的分配而結(jié)束，數(shù)據(jù)庫服務(wù)器不能利用預(yù)讀操作的優(yōu)點，因為：下一個相關(guān)聯(lián)的數(shù)據(jù)頁不臨近，而且這些相關(guān)連的下面的頁碼可能在數(shù)據(jù)文件的任何地方。

3、如何知道索引破碎是否已經(jīng)發(fā)生?

在數(shù)據(jù)庫執(zhí)行下面的SQL語句(下面的語句在SQLserver2005及以后的版本運行正常，以你的目標(biāo)數(shù)據(jù)庫的名字取代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 

分析查詢結(jié)果，你就能發(fā)現(xiàn)在哪里出現(xiàn)了索引碎片，應(yīng)用下面的規(guī)則：A ：ExternalFragmentation的值>10，預(yù)示對應(yīng)的索引出現(xiàn)外部碎片。B ： InternalFragmentation的值<75，預(yù)示對應(yīng)的索引出現(xiàn)內(nèi)部碎片。

4、什么時候重組和重建索引?

當(dāng)外部碎片的值在10-15，內(nèi)部碎片的值在60-75，對于這樣的索引，你應(yīng)該重組索引。否則，你應(yīng)該重建索引。

關(guān)于索引重建的一個重要的事情是：一旦在一個特定的表上重建索引，表就會被鎖定(重組的時候不會發(fā)生)。所以，對于一個產(chǎn)品數(shù)據(jù)庫的一個大的表，因為在一個大表上的索引重建往往需要花費數(shù)個小時，我們不希望這種鎖定。幸運的是，在SQL2005有一個解決方法，你可以在重建一個表的索引的時候，把ONLINE選項的值設(shè)為ON，這樣會使重建索引和表上的數(shù)據(jù)事務(wù)同樣進行。　

5、怎樣重新整理索引碎片。有兩種方式：

索引重組：執(zhí)行下面的命令：

ALTER INDEX ALL ON TableName RECOGNIZE 

索引重建：

ALTER INDEX ALL ON TableName REBUILD WITH(FILLFACTOR=90,ONONLINE=ON) 

通過使用具體索引的名字代替ALL,你能重組或重建單個的索引。你也可以使用數(shù)據(jù)庫控制臺來重建/重組索引。以上的相關(guān)內(nèi)容就是對SQL Server 使用索引實現(xiàn)數(shù)據(jù)訪問優(yōu)化的介紹，望你能有所收獲。

【編輯推薦】

1. SQL Server數(shù)據(jù)庫和Oracle數(shù)據(jù)同步方案
2. SQL Server ASP代碼與Access ASP代碼的比較
3. SQL Server 數(shù)據(jù)表信息的詳細(xì)描述
4. SQL Server置疑的出現(xiàn)的原因，現(xiàn)象與破解
5. 創(chuàng)建SQL Server全文檢索的2方案