SQL Server設定過濾條件提高索引效率

BKJIA獨家特稿】低碳指數：在這裡為了方便計算和直觀，我們以Intel至強X7500處理器的TDP為標準計算能耗TDP=130W/h=2.167W/m=0.036W/s）。另外根據中國林業局的資料，一棵樹一天吸收二氧化碳量為5.023kg，每一度電產生0.785公斤二氧化碳。

如果按照本文方法最佳化後資料庫執行時間由27秒縮減到14秒，也就是單位時間少47.8%的能量消耗。那麼在一天裡將減少1.486kw電能消耗，約合1.167kg二氧化碳排放，按我們的計算是一天減少0.232棵樹二氧化碳吸收量。

BKJIA資料庫頻道向您推薦《資料庫效能最佳化與調試》和《SQL Server 2008/2005全解》專題，以便於您更好的理解本文。

設定過濾條件提高索引效率

優秀的索引是SQL Server資料庫效能的關鍵，然而高效的索引都是經過精心設計而成的。眾所周知，主鍵是儲存資料對象的唯一標識，如果資料表中沒有聚簇索引，為了維護主鍵的唯一性，SQL Server資料庫在預設情況下將為主鍵建立聚簇索引Clustered index），除非使用者特別指定將索引建立為非聚簇索引Non-clustered index）。

毫無疑問，我們應當為頻繁訪問的資料建立聚簇索引，當然頻繁訪問的欄位應當經過詳細的分析和謹慎選擇，並且索引值應當儘可能短。提到建立索引，大家往往首先想到主鍵，但是主鍵的資料並不一定被頻繁訪問，而且很多時候為了保證主鍵的唯一性，主鍵的數值往往不是很短，比如我們經常會選擇通用唯一識別碼GUID）類型作為主鍵的資料類型，唯一識別碼的長度一般是16個位元組，就長度而言，這種資料類型並不是最理想的聚簇索引選項，在這種情況下，可以為主鍵建立非聚簇索引，因為主索引值在WHERE語句中用來查詢特定的記錄是非常高效的，建立非聚簇索引可以將查詢的效率再上一個台階。如果您選擇了整型作為主鍵的資料類型，那就可以考慮將為主鍵產生聚簇索引。

SQL Server 2008為我們提供了另外一種索引——設定過濾條件索引Filtered index），一個設定過濾條件索引是一個特殊的非聚簇索引，它是某些欄位的特定子集。換句話說，設定過濾條件索引是基於一部分選定的欄位產生的。比如說，在銷售業績資料表中，分公司所在城市的資料存放區在City欄位，如果我們建立一個非聚簇索引，那麼所有的分公司所在的城市，都會被納入索引當中。但是如果我們使用設定過濾條件索引，我們就可以只選擇一部分城市被索引，比如北京，上海和廣州，代碼如下：

 

  
CREATE NONCLUSTERED INDEX FilteredCities ON Sales(City)  
  
WHERE City IN ('北京','上海','廣州') 
 

與常規索引的區別在於，我們使用了WHERE語句來設定我們的過濾條件。假定公司的絕大部分收入都是來自於這三個城市的，那麼我們的資料庫查詢會經常訪問到在這三個城市產生的銷售記錄，在這種情況下，設定過濾條件索引會佔據較少的磁碟空間，因為只有City欄位的數值是北京，上海和廣州的記錄會被索引，這些記錄只是整個銷售資料表格中的一部分。

利用設定過濾條件索引可以提升資料庫的效能，首先，只有被索引到記錄發生變化的時候，才需要重建索引。比如，某一條在北京發生的銷售記錄需要調整，在更新操作之後，索引也要隨之更新，這跟其他的索引是一樣的。但如果發生在西安的銷售記錄發生了變化，無論添加或刪除了多少條記錄，我們之前建立的設定過濾條件索引都是不需要任何操作的，因為只有位於北京、上海和廣州分公司的銷售記錄有影響到這個索引。設定過濾條件索引的另外一個優勢是可以減少磁碟讀寫操作，比如我們要查詢所有北京分公司的銷售記錄，那麼使用剛才建立的設定過濾條件索引比常規的非聚簇索引要減少很多不必要的磁碟操作。

為了驗證設定過濾條件索引所帶來的效能優勢，我們進行了對比測試。

首先，我們在VirtualBox虛擬機器裡安裝Windows Server 2008 R2與SQL Server 2008 R2中文版，順便說一下，我們安裝的都是可以試用180天的試用版，在微軟官方網站可以直接下載，而且現在試用版也不需要申請序號了，在安裝過程中可以直接選擇安裝180天試用，就可以直接安裝，這位實驗和學習帶來了不少便利。

我們在資料庫中建立了一個500萬條記錄的銷售資料表，當然，銷售金額都是隨機產生的，而city欄位，我們隨機產生1到9這9個不同的數字，然後再根據需要將它們在替換為不同的城市，在這個實驗中，我們把北京、上海和廣州的銷售記錄總比例設定為67%。

點擊查看清晰大圖

接下來，我們將虛擬機器進行完整的複製，這樣就可以得到兩套完全一致的作業系統和資料庫，資料庫中已經包含了我們剛剛建立的資料表，相關過程可以參考VirtualBox的技術文檔。複製整個虛擬機器的目的在於確保硬體和作業系統對資料庫效能的影響最小，以便於我們將注意力集中在不同索引方式下，資料庫效能的表現。

下一步，我們在第一個虛擬機器中建立city欄位的完整的非聚簇索引，代碼如下：

 

  
CREATE NONCLUSTERED INDEX FilteredCities ON Sales(City) 
 

在第二個虛擬機器中，我們建立設定過濾條件索引，代碼如下

 

  
CREATE NONCLUSTERED INDEX FilteredCities ON Sales(City)  
  
WHERE City IN ('北京','上海','廣州') 
 

點擊查看清晰大圖

然後我們在兩個虛擬機器的資料庫中來計算北京、上海和廣州這三個城市的銷售金額總和，代碼如下

 

  
SELECT SUM(Value) FROM Sales  
  
WHERE City = '北京' or City = '上海' or City = '廣州' 
 

在使用完整的非聚簇索引的情況下，我們花費了27秒，而使用設定過濾條件索引的情況下，我們只需要14秒就得到了計算結果，可見非聚簇索引在大規模資料計算的情況下，對效能的提升還是非常可觀的，我們截取的螢幕如下，供大家參考：

最佳化前：點擊查看清晰大圖

最佳化後：點擊查看清晰大圖

在選擇過濾條件的時候，我們需要考慮哪些資料會隨著時間的推移而經常變化，比如，新增加的記錄是添加到索引的中間還是末尾？當記錄刪除的時候，索引值是否需要隨之刪除？這些問題的答案都會影響我們對索引的設計。

在這裡，我們需要用到填滿因數Fill Factor），填滿因數是一個以百分比表示的數值，在重建索引的時候，填滿因數的值決定了每個頁面上要填充資料的空間百分比，以便保留一些剩餘空間作為以後擴充索引的可用空間，以下代碼示範了如何將填滿因數設定為80，只有在進階選項開啟的情況下才能設定填滿因數：

 

  
Use DatabseName;  
  
GO  
  
sp_configure 'show advanced options', 1;  
  
GO  
  
RECONFIGURE;  
  
GO  
  
sp_configure 'fill factor', 80;  
  
GO 
 

如果填滿因數的值是100，那麼索引頁就被會全部填充。我們一般考慮將填滿因數設定為50到80中間的數值來保證添加新值的時候，不會發生頁面分割。如果經常需要在索引末尾添加欄位值的話，可以考慮將填滿因數設定為90到100之間的值。最理想的狀態是同時保證最少次數的的頁面分割和索引重建。