一次巧妙的SQL优化改写

字数 8649阅读 3101评论 2赞 8

作者：海猫

------------

如果您看到下面的执行计划：
![微信图片_20171026090726.jpg](/home/attachment/201710/26/994339_150898015643868.jpg)
而其单次执行的逻辑读达到1.6亿个，执行时间将近1500秒的情况下：
![微信图片_20171026091014.png](/home/attachment/201710/26/994339_150898022822019.png)
你会如何优化呢？单从执行计划你能想到的优化方案在哪里呢？
今天我们就来看看我们的海猫是如何巧妙的优化这一语句，如何一步步将原本执行时间需要1500秒的SQL优化到1秒！！
# 问题来了！！
大清早的，睡眼惺忪的，正在美梦中，电话响了，问题来了……
客户简明扼要的描述了一套业务系统，正在执行跑批，现在负载一直在飙高，而且居高不下，希望我们予以解决。目前已经提前收集了一些awr等相关信息。并发来当时的zabbix的负载图，有点吓人，一片蓝蓝的，负载全满了：
![微信图片_20171026091159.jpg](/home/attachment/201710/26/994339_150898033751019.jpg)
这的确让客户有点捉急……
 
随后客户还提供了他隔一分钟查看的session会话情况：
![微信图片_20171026091224.png](/home/attachment/201710/26/994339_150898035811318.png)
间隔一分钟：
![微信图片_20171026091317.png](/home/attachment/201710/26/994339_150898041922419.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](/home/attachment/201710/26/994339_150898050199309.png)
从SQL text上来看，本身并不是长达数百行的SQL语句，看起来不会把我们唬到；至少从心理上，我们不会害怕去面对它了。对于SQL语句，我们都习惯了看三个部分。
第一. Select部分：这里是很简单，就是count(*)，期望得到一个总数。一般这种的count或者sum之类聚合函数作为最后的结果，心里大概有个预设，就是涉及的表的行数应该都是比较多的。不然求个总数或总和似乎意义不大；
第二. From部分：很明显的三表关联，做内连接。这个没有什么特殊的地方；
第三. Where部分：这里where条件部分稍微复杂一点点，总体是or连接起来的两个过滤条件。看样子都是对T表的过滤。

但是对T.END_的过滤部分有一个子查询：
![微信图片_20171026091527.png](/home/attachment/201710/26/994339_150898053893090.png)
###知识普及
在这里我们要特别留意这个子查询，这点对我们的优化很重要。而对于各种子查询特性的掌握，恰恰对于我们SQL优化有很大的帮助。据了解，其实很多DBA还是不太熟悉一些常见的子查询。我们知道一个子查询就是一个嵌入 SELECT 语句中的另一个 SQL 语句的子句，而它的位置很灵活。我们常见的，比如select部分有子查询：
![微信图片_20171026091657.png](/home/attachment/201710/26/994339_150898062816282.png)
我们称之为标量子查询。这种子查询在真正的业务SQL中是非常非常常见的；标量子查询有个最大的特点，叫做“单行单列”；就是说：这个子查询的结果集，一定是唯一一个值，不会同时出现多个值，而且标量子查询还是比较容易出现性能问题的，是我们优化常常需要关注的点。
From后面如果跟了子查询，我们习惯叫它内联视图。比如：
![微信图片_20171026091718.png](/home/attachment/201710/26/994339_150898064877191.png)
内联视图的在我们SQL优化中，经常考虑的是视图合并之类的问题，也是需要关注的。而where后面的子查询，一般可以总称之为相关子查询，这种一般会跟主表做关联的。常见就是=、exists、in、notexists、not in等等，比如：
![微信图片_20171026091853.png](/home/attachment/201710/26/994339_150898077983491.png)
此类查询有个特点就是，主查询中的行每被处理一次，子查询就要被求值一次。
这些基本的SQL知识都是我们优化SQL语句很基础的知识。

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

###02
了解这个SQL语句文本的前前后后一些知识后，为了优化这条SQL，我们还是习惯把SQL的awr报告拉出来分析下SQL实际的执行效率，这个客户已经提前准备好了。直接分析即可:
![微信图片_20171026092009.png](/home/attachment/201710/26/994339_150898081959501.png)
从两个快照间的sql报告来看，该SQL目前执行了两次，平均每次执行消耗1400多秒，差不多20多分钟左右，而每次返回的是就只有1行数据。每次逻辑读是167340498，这个是多少……我就不扳手指头了……这个数量的确让人有点咋舌呀！看到这个报告，明显感觉这个SQL性能差到令人发指了。让人情何以堪？！这种SQL再不优化，数据库都要被拖垮了。

###03
查来查去，查了大半天，那么接下来就是怎么优化了？光说不练假把式，懂得再多，优化不了，那也是白扯。既然要优化，那我们还是要先找到SQL语句执行慢，具体的问题点在哪里，是走全表慢了吗？索引建的不合理？回表太多？rows估算不对？这些就是我们首先要确定的问题。这个时候，我们还是需要先把执行计划摆出来：
![微信图片_20171026092046.jpg](/home/attachment/201710/26/994339_150898085665851.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](/home/attachment/201710/26/994339_150898087857797.png)
比如这里我们看到是一个叫IDX_TASKINST_TSK的索引逻辑读很高。而一对比执行计划，就是执行计划的下面部分：
![微信图片_20171026092126.png](/home/attachment/201710/26/994339_150898091770249.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](/home/attachment/201710/26/994339_150898095911978.png)

###04
那么继续提问，是什么原因又导致扫描这个索引次数太多呢？为了把问题分析的更加透彻，我们可以做一项拆解工作。
根据执行计划前面的*号，再结合谓词信息（此处需要划重点，温馨提示，优化SQL一定要学会看执行计划了的*号和谓词信息），关注下ID=6的部分，可以得到如下过滤条件：
![微信图片_20171026092309.png](/home/attachment/201710/26/994339_150898099850535.png)
再关注下ID=10的部分：
![微信图片_20171026092328.png](/home/attachment/201710/26/994339_150898101837202.png)
有了上面两部分信息，再结合SQL原始文本，我们发现其实这个执行计划分成上下两部分，而分水岭就是SQL文本的OR关联词（其实就对应了执行计划了的CONCATENATION关键字）：
![微信图片_20171026092347.png](/home/attachment/201710/26/994339_150898103739972.png)
从执行计划上来看大概可以这样区分：
![微信图片_20171026092410.jpg](/home/attachment/201710/26/994339_150898106290141.jpg)
经常优化sql的就比较熟悉，一般or可以等价改写成union方式的，这里我们根据这个可以等价去把整个SQL拆成两部分：
第一部分：
![微信图片_20171026092443.png](/home/attachment/201710/26/994339_150898109368833.png)
第二部分：
![微信图片_20171026092510.png](/home/attachment/201710/26/994339_150898111978356.png)

实际拆解完之后，真实的跑来下，让我有种预料之中的感觉，么么哒了。。。。
那就是，第一部分一跑，嗖嗖的秒杀了。
但是第二部分，就………………………………
你懂的，一个漫长的等待…………………………
所以我果断终止了，这还跑啥，明摆着第二部分是最佳嫌疑犯啦。它是我们执行计划的下面部分，也是一开始我们猜想的子查询的部分；同样是我们通过awr报告大概确认问题点的部分。那么这个时候，小编心理想的是：如果我们把第二部分优化，理论上整个SQL语句就可以搞定了。这下总算把问题缩小到我们可以承受的范围了。
![微信图片_20171026092603.png](/home/attachment/201710/26/994339_150898117340085.png)
这种filter其实比较常见，一般是在exists或者in子查询语句里遇得到。这里的filter也比较类似，而且我们知道filter就是“优化版”的嵌套循环（这个是很重要的知识点，私下可以各种实验验证下）。那就是说，如果嵌套循环的驱动表返回数据量很大的话，被驱动表的部分就要被扫描N次，当然N是我们无法忍受的次数咯。

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

首先，驱动表部分，可能想到的就是ID=15部分：
![微信图片_20171026092623.png](/home/attachment/201710/26/994339_150898119465618.png)
看看是不是需要搞个索引，意思是T表的END_列是否可以加个索引，这个我还真看了下统计信息，基本上加个索引是没有问题的，一定程度上可以提高下效率。但是我们的案例，真的提高的只是微乎其微的。真正的80多万行的表，单纯全表扫，又能带来多大的性能问题？

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

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

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

必须有！！这种的改写SQL一般是上策。而且改写SQL可以很快得到验证。虽然不知道开发工程师认不认账我们DBA的改写，但是我们DBA必须具有改写SQL的基本功，至于采不采纳，那就管不了那么多了。先把SQL调的飞起来再说。事实会说明一切的。实在没招，想必他们也愿意重新调整SQL的。
那么我就斟酌如何去调整这个SQL语句。正如前面的拆解所提及的，这个SQL真正需要调整的是子查询部分，也就是类似这样的一段SQL语句：
![微信图片_20171026092646.png](/home/attachment/201710/26/994339_150898121581880.png)
这段SQL单纯这样看，感觉不太好懂，但是别着急。我们看下T.END_部分，其实就是主表返回一行，然后max子查询部分会去执行一次的。这个其实就是类似一个标量子查询，所以可以做一个稍微的调整，这样感觉就不一样了：
![微信图片_20171026092706.png](/home/attachment/201710/26/994339_150898123776513.png)
这样一看原来的子查询就是一个标量子查询，看下执行计划：
![微信图片_20171026092728.png](/home/attachment/201710/26/994339_150898125891097.png)
其实就可以把它看成标量的，只是作为过滤而已，跟标量雷同。那么既然这样，就可以考虑用left join去改写这个标量。

但是max子查询部分考虑再三，其实可以单独用一个with as代替：
可以先如下调整：
![微信图片_20171026092824.png](/home/attachment/201710/26/994339_150898131497411.png)
然后再去跟D表进行关联的。那么来回倒腾，最终得到了一个改写后的SQL语句，算是基本上成型了：
![微信图片_20171026092843.png](/home/attachment/201710/26/994339_150898133477996.png)
不着急，这几步的改写，或许你需要反反复复酝酿几遍……
死掉数以亿计的脑细胞……
死掉数以亿计的脑细胞……
死掉数以亿计的脑细胞……
酝酿完毕了，继续看上面待确定的部分，其实就是如何把where的or条件加到一起，再去count（*），这个倒不是特别难，用sum+case when去完成就行：
![微信图片_20171026092940.png](/home/attachment/201710/26/994339_150898139042048.png)
这里的意思就是满足or的任何一个条件的返回1，否则返回null，这样一统计，别看是换成了sum，其实就是count（*）的结果，毋庸置疑的是，这里返回null的肯定不会算在count里的（这里用sum来改写count的部分，个人感觉还是比较取巧的）。如此来来去去，最终调整好的SQL语句长成这样的：
![微信图片_20171026093000.png](/home/attachment/201710/26/994339_150898141113321.png)
是不是有点面目全非的感觉，不惊怀疑，这样的改写对吗？
# 完美的验证
我们DBA改写完之后，第一时间不是稀里糊涂去跑一把，而是看下执行计划再说：
![微信图片_20171026093044.png](/home/attachment/201710/26/994339_150898145680415.png)
从执行计划看，问题应该不大，没有什么可以再调整了。先不管改的对不对，满怀希望的尝试跑了一把再说。
哈哈哈哈哈……

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

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

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

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

Oracle数据库 sql优化

著作权归作者所有

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