【论题】+【方案】海量数据如何做分页处理

哑鸟 发布于 2013/05/22 13:48
阅读 1K+
收藏 10

论题时间:

2012-11-27

论题参与:

java 技术群(3 年以上) 95379959,所有成员

论题内容:

在软件开发中,大数据量的查询是一个常见的问题,经常会遇到对大量数据进行查询的场

景,项目实际开发中应用场景都很常见,尤其是大公司针对数据管理很是严谨,专业的 DBA

对数据的认识完全是摆脱了程序的执行力度, 数据处理都是从专业角度 , 因此大部分程序

员都觉得思想违背了现实程序的处理,但是 DBA 毕竟不是大众化,思想角度至少很是专业。

常见的对大数据量查询的解决方案有很多种方案:一、将全部数据先查询到内存中,然后在

内存中进行分页,这种方式对内存占用较大,必须限制一次查询的数据量,因此牺牲较大说

法很实际。二、采用存储过程在数据库中进行分页,这种方式对数据库的依赖较大,不同的

数据库实现机制不通,并且查询效率不够理想,海量数据的诸多操作,优化当然是首当其冲

了。

方案一:

方案人:

上海-java-?年(554952494)

方案内容:

常规处理-

一、将全部数据先查询到内存中,然后在内存中进行分页,这种方式对内存占用较大,

必须限制一次查询的数据量,因此牺牲较大说法很实际。

内存处理方式,实施的详细细节大体

二、采用存储过程在数据库中进行分页,这种方式对数据库的依赖较大,不同的数据库

实现机制不通,并且查询效率不够理想,海量数据的诸多操作,优化当然是首当其冲了。

存储过程方式基于用户体验,实现分页构造,构造第一页显示,其他页数展示,基于参

数传递应用存储过程;

三、

采用物理分页,基于数据的直接实现,物理分页根据所使用的数据库特性来组织,

根据数据环境和数据的特性建立索引,良好的索引建立是解决数据优化的好帮手,索引

当然是使得其当,不管是组合索引、独立索引,索引的填充因子和聚集、非聚集索引都

要考虑;

通用的 sql 分页方式,“限制行数结果集的倒序”分页:

1、取得符合条件的所有结果集中可以唯一标识的 Key 值(通常是主键),并正向排序。

2、利用数据库提供的特殊方法进行“最大结果集”的限制(在 Oracle 中使用 rownum,

sql server 中使用 top, mysql 中使用 limit...),

该“最大结果集”指包含当前所处页的所有记录数, 最大结果集”“应该只包含惟一的 Key

值。

3、 “最大结果集”进行逆序,并取得“显示当前页显示数量的结果集”,该结果集中只

包含惟一的 Key 值。

4、所取得的 Key 值取得显示数据,该显示数据就是当前页应该显示的数据。

效率处理-

海量数据处理难因为数据量大,那么解决海量数据处理难的问题其中一个技巧是减少数据

量。可以对海量数据分批处理(程序可以实现多线程方式),然后处理后的数据再进行合并操

作,这样逐个击破,有利于小数据量的处理,不至于面对大数据量带来的问题,不过这种方

法也要因时因势进行,如果不允许拆分数据,还需要另想办法(程序可以控制数据的拆分)。

现实操作优化处理用数据按天、按月、按年等存储的,都可以采用先分后合的方法,对数据

进行分开处理。

使用临时表和中间表 数据量增加时,处理中要考虑提前汇总。这样做的目的是化整为零,

大表变小表,分块处理完成后,再利用一定的规则进行合并,处理过程中的临时表的使用和

中间结果的保存都非常重要,如果对于超海量的数据,大表处理不了,只能拆分为多个小表;

建立视图、可能是部分程序员都很少考虑过,因为视图中的数据来源于基表,尤其是对海量

数据的处理,可以将数据按一定的规则分散到各个基表中,查询或处理过程中可以基于视图

进行,这样分散了磁盘 IO

以上只是针对数据优化处理,不涉及分页核心方案;

针对海量数据的认识详情资料可找我索取;

此处引申一个概念:物化视图

物化视图,它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行

查询时,就可以避免进行这些耗时的操作,而从快速的得到结果。

1、 建立基于优化设计的基表,基表设计字段尽可能的优化,便于建立索引,有的人觉得海

  量数据使用索引感觉是无用之功,其实亦不然,这里不是用索引做数据的最好方案,要

  知道,长城也是一块一块砖堆积的,海量数据的优化是在每个点上面最好优化处理,能

  针对数据优化有好处的手段作用在海量数据上面,优化从每一小步开始。

2、 针对不同版本数据库优化程序,海量数量,即使你只是做数据的查询处理,但在做存储

  数据时都已经做好数据的分表,分区,针对数据存储的各种手段此处不便于详细说明,

因此程序员只需做到针对数据库中的表或者是视图进行操作,这里可以算是数据库本身

程序的优化操作和处理吧。

3、 使用视图,传统概念的视图,每次访问视图的时候,他都会创建个临时表,然后执行一

  次。在海量数据的情况下,以访问执行的方式效率是非常低的。而物化视图,则会定时

  去刷新这个临时表,而不是你在用的访问的时候才会去刷新,物化视图的“临时表”是

  一直存在。在常规的视图处理中,提升质量使用物化视图。

4、 方案实现:

针对视图,或临时表做处理;

1) 应用聚合函数,、分别取出最大 id 和最小 id、以及 count 总数(表设计 ID 为主键,

自增)

ID 为自增设置,用 sql 的物理分页方式,在保证 ID 是连续不断的自增的前提下,采用

顺序算法,快速定位,如:需查询 5000W 数据中的第 500 页数据,实现:顺序显示为:

50(每页显示)*500(页数)=25000(ID 位置)

Sql 的实现:

select * from content where id>page_size*page-1 and id<page_size*page,

select * from content where id>24950 and id<25000;

存储过程也可实现,实现方式见方案 5;

以上只是针对 ID 为连续的递增方式做处理;

但是 ID 若有删除,数据定位就会有问题了,因此,此时算法很是重要,一定的算法也

是提高数据准确性的方式;

select min(id) from 表名 min_id

select max(id) from 表名 max_id

select count(*) from 表名 count

得出的结果:max_id≥Record_count

如果 max_id=count 说明 ID 连续不断,是理想情况按照前面的操作就可以定位需要

的分页数据 ID 范围。

如果 max_id>count 说明 ID 中间出现了空缺,这是现在面对的实际情况,在这种情

况下就进入下一步操作。

max_id-min_id/count=P

p 为整体 ID 差距系数。

2)、获取准确的起始位置的 ID

 最先把 start_id 的位置定在:min_id+page_size*page-1 得到 start_id=等于实际

值(随意定值)

 然后把 min_id 到 start_id 这段 ID 范围内的记录进行统计:

select count(*) from content where id<Start_id

select count(*) from content where id<得到的实际值

假设现在得到的结果是:假设结果

   现在说明取得的数据少了实际值-假设值=相差值条数据,也就间接说明中间的连续

ID 误差有次数。

   把相差次数赋给变量 P。

   现在 start_id 的位置要向后推移,现在要推移多少是一个未知数,下面将用到一

个函数,就是把 P 与 page_size 进行异和运算,得到结果。

P||page_size=M

P-M/分页显示条数=N。这里取出 P 与 page_size 相除的整数。

下面 start_id 下移:start_id+(N*page_size),然后重新统计

select count(*) from content where id< Start_id+(N*page_size)

重复以上操作,不断把 Start_id 向下推移,让 P 的值最后在 0-page*size 的范围

内。如此获取最终结果值,获取的最终结果值用 top 形式取出最终的几条数据,取出的

第一位 id 便是实际的 start_id。

这样就得到了最大接近实际真实情况的 Start_ID 的真实值了,当 R’的值在

0-page*size 的范围了,就用下面的方法得到最终真实的情况的 Start_ID+最终获取值

的大小=实际 start_id

   3)、获取到准确的 ID 后方案,可采取形式便是多样化了

5、4 步骤是实现基于顺序索引,也可借助 Lucene 的思想实现倒排索引,实现了 分块索

引 , 能 够针对新 的文件建立小文件索引,提升索 引速度。

方案二:

方案人:

北京-福-2(282937458)

    方案内容:

预先加载进内存中一部分(顺序存储),这个数据量应该可以满足一定页数的查找,并且

key 值是全部的 列组合而成。当用户查寻的时候,参数传递进来,与 key 值比对,key 包

含参数的情况下是最终的数据,然 后从缓存里删掉这第一页最后的一个数据之前的所有数

据,另起一个监控线程加载相应条数进内存,可以 把第一页数据存到用户的硬盘上(加上

时间戳),方便用户做回退操作(退出时可考虑删掉一部分时间以 前的)。

设计成伪翻页,都是从内存中顺序读。

弊病:换条件时需要重先加载进入内存。

备注:常见方式,牺牲内存

方案三:

方案人:

 武汉-java-3 年(237818280)

 方案内容:

1、开启服务器是 加载相应数据到缓存中(公司有自定义的 data

unit,用于网上存储传

输)

2、优化访问 sql 语句,尽量达到 IO 最少,因为 IO 一般都是数据库的瓶颈(我们公司有专

业的 DBA)

其他的我想表达是还有:有个数据库 叫 greenplum 他就采取了云存储 我们公司正在学

习处理,

原理就是同一个 web 服务器需要多个 DB 数据库支持

晚上开启 tomcat 服务器,一般在启动 tomcat 的同时利用线程启动线程 利用自定义的数据

结构 进行加载,因为我们公司数据量并不是非常庞大,一般晚上加载 4——6 小时,这样

做对硬件及其他的东西有些要求,并且弊端也出现了,就是在加载的过程中是很难进行访问

的。

关于数据库 方面,公 司对每个 程序员提 交的 sql 代码都需 要进行审 查,举个 例子:

select max(id),min(id) from table 和 select (select max(id) from table

),(select min(id) from table) from dual,这样是对数据库进行优化,可是关于分

页这个问题,我貌似还没注意这方面。但是,公司的 sql 只是相对而言的快。

关于 greenplum 数据库 这是一个涉及到云计算的的工程 数据库是可以根据行或列进行

存储的 他恰恰与 oracle 相反,oracle 要求集成数据在同一共有的空间中,而 greenplum

相反,他是依据不同的条件 把数据分布在不同的存储机器上,通过一个或多个 master 机

进行调配,运行时可以多台计算机同时进行处理,符合云计算原理。但是有个弊端 就是要

求网络非常的通常 需要千兆交换机,但很适合小型公司。

方案四:

方案人:

北京-零度 J-4 年(469102165)

方案内容:

分页的存储过程:CREATE

PROC

proc_pageview

  --要分页显示的表名

--用于定位记录的主键(惟一键)字段,可以

--要显示的页码

  --每页的大小(记录数)

--以逗号分隔的要显示的字段列表,如果不

--以逗号分隔的排序字段列表,可以指定

--查询条件

--总页数

@tbnamesysname,

@FieldKeynvarchar(1000),

是逗号分隔的多个字段

@PageCurrent int=1,

@PageSize

@FieldShow

   int=10,

nvarchar(1000)='',

指定,则显示所有字段

@FieldOrder nvarchar(1000)='',

在字段后面指定 DESC/ASC

@Where

@RecordCount

AS

SET

NOCOUNT

varchar(1000)='',

int

ON

IS

NULL

OUTPUT

--检查对象是否有效

IF OBJECT_ID(@tbname)

BEGIN

RAISERROR(N'对象"%s"不存在',1,16,@tbname)

RETURN

END

IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0

AND

AND

BEGIN

RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)

RETURN

END

--分页字段检查

IF ISNULL(@FieldKey,N'')=''

BEGIN

RAISERROR(N'分页处理需要主键(或者惟一键)',1,16)

RETURN

END

--其他参数检查及规范

IF

IF

IF

IF

ELSE

IF

    SET @FieldOrder=N'ORDER

ISNULL(@Where ,N'')=N''

BY

'+LTRIM(@FieldOrder)

ISNULL(@PageCurrent,0)<1

ISNULL(@PageSize,0)<1 SET

ISNULL(@FieldShow ,N'')=N''

ISNULL(@FieldOrder,N'')=N''

    SET @FieldOrder=N''

SET

@PageCurrent=1

@PageSize=10

SET @FieldShow=N'*'

OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0

OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0

SET

ELSE

SET

@Where=N''

@Where=N'WHERE

('+@Where+N')'

--如果@PageCount 为 NULL 值,则计算总页数(这样设计可以只在第一次计算总页数,以后调

用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可

以给@PageCount 赋值)

IF @RecordCount

BEGIN

DECLARE

SET

IS

@sql

+N'

EXEC

TPUT

END

--计算分页显示的 TOPN 值

DECLARE @TopN varchar(20),@TopN1

SELECT

NULL

nvarchar(4000)

@RecordCount=COUNT(*)'

'+@tbname

int

OUTPUT',@RecordCount

OU

FROM

@sql=N'SELECT

  +N' '+@Where

sp_executesql @sql ,N'@RecordCount

varchar(20)

@TopN=@PageSize,

@TopN1=(@PageCurrent-1)*@PageSize

--第一页直接显示

IF @PageCurrent=1

       EXEC(N'SELECT

+N'

+N'

+N'

+N'

ELSE

BEGIN

--处理别名

IF

TOP

'+@TopN

'+@FieldShow

FROM '+@tbname

'+@Where

'+@FieldOrder)

@FieldShow=N'*'

    SET @FieldShow=N'a.*'

--生成主键(惟一键)处理条件

DECLARE

SELECT

WHILE

@Where1

nvarchar(4000),@Where2

nvarchar(4000),

@s nvarchar(1000),@Field sysname

@Where1=N'',@Where2=N'',@s=@FieldKey

CHARINDEX(N',',@s)>0

 SELECT @Field=LEFT(@s ,CHARINDEX(N',',@s)-1),

@s=STUFF(@s ,1,CHARINDEX(N',',@s),N''),

@Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field ,

@Where2=@Where2+N' AND b.'+@Field+N' IS NULL',

@Where=REPLACE(@Where ,@Field ,N'a.'+@Field),

        @FieldOrder=REPLACE(@FieldOrder,@Field ,N'a.'+@Field),

        @FieldShow=REPLACE(@FieldShow ,@Field ,N'a.'+@Field)

@Where=REPLACE(@Where ,@s ,N'a.'+@s),

@FieldOrder=REPLACE(@FieldOrder,@s ,N'a.'+@s),

@FieldShow=REPLACE(@FieldShow ,@s ,N'a.'+@s),

@Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s ,1,5,N''),

@Where2=CASE

WHEN

ELSE

--执行查询

EXEC(N'SELECT

+N'

+N'

+N'

+N'

+N'

+N'

+N'

@Where=''

@Where+N'

THEN

AND

IS

N'WHERE

('

NULL'+@Where2+N')'

('

SELECT

END+N'b.'+@s+N'

TOP

'+@TopN

'+@FieldShow

FROM '+@tbname

a LEFT JOIN(SELECT

'+@FieldKey

FROM '+@tbname

a '+@Where

'+@FieldOrder

TOP

'+@TopN1

+N')b ON '+@Where1

+N' '+@Where2

+N' '+@FieldOrder)

END

GO

方案五:

方案人:

群成员

方案内容:

Hash 算法、Lucene 索引等方式

综上所述,方案较为简便,阅读人员,可以审批提取意见,便于后期订阅;

以上信息只是个人鉴赏所解,不足以引起学识争论,若有不足之处,仍属常规正常情况

所定,信息出至 java 技术群(3 年以上) 95379959,如有版权或者是学术争论,谨致以真诚道

歉!

加载中
0
命运13
命运13
顶!!我想入群 qq:1845428485 以申请 ,望同意!!
返回顶部
顶部