全部博文(141)
分类: 数据库开发技术
2012-08-23 08:58:15
你可能不知道
SQL Server索引列的升序和降序带来的性能问题当我们在创建索引的时候,很多时候,我们都是采用了一些默认的选项,如,使得索引中的数据列采用升序。我们一般认为,在创建索引的时候,加入索引中的列采用升序还是降序问题不大,因为索引中的索引页可以双向的导航,也就是说,索引结构在垂直方向是B树,在水平方向是双向链表,给个图大家就明白了:
初一看,从上面的结构可以看得出,不管以何种顺序创建索引中的列,效果是一样的,因为扫描索引结构无非就是两种:向前和向后,既然上面的结构是个双向的,那么就一样了。
好,我们就来看看,到底是不是这么回事!
我们这里主要几个例子来证明和讲述一些问题。
首先,我们在创建索引的时候,可以在SQL 语句中定义列以何种顺序进行,如下,降序:
升序如下:
好,知道了基本的语法之后,我们就正式进入实验环节,采用SQL Server自带的AdventureWorks示例数据库。
1.排序的数据列上面没有索引,在查询中对数据进行升序排列,查询语句:
执行计划如下:
因为这个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.我们将查询和索引都修改一下。
查询如下:
索引如下:
执行计划如下:
成本是0.0033123,这有何意义呢,接着看就明白了。
7.修改查询排序条件为:[OrderDate] ASC, [SubTotal] desc
还是采用第6个例子中的索引,但是我们现在把查询改为了:
执行计划如下:
成本是0.102122比之前那个大了很多。
我们看完最后一个例子,开始做结论。希望大家保持清醒的头脑,不要被这么多的例子搞糊涂了。
8.修改索引,使得列的排序为:修改查询排序条件为:[OrderDate] ASC, [SubTotal] desc
修改索引定义,如下:
然后再次执行第8个例子中的查询,如下:
好,成本小了很多。
看完了上面几个8个示例,是时候总结了,不然,大家就更加的晕了:
1.在创建索引的时候,如果索引中的数据列只有一个,那么不管是升序还是降序的定义,在查询中使用成本都是一样的。
2.如果索引中的数据列有多个,那么每一个列的升序和降序就非常的重要,如果在查询中的列的排序和索引中定义的不一样,成本就很大,反正,就很小。
打完,收工!