Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1142751
  • 博文数量: 141
  • 博客积分: 3161
  • 博客等级: 中校
  • 技术积分: 3011
  • 用 户 组: 普通用户
  • 注册时间: 2011-09-27 14:53
文章存档

2012年(28)

2011年(113)

分类: 数据库开发技术

2012-08-23 08:58:15

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

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



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


好,我们就来看看,到底是不是这么回事!

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


首先,我们在创建索引的时候,可以在SQL 语句中定义列以何种顺序进行,如下,降序:

  1. CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
  2. ON [Purchasing].[PurchaseOrderHeader]
  3. ( [OrderDate] DESC )
复制代码


升序如下:

  1. CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
  2. ON [Purchasing].[PurchaseOrderHeader]
  3. ( [OrderDate] ASC )
复制代码


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


1.排序的数据列上面没有索引,在查询中对数据进行升序排列,查询语句:

  1. Select top 10 OrderDate from Pubchasing.PurchaseOrderHeader order by OrderDate
复制代码


执行计划如下:



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

这个查询的成本是0.124344。


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

执行计划如下:


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


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

我们来看看执行计划:



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


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

执行计划如下:



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

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


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

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

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

执行计划如下:



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


查询如下:

  1. Select top 10 OrderDate,SubTotal from Pubchasing.PurchaseOrderHeader order by OrderDate asc, SubTotal asc
复制代码

索引如下:

  1. CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
  2. ON [Purchasing].[PurchaseOrderHeader]
  3. ( [OrderDate] ASC, [SubTotal] ASC )
复制代码



执行计划如下:



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


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

还是采用第6个例子中的索引,但是我们现在把查询改为了:

  1. Select top 10 OrderDate,SubTotal from Pubchasing.PurchaseOrderHeader order by OrderDate asc, SubTotal desc
复制代码


执行计划如下:




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


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


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


修改索引定义,如下:

  1. CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
  2. ON [Purchasing].[PurchaseOrderHeader]
  3. ( [OrderDate] ASC, [SubTotal] DESC )
复制代码

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



好,成本小了很多。


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


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

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

打完,收工!

阅读(10818) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~