SQL Server設(shè)定過(guò)濾條件提高索引效率
主要是想大家講述的是通過(guò)SQL Server設(shè)定過(guò)濾條件來(lái)對(duì)索引進(jìn)行提高的實(shí)際操作方案，這種獨(dú)特的數(shù)據(jù)庫(kù)優(yōu)化方法也是具有自己比較雨中不同的一面，下面大家一起來(lái)學(xué)習(xí)一下。
低碳指數(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ù)，一棵樹一天吸收二氧化碳量為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棵樹二氧化碳吸收量。
51CTO數(shù)據(jù)庫(kù)頻道向您推薦《數(shù)據(jù)庫(kù)性能優(yōu)化與調(diào)試》和《SQL Server 2008/2005全解》專題，以便于您更好的理解本文。
SQL Server設(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ù)表中沒有聚簇索引，為了維護(hù)主鍵的唯一性，SQL Server數(shù)據(jù)庫(kù)在默認(rèn)情況下將為主鍵創(chuàng)建聚簇索引（Clustered index），除非用戶特別指定將索引創(chuàng)建為非聚簇索引（Non-clustered index）。
毫無(wú)疑問，我們應(yīng)當(dāng)為頻繁訪問的數(shù)據(jù)創(chuàng)建聚簇索引，當(dāng)然頻繁訪問的字段應(yīng)當(dāng)經(jīng)過(guò)詳細(xì)的分析和慎重選擇，并且索引值應(yīng)當(dāng)盡可能短。提到創(chuàng)建索引，大家往往首先想到主鍵，但是主鍵的數(shù)據(jù)并不一定被頻繁訪問，而且很多時(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為我們提供了另外一種索引——SQL Server設(shè)定過(guò)濾條件索引（Filtered index），一個(gè)設(shè)定過(guò)濾條件索引是一個(gè)特殊的非聚簇索引，它是某些字段的特定子集。換句話說(shuō)，設(shè)定過(guò)濾條件索引是基于一部分選定的字段生成的。
比如說(shuō)，在銷售業(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)常訪問到在這三個(gè)城市產(chǎn)生的銷售記錄，在這種情況下，設(shè)定過(guò)濾條件索引會(huì)占據(jù)較少的磁盤空間，因?yàn)橹挥蠧ity字段的數(shù)值是北京，上海和廣州的記錄會(huì)被索引，這些記錄只是整個(gè)銷售數(shù)據(jù)表格中的一部分。
利用設(shè)定過(guò)濾條件索引可以提升數(shù)據(jù)庫(kù)的性能，首先，只有被索引到記錄發(fā)生變化的時(shí)候，才需要重建索引。比如，某一條在北京發(fā)生的銷售記錄需要調(diào)整，在更新操作之后，索引也要隨之更新，這跟其他的索引是一樣的。
但如果發(fā)生在西安的銷售記錄發(fā)生了變化，無(wú)論添加或刪除了多少條記錄，我們之前建立的設(shè)定過(guò)濾條件索引都是不需要任何操作的，因?yàn)橹挥形挥诒本⑸虾：蛷V州分公司的銷售記錄有影響到這個(gè)索引。SQL Server設(shè)定過(guò)濾條件索引的另外一個(gè)優(yōu)勢(shì)是可以減少磁盤讀寫操作，比如我們要查詢所有北京分公司的銷售記錄，那么使用剛才建立的設(shè)定過(guò)濾條件索引比常規(guī)的非聚簇索引要減少很多不必要的磁盤操作。
為了驗(yàn)證SQL Server設(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)條記錄的銷售數(shù)據(jù)表，當(dāng)然，銷售金額都是隨機(jī)產(chǎn)生的，而city字段，我們隨機(jī)產(chǎn)生1到9這9個(gè)不同的數(shù)字，然后再根據(jù)需要將它們?cè)谔鎿Q為不同的城市，在這個(gè)實(shí)驗(yàn)中，我們把北京、上海和廣州的銷售記錄總比例設(shè)定為67%。
【編輯推薦】
