按用户时区日期统计在线会话数及大表SQL查询优化咨询

需求说明 #

统计指定年份、月份、时区下，该月份所有日期的每日在线会话数量，无会话的日期需显示0。输入示例：年份2020、月份08、时区Asia/Dhaka，输出格式如下：

[
{"date": "2020-08-01", "online_session_count": 3},
{"date": "2020-08-02", "online_session_count": 0},
......
{"date": "2020-08-31", "online_session_count": 1},
]

表结构定义 #

CREATE TABLE online_speakers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    speaker_name VARCHAR(255) NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL
);

测试数据 #

INSERT INTO online_speakers (speaker_name, start_time, end_time) VALUES
('Speaker 1', '2020-08-01 10:00:00', '2020-08-01 11:00:00'),
('Speaker 2', '2020-08-01 14:00:00', '2020-08-01 15:00:00'),
('Speaker 3', '2020-08-02 09:00:00', '2020-08-02 10:00:00'),
('Speaker 4', '2020-08-03 10:00:00', '2020-08-03 11:00:00'),
('Speaker 5', '2020-08-03 13:00:00', '2020-08-03 14:00:00'),
('Speaker 6', '2020-08-03 15:00:00', '2020-08-03 16:00:00'),
('Speaker 7', '2020-08-04 11:00:00', '2020-08-04 12:00:00'),
('Speaker 8', '2020-08-04 13:00:00', '2020-08-04 14:00:00'),
('Speaker 9', '2020-08-05 10:00:00', '2020-08-05 11:00:00'),
('Speaker 10', '2020-08-05 15:00:00', '2020-08-05 16:00:00');

原查询及性能问题 #

原查询使用递归CTE生成日期范围，但WHERE子句中调用CONVERT_TZ函数处理时间范围，导致百万级数据下无法利用索引，全表扫描效率极低：

WITH RECURSIVE date_range AS (
    SELECT DATE('2020-08-01') AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE DATE_ADD(date, INTERVAL 1 DAY) <= '2020-08-31'
)
SELECT
    date_range.date,
    COALESCE(session_tbl.online_session_count, 0) AS online_session_count
FROM
    date_range
LEFT JOIN (
    SELECT
        DATE(CONVERT_TZ(start_time, 'UTC', 'Asia/Dhaka')) AS date,
        COUNT(*) AS online_session_count
    FROM
        online_speakers
    WHERE
        start_time BETWEEN CONVERT_TZ('2020-08-01 00:00:00', 'Asia/Dhaka', 'UTC')
                      AND CONVERT_TZ('2020-08-31 23:59:59', 'Asia/Dhaka', 'UTC')
    GROUP BY
        date
    ORDER BY
        date
) session_tbl ON session_tbl.date = date_range.date;

优化方案 #

1. 提前在后端计算UTC时间范围 #

把时区转换逻辑移到后端代码中，提前计算目标时区的起始/结束时间对应的UTC时间，作为常量参数传入SQL。这样WHERE子句直接用start_time与常量比较，能利用字段索引，避免全表扫描。

比如针对Asia/Dhaka（UTC+6），2020-08-01 00:00:00 对应UTC的2020-07-31 18:00:00，2020-08-31 23:59:59 对应UTC的2020-08-31 17:59:59，直接把这两个值作为参数传入。

2. 为start_time建立索引 #

创建索引加速时间范围查询：

CREATE INDEX idx_online_speakers_start_time ON online_speakers(start_time);

3. 移除子查询中多余的ORDER BY #

子查询内的ORDER BY完全多余，因为最终结果会和日期范围表关联排序，去掉后减少排序开销。

4. 优化日期生成（可选） #

递归CTE生成31天的日期范围性能足够，若追求极致效率，可改用数字表生成：

WITH date_range AS (
    SELECT ADDDATE('2020-08-01', t.n - 1) AS date
    FROM (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
        UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
        UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
        UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25
        UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
        UNION ALL SELECT 31
    ) t
    WHERE ADDDATE('2020-08-01', t.n - 1) <= '2020-08-31'
)

优化后的完整SQL #

WITH RECURSIVE date_range AS (
    SELECT DATE('2020-08-01') AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE DATE_ADD(date, INTERVAL 1 DAY) <= '2020-08-31'
)
SELECT
    dr.date,
    COALESCE(osc.online_session_count, 0) AS online_session_count
FROM date_range dr
LEFT JOIN (
    SELECT
        DATE(CONVERT_TZ(start_time, 'UTC', 'Asia/Dhaka')) AS target_date,
        COUNT(*) AS online_session_count
    FROM online_speakers
    WHERE start_time BETWEEN '2020-07-31 18:00:00' AND '2020-08-31 17:59:59'
    GROUP BY target_date
) osc ON osc.target_date = dr.date
ORDER BY dr.date;

额外建议 #

• 如果频繁按时区统计，可在表中新增local_date字段，存储转换后的本地日期，通过触发器或插入数据时同步计算，再为该字段建立索引，彻底避免查询时的时区转换开销。
• 若需统计跨天会话覆盖的所有日期（比如会话从2020-08-01 23:00到2020-08-02 01:00，需同时计入两天），需调整逻辑为判断日期范围与会话时间范围是否重叠，而非仅按start_time分组。

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