本文介绍了一种颠倒分页的优化方法，该方法通过区分前后半部分数据的不同处理方式来提高分页查询效率，尤其是在数据量较大时，能够有效减少中间页的查询时间。
颠倒分页目前主要流行两种方法：
方法A：
select * from Products where productid in ( select top 10 productid from ( select top 100 productid from Products order by productid ) as t order by t.productid desc )order by productid
优点：很好的利用了主键，是IT界公认的最佳分页方式之一。
缺点：分页的数据数据表必须具备主键。这也使得这种思路在通用性上稍差了一点。
方法B：
select * from ( select top 10 * from ( select top 100 * from Products order by productid ) as t order by t.productid desc) as t order by productid
优点：通用性比较好，几乎可以对任何数据进行分页。
缺点：如果表中记录条数在1W以上，且表字段比较多。读取后面几页数据时，几乎要把表中的数据读取出来完。这样的性能和速度是可想而知的。
有时候在做程序开发时，我们都想保持高性能又不失通用性。这里提供一种中间办法，可能代大家参考。
如果某一类信息有1000页以上，在大多用户有这样一个浏览习惯，前10页和最后10页是占总浏览量的95%以上（如果在翻页条上，没有直达中间页码的快捷功能，会更高）。也就是说，随着某一类信息的页码不断增多，浏览的页就主要集中在了前10页和最后10页。
假设某一类信息共分1000页，每页10条数据，共1W条数据。根据上述特点，我们做一个前500页和后500页的二计算。
前500页：仍然按照方法B计算。
最后500页：把数据从后往前读取。
例如：
读取第1000页的数据，只我们只读取最后10条数据，再做一次颠倒排序便可。
select * from ( select top 10 * from Products order by productid desc) as t order by t.productid asc
读取第999页的数据，只我们只读取最后20条数据，再做一次颠倒排序，读取前10条数据便可。
select top 10 * from ( select top 20 * from Products order by productid desc) as t order by t.productid asc
以次类推.......
颠倒这样改进后，翻第499页和第500页成了速度最慢的页面了，然而这些比较慢中间页码又是少有人到达的。
为了更方便使用改进后颠倒分页思路，我已经把它写成了存储过程如下:
ALTER Procedure p_Pagination
(
@TableName nvarchar(500), --
@ReturnFieldLists nvarchar(1800), --
@SortFieldLists nvarchar(500), -- (,)
@PageSize int = 10, --
@PageIndex int = 1, --
@WhereClause nvarchar(1000) = '' -- ( Where )
)
AS
Begin
--全局变量
Declare @strSQL nvarchar(4000), --
@strSQLcount nvarchar(2000),
@InnerOrder nvarchar(500),
@TotalPage int,
@TotalRecord int,
@YuShu int,
@i int
Set @WhereClause = rtrim(ltrim(@WhereClause));
Set @WhereClause = IsNull(@WhereClause,'');
Set @InnerOrder = @SortFieldLists
--颠倒排序字段计算变量
Declare @Index int,
@DotIndex int,
@SortFieldTemp nvarchar(500),
@strTemp nvarchar(500),
@strOrder1 nvarchar(500),
@strOrder2 nvarchar(500)
------------------------------------------------------------------------------------
--计算页码和总记录条数开始
Begin
IF @WhereClause<>''
Set @strSQLcount = 'Select @c=COUNT(1) From ' + @TableName + ' Where ' + @WhereClause + ';';
ELSE
Set @strSQLcount = 'Select @c=COUNT(1) From ' + @TableName + ';';
EXECUTE sp_ExecuteSQL @stmt=@strSQLcount,@params = N'@c as int OUTPUT',@c=@TotalRecord OUTPUT;
set @YuShu=@TotalRecord % @PageSize;
set @TotalPage=(CASE WHEN @YuShu>0 then @TotalRecord/@PageSize+1 else @TotalRecord/@PageSize end)
End
select @TotalPage TotalPage,@TotalRecord TotalRecord
--计算页码和总记录条数结束
--------------------------------------------------------------------------------------
--读取本页数据开始
Begin
if (@PageIndex>@TotalPage) or (@TotalRecord=0) --超出最大页码或总记录条数为0
begin
--------------------------------------------------------------------------------------
Set @strSQL = 'Select TOP 1 '+ @ReturnFieldLists +' From ' + @TableName + ' where 1<>1'
--------------------------------------------------------------------------------------
end
else IF (@PageIndex = 1)--第1页
Begin
--------------------------------------------------------------------------------------
IF @WhereClause<>''
Set @strSQL = 'Select TOP ' + Str(@PageSize) +
' '+ @ReturnFieldLists +
' From ' + @TableName +
' Where ' + @WhereClause +
' Order By ' + @InnerOrder;
ELSE
Set @strSQL = 'Select TOP ' + Str(@PageSize) +
' '+ @ReturnFieldLists +
' From ' + @TableName +
' Order By '+ @InnerOrder;
--------------------------------------------------------------------------------------
End
else IF (@PageIndex = @TotalPage)--最后一页
Begin
--------------------------------------------------------------------------------------
--计算颠倒排序字段
Set @strOrder1 = ''
Set @strOrder2 = ''
set @i=0
WHILE(1 = 1)
Begin
--防止死循环
set @i= @i+1;
if(@i>50)
BREAK;
Select @Index = CHARINDEX( ',', @SortFieldLists )
IF @Index = 0
Begin
Set @SortFieldTemp = @SortFieldLists;
Select @DotIndex = CHARINDEX( '.', @SortFieldTemp )
IF @DotIndex > 0
Set @strTemp = SubString(@SortFieldTemp, @DotIndex + 1, Len(@SortFieldTemp) - @DotIndex + 1)
ELSE
Set @strTemp = @SortFieldTemp
End
ELSE
Begin
Set @SortFieldTemp = SubString(@SortFieldLists, 1, @Index - 1)
Select @DotIndex = CHARINDEX( '.', @SortFieldTemp )
IF @DotIndex > 0
Set @strTemp = SubString(@SortFieldTemp, @DotIndex + 1, Len(@SortFieldTemp) - @DotIndex + 1)
ELSE
Set @strTemp = @SortFieldTemp
End
--组织顺排序字段
Set @strOrder1 = @strOrder1 + ',' + @strTemp
--组织反排序字段
Select @DotIndex = CHARINDEX( ' desc', @SortFieldTemp)
IF @DotIndex > 0
Set @strOrder2 = @strOrder2 + ',' + replace(@SortFieldTemp,' desc',' asc')
else
begin
Select @DotIndex = CHARINDEX( ' asc',@SortFieldTemp)
IF @DotIndex > 0
Set @strOrder2 = @strOrder2 + ',' + replace(@SortFieldTemp,' asc',' desc')
else
Set @strOrder2 = @strOrder2 + ',' + @SortFieldTemp +' desc'
end
--取得下一循环的数据
IF @Index = 0
BREAK;
ELSE
Select @SortFieldLists = SubString(@SortFieldLists, @Index + 1, Len(@SortFieldLists) - @Index + 1)
End
--
Select @strOrder1 = SubString(@strOrder1, 2, Len(@strOrder1))
Select @strOrder2 = SubString(@strOrder2, 2, Len(@strOrder2))
--------------------------------------------------------------------------------------
--第后一页的SQL
IF @WhereClause<>''
Set @strSQL ='select * from ('+
'Select TOP ' + str(case when (@YuShu>0) then @YuShu else @PageSize end) +
' '+ @ReturnFieldLists +
' From ' + @TableName +
' Where ' + @WhereClause +
' Order By ' + @strOrder2+
') t order by '+@strOrder1;
ELSE
Set @strSQL ='select * from ('+
'Select TOP ' + str(case when (@YuShu>0) then @YuShu else @PageSize end) +
' '+ @ReturnFieldLists +
' From ' + @TableName +
' Order By ' + @strOrder2+
') t order by '+@strOrder1;
--------------------------------------------------------------------------------------
End
else IF (@PageIndex > @TotalPage/2)--二分后，后一半页面
Begin
--------------------------------------------------------------------------------------
--计算颠倒排序字段
Set @strOrder1 = ''
Set @strOrder2 = ''
set @i=0
WHILE(1 = 1)
Begin
--防止死循环
set @i= @i+1;
if(@i>50)
BREAK;
Select @Index = CHARINDEX( ',', @SortFieldLists )
IF @Index = 0
Begin
Set @SortFieldTemp = @SortFieldLists;
Select @DotIndex = CHARINDEX( '.', @SortFieldTemp )
IF @DotIndex > 0
Set @strTemp = SubString(@SortFieldTemp, @DotIndex + 1, Len(@SortFieldTemp) - @DotIndex + 1)
ELSE
Set @strTemp = @SortFieldTemp
End
ELSE
Begin
Set @SortFieldTemp = SubString(@SortFieldLists, 1, @Index - 1)
Select @DotIndex = CHARINDEX( '.', @SortFieldTemp )
IF @DotIndex > 0
Set @strTemp = SubString(@SortFieldTemp, @DotIndex + 1, Len(@SortFieldTemp) - @DotIndex + 1)
ELSE
Set @strTemp = @SortFieldTemp
End
--组织顺排序字段
Set @strOrder1 = @strOrder1 + ',' + @strTemp
--组织反排序字段
Select @DotIndex = CHARINDEX( ' desc', @SortFieldTemp)
IF @DotIndex > 0
Set @strOrder2 = @strOrder2 + ',' + replace(@SortFieldTemp,' desc',' asc')
else
begin
Select @DotIndex = CHARINDEX( ' asc',@SortFieldTemp)
IF @DotIndex > 0
Set @strOrder2 = @strOrder2 + ',' + replace(@SortFieldTemp,' asc',' desc')
else
Set @strOrder2 = @strOrder2 + ',' + @SortFieldTemp +' desc'
end
--取得下一循环的数据
IF @Index = 0
BREAK;
ELSE
Select @SortFieldLists = SubString(@SortFieldLists, @Index + 1, Len(@SortFieldLists) - @Index + 1)
End
--
Select @strOrder1 = SubString(@strOrder1, 2, Len(@strOrder1))
Select @strOrder2 = SubString(@strOrder2, 2, Len(@strOrder2))
--------------------------------------------------------------------------------------
--二分后，后一半页面
IF @WhereClause<>''
Set @strSQL ='select top '+str(@PageSize)+' * from ('+
'Select TOP ' + str(case when (@YuShu>0) then ((@TotalPage-@PageIndex)*@PageSize+@YuShu) else (@TotalPage-@PageIndex+1)*@PageSize end) +
' '+ @ReturnFieldLists +
' From ' + @TableName +
' Where ' + @WhereClause +
' Order By ' + @strOrder2+
') t order by '+@strOrder1;
ELSE
Set @strSQL ='select top '+str(@PageSize)+' * from ('+
'Select TOP ' + str(case when (@YuShu>0) then ((@TotalPage-@PageIndex)*@PageSize+@YuShu) else (@TotalPage-@PageIndex+1)*@PageSize end) +
' '+ @ReturnFieldLists +
' From ' + @TableName +
' Order By ' + @strOrder2+
') t order by '+@strOrder1;
--------------------------------------------------------------------------------------
End
ELSE---二分后，前一半页面
Begin
--------------------------------------------------------------------------------------
--计算颠倒排序字段
Set @strOrder1 = ''
Set @strOrder2 = ''
set @i=0
WHILE(1 = 1)
Begin
--防止死循环
set @i= @i+1;
if(@i>50)
BREAK;
Select @Index = CHARINDEX( ',', @SortFieldLists )
IF @Index = 0
Begin
Set @SortFieldTemp = @SortFieldLists;
Select @DotIndex = CHARINDEX( '.', @SortFieldTemp )
IF @DotIndex > 0
Set @strTemp = SubString(@SortFieldTemp, @DotIndex + 1, Len(@SortFieldTemp) - @DotIndex + 1)
ELSE
Set @strTemp = @SortFieldTemp
End
ELSE
Begin
Set @SortFieldTemp = SubString(@SortFieldLists, 1, @Index - 1)
Select @DotIndex = CHARINDEX( '.', @SortFieldTemp )
IF @DotIndex > 0
Set @strTemp = SubString(@SortFieldTemp, @DotIndex + 1, Len(@SortFieldTemp) - @DotIndex + 1)
ELSE
Set @strTemp = @SortFieldTemp
End
--组织顺排序字段
Set @strOrder1 = @strOrder1 + ',' + @strTemp
--组织反排序字段
Select @DotIndex = CHARINDEX( ' desc', @strTemp)
IF @DotIndex > 0
Set @strOrder2 = @strOrder2 + ',' + replace(@strTemp,' desc',' asc')
else
begin
Select @DotIndex = CHARINDEX( ' asc', @strTemp)
IF @DotIndex > 0
Set @strOrder2 = @strOrder2 + ',' + replace(@strTemp,' asc',' desc')
else
Set @strOrder2 = @strOrder2 + ',' + @strTemp +' desc'
end
--取得下一循环的数据
IF @Index = 0
BREAK;
ELSE
Select @SortFieldLists = SubString(@SortFieldLists, @Index + 1, Len(@SortFieldLists) - @Index + 1)
End
--
Select @strOrder1 = SubString( @strOrder1, 2, Len(@strOrder1))
Select @strOrder2 = SubString( @strOrder2, 2, Len(@strOrder2))
--------------------------------------------------------------------------------------
--二分后，前一半页面
IF @WhereClause<>''
Set @strSQL ='select * from (select top '+ Str(@PageSize) + ' * from ('+
'Select TOP ' + str(@PageIndex*@PageSize) +
' '+ @ReturnFieldLists +
' From ' + @TableName +
' Where ' + @WhereClause +
' Order By ' + @InnerOrder+
') t order by '+@strOrder2+') t order by '+@strOrder1;
ELSE
Set @strSQL ='select * from (select top '+ Str(@PageSize) + ' * from ('+
'Select TOP ' + str(@PageIndex*@PageSize) +
' '+ @ReturnFieldLists +
' From ' + @TableName +
' Order By ' + @InnerOrder+
') t order by '+@strOrder2+') t order by '+@strOrder1;
--------------------------------------------------------------------------------------
End
End
--print @strSQL;
--print 'xx';
--print @strSQLcount;
--SQL
EXECUTE sp_ExecuteSQL @strSQL;
--读取本页数据结束
--------------------------------------------------------------------------------------
End
/*
调用说明
spc_Pagination
'NEWGXE_Order a, NEWGXE_Transaction b, NEWGXE_ProductTypeImage c',
'b.PaypalAccount_nvarchar,count(a.OrderID_int) as OrderCount,sum(a.USDmoney_money) as USDmoney_money',
'USDmoney_money DESC',
80,
2,
'a.OrderID_int=b.OrderID_int and a.ProductType_char=c.ProductType_char and a.Approve_char=''Yes'' and b.PaymentStatus_char=''Completed'' and a.ShipStatus_char=''Fini'' and DateDiff(d,''2007-09-01'', b.PaymentTime_nvarchar) >= 0 and DateDiff(d, b.PaymentTime_nvarchar, ''2007-10-01'') >=0 group by b.PaypalAccount_nvarchar having sum(a.USDmoney_money) > 0'
*/
扫一扫
3063
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
