索引深入浅出(9/10)：过滤索引

过滤索引（Filtered index ）是在SQL Server 2008里新引入的功能。到目前我们谈到的索引都是在建立在整张表上的。换句话说，索引和表有一样的记录树。使用过滤索引，我们可以创建表子集的索引。这个可以通过创建索引的时候加上where子语完成。这个可以帮助在存储上减小索引的大小同样索引的深度。在索引创建语句的where条件决定了索引里是否包含该记录。

这在大表上是一个巨大的性能提升，如果有大量的查询只查表的部分数据。常规索引都是建立在整个表上的，而忽略了大多数查询只查表的一部分数据的事实。这样话常规索引会增加索引的深度，需要更多的页来存储B树结构，从而带来更多的IO操作。而过滤索引只在表的部分数据上创建，因此需要更少的页来存储索引。

我们以有5年数据的salesorder 表来举例。这个表上大多数活跃的查询是基于去年和今年的。过滤索引的简单例子如下：

CREATE NONCLUSTERED INDEX ix_salesorder_Filter 
ON salesorder(SalesOrderId,OrderDate,Status,Customer_id,TotalDue) 
WHERE OrderDate>'2014-01-01' 

有多个NULL值的唯一列：

过滤索引的一个大量使用是在列上复合唯一限制定义。这个唯一限制，不允许在唯一列上有多个null值。列上的值除去NULL值，怎么保证它其它值唯一？例如，product表上的productode列可以为null，但是那列有值定义的话，就不能为null。我们来看一个例子。

CREATE TABLE Product
    (
      Productid INT NOT NULL  PRIMARY KEY ,
      ProductCode CHAR(10) ,
      ProductName VARCHAR(100)
    )
GO 
CREATE UNIQUE INDEX ix_Unique_Filtered ON Product(Productcode) WHERE productcode IS NOT NULL
GO
INSERT INTO Product VALUES(1,'AR-5381','Adjustable Race')
INSERT INTO Product VALUES(2,NULL,'Bearing Ball')
INSERT INTO Product VALUES(3,NULL,'BB Ball Bearing')
INSERT INTO Product VALUES(4,'AR-5381','Adjustable Race-Small')

我们可以插入produtcode列为NULL的多条记录，但当我们尝试插入有重复值AR-5381的记录是，SQL Server提示错误，那是因为表上的唯一过滤索引帮助我们强制这样的唯一性。

读操作：

上述提高的是覆盖索引的一种常规用法。覆盖索引的另一种用法是支持对应的查询，我们来看一个例子，点击工具栏的显示包含实际的执行计划。

USE IndexDB
GO                               
SELECT * INTO SalesOrderheader FROM AdventureWorks2008r2.Sales.SalesOrderheader
GO
--Unique Clustered index
CREATE UNIQUE CLUSTERED INDEX ix_SalesOrderheader ON SalesOrderheader(SalesOrderid)
GO
--Filtered Index
CREATE INDEX ix_filtered_index ON SalesOrderheader(orderdate) WHERE orderdate>'2008-01-01'

SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE 
orderdate>'2008-05-01'
GO
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE 
orderdate='2008-03-01'
GO
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE 
orderdate='2007-12-01'

我们在orderdate列上定义了过滤索引，条件是日期大于2008-1-1。第一个和第二个查询条件都命中了过滤索引的条件，因此都用到了索引查找；第三个在覆盖索引条件之外，SQL Server只能用聚集索引扫描来找对应的数据。过滤索引对于这类查询有大幅度的性能提升。

过滤列并不一定是索引的一部分。但如果是这个情况的话，查询条件就要和过滤索引的条件完全一致了，我们来看一个例子。

CREATE INDEX ix_TerritoryID_Filter ON SalesOrderheader (OrderDate) WHERE TerritoryID<=5
GO
SELECT salesorderid,orderdate FROM SalesOrderheader WHERE 
TerritoryID<=5
GO
SELECT salesorderid,orderdate FROM SalesOrderheader WHERE 
TerritoryID=4

过滤索引局限性：

过滤索引非常有用，但也有它的局限性，尤其是在参数化查询的时候。我们来看一个例子。

SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>'2008-05-01'

DECLARE @Orderdate date='2008-05-01'
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>@Orderdate 

当我们用本地参数重写我们的查询的时候，查询计划没有用到过滤索引。背后的原因是，在编译期间，查询优化器不知道什么样的值会传给@Orderdate参数。因此优化器生成一个保守的计划来满足所有的条件。当我们修改数据库属性来强制参数化或把语句定义为存储过程也会出现同样的问题。

ALTER DATABASE IndexDB SET parameterization forced
GO
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>'2008-05-01'
GO

CREATE PROCEDURE GetSalesorder (@OrderDate date) 
AS
BEGIN
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate=@OrderDate END 


EXEC GetSalesorder '2008-05-01'

在这些情况下优化器都没有用到过滤索引，因为它在执行的时候不知道传入的值是多少，只能不考虑使用过滤索引生成一个相对保守的计划。

当我们把数据库属性设置为强制参数化时（ALTER DATABASE IndexDB SET parameterization forced），优化器会把所有静态值用本地变量代替。例如下面的语句：

SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>'2008-05-01'

优化器会认为是下列语句：

DECLARE @Orderdate date='2008-05-01'
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>@Orderdate

但在动态生成的语句时，会让SQL Server用到过滤索引：

ALTER DATABASE IndexDB SET PARAMETERIZATION SIMPLE

DECLARE @Orderdate date='2008-05-01'
DECLARE @SQL NVARCHAR(1000)
SET @SQL=N'SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE 
orderdate>'''+CAST(@Orderdate AS CHAR(10))+''''
EXEC (@SQL)
GO 

从执行计划里我们可以看到，查询计划用到了过滤索引。

参考文章： 

http://www.sqlservercentral.com/blogs/practicalsqldba/2013/04/08/sql-server-part-9-filtered-index-a-new-way-for-performance-improvemnt/