MySQL中Case语句内嵌Select查询的优化方案探讨

背景说明 #

现有deals表结构如下：

• PositionID：整数类型
• Login：整数类型
• VolumeClosed：整数类型
• Time：日期时间类型

当前使用的查询语句及结果如下：

当前查询语句 #

select
    d.PositionID,
    d.login,
    case
        when d.VolumeClosed = 0 then d.Time
        else ''
    end as Open_Time,
    
    case
        when d.VolumeClosed = 0 then (
        select
            `TIME`
        from
            deals
        where
            volumeclosed <> 0
            and d.PositionID = PositionID)
        else ''
    end as Close_Time
from
    deals d

当前查询结果 #

"PositionID","Login","Open_Time","Close_Time"
0,250052,"2022-08-01 10:32:29",
6218368,250052,"2022-08-01 10:45:11","2022-08-01 10:45:12"
6218368,250052,"",""
6218374,250052,"2022-08-01 10:45:41","2022-08-01 10:45:42"
6218374,250052,"",""

优化方案 #

原查询中的子查询属于相关子查询，会对主查询的每一行重复执行一次，数据量大时性能很差。可以通过以下两种方式优化：

方案一：预聚合+JOIN #

先一次性查询出所有PositionID对应的关闭时间，再通过JOIN关联原表，避免重复查询：

select
    d.PositionID,
    d.Login,
    case when d.VolumeClosed = 0 then d.Time else '' end as Open_Time,
    case when d.VolumeClosed = 0 then c.Close_Time else '' end as Close_Time
from deals d
left join (
    -- 预聚合所有有关闭记录的PositionID及其时间
    select PositionID, `Time` as Close_Time
    from deals
    where VolumeClosed <> 0
) c on d.PositionID = c.PositionID

方案二：窗口函数 #

利用窗口函数直接在原表中计算每个PositionID对应的关闭时间，无需额外JOIN：

select
    PositionID,
    Login,
    case when VolumeClosed = 0 then Time else '' end as Open_Time,
    -- 按PositionID分组，取出该组内关闭记录的时间
    case when VolumeClosed = 0 then max(case when VolumeClosed <> 0 then Time end) over(partition by PositionID) else '' end as Close_Time
from deals

两种方案都能大幅减少查询次数，提升性能，其中窗口函数的写法更简洁，适合支持窗口函数的数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）。

内容的提问来源于stack exchange，提问作者pewocis495