创建筛选索引

项目
11/19/2024

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Microsoft Fabric SQL 数据库

本文介绍如何使用 SQL Server Management Studio (SSMS) 或 Transact-SQL 创建筛选索引。筛选索引是一种经过优化的、基于磁盘的行存储非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的部分行进行索引。与全表索引相比，设计良好的筛选索引可以提高查询性能、减少索引维护开销并可降低索引存储开销。

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

提高了查询性能和计划质量。

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

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

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

设计注意事项

在列中只有一些相关值需要查询时，可以针对值的子集创建筛选索引。由此得到的索引与对相同键列定义的全表非聚集索引相比，前者更小且维护开销更低。

例如，请考虑以下数据方案中的筛选索引。在每种情况下，查询的 WHERE 子句应该是筛选索引的 WHERE 子句的一个子集，才能受益于筛选索引。

当一列中的值几乎是 NULL 且查询仅从非 NULL 值中选择。可以为非 NULL 数据行创建筛选索引。
表中的行被标记为由定期工作流或队列流程处理。随着时间的推移，表中的大部分行会标记为已处理。尚未处理的行上的筛选索引将会对查找尚未处理的行的定期查询有用。
当表包含异类数据行时。可以为一种或多种类别的数据创建筛选索引。通过将查询范围缩小为表的特定区域，这可以提高针对这些数据行的查询性能。此外，由此得到的索引与全表非聚集索引相比，前者更小且维护开销更低。

限制和局限

不能对视图创建筛选索引。但是，查询优化器可以从对视图中引用的表定义的筛选索引中获益。对于从视图中选择数据的查询，如果查询结果正确，查询优化器会考虑对此查询使用筛选索引。
当筛选器表达式中访问的列为 CLR 数据类型，不能在表中创建筛选过的索引。
筛选索引与索引视图相比，具有以下优点：
- 减少了索引维护开销。例如，相对于索引视图而言，查询处理器使用更少的 CPU 资源便可更新筛选的索引。
- 改善了计划质量。例如，在查询编译期间，查询优化器考虑使用筛选的索引的情况要比考虑使用等效的索引视图的情况多。
- 联机索引重新生成。您可以在筛选的索引可用于查询时重新生成它们。索引视图不支持联机索引重新生成。有关详细信息，请参阅 ALTER INDEX (Transact-SQL) 的 REBUILD 选项。
- 非唯一索引。筛选索引可以是非唯一的，而索引视图必须是唯一的。
筛选索引是针对一个表定义的，仅支持简单比较运算符。如果需要引用多个表或具有复杂逻辑的筛选表达式，则应创建视图。筛选的索引不支持 LIKE 运算符。
如果筛选索引表达式等效于查询谓词并且查询并未在查询结果中返回筛选索引表达式中的列，则筛选索引表达式中的列不需要作为筛选索引定义中的键或包含列。
如果查询谓词在不与筛选索引表达式等效的比较中使用了筛选索引表达式中的某列，则该列应为筛选索引定义中的键或包含列。
如果筛选索引表达式中的某列在查询结果集中，则该列应为筛选索引定义中的键或包含列。
表的聚集索引键不需要是筛选索引定义中的键或包含列。聚集索引键自动包含在所有非聚集索引（包括筛选索引）中。有关详细信息，请参阅索引体系结构和设计指南。
如果筛选索引结果的筛选索引表达式中指定的比较运算符会导致隐式或显式数据转换，则转换发生在比较运算符的左边时，会出现错误。解决方法是在比较运算符的右边编写包含数据转换运算符（CAST 或 CONVERT）的筛选索引表达式。
在 CREATE INDEX (Transact-SQL) 语法中查看创建筛选索引所需的 SET 选项
筛选器不能应用于主键或唯一约束，但可以应用于具有 UNIQUE 属性的索引。
不能对计算列创建筛选索引。

权限

要求对表或视图具有 ALTER 权限。用户必须是 sysadmin 固定服务器角色的成员，或者是 db_ddladmin 和 db_owner 固定数据库角色的成员。如需修改筛选索引表达式，可以使用 CREATE INDEX WITH DROP_EXISTING。

使用 SSMS 创建筛选索引

在对象资源管理器中，选择加号以便展开包含要在其上创建筛选索引的表的数据库。
选择加号以便展开“表”文件夹。
选择加号以便展开要在其上创建筛选索引的表。
右键单击“索引”文件夹，指向“新建索引”，然后选择“非群集索引…”。
在 “新建索引” 对话框的 “常规” 页中，在 “索引名称” 框中输入新索引的名称。
在“索引键列”下，选择“添加…”。
在“从 table_name 中选择列”对话框中，选中要添加到索引的一个或多个表列的复选框。
选择“确定”。
在“筛选器”页的“筛选表达式”下，输入要用于创建筛选索引的 SQL 表达式。
选择“确定”。

使用 Transact-SQL 创建筛选索引

本文中的 Transact-SQL 代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可从 Microsoft SQL Server 示例和社区项目主页下载它。

在 “对象资源管理器” 中，连接到数据库引擎的实例。
在标准栏上，选择“新建查询” 。
将以下示例复制并粘贴到查询窗口中，然后选择“执行”。

USE AdventureWorks2022;
GO

DROP INDEX IF EXISTS FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

筛选索引 FIBillOfMaterialsWithEndDate 对下面的查询有效。可以显示查询执行计划，以确定查询优化器是否使用了该筛选索引。

USE AdventureWorks2022;
GO

SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
    AND ComponentID = 5
    AND StartDate > '01/01/2008' ;
GO

后续步骤

若要了解有关创建索引和相关概念的详细信息，请参阅以下文章：

通过