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