首页 新闻 会员 周边 捐助

上亿数据量分页处理,高手请指教!

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,在进行分页;
请问大家有什么更好的方式处理么?

.net_樊的主页 .net_樊 | 初学一级 | 园豆:169
提问于:2013-08-05 20:27
< >
分享
所有回答(7)
2

这么大的数据,可以分表啊

libaoheng | 园豆:1433 (小虾三级) | 2013-08-05 20:53

嗯,也有想过,按时间段分表,比如月;

问,我们使用ext.net经常需要按非主键列查询,列值为Null的,数据分页查询为乱(第一,二页可能会出现重复,因为这里采用的是Max top,max<某值时,很多null列值)top的数据不正确了,请问你有什么好的方式处理么?)Row_Number效率太慢了。

支持(1) 反对(0) .net_樊 | 园豆:169 (初学一级) | 2013-08-05 21:01
0

这么大,我觉得分表是必须的。分表后,可以采用row_number分页。速度还不错~

幻天芒 | 园豆:37207 (高人七级) | 2013-08-05 23:39

分表,是根据按月或者按年份分表吧;

表分区呢?

支持(0) 反对(0) .net_樊 | 园豆:169 (初学一级) | 2013-08-06 10:38

@.net_樊: 分表就是按年月吧,这个看数据量。尽量把每个表数据控制在千万以下。

分区,请看看:http://www.cnblogs.com/lordyym/articles/1360729.html

支持(0) 反对(0) 幻天芒 | 园豆:37207 (高人七级) | 2013-08-06 10:53
0

建议看一下执行计划,找出究竟慢在哪里

根据我的判断,这么慢,索引存在问题的可能性很大

dudu | 园豆:30585 (高人七级) | 2013-08-06 08:09

我是根据主键排序的,主键有建索引的。

支持(0) 反对(0) .net_樊 | 园豆:169 (初学一级) | 2013-08-06 10:39

@.net_樊: 这个说明不了什么,要看执行计划。1分钟内查询好这个要求不高,优化一下索引就可以了

支持(0) 反对(0) dudu | 园豆:30585 (高人七级) | 2013-08-06 10:44

@dudu:  请教下,我是使用主键排序,用的主键索引,没有带where条件进行分页,请问如何优化索引?

如果使用条件查询,我认为是可以优化下经常查询的字段,给加上索引.

支持(0) 反对(0) .net_樊 | 园豆:169 (初学一级) | 2013-08-06 11:05

@.net_樊: 还有单单使用索引优化,不做分区,分表,能在1分钟内实现?你试过?

支持(0) 反对(0) .net_樊 | 园豆:169 (初学一级) | 2013-08-06 11:08

@.net_樊: 是的,1分钟不算要求,优化的好的话,1秒内都有可能

支持(0) 反对(0) dudu | 园豆:30585 (高人七级) | 2013-08-06 11:17

@dudu: 能教教我么?

我直接也经常处理大数据量,那都是使用计划任务,把很大部分过期数据,放到历史数据表里。

请问你是怎么优化的,我这就根据主键,查询,还能怎么优化,我不理解,请给出具体方案来。

支持(0) 反对(0) .net_樊 | 园豆:169 (初学一级) | 2013-08-06 12:11

@.net_樊: 建议先了解一下如何看执行计划,推荐一篇博文:SQL优化-使用执行计划

支持(0) 反对(0) dudu | 园豆:30585 (高人七级) | 2013-08-06 13:27
0

路过学习

panjk | 园豆:712 (小虾三级) | 2013-08-06 08:57
0

分表是必须的,

建立自增列,也就是先把Row_Number列建好,然后再使用你的方法查询

只会造轮子 | 园豆:2274 (老鸟四级) | 2013-08-06 09:59
0

我晕死你!你的数据库设计看看.

知道建聚簇索引的法则不?

知道怎么做算法简化不?

知道有些东西就要拿出来!不一定要EXT

[秦时明月] | 园豆:738 (小虾三级) | 2013-08-06 15:41
0

从你数据库的角度去考虑,Ext这东西加载数据的时候就比较慢。

不负春光,努力生长 | 园豆:1382 (小虾三级) | 2013-08-06 22:54
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册