往期回顾

前面我们分别探讨了"莫名其妙"的行列转换和"无脑复制粘贴"给SQL性能带来的负面影响。本篇，我们将继续"精兵简政"的优化之旅，聚焦一个重要的优化思路——面对复杂需求时，先将问题最小化，再寻求高效解决方案。

案例7 遇到难题可先考虑将需求最小化

select distinct tb.peer_id,
                decode(ta.action, null, '', ta.action) action,
                decode(ta.amt,
                       null,
                       '',
                       to_char(ta.amt, 'yyyy-mm-dd hh24:mi:ss')) amt,
                decode(ta.bmt,
                       null,
                       '',
                       to_char(ta.bmt, 'yyyy-mm-dd hh24:mi:ss')) bmt
  from (select t1.peer_id, t3.action, t2.amt, t1.bmt
          from (select a.peer_id, max(a.log_time) as bmt
                  from dcc_sys_log a
                 where a.action = 'disconnect'
                   and a.cause like '对端被关闭%'
                 group by a.peer_id) t1,
               (select b.peer_id, max(b.log_time) as amt
                  from dcc_sys_log b
                 where b.action = 'connecting'
                   and (b.cause like '分配对端标识%' or
                       b.cause like '接收到对端主机CEA消息包%')
                 group by b.peer_id) t2,
               (select c.peer_id, c.action
                  from dcc_sys_log c,
                       (select max(log_time) mt, peer_id
                          from dcc_sys_log
                         where ((action = 'connecting' and
                               (cause like '分配对端标识%' or
                               cause like '接收到对端主机CEA消息包%')) or
                               (action = 'disconnect' and cause like '对端被关闭%'))
                         group by peer_id) d
                 where to_char(c.log_time, 'yyyymmddhh24miss') =
                       to_char(d.mt, 'yyyymmddhh24miss')
                   and c.peer_id = d.peer_id
                   and ((c.action = 'connecting' and
                        (c.cause like '分配对端标识%' or
                        c.cause like '接收到对端主机CEA消息包%')) or
                        (c.action = 'disconnect' and c.cause like '对端被关闭%'))) t3
         where t1.peer_id = t2.peer_id
           and t3.peer_id = t2.peer_id) ta,
       dcc_ne_info tb
 where ta.peer_id(+) = tb.peer_id

乍看这段SQL，复杂程度令人生畏。它包含多个子查询、多表连接和复杂的条件判断，代码冗长且难以理解。这种复杂性不仅增加了维护难度，还可能导致性能问题。面对如此复杂的SQL，我们应该如何优化呢？

需求理解

首先，让我们理解这段SQL的业务需求：

分析各PEER_ID连接或者退出的记录情况，该PEER_ID允许多次登录和退出，需要记录：

1. 最近一次登录时间

2. 最近一次退出时间

3. 最后该PEER_ID当前状态是登录还是退出

4. 查询结果必须在DCC_NE_INFO表中有配置信息

为了更清晰地理解需求，我们可以构造一个简化的测试场景：

DROP TABLE test;
create table test (peer_id number,log_time date,action varchar2(20),cause varchar2(20));
insert into test VALUES (1,sysdate-4,'connect','连接');
insert into test VALUES (1,sysdate-9999,'connect','连接');
insert into test VALUES (1,sysdate-9991,'disconnect','退出');
insert into test VALUES (2,sysdate-4,'connect','连接');
insert into test VALUES (2,sysdate-3,'disconnect','退出');
insert into test VALUES (3,sysdate-4,'connect','连接');
insert into test VALUES (4,sysdate-4,'disconnect','退出');
commit;
select * from test;

期望的查询结果如下：

PEER_ID  当前状态为       最新登录时间    最新断开时间
1,       connect        sysdate-4     sysdate-9991
2,       disconnect     sysdate-4     sysdate-3
3,       connect        sysdate-4    
4,       disconnect                   sysdate-4

问题分析

原SQL存在以下主要问题：

1. 过度复杂化：使用了三个子查询（t1、t2、t3）来分别获取最新断开时间、最新连接时间和当前状态，然后再进行连接

2. 重复扫描：多次扫描dcc_sys_log表，增加了I/O开销

3. 条件重复：相同的过滤条件在不同子查询中重复出现

4. 格式转换低效：使用to_char进行日期格式转换，而后又进行字符串比较

5. 冗余连接条件：t1、t2、t3三个子查询结果通过peer_id连接，但实际上这些信息可以在一次查询中获得需求最小化思路

面对这种复杂SQL，我们可以采用"需求最小化"的思路：

1. 识别核心需求：本质上是要找出每个peer_id的最新连接时间、最新断开时间和当前状态

2. 简化问题：将复杂问题拆解为更简单的子问题

3. 寻找高效算法：使用分析函数一次性解决问题，而不是多次查询后再连接

优化方案

对于这种场景，我们可以使用Oracle的KEEP分析函数和条件聚合来一次性解决问题：

SELECT peer_id
      ,max(action) keep(dense_rank last order by log_time)
      ,max(decode(action,'connect',log_time))
      ,max(decode(action,'disconnect',log_time))
  FROM test
GROUP BY peer_id;

这个简洁的查询能够一次性获取所需的全部信息：

- 用max(action) keep(dense_rank last order by log_time)获最后一次操作类型（当前状态）

- 使用max(decode(action,'connect',log_time))获取最新连接时间

- 使用max(decode(action,'disconnect',log_time))获取最新断开时间

将这一思路应用到原始SQL中，优化后的代码如下：

SELECT peer_id
      ,max(action) keep(dense_rank last order by log_time)
      ,max(decode(action,'connect',log_time))
      ,max(decode(action,'disconnect',log_time))
  FROM test
GROUP BY peer_id;

根据分析，优化改造为

select tb.peer_id,nvl(ta.action,'') action ,
nvl2(ta.amt,to_char(ta.amt, 'yyyy-mm-dd hh24:mi:ss'),'') amt,
nvl2(ta.bmt,to_char(ta.bmt, 'yyyy-mm-dd hh24:mi:ss'),'') bmt
from 
(WITH DCC_SYS_LOG_TMP
AS (SELECT * from dcc_sys_log
                         where ((action = 'connecting' and
                               (cause like '分配对端标识%' or
                               cause like '接收到对端主机CEA消息包%')) or
                               (action = 'disconnect' and cause like '对端被关闭%')
                               )
      )
SELECT peer_id
      ,max(action) keep(dense_rank last order by log_time) action
      ,max(decode(action,'connecting',log_time)) amt
      ,max(decode(action,'disconnect',log_time)) bmt
  FROM DCC_SYS_LOG_TMP
group by peer_id) ta, dcc_ne_info tb
where ta.peer_id(+)=tb.peer_id

优化效果分析

优化后的SQL相比原SQL有以下显著改进：

1. 代码量大幅减少：从原来的30多行减少到约15行，更易于理解和维护

2. 表扫描次数减少：只需扫描dcc_sys_log表一次，而非原来的多次

3. 逻辑更加清晰：使用WITH子句先过滤出符合条件的记录，再一次性计算所需结果

4. 使用高效函数：利用KEEP分析函数和条件聚合直接获取所需信息

5. 减少中间结果：不再需要生成和处理多个中间结果集

优化后的SQL不仅执行效率更高，而且代码更加简洁明了，体现了"精兵简政"的优化理念。

总结：化繁为简的优化艺术

本案例展示了一个重要的SQL优化思路——"需求最小化"。面对复杂的SQL问题，我们不应该直接陷入复杂的实现细节，而是应该先退一步，思考问题的本质，将需求简化到最小程度。

在这个案例中，原SQL通过多个子查询和连接来解决问题，走了一条复杂的路径。而优化后的方案则直接使用分析函数和条件聚合，一步到位地解决了问题。这种"化繁为简"的思路，正是"精兵简政"理念的生动体现。

SQL优化不仅仅是技术层面的改进，更是思维方式的转变。当我们面对复杂问题时，不要急于堆砌复杂的代码，而是应该先问自己："这个问题的本质是什么？有没有更简单直接的解决方案？"往往，最优雅的解决方案也是最简单的那个。

在日常开发中，我们应该培养这种"需求最小化"的思维习惯，不断寻找问题的最简解法，这样才能写出既高效又优雅的SQL代码。

未完待续...

SQL等价改写优化案例精选第二部【精兵简政】④ 多余写法

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

基于达梦的SQL等价改写优化案例精选第二部【精兵简政】③ 有困难先将需求最小化