大数据集下SQL查询超时，请求特定查询语句的性能优化建议

嘿，针对你碰到的这个大数据集查询超时问题，咱先拆解下原查询的瓶颈，再给你几个实打实的优化方向：

原查询回顾 #

select userid, max(recent_activity_date) recent_activity_date 
from ( 
    SELECT id AS userid, recent_logged_in AS recent_activity_date 
    FROM user 
    WHERE recent_logged_in > now() - cast('10 days' AS INTERVAL) 
    UNION 
    SELECT userid AS userid, max(recentaccessed) AS recent_activity_date 
    FROM tokencreds 
    WHERE recentaccessed > now() - cast('10 days' AS INTERVAL) 
    GROUP BY userid 
) recent_activity 
WHERE EXISTS(select 1 from user where id = userid and not deleted) 
group by userid 
order by userid;

核心问题分析 #

1. 冗余的分组与去重：外层的GROUP BY和MAX属于重复操作——内层第一个子查询返回的userid是user表主键（唯一），第二个子查询已经按userid分组取了最大值，而UNION本身会触发去重排序，额外消耗资源。
2. 过滤时机太晚：EXISTS子句在最后才过滤未删除用户，导致中间结果集先包含了大量无效数据，拖慢后续处理。
3. 索引利用不充分：tokencreds表的现有索引(userid, token)无法高效支撑recentaccessed的时间过滤+分组取最大值的操作。

具体优化方案 #

1. 优化查询逻辑，减少冗余操作 #

把UNION换成UNION ALL（避免不必要的去重排序），同时提前过滤未删除用户，缩小中间结果集：

SELECT 
    userid, 
    MAX(recent_activity_date) AS recent_activity_date
FROM (
    -- 直接从user表取未删除且符合时间条件的用户登录记录
    SELECT 
        id AS userid, 
        recent_logged_in AS recent_activity_date
    FROM "user"
    WHERE 
        recent_logged_in > NOW() - INTERVAL '10 days'
        AND NOT deleted
    UNION ALL
    -- 关联user表过滤未删除用户，再取tokencreds的最近访问时间
    SELECT 
        tc.userid, 
        MAX(tc.recentaccessed) AS recent_activity_date
    FROM tokencreds tc
    JOIN "user" u ON tc.userid = u.id
    WHERE 
        tc.recentaccessed > NOW() - INTERVAL '10 days'
        AND NOT u.deleted
    GROUP BY tc.userid
) AS combined_activity
GROUP BY userid
ORDER BY userid;

2. 调整索引，提升查询效率 #

• 优化user表索引：把现有user_recent_logged_in改成覆盖索引，避免回表查询：

  CREATE INDEX idx_user_recent_logged_in_id ON "user"(recent_logged_in, id);
  

• 新增tokencreds表索引：创建支持时间过滤+分组取最大值的覆盖索引：

  CREATE INDEX idx_tokencreds_recentaccessed_userid ON tokencreds(recentaccessed, userid);
  

  这个索引能让数据库直接通过索引过滤时间条件，同时获取分组所需的userid和计算最大值的recentaccessed，完全不需要回表。

3. 辅助优化手段 #

• 更新统计信息：确保数据库能生成最优执行计划，执行以下语句：

  ANALYZE "user";
  ANALYZE tokencreds;
  

• 临时调整超时时间：如果优化后仍有短时间需求，可以临时增大语句超时阈值（治标不治本，优先用前面的方法）：

  SET statement_timeout = '300s'; -- 设置为5分钟，根据实际情况调整
  

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