SQL Server 筛选索引

• SQL Server 筛选索引
  • 简介
  • 示例
  • 筛选索引的优点

简介

如果使用得当，非聚集索引可以大大提高查询性能。然而，非聚集索引的好处是有代价的：存储和维护。

• 首先，它需要额外的存储空间来存储索引键列的数据副本。
• 其次，当从表中插入、更新或删除行时，SQL Server需要更新关联的非聚集索引。

如果应用程序只查询表的一部分行，那么效率会很低。此时筛选索引便可以发挥作用了。

筛选索引是带有谓词（条件表达式）的非聚集索引，该谓词允许您指定应将哪些行添加到索引中。

语法：

CREATE INDEX index_name
ON table_name(column_list)
WHERE predicate;

其中，使用带有谓词的WHERE子句来指定索引中应包括表的哪些行。

示例

有如下客户表：

表中的phone字段有很多NULL值：

SELECT 
    SUM(CASE
            WHEN phone IS NULL
            THEN 1
            ELSE 0
        END) AS [Has Phone], 
    SUM(CASE
            WHEN phone IS NULL
            THEN 0
            ELSE 1
        END) AS [No Phone]
FROM 
    sales.customers;

Has Phone   No Phone
----------- -----------
1267        178

(1 row affected)

phone非常适合做过滤索引

在phone字段上创建过滤索引:

CREATE INDEX ix_cust_phone
ON sales.customers(phone)
WHERE phone IS NOT NULL;

查找电话号为(281) 363-3309的客户：

SELECT    
    first_name,
    last_name, 
    phone
FROM    
    sales.customers
WHERE phone = '(281) 363-3309';

可见，查询优化器可以利用筛选索引ix_cust_phone进行搜索。

请注意，要改进键查找，可以使用包含列的索引，其中包含索引中的first_name和last_name列，这个在上一篇讲过。

CREATE INDEX ix_cust_phone
ON sales.customers(phone)
INCLUDE (first_name, last_name)
WHERE phone IS NOT NULL;

筛选索引的优点

如前所述，过滤索引可以帮助您节省空间，特别是当索引键列稀疏时。稀疏列是具有许多NULL值的列。

此外，筛选索引可以降低维护成本，因为当关联表中的数据发生更改时，只需要更新部分数据行，而不是全部数据行。