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

yanyangtian4502

你可能不知道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.如果索引中的数据列有多个，那么每一个列的升序和降序就非常的重要，如果在查询中的列的排序和索引中定义的不一样，成本就很大，反正，就很小。

打完，收工！

arron刘

yanyangtian4502

arron刘 发表于 2012-8-23 08:59

够早啊

arron刘

yanyangtian4502 发表于 2012-8-23 09:00
够早啊

恩啊。学技术当然要笨鸟先飞哇

CareySon

小例子看起来更容易懂

希望到时候讲讲查询在多个索引的情况下选择的套路

yanyangtian4502

CareySon 发表于 2012-8-23 11:59
小例子看起来更容易懂

希望到时候讲讲查询在多个索引的情况下选择的套路

ok

kinwar

支持下~

shanks_gao

支持,
这也是创建复合索引时,第一列的选择是很重要的.

grantguan

受教了，Thanks.

kissmoon

学习了，
没有想过这么细节的问题