DBA小y
作者DBA小y2017-10-26 09:32
系统工程师, 中亦科技

一次巧妙的SQL优化改写

字数 6656阅读 3076评论 2赞 8

作者:海猫


如果您看到下面的执行计划:
微信图片_20171026090726.jpg

微信图片_20171026090726.jpg

而其单次执行的逻辑读达到1.6亿个,执行时间将近1500秒的情况下:
微信图片_20171026091014.png
微信图片_20171026091014.png

你会如何优化呢?单从执行计划你能想到的优化方案在哪里呢?
今天我们就来看看我们的海猫是如何巧妙的优化这一语句,如何一步步将原本执行时间需要1500秒的SQL优化到1秒!!

问题来了!!

大清早的,睡眼惺忪的,正在美梦中,电话响了,问题来了……
客户简明扼要的描述了一套业务系统,正在执行跑批,现在负载一直在飙高,而且居高不下,希望我们予以解决。目前已经提前收集了一些awr等相关信息。并发来当时的zabbix的负载图,有点吓人,一片蓝蓝的,负载全满了:
微信图片_20171026091159.jpg

微信图片_20171026091159.jpg

这的确让客户有点捉急……

随后客户还提供了他隔一分钟查看的session会话情况:
微信图片_20171026091224.png

微信图片_20171026091224.png

间隔一分钟:
微信图片_20171026091317.png
微信图片_20171026091317.png

简单确认问题

看了客户提供的几张session的截图,我了然于心,完全淡定了。从现象看这是典型的CBC(cache buffers chains)问题,也可以说是热点块的问题。而且客户已经找到导致问题的SQL ID,那么很简单,这个SQL就是罪魁祸首。

根据以往的经验,这种情况,基本上SQL优化掉,问题就迎刃而解了。但是为了保险起见,我还是持续观察了下v$session的情况,观察了一段等待事件latch: cache buffers chains的p1,p2,p3的情况,确认SQL并没有hang住,只是跑的比较慢,一直在扫描某个对象的数据块。

这种情况,一般就不用纠结了,很快定位是SQL语句性能的问题,而且需要更进一步分析SQL执行的情况。

开始分析问题

从目前诊断来看,问题已经很明朗。就是sql语句导致的性能问题,从而表现出来资源消耗很高、业务反应很慢的情况。那么,既然问题已经确定了,作为DBA的你下一步会怎么做呢?

基本上两种情况:
(1)作为DBA我已经把问题诊断的差不多,就是业务sql性能导致的问题,可以丢给业务部门,让开发一个劲去优化就好了;所以感觉接下来似乎就是开发的事情,跟我们DBA没有一点关系,so easy有木有!!
(2)就是DBA本身就有能力去把问题SQL搞定,提供强有效的优化方案,让SQL执行的飞快,最终在我们DBA这一关,就把问题全部搞定。如果是您,会怎么选择呢?
其实个人还是觉得优化SQL应该还是我们自己的事情,丢给开发,一来显得我们自身太弱,二来或许也会阻挡我们更深入的认识oracle,技术上会一直有缺口。再说,一个相当经典的优化案例,既可以训练自己的优化,又可以帮帮客户,当然如果可以的话,也可以装装逼,呵呵。。。一举N得,何乐而不为?
OK,那么可以开始优化SQL了。

01

从最基本的开始,首先要大概看懂SQL语句简简单单表达出来的意思,无论是逻辑上或者业务上,我们总要做到心里有数,当前的这个SQL语句:
微信图片_20171026091451.png

微信图片_20171026091451.png

从SQL text上来看,本身并不是长达数百行的SQL语句,看起来不会把我们唬到;至少从心理上,我们不会害怕去面对它了。对于SQL语句,我们都习惯了看三个部分。
第一. Select部分:这里是很简单,就是count(*),期望得到一个总数。一般这种的count或者sum之类聚合函数作为最后的结果,心里大概有个预设,就是涉及的表的行数应该都是比较多的。不然求个总数或总和似乎意义不大;
第二. From部分:很明显的三表关联,做内连接。这个没有什么特殊的地方;
第三. Where部分:这里where条件部分稍微复杂一点点,总体是or连接起来的两个过滤条件。看样子都是对T表的过滤。

但是对T.END_的过滤部分有一个子查询:
微信图片_20171026091527.png

微信图片_20171026091527.png

知识普及

在这里我们要特别留意这个子查询,这点对我们的优化很重要。而对于各种子查询特性的掌握,恰恰对于我们SQL优化有很大的帮助。据了解,其实很多DBA还是不太熟悉一些常见的子查询。我们知道一个子查询就是一个嵌入 SELECT 语句中的另一个 SQL 语句的子句,而它的位置很灵活。我们常见的,比如select部分有子查询:
微信图片_20171026091657.png

微信图片_20171026091657.png

我们称之为标量子查询。这种子查询在真正的业务SQL中是非常非常常见的;标量子查询有个最大的特点,叫做“单行单列”;就是说:这个子查询的结果集,一定是唯一一个值,不会同时出现多个值,而且标量子查询还是比较容易出现性能问题的,是我们优化常常需要关注的点。
From后面如果跟了子查询,我们习惯叫它内联视图。比如:
微信图片_20171026091718.png
微信图片_20171026091718.png

内联视图的在我们SQL优化中,经常考虑的是视图合并之类的问题,也是需要关注的。而where后面的子查询,一般可以总称之为相关子查询,这种一般会跟主表做关联的。常见就是=、exists、in、notexists、not in等等,比如:
微信图片_20171026091853.png
微信图片_20171026091853.png

此类查询有个特点就是,主查询中的行每被处理一次,子查询就要被求值一次。
这些基本的SQL知识都是我们优化SQL语句很基础的知识。

回归到我们的问题SQL上来,很显然这里更像是一个相关子查询。这个子查询的结果不会出现在我们最终的select结果集里,但是它对我们的最终的结果起到一定的过滤作用,所以同样重要。

02

了解这个SQL语句文本的前前后后一些知识后,为了优化这条SQL,我们还是习惯把SQL的awr报告拉出来分析下SQL实际的执行效率,这个客户已经提前准备好了。直接分析即可:
微信图片_20171026092009.png

微信图片_20171026092009.png

从两个快照间的sql报告来看,该SQL目前执行了两次,平均每次执行消耗1400多秒,差不多20多分钟左右,而每次返回的是就只有1行数据。每次逻辑读是167340498,这个是多少……我就不扳手指头了……这个数量的确让人有点咋舌呀!看到这个报告,明显感觉这个SQL性能差到令人发指了。让人情何以堪?!这种SQL再不优化,数据库都要被拖垮了。

03

查来查去,查了大半天,那么接下来就是怎么优化了?光说不练假把式,懂得再多,优化不了,那也是白扯。既然要优化,那我们还是要先找到SQL语句执行慢,具体的问题点在哪里,是走全表慢了吗?索引建的不合理?回表太多?rows估算不对?这些就是我们首先要确定的问题。这个时候,我们还是需要先把执行计划摆出来:
微信图片_20171026092046.jpg

微信图片_20171026092046.jpg

传说中,一类高手拿到SQL文本,嗖嗖就能判断出问题出在哪里,另外一类高手就是拿到执行计划,啪啪就能判断出问题的症结所在之处。到底是怎么看的呢?这就有很多办法,比如问题时刻,你直接看下v$session里的ROW_WAIT_OBJ#字段或者ash里的current_obj#字段,然后跟dba_objects一关联,就知道是什么对象一直被扫描,这样我们结合下执行计划就知道问题大概出在哪里了。当然如果你有足够的耐心,执行下alter session set statistics_level=all,接着实际跑下sql语句。基本上都比较容易确定问题点啦,方法真的很多很多。这里,我们就用比较熟悉的awr来定位下问题。看了下awr报告里的segment statistics部分,基本上逻辑读一看,占比多的那个segment,其实就大概知道执行计划里出问题的部分是哪里了:
微信图片_20171026092106.png
微信图片_20171026092106.png

比如这里我们看到是一个叫IDX_TASKINST_TSK的索引逻辑读很高。而一对比执行计划,就是执行计划的下面部分:
微信图片_20171026092126.png
微信图片_20171026092126.png

看到这里,很多老司机,应该心里马上就清楚真正问题的部分。这里我们目前可以得出的结论是:索引IDX_TASKINST_TSK被扫描太多次,索引被扫描太多次,回表的部分同样也不少的。而且这里还是个跳跃扫,比较坑人的。。。。。。
好的,问题的切入点终于找到了,也算是问题的根源找到了。但是还是那个问题,这种SQL怎么优化呢???????????????
不是有全表扫描嘛,加个索引试试?
不是有索引跳跃扫嘛,重新建个更有效的索引?
还是???
其实我们知道这个sql语句就三张表, JBPM_TESTTEST1、JBPM_TESTTEST2和CCFORM_TESTTEST3。不过这里有一点需要说明的是,你会发现怎么执行计划里压根没看到跟CCFORM_TESTTEST3这个表相关的信息,而是冒出一个表 CCFORM_TEST1。

那么是不是第一时间想到有可能是视图……我觉得大部分人会这样想,至少我会的其实它真的就是一个简单的查询了下表CCFORM_TEST1的视图,并不复杂。从表的大小来看,最大也就是JBPM_TESTTEST1,才88万多行,其他都是十多万行。如果这个时候,我们把关注点放在全部扫描,还是索引扫描上,势必会偏离SQL的最真实的问题点。

刚才我们已经判断是执行计划里ID=18和ID=19部分被扫描的次数太多导致最终逻辑读太高:
微信图片_20171026092228.png

微信图片_20171026092228.png

04

那么继续提问,是什么原因又导致扫描这个索引次数太多呢?为了把问题分析的更加透彻,我们可以做一项拆解工作。
根据执行计划前面的号,再结合谓词信息(此处需要划重点,温馨提示,优化SQL一定要学会看执行计划了的号和谓词信息),关注下ID=6的部分,可以得到如下过滤条件:
微信图片_20171026092309.png

微信图片_20171026092309.png

再关注下ID=10的部分:
微信图片_20171026092328.png
微信图片_20171026092328.png

有了上面两部分信息,再结合SQL原始文本,我们发现其实这个执行计划分成上下两部分,而分水岭就是SQL文本的OR关联词(其实就对应了执行计划了的CONCATENATION关键字):
微信图片_20171026092347.png
微信图片_20171026092347.png

从执行计划上来看大概可以这样区分:
微信图片_20171026092410.jpg
微信图片_20171026092410.jpg

经常优化sql的就比较熟悉,一般or可以等价改写成union方式的,这里我们根据这个可以等价去把整个SQL拆成两部分:
第一部分:
微信图片_20171026092443.png
微信图片_20171026092443.png

第二部分:
微信图片_20171026092510.png
微信图片_20171026092510.png

实际拆解完之后,真实的跑来下,让我有种预料之中的感觉,么么哒了。。。。
那就是,第一部分一跑,嗖嗖的秒杀了。
但是第二部分,就………………………………
你懂的,一个漫长的等待…………………………
所以我果断终止了,这还跑啥,明摆着第二部分是最佳嫌疑犯啦。它是我们执行计划的下面部分,也是一开始我们猜想的子查询的部分;同样是我们通过awr报告大概确认问题点的部分。那么这个时候,小编心理想的是:如果我们把第二部分优化,理论上整个SQL语句就可以搞定了。这下总算把问题缩小到我们可以承受的范围了。
微信图片_20171026092603.png

微信图片_20171026092603.png

这种filter其实比较常见,一般是在exists或者in子查询语句里遇得到。这里的filter也比较类似,而且我们知道filter就是“优化版”的嵌套循环(这个是很重要的知识点,私下可以各种实验验证下)。那就是说,如果嵌套循环的驱动表返回数据量很大的话,被驱动表的部分就要被扫描N次,当然N是我们无法忍受的次数咯。

怎么让这个filter快起来呢?如果从最传统的建索引等优化扫描方式的角度出发,无非就是折腾下驱动表部分和被驱动表部分。

首先,驱动表部分,可能想到的就是ID=15部分:
微信图片_20171026092623.png

微信图片_20171026092623.png

看看是不是需要搞个索引,意思是T表的END_列是否可以加个索引,这个我还真看了下统计信息,基本上加个索引是没有问题的,一定程度上可以提高下效率。但是我们的案例,真的提高的只是微乎其微的。真正的80多万行的表,单纯全表扫,又能带来多大的性能问题?

接下来,被驱动表部分走了索引的跳跃扫描,因为没有PROCINST_作为前导列的索引,而且这里跳跃之后还回表;鉴于目前的情况,我看下列PROCINST_的统计信息,可以在PROCINST_上建个索引,甚至直接用(PROCINST_,END_)建个组合索引似乎是蛮不错的选择。

这样一路索引建下来,你是不是有种感觉:根本问题并没有解决?

因为filter的驱动表数据量还摆在那里,被驱动表该扫描N次,还是要扫的,就算建了索引,也是杯水车薪,并不能解决根本问题,至少现在我是这么认为的。再说,客户生产环境,也不是说让你建索引就建的,有的还的申请批准。那还有其他招数?

必须有!!这种的改写SQL一般是上策。而且改写SQL可以很快得到验证。虽然不知道开发工程师认不认账我们DBA的改写,但是我们DBA必须具有改写SQL的基本功,至于采不采纳,那就管不了那么多了。先把SQL调的飞起来再说。事实会说明一切的。实在没招,想必他们也愿意重新调整SQL的。
那么我就斟酌如何去调整这个SQL语句。正如前面的拆解所提及的,这个SQL真正需要调整的是子查询部分,也就是类似这样的一段SQL语句:
微信图片_20171026092646.png

微信图片_20171026092646.png

这段SQL单纯这样看,感觉不太好懂,但是别着急。我们看下T.END_部分,其实就是主表返回一行,然后max子查询部分会去执行一次的。这个其实就是类似一个标量子查询,所以可以做一个稍微的调整,这样感觉就不一样了:
微信图片_20171026092706.png
微信图片_20171026092706.png

这样一看原来的子查询就是一个标量子查询,看下执行计划:
微信图片_20171026092728.png
微信图片_20171026092728.png

其实就可以把它看成标量的,只是作为过滤而已,跟标量雷同。那么既然这样,就可以考虑用left join去改写这个标量。

但是max子查询部分考虑再三,其实可以单独用一个with as代替:
可以先如下调整:
微信图片_20171026092824.png

微信图片_20171026092824.png

然后再去跟D表进行关联的。那么来回倒腾,最终得到了一个改写后的SQL语句,算是基本上成型了:
微信图片_20171026092843.png
微信图片_20171026092843.png

不着急,这几步的改写,或许你需要反反复复酝酿几遍……
死掉数以亿计的脑细胞……
死掉数以亿计的脑细胞……
死掉数以亿计的脑细胞……
酝酿完毕了,继续看上面待确定的部分,其实就是如何把where的or条件加到一起,再去count(*),这个倒不是特别难,用sum+case when去完成就行:
微信图片_20171026092940.png
微信图片_20171026092940.png

这里的意思就是满足or的任何一个条件的返回1,否则返回null,这样一统计,别看是换成了sum,其实就是count(*)的结果,毋庸置疑的是,这里返回null的肯定不会算在count里的(这里用sum来改写count的部分,个人感觉还是比较取巧的)。如此来来去去,最终调整好的SQL语句长成这样的:
微信图片_20171026093000.png
微信图片_20171026093000.png

是不是有点面目全非的感觉,不惊怀疑,这样的改写对吗?

完美的验证

我们DBA改写完之后,第一时间不是稀里糊涂去跑一把,而是看下执行计划再说:
微信图片_20171026093044.png

微信图片_20171026093044.png

从执行计划看,问题应该不大,没有什么可以再调整了。先不管改的对不对,满怀希望的尝试跑了一把再说。
哈哈哈哈哈……

返回1行记录……1.86秒解决战斗……

但是千万不要高兴太早,这种改写对不对还不知道,还是要去验证下,不然就是自欺欺人。没办法,只能小心翼翼的跑下原始SQL,看看到底跑多久,到底返回多少行数据,生产环境一边跑着SQL,一边我战战兢兢地看着负载情况,就怕又飚高了。漫长的等待……

功夫不负有心人,终于等来了姗姗来迟的结果,结果完全一致,总共花费了23.49分钟。这个等待是漫长的,但是结果却还是蛮喜人的噢。
目前至少结果一样了,而且时间上大大缩短。当然对于这种的改写很多时候也不能太乐观,因为最好跟开发沟通这样的改写是否符合业务需求;毕竟他们才是业务开发者,比我们知道的更多,但是对数据库的了解,我们就更胜一筹了。

基本上根据经验来讲,这样的改写应该是没有问题了;至于开发买不买账,那就另当别论咯,反正至少完美自己可以从中体验了优化带来的煎熬、挣扎,还有乐趣,最终也可以提供给客户一份满意的答卷。

如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!

8

添加新评论2 条评论

1301664724qq1301664724qqIT顾问, IBM
2017-12-14 15:55
确实不错呀,很详细,谢谢分享!!!
wuwenpinwuwenpin软件开发工程师, 南京
2017-10-26 14:09
真的不错。
Ctrl+Enter 发表

作者其他文章

相关文章

相关问题

相关资料

X社区推广