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

以你之姓@ 2022-09-30 03:40 163阅读 0赞

环境：阿里云ECS Oracle 11.2.0.1 ADG

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

![Image 1][] ![Center][]

![Image 1][] ![Center 1][]

![Image 1][]

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

![Image 1][] ![Center 2][]

![Image 1][]

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

![Image 1][] ![Center 3][]

![Image 1][]

执行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')

![Image 1][] ![Center 4][]

![Center 5][]

![Center 6][]

![Image 1][]

果然执行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

![Image 1][] ![Center 7][]

![Image 1][]

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

![Center 8][]

![Image 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语句如下：

![Image 1][] ![Center 9][]

![Center 10][]

![Image 1][] ![Center 11][]

![Image 1][]

可以看到时间从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);

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

见证奇迹的时刻到了

![Image 1][]

![Center 12][]

![Image 1][] ![Center 13][]

![Image 1][]

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

如此，完美！！！

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

待后续！！！！

[Image 1]: 
[Center]: /images/20220708/e508446a4ecd498d835bd85154206ab6.png
[Center 1]: /images/20220708/d2f067657a0e47db9af464978438b9d3.png
[Center 2]: /images/20220708/79add74f0be34b37aca7cb692a849892.png
[Center 3]: /images/20220708/49ec21dcbe4247e68847ea158b156c35.png
[Center 4]: /images/20220708/57f413e9ccda4cd59810883fcdf3dc39.png
[Center 5]: /images/20220708/a23bbe1212b04fa98947fe1edf9a9c04.png
[Center 6]: /images/20220708/8f2a559be2034bbab8dd9b690882d03f.png
[Center 7]: /images/20220708/d5f08d7e188a4d1a911617727f6cf762.png
[Center 8]: /images/20220708/5027eee1aefa472cb24d39ae2f233aac.png
[Center 9]: /images/20220708/2181f99b7cd945079ffb3bcfbe8ad233.png
[Center 10]: /images/20220708/fc6c0c68474f4f2b978d38cfa7779a3b.png
[Center 11]: /images/20220708/1906e65af1b34087a80afaf9a39aaef8.png
[Center 12]: /images/20220708/f3bf7471b2fc48d3bec9c3002dc0b157.png
[Center 13]: /images/20220708/8d7f41cfc557475e9442fb2ef2f485f9.png