上亿数据量分页处理，高手请指教！

fanyaqin 2013-08-05 08:10:46

因为我们的订单表数据非常大，至少几千万的，客户端采用ext.net展示数据，要根据订单表里面的大部份列进行排序，比如，订单号，商品名，订单创建时间等等，使用这些非主键字段查询起来非常非常慢，而且有很多字段不填写，默认是NULL，导致分页根据某非主键排序非常慢，现在我想把数据结构优化下，让用户能正确查询信息；
我使用的存储过程也是网上找的晒下代码：

ALTER proc [dbo].[pagination] 

( 

@tblName   nvarchar(200),    ----要显示的表或多个表的连接 

@fldName   nvarchar(500)='*',   ----要显示的字段列表 

@pageSize   int = 10,     ----每页显示的记录个数 

@page    int = 1,     ----要显示那一页的记录 

@fldSort   nvarchar(200)=null,   ----排序字段列表或条件 

/**排序方法，0为升序，1为降序

   *(如果是多字段排列Sort指代最后一个排序字段的排列顺序

   *(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ') 

**/

@Sort    bit = 0,    

@strCondition nvarchar(1000) = null, ----查询条件,不需where 

@ID     nvarchar(150),    ----主表的主键 

@Dist           bit = 0,     ----是否添加查询字段的DISTINCT

@pageCount   int=1 output,           ----查询结果分页后的总页数 

@Counts    int=1 output    ----查询到的记录数 

) 

as 

set nocount on 

declare @sqlTmp nvarchar(1000)        ----存放动态生成的SQL语句 

declare @strTmp nvarchar(1000)        ----存放取得查询结果总数的查询语句 

declare @strID     nvarchar(1000)     ----存放取得查询开头或结尾ID的查询语句 



declare @strSortType nvarchar(10)   ----数据排序规则A 

declare @strFSortType nvarchar(10)   ----数据排序规则B



declare @SqlSelect nvarchar(50)         ----对含有DISTINCT的查询进行SQL构造 

declare @SqlCounts nvarchar(50)         ----对含有DISTINCT的总数查询进行SQL构造



declare @timediff datetime --耗时测试时间差 

select @timediff=getdate()



--set @tblName='(select * from ('+@tblName+')) as _temp'

if @Dist = 0 

begin 

set @SqlSelect = 'select ' 

set @SqlCounts = 'Count(*)' 

end 

else 

begin 

set @SqlSelect = 'select distinct ' 

set @SqlCounts = 'Count(DISTINCT '+@ID+')' 

end





if @Sort=0 

begin 

set @strFSortType=' ASC ' 

set @strSortType=' DESC ' 

end 

else 

begin 

set @strFSortType=' DESC ' 

set @strSortType=' ASC ' 

end



--------生成查询语句-------- 

--此处@strTmp为取得查询结果数量的语句 

if @strCondition is null or @strCondition=''     --没有设置显示条件 

begin 

set @sqlTmp = @fldName + ' From ' + @tblName 

set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName 

set @strID = ' From ' + @tblName 

end 

else 

begin 

set @sqlTmp=+@fldName+'From '+@tblName+' where (1>0) '+@strCondition 

set @strTmp=@SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName

     +' where (1>0) '+@strCondition 

set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition 

end

--print @strTmp

----取得查询结果总数量----- 

exec sp_executesql @strTmp,N'@Counts int out ',@Counts out 

declare @tmpCounts int 

if @Counts = 0 

set @tmpCounts = 1 

else 

set @tmpCounts = @Counts



--取得分页总数 

set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize



/**//**//**//**当前页大于总页数 取最后一页**/ 

if @page>@pageCount 

set @page=@pageCount



/*-----数据分页2分处理-------*/ 

declare @pageIndex int --总数/页大小 

declare @lastcount int --总数%页大小



set @pageIndex = @tmpCounts/@pageSize 

set @lastcount = @tmpCounts%@pageSize 

if @lastcount > 0 

set @pageIndex = @pageIndex + 1 

else 

set @lastcount = @pagesize



--//***显示分页 

if @strCondition is null or @strCondition=''     --没有设置显示条件 

begin 

if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理 

begin 

   if @page=1 

    set @strTmp=@SqlSelect+' top '+ STR(@pageSize) +' '

       + @fldName+' from '+@tblName+' order by '

       + @fldSort +' '+ @strFSortType 

   else 

   begin 

    if @Sort=1 

    begin                     

     set @strTmp=@SqlSelect+' top '+ STR(@pageSize )+' '

        + @fldName+' from '+@tblName +' where '+@ID

        +' <(select min('+ @ID +') from ('+ @SqlSelect+' top '

        + STR(@pageSize*(@page-1)) +' '+ @ID 

        +' from '+@tblName +' order by '+ @fldSort +' '

        + @strFSortType+') AS TBMinID)' +' order by '

        + @fldSort +' '+ @strFSortType 

    end 

    else 

    begin 

     set @strTmp=@SqlSelect+' top '+ STR(@pageSize)+' '

        + @fldName+' from '+@tblName +' where '+@ID

        +' >(select max('+ @ID +') from ('+ @SqlSelect+' top '

        + STR(@pageSize*(@page-1)) +' '+ @ID 

        +' from '+@tblName+' order by '+ @fldSort +' '

        + @strFSortType+') AS TBMinID)' +' order by '+ @fldSort 

        +' '+ @strFSortType 

    end 

   end     

end 

else 

begin 

   set @page = @pageIndex-@page+1 --后半部分数据处理 

   if @page <= 1 --最后一页数据显示                 

    set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '

       + STR(@lastcount)+' '+ @fldName+' from '

      +@tblName +' order by '+ @fldSort +' '+ @strSortType

       +') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

   else 

    if @Sort=1 

    begin 

     set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '

        + STR(@pageSize)+' '+ @fldName+' from '

       +@tblName +' where '+@ID+' >(select max('+ @ID +') from('

        + @SqlSelect+' top '+ STR(@pageSize*(@page-2)

       +@lastcount ) +' '+ @ID +' from '

       +@tblName+' order by '+ @fldSort +' '+ @strSortType

        +') AS TBMaxID)'+' order by '+ @fldSort +' '+ @strSortType

        +') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

    end 

    else 

    begin 

     set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '

        + STR(@pageSize)+' '+ @fldName+' from '

       +@tblName +' where '+@ID+' <(select min('+ @ID +') from('

        + @SqlSelect+' top '+ STR(@pageSize*(@page-2)

       +@lastcount ) +' '+ @ID +' from '

       +@tblName+' order by '+ @fldSort +' '+ @strSortType

        +') AS TBMaxID)' +' order by '+ @fldSort +' '

        + @strSortType+') AS TempTB'+' order by '+ @fldSort +' '

        + @strFSortType 

    end 

   end 

end 

else --有查询条件 

begin 

   if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理 

   begin 

    if @page=1 

     set @strTmp=@SqlSelect+' top '+ STR(@pageSize)+' '

        + @fldName+' from '+@tblName +' where 1=1 ' 

        + @strCondition + ' order by '+ @fldSort +' '

        + @strFSortType 

    else if(@Sort=1) 

    begin                     

     set @strTmp=@SqlSelect+' top '+ STR(@pageSize)+' '

        + @fldName+' from '+@tblName +' where '+@ID

        +' <(select min('+ @ID +') from ('+ @SqlSelect+' top '

        + STR(@pageSize*(@page-1)) +' '+ @ID 

        +' from '+@tblName +' where (1=1) ' + @strCondition 

        +' order by '+ @fldSort +' '+ @strFSortType

        +') AS TBMinID)' +' '+ @strCondition +' order by '

        + @fldSort +' '+ @strFSortType 

    end 

    else 

    begin 

     set @strTmp=@SqlSelect+' top '+ STR(@pageSize )+' '

        + @fldName+' from '+@tblName +' where '+@ID

        +' >(select max('+ @ID +') from ('+ @SqlSelect+' top '

        + STR(@pageSize*(@page-1)) +' '+ @ID 

        +' from '+@tblName +' where (1=1) ' + @strCondition 

        +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'        +' '+ @strCondition +' order by '+ @fldSort +' '

        + @strFSortType 

    end            

   end 

   else 

   begin 

    set @page = @pageIndex-@page+1 --后半部分数据处理 

    if @page <= 1 --最后一页数据显示 

     set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '

        + STR(@lastcount )+' '+ @fldName+' from '

       +@tblName +' where (1=1) '+ @strCondition +' order by '

        + @fldSort +' '+ @strSortType+') AS TempTB'+' order by '

        + @fldSort +' '+ @strFSortType                      

    else if(@Sort=1) 

     set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '

        + STR(@pageSize )+' '+ @fldName+' from '

       +@tblName +' where '+@ID+' >(select max('+ @ID +') from('

        + @SqlSelect+' top '+ STR(@pageSize*(@page-2)

       +@lastcount ) +' '+ @ID +' from '+@tblName 

        +' where (1=1) '+ @strCondition +' order by '+ @fldSort 

        +' '+ @strSortType+') AS TBMaxID)' +' '+ @strCondition

        +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'

        +' order by '+ @fldSort +' '+ @strFSortType     

    else 

     set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '

        + STR(@pageSize )+' '+ @fldName+' from '

       +@tblName +' where '+@ID+' <(select min('+ @ID +') from('

        + @SqlSelect+' top '+ STR(@pageSize*(@page-2)

       +@lastcount ) +' '+ @ID +' from '+@tblName 

        +' where (1=1) '+ @strCondition +' order by '+ @fldSort 

        +' '+ @strSortType+') AS TBMaxID)' +' '+ @strCondition

        +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'

        +' order by '+ @fldSort +' '+ @strFSortType             

   end     

end

exec sp_executesql @strTmp 

select datediff(ms,@timediff,getdate()) as 耗时 

print @strTmp 

set nocount off 





GO

在我本地测试环境中：
数据：104000008(1亿零4百万)
分页大小：50
主键采用:GUID
查询效率非常慢，而且分页时列值为NUll时数据会乱（会出现第1页和第2页都可能出现同一数据），查最后一页要20多分钟，所以想请大家给我帮帮忙，出出注意，最好能在1分钟内查询好；
在网上了解过，进行表分区，建立分区索引，实在不行，我们老板同意，默认查询时只让他查询200w的数据；
但是数据都放在一个表里，1亿条，在里面取200w：按50每页进行排序，但是我不知道如何过滤取200w，在进行分页；
请问大家有什么更好的方式处理么？

...全文

456 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

飞啊子 2013-08-07

打赏
举报

查询时，要求客户必选时间段查询，最多选择一个月你1个月，能有多少数据?!

Q315054403 2013-08-06

打赏
举报

既然是这个数据量，为什么还要动态地任意查询？将各种查询规范起来，再针对设计和写代码是正道这涉及的不仅仅是技术问题了

KeepSayingNo 2013-08-06

打赏
举报

你这种属于亿万级数据，建议重新组织表，按年月分表存储

haitao 2013-08-06

打赏
举报

大记录数，先考虑移走历史记录到非交易表然后才有分区查询，可以采用rownumber()（sql2005）或OFFSET-FETCH（sql2012）

fanyaqin 2013-08-06

打赏
举报

朋友，不是一次显示200w，是在1亿条记录中，只查询200w来做分页，PagaSize=50，PageCount=200W/50;没点下页取50条记录

最爱午夜 2013-08-06

打赏
举报

这个需求就很坑爹，一次显示200W行，客户端内存够不？

發糞塗牆 2013-08-06

打赏
举报

个人建议： 1、拼接sql本身效率不会非常理想，但是这只是通常情况，动态sql也可以用索引。但是要写好。 2、1亿已经达到可以做分区的程度了，建议先分区，减少每次查找或者扫描的范围。 3、根据业务，默认好一个预排序的字段，上面创建聚集索引。减少排序操作。 4、还是预处理，一般程序一次真正展示的可能也就50行左右，所以利用的好的话，从1亿里面找50行还是没问题的，这部分用于展示，另外的数据仅仅是一个“条数”及主键，翻页的时候再查询也可以。

Shawn 2013-08-05

打赏
举报

--即使200W，因为要ORDER BY，这个性能的消耗也伤不起。暂无好办法，等待高手！ --如果表中的数据，只增不删除的话，可以想办法用不精确来换取性能。建立一张分段表，大概是这个样子：段ID，段记录数量，段开始主键ID，段结束主键ID 1，50，1，50 2，50，51，100 ...... 分页查询时，先查此表，确定主键ID的开始和结束值，加到WHERE条件中，即可。

唐诗三百首 2013-08-05

打赏
举报

可以对非主键列建条件索引,即索引中加where条件..


create nonclustered index [索引名] on [表名]([字段名]) where [字段名] is not null

fanyaqin 2013-08-05

打赏
举报

嗯，这个我也想过，也是个办法，我记下了。我们这数据量，估计要按月处理，你是说历史表里加索引吧。请问，我们使用ext.net经常需要按非主键列查询，列值为Null的，数据分页查询为乱（第一，二页可能会出现重复，因为这里采用的是Max top，max<某值时，很多null列值）top的数据不正确了，请问你有什么好的方式处理么？）Row_Number效率太慢了。

唐诗三百首 2013-08-05