随笔 - 99  文章 - 2 评论 - 901 trackbacks - 17
<2008年7月>
日一二三四五六
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789


转载请保留链接。
我的最新闪存
国家体育场（鸟巢）是建筑史上的令人惊叹之作，杂乱的基础结构组成了一个和谐的整体，正是中国社会的成功写照。 7-18 17:44

与我联系

常用链接

留言簿(11)

我管理的小组

我参加的小组

我参与的团队

我的标签

随笔分类

随笔档案

文章分类

相册

最新随笔

搜索

  •  

积分与排名

  • 积分 - 140978
  • 排名 - 242

最新评论

阅读排行榜

评论排行榜

60天内阅读排行

建索引（尤其是主键）时请注意索引列的顺序
在我们创建主键或者其他索引时，sql server总是自动将字段的顺序设置为升序排列；升序是默认设置，是为了保持与 SQL Server 早期版本的兼容性。建索引时索引列的顺序应该按照常用查询中的排序方式排序。

我们做个试验创建一个表，其中主键上的聚集索引按照id倒叙排列，然后分别倒叙顺序select数据，比较select的时间：
测试代码
以下是查询时间结果
查询时间(毫秒)
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
Tag标签: sql,performance,index
posted on 2008-05-09 13:15 玉开 阅读(1310) 评论(41)  编辑 收藏 所属分类: 数据库 、sql server

FeedBack:
#1楼  2008-05-09 13:20 李战      
路过，学习！
  回复  引用  查看    
#2楼 [楼主] 2008-05-09 13:24 玉开      
@李战
真的有抢沙发的软件？拿出来给大家分享一下。刺激一下大家看谁的更能强。
  回复  引用  查看    
#3楼  2008-05-09 13:39 小寒      
要仔细阅读别人的文章，才对得起作者的劳动
抢沙发有什么意思
  回复  引用  查看    
#4楼  2008-05-09 13:40 李战      
@玉开
俺真的没有什么沙发软件，只是碰巧被您的文章吸引了。

俺是个俗人，午休正在做梦娶媳妇呢，楼主这句“建索引时索引列的顺序应该按照常用查询中的排序方式排序”，一语点醒梦中的俺啊！




  回复  引用  查看    
#5楼  2008-05-09 13:45 Cheney Shue      
"在建主键时要特别注意，因为sql server会自动按照升序来建"

这句话有点语病
  回复  引用  查看    
#6楼 [楼主] 2008-05-09 14:08 玉开      
@李战
sorry,看来我误解你了。 ^_^
  回复  引用  查看    
#7楼 [楼主] 2008-05-09 14:10 玉开      
@Cheney Shue
语病在哪儿？

我这句话的意思是
因为主键sql server会自动建成按照升序排列的聚集索引，如果查询时经常按照主键倒叙排的话，就要自己注意修改默认的的设置了。
  回复  引用  查看    
#8楼  2008-05-09 14:17 kuber      
挺有价值的. 谢谢了
  回复  引用  查看    
#9楼  2008-05-09 14:26 Cheney Shue      
--引用--------------------------------------------------
玉开: @Cheney Shue
语病在哪儿？

我这句话的意思是
因为主键sql server会自动建成按照升序排列的聚集索引，如果查询时经常按照主键倒叙排的话，就要自己注意修改默认的的设置了。
--------------------------------------------------------


我觉得SQL Server不会这么愚蠢，应该是那个该死的企业管理器产生的DDL自动建立升序的聚类索引。
  回复  引用  查看    
#10楼 [楼主] 2008-05-09 14:27 玉开      
@kuber
呵呵，要感谢博客园我们提供的环境。

  回复  引用  查看    
#11楼 [楼主] 2008-05-09 14:29 玉开      
@Cheney Shue
你说的没错，确实是DDL做的，
在下面这篇文章中说是为了向前兼容。
http://technet.microsoft.com/zh-cn/library/ms181154.aspx
  回复  引用  查看    
#12楼  2008-05-09 15:50 jowo      
学习
  回复  引用  查看    
#13楼  2008-05-09 16:59 金色海洋（jyk）      
@玉开
我证明，李战没有抢沙发的软件，不信的话看我的帖子，:)

我感觉不会有这样的想象，我一会测试一下。
  回复  引用  查看    
#14楼 [楼主] 2008-05-09 17:33 玉开      
@金色海洋（jyk）
好的，文中有我的测试脚本，期待你的测试结果。

  回复  引用  查看    
#15楼  2008-05-09 17:52 金色海洋（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多毫秒。

不知道是不是我的测试方法不对。




  回复  引用  查看    
#16楼  2008-05-09 17:53 金色海洋（jyk）      
我在用 sql server 2005 测一下。
sql server 2005 还不熟呢，好像是第三次用，正在研究中。
  回复  引用  查看    
#17楼  2008-05-09 18:26 金色海洋（jyk）      
被害了，添加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 已经改进了一点，先执行哪个，哪个就能快一点，后执行的就会慢一点。

呵呵。

再试一试我的分页算法。


  回复  引用  查看    
#18楼  2008-05-09 18:41 金色海洋（jyk）      
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






  回复  引用  查看    
#19楼  2008-05-09 18:45 金色海洋（jyk）      
看来，当数据库从2000升级到2005之后，我的分页控件内部使用的分页算法，就可以改成使用 ROW_NUMBER 的方法了，

升级的时候，不需要修改项目里的任何的代码，只升级分页控件就可以了。

数据库也不用做修改（在分页方面），因为没有使用存储过程来分页。


  回复  引用  查看    
#20楼  2008-05-09 19:13 金色海洋（jyk）      
两个排序字段的测试



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条记录。:)



  回复  引用  查看    
#21楼  2008-05-09 19:22 alonesword [未注册用户]
留意一下，对于这样的细节问题的确怎么注意过。
学习，mark
  回复  引用    
#22楼  2008-05-09 19:58 金色海洋（jyk）      
再看一下表变量 + 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给 表变量 加了一个聚集索引，所以再次查询的时候才会比较快。

比较晕
  回复  引用  查看    
#23楼  2008-05-09 21:22 疯狂的程序员 [未注册用户]
请沙发就RSS订阅 -_-
  回复  引用    
#24楼  2008-05-09 21:36 PerfectDesign      
@金色海洋（jyk）
数据量上万的都要使用临时表，并且在生产服务器上将临时库指定到单独的磁盘上去，并将临时库的指定大小设成比较大，具体看自己环境的实际应用。
  回复  引用  查看    
#25楼  2008-05-09 21:39 PerfectDesign      
@金色海洋（jyk）
没有必要使用
print datediff(millisecond,@t,getdate());
使用ＭＳ提供的统计信息就可以了。
set statistics io on
set statistics time on
  回复  引用  查看    
#26楼  2008-05-09 21:41 PerfectDesign      
在我的机器上使用８万条记录，正序倒序都是一样的。
而且我目前也没有看到有文档说索引的正序和倒序会有性能影响，倒是复合列的头列是哪个有影响。
ｐｓ，开始误会你的意思以为说的复合列列顺序问题
  回复  引用  查看    
#27楼  2008-05-09 21:59 PerfectDesign      
晕啊，玉开兄
你看看你提供的那个链接，
http://technet.microsoft.com/zh-cn/library/ms181154.aspx
说的是一个示例，关于索引排序顺序的不一致可能导致性能问题，但是那个查询是
USE AdventureWorks;
GO
SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,
ProductID, DueDate
FROM Purchasing.PurchaseOrderDetail
ORDER BY RejectedQty DESC, ProductID ASC;

你没有看到Purchasing.PurchaseOrderDetail　表是用的联合主键啊。
而且建的聚集索引也是两个字段，而在你的示例代码里面却是只有一个ＩＤ做聚集索引，完全不能做参考的。
  回复  引用  查看    
#28楼  2008-05-09 22:20 PerfectDesign      
索引的本质是树，至于树的排序，如果熟悉2叉树算法的都知道正序或者倒序都是一棵树，只是在扫描整棵树的时候是从左边开始扫描还是从右边开始扫描

  回复  引用  查看    
#29楼  2008-05-09 22:21 PerfectDesign      
而如果表存在聚集索引，那么页面与页面之间就是双向链表关系，这种关系具体反映在索引的顺序上是无所谓的
  回复  引用  查看    
#30楼  2008-05-10 11:18 SZW      
还真是个以前没有注意到的地方，感谢博主:)
@金色海洋（jyk）
真是不辞辛劳啊^_^
  回复  引用  查看    
#31楼  2008-05-10 12:28 金色海洋（jyk）      
我主要是学习一下 row_Number的分页算法，然后又顺便和自己的分页算法做了一下对比。


  回复  引用  查看    
#32楼  2008-05-10 17:24 电机拖动      
我觉得这种思考方式很不错，但是结论不要这样推出，因为这是有问题的

我们应该从SQLSERVER的索引结构和数据存储结构，还有查找算法来得出结论，而不是一两个测试，除非你的测试很多很多……我简单说说我的看法：

聚集索引的确是按顺序排列的（不过绝对不是物理顺序排列，至少不完全是，最多是部分物理顺序排列），那么，在已经排序的序列中进行查找（BineraySearch大家都是知道的，当然，这只对内存数据有效，“页面”也就是用来干这个的），正序和反序（或者说头、尾）的读取平均概率应该是差不多的。再者，SQLSERVER的索引就B树这一种，那么既然有了B树，就说明不管正序还是反序，要找到一条或一群记录所需要读取的页面不会特别多，如果再考虑上SQLSERVER2005那改进过的缓存机制的话，主要的IO基本就集中在叶子级上了，这样的话，正序和反序基本上就没有区别了。

所以，我觉得虽然索引可以ASC或DESC，对长期运行的系统来说，实际上是没有什么影响的。


  回复  引用  查看    
#33楼  2008-05-10 17:39 电机拖动      
不好意思，刚才没有完全看完，lz说的是排序的问题

这样的话，结论就没有什么大问题了。

应该考虑一下sqlserver到底是如何排序的以及到底是在哪排序的（仍然需要理解数据存储形式以及索引存储形式），这些信息将严重影响数据库的性能，绝对不会仅仅文中的测试结果那么几百毫秒，不考虑这部分内容的话，性能差距可能会上升为秒级或十秒级的……

总结一下，如果只是查找的话，索引顺序几乎不会有任何影响。如果有排序动作的话，就有影响了，不过还是要考虑量的问题。

最后，我觉得，考虑排序的工作量不小的，如果没有特别需要的话，数据库设计的时候可以不用理会的，相信不会有人经常一次取出几十万条数据来进行排序处理吧？
  回复  引用  查看    
#34楼  2008-05-10 21:05 PerfectDesign      
--引用--------------------------------------------------
电机拖动: 不好意思，刚才没有完全看完，lz说的是排序的问题 <br />
<br />
这样的话，结论就没有什么大问题了。<br />
<br />
应该考虑一下sqlserver到底是如何排序的以及到底是在哪排序的（仍然需要理解数据存储形式以及索引存储形式），这些信息将严重影响数据库的性能，绝对不会仅仅文中的测试结果那么几百毫秒，不考虑这部分内容的话，性能差距可能会上升为秒级或十秒级的&#8230;&#8230; <br />
<br />
<span style="color: red">总结一下，如果只是查找的话，索引顺序几乎不会有任何影响。如果有排序动作的话，就有影响了，不过还是要考虑量的问题。</span><br />
<br />
最后，我觉得，考虑排序的工作量不小的，如果没有特别需要的话，数据库设计的时候可以不用理会的，相信不会有人经常一次取出几十万条数据来进行排序处理吧？
--------------------------------------------------------
翻页就会这样，几十万记录来回排序
  回复  引用  查看    
#35楼  2008-05-11 13:35 电机拖动      
@PerfectDesign
--引用--------------------------------------------------
翻页就会这样，几十万记录来回排序
--------------------------------------------------------


如果每页显示20条，共20W条数据，也就是说有1W页
请问是否有人真的会看完这1W页？如果你说有SORT这样的操作，那么正反序又成一样的了。

不知道是英才还是智联，查询后只显示前2000条数据，这是为什么？不是说什么数据都要拿出来显示的

此外，索引的排序就算能够增加查询（针对特定查询，很多时候说不定会降低查询效率，那要看你是不是能准确分析数据库的用途了）的效率，那么修改（C/U/D）的时候呢？
  回复  引用  查看    
#36楼  2008-05-11 16:29 PerfectDesign      
@电机拖动
请问是否有人真的会看完这1W页？
这个我只是说一旦有翻页，不管是否看完所有的1w页，都会有排序发生的。

那么修改（C/U/D）的时候呢？
你说的索引值不值得的问题那就是个大话题了，我只是说如果想得到最快速度，并没说索引带来的其他弊端了


  回复  引用  查看    
#37楼  2008-05-12 01:11 镜涛      
@小寒
坚决同意！
  回复  引用  查看    
#38楼 [楼主] 2008-05-12 08:58 玉开      
@金色海洋（jyk）
多谢了，麻烦你测试了这么多次

可以使用如下语句清空缓存。
GO
checkpoint
dbcc freeproccache
dbcc dropcleanbuffers
GO
另外，理论上来讲，清空缓存之后第一次查需要的时间应该长一些，因为需要将索引载入到内存中，第二次查时索引已经在内存中了，少了载入时间。
  回复  引用  查看