详解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

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