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索引只能针对具体的语句进行创建,而不能作为通用的索引使用,这个比较简单,记录一下,防止犯错。

 

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐