SQL Server 2008篩選索引設計準則

篩選索引是一種經過優化的非聚集索引，尤其適用於涵蓋從定義完善的資料子集中選擇資料的查詢。篩選索引使用篩選謂詞對錶中的部分行進行索引。與全表索引相比，設計良好的篩選索引可以提高查詢效能、減少索引維護開銷並可降低索引儲存開銷。

篩選索引與全表索引相比具有以下優點：

提高了查詢效能和計劃質量
設計良好的篩選索引可以提高查詢效能和執行計劃質量，因為它比全表非聚集索引小並且具有經過篩選的統計資訊。與全表統計資訊相比，經過篩選的統計資訊更加準確，因為它們只涵蓋篩選索引中的行。

減少了索引維護開銷
僅在資料操作語言 (DML) 語句對索引中的資料產生影響時，才對索引進行維護。與全表非聚集索引相比，篩選索引減少了索引維護開銷，因為它更小並且僅在對索引中的資料產生影響時才進行維護。篩選索引的數量可以非常多，特別是在其中包含很少受影響的資料時。同樣，如果篩選索引只包含頻繁受影響的資料，則索引大小較小時可以減少更新統計資訊的開銷。

減少了索引儲存開銷
在沒必要建立全表索引時，建立篩選索引可以減少非聚集索引的磁碟儲存開銷。可以使用多個篩選索引替換一個全表非聚集索引而不會明顯增加儲存需要。

 設計注意事項
為了設計有效的篩選索引，必須瞭解應用程式使用哪些查詢以及這些查詢與您的資料子集有何關聯。例如，所含值中大部分為 NULL 的列、含異類類別的值的列以及含不同範圍的值的列都屬於具有定義完善的子集的資料。以下設計注意事項提供了篩選索引優於全表索引的各種情況。

資料子集的篩選索引
在列中只有少量相關值需要查詢時，可以針對值的子集建立篩選索引。例如，當列中的值大部分為 NULL 並且查詢只從非 NULL 值中進行選擇時，可以為非 NULL 資料行建立篩選索引。由此得到的索引與對相同鍵列定義的全表非聚集索引相比，前者更小且維護開銷更低。

例如，AdventureWorks 資料庫中有一個包含 2679 行的 Production.BillOfMaterials 表。EndDate 列只有 199 行包含非 NULL 值，其餘 2480 行均包含 NULL。下面的篩選索引將涵蓋這樣的查詢：返回在此索引中定義的列的查詢，以及只選擇 EndDate 值不為 NULL 的行的查詢。

 
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
WHERE EndDate IS NOT NULL;
GO
篩選索引 FIBillOfMaterialsWithEndDate 對下面的查詢有效。您可以顯示查詢執行計劃，以確定查詢優化器是否使用了此篩選索引。有關如何顯示查詢執行計劃的資訊，請參閱分析查詢。

 
SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO有關如何建立篩選索引以及如何定義篩選索引謂詞表示式的詳細資訊，請參閱 CREATE INDEX (Transact-SQL)。

異類資料的篩選索引
表中含有異類資料行時，可以為一種或多種類別的資料建立篩選索引。

例如，Production.Product 表中列出的每種產品均分配到一個 ProductSubcategoryID，後者又與 Bikes、Components、Clothing 或 Accessories 產品類別關聯。這些類別為異類類別，因為它們在 Production.Product 表中的列值並不是緊密相關的。例如，對於每種產品類別，Color、ReorderPoint、ListPrice、Weight、Class 和 Style. 均具有唯一特徵。假設會經常查詢具有子類別 27-36 的 Accessories。通過對 Accessories 子類別建立篩選索引，可以提高對 Accessories 的查詢的效能。

下面的示例對 Production.Product 表中 Accessories 子類別中的所有產品建立一個篩選索引。

 
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIProductAccessories'
    AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
    ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (Name, ListPrice)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO
篩選索引 FIProductAccessories 對下面的查詢有效，因為查詢謂詞等效於篩選索引表示式。

SELECT Name, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID BETWEEN 27 AND 36;
GO檢視與篩選索引
檢視是儲存查詢定義的虛擬表；與篩選索引相比，其用途更廣，功能更強。有關檢視的詳細資訊，請參閱瞭解檢視和使用檢視的情況。下表比較了在檢視和篩選索引中可以使用的部分功能。

在表示式中允許  檢視  篩選的索引 
計算列
 是
 否
 
聯接
 是
 否
 
多個表
 是
 否
 
謂詞中的簡單比較邏輯*
 是
 是
 
謂詞中的複雜邏輯**
 是
 否
 

*有關謂詞中的簡單比較邏輯，請參閱 CREATE INDEX 中的 WHERE 子句語法。

**有關謂詞中的複雜比較邏輯，請參閱 SELECT 中的 WHERE 子句語法。

不能對檢視建立篩選索引。但是，查詢優化器可以從對檢視中引用的表定義的篩選索引中獲益。對於從檢視中選擇資料的查詢，如果查詢結果正確，查詢優化器會考慮對此查詢使用篩選索引。下面的示例建立一個檢視（開始日期在 2000 年 4 月 1 日以後）和一個篩選索引（開始日期在 2000 年 8 月 1 日以後）。

USE AdventureWorks;
GO
IF OBJECT_ID ('ViewOnBillOfMaterials') IS NOT NULL
DROP VIEW ViewOnBillOfMaterials;
GO
CREATE VIEW ViewOnBillOfMaterials AS
SELECT ComponentID, StartDate, EndDate, StartDate + 2 AS ShipDate
FROM Production.BillOfMaterials
WHERE StartDate > '20000401';
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsByStartDate'
    AND object_ID = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsByStartDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsByStartDate
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE StartDate > '20000801';
GO
在下面的示例中，查詢選擇 2000 年 9 月 1 日以後的開始日期，這些日期完全包含在此篩選索引和篩選檢視中。查詢優化器將考慮使用篩選索引 FIBillOfMaterialsByStartDate，因為其中包含了正確的查詢結果。

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20000901';
GO在下一示例中，查詢選擇 2000 年 6 月 1 日以後的開始日期，這些日期完全包含在此檢視中，但未完全包含在此篩選索引中。查詢優化器不考慮使用篩選索引 FIBillOfMaterialsByStartDate，因為與查詢從檢視中選擇資料所返回的正確結果相比，查詢使用篩選索引會返回不同的結果。

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20000601';
GO索引檢視與篩選的索引的對比
篩選的索引與索引檢視相比，具有以下優點：

減少了索引維護開銷。例如，相對於索引檢視而言，查詢處理器使用更少的 CPU 資源便可更新篩選的索引。

改善了計劃質量。例如，在查詢編譯期間，查詢優化器考慮使用篩選的索引的情況要比考慮使用等效的索引檢視的情況多。

聯機索引重新生成。您可以在篩選的索引可用於查詢時重新生成它們。索引檢視不支援聯機索引重新生成。有關詳細資訊，請參閱 ALTER INDEX (Transact-SQL) 的 REBUILD 選項。

非唯一索引。篩選的索引可以是非唯一的，而索引檢視必須是唯一的。

出於以上原因，建議儘可能使用篩選的索引，不使用索引檢視。如果滿足以下條件，則可以使用篩選的索引而不使用索引檢視：檢視只引用一個表，查詢不返回計算列且檢視謂詞使用簡單的比較邏輯。例如，允許在檢視定義中使用如下謂詞表示式，但不允許在篩選索引中使用它，因為它包含 LIKE 運算子。

WHERE StartDate > '20000701' AND ModifiedDate LIKE 'E%'

鍵列
最好在篩選索引定義中包含少量的鍵或包含列，並且只包含查詢優化器為查詢執行計劃選擇篩選索引所需的列。無論某一篩選索引是否涵蓋了查詢，查詢優化器都可以為查詢選擇此篩選索引。但是，如果某一篩選索引涵蓋了查詢，則查詢優化器更有可能選擇此篩選索引。有關涵蓋查詢的詳細資訊，請參閱建立帶有包含列的索引。

在某些情況下，篩選索引涵蓋查詢，但沒有將篩選索引表示式中的列作為鍵或包含列包括在篩選索引定義中。以下準則說明了篩選索引表示式中的列何時應為篩選索引定義中的鍵或包含列。這些示例引用了此前建立的篩選索引 FIBillOfMaterialsWithEndDate。

如果篩選索引表示式等效於查詢謂詞並且查詢並未在查詢結果中返回篩選索引表示式中的列，則篩選索引表示式中的列不需要作為篩選索引定義中的鍵或包含列。例如，FIBillOfMaterialsWithEndDate 涵蓋下面的查詢，因為查詢謂詞等效於篩選表示式，並且查詢結果中未返回 EndDate。FIBillOfMaterialsWithEndDate 不需要將 EndDate 作為篩選索引定義中的鍵或包含列。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO如果查詢謂詞在不與篩選索引表示式等效的比較中使用了篩選索引表示式中的某列，則該列應為篩選索引定義中的鍵或包含列。例如，FIBillOfMaterialsWithEndDate 對下面的查詢有效，因為它從篩選索引中選擇了行的子集。但是，它不涵蓋下面的查詢，因為在比較 EndDate > '20000101' 中使用了 EndDate，此比較不與篩選索引表示式等效。查詢處理器在不查詢 EndDate 值的情況下無法執行此查詢。因此，EndDate 應為篩選索引定義中的鍵或包含列。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20000101';
GO如果篩選索引表示式中的某列在查詢結果集中，則該列應為篩選索引定義中的鍵或包含列。例如，FIBillOfMaterialsWithEndDate 不涵蓋下面的查詢，因為它在查詢結果中返回了 EndDate 列。因此，EndDate 應為篩選索引定義中的鍵或包含列。

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO表的主鍵不需要是篩選索引定義中的鍵或包含列。主鍵自動包含在所有非聚集索引（包括篩選索引）中。

篩選謂詞中的資料轉換運算子
如果篩選索引結果的篩選索引表示式中指定的比較運算子會導致隱式或顯式資料轉換，則轉換髮生在比較運算子的左邊時，會出現錯誤。解決方法是在比較運算子的右邊編寫包含資料轉換運算子（CAST 或 CONVERT）的篩選索引表示式。

下面的示例建立一個包含多種資料型別的表。

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.TestTable') IS NOT NULL
DROP TABLE dbo.TestTable;
GO
CREATE TABLE TestTable (a int, b varbinary(4));
GO
在下面的篩選索引定義中，列 b 隱式轉換為整數資料型別，以便與常量 1 進行比較。因為轉換髮生在篩選謂詞中運算子的左邊，所以這會生成錯誤訊息 10611。

USE AdventureWorks;
GO
IF EXISTS ( SELECT name from sys.indexes
    WHERE name = N'TestTabIndex'
    AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
GO
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO解決方法是將右側的常量轉換為與列 b 的型別相同的型別，如下例所示：

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO將資料轉換從比較運算子的左邊移動到右邊可能會改變轉換的含義。在上例中，將 CONVERT 運算子新增到右邊時，相應的比較從整數比較更改為 varbinary 比較。

引用依賴項
sys.sql_expression_dependencies 目錄檢視將篩選索引表示式中的每一列作為一個引用依賴項進行跟蹤。不能刪除、重新命名或更改在篩選索引表示式中定義的表列的定義。

何時使用篩選索引
列中包含查詢在 SELECT 語句中引用的定義完善的資料子集時，篩選索引很有用。以下是一些示例：

僅包含少量非 NULL 值的稀疏列。

包含多種類別的資料的異類列。

包含多個範圍的值（如美元金額、時間和日期）的列。

由列值的簡單比較邏輯定義的表分割槽。

如果索引中的行數與全表索引相比較少時，篩選索引減少的維護開銷最為明顯。如果篩選索引包含表中的大部分行，則與全表索引相比，其維護開銷可能更高。在這種情況下，應使用全表索引而不是篩選索引。

篩選索引是針對一個表定義的，僅支援簡單比較運算子。如果需要引用多個表或具有複雜邏輯的篩選表示式，則應建立檢視。

篩選索引功能支援
一般情況下，資料庫引擎和工具為篩選索引提供了與非聚集全表索引相同的支援，將篩選索引視為特殊型別的非聚集索引。下面的列表提供了有關對篩選索引提供完全支援、不提供支援或提供有限支援的工具和功能的說明。

ALTER INDEX 支援篩選索引。若要修改篩選索引表示式，請使用 CREATE INDEX WITH DROP_EXISTING。

缺失索引功能不建議使用篩選索引。

資料庫引擎優化顧問在提供索引優化建議時會考慮篩選索引。

聯機索引操作支援篩選索引。

表提示支援篩選索引，但有一些不適用於非篩選索引的限制。下一節將介紹這些內容。

 查詢注意事項
如果不論是否使用篩選索引，查詢均選擇相同的結果，則查詢優化器會使用篩選索引。此前介紹的篩選索引 FIBillOfMaterialsWithEndDate 對以下兩個查詢有效。在第一個示例中，查詢謂詞與篩選索引謂詞 WHERE EndDate IS NOT NULL 完全匹配。在第二個示例中，由於查詢謂詞包含索引中行的子集，所以它比篩選謂詞具有更強的選擇性。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate < '20000701';
GO下一個查詢也可使用 FIBillOfMaterialsWithEndDate。但是，由於存在其他決定查詢開銷的因素（如查詢謂詞的選擇性），優化器可能不會選擇篩選索引。如下例所示，可以通過將篩選索引用作查詢提示強制優化器選擇篩選索引。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
    WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )
WHERE EndDate IN ('20000825', '20000908', '20000918');
GO如果查詢可以返回不在篩選索引中的行，則查詢優化器將不會使用篩選索引。例如，查詢優化器將不考慮對下面的查詢使用 FIBillOfMaterialsWithEndDate，因為查詢可能返回 NULL EndDate 值和非 NULL ModifiedDate 值，這些值不能包含在 FIBillOfMaterialsWithEndDate 中（因為它只包含非 NULL EndDate 值）。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL OR ModifiedDate IS NOT NULL;
GO如果將篩選索引顯式用作表提示並且篩選索引可能不包含所有查詢結果，則查詢優化器將生成查詢編譯錯誤 8622。在下面的示例中，查詢優化器會生成錯誤 8622，因為 FIBillOfMaterialsWithEndDate 對此查詢無效，並且它顯式用作了索引提示：

SELECT StartDate, ComponentID FROM Production.BillOfMaterials
    WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )
WHERE EndDate IS NOT NULL OR ModifiedDate IS NOT NULL;
GO引數化查詢
在某些情況下，引數化查詢在編譯時包含的資訊不足以滿足查詢優化器選擇篩選索引的需要。可能可以重寫此查詢以提供缺少的資訊。在下面的示例中，查詢優化器不考慮對 SELECT 語句使用篩選索引 FIBillOfMaterialsWithComponentID，因為 @p 和 @q 的引數值在編譯時未知。下面的查詢示例執行時將 SHOWPLAN_XML 設定為 ON，以使您可以在 SHOWPLAN_XML 輸出中檢視引數化查詢的不匹配的篩選索引。

USE AdventureWorks;
GO
IF EXISTS ( SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE ComponentID IN (533, 324, 753);
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID from Production.BillOfMaterials
WHERE ComponentID = @p OR ComponentID = @q;
GO
SET SHOWPLAN_XML OFF;
GO
SHOWPLAN_XML 輸出中的 UnmatchedIndexes 元素和 Parameterization 子元素指示篩選索引與查詢不匹配。有關如何檢視 SHOWPLAN_XML 輸出的資訊，請參閱 XML 顯示計劃。

解決方法是修改此查詢，使在引數化表示式不是篩選謂詞的子集時查詢結果為空。下面的查詢說明了如何進行這種修改。通過將 ComponentID in (533, 324, 753) 表示式新增到 WHERE 子句，確保了此查詢的結果是篩選謂詞表示式的子集。通過這種修改，查詢優化器可以考慮對下面的 SELECT 語句使用篩選索引 FIBillOfMaterialsWithComponentID。

USE AdventureWorks;
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WHERE ComponentID in (533, 324, 753)
    AND (ComponentID = @p OR ComponentID = @q);
GO
SET SHOWPLAN_XML OFF;
GO簡單引數化
在大多數情況下，如果某查詢計劃包括篩選索引，查詢優化器將不對該查詢執行簡單引數化（在 SQL Server 2005 中稱為“自動引數化”）。對此類查詢執行簡單引數化可擴大可能引數值的範圍，這樣，篩選索引便不能保證查詢結果的準確性。例如，如果 SELECT 語句的 WHERE 子句使用了在篩選索引的謂詞中使用的列，則查詢優化器可能不會執行簡單引數化，這是因為查詢計劃中很可能會包括篩選索引。

如果適合，使用本節中所述的準則重寫查詢以確保篩選索引將涵蓋該查詢，也許能夠引數化該查詢。

使用鍵查詢的查詢
查詢優化器可以執行鍵查詢操作來檢索篩選索引沒有涵蓋的剩餘列，從而即使在某篩選索引不涵蓋查詢的情況下也可以使用該篩選索引。有關鍵查詢的詳細資訊，請參閱Key Lookup Showplan 運算子。如果估計的鍵查詢次數很少，則查詢優化器可能會選擇此方法。下面的查詢使用索引提示強制查詢處理器使用 FIBillOfMaterialsWithEndDate，同時對 EndDate 執行書籤查詢操作。對於查詢謂詞中的 EndDate > @date 比較，會執行鍵查詢操作。

USE AdventureWorks;
GO
DECLARE @date AS DATE;
SET @date = '20000825'
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WITH ( INDEX (FIBillOfMaterialsWithEndDate) )
WHERE EndDate > @date;
GO請注意，EndDate > @Date 與篩選索引表示式 EndDate IS NOT NULL 不完全匹配。篩選索引對此引數化查詢仍有效，因為它返回了由篩選索引表示式定義的行的子集。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-515883/，如需轉載，請註明出處，否則將追究法律責任。