适用于有order by的分页语句优化,且原sql写法如下的优化场景:
建议改写为下面格式:
并且,在order by需要排序字段上创建索引。
问题概述
(以下均为测试数据)
1. 优化的思路是什么?
1.1 搭建测试环境
注释:测试表emp1内有14条记录
注释:创建ename,deptno列上的索引
1.2 原sql的a-time执行计划
注释:sql的执行顺序为4->3->2->1,id=4 通过条件筛选从emp1里选出13行记录,在id=2这里对这13条记录排序,并在id=1的位置上通过rn >= 0 and rn <=10截取10行记录。1.3 改写后sql a-time执行计划
注释:优化前后的区别在于id=4这里,使用rownum<=10将结果集固化为10行,所以改写前后排序的记录从原来13行减少到10行。
2. 为什么排序不走索引IDX_MIX?
注释:empno和deptno列值都不为空。
注释:empno和deptno列值上都没有非空约束,测试发现即使使用hint index(a IDX_MIX)强制使用使用IDX_MIX,优化器也不走索引。
问题原因
1. 改写后sql执行效率提升的原因
(仅限于结果集b很大这一场景,b很小那么分不分页效率都不会太差)
原sql的逻辑会将b中满足条件的所有记录进行排序,最后截取一页数据,而改写后,sql仅仅对rownum截取的记录集进行排序,排序的内容少了,sql的执行效率也就提升了。
2. 不走索引的原因
测试发现当添加a.empno is not null and a.deptno is not null条件后,可以使用索引,如下图所示:
结论:因为null值可以参与排序,其次索引中不存空值,所以优化器认为从索引中取得的empno和deptno列值的排序结果,不体现表中实际的数据分布,所以不走索引。
解决方案
1. sql文本优化方案:
使用改写后的sql替换原sql;
2. 索引不走解决方案:
2.1 如果需要排序的列上没有非空约束,需要在要排序列上加is not null限制;
例如:
2.2 或者,创建含有常量的索引,并且使用hint index() 强制使用索引(避免少数优化器仍然不走索引的情况)。例如: