基于MySQL和Laravel实现订阅站点推荐追踪的方案咨询

你的初始思路方向没问题，但核心要遵循「历史数据不可篡改、所有状态变更留痕」的原则，不要直接更新已有记录，否则会导致历史月份统计数据失真，具体实现如下：

一、MySQL表结构设计 #

拆成两张核心表，职责分离：

1. 推荐关系主表 referral_relation #

注册绑定推荐关系时写入，永久不更新、不删除，只做关系绑定用：

• id：bigint 主键
• referrer_id：bigint 推荐人用户ID，加普通索引
• referee_id：bigint 被推荐人用户ID，加唯一索引（保证一个被推荐人仅能绑定一个推荐人，避免重复归因）
• bind_time：datetime 关系绑定时间（即用户注册时间）
• is_valid：tinyint 关系是否有效（默认1，反作弊判定刷量时置0，统计时过滤无效数据）

2. 推荐订阅事件流水表 referral_subscription_event #

只追加写、不修改历史记录内容，所有订阅状态变化都在这张表留痕，是统计的核心数据源：

• id：bigint 主键
• referrer_id：bigint 推荐人用户ID，和stat_month、subscription_tier建联合索引
• referee_id：bigint 被推荐人用户ID
• sub_id：bigint 关联的订阅订单ID，无订单时（比如注册、取消订阅）填0
• subscription_tier：varchar(16) 当前生效订阅层级，枚举值：Free/Pro/Premier
• event_type：varchar(16) 事件类型，枚举值：register（注册送免费版）/renew_success（续费成功）/upgrade（层级升级）/downgrade（层级降级）/cancel（订阅到期取消）
• effective_start_time：datetime 当前订阅状态的生效开始时间
• effective_end_time：datetime 当前订阅状态的生效结束时间，默认NULL代表该状态是当前最新生效状态
• stat_month：char(6) 状态生效所在的统计月份，格式为YYYYMM，比如202405，加索引提升按月统计效率

二、业务触发逻辑 #

所有事件触发时遵循「先终结旧状态、再写入新状态」的规则：

• 用户注册绑定推荐关系：先写referral_relation主表记录，再往流水表插入一条event_type=register、subscription_tier=Free的记录，effective_start_time填注册时间，stat_month取注册时间对应的年月。
• 用户续费扣费成功：先将该被推荐人当前生效中（effective_end_time IS NULL）的流水记录的effective_end_time更新为本次续费周期的前1秒，再插入一条event_type=renew_success的新记录，层级和上一周期保持一致，effective_start_time填本次续费周期的开始时间，stat_month取新周期对应的年月。
• 用户订阅升降级生效：同样先将当前生效记录的effective_end_time更新为升降级生效时间的前1秒，再插入对应event_type=upgrade/downgrade的新记录，subscription_tier填新的层级，effective_start_time填升降级生效时间，stat_month取生效时间对应的年月。禁止直接修改已有记录的层级字段，否则会导致历史月份的统计数据错乱。
• 用户订阅到期取消：将当前生效的付费层级记录的effective_end_time更新为到期时间，再插入一条event_type=cancel、subscription_tier=Free的记录。

三、统计查询实现 #

如果是查自然月的各层级推荐人数，直接按stat_month聚合即可，示例SQL：

SELECT 
  subscription_tier,
  COUNT(DISTINCT referee_id) AS user_count
FROM referral_subscription_event
WHERE referrer_id = ? -- 传入待查询的推荐人ID
  AND stat_month = ? -- 传入待查询的月份，比如202405
  AND (SELECT is_valid FROM referral_relation rr WHERE rr.referee_id = referral_subscription_event.referee_id) = 1
GROUP BY subscription_tier

如果遇到跨月升降级、月中升降级的场景，需要按状态实际生效时长占比/生效时点统计的话，直接通过effective_start_time和effective_end_time判断记录的生效周期和目标统计时间段是否有交集即可，逻辑灵活度远高于单表更新字段的方案。

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