详解SQL Server的聚焦过滤索引

前言

这一节我们还是继续讲讲索引知识，前面我们聚集索引、非聚集索引以及覆盖索引等，在这其中还有一个过滤索引，通过索引过滤我们也能提高查询性能，简短的内容，深入的理解。

过滤索引，在查询条件上创建非聚集索引（1）

过滤索引是sql 2008的新特性，被应用在表中的部分行，所以利用过滤索引能够提高查询，相对于全表扫描它能减少索引维护和索引存储的代价。当我们在索引上应用where条件时就是过滤索引。也就是满足如下格式：

create nonclustered index <index name>
on <table> (<columns>)
where <criteria>;
go

下面我们来看一个简单的查询

use adventureworks2012
go
select salesorderdetailid, unitprice
from sales.salesorderdetail
where unitprice > 2000
go

上述列中未建立任何索引，当然除了salesorderdetailid默认创建的聚集索引，这种情况下我们能够猜想到其执行的查询计划必然是主键创建的聚集索引扫描，如下

上述我们已经说过此时未在查询条件上创建索引，所以此时必然走的是主键创建的聚集索引，接下来我们首先在unitprice列上创建非聚集索引来提高查询性能，

create nonclustered index idx_salesorderdetail_unitprice
on sales.salesorderdetail(unitprice)

此时我们再来比较二者查询开销

use adventureworks2012
go
dbcc freeproccache
dbcc dropcleanbuffers
select salesorderdetailid, unitprice
from adventureworks2012.sales.salesorderdetail with(index([pk_salesorderdetail_salesorderid_salesorderdetailid]))
where unitprice > 2000
go
select salesorderdetailid, unitprice
from sales.salesorderdetail with(index([idx_salesorderdetail_unitprice]))
where unitprice > 2000

此时在查询条件上建立了非聚集索引之后，查询开销提升的非常明显，提升达到了90%以上，因为非聚集索引也会引用了主键创建的聚集索引，所以这个时候不会导致bookmark lookup或者key lookup查找。接下来我们我们再添加一个带有条件的非聚集索引即过滤索引

create nonclustered index idxwhere_salesorderdetail_unitprice
on sales.salesorderdetail(unitprice)
where unitprice > 1000

此时我们再来看看创建了过滤索引之后和之前非聚集索引性能开销差异：

use adventureworks2012
go
dbcc freeproccache
dbcc dropcleanbuffers
select salesorderdetailid, unitprice
from adventureworks2012.sales.salesorderdetail with(index([idx_salesorderdetail_unitprice]))
where unitprice > 2000
select salesorderdetailid, unitprice
from sales.salesorderdetail with(index([idxwhere_salesorderdetail_unitprice]))
where unitprice > 2000

此时我们知道创建的非聚集过滤索引与传统创建的非聚集索引相比，我们的查询接近减少了一半。

唯一过滤索引

唯一过滤索引对于所有列必须唯一且不为空（只允许一个null存在）也是非常好的解决方案，所以此时在创建唯一过滤索引时需要将null值除外，比如如下：

create unique nonclustered index uq_fix_customers_email
on customers(email)
where email is not null
go

过滤索引结合include

当我们再添加一个额外列时，使用默认主键创建的聚集索引时，此时会走聚集索引扫描，然后我们在查询条件上创建一个过滤索引，我们强制使用这个过滤索引时，此时由于添加额外列，会导致需要返回到基表中再去获取数据，所以也就造成了key lookup查找，如下：

use adventureworks2012
go
select salesorderdetailid, unitprice, unitpricediscount
from sales.salesorderdetail
where unitprice > 2000
go

此时我们需要用include来包含额外列。

create nonclustered index [idx_salesorderdetail_unitprice] on sales.salesorderdetail(unitprice) include(unitpricediscount)

我们再创建一个过滤索引同时包括额外列

create nonclustered index [idxwhere_salesorderdetail_unitprice] on sales.salesorderdetail(unitprice) include(unitpricediscount)
where unitprice > 2000

接下来再来执行比较添加过滤索引和未添加过滤索引同时都包括了额外列的性能查询差异。

select salesorderdetailid, unitprice, unitpricediscount
from adventureworks2012.sales.salesorderdetail with(index([idx_salesorderdetail_unitprice]))
where unitprice > 2000 
select salesorderdetailid, unitprice, unitpricediscount
from sales.salesorderdetail with(index([idxwhere_salesorderdetail_unitprice]))
where unitprice > 2000

此时性能用include来包含额外列性能也得到了一定的改善。

过滤索引，在主键上创建非聚集索引（2）

在第一个案列中，我们可以直接在查询列上创建非聚集索引，因为其类型是数字类型，要是查询条件是字符类型呢？首选现在我们先创建一个测试表

use tsql2012
go
create table dbo.testdata 
(
  rowid    integer identity not null, 
  somevalue  varchar(max) not null,   
  startdate  date not null,
  constraint pk_data_rowid
    primary key clustered (rowid)
);

添加10万条测试数据

use tsql2012
go
insert dbo.testdata with (tablockx)
  (somevalue, startdate)
select
  cast(n.n as varchar(max)) + 'jeffckywang',
  dateadd(day, (n.n - 1) % 31, '20140101')
from dbo.nums as n
where 
  n.n >= 1 
  and n.n < 100001;

如果我们需要获取表testdata中somevalue = 'jeffckywang'，此时我们想要在somevalue上创建一个非聚集索引然后进行过滤，如下

use tsql2012
go
create nonclustered index idx_noncls_somevalue
on dbo.testdata(somevalue)
where somevalue = 'jeffckywang'

更新

sql server对创建索引大小有限制，最大是900字节，上述直接写的varchar(max)，所以会出错，切记，切记。

此时我们在主键上创建非聚集索引，我们在主键rowid上创建一个过滤索引且somevalue = 'jeffckywang'，然后返回数据，如下：

create nonclustered index idxwhere_noncls_somevalue
on dbo.testdata(rowid)
where somevalue = 'jeffckywang'

下面我们来对比建立过滤索引前后查询计划结果：

use tsql2012
go
select rowid, somevalue, startdate 
from dbo.testdata with(index([idx_pk_rowid]))
where somevalue = 'jeffckywang'
select rowid, somevalue, startdate 
from dbo.testdata with(index([idxwhere_noncls_somevalue]))
where somevalue = 'jeffckywang'

然后结合之前所学，移除key lookup，对创建的过滤索引进行include。

create nonclustered index [idxwhere_noncls_somevalue] on dbo.testdata(rowid) include(somevalue,startdate) 
where somevalue = 'jeffckywang'

从这里看出，无论是对查询条件创建过滤索引还是对主键创建过滤索引，我们都可以通过结合之前所学来提高查询性能。

我们从开头就一直在讲创建过滤索引，那么创建过滤索引优点的条件到底是什么？

（1）只能通过非聚集索引进行创建。

（2）如果在视图上创建过滤索引，此视图必须是持久化视图。

（3）不能在全文索引上创建过滤索引。

过滤索引的优点

（1）减少索引维护成本：对于增、删、改等操作不需要代价没有那么昂贵，因为一个过滤索引的重建不需要耗时太多时间。

（2）减少存储成本：过滤索引的存储占用空间很小。

（3）更精确的统计：通过在where条件上创建过滤索引比全表统计结果更加精确。

（4）优化查询性能：通过查询计划可以看出其高效性。

讲到这里为止，一直陈述的是过滤索引的好处和优点，已经将其捧上天了，其实其缺点也是显而易见。

过滤索引缺点

最大的缺点则是查询条件的限制。其查询条件仅限于

<filter_predicate> ::=  
  <conjunct> [ and <conjunct> ] 
<conjunct> ::= 
  <disjunct> | <comparison>  
<disjunct> ::= 
    column_name in (constant ,...n)

过滤条件仅限于and、|、in。比较条件仅限于 { is | is not | = | <> | != | > | >= | !> | < | <= | !< }，所以如下利用like不行

create nonclustered index [idxwhere_noncls_somevalue] on dbo.testdata(rowid) include(somevalue,startdate) 
where somevalue like 'jeffckywang%'

如下可以

use adventureworks2012
go
create nonclustered index idx_salesorderdetail_modifieddate
on sales.salesorderdetail(modifieddate)
where modifieddate >= '2008-01-01' and modifieddate <= '2008-01-07'
go

如下却不行

create nonclustered index idx_salesorderdetail_modifieddate
on sales.salesorderdetail(modifieddate)
where modifieddate = getdate()
go

变量对过滤索引影响

上述我们创建过滤索引在查询条件上直接定义的字符串，如下：

create nonclustered index idxwhere_salesorderdetail_unitprice
on sales.salesorderdetail(unitprice)
where unitprice > 1000

如果定义的是变量，利用变量来进行比较会如何呢？首先我们创建一个过滤索引

create nonclustered index idx_salesorderdetail_productid 
on sales.salesorderdetail (productid)
where productid = 870

利用变量来和查询条件比较，强制使用过滤索引（默认情况下走聚集索引）

use adventureworks2012
go
declare @productid int 
set @productid = 870 
select productid 
from sales.salesorderdetail with(index([idx_salesorderdetail_productid]))
where productid = @productid

查看查询执行计划结果却出错了，此时我们需要添加option重新编译，如下：

use adventureworks2012
go
declare @productid int 
set @productid = 870 
select productid 
from sales.salesorderdetail
where productid = @productid
option(recompile)

上述利用变量来查询最后通过option重新编译在sql server 2012中测试好使，至于其他版本未知，参考资料【the pains of filtered indexes】。

总结

本节我们学习了通过过滤索引来提高查询性能，同时也给出了其不同的场景以及其使用优点和明显的缺点。简短的内容，深入的理解，我们下节再会，good night。

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，同时也希望多多支持！