求系统日活跃用户峰值时刻及SQL查询优化方案

问题背景 #

我们有一张sessions表，结构包含id、user_id、login_time、logout_time，单日数据量达百万级。示例数据如下：

注：登出时间为NULL表示会话仍处于活跃状态，当日结束前均视为有效。

需求是找出系统登录用户数最多的时刻，支持分钟甚至秒级粒度，且查询耗时控制在5-10秒内。

原方案及存在的问题 #

原方案通过生成时间序列并关联会话表统计活跃用户，但存在明显性能问题：

SELECT minutes::timestamp, COUNT(user_id) as active_users
FROM generate_series(timestamp '2023-05-10 00:00:00', timestamp '2023-05-10 23:59:59', interval '10 min') t(minutes)
INNER JOIN sessions as s
    on minutes between s.login_time and (CASE
    WHEN logout_time IS NULL THEN '2023-05-10 23:59:59'
    ELSE logout_time
    END)
GROUP BY minutes
ORDER BY active_users DESC
LIMIT 1;

• 查询耗时约35秒，效率极低；
• 仅支持固定时间粒度，无法高效扩展到分钟/秒级；
• 核心逻辑方向错误：通过时间点与会话做关联，本质是O(m*n)的复杂度（m为时间点数，n为会话数），百万级会话下性能必然崩溃。

优化方案：事件流累加算法 #

正确的思路是将每个会话拆解为**登录（+1）和登出（-1）**两个事件，通过排序后累加计算活跃用户数的变化，最终找到最大值对应的时刻。这种方法的时间复杂度为O(n log n)（n为事件数，约2倍会话数），能轻松满足性能要求。

1. 核心查询代码 #

WITH events AS (
    -- 生成登录事件：每个会话登录时活跃用户+1
    SELECT 
        login_time AS event_time,
        1 AS delta
    FROM sessions
    WHERE login_time >= '2023-05-10 00:00:00' 
      AND login_time < '2023-05-11 00:00:00'
    
    UNION ALL
    
    -- 生成登出事件：每个会话登出时活跃用户-1（未登出则取当日结束时间）
    SELECT 
        COALESCE(logout_time, '2023-05-10 23:59:59') AS event_time,
        -1 AS delta
    FROM sessions
    WHERE (logout_time IS NULL OR logout_time >= '2023-05-10 00:00:00')
      AND (logout_time IS NULL OR logout_time < '2023-05-11 00:00:00')
),
running_totals AS (
    -- 按时间排序，计算累计活跃用户数
    SELECT 
        event_time,
        SUM(delta) OVER (ORDER BY event_time) AS active_users
    FROM events
),
max_active AS (
    -- 找出最大活跃用户数
    SELECT MAX(active_users) AS max_users
    FROM running_totals
)
-- 获取达到最大活跃数的时刻（如果有多个，取最早/最晚可调整排序）
SELECT event_time, active_users
FROM running_totals, max_active
WHERE active_users = max_users
ORDER BY event_time
LIMIT 1;

2. 索引优化 #

为了进一步提升事件生成的速度，给sessions表创建复合索引：

CREATE INDEX idx_sessions_login_logout ON sessions (login_time, logout_time);

该索引能快速过滤当日的会话数据，避免全表扫描。

3. 粒度调整 #

• 秒级粒度：上述代码直接使用原始时间戳，天然支持秒级精度；
• 分钟粒度：若需按分钟统计，只需对event_time做截断处理，修改running_totals部分：

  running_totals AS (
      SELECT 
          DATE_TRUNC('minute', event_time) AS minute_time,
          SUM(delta) OVER (ORDER BY DATE_TRUNC('minute', event_time)) AS active_users
      FROM events
  )
  

  后续查询对应调整为minute_time即可。

4. 性能说明 #

该方案将百万级会话转换为约2百万条事件，排序和累加操作在PostgreSQL中通常能在3-8秒内完成，完全满足5-10秒的耗时要求。

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