分页查询大数据量越往后越慢如何解决及索引如何构建比较合理?

PangSir 2013-06-26 05:23:13
数据库:mytable,目前记录130万左右;
字段:id-ID主键已加索引,version-版本,h_fid-单位ID,hs_fid-部门ID, fid-用户ID, login_time-登录时间(yyyyMMddHHmmss,比如:20130626170722), state-状态, hs_name-部门名字, name-用户名字

以前用MySQL数据库,百来万条数据库在表中,相同的查询条件,跳转到最后一页时,除了第一次查询有点点慢(2秒以内),如再进行查询相同页数的记录时,几乎不花什么时间,都是在几个纳秒以内;
由于客户扩容本项目,改用了Oracle数据库,现在发现,查询前面几页时,速度还行,越往后查越慢,直接跳转到最后一页时,简直弱爆了,都有30秒左右,查询语句如下:
select * from ( select row_.*, rownum rownum_ from ( select t.id as id_1, t.version as ver_1, t.h_fid as h_fid_1, t.hs_fid as hs_fid_1, t.fid as fid_1, t.login_time as log_1, t.state as sta_1,t.h_name as hs_name_1,t.name as name_1 from wf.mytable t where t.state=1 and t.h_fid='5a4ca4a22a74f721012a7dcf16890013' order by t.ss_fid, t.ss_hs_fid, t.login_time) row_ where rownum <= 1000720) where rownum_ > 1000701
这个语句只是查询具体记录信息,按理还需要一个select count (*) from (select t.id as id_1, t.version as ver_1, t.h_fid as h_fid_1, t.hs_fid as hs_fid_1, t.fid as fid_1, t.login_time as log_1, t.state as sta_1,t.h_name as hs_name_1,t.name as name_1 from wf.mytable t where t.state=1 and t.h_fid='5a4ca4a22a74f721012a7dcf16890013' order by t.ss_fid, t.ss_hs_fid, t.login_time),才能够得出记录总数,最分页用,这个语句也是耗时大概8秒左右
先说明一个问题,现在项目用的是Hibernate3.0,分页用的是setFirstResult(),setMaxResults(),以上的查询分页语句是Hibernate框架自行构造;
故有以下问题:
1、需要怎么优化数据库配置才能够缓解这种延时现象吗?比如是否需要给数据库配置默认初始内存和缓存大小之类的。
2、如果需要加索引,按照这样的查询语句该如何构建索引比较合理,是否需要组合索引?
3、Hibernate是否需要启用二级缓存(估计该问题不应该在这个版块中提,只作为引子而已)

期待大侠的赐招,小生在下有礼,先谢过!
...全文
2167 15 打赏 收藏 举报
写回复
15 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
这个问题解决没有呀?
  • 打赏
  • 举报
回复
kinlin 2013-07-08
t.state和 t.h_fid 建立组合索引
  • 打赏
  • 举报
回复
PangSir 2013-07-02
拜求~~
  • 打赏
  • 举报
回复
PangSir 2013-07-01
引用 11 楼 kinlin 的回复:
求记录数没必要用order by t.ss_fid, t.ss_hs_fid, t.login_time 既然知道记录数你就知道你要检索的是前半部分还是后半部分, 前半部分: where rownum <= 1000720) where rownum_ > 1000701 后半部分: where rownum_ > 1000701) where rownum <= 1000720 具体好不好用俺也不知道,你可以试试
按照你的提示,尝试不用order by t.ss_fid, t.ss_hs_fid, t.login_time:第一次查询时,无论是select *查记录,还是select count(*)统计,依然是挺耗时,分别是22秒和5秒,但是第二次查询相同分页数据时,基本都能控制在0.5到0.7秒以内,这个是不是得益于缓存?如果是,那如何用缓存来解决或是缓解因为增加order by而带来的负面性能影响?
  • 打赏
  • 举报
回复
kinlin 2013-07-01
求记录数没必要用order by t.ss_fid, t.ss_hs_fid, t.login_time 既然知道记录数你就知道你要检索的是前半部分还是后半部分, 前半部分: where rownum <= 1000720) where rownum_ > 1000701 后半部分: where rownum_ > 1000701) where rownum <= 1000720 具体好不好用俺也不知道,你可以试试
  • 打赏
  • 举报
回复
PangSir 2013-07-01
引用 7 楼 cowboyhn 的回复:
你这也是查看执行计划的一种方法,它并没有用到索引,而且也不能用索引(查询条件的选择性太差),所以你建索引是没有效果的。
如果你这个业务比较关键且有足够的SGA,可考虑把表CACHE到BUFFER_CACHE中,提高查询效率。

SGA术语第一次接触,检查了一下测试机的SGA,如下:

以上信息是否代表有足够的SGA?那如何把表CACHE到BUFFER_CACHE中呢?这样一来能否保证在实时修改、删除记录时磁盘里的信息变动后CACHE里的信息也能够及时得到同步或更新?还望能够更深入指点一下,谢谢!

引用 8 楼 gyouth 的回复:
你看一下这篇文章,也许有帮助!
http://blog.csdn.net/yangzhawen/article/details/7661776


按照文章所述,已调整为708M,问题还是没得到解决。
  • 打赏
  • 举报
回复
陈字文 2013-06-29
如何建立索引比较合理(目前已经把表里的10来字段都单独建立了索引,还是慢) -1 这样的索引,不如不建。
  • 打赏
  • 举报
回复
PangSir 2013-06-28
引用 3 楼 java3344520 的回复:
直接跑plsql的分页语句,看看影响速度是在hibernate还是sql问题
起初是在项目测试过程中出现慢的问题,后来干脆直接把语句在plsql下跑,结果发现同样存在类似延时问题,基本排除Hibernate的问题; 现在是想优化索引,但是就这个查询语句,有where条件,并且有order by排序,如何建立索引比较合理(目前已经把表里的10来字段都单独建立了索引,还是慢),是不是需要把几个字段组合在一起做一个索引,生成组合索引之类的?
  • 打赏
  • 举报
回复
gyouth 2013-06-28
你看一下这篇文章,也许有帮助! http://blog.csdn.net/yangzhawen/article/details/7661776
  • 打赏
  • 举报
回复
cowboyhn 2013-06-28
你这也是查看执行计划的一种方法,它并没有用到索引,而且也不能用索引(查询条件的选择性太差),所以你建索引是没有效果的。 如果你这个业务比较关键且有足够的SGA,可考虑把表CACHE到BUFFER_CACHE中,提高查询效率。
  • 打赏
  • 举报
回复
PangSir 2013-06-28
引用 5 楼 cowboyhn 的回复:
查看下执行计划
t.state=1 and t.h_fid='5a4ca4a22a74f721012a7dcf16890013' 这个查询条件选择性如何?如果把大部分的数据都查出来,那就用不了索引。

刚刚接触Oracle,很多术语都不懂,你所指的“执行计划”,是否是在plsql执行查询语句后,按下F5可以查看,如下图:


这个是如何看的?
不过可以看得出644799是符合本次查询条件下的记录总数,数据库中一共有差不多130万条记录。
  • 打赏
  • 举报
回复
cowboyhn 2013-06-28
查看下执行计划 t.state=1 and t.h_fid='5a4ca4a22a74f721012a7dcf16890013' 这个查询条件选择性如何?如果把大部分的数据都查出来,那就用不了索引。
  • 打赏
  • 举报
回复
iqlife 2013-06-27
直接跑plsql的分页语句,看看影响速度是在hibernate还是sql问题
  • 打赏
  • 举报
回复
PangSir 2013-06-27
各位老大们,发点力吧,求求你们了
  • 打赏
  • 举报
回复
PangSir 2013-06-26
网上查了很多,都说需要优化(有点像废话),但是并没有说到具体的优化方法。
  • 打赏
  • 举报
回复
相关推荐
发帖
Oracle

1.6w+

社区成员

Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
帖子事件
创建了帖子
2013-06-26 05:23
社区公告
暂无公告