在我们创建主键或者其他索引时，sql server总是自动将字段的顺序设置为升序排列；
升序是默认设置，是为了保持与 SQL Server 早期版本的兼容性。建索引时索引列的顺序应该按照常用查询中的排序方式排序。
我们做个试验创建一个表，其中主键上的聚集索引按照id倒叙排列，然后分别倒叙顺序select数据，比较select的时间：
测试代码
if object_id('test_indexorder','U') is not null
begin
truncate table test_indexorder
drop table test_indexorder
end
go
create table test_indexorder
(
id int identity(1,1) not null,
name varchar(20) not null,
content varchar(50) not null,
co1 varchar(50),
co2 varchar(50),
co3 varchar(50),
co4 varchar(50),
co5 varchar(50),
constraint pk_testorder primary key clustered(
id desc
)
)
go
--insert 1000000 条数据
set nocount on;
declare @t datetime;
set @t = getdate();
DECLARE @cn int;
set @cn = 1000000;
while(@cn > 0)
begin
insert into test_indexorder(name,content,co1,co2,co3,co4,co5)
VALUES(
'name' + cast(@cn as varchar(10)),
cast(newid() as varchar(50)),
cast(newid() as varchar(50)),
cast(newid() as varchar(50)),
cast(newid() as varchar(50)),
cast(newid() as varchar(50)),
cast(newid() as varchar(50)));
set @cn = @cn -1;
end
print '插入时间（毫秒）：';
print datediff(millisecond,@t,getdate());
set nocount off;
GO
checkpoint
dbcc freeproccache
dbcc dropcleanbuffers
GO
go
set nocount on;
declare @t datetime;
set @t = getdate();
with t_rn as (
select *,rn = ROW_NUMBER() OVER (ORDER BY id desc) FROM test_indexorder
)
SELECT id,name,content,co1,co2,co3,co4,co5 from t_rn WHERE rn between 19007 and 19057;
print '查询时间(毫秒)'
print datediff(millisecond,@t,getdate())
set @t = getdate();
with t_rn as (
select *,rn = ROW_NUMBER() OVER (ORDER BY id asc) FROM test_indexorder
)
SELECT id,name,content,co1,co2,co3,co4,co5 from t_rn WHERE rn between 17007 and 17057;
print '查询时间(毫秒)'
print datediff(millisecond,@t,getdate())
set nocount off;
查询时间(毫秒)
393
查询时间(毫秒)
606
按照和索引相同顺序从100万条数据中取50条时需要393毫秒，相反顺序时需要606毫秒。造成的性能影响还是挺大的。
结论：
在建索引时要考虑常用查询的排序方式，在建主键时要特别注意，因为sql server会自动按照升序来建，这时候如果您的查询多数用主键列倒叙排列，记得要修改一下默认的设置。
参考资料：
http://technet.microsoft.com/zh-cn/library/ms181154.aspx
http://forums.microsoft.com/china/ShowPost.aspx?PostID=3307724&SiteID=15
posted on 2008-05-09 13:15
玉开 阅读(1669)
评论(41) 编辑 收藏 网摘 所属分类:
数据库sql server
FeedBack:
http://www.cnblogs.com/Emoticons/yoyocici/223852199.gif" alt="" />路过，学习！
回复 引用 查看
@李战
真的有抢沙发的软件？拿出来给大家分享一下。刺激一下大家看谁的更能强。 回复 引用 查看
要仔细阅读别人的文章，才对得起作者的劳动
抢沙发有什么意思 回复 引用 查看
@玉开
http://www.cnblogs.com/Emoticons/msn/cry_smile.gif" alt="" />俺真的没有什么沙发软件，只是碰巧被您的文章吸引了。
俺是个俗人，午休正在做梦娶媳妇呢，楼主这句“建索引时索引列的顺序应该按照常用查询中的排序方式排序”，一语点醒梦中的俺啊！
http://www.cnblogs.com/Emoticons/qface/055243188.gif" alt="" />
回复 引用 查看
"在建主键时要特别注意，因为sql server会自动按照升序来建"
这句话有点语病 回复 引用 查看
@李战
sorry,看来我误解你了。 ^_^ 回复 引用 查看
@Cheney Shue
语病在哪儿？
我这句话的意思是
因为主键sql server会自动建成按照升序排列的聚集索引，如果查询时经常按照主键倒叙排的话，就要自己注意修改默认的的设置了。 回复 引用 查看
--引用--------------------------------------------------
玉开: @Cheney Shue
语病在哪儿？
我这句话的意思是
因为主键sql server会自动建成按照升序排列的聚集索引，如果查询时经常按照主键倒叙排的话，就要自己注意修改默认的的设置了。
--------------------------------------------------------
我觉得SQL Server不会这么愚蠢，应该是那个该死的企业管理器产生的DDL自动建立升序的聚类索引。 回复 引用 查看
#10楼[
楼主] 2008-05-09 14:27
@kuber
呵呵，要感谢博客园我们提供的环境。
回复 引用 查看
#11楼[
楼主] 2008-05-09 14:29
@玉开
我证明，李战没有抢沙发的软件，不信的话看我的帖子，:)
我感觉不会有这样的想象，我一会测试一下。 回复 引用 查看
#14楼[
楼主] 2008-05-09 17:33
@金色海洋（jyk）
好的，文中有我的测试脚本，期待你的测试结果。
回复 引用 查看
我在sql Server 2000 里测试了一下，确实不一样。
Products表，ProductID 是正序的聚集索引。记录数：250万左右。
执行两个select 语句，一个是倒序，一个是正序，对比先正序、在倒序，和先倒叙在正序，两种方式需要的时间的对比。结果挺奇怪的。
1、先正序在倒序的测试。
declare @t datetime;
set @t = getdate();
--先正序在倒序。
select top 50 * from Products order by ProductID --desc
print '查询时间（毫秒）：';
print datediff(millisecond,@t,getdate());
set @t = getdate();
select top 50 * from Products order by ProductID desc
print '查询时间（毫秒）：';
print datediff(millisecond,@t,getdate());
先正序在倒序的测试结果==================
（所影响的行数为 50 行）
正序查询时间（毫秒）：（记作时间a）
46
（所影响的行数为 50 行）
倒序查询时间（毫秒）：（记作时间b）
63
2、先倒序在正序的测试。
重启sql 服务，把sql语句改成 先倒叙在正序，sql语句就略了。
测试结果
（所影响的行数为 50 行）
倒序查询时间（毫秒）：（记作时间c）
80
（所影响的行数为 50 行）
正序查询时间（毫秒）：（记作时间d）
123
正序所需要的时间反倒多了，挺奇怪的。
进行了多次测试，都是先重启数据库服务（不重启的话都是0毫秒），虽然每次的时间都不太一样，
但是 时间d 总是比 时间c 大。
时间b 总是比 时间a 大。
3、单独执行 正序查询
正序查询时间（毫秒）：
16
4、单独执行倒序查询
倒序查询时间（毫秒）：
80
挺奇怪的，不管怎么查，倒序需要的时间总是80毫秒左右，而正序却波动很大。从16毫秒到46毫秒，在到100多毫秒。
不知道是不是我的测试方法不对。
回复 引用 查看
我在用 sql server 2005 测一下。
sql server 2005 还不熟呢，好像是第三次用，正在研究中。 回复 引用 查看
被害了，添加100万条数据，花了 326733毫秒，也就是 5分26秒。看来机器够慢的了。
1、先正序在倒序（和lz的一样）
查询时间(毫秒)
250
查询时间(毫秒)
406
（居然比lz的快，我的cpu是AMD3000+的）
在此查询（没有重启）
查询时间(毫秒)
63
查询时间(毫秒)
266
在此查询（重启服务）
查询时间(毫秒)
190
查询时间(毫秒)
356
2、先倒序在正序
查询时间(毫秒)
200
查询时间(毫秒)
376
在此查询（没有重启）
查询时间(毫秒)
63
查询时间(毫秒)
266
3、单独执行正序
查询时间(毫秒)
203
在此查询（没有重启）
查询时间(毫秒)
46
4、单独执行倒序
查询时间(毫秒)
203
在此查询（没有重启）
查询时间(毫秒)
63
=============
看来 SQL Server 2005 已经改进了一点，先执行哪个，哪个就能快一点，后执行的就会慢一点。
呵呵。
再试一试我的分页算法。
回复 引用 查看
set nocount on;
declare @t datetime;
set @t = getdate();
declare @col int
select top 19007 @col = id from test_indexorder order by id --desc
select top 50 * from test_indexorder where id > @col order by id
print '定位法(毫秒)'
print datediff(millisecond,@t,getdate())
set nocount off;
我的分页算法。
测试结果和使用 ROW_NUMBER 的方法基本一致。
lz 感兴趣的话，也可以试一试。
第一次查询
查询时间(毫秒)
203
在此查询
定位法(毫秒)
60
回复 引用 查看
看来，当数据库从2000升级到2005之后，我的分页控件内部使用的分页算法，就可以改成使用 ROW_NUMBER 的方法了，
升级的时候，不需要修改项目里的任何的代码，只升级分页控件就可以了。
数据库也不用做修改（在分页方面），因为没有使用存储过程来分页。
回复 引用 查看
两个排序字段的测试
set nocount on;
declare @t datetime;
set @t = getdate();
with t_rn as (
select *,rn = ROW_NUMBER() OVER (ORDER BY name ,id asc) FROM test_indexorder
)
SELECT id,name,content,co1,co2,co3,co4,co5 from t_rn WHERE rn between 19007 and 19057;
print '查询时间(毫秒)'
print datediff(millisecond,@t,getdate())
1、没加 name 相关的索引
查询时间(毫秒)
38953
2、添加非聚集索引 name,id
第一次查询
查询时间(毫秒)
733
在此查询
查询时间(毫秒)
216
在在此查询
查询时间(毫秒)
156
3、颠倒法分页。
set nocount on;
declare @t datetime;
set @t = getdate();
select * from test_indexorder where id in
( select top 50 id from
(select top 19056 id , [name] from test_indexorder
order by [name] asc, id asc
) as aa
order by [name] desc, id desc
)
order by [name] , id
print '颠倒法(毫秒)'
print datediff(millisecond,@t,getdate())
set nocount off;
第一次查询
颠倒法(毫秒)
1093
在此查询
颠倒法(毫秒)
1093
看来还是 ROW_NUMBER() 的方式快，不过也不怕，改一下分页控件内部的分页算法就可以了，调用分页控件的部分不用作修改。
整个项目也可以平稳升级到 2005 系列。至少是分页相关的部分。
感谢lz，我正想找一个 ROW_NUMBER() 的分页方式作一下对比测试呢。
另外 between 19007 and 19057 是51条记录。:)
回复 引用 查看
留意一下，对于这样的细节问题的确怎么注意过。
学习，mark 回复 引用
再看一下表变量 + identity 的方法。
就是吴旗娃的分页控件采用的方法。我知道在那个控件的网站上，代码工具里面已经区分了SQL Server 2000 和SQL Server 2005 ，其中05 已经改成了 ROW_NUMBER() 的方式。
刚看到的，我这里只是想测试一下，在SQL Server 2005里面 表变量 + identity 的效率。
set nocount on;
declare @t datetime;
set @t = getdate();
declare @pagesize int
declare @pageindex int
set @pagesize = 50
set @pageindex = 380
set nocount on
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select id from test_indexorder order by name,id
select * from test_indexorder O,@indextable t where O.id=t.nid
and t.id between @PageLowerBound+1 and @PageUpperBound order by t.id
--set nocount off
print '颠倒法(毫秒)'
print datediff(millisecond,@t,getdate())
set nocount off;
第一次查询
表变量法(毫秒)
23453
在此查询
表变量法(毫秒)
263
在此查询（重启服务）
表变量法(毫秒)
22486
奇怪，为什么第一次查询这么慢呀？cpu和内存占用率一点都不高呀。
跳到 1380页
表变量(毫秒)
13063
颠倒法跳页
颠倒法(毫秒)
3893
好像2005给 表变量 加了一个聚集索引，所以再次查询的时候才会比较快。
比较晕 回复 引用 查看
@金色海洋（jyk）
数据量上万的都要使用临时表，并且在生产服务器上将临时库指定到单独的磁盘上去，并将临时库的指定大小设成比较大，具体看自己环境的实际应用。 回复 引用 查看
@金色海洋（jyk）
没有必要使用
print datediff(millisecond,@t,getdate());
使用ＭＳ提供的统计信息就可以了。
set statistics io on
set statistics time on 回复 引用 查看
在我的机器上使用８万条记录，正序倒序都是一样的。
而且我目前也没有看到有文档说索引的正序和倒序会有性能影响，倒是复合列的头列是哪个有影响。
ｐｓ，开始误会你的意思以为说的复合列列顺序问题 回复 引用 查看
索引的本质是树，至于树的排序，如果熟悉2叉树算法的都知道正序或者倒序都是一棵树，只是在扫描整棵树的时候是从左边开始扫描还是从右边开始扫描
回复 引用 查看
而如果表存在聚集索引，那么页面与页面之间就是双向链表关系，这种关系具体反映在索引的顺序上是无所谓的 回复 引用 查看
还真是个以前没有注意到的地方，感谢博主:)
@金色海洋（jyk）
真是不辞辛劳啊^_^ 回复 引用 查看
我主要是学习一下 row_Number的分页算法，然后又顺便和自己的分页算法做了一下对比。
回复 引用 查看
我觉得这种思考方式很不错，但是结论不要这样推出，因为这是有问题的
我们应该从SQLSERVER的索引结构和数据存储结构，还有查找算法来得出结论，而不是一两个测试，除非你的测试很多很多……我简单说说我的看法：
聚集索引的确是按顺序排列的（不过绝对不是物理顺序排列，至少不完全是，最多是部分物理顺序排列），那么，在已经排序的序列中进行查找（BineraySearch大家都是知道的，当然，这只对内存数据有效，“页面”也就是用来干这个的），正序和反序（或者说头、尾）的读取平均概率应该是差不多的。再者，SQLSERVER的索引就B树这一种，那么既然有了B树，就说明不管正序还是反序，要找到一条或一群记录所需要读取的页面不会特别多，如果再考虑上SQLSERVER2005那改进过的缓存机制的话，主要的IO基本就集中在叶子级上了，这样的话，正序和反序基本上就没有区别了。
所以，我觉得虽然索引可以ASC或DESC，对长期运行的系统来说，实际上是没有什么影响的。
回复 引用 查看
不好意思，刚才没有完全看完，lz说的是排序的问题
这样的话，结论就没有什么大问题了。
应该考虑一下sqlserver到底是如何排序的以及到底是在哪排序的（仍然需要理解数据存储形式以及索引存储形式），这些信息将严重影响数据库的性能，绝对不会仅仅文中的测试结果那么几百毫秒，不考虑这部分内容的话，性能差距可能会上升为秒级或十秒级的……
总结一下，如果只是查找的话，索引顺序几乎不会有任何影响。如果有排序动作的话，就有影响了，不过还是要考虑量的问题。
最后，我觉得，考虑排序的工作量不小的，如果没有特别需要的话，数据库设计的时候可以不用理会的，相信不会有人经常一次取出几十万条数据来进行排序处理吧？ 回复 引用 查看
--引用--------------------------------------------------
电机拖动: 不好意思，刚才没有完全看完，lz说的是排序的问题 <br />
<br />
这样的话，结论就没有什么大问题了。<br />
<br />
应该考虑一下sqlserver到底是如何排序的以及到底是在哪排序的（仍然需要理解数据存储形式以及索引存储形式），这些信息将严重影响数据库的性能，绝对不会仅仅文中的测试结果那么几百毫秒，不考虑这部分内容的话，性能差距可能会上升为秒级或十秒级的…… <br />
<br />
<span style="color: red">总结一下，如果只是查找的话，索引顺序几乎不会有任何影响。如果有排序动作的话，就有影响了，不过还是要考虑量的问题。</span><br />
<br />
最后，我觉得，考虑排序的工作量不小的，如果没有特别需要的话，数据库设计的时候可以不用理会的，相信不会有人经常一次取出几十万条数据来进行排序处理吧？
--------------------------------------------------------
翻页就会这样，几十万记录来回排序 回复 引用 查看
@PerfectDesign
--引用--------------------------------------------------
翻页就会这样，几十万记录来回排序
--------------------------------------------------------
如果每页显示20条，共20W条数据，也就是说有1W页
请问是否有人真的会看完这1W页？如果你说有SORT这样的操作，那么正反序又成一样的了。
不知道是英才还是智联，查询后只显示前2000条数据，这是为什么？不是说什么数据都要拿出来显示的
此外，索引的排序就算能够增加查询（针对特定查询，很多时候说不定会降低查询效率，那要看你是不是能准确分析数据库的用途了）的效率，那么修改（C/U/D）的时候呢？ 回复 引用 查看
@电机拖动
请问是否有人真的会看完这1W页？
这个我只是说一旦有翻页，不管是否看完所有的1w页，都会有排序发生的。
那么修改（C/U/D）的时候呢？
你说的索引值不值得的问题那就是个大话题了，我只是说如果想得到最快速度，并没说索引带来的其他弊端了
回复 引用 查看
#38楼[
楼主] 2008-05-12 08:58
@金色海洋（jyk）
多谢了，麻烦你测试了这么多次
可以使用如下语句清空缓存。
GO
checkpoint
dbcc freeproccache
dbcc dropcleanbuffers
GO
另外，理论上来讲，清空缓存之后第一次查需要的时间应该长一些，因为需要将索引载入到内存中，第二次查时索引已经在内存中了，少了载入时间。 回复 引用 查看
#39楼[
楼主] 2008-05-12 09:01
@电机拖动
@PerfectDesign
其实索引本来就是双刃剑，会提高查询速度，肯定减缓写入速度。
只是我们通常情况下总是查询和写数据的比例在100：1甚至更高，所以才建索引提高查询速度。
回复 引用 查看
#40楼[
楼主] 2008-05-12 09:03
@电机拖动
--引用--------------------------------------------------
所以，我觉得虽然索引可以ASC或DESC，对长期运行的系统来说，实际上是没有什么影响的。
--------------------------------------------------------
如果事先可以确定查询是倒叙还是顺序，指定索引的顺序还是有意义的；比如说bbs系统，帖子总是按照最后回复时间倒叙排序的，这时候索引按照倒叙建就是有意义的。
回复 引用 查看
#41楼[
楼主]
2008-05-12 09:05
@PerfectDesign
--引用--------------------------------------------------
你没有看到Purchasing.PurchaseOrderDetail 表是用的联合主键啊。
而且建的聚集索引也是两个字段，而在你的示例代码里面却是只有一个ＩＤ做聚集索引，完全不能做参考的。
--------------------------------------------------------
多谢老兄提示，这个我还是看到了；我之所以在这篇文章中举一个这样的例子是因为主键sql server总是自动建了聚集索引；这有时候会对性能有影响。
回复 引用 查看