重复关联EventDay表的查询方案性能与优化探讨

问题背景 #

现有实体包括：

• Event：包含1个或多个EventDay（代表单日连续时间段）
• User：可设置Schedule（每周各天的1个或多个连续空闲时间段）

需求：获取用户可参与的Event列表，按匹配用户日程的EventDay数量排序，同时统计活动所有天数的总时长，并支持按星期几过滤活动。时间以30分钟为单位存储为整数（0代表00:00，48代表24:00）。

现有SQL通过两次INNER JOIN关联event_days表，需分析该方案的性能、缺点、优化方向，同时探讨预存时间段表的替代方案可行性。

实体表结构 #

Event表

EventDay表

Schedule表

注：Schedule表的day字段取值0-6，代表星期几；EXTRACT(ISODOW FROM event_day.date) - 1用于将日期转换为对应的星期几（0-6）

现有SQL代码：

SELECT
    event.id,
    SUM(unfiltered_event_day.ending_at - unfiltered_event_day.starting_at) as total_event_hours,
FROM events event
         INNER JOIN event_days event_day ON event.id = event_day.event_id
         INNER JOIN event_days unfiltered_event_day ON event.id = unfiltered_event_day.event_id
         INNER JOIN schedules schedule ON EXTRACT(ISODOW FROM event_day.date) - 1 = schedule.day
WHERE
        schedule.user_id = :userId AND 
        EXTRACT(ISODOW FROM event_day.date) - 1 in :listOfdays AND
        event_day.starting_at >= schedule.starting_at AND
        event_day.ending_at <= schedule.ending_at
GROUP BY event.id
ORDER BY count(event.id) DESC, MIN(event_day.date)

现有SQL方案的性能表现 #

• 数据膨胀严重：两次关联event_days会触发笛卡尔积。假设一个Event有N个EventDay，每个匹配的EventDay关联M条Schedule记录，中间结果集会生成NMN条数据，数据量随活动天数、用户Schedule条目数呈指数增长，大幅拉高内存和CPU消耗。
• 函数调用拖慢查询：WHERE和JOIN条件中多次调用EXTRACT(ISODOW FROM event_day.date) - 1，该函数无法利用索引，导致event_days表全表扫描，数据量大时性能断崖式下降。
• 聚合效率低下：膨胀后的数据集做SUM、COUNT聚合，需要处理大量重复数据，增加聚合阶段的计算成本。

现有方案的缺点 #

1. 冗余关联导致数据爆炸：用两个event_days关联分别做匹配和统计完全没必要，直接造成中间结果集冗余。
2. 索引失效：日期转星期几的函数调用破坏了索引可用性，就算event_days有date或event_id的索引，也无法被有效利用。
3. 逻辑重复：WHERE条件重复计算星期几，增加不必要的计算开销。
4. 计数不准确：如果一个EventDay匹配多条Schedule记录，COUNT(event.id)会重复计数，导致排序用的匹配天数被高估。

优化方向 #

1. 消除冗余关联 #

合并两次event_days关联，先筛选符合条件的EventDay，再通过子查询统计总时长：

SELECT
    e.id,
    (SELECT SUM(ed.ending_at - ed.starting_at) FROM event_days ed WHERE ed.event_id = e.id) as total_event_hours,
    COUNT(DISTINCT ed.id) as matched_days_count
FROM events e
JOIN event_days ed ON e.id = ed.event_id
JOIN schedules s ON (EXTRACT(ISODOW FROM ed.date) - 1) = s.day
WHERE
    s.user_id = :userId
    AND (EXTRACT(ISODOW FROM ed.date) - 1) IN :listOfdays
    AND ed.starting_at >= s.starting_at
    AND ed.ending_at <= s.ending_at
GROUP BY e.id
ORDER BY matched_days_count DESC, MIN(ed.date)

注：用COUNT(DISTINCT ed.id)避免同一EventDay匹配多条Schedule时重复计数

2. 预计算星期几，添加复合索引 #

• 在event_days表新增day_of_week字段（0-6），通过触发器或ETL流程预计算并存储日期对应的星期几。
• 为event_days创建复合索引：(event_id, day_of_week, starting_at, ending_at)；为schedules创建(user_id, day, starting_at, ending_at)索引，让JOIN和WHERE条件完全命中索引。

3. 用CTE减少数据膨胀 #

先通过CTE筛选出匹配的EventDay，再关联统计总时长，避免笛卡尔积：

WITH matched_event_days AS (
    SELECT DISTINCT ed.event_id, ed.id
    FROM event_days ed
    JOIN schedules s ON ed.day_of_week = s.day
    WHERE
        s.user_id = :userId
        AND ed.day_of_week IN :listOfdays
        AND ed.starting_at >= s.starting_at
        AND ed.ending_at <= s.ending_at
)
SELECT
    e.id,
    (SELECT SUM(ed.ending_at - ed.starting_at) FROM event_days ed WHERE ed.event_id = e.id) as total_event_hours,
    COUNT(med.id) as matched_days_count
FROM events e
JOIN matched_event_days med ON e.id = med.event_id
GROUP BY e.id
ORDER BY matched_days_count DESC, (SELECT MIN(ed.date) FROM event_days ed WHERE ed.event_id = e.id)

预存时间段表的替代方案可行性 #

方案思路 #

预创建time_slots表，存储所有30分钟粒度的时间片段（0到47，对应00:00-23:30），同时可扩展存储时间段对应的星期几等信息。将EventDay和Schedule与该表关联，通过匹配时间段判断是否符合条件。

可行性分析 #

1. 优势：
   • 把时间范围匹配转化为等值匹配，更容易利用索引，简化查询逻辑。
   • 预计算所有可能的时间片段组合，减少重复计算，适合频繁进行时间匹配的场景。
2. 局限性：
   • 数据量膨胀：每个EventDay会拆分成多个时间段记录（比如3小时的EventDay对应6条记录），event_day_time_slots关联表的数据量会显著增加。
   • 维护成本高：新增或修改EventDay时，需要同步更新关联的时间段记录，需触发器或应用层逻辑支持。
3. 适用场景：
   • 当系统时间范围匹配查询频繁，且用户Schedule的时间段固定为30分钟粒度时，该方案能显著提升性能。
   • 若活动多为不规则长时段，拆分后的数据量膨胀可能抵消性能收益，不建议使用。

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