如何优化按account_id聚合判断记录类别是否存在的SQL查询？

问题背景 #

需求是按account_id分组，判断user_account_values表中是否存在指定类别的记录，最终聚合为true/false形式输出（示例字段如AccountID、Category1Exists、Category2Exists等）。

表结构：

TABLE `user_account_values` (
    user_id,
    account_id, -- 非唯一
    svc_id,
    card_id,
    name,
    status
)

当前使用的解决方案是通过子查询生成标识位，再用MAX()聚合得到是否存在的结果，SQL代码如下：

SELECT 
    account_id, 
    MAX(first) AS firstExists, 
    MAX(final) AS finalExists
FROM
    (SELECT 
         account_id,
         CASE 
             WHEN name IN ('something1_first', 'something2_first')
                 THEN 1 
                 ELSE 0 
         END AS first,
         CASE 
             WHEN name IN ('something1_final', 'something2_final') 
                 THEN 1 
                 ELSE 0 
         END AS final
     FROM  
         user_account_values 
     WHERE 
         user_id = :user  
         AND account_id IS NOT NULL
         AND status = 'A')
GROUP BY 
    account_id

询问是否有更简洁的实现方式，曾考虑按类别分别查询account_id再关联，但担心处理4-5个类别时的性能问题。

优化方案：去掉子查询，直接聚合CASE表达式 #

可以将子查询中的CASE逻辑直接放到主查询的聚合函数里，省去一层子查询，代码更简洁，且性能不会下降（甚至可能更优，减少了中间结果集的生成）：

SELECT 
    account_id,
    MAX(CASE WHEN name IN ('something1_first', 'something2_first') THEN 1 ELSE 0 END) AS firstExists,
    MAX(CASE WHEN name IN ('something1_final', 'something2_final') THEN 1 ELSE 0 END) AS finalExists
FROM user_account_values
WHERE 
    user_id = :user
    AND account_id IS NOT NULL
    AND status = 'A'
GROUP BY account_id

如果需要输出布尔类型（而不是0/1），可以根据数据库语法调整：

• PostgreSQL可直接返回布尔值：

SELECT 
    account_id,
    MAX(CASE WHEN name IN ('something1_first', 'something2_first') THEN TRUE ELSE FALSE END) AS firstExists,
    MAX(CASE WHEN name IN ('something1_final', 'something2_final') THEN TRUE ELSE FALSE END) AS finalExists
FROM user_account_values
WHERE 
    user_id = :user
    AND account_id IS NOT NULL
    AND status = 'A'
GROUP BY account_id

• MySQL可通过CAST转换：

SELECT 
    account_id,
    CAST(MAX(CASE WHEN name IN ('something1_first', 'something2_first') THEN 1 ELSE 0 END) AS BOOLEAN) AS firstExists,
    CAST(MAX(CASE WHEN name IN ('something1_final', 'something2_final') THEN 1 ELSE 0 END) AS BOOLEAN) AS finalExists
FROM user_account_values
WHERE 
    user_id = :user
    AND account_id IS NOT NULL
    AND status = 'A'
GROUP BY account_id

关于性能的说明 #

• 「按类别分别查询再关联」的方式不推荐：这种方式需要对表进行4-5次扫描（每个类别一次），再通过JOIN合并结果，数据量较大时性能会明显低于单表扫描方案。
• 上述优化方案和原方案本质都是单表扫描，一次遍历即可完成所有类别的判断，新增4-5个CASE表达式的计算开销可以忽略。

若要进一步优化性能，可创建复合索引(user_id, status, account_id, name)，让查询直接通过索引获取所需数据，无需回表扫描。

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