sqlserver 筛选索引（filter index）在查询是需要注意的事项

 

sqlserver 的筛选索引（filter index）与常规的非筛选索引，加了一定的filter条件，可以按照某些条件对表中的字段进行索引，但是filter 索引在查询 使用上，并不等同于常规的索引，
如果忽略了这些差异，可能会造成潜在的问题，因此在使用filter索引的时候，一定要结合具体的查询，做充分的测试。

测试case

if object_id('test_filter_index') is not null
    drop table test_filter_index
go

create table test_filter_index
(
    id int identity(1,1) not null primary key,
    col2 varchar(10),
    col3 varchar(10),
    create_date datetime
)
go


--写入10w行测试数据，col2 col3非空
insert into test_filter_index
select concat('a',cast(rand()*1000000 as int)),concat('b',cast(rand()*1000000 as int)),getdate()
go 100000


--写入1w行测试数据，col2 col3为空
insert into test_filter_index
select null,null,getdate()
go 10000

非filter索引

如果是正常的索引，也即不加filter条件，如下创建测试表上的索引

--col2和col3上，如果是常规索引（非筛选索引）
create index idx_col2 on test_filter_index(col2,col3) 
go

如下，只要是按照索引的前导列进行查询或join，都可以使用到索引

 

filter索引

如果在创建索引的时候增加filter条件

--删除之前创建的索引
drop index idx_col2 on test_filter_index

--col2和col3上，如果是筛选索引（增加col2和col3上的筛选条件）
create index idx_col2 on test_filter_index(col2,col3) 
where col2 is not null and col3 is not null
go

在执行上述的两个查询，会发现，尽管使用的查询条件为索引的前导列，但是扔无法使用到上面创建的filter索引

其实不难理解，为什么上面两种情况无法使用到创建的filter索引？由于在创建索引的时候，增加筛选条件，这个索引树种的数据，可能是不完全符合查询语义的
就比如select * from test_filter_index where col2 = 'a632395'，除了 col2 = 'a632395'这个条件之外，对于col3字段，潜在两种符合条件的数据
第一种：select * from test_filter_index where col2 = 'a632395' and col3 is null
第二种：select * from test_filter_index where col2 = 'a632395' and col3 is not null
如果走了filter索引，也即idx_col2 ，查询出来的结果可能就是不完整的，因此不会使用到idx_col2 这个索引
事实上，执行计划很清楚地显示了，什么情况下才可以用到filter索引，只有查询条件的数据被filter索引的筛选条件覆盖，或者说查询条件是filter条件的子集，才有可能用到filter索引

查询是否可以使用到filter索引，只有满足当前的查询结果集，一定是属于索引的filter筛选之后的子集的情况下，才能使用到filter索引，否则都无法使用到filter索引
filter索引只能针对具体的语句进行创建，而不能作为通用的索引使用，这个比较简单，记录一下，防止犯错。