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

数据库：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是否需要启用二级缓存（估计该问题不应该在这个版块中提，只作为引子而已）

期待大侠的赐招，小生在下有礼，先谢过！

15 个解决方案

#1

网上查了很多，都说需要优化（有点像废话），但是并没有说到具体的优化方法。

#2

各位老大们，发点力吧，求求你们了

#3

直接跑plsql的分页语句，看看影响速度是在hibernate还是sql问题

#4

引用 3 楼 java3344520 的回复:

直接跑plsql的分页语句，看看影响速度是在hibernate还是sql问题

起初是在项目测试过程中出现慢的问题，后来干脆直接把语句在plsql下跑，结果发现同样存在类似延时问题，基本排除Hibernate的问题；

现在是想优化索引，但是就这个查询语句，有where条件，并且有order by排序，如何建立索引比较合理（目前已经把表里的10来字段都单独建立了索引，还是慢），是不是需要把几个字段组合在一起做一个索引，生成组合索引之类的？

#5

查看下执行计划
t.state=1 and t.h_fid='5a4ca4a22a74f721012a7dcf16890013' 这个查询条件选择性如何？如果把大部分的数据都查出来，那就用不了索引。

#6

引用 5 楼 cowboyhn 的回复:

查看下执行计划
t.state=1 and t.h_fid='5a4ca4a22a74f721012a7dcf16890013' 这个查询条件选择性如何？如果把大部分的数据都查出来，那就用不了索引。

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


这个是如何看的？
不过可以看得出644799是符合本次查询条件下的记录总数，数据库中一共有差不多130万条记录。

#7

你这也是查看执行计划的一种方法，它并没有用到索引，而且也不能用索引（查询条件的选择性太差），所以你建索引是没有效果的。
如果你这个业务比较关键且有足够的SGA，可考虑把表CACHE到BUFFER_CACHE中，提高查询效率。

#8

你看一下这篇文章，也许有帮助！
http://blog.csdn.net/yangzhawen/article/details/7661776

#9

如何建立索引比较合理（目前已经把表里的10来字段都单独建立了索引，还是慢）

-1

这样的索引，不如不建。

#10

引用 7 楼 cowboyhn 的回复:

你这也是查看执行计划的一种方法，它并没有用到索引，而且也不能用索引（查询条件的选择性太差），所以你建索引是没有效果的。
如果你这个业务比较关键且有足够的SGA，可考虑把表CACHE到BUFFER_CACHE中，提高查询效率。

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

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

引用 8 楼 gyouth 的回复:

你看一下这篇文章，也许有帮助！
http://blog.csdn.net/yangzhawen/article/details/7661776

按照文章所述，已调整为708M，问题还是没得到解决。

#11

求记录数没必要用order by t.ss_fid, t.ss_hs_fid, t.login_time

既然知道记录数你就知道你要检索的是前半部分还是后半部分，
前半部分： where rownum <= 1000720) where rownum_ > 1000701
后半部分： where rownum_ > 1000701) where rownum <= 1000720

具体好不好用俺也不知道，你可以试试

#12

引用 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而带来的负面性能影响？

#13

拜求~~

#14

t.state和 t.h_fid 建立组合索引

#15

这个问题解决没有呀？