oracle分页技术性能比较

一、前言

在一个有30亿条数据的大表上分页,为了对方案进行性能测试,先忽略其他条件查询的影响,单看下分页部分的性能,顺便考察说明下oraclerownum使用中一些比较tricky的地方。 实验条件: 表结构如下,内有2千万条实验数据。 table-columns

二、实验

提供7种不同方式(其实是5种,二和四是一种、三和五是一种)方式的 。第一种只是为了demo一下假设的一种错误逻辑方式,第二种和第四种是一种逻辑正确,但是性能极差的方式。筛选下来看上去性能可行的方式是第五、第六、第七方式。 这里仅仅记录没中方式的执行结果和计划。

方式1

笨笨的想想。Oracle里面不是有个变量叫rownum,顾名思义,就是行号的意思,我要获取第十行到第二十行的数据,sql写起来很精练!比myslqlimitmssqltop折腾看着还要优雅!

喔!十条记录执行了十分钟还么有结果,一定是哪儿有问题了,shut了重试。那就来个简单的:

也没有记录,再尝试rownum=2都不会有记录。 分析rownum的原理就不难理解。rownum是查询到的结果集中的一个伪列,可以理解成在我们查询到的结果上加序号。按照这个逻辑,写rownum=1是能得到结果集的第一行。执行rownum=2时,先比较第一行,其rownum是1,则扔掉,考察下一行,rownum又是1,直到扫描完整个表,没有满足条件的结果集。 查询计划如下。

执行了4分钟,没有得到一条记录。尝试下面的方法。

方法2

明白了rownum的意思,意识到解决问题的办法,是再加一层查询,即里面括号的是我们要的数据,然后从上面选择rownum,其实就是行号为10到20的数据行。

oracle-rownum-2-round-between1 看到返回的是希望的1019的记录,但是耗费的时间有点长,达到了34S 查询计划如下

?方式3

  • 尝试另外一种写法,看起来语义好像也差不多。先取出满足条件的前20条记录,然后在中间选择行号大于10的,即1020行的记录。

oracle-rownum-2-round-between2 看到结果集,和2相同,但是耗费时间只有,时间是0.078S。问题出在哪儿呢,观察下查询计划。

对比23的查询计划。不用仔细分析,看计划步骤中间的每一步操作的涉及的行数,以及consistent?gets和physical?reads的不同量级即可理解的差不多。2是把获取所有数据,然后在上面选择1020的行,3是只获取前20行,从中选择10行之后的数据行。

方式4

方式2?中?有order?by,这是最常见的一种场景了,按照某个列排序,然后去中间某几条记录,其实就是某一页。

oracle-rownum-2-round-between1_order_by ? 执行了13分钟,获取10行数据。看看查询计划。

?方式5

方式3中加上order?by?条件。

oracle-rownum-2-round-between2_order_by 和方式2类似,花费时间也是毫秒级。 查询计划

方式6

使用minus

oracle-rownum-1-round-minus ? 对应查询计划如下

?方式7:

采用row_number()解析函数

oracle-rownum-over-2-round-between ? 执行计划如下:

?三、总结

为了简单期间,只是从2千万条记录中查询1020行的数据。考察发现如下三种方式性能上是可以接受的3567写法是可以接受的(35其实差不多,如果如实验所示,在order?by列上是索引聚集的话),都是毫秒级可以出结果。 但是当查询后若干条记录的时候,如一千万行的前十行记录。每种也都需要几分钟的执行时间。

性能比较如下:

? 使用row_number函数 三层的select 使用minus关键字
1000万行前面10 766.234 404.125 795.562
100万行前面10 12.468 12.438 26.125
10万行前面10 1.265 1.125 1.5
1万行前面10 0.329 0.312 0.25
1000行前面10 0.438 0.468 0.203
100行前面10 0.25s 0.219s 0.265

四、分析

1. ?执行计划中的count 和?count?stopkey的影响。

count和 count stopkey都是伴随伪列rownum出现的;用来对rownum进行增量。如果不为Rownum伪列指定限定条件,则是使用count操作,如果对rownum伪列应用一个限定条件,如where rownum<10;则使用 count stopkey操作。为了实施限定条件,优化器用count?stopkey操作代替count操作,它将rownum伪列的增量值与限定条件中指定的值进行比较,如果rownum伪列的值大于限定条件中指定的值,则查询不再返回更多的行。如本文的方式2的计划中可以看到count,而方式3中是就是count stopkey了。对比两种方式也可以看到使用count stopkey的时候?consistent?gets?? 和 physical?reads两个指标都对应小的多,原因就是后面的不再读了。,? ??sql优化之stopkey这个帖子中列出了一个stopkey使用的例子,包括对计划的详细解释。

2. 执行计划中的?INDEX?FULL?SCAN 3. ??consistent?gets?? 和 physical?reads

 

关于查询计划的相信解释可以参照:DaveOracle 执行计划(Explain Plan) 说明

五、最后

本来到这里几种对照就应该结束了。尤其是场景2和场景4,场景3和场景5只是多了个order?by子句,因为本来就是以ID列为主键的索引组织表。理解就是按照ID顺序来存记录的,则是否显示的写order?by子句应该没有影响,却观察到23的结果和45的结果不一样呢?为了主题集中期间,这个问题放在下一篇介绍。

附:

原创文章。为了维护文章的版本一致、最新、可追溯,转载请注明: 转载自idouba

本文链接地址: oracle分页技术性能比较


,

No comments yet.

发表评论