三、索引优化（6）筛选索引

一、概念

　　筛选索引是一种经过优化的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的部分行进行索引。

 

二、优势

　　筛选索引与全表索引相比具有以下优点：

（1）提高了查询性能和计划质量

　　设计良好的筛选索引可以提高查询性能和执行计划质量，因为它比全表非聚集索引小并且具有经过筛选的统计信息。与全表统计信息相比，经过筛选的统计信息更加准确，因为它们只涵盖筛选索引中的行。

（2）减少了索引维护开销

　　仅在数据操作语言 (DML) 语句对索引中的数据产生影响时，才对索引进行维护。与全表非聚集索引相比，筛选索引减少了索引维护开销，因为它更小并且仅在对索引中的数据产生影响时才进行维护。筛选索引的数量可以非常多，特别是在其中包含很少受影响的数据时。同样，如果筛选索引只包含频繁受影响的数据，则索引大小较小时可以减少更新统计信息的开销。

（3）减少了索引存储开销

　　在没必要创建全表索引时，创建筛选索引可以减少非聚集索引的磁盘存储开销。可以使用多个筛选索引替换一个全表非聚集索引而不会明显增加存储需要。

 

 

三、设计注意事项

　　为了设计有效的筛选索引，必须了解应用程序使用哪些查询以及这些查询与您的数据子集有何关联。例如，所含值中大部分为 NULL 的列、含异类类别的值的列以及含不同范围的值的列都属于具有定义完善的子集的数据。以下设计注意事项提供了筛选索引优于全表索引的各种情况。

1. 数据子集的筛选索引

　　在列中只有少量相关值需要查询时，可以针对值的子集创建筛选索引。例如，当列中的值大部分为 NULL 并且查询只从非 NULL 值中进行选择时，可以为非 NULL 数据行创建筛选索引。由此得到的索引与对相同键列定义的全表非聚集索引相比，前者更小且维护开销更低。

　　例如，AdventureWorks2008R2 数据库中有一个包含 2679 行的 Production.BillOfMaterials 表。EndDate 列只有 199 行包含非 NULL 值，其余 2480 行均包含 NULL。下面的筛选索引将涵盖这样的查询：返回在此索引中定义的列的查询，以及只选择 EndDate 值不为 NULL 的行的查询。

USE AdventureWorks2008R2; 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 SELECT ProductAssemblyID, ComponentID, StartDate  FROM Production.BillOfMaterials WHERE EndDate IS NOT NULL      AND ComponentID = 5      AND StartDate > '01/01/2008' ; GO

 

2. 异类数据的筛选索引

　　表中含有异类数据行时，可以为一种或多种类别的数据创建筛选索引。

　　例如，Production.Product 表中列出的每种产品均分配到一个 ProductSubcategoryID，后者又与 Bikes、Components、Clothing 或 Accessories 产品类别关联。这些类别为异类类别，因为它们在 Production.Product 表中的列值并不是紧密相关的。例如，对于每种产品类别，Color、ReorderPoint、ListPrice、Weight、Class 和 Style 均具有唯一特征。假设会经常查询具有子类别 27-36 的 Accessories。通过对 Accessories 子类别创建筛选索引，可以提高对 Accessories 的查询的性能。

USE AdventureWorks2008R2; 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 (ProductSubcategoryID, ListPrice)          Include (Name) WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36; GO SELECT Name, ProductSubcategoryID, ListPrice FROM Production.Product WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ; GO

　　筛选索引 FIProductAccessories 涵盖上面的查询，因为查询结果包含在该索引中，并且查询计划不包括基表查找。例如，查询谓词表达式 ProductSubcategoryID = 33 是筛选索引谓词 ProductSubcategoryID >= 27 和 ProductSubcategoryID <= 36 的子集，查询谓词中的 ProductSubcategoryID 和 ListPrice 列全都是索引中的键列，并且名称作为包含列存储在索引的叶级别。

 

 

本文结语：

　　筛选索引提高了查询性能和计划质量，减少了索引维护开销，还可以减少非聚集索引的磁盘存储开销。