SQL Server索引进阶第七篇：过滤的索引

索引设计是数据库设计中比较重要的一个环节，对数据库的性能其中至关重要的作用，但是索引的设计却又不是那么容易的事情，性能也不是那么轻易就获取到的，很多的技术人员因为不恰当的创建索引，最后使得其效果适得其反，可以说“成也索引，败也索引”。

本系列文章来自Stairway to SQL Server Indexes，然后经过我们团队的理解和整理发布在agilesharp，希望对广大的技术朋友在如何使用索引上有所帮助。

系列文章索目录：

SQL Server索引进阶第一篇：索引介绍
SQL Server索引进阶第二篇：深入非聚集索引
SQL Server索引进阶第三篇：聚集索引
SQL Server索引进阶第四篇：页和区

SQL Server索引进阶第五篇：索引包含列
SQL Server索引进阶第六篇：书签
SQL Server索引进阶第七篇：过滤的索引

在之前的文章中，我们已经介绍了很多有关索引的知识，不管是对聚集索引还是非聚集索引，有一点我们可以知道就是：在底层数据表中的每一行，在索引页中都有一个条目与之前对应，换句话说，就是，如果底层的数据有，假设10w条，那么在索引结构中必然会包含10w个引用指向这些数据行。

在SQL Server 2008以后的版本中，引入了一个特殊的“过滤的索引”改变了这种情况，而且也相应的带来了很多的好处，例如节省磁盘空间，节省内存，节省CPU，最后使得性能提升。

那么，我们本篇文章就来看看如何使用这个“过滤的索引“。

过滤的索引的使用

首先，我们来看看过滤的索引的定义方法，其实很简单，“过滤“顾名思义就是加上条件判断，过滤的索引，就是在定义索引的使用，加上过滤条件，示例代码如下：

IF EXISTS ( SELECT * FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail')
AND name = 'FI_SpecialOfferID' )
DROP INDEX Sales.SalesOrderDetail.FI_SpecialOfferID
GO
CREATE INDEX FI_SpecialOfferID
ON Sales.SalesOrderDetail (SpecialOfferID)
WHERE SpecialOfferID<>1;

复制代码

从代码中可以看出：定义过滤的索引的语法和之前的定义索引的语法没有什么差别，只是多了个Where条件。

在代码中，我们依然采用的示例数据库AdventureWorks中的SalesOrderDetail'表。如果了解索引定义的一般规格的朋友们都知道：如果想在某个字段上面建立一个索引，一定要考虑这个字段的选择率高不高（选择率简单的讲就是字段中包含的唯一的值占字段所有值的比例）。

通过分析数据，我们可以得到SpecialOfferID字段的一个数据的分布情况，如下：

其实可以看得出，虽然表中包含了121,317行数据，其实在这些数据中，SpecialOfferID的唯一的值也是有16个，即从1到16，选择率很低为：(16/121317)*100%。即使我们在上面建立索引，那么查询优化器最后可能会进行过滤这个字段的时候进行整表的扫描，而不会使用建立的索引。

并且，在上面的数据中，SpecialOfferID值为1的数据行占所有数据行95%。所以，就算我们建立了索引，但是会进行整表扫描。朋友们就要问了：如果在查询中的过滤条件是SpecialOfferID=5，或者是其他的但不是1的值，还是会进行整表的扫描吗？回答：是的。

因为现在的情况是：在索引的结构中，每一个数据行都要一个条目与之对应，也就是说，索引中包含很多的重复的数据，那么优化器会认为：与其先扫索引，在查找数据表，还不如直接扫描数据表来的高效，事实也确实如此。

朋友们，就要问了：凭什么SpecialOfferID值为1的数据行，影响了其他的数据行呢，如果抛开值为1的数据，还有5433行数据，此时的SpecialOfferID选择率就是(11/5433)*100%，虽然不是很高，还没有达到一些建议的值（75%以上，当然，要酌情考虑的，不是一个定死的值），但是比之前高多了？或者说，如果不存在了SpecialOfferID为1的数据，只存在其他的，如果再在上面上面索引，会不会好些呢，这就是我们现在要讲的过滤的索引的作用：在建立索引的时候，把SpecialOfferID为1的数据不要，而是对其他的值建立索引。

所以我们上面的定义过滤的索引的语句就可以这样的解读：建立索引的时候，如果遇到SpecialOfferID值为1的数据行，那么就不把这一行上面的SpecialOfferID值包含在索引中，否则，就包含。

不仅仅只有这个例子，过滤的索引的作用是非常的大的。例如，在我们的某些应用程序的数据库中，当我们要在某些字段上面建立索引的时候，发现有一些值是null，此时，我们就可以过滤掉这个null等。

通过示例演示过滤的索引的高效性

光说不练可不行，我们就来看个例子。在下面的演示中，我们将会运行6个查询：

1.在没有建立过滤的索引的前提下，将过滤的参数分别设置为1,13,14，然后各自运行一次。

2.在建立过滤索引的前提下，将参与设置为1,13,14，然后在各自运行一次。

查询语句的模板如下：

SELECT *
FROM Sales.SalesOrderDetail
WHERE SpecialOfferID = <parameter value>
ORDER BY SpecialOfferID ;

复制代码

其实我们通过之前的数据分布，知道查询的结果的数据的比例：值为1的，占了95%，值为13的，占了4%，而值为14的，占2%。

通过运行之后，查看实际的执行计划，得到相关的信息，整理如下：

可以看出：从执行计划看出，前后的成本是一样的。但是其实有着巨大的差别：就是索引结构存储所需要的磁盘空间，以及把索引结构读入到内存，内存的占用量，以及读索引数据到内存中的时候的锁资源的使用。使用过滤的索引，在这方面都会很小。

过滤的索引进阶

在上面过滤的索引的示例中，索引中的数据列和过滤列是一样的，如，我们在SpecialOfferID上面建立索引，同时也过滤掉SpecialOfferID不为1的数据行。

其实，我们可以将之使用的更加灵活。例如，我们可以在SpecialOfferID上面建立索引，但是按照其他的数据列的值进行过滤。如下代码所示：

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

复制代码

然后运行一个查询：

SELECT ProductID ,
ModifiedDate ,
SUM(OrderQty) 'No of Items' ,
AVG(UnitPrice) 'Avg Price' ,
SUM(LineTotal) 'Total Value'
FROM Sales.SalesOrderDetail
WHERE SpecialOfferID <> 1
GROUP BY ProductID ,
ModifiedDate

复制代码

运行上面的查询就可以知道，通过使用过滤的索引之后，查询只是扫描了36个索引页（因为这个所有的数据都包含在索引结构中了，没有必要扫描底层的数据表），就找出所需要的2012行数据。

使用过滤的索引常见误区

工具好了，有人一兴奋，肯定会有人会乱用的，最后用的不好，就反过来怪工具不好，其实说到底，还是人，因为工具是死的，人是活的。就好比有人在用.NET开发应用一样，出来的东西性能很差，常常挂掉，然后就怪微软不行，认为.NET太差。出现问题之后，第一反应就是推卸责任，找替死鬼，这是人之常情，但是仔细想想到底是谁之过呢！所以，希望朋友们不要成为“怨妇“，”怨夫“，把精力放在解决问题之上。

言归正传，下面，我们就来看看在使用过滤的索引的时候，必须注意哪些地方。

过滤的索引的条件定义问题

在上面的定义过滤的索引的代码中，采用了Where来进行定义。这个时候，我们可能就会发散思维：如果我们只要索引中包含有SpecialOfferID为2的值，是否可以把条件从“SpecialOfferID <>1“改为”SpecialOfferID =2“。回答是：NO。

原因就是在创建过滤的索引中使用的Where的过滤条件不是一个布尔的逻辑表达式，而是一个基于词汇的表达式（大家只要知道这些就行了）。所以，我们也不能在Where中进行过条件的拼接，如”WHERE SpecialOfferID <> 1 AND SpecialOfferID = 2“。

posted on 2012-12-03 11:29 蓝海豹阅读(454) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

蓝海豹

公告