【翻译】SQL Server索引进阶：第七级，过滤的索引

过滤一个索引

创建一个包含where子句的过滤的非聚集索引。

1. IF EXISTS ( SELECT  * 
2.             FROM    sys.indexes 
3.             WHERE   OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail') 
4.                     AND name = 'FI_SpecialOfferID' )  
5.     DROP INDEX Sales.SalesOrderDetail.FI_SpecialOfferID ; 
6. GO 
7.  
8. CREATE INDEX FI_SpecialOfferID 
9. ON Sales.SalesOrderDetail (SpecialOfferID) 
10. WHERE SpecialOfferID; 

过滤一个索引的主要原因，是为了从索引中消除一个或者多个不经常被选择的值。看一下SalesOrderDetail表中的SpecialOfferID列，121317行的数据包含了12个不同的SpecialOfferID值，从1到16，每个值对应的行数如下。

SpecialOfferID RowCount
-------------- -----------

1              115884
2              3428
3              606
13             524
14             244
16             169
7              137
8              98
11             84
4              80
9              61
5              2

大部分的行，超过95%，SpecialOfferID的值是1.在SpecialOfferID列的非聚集索引对于SpecialOfferID=1的查询没有好处。查询将使用表扫描来查询115884行数据。但是，所以对于SpecialOfferID=5的查询是有好处的。

本文开头的创建索引对于115884行SpecialOfferID=1的数据行没有入口。因此索引很小，很高效，只包含5433个入口。

在我们的SalesOrderDetail例子中，需要过滤的主要值是“1”。在你自己的应用中，最常见的可能是NULL。在典型的事务数据库中，在可空的列中，如果null值占多数，NOT NULL就是例外。在这些列上创建索引的时候，要考虑过滤null值。

概念证明

为了证明过滤索引的好处，我们将六次执行下面的查询：

• 三次在没有过滤的索引上执行，SpecialOfferID的值分别是：1,13,14。
• 三次在有过滤的索引上执行，参数和上面的一样。

1. SELECT  * 
2. FROM    Sales.SalesOrderDetail 
3. WHERE   SpecialOfferID = parameter value
   
4. ORDER BY SpecialOfferID ; 

在上面的统计中可以看出来，1的行数占95%，13的行数占4%，14的行数占2%。

和往常一样，我们使用IO读取次数作为主要的衡量指标，同时打开SQL Server管理器的“显示执行计划”选项，观察每次查询的执行计划。

执行结果的统计如下。

从上面可以看出来吗，不管用没用到索引，结果是一样的。换句话说，过滤的索引和非过滤的索引带来的好处是一样的。在不影响查询效率的情况下，我们节省了大量的磁盘空间。

过滤，查询，覆盖

在上面的例子中，对同一个索引来说，过滤列和索引键列是同一个列。当我们通过where子句指定过滤的时候，我们告诉SQL Server：“如果你查询那些SpecialOfferID<>1的行，这个索引有这些行的入口。”不管索引键是什么，让SQL Server知道这些信息都是有好处的。

想一想我们之前创建的一个索引，帮助仓库管理员查询SalesOrderDetail表中产品相关的信息。

1. CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate 
2.    ON Sales.SalesOrderDetail (ProductID,ModifiedDate) 
3.    INCLUDE (OrderQty,UnitPrice,LineTotal) ; 

上面的索引产生的结果是。

:- Search Key Columns -:      : ---   Included Columns  ---:    :---   Bookmark   ---:

ProductID   ModifiedDate      OrderQty UnitPrice LineTotal      OrderId     DetailId
----------- ------------      -------- --------- ---------      ----------- ----------

Page n-1:

709         01 Feb 2002       1            5.70       5.70      45329       6392
709         01 May 2002       1            5.70       5.70      46047       8601
710         01 Jul 2001       1            5.70       5.70      43670       111
710         01 Jul 2001       1            5.70       5.70      43676       152
710         01 Sep 2001       1            5.70       5.70      44075       1448

Page n:

710         01 Oct 2001       1            5.70       5.70      44303       2481
710         01 Nov 2001       1            5.70       5.70      44484       2853
710         01 Nov 2001       1            5.70       5.70      44499       3006
710         01 Nov 2001       1            5.70       5.70      44523       3346
710         01 Nov 2001       1            5.70       5.70      44527       3400

如果仓库管理员经常查询SpecialOfferID<>1的信息，很少查询=1的信息，在创建索引的时候添加一个SpecialOfferID!=1的where子句是很有意义的。结果就是很小的索引就可以覆盖大部分的请求。

1. CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate 
2.   ON Sales.SalesOrderDetail (ProductID,ModifiedDate) 
3.   INCLUDE (OrderQty,UnitPrice,LineTotal) 
4.   WHERE SpecialOfferID <>1 

执行下面的查询语句。

1. SELECT  ProductID , 
2.         ModifiedDate , 
3.         SUM(OrderQty) 'No of Items' , 
4.         AVG(UnitPrice) 'Avg Price' , 
5.         SUM(LineTotal) 'Total Value' 
6. FROM    Sales.SalesOrderDetail 
7. WHERE   SpecialOfferID <> 1 
8. GROUP BY ProductID , 
9.         ModifiedDate 

SQL Server管理器告诉我们，过滤的索引被扫描，读取了36页，产生2102条结果。

一些警告

在决定使用过滤索引的时候，要记住两个重要的问题。

1 SQL Server如何评估过滤的索引

你可能会很奇怪，把之前查询语句的where条件从SpecialOfferID<>1变成SpecialOfferID=2，就会防止SQL Server使用过滤的索引。这是因为SQL Server比较了select查询的where子句和create index的where子句，认为他们两个语法上是相等的，而不是比较逻辑的相等。因此，SQL Server没有意识到过滤的索引覆盖了查询。

另外，你不能通过复合的where子句，例如：where SpecialOfferID<>1 and SpecialOfferID=2来促使SQL Server使用过滤的索引。在后面的级别中，我们将会给出一些提示，教给你一些影响SQL Server选择索引的能力。现在，记住SQL Server在评估过滤索引的时候，做出的是语法的决定。

2 不要使用过滤的索引来弥补不好的数据库设计

在创建过滤索引的时候，不要创建索引来弥补违反三范式的数据库设计。

大部分违反三范式的数据库设计，主要是对于实体的子类型认识错误。看一下下面的一张表。

很容易看出表中包含两种类型的商品：Book书和Periodical期刊。只有书才有Author作者信息，只有期刊才有IssuesPerYear每年的刊数信息。正确的方法是，一张主表来存放公共的信息，然后每个子类型附件一张表。每张表都有相同的主键，子类型表的主键也是连接主表的外键。

Products Table

Books table

Periodicals table

像上面的情况，可以建立过滤的索引，来过滤NULL列。

但是正确的做法是重新定义表结构。应用开发者和开发工具不知道你设计的索引，他们只能看见你的表。如果表的结构不影响业务的结构，开发者将会尽力的构建和维护数据库之上的应用。

结论

过滤的索引消除了索引中无用的入口，产生的索引更小，更有利于查询。过滤的索引是通过在create index中指定where子句来实现的。在where子句中的列不同于索引键的列，也不同于include子句中的列。

如果一张表中的一个子集经常被访问，过滤的索引也能是一个覆盖的索引，也可以导致IO有一个相当大的减少。

不要将创建过滤索引作为正确设计数据库的替代选择。

本文转自 virusswb 51CTO博客，原文链接：http://blog.51cto.com/virusswb/986526，如需转载请自行联系原作者