Oracle高效排序分页分析

雨中笑 数据库 经验 737热度

简介记录在工作上遇到的Oracle分页性能分析

公司的日志是放在Oracle中的,我在go项目配置好Oracle后,就开始写代码,发现Oracle和mysql还是挺不同的,就目前遇到的分页来说,Oracle是没有limit的,它是通过rownum来分页的。

从rownum的英文上可以知道是 行数量,下面我们开始分析:

SELECT ROWNUM, a.event, a.createtime FROM oray_ddns.sun_client_log a where event in ('remote_login', 'remote_logout') and rownum <5  ORDER BY "CREATETIME" DESC


rownum和order by一起在一个select时排序会被打乱,这时候我们就需要子查询来解决这个问题

1、简单的between分页

SELECT
num, event, createtime
FROM
(select rownum num, a.* from oray_ddns.sun_client_log a WHERE event in ('remote_login', 'remote_logout') ORDER BY a.createtime) b where num BETWEEN 0 AND 5


可以发现此时排序分页是可以的,但是查询时间太久了足足18s,这是因为它将内层的结果集全部排序,再从中取出想要的分

其实这个中分页还是有弊端的,你分下一页你就能看出了

顺序又乱了,(where num>6 and num<=10也是一样顺序乱了)

2、高效一点的写法

SELECT
num, event, createtime
FROM
(select rownum num, b.* from (select a.* from oray_ddns.sun_client_log a
WHERE event in ('remote_login', 'remote_logout') ORDER BY a.createtime desc) b
where rownum <=5 ) c where num > 0


可以看出效率好了一点点,也是要15s,只需取排序后的<=部分结果,而一般分页查询也访问前面的几率是比较大的,如果访问分页后的部分,效率就和1差别不大了,而且这个再下一页排序也不会出现问题

排序正常

3、对order by字段建立索引

SELECT
num, event, createtime
FROM
(select rownum num, a.* from oray_ddns.sun_client_log a
WHERE event in ('remote_login', 'remote_logout') AND clientinfo is not null and rownum <= 5 ORDER BY a.createtime) b where num >0;


可以看出查询不到1s,效率提高了十多倍,而且后续的分页也是没有问题的。

结果分析

从结果上看,第一种方式的分页看似可以,其实后面再分页会出现排序错乱,不可取。第二种方式完全可以分页,不需要另外加索引啥的,第三种方式需要给order by的参数加索引,让查询优先执行order by,然后执行rownum。从性能上看的话第三种明显优于第二种,从实际出发选择排序,第一种一定淘汰,后面两种根据自己需求情况选择。

上面只是我的一个例子,单单挑出几个字段来做实列,真实表数据其它必须字段是做了索引的,因为这次需求是只给用户个人看他的日志,createtime就没有加索引,采取第二种方式再子查询前带上userid,速度非常快


很赞哦!(5)

本文阅读量 1815发布于 2021年6月24日

您的访问IP 100.28.2.72最早于 2024年6月13日 9时04分43秒 阅读过本文 为本文提供了 1 热度 1 阅读量

文章评论
回帖