谷歌日历式多用户重复提醒的表结构优化与指定时段SQL查询方案咨询

针对你设计的多用户预约提醒系统，我从存储结构和SQL查询两个方面给出优化建议：

一、更优的存储方案优化 #

你当前的分表思路是对的，但可以通过减少冗余、统一结构来提升扩展性和维护性：

1. 统一单条/重复提醒的存储逻辑 #

把单条提醒视为「重复次数为1、无后续重复」的特殊情况，这样无需单独区分表结构。建议在appointment表中增加is_recurring布尔字段，标记是否为重复提醒；同时补充title字段（对应用户期望输出的name），方便直接查询展示。

2. 简化重复规则的字段设计 #

当前appointment_frequency表的星期字段（sunday到saturday）过于冗余，建议替换为紧凑的星期标记字段：

• 方案1：用week_days VARCHAR(7)存储，每个字符对应一个星期几（比如1010000表示选中周日、周二，1代表选中，0未选中）
• 方案2：用整数位掩码（比如1<<0代表周日，1<<1代表周一，最终数值是选中星期的位运算结果）
  这样既节省存储空间，也更便于SQL查询时判断日期是否符合星期规则。

3. 减少互斥字段的冗余 #

on_day、on_week、on_last_week是针对不同频率类型的互斥配置（比如每月重复要么是固定日期，要么是第几周的星期几），可以保留字段但通过业务逻辑或数据库约束确保同一记录仅使用对应频率的字段，避免大量空值。另外，max_repeat和end_date也是互斥的停止条件，建议在业务逻辑中优先取较早的停止规则（比如同时设置了重复次数和结束日期，哪个先到就停止）。

优化后的参考表结构：
表：appointments

• appointment_id (INT, PK)：主键
• user_id (INT)：关联用户ID
• title (VARCHAR(255))：提醒名称
• start_date (DATE)：首次提醒日期
• start_time (TIME)：开始时间
• end_time (TIME)：结束时间
• end_repeat_date (DATE, NULL)：重复结束日期（NULL表示永不结束）
• is_recurring (BOOLEAN)：是否为重复提醒
• is_active (BOOLEAN)：是否有效（软删除标记）

表：recurrence_rules

• rule_id (INT, PK)：主键
• appointment_id (INT, FK)：关联预约ID
• frequency_type (CHAR(1))：D(每日), W(每周), M(每月), Y(每年)
• interval (INT, DEFAULT 1)：重复间隔（如每2天、每3周）
• week_days (VARCHAR(7))：选中的星期几（仅每周/每月周重复时使用）
• month_day (INT, NULL)：每月固定日期（仅每月日期重复时使用）
• month_week (INT, NULL)：每月第几周（仅每月周重复时使用）
• is_last_week (BOOLEAN, DEFAULT FALSE)：是否为每月最后一周（仅每月周重复时使用）
• max_repeats (INT, NULL)：最大重复次数

二、直接用SQL查询指定时段的提醒 #

完全可以通过SQL的递归CTE（公共表表达式）生成所有符合规则的重复提醒，无需Java代码二次筛选。下面是针对你需求的SQL示例：

查询用户1、2在2021-10-01至2021-10-15的提醒 #

WITH RECURSIVE recurring_reminders AS (
    -- 初始记录：取所有符合条件的重复提醒的首次日期
    SELECT
        a.appointment_id AS reminder_id,
        a.user_id,
        a.title AS name,
        a.start_date AS `Date`,
        a.start_time AS Start_time,
        a.end_time AS End_time,
        r.frequency_type,
        r.interval,
        r.week_days,
        r.month_day,
        r.month_week,
        r.is_last_week,
        r.max_repeats,
        a.end_repeat_date,
        1 AS repeat_count
    FROM appointments a
    JOIN recurrence_rules r ON a.appointment_id = r.appointment_id
    WHERE a.is_recurring = TRUE
      AND a.user_id IN (1, 2)
      AND a.is_active = TRUE
      -- 确保重复规则覆盖查询时段
      AND (a.start_date <= '2021-10-15' 
           AND (a.end_repeat_date IS NULL OR a.end_repeat_date >= '2021-10-01'))

    UNION ALL

    -- 递归生成后续重复日期
    SELECT
        rd.reminder_id,
        rd.user_id,
        rd.name,
        -- 根据频率类型计算下一个提醒日期
        CASE
            WHEN rd.frequency_type = 'D' THEN DATE_ADD(rd.`Date`, INTERVAL rd.interval DAY)
            WHEN rd.frequency_type = 'W' THEN DATE_ADD(rd.`Date`, INTERVAL rd.interval WEEK)
            WHEN rd.frequency_type = 'M' THEN 
                CASE
                    -- 每月固定日期
                    WHEN rd.month_day IS NOT NULL THEN 
                        DATE_ADD(rd.`Date`, INTERVAL rd.interval MONTH)
                    -- 每月最后一周的指定星期
                    WHEN rd.is_last_week THEN 
                        LAST_DAY(DATE_ADD(rd.`Date`, INTERVAL rd.interval MONTH)) 
                        - INTERVAL (WEEKDAY(LAST_DAY(DATE_ADD(rd.`Date`, INTERVAL rd.interval MONTH))) 
                                   - LOCATE('1', rd.week_days) + 2) DAY
                    -- 每月第N周的指定星期
                    ELSE 
                        DATE_ADD(rd.`Date`, INTERVAL rd.interval MONTH)
                        + INTERVAL (rd.month_week - WEEK(DATE_ADD(rd.`Date`, INTERVAL rd.interval MONTH), 1)) WEEK
                END
            WHEN rd.frequency_type = 'Y' THEN DATE_ADD(rd.`Date`, INTERVAL rd.interval YEAR)
        END AS `Date`,
        rd.Start_time,
        rd.End_time,
        rd.frequency_type,
        rd.interval,
        rd.week_days,
        rd.month_day,
        rd.month_week,
        rd.is_last_week,
        rd.max_repeats,
        rd.end_repeat_date,
        rd.repeat_count + 1 AS repeat_count
    FROM recurring_reminders rd
    WHERE 
        -- 停止递归的条件
        (rd.max_repeats IS NULL OR rd.repeat_count < rd.max_repeats)
        AND (rd.end_repeat_date IS NULL OR DATE_ADD(rd.`Date`, INTERVAL rd.interval DAY) <= rd.end_repeat_date)
        AND DATE_ADD(rd.`Date`, INTERVAL rd.interval DAY) <= '2021-10-15'
        -- 每周重复时，验证下一个日期是否在选中的星期几内
        AND (rd.frequency_type != 'W' OR SUBSTRING(rd.week_days, WEEKDAY(DATE_ADD(rd.`Date`, INTERVAL rd.interval DAY)) + 1, 1) = '1')
)
-- 合并重复提醒和单条提醒的结果
SELECT reminder_id, user_id, name, `Date`, Start_time, End_time
FROM recurring_reminders
WHERE `Date` BETWEEN '2021-10-01' AND '2021-10-15'

UNION ALL

SELECT 
    appointment_id AS reminder_id,
    user_id,
    title AS name,
    start_date AS `Date`,
    start_time AS Start_time,
    end_time AS End_time
FROM appointments
WHERE is_recurring = FALSE
  AND user_id IN (1, 2)
  AND is_active = TRUE
  AND start_date BETWEEN '2021-10-01' AND '2021-10-15'
ORDER BY user_id, `Date`, Start_time;

关键逻辑说明 #

1. 递归CTE：先获取所有符合条件的重复提醒的首次记录，然后递归生成后续符合规则的日期，直到达到停止条件（超过最大重复次数、结束日期或查询时段）。
2. 频率适配：针对每日、每周、每月、每年的不同规则，分别计算下一个提醒日期。
3. 结果合并：将递归生成的重复提醒和单条提醒合并，最终输出符合格式的结果。

性能优化建议 #

• 给appointments表的user_id、start_date、end_repeat_date、is_recurring字段添加联合索引。
• 给recurrence_rules表的appointment_id字段添加外键索引。
• 对于超大量的重复提醒，可以考虑在业务逻辑中预生成近期的提醒记录并缓存，减少递归查询的压力。

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