一次巧妙的SQL优化改写--技术人生系列第四十期-我和数据中心的故事

来源：这里教程网时间：2026-03-03 12:34:08 作者：

不知不觉技术人生系列已经来到了第四十期，前面我们分享了各种精彩案例，在与众多读者的交流中发现许多读者有对SQL优化的案例的需求，通常我们认为，SQL优化涉及的信息会比较多，比如需要各种表的数据分布信息，索引信息，统计信息等，有时还跟业务逻辑紧密相关，所以SQL优化的案例在我们技术人生系列的分享中出现的次数较少；不过今天，我们要分享的SQL优化案例能充分体现SQL优化的技巧，忍不住要将精彩奉献给大家 …

如果您看到下面的执行计划：

而其单次执行的逻辑读达到1.6亿个，执行时间将近1500秒的情况下：

你会如何优化呢？单从执行计划你能想到的优化方案在哪里呢？

今天我们就来看看我们的海猫是如何巧妙的优化这一语句，如何一步步将原本执行时间需要 1500秒的SQL优化到1秒 ！！

问题来了！！

大清早的，睡眼惺忪的，正在美梦中，电话响了，问题来了 ……

客户简明扼要的描述了一套业务系统，正在执行跑批，现在负载一直在飙高，而且居高不下，希望我们予以解决。目前已经提前收集了一些 awr 等相关信息。并发来当时的 zabbix 的负载图，有点吓人，一片蓝蓝的，负载全满了：

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

随后客户还提供了他隔一分钟查看的 session 会话情况：

间隔一分钟：

简单确认问题

看了客户提供的几张 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了。

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

从 SQL text 上来看，本身并不是长达数百行的 SQL 语句，看起来不会把我们唬到；至少从心理上，我们不会害怕去面对它了。对于 SQL 语句，我们都习惯了看 三个部分 。

第一. Select 部分：这里是很简单，就是 count(*) ，期望得到一个总数。一般这种的 count 或者 sum 之类聚合函数作为最后的结果，心里大概有个预设，就是涉及的表的行数应该都是比较多的。不然求个总数或总和似乎意义不大；

第二. From 部分：很明显的三表关联，做内连接。这个没有什么特殊的地方；

第三. Where 部分：这里 where 条件部分稍微复杂一点点，总体是 or 连接起来的两个过滤条件。看样子都是对 T 表的过滤。

但是对 T.END_ 的过滤部分有一个子查询：

知识普及

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

我们称之为标量子查询。这种子查询在真正的业务 SQL 中是非常非常常见的；标量子查询有个最大的特点，叫做“单行单列”；就是说：这个子查询的结果集，一定是唯一一个值，不会同时出现多个值，而且标量子查询还是比较容易出现性能问题的，是我们优化常常需要关注的点。

From 后面如果跟了子查询，我们习惯叫它内联视图。比如：

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

此类查询有个特点就是，主查询中的行每被处理一次，子查询就要被求值一次。

这些基本的 SQL 知识都是我们优化 SQL 语句很基础的知识。

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

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

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

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

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

比如这里我们看到是一个叫 IDX_TASKINST_TSK 的索引逻辑读很高。而一对比执行计划，就是执行计划的下面部分：

看到这里，很多老司机，应该心里马上就清楚真正问题的部分。这里我们目前可以得出的结论是：索引 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 部分被扫描的次数太多导致最终逻辑读太高：

那么继续提问，是什么原因又导致扫描这个索引次数太多呢？为了把问题分析的更加透彻，我们可以做一项拆解工作。

根据执行计划前面的 * 号，再结合谓词信息（此处需要划重点，温馨提示，优化 SQL 一定要学会看执行计划了的 * 号和谓词信息），关注下 ID=6 的部分，可以得到如下过滤条件：

再关注下 ID=10 的部分：

有了上面两部分信息，再结合 SQL 原始文本，我们发现其实这个执行计划分成上下两部分，而分水岭就是 SQL 文本的 OR 关联词（其实就对应了执行计划了的 CONCATENATION 关键字）：

从执行计划上来看大概可以这样区分：

经常优化 sql 的就比较熟悉，一般 or 可以等价改写成 union 方式的，这里我们根据这个可以等价去把整个 SQL 拆成两部分：

第一部分：

第二部分：

实际拆解完之后，真实的跑来下，让我有种预料之中的感觉，么么哒了。。。。

那就是，第一部分一跑，嗖嗖的秒杀了。

但是第二部分，就………………………………

你懂的，一个漫长的等待…………………………

所以我果断终止了，这还跑啥，明摆着第二部分是最佳嫌疑犯啦。它是我们执行计划的下面部分，也是一开始我们猜想的子查询的部分；同样是我们通过 awr 报告大概确认问题点的部分。那么这个时候，小编心理想的是：如果我们把第二部分优化，理论上整个 SQL 语句就可以搞定了。这下总算把问题缩小到我们可以承受的范围了。

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

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

首先，驱动表部分，可能想到的就是 ID=15 部分：

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

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

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

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

必须有！！这种的改写 SQL 一般是上策。而且改写 SQL 可以很快得到验证。虽然不知道开发工程师认不认账我们 DBA 的改写，但是我们 DBA 必须具有改写 SQL 的基本功，至于采不采纳，那就管不了那么多了。先把 SQL 调的飞起来再说。事实会说明一切的。实在没招，想必他们也愿意重新调整 SQL 的。

那么我就斟酌如何去调整这个 SQL 语句。正如前面的拆解所提及的，这个 SQL 真正需要调整的是子查询部分，也就是类似这样的一段 SQL 语句：

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

这样一看原来的子查询就是一个标量子查询，看下执行计划：

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

但是 max 子查询部分考虑再三，其实可以单独用一个 with as 代替：

可以先如下调整：

然后再去跟 D 表进行关联的。那么来回倒腾，最终得到了一个改写后的 SQL 语句，算是基本上成型了：

不着急，这几步的改写，或许你需要反反复复酝酿几遍 ……

死掉数以亿计的脑细胞 ……

酝酿完毕了，继续看上面待确定的部分，其实就是如何把 where 的 or 条件加到一起，再去 count （ * ），这个倒不是特别难，用 sum+case when 去完成就行：

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