一次查询一条数据花费6分钟的SQL优化到0.05s的经历

环境：阿里云ECS Oracle 11.2.0.1 ADG

最近数据库晚上在0点到2点之间的报表任务job运行时间明显上升，如下是截取7天快照DB TIME所看到的情况：

虽然并没有影响到日终的进行，但是由于上次造成alert出现snapshot too old的事件以后，还是觉得需要看一下为好，索性就找了当天的0点到1点的awr报告看了一眼：

我同时查看了之前几天的，都如上图所示，鹤立鸡群，很明显，2c7ut81kqjf4k 这个sql_id语句执行了300多秒，执行一次，一开始我以为查的数据比较多，加上SQL语句有些问题，会很慢，然后就将对应的sql_id的awr报告下载：

执行300多秒，愣是没有查出什么数据来。这里我就开始怀疑，SQL查询数据可能数量极少，但是时间很久。然后就手动执行了这条SQL语句：

原始SQL语句如下：

select distinct a.id, a.busistate, a.processinstanceid
  from ACCEPAPPLYINFO a, VBPMEXTTASK c, VBPMPROCESSINFO d
 where c.ACTIVATIONTIME =
       (select max(t.activationtime) activationtime
          from VBPMEXTTASK t
         where t.PROCESSINSTANCEID = d.id
         group by t.PROCESSINSTANCEID)
   and a.applysubno = d.BUSINESSKEY
   and d.PROCESSINSTANCEID >= c.PROCESSINSTANCEID
   and a.busistate in ('09', '17')
   and a.DATASOURCE not in ('2', '4')
   and (select businessdate from smsysdate) - trunc(c.ACTIVATIONTIME) >=
       (select decode(durationunit,
                      '01',
                      duration,
                      '02',
                      duration * 30,
                      '03',
                      duration * 365)
          from smwarning
         where warntype = '02')

果然执行343s，cost多达549M，16w逻辑读，7216物理读，2次内存排序，就查出来一条数据。

很明显这是不正常的。

1）、然后我们来分析一下执行计划：

问题1：其中sort join出现2次，还有一次merge join；

问题2：步骤10那里就因为这个sort join导致了13M的临时表的占用，并且cost值迅速增大，看谓词信息发现是条件d.PROCESSINSTANCEID >= c.PROCESSINSTANCEID搞的怪，然而我非常不明白，这里并没有使用函数，为何会有internal function字样的出现；

问题3：步骤7,8，很明显，驱动表不对，ACCEPAPPLYINFO表应该是被驱动表才对啊，这样再加上不好的驱动nested loop，cost又是上涨一大截；

问题4：谓词条件2,7，全部都是filter，尤其针对2，子查询并未展开，而是直接过滤。

综上来说，这个SQL语句改写的余地很大，这里暂时没有考虑索引的问题。

2）、然后让我们来分析SQL语句的问题：

问题1：第一个where条件就类似于：c. ACTIVATIONTIME = max(c.ACTIVATIONTIME)再加几个条件的等价子查询，这其实就平白多增加了一次对c表的扫描操作；

问题2：这里又有一个d.PROCESSINSTANCEID >= c.PROCESSINSTANCEID的条件导致Oracle内部必须对两张表都进行排序然后比较；

问题3：a.DATASOURCE not in ('2', '4')条件，历来都反对有not in的操作。

问题4：(select businessdate from smsysdate) - trunc(c.ACTIVATIONTIME)，总感觉这个trunc用的不好，不过后来证明我是错的，关键点不在这里。

到了这里分析告一段落，让来开始进行改写操作。

总体的大致思路是：尽量减少排序操作和减少表的扫描操作，这个思路看似没有什么问题，但是我却忽略了整体，着重了片面，导致我停留了很长时间在这里；

a、最开始，针对分析SQL第一个问题，第一反应就是去掉group by，如何去掉呢，就是使用分析函数。针对

select max(t.activationtime) activationtime
          from VBPMEXTTASK t
         where t.PROCESSINSTANCEID = d.id
         group by t.PROCESSINSTANCEID

子查询，改写如下：

select max(t.activationtime) over(partition by t.PROCESSINSTANCEID) activationtime
          from VBPMEXTTASK t
         where t.PROCESSINSTANCEID = d.id

然而其实我是在自欺欺人，虽然去掉了group by，但是效果并不理想，这不过是一个变着法的排序罢了。

b、然后就是针对问题3，not in的改写，查询了一下实际的表，这个DATASOURCE列只有三个值，0,1,2，所以我就直接改成了a.DATASOURCE in ('0', '1')，企图有所效果，可是，并没有眷顾我，效果甚微，只是从549M到了541M，基本没什么变化。

c、然后就是针对问题2的d.PROCESSINSTANCEID >= c.PROCESSINSTANCEID，想着既然这个条件只有c和d两个表，那我要是单独合到一起的话怎么样呢，然后就有了如下改写：

select max(c.ACTIVATIONTIME)
    from ACCEPAPPLYINFO a,VBPMEXTTASK c, VBPMPROCESSINFO d
    where d.PROCESSINSTANCEID >= c.PROCESSINSTANCEID
       and a.applysubno = d.BUSINESSKEY
       and c.PROCESSINSTANCEID = d.id
       group by c.PROCESSINSTANCEID

整体情况:

select distinct a.id, a.busistate, a.processinstanceid
  from ACCEPAPPLYINFO a，(
select max(c.ACTIVATIONTIME) ACTIVATIONTIME
    from ACCEPAPPLYINFO a,VBPMEXTTASK c, VBPMPROCESSINFO d
    where d.PROCESSINSTANCEID >= c.PROCESSINSTANCEID
       and a.applysubno = d.BUSINESSKEY
       and c.PROCESSINSTANCEID = d.id
       group by c.PROCESSINSTANCEID) c
   where  a.busistate in ('09', '17')
   and a.DATASOURCE not in ('2', '4')
   and  (select businessdate from smsysdate) - trunc(c.ACTIVATIONTIME) >=
           (select decode(durationunit,
                      '01',
                      duration,
                      '02',
                      duration * 30,
                      '03',
                      duration * 365)
          from smwarning
         where warntype = '02')

然而虽然查看执行计划cost从549M降到了289M

然而，查询出来的结果竟然有198条数据，完全不是1条数据啊，改写再一次失败

分析原因，应该是我多加一个a表，导致最后进行关联连接的时候导致更多的数据被保留下来，这很明显不符合原SQL语义，虽然语法没有了问题，但是语义出现了偏差。

不过意识到这个问题以后，我有些豁然开朗

我的整体思路没有理清，虽然自己是针对片面减少了排序和时间，但是语义上出现了偏差。

然后重新理了一下思路：

减少排序是必须的，但是还有一点就是缩小所需结果集，按照集合的思想来考虑，尽可能使用更多的条件来限定使用的子查询结果集的大小。所以进一步修改SQL的语句如下：

select distinct a.id, a.busistate, a.processinstanceid
  from (select a.id, a.busistate, a.processinstanceid,max(c.ACTIVATIONTIME) ACTIVATIONTIME
from ACCEPAPPLYINFO a,VBPMEXTTASK c, VBPMPROCESSINFO d
where d.PROCESSINSTANCEID >= c.PROCESSINSTANCEID
   and a.applysubno = d.BUSINESSKEY
   and c.PROCESSINSTANCEID = d.id
   and a.busistate in ('09', '17')
   and a.DATASOURCE not in ('2', '4')
   group by a.id, a.busistate, a.processinstanceid) a
   where  (select businessdate from smsysdate) - trunc(a.ACTIVATIONTIME) >=
       (select decode(durationunit,
                      '01',
                      duration,
                      '02',
                      duration * 30,
                      '03',
                      duration * 365)
          from smwarning
         where warntype = '02')  

解读一下就是，我先把a、b、c三张表进行关联查询结果，把原SQL的第一个子查询分解掉，直接使用max函数查询结果，然后将各条件剥离出来的结果进行group by，这样所需的结果集就缩小了很多。然后外层再进行主查询，使用最后一个子查询条件进行滤过即可。

然后再一次执行SQL语句如下：

可以看到时间从343s一下降到了0.38s，cost从549M降到14199，逻辑读从16w降到4w，物理读从7192降到402，内存排序从2次变为0，变化巨大。

其实到这里基本就可以完工了，我也本来打算就酱紫了，准备提交开发同事，但是，吃饭的时候我看着这个执行计划，心里还是痒痒。

a（ACCEPAPPLYINFO） 表仍然是全表扫描，但是条件里有

   and a.applysubno = d.BUSINESSKEY

   and a.busistate in ('09', '17')

   and a.DATASOURCE not in ('2', '4')

这三个条件和a表有关，那为何不走索引呢，然后我就查了一下applysubno 列distinct值很多，但是由于是等值条件，无法使用也算正常；

DataSource列一共就0,1,2,单个distinct值，开始也测试了也没有必要，何况我查询这个表的索引的时候applysubno 列是有索引的，然而并没有走；然后只有busistate这个列了，没有索引，不同的值查看了一下有30个，也就是说这个SQL用到了2个，虽然理论上来说，选择性并不是很好，但是还是觉得尝试一下如何

SQL> create index idx_busistate on ACCEPAPPLYINFO(busistate);

然后再一次进行查询操作：

见证奇迹的时刻到了

从原来的0.38s降到了0.05s，逻辑读从14199降到1947，物理读从402降到378，对于a表也走了索引，正是我创建的索引，谓词条件也看到从原来的filter，变成了access。

如此，完美！！！

然后提交开发同事确认了一下，进行相关测试

待后续！！！！