了解筛选索引

开始

筛选索引是SQL Server 2008中的一种新功能，它是对表中的部分行进行索引。

基本语法：

create nonclustered index index_name on <object> (columns) where <filter_predicate>

在一些特定的应用环境下，筛选索引与传统的全表非聚集索引相比，具有以下优点。

• 提高了查询性能和计划质量
• 减少了索引存储开销
• 减少了索引维护开销

 

接下来，我以例子来说明这三方面的优点。

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

在数据库TestDB上创建两个表(table_a & table_b),而且每一个表都有相同的记录行(各100W行记录)。可以参见下面的测试脚本SQL：

use TestDB

go

if object_id('table_a') is not null drop table table_a

if object_id('table_b') is not null drop table table_b

go

create table table_a (id int identity,col1 int,col2 nvarchar(128),constraint pk_table_a primary key(id))

create table table_b (id int identity,col1 int,col2 nvarchar(128),constraint pk_table_b primary key(id))

go

insert into table_a(col1,col2)

    select top(1000000) a.object_id as col1,b.name as col2

        from sys.all_objects a,

            sys.all_columns b

go

insert into table_b(col1,col2)

    select col1,col2 from table_a

 

go

在Microsoft SQL Server Management Studio 新建一个查询，并执行上面的SQL语句。

1. 没有索引情况：

假设我要查，条件等于"col1 between -200 and 10"的id & col1记录，那么对应SQL语句是:

select id,col1 from table_a a where a.col1 between -200 and 10

为了能够跟踪到执行计划情况和IO信息，我这里设置了"set statistics profile,io on":

use TestDB

go

set statistics profile,io on

select id,col1 from table_a a where a.col1 between -200 and 10

set statistics profile,io off

go

执行结果返回17540行记录，在执行计划过程，采用聚集索引扫描（pk_table_a），IO逻辑读取4311次：

                                                            图1.

1. 筛选索引 Vs. 全表非聚集索引：

为了提升查询性能，通常会在字段col1上创建一个非聚集索引，如（ix_table_a_col1）:

create nonclustered index ix_table_a_col1 on dbo.table_a(col1)

同时，为了让筛选索引和全表非聚集索引进行比较，我在表table_b上创建了一个筛选索引，如(ix_table_b_col1_Filtered):

create nonclustered index ix_table_b_col1_Filtered on dbo.table_b(col1) where col1>=-200

接下来，要查询两个表中"col1 between -200 and 10"的id & col1记录：

use TestDB

go

set statistics profile,io on

select id,col1 from table_a a where a.col1 between -200 and 10

select id,col1 from table_b a where a.col1 between -200 and 10

set statistics profile,io off

go

 

                                                           图2.

图2. 从表table_a和表table_b的实际执行计划统计信息中，看TotalSubtreeCost（所有子操作的预计开销合计）数据，使用筛选索引的table_b（TotalSubtreeCost=0.05036455）明显低于于使用全表非聚集索引的table_a（TotalSubtreeCost=0.02331454）。也就是使用筛选索引的成本，是使用全表非聚集索引的成本的1/2。

 

                                                           图3.

 

图3.从IO信息收集结果看，针对表table_a进行了35次的逻辑读取，而表table_b 只进行了33次逻辑读取。也就说明使用筛选索引在IO逻辑读取次数少于全表非聚集索引在IO的逻辑读取次数。

 

下面我还从客户端统计信息来分析，使用筛选索引和全表非聚集索引的执行时间差别：

                                                           图4.

图4.可以看出，使用筛选索引，在客户端处理时间、总执行时间、服务器等待时间的平均值，都比全表非聚集索引的低。

 

减少了索引存储开销

可以使用下面的SQL语句来查看筛选索引（ix_table_b_col1_Filtered）和全表非聚集索引（ix_table_a_col1）的存储大小：

use TestDB

go

select object_name(b.object_id) as TableName,

        b.name as IndexName ,

sum(a.used_page_count) * 8 as IndexSizeKB

    from sys.dm_db_partition_stats as a

join sys.indexes as b on a.object_id = b.object_id

and a.index_id = b.index_id

    where b.object_id in ( object_id('table_a'), object_id('table_b') )

        and b.name in('ix_table_a_col1','ix_table_b_col1_Filtered')

    group by b.object_id,

            b.name

    order by b.name

                                                           图5.

图5，可以看出筛选索引使用的存储空间明显小于全表非聚集索引。

 

 

 

减少了索引维护开销

仅在数据操作语言 (DML) 语句对索引中的数据产生影响时，才对索引进行维护。与全表非聚集索引相比，筛选索引减少了索引维护开销，因为它更小并且仅在对索引中的数据产生影响时才进行维护。

举个例子，先查表table_a和表table_b中，ID=10的数据：

use TestDB

go

select * from dbo.table_a where id=10

select * from dbo.table_b where id=10

                                                           图6.

现在要上面的col1= -1068265529 改成col1=-123456 ，看更新过程对两表索引（ix_table_a_col1 & ix_table_b_col1_Filtered）产生的影响情况 ：

use TestDB

go

checkpoint

go

update table_a

set col1=-123456 -- -1068265529

where id=10

 

update table_b

set col1=-123456 -- -1068265529

where id=10

 

go

select Operation ,

Context ,

AllocUnitName ,

[Transaction Name] ,

Description

    from fn_dblog(null, null) as a

go

                                                           图7.

图7. 可以看到，前面的更新语句，针对于表table_a，有维护到索引ix_table_a_col1，而在表table_b，没找到维护ix_table_b_col1_Filtered的记录。

从这里可以判验证筛选索引减少了索引维护开销。

 

小结

上面的例子，说明了使用筛选索引的一些优点。在实际生产环境中，需要检查和分析经常用到的存储过程或程序代码中的SQL语句，是否有必要创建筛选索引来提升性能。虽然筛选索引，在某些情况下会提升查询性能，节省存储空间，但必须要小心使用，不能轻易删除现有的全表索引，使用筛选索引。

 

 

参考资料

SQL University: Advanced Indexing – Filtered Indexes：

http://sqlinthewild.co.za/index.php/2011/11/09/sql-university-advanced-indexing-filtered-indexes-2/

The Joys of Filtered Indexes：

http://blogs.msdn.com/b/timchapman/archive/2012/08/27/the-joys-of-filtered-indexes.aspx

Filtered Indexes: What You Need To Know ：

http://sqlfool.com/2009/04/filtered-indexes-what-you-need-to-know/

创建筛选索引：

http://msdn.microsoft.com/zh-cn/library/cc280372(v=sql.100).aspx

CREATE INDEX：

http://msdn.microsoft.com/zh-cn/library/ms188783.aspx

非聚集索引设计指南：

http://msdn.microsoft.com/zh-cn/library/ms179325(v=sql.100).aspx