[转]你可能不知道SQL Server索引列的升序和降序带来的性能问题

转:

http://www.itpub.net/forum.php?mod=viewthread&tid=1709735

你可能不知道SQL Server索引列的升序和降序带来的性能问题


当我们在创建索引的时候，很多时候，我们都是采用了一些默认的选项，如，使得索引中的数据列采用升序。我们一般认为，在创建索引的时候，加入索引中的列采用升序还是降序问题不大，因为索引中的索引页可以双向的导航，也就是说，索引结构在垂直方向是B树，在水平方向是双向链表，给个图大家就明白了：


IC144198.gif(21.33 K)
8/22/2012 11:08:06 AM



初一看，从上面的结构可以看得出，不管以何种顺序创建索引中的列，效果是一样的，因为扫描索引结构无非就是两种：向前和向后，既然上面的结构是个双向的，那么就一样了。



好，我们就来看看，到底是不是这么回事！

我们这里主要几个例子来证明和讲述一些问题。



首先，我们在创建索引的时候，可以在SQL 语句中定义列以何种顺序进行，如下，降序：
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader]
( [OrderDate] DESC )

复制代码


升序如下：
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader]
( [OrderDate] ASC )

复制代码



好，知道了基本的语法之后，我们就正式进入实验环节，采用SQL Server自带的AdventureWorks示例数据库。


1.排序的数据列上面没有索引，在查询中对数据进行升序排列，查询语句：
Select top 10 OrderDate from Pubchasing.PurchaseOrderHeader order by OrderDate

复制代码



执行计划如下：


1337_descIn7.jpg(38.00 K)
8/22/2012 11:08:06 AM



因为这个PurchaseOrderHeader表上面有一个聚集索引在ID上面，所以此时的数据检索采用的就是聚集索引扫描，其实也就是对整个表进行扫描，只不过是现在表中的数据按照聚集索引进行了排列。这一点应该是很基本的内容，大家应该清楚。

这个查询的成本是0.124344。


2.排序的数据列上面没有索引，在查询中对数据进行降序排列，查询语句：

执行计划如下：
1337_descIn8.jpg(38.31 K)
8/22/2012 11:08:06 AM





可以看出这个查询计划和第一个例子中是一模一样的。原因很简单，大家自己思考一下。


3.在OrderDate上面创建一个索引，并且按照OrderDate的升序排序，查询中的采用OrderDate升序

我们来看看执行计划：


1337_descIn9.jpg(38.07 K)
8/22/2012 11:08:06 AM



其实，计划采用Index Scan，因为此时查询在扫描OrderDate上面的非聚集索引，所花的成本是0.0033056，比之前没有索引的成本小了很多。



4.在OrderDate上面创建一个索引，并且按照OrderDate的升序排序，查询中的采用OrderDate降序

执行计划如下：


1337_descIn10.jpg(37.98 K)
8/22/2012 11:08:06 AM



大家可以发现：此时所花的成本和第3个是一样的，这就是进一步的证明：索引页的双向列表结构使得从前到后，和从后向前的扫描是一样的。

到这里，事情还没有完，接着往下看。



5.在OrderDate上面创建一个索引，并且按照OrderDate的降序排序，查询中的采用OrderDate升序

大家可以猜一下会是这样的结果。

用脚趾头都有可以想到，应该会和前面2个一样。

执行计划如下：


1337_descIn11.jpg(38.65 K)
8/22/2012 11:08:06 AM



6.我们将查询和索引都修改一下。


查询如下：
Select top 10 OrderDate,SubTotal from Pubchasing.PurchaseOrderHeader order by OrderDate asc, SubTotal asc

复制代码


索引如下：
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader]
( [OrderDate] ASC, [SubTotal] ASC )

复制代码




执行计划如下：


1337_descIn13.jpg(37.82 K)
8/22/2012 11:08:06 AM



成本是0.0033123，这有何意义呢，接着看就明白了。



7.修改查询排序条件为：[OrderDate] ASC, [SubTotal] desc

还是采用第6个例子中的索引，但是我们现在把查询改为了：
Select top 10 OrderDate,SubTotal from Pubchasing.PurchaseOrderHeader order by OrderDate asc, SubTotal desc

复制代码


执行计划如下：

1337_descIn14.jpg(39.56 K)
8/22/2012 11:08:06 AM






成本是0.102122比之前那个大了很多。


我们看完最后一个例子，开始做结论。希望大家保持清醒的头脑，不要被这么多的例子搞糊涂了。


8.修改索引，使得列的排序为：修改查询排序条件为：[OrderDate] ASC, [SubTotal] desc



修改索引定义，如下：
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader]
( [OrderDate] ASC, [SubTotal] DESC )

复制代码

然后再次执行第8个例子中的查询，如下：

1337_descIn15.jpg(38.11 K)
8/22/2012 11:08:06 AM





好，成本小了很多。



看完了上面几个8个示例，是时候总结了，不然，大家就更加的晕了：


1.在创建索引的时候，如果索引中的数据列只有一个，那么不管是升序还是降序的定义，在查询中使用成本都是一样的。

2.如果索引中的数据列有多个，那么每一个列的升序和降序就非常的重要，如果在查询中的列的排序和索引中定义的不一样，成本就很大，反正，就很小。

打完，收工！



Server, 索引, 性能