sql优化案例:分页查询(一)

该案例来自西区某客户,该业务应该是停车场出入系统,五一高峰期出现了比较严重的性能问题。通过查询发现出现性能问题的sql是一个分页查询sql。

sql文本为:

执行计划和性能统计:

据业务人员反应,该sql是一个没有绑定变量的sql,PARKIN_TIME是页面的输入条件,当选取的时间范围越大,该sql性能消耗越大,查询时间越长。每一天的数据量大概在35w,当时间范围超过1天时,基本要等很久页面才会返回数据。

关于分页查询的优化,还是有套路的,而且个人认为所有数据库的分页查询优化思路都是一样的。一个完美的分页查询sql的执行计划,应该是一套非阻塞操作,其手段就是利用排序字段列的表对应的索引(免排序)作为驱动+NL连接(合适的索引实现精确匹配)+STOPKEY。基本无论传入什么输入条件,结果都应该是秒出的。很明显本案例的执行计划不是一个优秀的分页执行计划。

首先提一下执行计划的非阻塞操作。非阻塞操作其实就是当查询一个块的数据,不用等待其操作完成就可以按执行计划继续往下走或者返回客户端。比如全表扫描、索引范围扫描、NL连接都是非阻塞操作。常见的阻塞操作比如排序、HASH连接、VIEW关键字等等。对于分页查询的优化思路其实就是避免阻塞操作,并且尽量保证每一条记录的驱动都能最终返回(少做无用功)。

回到本案例,分析分页查询的第一个阻塞操作点order by部分:

PARKIN_TIME和createtime来自表p_park_in,PARKOUT_TIME来自p_park_out,当排序字段来自多个表时,是没法通过索引去消除order by的阻塞操作的。发现第一个问题之后,立刻与开发沟通。

给出的建议有两种方法:

  • 是否能去掉PARKOUT_TIME的order by,改为

  • 如果不能去掉,可以改写sql分页部分,order by ORDER BY PARKIN_TIME DESC, createtime DESC之后,rownum<=10改成rownum<=10000,再根据这10000条记录做ORDER BY PARKIN_TIME DESC, PARKOUT_TIME DESC, createtime DESC,相当于排序了两次,应该没有人去翻页1000次。

最终开发认为PARKOUT_TIME的排序是可以去掉的,那么第一个阻塞点就消除了。结合谓词信息:

第一个优化建议:

创建索引PARKIN_TIME、CREATE_TIME组合索引。由该索引开始驱动,同时避免了排序阻塞操作(SORT ORDER BY STOPKEY)。

仔细分析执行计划,第二个阻塞操作点

ID=7的view关键字,说明VIEW下的连接需要关联查询完成当作一个整体才能与ID=6做HASH外连接。

为何会出现VIEW呢?为什么oracle不能做view merge的查询转换呢?view merge相关概念参考之前的一篇文章。

Query Transformation-视图篇

仔细分析sql文本发现:

与表p_place_door关联时,关联字段有表P_PARK_IN的PLACE_ID,还有表P_PARK_OUT的PARKOUT_EXIT_NO。如果oracle做了view merge,那么会导致d表同时与a和b都外连接,这在oracle中是不允许的,会报出ORA-01417错误。所以CBO不会对这样的view做merge转换。

那么要消除VIEW必须要CBO去把这个view merge掉,解决办法只有d表要么都与P_PARK_IN的字段关联,要么都与P_PARK_OUT的字段关联。

跟开发沟通之后P_PARK_OUT表的PARKOUT_PLACEID可以替代P_PARK_IN的PLACE_ID字段。

最终sql改写为:

消除了阻塞操作点之后,需要查看后续关联的对象上是否有对应的索引,保证NL连接能使用上,这里的索引都可以不考虑其选择性,其主要目的是保证精确匹配。

后续NL连接关联索引都有。那么看看优化效果,输入了一个月的时间范围,直接秒出结果。

优化效果非常明显,无论输入多少时间范围都是秒出结果。当一个分页查询的执行计划都是只有COUNT STOPKEY+NL连接+并且谓词都是access(没有filter或者filter的过滤条件过滤性微乎其微),就是一个性能非常好的分页查询执行计划。

 

 

 

此条目发表在Oracle, Oralce performance分类目录,贴了, 标签。将固定链接加入收藏夹。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注