Sql Server 2005 row_number()分页性能测试

核心提示：现在分页方法大多集中在select top/not in/游标/row_number，而select top分页(在这基础上还有二分法)方法似乎更受大家欢迎，Sql Server 2005 row_number()分页性能测试，这篇文章并不打算去讨论是否通用的问题，本着实用的原则，淘宝只展示前100页)，按测试的row

现在分页方法大多集中在select top/not in/游标/row_number，而select top分页(在这基础上还有二分法)方法似乎更受大家欢迎，这篇文章并不打算去讨论是否通用的问题，本着实用的原则，花了一些时间去测试 row_number()分页的性能，感觉并不像一部分人所说的那么鸡肋，由于接触软件开发才十个月，方方面面的东西都要学，经验实在有限,不足之处请原谅，测试如下：

平台与环境：

CPU:AMD 1150 2G 单核

内存：1G(系统正常启动后约占300M空间)

硬盘：SATA 160G 8M Cache

系统：windows 2003 ent+Sql Server 2005 sp2

数据：共500万条

测试数据：

create　table　test_table
(
id　　　int　identity(1,1)　primary　key　not　null,
cid　　　int　　not　null,
userName　　varchar(50)　null,
userPwd　　　varchar(50)　null,
createTime　datetime　null
)

插入记录(cid分别插入1,2,3,4,机器实在太慢,总共只插入500万条)：

declare　@count　int
set　@count=1
while　@count<=1000000
begin
insert　into　test_table(cid,userName,userPwd,createTime)　values(2,'admin','admin888',getdate())
set　@count=@count+1
end

分页测试代码：

这里采用row_number的两种分页方式：分别用top和between过滤

/*row_number()　查询方法一*/
declare　@tdiff　datetime
set　@tdiff=getdate()
select　top　20　*　from(select　row_number()　over(order　by　createtime　desc,id　asc)　as　rownumber,*　from　test_table　)　as　tb　where　rownumber>120000
select　datediff(ms,@tdiff,getdate())　as　'耗时(毫秒)'
/*row_number()　查询方法二*/
declare　@tdiff　datetime　
set　@tdiff=getdate()
select　*　from(select　row_number()　over(order　by　createtime　desc,id　asc)　as　rownumber,*　from　test_table　)　as　tb　where　rownumber　between　120000　and　120200
select　datediff(ms,@tdiff,getdate())　as　'耗时(毫秒)'

测试方法及结果(取三次平均值)：

第一次测试，每页显示20条(单位：毫秒)：

索引1(聚集) id asc

索引2(非聚集) createtime desc

页次　　　　　　　方法1　　　　　　方法2
1　　　　　　　　　　　　　　　　0　　　　　　　　　　　　　　0
10　　　　　　　　　　　　　　0　　　　　　　　　　　　　　0
100　　　　　　　　　　　　10　　　　　　　　　　　　10
1000　　　　　　　　　　65　　　　　　　　　　　　70
1W　　　　　　　　　　　　530　　　　　　　　　　　546
10W　　　　　　　4500　　　　　　　　　　　4700
20W　　　　　　　9.5秒　　　　　　　　　9.7秒

第二次测试，每页显示20条(单位：毫秒)：

索引1(聚集) id asc

索引2(非聚集) createtime desc，包含性列:cid,userName,userPwd

页次　　　　　　　方法1　　　　　　方法2
1　　　　　　　　　　　　　　　　0　　　　　　　　　　　　　　0
10　　　　　　　　　　　　　　0　　　　　　　　　　　　　　0
100　　　　　　　　　　　　0　　　　　　　　　　　　　　0
1000　　　　　　　　　　13　　　　　　　　　　　　16
1W　　　　　　　　　　　240　　　　　　　　　250
10W　　　　　　　　　2240　　　　　　　2260
20W　　　　　　　　　4436　　　　　　　4481

总结及个人观点：

由于表内记录具有一定规律性和查询的不确定性，在实际操作中，查询时间会比以上数据长，查询结果仅做参考。

1.top过滤要稍优于between过滤

2.在分页至10W即第200W第记录时，查询已经要2秒以上,个人机器原因，稍微好点的电脑查询速度可能可以提高到1秒以内。

3.分页查询的效率更重要的是取决于根据程序对数据库的优化，如索引的正确建立，分区等因素(还在学习和研究中...)

3.如果是海量级数据，其实转变一下思路也未尝不可，按用户的浏览习惯几乎不会翻到千页以后,个人感觉只要前1000页分页效率能接受就可以，测试1千页以后的效率有些多余,前台完全只需要呈现前几百页即可(如博客园只展示前200页(目前随笔数　568234)，淘宝只展示前100页)，按测试的row_number效率。完全可以胜任。