Rownum和OrderBy的执行顺序造成的影响
6月的某天下午,某用户反馈,医生站首页中病案附加项目的顺序乱了,影响医生的正常工作。
专注于为中小企业提供网站制作、成都网站设计服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业建昌免费做网站提供优质的服务。我们立足成都,凝聚了一批互联网行业人才,有力地推动了上千家企业的稳健成长,帮助中小企业通过网站建设实现规模扩充和转变。
从跟踪出来的SQL看到,执行得到的数据没有按预期的方式排序,但是,相同的SQL在测试库运行却能得到正常排序的结果。
院方近期没有做什么调整,怀疑跟一个月前我们转移历史数据之前的一系列性能优化调整有关。
经过一番分析,最终,通过重新收集该SQL涉及到的表的统计信息后,问题得到解决。
感觉有点儿不可思议吧?
统计信息收集不正确,还会影响产品功能的正常性?
有些事情的真相并不是我们看到那样,就像鸡血在磨心上点了几下之后,小孩的肚子就不痛了,如果你相信这样的巫术,可能会影响到你对很多事情的判断,有时,我们看到的并不一定是真相,倒不是因为对未知的知识缺乏敬畏,而是基本的逻辑推理问题,以及探寻真相的钻研精神。
统计信息收集确实是解决很多性能问题的一副灵药,但对这个问题来说,收集统计信息只是其中的一种临时解决办法,根本的原因还是SQL书写方面存在问题。
下面我们来一步一步解开事情的真相。
SQL语句如下:
select rownum as序号,编码,名称,内容 from病案项目 order by编码
多么简单的SQL,不像那些需要翻几页才能看得完整的变态SQL,这种简单的SQL除了理解起来更节约时间之外,还减少了很多可能导致验证结果偏差的干扰。
按开发人员的预期,先排序再对结果集进行编号,Rownum作为Oracle下特有的“伪列”,作用就是根据数据结果行数产生一个从1开始递增的行号。
有一定基础的同学可能一下就看出的不对之处:
Order by和Rownum在同一层次时,排序是最后执行的,先取行号自然就无法得到想要的按排序结果递增的行号,所以,如下所示,序号列就是“乱序”的。
那么,问题来了,既然这个SQL有问题,为什么在测试库运行又是正常的呢?
近期产品中的这条SQL也没有做过修改,为什么以前是正常的,现在却突然出现问题了呢?
没有骗你哟,用户发来了测试库上执行后结果正确的截图。
刚开始看到这个现象,也是很纳闷,会不会是数据库的什么参数影响了排序,像经常遇到的"_gby_hash_aggregation_enabled"这个参数对Group By排序的影响(后续可能会写一个这方面的案例),问题是这个SQL里没有Group by子句。
从数据库的基础理论方面想了想,影响排序的还有哪些因素呢?
如果是有索引,那么索引本身就是排了序的,读取数据时就不需要排序了,再用Rownum取值,是不是就可以取到预期的结果了呢?
也就是说,变相实现了先排序,后取序号的作用。
为了证实这一点,在公司的测试库上做了一个验证:
1. 相同的SQL,执行结果跟用户测试库的结果是一样的,序号正常排序。
2. 禁用主键“病案项目_PK”(以“编码”字段为索引)后,执行结果跟用户正式库的结果一样,序号排序就乱了,再现了问题。
alter table病案项目 disable constraint病案项目_PK;
恢复主键后,序号就正常排序了。
alter table病案项目 enable constraint病案项目_PK;
是不是用户生产库的主键被禁用了,索引丢了?
转移历史数据期间,的确会禁用一些约束,但是这张表并不是转出相关的表呀!而且转完数据后,我们恢复约束后也做过检查。
会不会是用户后期运行过程中,某种原因导致该索引无效了呢?
马上查询用户的生产库,主键是有效的,索引也是有效的。
眼看找到一条路,没想到走到底发现是个死胡同,不要懈气,既然问题再现了,原理也清楚了,顺着这条路,仔细找找,一定有出路。
在测试环境,对比分析一下,禁用主键(删除“编码”字段的索引)前后的执行计划。
对比发现了差异:
有索引时,执行计划包含” INDEX FULL SCAN”,没有排序操作。
没有索引时,执行计划包含” TABLE ACCESS FULL”,有排序操作“SORT ORDER BY”。
用户生产库的执行计划是什么呢?
一查询,结果跟我在这边测试环境下的执行计划一样,走了全表扫描。
为什么没有走索引全扫呢?
会不会是统计信息收集有问题,导致成本评估时,认为全表扫描的成本更低,所以选择了它呢。
在用户生产库收集了一下统计信息,结果就正常了,执行计划变成了” INDEX FULL SCAN”。
表的统计信息丢失的原因,不得而知。
其实解决问题的办法还有其他的,例如:通过Sql Profile加提示字指定索引。
既然是统计信息没有收集的问题,那是不是可以再现一下问题现象呢?
完全可以。
我们删除统计信息后来看看,是不是就再现用户生产库的场景了呢?
exec dbms_stats.delete_table_stats(ownname => 'ZLHIS',tabname => '病案项目');
再PLSQL中查看刚才那条SQL的执行计划,确实从索引全扫描变成了全表扫描。
执行SQL,查询一下数据,咦,怎么数据不是乱序的?
执行计划不是变了吗?
什么情况?
又到强调掌握数据库基础理论的重要性的时候了,如果只学习那些用得到的知识,书到用时方恨少,只有打开坚实的基础,才能在实战中临场应对。
清空一下共享池再看看。
alter system flush SHARED_POOL;
再次执行数据查询,哈哈,这次,“编码”字段终于乱序了,一阵窃喜。
数据乱了还要高兴,什么原因?因为它如你所愿,你看到了想要的结果。
为什么之前从PLSQL看到执行计划是全表扫描,但查询数据却跟索引全扫描的结果一样呢?
别忘了,PLSQL只是一个工具,它的F5查看执行计划的功能,不知道骗了多少人,坑了多少人。
其实你明白它查看执行计划的原理,就知道它不是真的想骗你,如果你有兴趣,可以通过10046去探个究竟,因为篇幅有限,这里就不再详述。
总结:
至此,这个问题搞清楚了,我们再来回看一下SQL,重新梳理一下:
select rownum as 序号,编码,名称,内容 from 病案项目 order by 编码
当Order by和Rownum在同一层次时,排序是最后执行的,先取行号再排序,这样就无法根据排序结果得到正确行号,如果能够避免排序,就能得到期望的结果。
当Order by中的字段是非空索引时(主键索引属于非空索引),如果统计信息收集正确,执行计划评估成本后,就会选择“索引全扫描”,由于索引本身是排了序的,就能避免排序。
当表的统计信息丢失,优化器在评估成本时,由于缺乏统计信息作为成本计算的依据,它就会选择“全表扫描”作为执行计划,然后再排序。
为了避免统计信息收集导致的这种问题,在各种用户环境下得到稳定的结果,这个SQL可以改为:
Select Rownum As 序号, 编码, 名称, 内容 From (Select 编码, 名称, 内容 From 病案项目 Order By 编码)
在子查询中先排序,外层查询中再取Rownum,类似的,当我们在写Rownum<5这类SQL时,也需要注意排序的问题,应该先在子查询中写排序,外面再限制返回行数。
思考:
为了加深对这个知识的理解,你可以试一下这两个SQL:
Select姓名 From人员表 Where Rownum < 2 Order By建档时间 Desc;
Select姓名 From人员表 Where Rownum < 2 Order By ID Desc;
ID是主键,顺序递增,建档时间最大的记录,其ID最大。
为什么两个SQL得到的数据不样呢?对比一下执行计划看看。
知识点的扩展应用:
利用这个案例中的知识点:排序字段如果与非空索引的字段顺序相同,则可以利用索引本身已排序的特性来避免排序,这在一些高并发的高频SQL中,对系统的整体性能提升将会起到非常重要的作用,因为排序操作对cpu消耗比较大,特别是那些大的、高频的排序。
特别提醒,两个重要条件:字段顺序相同,非空索引。
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
公众号:医信系统性能优化
主要写一些日常工作中性能优化方面的案例,包括SQL优化,数据结构设计优化,Oracle系统性能优化。
面向编写SQL及相关脚本的开发人员和技术支持人员,分享一些性能优化的经验。
对性能优化技术学习感兴趣的同学,欢迎订阅,共同学习,相互交流。
网页标题:Rownum和OrderBy的执行顺序造成的影响
标题URL:http://pwwzsj.com/article/geesic.html