关于分页语句的优化相信很多大神们已经烂熟于心了，高手请直接飘过。这篇文章之所以被我记录下来，是因为在本次优化过程当中颇为费了一番周折，甚至还发现了一个我以前从来没有（或者说被我遗忘掉的）注意过的现象，所以还是可以写一下。内容有点儿长，大家有耐心的话可以慢慢看。
事情的起因是这样的，最近上级的安全审计部门来我们公司做审查，在使用到我们的财务系统的时候，说我们的系统用起来很慢，由于平日的时候也很少接到财务的同事来反馈相关的问题，本着积极主动的原则我就顺手抓了一把这个数据库中IO消耗比较大的SQL:
于是发现这个SQL，截止抓取时间的时候它的IO高达1.1T，单次需要近11G的样子，那么活儿就来了。看看这个SQL长啥样的：
SELECT y.*
FROM mm_writeoutstatus_to d,
mm_writeout_to y
where d.id = y.id
and d.datasource in ('NVHLPCIS','VHLPCIS' )
and d.writeouttype = '1'
and (d.status = '00' or (d.STATUS = 'ZZ' and d.count < = 3))
and d.batch_id is null
and y.opflag in ('5', '6')
and rownum <= 20
order by y.opdate asc;
这两个表里面的数据记录都是千万级的，只是d表有2G左右，y表比较大，10来个G的样子。
老实说说，我第一眼看到的就是这个rownum的使用位置，感觉怪怪的，按理说这种返回rownum的一般都应该搁置在order by之后才有意义，而且这种分页框架如果是在11g里面大概率是用不上count stopkey特性的，但是本生产库是19c，于是就看了看他的执行计划：
执行跑了十几秒的时间，而且逻辑读还挺高，有时候甚至跑了10多分钟都出不了结果。老实说我感觉挺意外的，19c相比于11c，这特性增强也太叼毛了，居然可以自动识别这种不太健康的分页框架。但让我感觉很奇怪的是，这SQL的速度也太奇怪了点儿，最快要15秒多，慢的时候甚至10来分钟都跑不出来，既然用上了count stopkey那么速度应该很快才对。于是尝试看看A-Time的执行计划：
唉。。看看这几个地方，说白了d表查询出4786K条数据，然后和y表也做NL嵌套连接，也循环了4786K次，最后再从循环后的结果中走count stopkey，之后在进行排序。这根本就快不起来，更何况这SQL查询实际上一条结果都没有。尤其是这SQL还奇葩的把order by 排在了最外面，也不知道他们这么弄到底是想做啥。
按我的认知，两表关联的分页语句正确的count stopkey特性指的是，两表走NL嵌套，然后由排序表作为驱动表，两表的嵌套循环连接只需要执行20次后，无论有没有结果都应该直接返回才对（这个理解其实是错误的，后面有解释）。
不管了先按我理解的来改写一下这个分页框架吧，首先在y表上面的opdate列创建一个asc排序的索引，消除sort order by，然后在对SQL套上正确的分页框架：
create index idx_y on mm_writeout_to(opdate asc);
select * from (select a.*, rownum rn
from (SELECT y.*
FROM mm_writeoutstatus_to d,
mm_writeout_to y
where d.id = y.id
and d.datasource in ('NVHLPCIS','VHLPCIS' )
and d.writeouttype = '1'
and (d.status = '00' or (d.STATUS = 'ZZ' and d.count < = 3))
and d.batch_id is null
and y.opflag in ('5', '6')
order by y.opdate asc) a
)
where rownum <= 20;
执行还是很慢，A-Time计划是这样：
仔细思考了一下感觉还是不对，在两个表的内连接关联分页语句中，排序的表应该作为驱动表才对，但是这里却反过来了，估计是优化器发觉y表的连接列是一个主键列，所以就把他当做被驱动表了，并且里面的sort order by 也没能消除，那么改进一下y表的索引并且加一个Hint来试试吧：
create index idx_y_2 on mm_writeout_to(opflag,id,opdate asc);
select * from (select a.*, rownum rn
from (SELECT /*+ index(y idx_y_2) leading(y) use_nl(y,d) */ y.*
FROM mm_writeoutstatus_to d,
mm_writeout_to y
where d.id = y.id
and d.datasource in ('NVHLPCIS','VHLPCIS' )
and d.writeouttype = '1'
and (d.status = '00' or (d.STATUS = 'ZZ' and d.count < = 3))
and d.batch_id is null
and y.opflag in ('5', '6')
order by y.opdate asc) a
)
where rownum <= 20;
3秒内出了结果，反复多次执行都是这样，效果提升还是比较明显的，但还是没走出我心中理想的状态。这种按理应该秒出才对，而且排序还是没能消除：
真正理想的结果是，sort order by 被消除，同时y表走索引返回20条记录后，就去和b表进行嵌套（这个理解也是错误的，后面有解释），那么b表就只需要被扫描出20条结果就行。但是这里的情况跟前面的情况是差不多的，唯一的区别是修改了NL循环的驱动表之后，通过索引范围扫描快速完成y表过滤，然后NL的次数被减少很多而已。实际上这个count stopkey的效果根本就没展示出来。
下面再改进一下y表的索引，将排序列放在首位（原因后面解释）：
create index idx_y_3 on mm_writeout_to(opdate asc,id, opflag);
查询花了近1分钟时间，排序消除了,但是count stopkey仍然没有跑出理想的计划：
为什么这里排序消除了，但是查询反而比上面更慢了呢，仔细看计划就知道，idx_y_2索引虽然没有消除排序，但是他走的是范围扫描，然后再走的inlist迭代进行排序。在我们的认知当中排序是相当消耗资源的，所以我们总是尽量首先先消除排序，这本身是没大的问题，但是有时候我们还是需要根据实际情况来进行判断。另外，这个count stopkey的性能，我始终感觉还是没能发挥出它的性能出来，理想的结果应该是y表的A-rows为20，d表的Starts对应也该是20，如此才能体现出count stopkey的特性。
为了验证我对count stopkey的猜想，我在测试环境中通过复制dba_objects创建了两张表TEST3，TEST4，然后做了一些测试：
create table test3 as select * from dba_objects;
create table test4 as select * from dba_objects;
create index idx_test3_1 on test3(object_id);
测试代码尽量和业务代码保持一致：
select * from (
select x.*, rownum as rn from (
select /*+ leading(b) use_nl(b,a) */a.*
from test3 a, test4 b
where a.object_id=b.object_id
and b.object_type in('TABLE','INDEX')
order by b.object_name asc) x
)
where rownum <=10;
首先，我们来看看这个测试代码的执行计划（此时test4表没有建任何索引）：
TEST4表返回了5097行代码，然后TEST3表也被扫了5097次，这个计划里面仍然有count stopkey字样，但是我觉得实际上他的作用还是在整个Join完成之后的结果上，再取的10行记录，根本就没有发挥出它应有的威力。
下面我们来尝试消除sort order by:
create index idx_test4_2 on test4(object_name asc);
然后我们通过hint强制走idx_test4_2，看看执行计划：
没啥变化！强制走索引也没生效，这再次印证了我们前面的那个测试。那么这里为了防止Object_name列空值可能造成什么影响，我们对这个索引重建一下,给它加个常量：
create index idx_test4_2 on test4(object_name asc, 0);
然后再来试试查询，看看执行计划如何：
请仔细看，这才是我心中认为的真正理想的count stopkey的效果，TEST4表虽然走索引全扫之后得到15条结果，但是在回表的时候只回了10条记录，因此TEST3也只需要扫10次就可以了。如果我们将测试代码中的object_type条件改为 LIKE ‘IND%’，效果会更明显：
select * from (
select x.*, rownum as rn from (
select /*+ index(b idx_test4_2) leading(b) use_nl(b,a) */a.*
from test3 a, test4 b
where a.object_id=b.object_id
and b.object_type LIKE 'IND%'
order by b.object_name asc) x
)
where rownum <=10;
这个地方也许有人问，为什么建索引的要加个常量0呢，如果我的表的排序列它确实不存在空值的还需要加这个常量么？这个问题我无法以真理的角度给你答案，但是经过我反复的几十次的实验后得出的结论是，不管排序列是否存在空值，如果只在排序列上建单列索引而不加常量的话，这个索引大概率是完全没用的，即使强制走Hint都不行，除非这个排序列同时带有过滤条件，这样的话不加常量也是可以的。
现在我们回到初始的业务代码上，根据上面的测试结果，我们对业务代码的索引做如下改进：
create index idx_y_3 on mm_writeout_to(opdate asc,id, opflag);
select * from (select a.*, rownum rn
from (SELECT /*+ index(y idx_y_3) leading(y) use_nl(y,d) */ y.*
FROM mm_writeoutstatus_to d,
mm_writeout_to y
where d.id = y.id
and d.datasource in ('NVHLPCIS','VHLPCIS' )
and d.writeouttype = '1'
and (d.status = '00' or (d.STATUS = 'ZZ' and d.count < = 3))
and d.batch_id is null
and y.opflag in ('5', '6')
order by y.opdate asc) a
)
where rownum <= 20;
结果还是不理想，执行计划也消除了sort order by，但是count stopkey还是没有体现出来，y表返回了223K条记录，导致d表被也被循环了223k次，并且因为走的索引全扫，导致执行时间反而变成了1分多钟。这和我们的预期测试结果是不相符的：
这尼玛，简直没道理啊。
下面试试只查询y表，看看这个分页啥情况：
select * from (
select a.*, rownum rn
from (select *
from mm_writeout_to y
where y.opflag in ('5', '6')
order by y.opdate asc) a
)
where rownum <=10;
秒出，计划也很理想, count stopkey也体现出来了，虽然y表的过滤条件结果是223K条记录，但是这类它只扫描了20条就停止：
下面加入d表，但是只做连接，d表不带任何过滤条件：
select * from (
select a.*, rownum rn
from (select y.*
from mm_writeout_to y,
mm_writeoutstatus_to d
where d.id=y.id
and y.opflag in ('5', '6')
order by y.opdate asc) a
)
where rownum <=20;
秒出，并且y表只走了20条结果，符合我们的预期：
d表再加一个条件呢：
select * from (
select a.*, rownum rn
from (select y.*
from mm_writeout_to y,
mm_writeoutstatus_to d
where d.id=y.id
and d.writeouttype = '1'
and y.opflag in ('5', '6')
order by y.opdate asc) a
)
where rownum <=20;
秒杀！但是！！！count stopkey 性能开始下降，y表返回了166条：
继续添加d表条件：
select * from (
select a.*, rownum rn
from (select y.*
from mm_writeout_to y,
mm_writeoutstatus_to d
where d.id=y.id
and d.datasource in ('NVHLPCIS','VHLPCIS' )
and d.writeouttype = '1'
and y.opflag in ('5', '6')
order by y.opdate asc) a
)
where rownum <=20;
计划基本没变：
继续加：
select * from (select a.*, rownum as rn
from (SELECT /*+ index(y idx_y_3) leading(y) use_nl(y,d) */ y.*
FROM mm_writeoutstatus_to d,
mm_writeout_to y
where d.id = y.id
and d.datasource in ('NVHLPCIS','VHLPCIS' )
and d.writeouttype = '1'
and d.batch_id is null
and y.opflag in ('5', '6')
order by y.opdate asc) a
)
where rownum <= 20;
性能下降开始明显了：
这里就是我所发现的非常有意思的地方了。我查询了一下，按照业务SQL中d表的过滤条件，发现d表过滤出来的结果数据是0条。我仔细思考了1分钟，得出这么一个结论：
1）两表关联分页的时候，此时的count stopkey特性就是，驱动表每过滤出一条记录后，就会立即去和被驱动表进行关联；
2）如果关联不上，则用驱动表的下一条记录继续去做关联，一直循环关联到匹配成功的结果集数量满足rownumn的限制时，就会停止关联，然后立即返回，而不需要将所有的驱动表结果集都拿去关联一次；
3）但是，如果两个表满足匹配条件的结果集非常少的时候，这个关联匹配可能就需要执行很多次之后，才能得到想要的rownum结果集，甚至说两个表根本就没有满足条件的匹配项时，驱动表甚至需要将它所有的结果集都拿去匹配一次，即使最终结果集还是为0条。这也是为什么我们上面的测试中，随着d表的过滤条件增加，y表的返回记录会逐渐增多的原因，因为随着d表的过滤条件增加，满足匹配条件的结果集开始迅速减少，y表需要做匹配的次数越来越多。
（这个结论估计很多人老早就知道了，当我写到这个地方的时候，一种似曾相识的感觉非常强烈，我想应该是我以前就有过这个结论，只是因为时间太久了搞忘了。）
有了上面这些结论，那么这个分页语句就不能再按照套路来进行优化了。由于这个SQL情况的特殊性在于，两个表各自的过滤结果集都比较大，y表有22万多，d表有400多万，两表关联的字段id又是各自的主键列，但他们关联的结果集又非常非常的少，如果走NL，那么循环的次数会非常多，逻辑读会非常高，走hash的话物理读就是个大麻烦，得设法减少，走merge就更不能选，两表全扫加两表排序。
在做了几次实验之后，我最终得出了如下两种方法：
方法一：
create index idx_y_2 on mm_writeout_to(opflag,id,opdate asc);
create index idx_d_5 on mm_writeoutstatus_to(status,datasource,writeouttype,batch_id,id)；
with d as
(select id
from mm_writeoutstatus_to d
where d.datasource in ('NVHLPCIS', 'VHLPCIS')
and d.writeouttype = '1'
and d.batch_id is null
and (d.status = '00' or (d.STATUS = 'ZZ' and d.count < = 3))),
y as
(select id
FROM mm_writeout_to y
where y.opflag in ('5', '6')
order by y.opdate asc)
select * from (select a.*, rownum as rn
from (select y.*
from mm_writeout_to y
where y.id in (select y.id from d, y where d.id = y.id)) a
)
where rownum <= 20;
这个方法的大致原理就是让d表的过滤条件，通过走索引范围扫描进行union-all方式得到d表的ID, 然后让他和y表的ID做hash semi连接，最终执行计划显示平均执行时间为4秒，逻辑读为25004：
方法二：
create index idx_y_2 on mm_writeout_to(opflag,id,opdate asc);
select * from (select a.*, rownum as rn
from (SELECT /*+ index(y idx_y_2) leading(y) use_nl(y,d) */ y.*
FROM mm_writeoutstatus_to d,
mm_writeout_to y
where d.id = y.id
and d.datasource in ('NVHLPCIS','VHLPCIS' )
and d.writeouttype = '1'
and (d.status = '00' or (d.STATUS = 'ZZ' and d.count < = 3))
and d.batch_id is null
and y.opflag in ('5', '6')
order by y.opdate asc) a
)
where rownum <= 20;
这个方法的原理就是走的套路，两表关联结果集非常少的时候，走NL，小结果集y驱动大结果集d，同时因为y表的排序列没有过滤条件，如果要消除排序的话，它的索引就需要走索引全扫，因此这里我选择牺牲了sort order by，让索引走范围扫描，最终多次执行平均时间1秒左右，但逻辑读比较高，有195833，虽然循环次数很多，但是被驱动表都是走索引唯一扫描，所以速度比较快，同时相比最初始的100多W的逻辑读，这也算一个不小的降低幅度了：
方法一的执行时间主要花在了d表的UNION-ALL操作上面。同时这似乎又说明了另一个问题，逻辑读低的SQL执行时间未必就比逻辑读高的SQL快，这又是一个让我不太理解的地方了，后面慢慢继续研究吧，如果有了解其中原理的同学，请一定不吝赐教。
当然这个SQL没有优化的很彻底，在不考虑修改前端业务逻辑的情况下，我也没有再继续深究其它方式了，太费脑子。至少这两种方式我可以保证他们在数秒中内出结果，而不是时不时的需要跑个几分钟，并且单次IO可以从10G左右降低到数百兆的样子。
两种方式如何取舍，大概率他们会选择方法二吧，毕竟天下武功唯快不破，他们开心就好！
