java 倒序分页_翻动100万级的数据(自定义的MSSQL分页查询过程)

翻动

100

万级的数据 —— 只需几十毫秒 之揭秘篇:有详细的说明,不要错过。

感谢大家的支持!!!

昨天发了一个邀请,邀请大家帮忙测试,效果还可以,下面小结一下:

通过内部的计数器得知:访问次数是

1071

(其中有好多是自己点的:)),人数不是太理想,本来是想看看上万人同时访问的情况:)

系统资源的占用情况

内存 —— 很理想。

SQL

占用的内存最大也没有超过

65

M,一般是在

35

M左右;asp.net占用的内存最大也没有超过

40

M,一般是在

25

M左右。

CPU:

8

%左右,由于访问次数不多,也不够集中,所以这个数值也说明不了什么。自己连续点了n次下一页,发现CPU的使用率飘高,达到了

50

%左右。

但是对于

100

万的记录,AMD XP2000+ 的CPU 几十毫秒的放映速度,因该是可以接受的,甚至是很理想的吧。

毕竟服务器的CPU要比我的快很多吧,而且记录也很难达到

100

万吧。

结果还是很满意的,但是美中不足的是,我想看一下海量访问的情况下的效果,

希望大家再支持一下,多点几下,谢谢了。呵呵

另外说明一下:前n页可以在

60

毫秒内完成,n应该是大于

500

的,小于多少嘛还没有测试。后n页就比较慢了,需要

500

毫秒左右。

下面讨论一下翻页的技巧吧。

我没有用游标、临时表、

not in

in

这些方法,并不是说他们的效率不高,而是我还没有测试过。我只用了 top ,查了两次表。

大家也可提供一些其他的方法,我来测试一下,看看在

100

万条的情况下的效果。(请不要给在存储过程里面组串的,看着实在是太费劲了)

讨论的前提是在海量数据的情况下,至少是在

10

万以上的。如果是很少的数据呢,那怎么翻都可以了。也差不了多少。

1.

设置合理的索引

首先要做的是设置合理的索引,这个好像经常被忽略,至少很少被谈起。

注意:主键是索引的一种,而且是最快的一种。如果你都是把主键当作排序字段的话,那么你已经利用了索引。

不设置合理的索引的话,会导致查询速度非常的慢,甚至会造成超时。

这方面你可以做一个实验:找一个表,填进去

10

万条记录,假设有ID 、addedDate等字段,在查询分析器里面执行一下

select

top

10

*

from table

应该立刻就能出现结果。

然后再执行

select

top

10

*

from table order by

ID(这时ID字段是主键)

也是立刻就出现了结果。

然后再执行

select

top

10

*

from table order by

addedDate (这时addedDate字段没有索引)

你会发现速度很慢。

现在给addedDate 加一个非聚集索引,然后在执行上面的查询语句,速度也变得很快了。

可见索引神奇的效果!

这是翻动百万级记录最基本的设置,具体到我的那个论坛的翻页,我是设置了BoardID、 replyDate两个字段作为联合索引的。

因为是要在同一个讨论组李翻页,而且是按replyDate排序的。

2.

只返回需要的记录

对于海量数据,都读出来做缓存,那是不可想象的(记录少的话,也要看利用率,一般都是很浪费的)。

所以呢如果一页显示

20

条的话名那就只都读出来

20

条,这样就很省内存和时间。

注意:虽然ADO.NET里面有这个方法

SqlDataAdapter.Fill(DataSet1,startRecord,maxRecords,srcTable);

但是他还是要先从

SQL

里面把查询语句的查出来的所有记录都出来,然后在截取指定的记录数。这对于

SQL

来说是一样的,对于海量数据依然会很慢。

论坛里的首页用的是

select

top

20

*

from table where

boardID =

5

order by

replyDate

desc

这样呢就只返回了

20

条记录,再加上索引的功劳,速度是非常快的。

3.

尽量减少字段的长度

一个表可以建很多的字段,但是字段的总长度不能超过

8060

B,也就是说如果你建了一个

char

(

8060

)的字段,就不能在建其他的字段了。

我在第一次的测试中(星期天的),把主题的所有信息都放在了一个表里面,包括了一个nvarchar(

3600

)的主题内容的字段,复制记录的时候发现非常的慢,

当达到

9

万的时候,就已经很慢的,勉强把记录数拷贝到了

35

万,加了索引,测试了一下,翻页速度还是可以的,前n也都是很快的,后n页就很慢了,

如果再加上查询那就非常之慢了。

查看了一下数据文件吓了一跳 —— 他居然占用了

1.4

G的硬盘空间,怪不得拷贝和查询都慢的要死呢。

于是修改了一下表结构,把那个nvarchar(

3600

)的主题内容的字段踢了出去,放在一个单独的表里面。

再重新拷贝记录就非常的快了,很快就把记录数从

16

表成了

1048577

。昨天的测试就是在这个条件下进行的。

4.

技巧

终于到了翻页算法的地方了,呵呵没有等急吧。

思路呢就是先找到一个标志,然后呢把大于(或小于)这个标志的前n条记录取出来。

什么?没看懂。没关系,我举个例子吧。

假设是按ID倒序的,每一页显示

10

条记录,有

100

条记录,记录号正好是

1

100

(怎么这么巧??为了说明方便嘛)

那么第一页的记录就是

100

91

、第二页的记录就是

90

81

、第三页的记录就是

80

71......

我现在要翻到第三页,那么要找到第

21

行的记录的ID的值(也就是

80

),然后把小于等于

80

的记录用top

10

取出来就行了。

查询语句

declare

@pageSize

int

--返回一页的记录数

declare

@CurPage

int

--页号(第几页)1:第一页;2:第二页;......;-1最后一页。

declare

@Count

int

declare

@id

int

set

@pageSize=

10

set

@CurPage =

1

if

@CurPage = -

1

begin

--最后一页

set

rowcount @pageSize

select

@id=ID

from table order by

ID

end

--定位

if

@CurPage >

0

begin

set

@Count = @pageSize * (@CurPage -

1

) +

1

set

rowcount @Count

select

@id=ID

from table order by

ID

desc

end

--返回记录

set

rowcount @pageSize

select

*

from table where

ID <=@id

order by

ID

desc

set

rowcount

0

其中“定位”用了

select

@id=ID

from table order by

ID

desc

这种方法,感觉上是很省内存的,因为只记录了一个ID,

然后用

select

*

from table where

ID <=@id

order by

ID

desc

取得最终需要的记录

set

rowcount @pageSize 相当于 top @pageSize 。

优点:无论翻到哪一页,内存的占用情况都不变,多人访问内存也不会不变,很多人呢,还没有测试出来:)

缺点:单表、单排序字段。

http://community.csdn.net/Expert/TopicView3.asp?id=

4182510

发了这个帖子,回复的人很多,感谢大家的支持。这里有个误会我不得不说明一下,免的误人子弟。

在帖子里我并不是写了个算法就完事了,而是说了很多翻动海量数据要注意的地方,

比如建立合理的索引,只返回需要的记录 ,尽量减少字段的长度 等注意到或没有注意到的地方。

最后说的才是算法,可能是我的表达能力太差了吧,举的例子给大家带来了误会。

翻页的语句 ( @pageSize * (@CurPage -

1

) +

1

)

--定位

declare

@id

int

select

top

41

@id=ID

from table order by

ID

desc

--显示数据

select

top

20

*

from table where

ID <=@id

order by

ID

desc

按照ID倒序排列(也就是按照

int

类型的字段排序)

一页显示

20

条记录,这是显示第三页的语句

@pageSize * (@CurPage -

1

) +

1

=

20

*(

3-1

) +

1

=

41

正是因为ID是不连续的所以才需要用第一个语句来定位,如果是连续的那还用第一条语句做什么呢?

举各少量数据的例子:

假设有

10

条记录,ID是:

1000

500

320

205

115

110

95

68

4

1

。这回不写连续的了免的误会

一页显示两条记录,现在要显示第三页,那么第三页的id就是

115

110

先看第一条语句

select

top

5

@id=ID

from table order by

ID

desc

不知道大家有没有看懂这句,这时print @id 得到的结果是

115

再看第二条语句

select

top

2

*

from table where

ID <=

115

order by

ID

desc

这时的记录集就是

115

110

,也就是我们所需要的记录了。

注意:不需要连续的ID,也不局限只能按ID排序,你可以换成ReplyDate(最后回复时间)字段,

当然了

declare

@id

int

要改成

declare

@id datetime

这里的ID 是主键,唯一标识记录的字段,它本身就是一种索引,而且是效率最高的索引。

A.唯一标识记录的字段的值怎么能随意改动呢,那不乱套了吗?

B.主键是最快的索引,可能你还没有意识到(一开始我就不知道,学了

SQL

很久以后才知道的),如果你的算法用它作为排序字段,那么速度会很快,会比用其他字段(没有索引的字段)排序快很多。

C.用ReplyDate(最后回复时间)来排序,那么就必须给他建立索引(在海量数据的情况下),否则会超时的。

D.建立索引后,再执行添加、修改、删除会对数据库带来灾难性的折磨??

一开始我也是这么认为的,但是为了能够翻页,不得不加索引。

但是接下来的事实确打消了我的顾虑

先来看添加。

100

万条记录是怎么弄出来的?大家可以看到帖子里有很多标题一样的主题,对了是复制出来的。

我先加了

16

条记录,然后加上了索引。注意在

insert into

之前就已经建立好了索引!

接下来就是

insert into table

(...)

select

...

from table

影响的行数:

16

32

64

128

256

512

1024

2048

4096

8192

16384

32768

65536

131072

262144

524288

很快记录就达到了

100

完了。

最后一次也只不过一两分钟(具体的时间忘记了,反正是很快了)。

同时,论坛也提供了发贴的功能,只是在批量添加记录的时候,把一些记录的最后回复时间弄成了

2006

年,

所以,你发的帖子不会显示在第一页。但是你可以看到,执行时间是很快的。

可见添加的时候是不成问题的,索引是倒序排列的,所以影响的行数绝对没有你想象的那么多。

再来看修改。

看了sp1234的回复,加了修改的功能,只是为了测试,所以呢可以修改标题、最后发表时间、分组ID。

为什么可以修改这几个字段呢?标题是普通字段,最后发表时间和分组ID是索引字段。

修改这几个字段需要的时间都是很快的,在最后回复时间的右面有 [改] [删] 字样,大家可以试一试。

同样,修改的时候,影响的行数也不是很多。

最后看删除

不多说了,论坛提供了这个功能,试一下就知道了。另外,删除的时候,不用重新建立一遍索引吧?

在来说一下使用范围吧。

首先呢这只是一种方法,而不是一个通用的存储过程,也就是说要根据情况作适当的修改。

最佳使用环境:

单表,单排序字段,可以利用索引。

注意事项:

排序字段不必连续,最好使用

int

、datetime类型的字段,字符串型的字段没有试过,效果可能会略差。

表可以没有主键,但是对于海量数据的情况下,必须建立合理的索引。

有一个比较致命的限制,大家好像都没有发现,那就是排序字段的重复性,

最好是没有重复的,但不是说绝对不能有重复的记录,有不要紧,只要不跨页就行,跨页的话就会挤掉若干条记录,

用时间字段来排序,发生重复的记录的可能性就很小了。

扩展性:

bingbingcha(不思不归,不孟不E,原来是头大灰狼) 的回复很精彩

-----------------

这样的技巧在

SQL

区都讨论过了..速度是很快的..但是满足不了需求的..实用性太差了..现在的企业需要用到分页的大部分都是多表查询..单表分页满足不了需求的..

这个存储过程可以扩展..用临时表+楼主的方法..是个不错的选择..

-----------------

对于多表关联查询,有两种方法,第一种就是bingbingcha说的 —— “用临时表+楼主的方法”,这是在海量数据的时候唯一可行的方法。

但是在小数据量的时候,这么些就有一点繁琐,而且不容易归纳到通用的写法里。

先来看一下查询语句据的写法:

联合的

SELECT

a.ReplyID, a.TopicID

FROM

dbo.BBS_Reply a

INNER JOIN

dbo.BBS_body b

ON

a.BodyID = b.bodyID

where

a.ReplyID >

10

单表的

SELECT

ReplyID, TopicID

FROM

dbo.BBS_Reply

where

ReplyID >

10

有没有看到相同的地方:

select

显示的字段

from

where

条件

那么单表查询和多表查询有什么区别呢?

至少有很多的多表(单字段排序)查询都是可用这种方式的。

注意:我并没有说所有的多表(单字段排序)查询都可以用,看具体情况了。

这是一个效率最高(需要合理的索引的帮忙),比较通用的翻页方法。不知道这次我有没有讲明白。

==============================================================================

CREATE PROCEDURE

CN5135_SP_Pagination

/*

***************************************************************

** 千万数量级分页存储过程 **

***************************************************************

参数说明:

1.Tables :表名称,视图

2.PrimaryKey :主关键字

3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc

4.CurrentPage :当前页码

5.PageSize :分页尺寸

6.Filter :过滤语句,不带Where

7.Group :Group语句,不带Group By

效果演示:http://www.cn5135.com/_App/Enterprise/QueryResult.aspx

***************************************************************/

(

@Tables

varchar

(

1000

),

@PrimaryKey

varchar

(

100

),

@Sort

varchar

(

200

) =

NULL

,

@CurrentPage

int

=

1

,

@PageSize

int

=

10

,

@Fields

varchar

(

1000

) =

'*'

,

@Filter

varchar

(

1000

) =

NULL

,

@

Group varchar

(

1000

) =

NULL

)

AS

/*默认排序*/

IF

@Sort

IS NULL OR

@Sort =

''

SET

@Sort = @PrimaryKey

DECLARE

@SortTable

varchar

(

100

)

DECLARE

@SortName

varchar

(

100

)

DECLARE

@strSortColumn

varchar

(

200

)

DECLARE

@operator

char

(

2

)

DECLARE

@type

varchar

(

100

)

DECLARE

@prec

int

/*设定排序语句.*/

IF

CHARINDEX(

'DESC'

,@Sort)>

0

BEGIN

SET

@strSortColumn = REPLACE(@Sort,

'DESC'

,

''

)

SET

@operator =

'<='

END

ELSE

BEGIN

IF

CHARINDEX(

'ASC'

, @Sort) =

0

SET

@strSortColumn = REPLACE(@Sort,

'ASC'

,

''

)

SET

@operator =

'>='

END

IF

CHARINDEX(

'.'

, @strSortColumn) >

0

BEGIN

SET

@SortTable = SUBSTRING(@strSortColumn,

0

, CHARINDEX(

'.'

,@strSortColumn))

SET

@SortName = SUBSTRING(@strSortColumn, CHARINDEX(

'.'

,@strSortColumn) +

1

, LEN(@strSortColumn))

END

ELSE

BEGIN

SET

@SortTable = @Tables

SET

@SortName = @strSortColumn

END

SELECT

@type=t.name, @prec=c.prec

FROM

sysobjects o

JOIN

syscolumns c

on

o.id=c.id

JOIN

systypes t

on

c.xusertype=t.xusertype

WHERE

o.name = @SortTable

AND

c.name = @SortName

IF

CHARINDEX(

'char'

, @type) >

0

SET

@type = @type +

'('

+

CAST

(@prec

AS varchar

) +

')'

DECLARE

@strPageSize

varchar

(

50

)

DECLARE

@strStartRow

varchar

(

50

)

DECLARE

@strFilter

varchar

(

1000

)

DECLARE

@strSimpleFilter

varchar

(

1000

)

DECLARE

@strGroup

varchar

(

1000

)

/*默认当前页*/

IF

@CurrentPage <

1

SET

@CurrentPage =

1

/*设置分页参数.*/

SET

@strPageSize =

CAST

(@PageSize

AS varchar

(

50

))

SET

@strStartRow =

CAST

(((@CurrentPage -

1

)*@PageSize +

1

)

AS varchar

(

50

))

/*筛选以及分组语句.*/

IF

@Filter

IS NOT NULL AND

@Filter !=

''

BEGIN

SET

@strFilter =

' WHERE '

+ @Filter +

' '

SET

@strSimpleFilter =

' AND '

+ @Filter +

' '

END

ELSE

BEGIN

SET

@strSimpleFilter =

''

SET

@strFilter =

''

END

IF

@

Group IS NOT NULL AND

@

Group

!=

''

SET

@strGroup =

' GROUP BY '

+ @

Group

+

' '

ELSE

SET

@strGroup =

''

/*执行查询语句*/

EXEC

(

'

DECLARE @SortColumn '

+ @type +

'

SET ROWCOUNT '

+ @strStartRow +

'

SELECT @SortColumn='

+ @strSortColumn +

' FROM '

+ @Tables + @strFilter +

' '

+ @strGroup +

' ORDER BY '

+ @Sort +

'

SET ROWCOUNT '

+ @strPageSize +

'

SELECT '

+ @Fields +

' FROM '

+ @Tables +

' WHERE '

+ @strSortColumn + @operator +

' @SortColumn '

+ @strSimpleFilter +

' '

+ @strGroup +

' ORDER BY '

+ @Sort +

'

'

)

GO

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值