SQL等价改写优化案例精选第一部【一统天下】②分析函数的穿透之力

梁敬彬梁敬弘兄弟出品

往期回顾

SQL等价改写优化案例精选第一部【一统天下】① COUNT(CASE WHEN)的合并魔力

在SQL优化中，分析函数（Analytics Functions）是一类功能强大但常被忽视的工具。它们能在单次数据扫描中完成复杂的计算，避免多次访问相同数据。本案例将展示如何利用KEEP和DENSE_RANK函数组合，将多次表扫描简化为一次，大幅提升查询性能。

案例2：KEEP与DENSE_RANK的完美配合

在前一篇案例中，我们对一个复杂SQL进行了全面优化。今天，我们聚焦其中的一个关键环节：如何优化获取每个peer_id最新状态的子查询。

原始SQL：嵌套子查询的性能陷阱

select distinct dsl1.peer_id peer_id,
                                 nvl(ne_disconnect_info.ne_state, 1) ne_state
                   from dcc_sys_log dsl1,
                        (select distinct dnl.peer_id peer_id,
                                         decode(action,
                                                'disconnect',
                                                0,
                                                'connect',
                                                0,
                                                1) ne_state
                           from dcc_sys_log dsl, dcc_ne_log dnl
                          where dsl.peer_id = dnl.peer_id
                            and ((dsl.action = 'disconnect' and
                                dsl.cause = '关闭对端') or
                                (dsl.action = 'connect' and
                                dsl.cause = '连接主机失败'))
                            and log_type = '对端交互'
                            and dsl.log_time =
                                (select max(log_time)
                                   from dcc_sys_log
                                  where peer_id = dnl.peer_id
                                    and log_type = '对端交互')) ne_disconnect_info
                  where dsl1.peer_id = ne_disconnect_info.peer_id(+)

问题分析：

1. 复杂的嵌套子查询结构
2. 内部子查询需要多次访问dcc_sys_log表
3. 每个peer_id都要执行相关子查询，找出最大log_time
4. 连接逻辑复杂，可读性差

优化方案：分析函数的巧妙应用

原先至少需要扫描2次！现在根据KEEP结合DENSE_RANK的方式，将表扫描从2次变为了1次，具体代码改写如下：

SELECT 
    a.peer_id,
    CASE 
        WHEN dnl.peer_id IS NOT NULL 
             AND str IN ('disconnect关闭对端', 'connect连接主机失败') 
        THEN '0' 
        ELSE '1' 
    END ne_state
FROM 
    (
        SELECT 
            peer_id,
            MIN(action||cause) KEEP(DENSE_RANK LAST ORDER BY log_time) str 
        FROM 
            dcc_sys_log dsl
        WHERE 
            log_type = '对端交互'
        GROUP BY 
            peer_id
    ) a,
    (
        SELECT 
            DISTINCT peer_id 
        FROM 
            dcc_ne_log
    ) dnl 
WHERE 
    a.peer_id = dnl.peer_id(+)

优化解析：KEEP函数的魔力

这个优化方案使用了Oracle特有的KEEP…DENSE_RANK组合，这是一种强大但常被忽视的分析工具。让我们深入理解它的工作原理：

1. 分析函数如何改变游戏规则

原始SQL中，我们需要：

1. 先按peer_id分组
2. 对每组找出最大log_time的记录
3. 再检查该记录的action和cause组合是否满足条件

传统方式需要使用嵌套子查询，至少扫描表两次。但使用KEEP函数，我们可以在单次扫描中完成同样的工作。

2. KEEP(DENSE_RANK…)的工作机制

MIN(action||cause) KEEP(DENSE_RANK LAST ORDER BY log_time)

这行代码的含义是：

1. 按log_time排序（DENSE_RANK ORDER BY log_time）
2. 取排序后的最后一条记录（LAST）
3. 返回该记录的action||cause值

一句话完成了原先需要嵌套子查询才能实现的功能！

结语

分析函数是SQL优化的强大武器，它可以将多次数据访问简化为一次，大幅提升查询性能。在面对需要查询"最新"、“最大”、"排名前N"等场景时，优先考虑使用分析函数，而不是复杂的嵌套子查询。

正如本案例所示，通过KEEP和DENSE_RANK的组合，我们将原本需要两次表扫描的复杂查询简化为一次扫描，不仅提高了性能，还增强了代码的可读性和可维护性。这正是SQL等价改写的艺术所在。

系列回顾

“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列