SQL Server設(shè)定過(guò)濾條件提高索引效率
原創(chuàng)【51CTO獨(dú)家特稿】低碳指數(shù)：在這里為了方便計(jì)算和直觀，我們以Intel至強(qiáng)X7500處理器的TDP為標(biāo)準(zhǔn)計(jì)算能耗（TDP=130W/h=2.167W/m=0.036W/s）。另外根據(jù)中國(guó)林業(yè)局的數(shù)據(jù)，一棵樹(shù)一天吸收二氧化碳量為5.023kg，每一度電產(chǎn)生0.785公斤二氧化碳。
如果按照本文方法優(yōu)化后數(shù)據(jù)庫(kù)執(zhí)行時(shí)間由27秒縮減到14秒，也就是單位時(shí)間少47.8%的能量消耗。那么在一天里將減少1.486kw電能消耗，約合1.167kg二氧化碳排放，按我們的計(jì)算是一天減少0.232棵樹(shù)二氧化碳吸收量。
51CTO數(shù)據(jù)庫(kù)頻道向您推薦《數(shù)據(jù)庫(kù)性能優(yōu)化與調(diào)試》和《SQL Server 2008/2005全解》專題，以便于您更好的理解本文。
設(shè)定過(guò)濾條件提高索引效率
優(yōu)秀的索引是SQL Server數(shù)據(jù)庫(kù)性能的關(guān)鍵，然而高效的索引都是經(jīng)過(guò)精心設(shè)計(jì)而成的。眾所周知，主鍵是儲(chǔ)存數(shù)據(jù)對(duì)象的***標(biāo)識(shí)，如果數(shù)據(jù)表中沒(méi)有聚簇索引，為了維護(hù)主鍵的***性，SQL Server數(shù)據(jù)庫(kù)在默認(rèn)情況下將為主鍵創(chuàng)建聚簇索引（Clustered index），除非用戶特別指定將索引創(chuàng)建為非聚簇索引（Non-clustered index）。
毫無(wú)疑問(wèn)，我們應(yīng)當(dāng)為頻繁訪問(wèn)的數(shù)據(jù)創(chuàng)建聚簇索引，當(dāng)然頻繁訪問(wèn)的字段應(yīng)當(dāng)經(jīng)過(guò)詳細(xì)的分析和慎重選擇，并且索引值應(yīng)當(dāng)盡可能短。提到創(chuàng)建索引，大家往往首先想到主鍵，但是主鍵的數(shù)據(jù)并不一定被頻繁訪問(wèn)，而且很多時(shí)候?yàn)榱吮ＷC主鍵的***性，主鍵的數(shù)值往往不是很短，比如我們經(jīng)常會(huì)選擇全局***標(biāo)識(shí)符（GUID）類型作為主鍵的數(shù)據(jù)類型，***標(biāo)識(shí)符的長(zhǎng)度一般是16個(gè)字節(jié)，就長(zhǎng)度而言，這種數(shù)據(jù)類型并不是最理想的聚簇索引選項(xiàng)，在這種情況下，可以為主鍵創(chuàng)建非聚簇索引，因?yàn)橹麈I值在WHERE語(yǔ)句中用來(lái)查詢特定的記錄是非常高效的，創(chuàng)建非聚簇索引可以將查詢的效率再上一個(gè)臺(tái)階。如果您選擇了整型作為主鍵的數(shù)據(jù)類型，那就可以考慮將為主鍵生成聚簇索引。
SQL Server 2008為我們提供了另外一種索引——設(shè)定過(guò)濾條件索引（Filtered index），一個(gè)設(shè)定過(guò)濾條件索引是一個(gè)特殊的非聚簇索引，它是某些字段的特定子集。換句話說(shuō)，設(shè)定過(guò)濾條件索引是基于一部分選定的字段生成的。比如說(shuō)，在銷(xiāo)售業(yè)績(jī)數(shù)據(jù)表中，分公司所在城市的數(shù)據(jù)存儲(chǔ)在City字段，如果我們創(chuàng)建一個(gè)非聚簇索引，那么所有的分公司所在的城市，都會(huì)被納入索引當(dāng)中。但是如果我們使用設(shè)定過(guò)濾條件索引，我們就可以只選擇一部分城市被索引，比如北京，上海和廣州，代碼如下：
- CREATE NONCLUSTERED INDEX FilteredCities ON Sales(City)
- WHERE City IN ('北京','上海','廣州')
與常規(guī)索引的區(qū)別在于，我們使用了WHERE語(yǔ)句來(lái)設(shè)定我們的過(guò)濾條件。假定公司的絕大部分收入都是來(lái)自于這三個(gè)城市的，那么我們的數(shù)據(jù)庫(kù)查詢會(huì)經(jīng)常訪問(wèn)到在這三個(gè)城市產(chǎn)生的銷(xiāo)售記錄，在這種情況下，設(shè)定過(guò)濾條件索引會(huì)占據(jù)較少的磁盤(pán)空間，因?yàn)橹挥蠧ity字段的數(shù)值是北京，上海和廣州的記錄會(huì)被索引，這些記錄只是整個(gè)銷(xiāo)售數(shù)據(jù)表格中的一部分。
利用設(shè)定過(guò)濾條件索引可以提升數(shù)據(jù)庫(kù)的性能，首先，只有被索引到記錄發(fā)生變化的時(shí)候，才需要重建索引。比如，某一條在北京發(fā)生的銷(xiāo)售記錄需要調(diào)整，在更新操作之后，索引也要隨之更新，這跟其他的索引是一樣的。但如果發(fā)生在西安的銷(xiāo)售記錄發(fā)生了變化，無(wú)論添加或刪除了多少條記錄，我們之前建立的設(shè)定過(guò)濾條件索引都是不需要任何操作的，因?yàn)橹挥形挥诒本?、上海和廣州分公司的銷(xiāo)售記錄有影響到這個(gè)索引。設(shè)定過(guò)濾條件索引的另外一個(gè)優(yōu)勢(shì)是可以減少磁盤(pán)讀寫(xiě)操作，比如我們要查詢所有北京分公司的銷(xiāo)售記錄，那么使用剛才建立的設(shè)定過(guò)濾條件索引比常規(guī)的非聚簇索引要減少很多不必要的磁盤(pán)操作。
為了驗(yàn)證設(shè)定過(guò)濾條件索引所帶來(lái)的性能優(yōu)勢(shì)，我們進(jìn)行了對(duì)比測(cè)試。
首先，我們?cè)赩irtualBox虛擬機(jī)里安裝Windows Server 2008 R2與SQL Server 2008 R2中文版，順便說(shuō)一下，我們安裝的都是可以試用180天的試用版，在微軟官方網(wǎng)站可以直接下載，而且現(xiàn)在試用版也不需要申請(qǐng)序列號(hào)了，在安裝過(guò)程中可以直接選擇安裝180天試用，就可以直接安裝，這位實(shí)驗(yàn)和學(xué)習(xí)帶來(lái)了不少便利。
我們?cè)跀?shù)據(jù)庫(kù)中創(chuàng)建了一個(gè)500萬(wàn)條記錄的銷(xiāo)售數(shù)據(jù)表，當(dāng)然，銷(xiāo)售金額都是隨機(jī)產(chǎn)生的，而city字段，我們隨機(jī)產(chǎn)生1到9這9個(gè)不同的數(shù)字，然后再根據(jù)需要將它們?cè)谔鎿Q為不同的城市，在這個(gè)實(shí)驗(yàn)中，我們把北京、上海和廣州的銷(xiāo)售記錄總比例設(shè)定為67%。
點(diǎn)擊查看清晰大圖
接下來(lái)，我們將虛擬機(jī)進(jìn)行完整的復(fù)制，這樣就可以得到兩套完全一致的操作系統(tǒng)和數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)中已經(jīng)包含了我們剛剛創(chuàng)建的數(shù)據(jù)表，相關(guān)過(guò)程可以參考VirtualBox的技術(shù)文檔。復(fù)制整個(gè)虛擬機(jī)的目的在于確保硬件和操作系統(tǒng)對(duì)數(shù)據(jù)庫(kù)性能的影響最小，以便于我們將注意力集中在不同索引方式下，數(shù)據(jù)庫(kù)性能的表現(xiàn)。
下一步，我們?cè)?**個(gè)虛擬機(jī)中創(chuàng)建city字段的完整的非聚簇索引，代碼如下：
- CREATE NONCLUSTERED INDEX FilteredCities ON Sales(City)
在第二個(gè)虛擬機(jī)中，我們創(chuàng)建設(shè)定過(guò)濾條件索引，代碼如下
- CREATE NONCLUSTERED INDEX FilteredCities ON Sales(City)
- WHERE City IN ('北京','上海','廣州')
點(diǎn)擊查看清晰大圖
然后我們?cè)趦蓚€(gè)虛擬機(jī)的數(shù)據(jù)庫(kù)中來(lái)計(jì)算北京、上海和廣州這三個(gè)城市的銷(xiāo)售金額總和，代碼如下
- SELECT SUM(Value) FROM Sales
- WHERE City = '北京' or City = '上海' or City = '廣州'
在使用完整的非聚簇索引的情況下，我們花費(fèi)了27秒，而使用設(shè)定過(guò)濾條件索引的情況下，我們只需要14秒就得到了計(jì)算結(jié)果，可見(jiàn)非聚簇索引在大規(guī)模數(shù)據(jù)計(jì)算的情況下，對(duì)性能的提升還是非?？捎^的，我們截取的屏幕如下，供大家參考：
優(yōu)化前：點(diǎn)擊查看清晰大圖
優(yōu)化后：點(diǎn)擊查看清晰大圖
在選擇過(guò)濾條件的時(shí)候，我們需要考慮哪些數(shù)據(jù)會(huì)隨著時(shí)間的推移而經(jīng)常變化，比如，新增加的記錄是添加到索引的中間還是末尾？當(dāng)記錄刪除的時(shí)候，索引值是否需要隨之刪除？這些問(wèn)題的答案都會(huì)影響我們對(duì)索引的設(shè)計(jì)。
在這里，我們需要用到填充因子（Fill Factor），填充因子是一個(gè)以百分比表示的數(shù)值，在重建索引的時(shí)候，填充因子的值決定了每個(gè)頁(yè)面上要填充數(shù)據(jù)的空間百分比，以便保留一些剩余空間作為以后擴(kuò)展索引的可用空間，以下代碼演示了如何將填充因子設(shè)定為80，只有在高級(jí)選項(xiàng)打開(kāi)的情況下才能設(shè)定填充因子：
- Use DatabseName;
- GO
- sp_configure 'show advanced options', 1;
- GO
- RECONFIGURE;
- GO
- sp_configure 'fill factor', 80;
- GO
如果填充因子的值是100，那么索引頁(yè)就被會(huì)全部填充。我們一般考慮將填充因子設(shè)定為50到80中間的數(shù)值來(lái)保證添加新值的時(shí)候，不會(huì)發(fā)生頁(yè)拆分。如果經(jīng)常需要在索引末尾添加字段值的話，可以考慮將填充因子設(shè)定為90到100之間的值。最理想的狀態(tài)是同時(shí)保證最少次數(shù)的的頁(yè)拆分和索引重建。
【編輯推薦】
