SQL Server 中几种分页方法的再测试

SQL Server 中几种分页方法的再测试

 刚搬家到优快云，决定贡献第一篇文章。：）
 
最近做一个新项目，想使用一下SQL Server 2005中的 ROW_NUMBER的分页方式，因为这个新的特性可以使得分页代码的编写变得轻松。唯一担心的是性能问题，于是我上网搜索了一下关于 ROW_NUMBER的性能的讨论，可是发现有些人的测试表明，该特性形同鸡肋，并不能带来性能的显著提高。对此我颇为怀疑，为了求证这种分页方法的的性能到底如何。只好亲自操刀，对于现在流行的3种分页方法做一个对比测试。
 
对比测试的方法如下：

•  利用TOP N 分页
•  利用临时表分页
•  ROW_NUMBER 分页

测试环境：

• Windows Server 2003
• Intel Core2 6300/4G 内存
• Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)   Mar 23 2007 16:28:52   Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

测试说明：

• 分别用三种方法建立Stored procedures参数统一如下:
  @pageindex int=1,
  @pagesize int=10,
  @ordercolumn nvarchar(20)='',
  @order bit =0,-- 0 stands for 'ASC' 1 stands for 'DESC'
  @totalcount int output
  
• 测试表插入300万条记录，按照如下分页规则测试：
   1) 每页显示30条记录，返回第10页
   2) 每页显示30条记录，返回第1000页
   3) 每页显示30条记录，返回第10000页
   4) 每页显示20条记录，返回第100000页

测试脚本如下：

--分页性能测试
 
 --1 建立测试表
 CREATE TABLE [dbo].[tb_group](
     [ID] [int] IDENTITY(1,1) NOT NULL,
     [Name] [nvarchar](50) NULL,
     [city] [nvarchar](50) NULL,
     [province] [nvarchar](50) NULL,
     [country] [nvarchar](50) NULL,
  CONSTRAINT [PK_tb_group] PRIMARY KEY CLUSTERED 
 (
     [ID] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 
 --2 插入300万条记录
 select @@version
 declare @count int
 declare @ch char(4)
 declare @c nvarchar(2)
 declare @i int
 
 set @count=3000000
 while @count>0
 begin
 set @i=65+floor(rand()*26)
 set @c=convert(nvarchar(2),@i)
 set @ch=replicate(char(@i),4)
 
 insert tb_group ([name],[city],[province],[country]) values 
 (@ch,'C'+@c,'P'+@c,@ch)
 
 set @count=@count-1
 end
 
 --select count(*) from tb_group
 
 --方法一
 -- TOP N 分页
 create proc sp_pagination1
 @pageindex int=1,
 @pagesize int=10,
 @ordercolumn nvarchar(20)='',
 @order bit =0,-- 0 is'ASC' 1 is 'DESC'
 @totalcount int output
 as
 declare @sql nvarchar(4000)
 declare @strOrder1 nvarchar (4)
 declare @strOrder2 nvarchar (4)
 declare @strOrderColumn nvarchar(60)
 
 select @totalcount=count(id) from tb_group
 
 if (@order=0)
 begin
     set @strOrder1 ='asc'
     set @strOrder2 ='desc'
 end else begin
     set @strOrder1 ='desc'
     set @strOrder2 ='asc'
 end
 
 if (@ordercolumn<>'')
 begin
     set @strOrderColumn='order by ' + @ordercolumn + ' '
 end else begin
     set @strOrderColumn=''
     set @strOrder1 =''
     set @strOrder2 =''
 end
 if (@pageindex<=0 or @pagesize<=0 or (@pagesize*(@pageindex-1)>@totalcount)) begin
     set @sql='select * from tb_group where 1<>1'
 end else begin
     if (@pagesize*@pageindex>@totalcount)
     begin
         set @sql='select * from '+
             '(select top ' + convert(nvarchar(10),@totalcount-(@pagesize*(@pageindex-1))) +
             ' * from tb_group ' + @strOrderColumn + @strOrder2 +',id desc) t1 ' +
             @strOrderColumn + @strOrder1 +',id'
     end else begin
         set @sql='select * from '+
             '(select top ' +convert(nvarchar(10),@pagesize)+' * from ' +
             '(select top ' + convert(nvarchar(10),@pagesize+@pagesize*(@pageindex-1)) +
             ' * from tb_group ' + @strOrderColumn + @strOrder1+ ',id) t1 ' + @strOrderColumn + @strOrder2 +
             ',id desc) t2 ' + @strOrderColumn + @strOrder1 +',id'
     end
 end
 
 exec (@sql)
 
 --方法二
 -- 临时表分页
 create proc sp_pagination2
 @pageindex int=1,
 @pagesize int=10,
 @ordercolumn nvarchar(20)='',
 @order bit =0,-- 0 is'ASC' 1 is 'DESC'
 @totalcount int output
 as
 
 declare @sql nvarchar(4000)
 declare @strOrder nvarchar (4)
 declare @strOrderColumn nvarchar(60)
 
 if (@order=0)
 begin
     set @strOrder ='asc'
 end else begin
     set @strOrder ='desc'
 end
 
 if (@ordercolumn<>'')
 begin
     set @strOrderColumn='order by ' + @ordercolumn + ' '
 end else begin
     set @strOrderColumn=''
     set @strOrder =''
 end
 
 select @totalcount=count(id) from tb_group
 
 create table #pager (id int, pagerid int identity(1, 1) not null)
 
 set @sql= 'insert into #pager (id) ' +
     'select top ' +convert(varchar(10),@pageindex*@pagesize) + ' id from tb_group ' + @strOrderColumn + @strOrder + ',id'
 exec (@sql)
 
 set @sql='select * from tb_group as A inner join #pager as B on A.id=B.id' +
     ' where b.pagerid > '+ convert(varchar(10),@pagesize * (@pageindex-1))+' and b.pagerid < ' +
     convert(varchar(10),@pagesize * (@pageindex) +1)
 exec (@sql)
 
 --方法三
 -- ROW_Number分页
 
 --在开始测试这个方法之前我发现一个问题,下面两个语句照道理应该一模一样，可是上面用小写英文的却无法执行，查询KB也没有发现相关问题。莫非我发现了一个BUG？(重新连接DB后此问题消失)
 
 select * from (select *,row_number() over (order by country) as rowno from tb_group) as t1
 where rowno > 0 and rowno < 61
 
 SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY country) AS RowNo FROM tb_group) AS T1
 WHERE RowNo > 30 and RowNo < 61
 
 --下面是测试代码
 
 create proc sp_pagination3
 @pageindex int=1,
 @pagesize int=10,
 @ordercolumn nvarchar(20)='',
 @order bit =0,-- 0 is'ASC' 1 is 'DESC'
 @totalcount int output
 as
 
 declare @sql nvarchar(4000)
 declare @strOrder nvarchar (4)
 declare @strOrderColumn nvarchar(60)
 
 if (@order=0)
 begin
     set @strOrder ='asc'
 end else begin
     set @strOrder ='desc'
 end
 
 if (@ordercolumn<>'')
 begin
     set @strOrderColumn='order by ' + @ordercolumn + ' '
 end else begin
     set @strOrderColumn=''
     set @strOrder =''
 end
 
 select @totalcount=count(id) from tb_group
 
 
 set @sql= 'SELECT * FROM (SELECT *,ROW_NUMBER() OVER (' + @strOrderColumn + @strOrder +') AS RowNo FROM tb_group) AS T1' +
     ' WHERE RowNo > '+ convert(varchar(10),@pagesize * (@pageindex-1)) +
     ' and RowNo < ' + convert(varchar(10),@pagesize * (@pageindex) +1)
 exec (@sql)

测试用例代码：

 

--测试在排序 “无索引列” 时的分页性能
 
 DBCC FREEPROCCACHE
 GO
 select * from tb_group order by country asc
 --DBCC FREESESSIONCACHE
 declare @starttime datetime
 declare @endtime datetime
 declare @time datetime
 declare @total int
 set @starttime=getdate()
 exec sp_pagination3 1,10,'country',0,@total output
 set @endtime=getdate()
 set @time=@endtime-@starttime
 select @total,@time
 
 
 
 --测试在排序 “有索引列” 时的分页性能
 
 DBCC FREEPROCCACHE
 GO
 --DBCC FREESESSIONCACHE
 select * from tb_group
 declare @starttime datetime
 declare @endtime datetime
 declare @time datetime
 declare @total int
 set @starttime=getdate()
 exec sp_pagination3 3,5,'id',0,@total output
 set @endtime=getdate()
 set @time=@endtime-@starttime
 select @total,@time

 

测试结果：

利用TOP N 分页

测试项目	测试用时
	无索引列排序	有索引列排序
每页显示30条记录，返回第10页 :	1:05分	220毫秒
每页显示30条记录，返回第1000页 :	1:07分	280毫秒
每页显示30条记录，返回第10000页 :	1:07分	640毫秒
每页显示20条记录，返回第100000页 :	1:15分	2秒640毫秒

 

 

 

 

 

临时表分页

测试项目	测试用时
	无索引列排序	有索引列排序
每页显示30条记录，返回第10页 :	27秒407毫秒	2秒627毫秒
每页显示30条记录，返回第1000页 :	28秒170毫秒	2秒873毫秒
每页显示30条记录，返回第10000页 :	31秒500毫秒	5秒783毫秒
每页显示20条记录，返回第100000页 :	45秒937毫秒	15秒750毫秒

 

 

 

 

 

ROW_NUMBER 分页

测试项目	测试用时
	无索引列排序	有索引列排序
每页显示30条记录，返回第10页 :	9秒107毫秒	250毫秒
每页显示30条记录，返回第1000页 :	9秒873毫秒	263毫秒
每页显示30条记录，返回第10000页 :	12秒230毫秒	517毫秒
每页显示20条记录，返回第100000页 :	19秒640毫秒	1秒983毫秒

 

 

 

 

 

结果分析

• 利用TOP N 分页
  
  缺点: 如果排序列不是索引列，性能低下。最后一页的显示会错误。除非添加代码对最后一页进行处理。另外如果排序列有重复值，排序发生错误，解决方案是进行二次排序。 （e.g.: order by column1, PKcolum）。需要编写代码判断pageindex的有效性。如果用时间列作为排序列似乎需要进一步更改代码，笔者未作进一步尝试。
  
• 利用临时表分页
  
  缺点: 消耗服务器IO，如果数据量大，可能因磁盘性能导致查询速度下降。 如果插入临时表时不使用top 性能下降，如果使用top，对有重复值的列排序会不正确。解决方案是进行二次排序.
  
• ROW_NUMBER 分页
  
  缺点: 结果较为满意，缺点是只能用于SQL2005

从上面的分析可以看出，在有条件的情况下（有SQL2005）应该优先考虑使用ROW_NUMBER 排序。此外，如果仅对于索引列排序，可以使用TOP N排序获得较好的性能。但是如果需要使用无索引列排序，可以考虑使用临时表。或者修改表结构建立索引。

 

作者： Eric Z.