上亿数据量分页处理，高手请指教！
0
悬赏园豆：50
[待解决问题]
因为我们的订单表数据非常大，至少几千万的，客户端采用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
在我本地测试环境中：
数据：104000008(1亿零4百万)
分页大小：50
主键采用:GUID
查询效率非常慢，而且分页时列值为NUll时数据会乱（会出现第1页和第2页都可能出现同一数据），查最后一页要20多分钟，所以想请大家给我帮帮忙，出出注意，最好能在1分钟内查询好；
在网上了解过，进行表分区，建立分区索引，实在不行，我们老板同意，默认查询时只让他查询200w的数据；
但是数据都放在一个表里，1亿条，在里面取200w：按50每页进行排序，但是我不知道如何过滤取200w，在进行分页；
请问大家有什么更好的方式处理么？
所有回答(7)
2
这么大的数据，可以分表啊
嗯，也有想过，按时间段分表，比如月；
问，我们使用ext.net经常需要按非主键列查询，列值为Null的，数据分页查询为乱（第一，二页可能会出现重复，因为这里采用的是Max top，max<某值时，很多null列值）top的数据不正确了，请问你有什么好的方式处理么？）Row_Number效率太慢了。
0
这么大，我觉得分表是必须的。分表后，可以采用row_number分页。速度还不错~
分表，是根据按月或者按年份分表吧；
表分区呢？
@.net_樊: 分表就是按年月吧，这个看数据量。尽量把每个表数据控制在千万以下。
0
建议看一下执行计划，找出究竟慢在哪里
根据我的判断，这么慢，索引存在问题的可能性很大
我是根据主键排序的，主键有建索引的。
@.net_樊: 这个说明不了什么，要看执行计划。1分钟内查询好这个要求不高，优化一下索引就可以了
@dudu: 请教下，我是使用主键排序，用的主键索引，没有带where条件进行分页，请问如何优化索引？
如果使用条件查询，我认为是可以优化下经常查询的字段，给加上索引.
@.net_樊: 还有单单使用索引优化，不做分区，分表，能在1分钟内实现？你试过？
@.net_樊: 是的，1分钟不算要求，优化的好的话，1秒内都有可能
@dudu: 能教教我么？
我直接也经常处理大数据量，那都是使用计划任务，把很大部分过期数据，放到历史数据表里。
请问你是怎么优化的，我这就根据主键，查询，还能怎么优化，我不理解，请给出具体方案来。
@.net_樊: 建议先了解一下如何看执行计划，推荐一篇博文：SQL优化－使用执行计划
0
路过学习
0
分表是必须的,
建立自增列,也就是先把Row_Number列建好,然后再使用你的方法查询
0
我晕死你!你的数据库设计看看.
知道建聚簇索引的法则不?
知道怎么做算法简化不?
知道有些东西就要拿出来!不一定要EXT
0
从你数据库的角度去考虑，Ext这东西加载数据的时候就比较慢。
