校园大使需要查询校园用户的活跃度（最近7天登录、观看视频数）、推荐内容（用户兴趣），如何设计数据库模型和查询优化？请考虑数据量、查询复杂度、性能。
答案
1) 【一句话结论】：采用星型模式结合宽表与时序表设计，通过事实表（用户行为宽表）聚合行为数据并嵌入维度信息，时序表按时间分区存储7天活跃度，结合索引、分区和缓存优化查询，平衡数据量与查询复杂度。
2) 【原理/概念讲解】：星型模式是数据仓库经典模型，核心是事实表（行为聚合表）与维度表（上下文信息表，如用户兴趣、时间）。宽表是事实表的扩展，将维度信息直接嵌入事实表，减少连接操作；时序表专门存储时间序列数据（如7天行为轨迹），利用时间分区优化查询。类比：事实表像“行为记录本”，维度表像“标签库”，宽表是“带标签的行为记录”，时序表是“时间轴上的行为轨迹”，这样查询活跃度时直接聚合字段，推荐内容通过标签快速匹配。
3) 【对比与适用场景】：
| 模型 | 定义 | 特性 | 使用场景 | 注意点 |
|---|---|---|---|---|
| 事实表（宽表） | 聚合用户行为（登录、观看视频）的聚合数据 | 存储行为ID、用户ID、时间戳、聚合值（如登录次数、视频数），直接关联维度信息 | 查询用户活跃度（聚合行为）和兴趣关联 | 数据冗余，需定期更新维度信息 |
| 用户兴趣维度表 | 存储用户兴趣标签（如“短视频”“游戏”） | 存储用户ID、兴趣标签ID、标签名称 | 推荐内容分析（用户兴趣匹配） | 标签可能动态变化，需维护更新 |
| 时序表（活跃度） | 存储用户最近7天行为序列（登录、观看视频） | 存储用户ID、行为类型、行为时间、行为次数，按时间分区（如按天分区） | 查询7天内的活跃度（如最近7天登录次数、视频观看数） | 时间序列数据量大，需按时间分区优化查询 |
4) 【示例】：伪代码（SQL示例，宽表设计下聚合字段直接存储，减少连接）：
- 事实表（用户行为宽表）：
user_behavior_fact(user_id, behavior_type, behavior_count, behavior_time, interest_tag_id, login_count, video_watch_count, created_at) - 用户兴趣维度表：
user_interest_dim(user_id, interest_tag_id, interest_name) - 时序表（活跃度）：
user_activity_time_series(user_id, behavior_type, behavior_time, behavior_count, partition_date)
查询示例（查询用户活跃度与兴趣）：
SELECT
u.user_id,
u.login_count,
u.video_watch_count,
i.interest_name
FROM
user_behavior_fact u
JOIN
user_interest_dim i ON u.user_id = i.user_id
WHERE
u.behavior_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND u.behavior_type IN ('login', 'video_watch')
GROUP BY
u.user_id, u.login_count, u.video_watch_count, i.interest_name
ORDER BY
u.login_count DESC;
5) 【面试口播版答案】：
面试官您好，针对校园用户活跃度和推荐内容查询，我建议采用星型模式结合宽表与时序表的设计方案。核心思路是：用事实表（宽表）聚合用户行为数据（如登录、观看视频的次数），直接嵌入用户兴趣标签等维度信息，减少连接操作；同时用专门的时间序列表存储7天内的行为轨迹，按时间分区优化查询。具体来说，事实表会包含用户ID、行为类型、行为次数、行为时间，以及用户兴趣标签ID等，这样查询活跃度时，可以直接聚合字段，不用额外连接维度表。对于推荐内容，通过用户兴趣维度表关联标签，快速匹配用户兴趣。查询优化方面，对事实表按用户ID和时间分区，建立索引（如user_id和behavior_time的复合索引），并考虑缓存常用查询结果，平衡数据量与查询复杂度。这样既能高效查询7天活跃度，又能快速获取用户兴趣，满足校园大使的运营需求。
6) 【追问清单】：
- 问：如何处理数据量增长（如用户数百万级，行为数据每天几十万条）？
答：采用时间分区（按天、周分区），定期归档旧数据，并使用列式存储（如Parquet）优化查询性能。 - 问：如果用户兴趣标签动态变化，如何更新？
答：通过触发器或ETL流程，实时更新用户兴趣维度表，确保推荐内容及时反映用户最新兴趣。 - 问：查询性能如何监控？
答：使用数据库监控工具（如Prometheus+Grafana）监控查询延迟和资源使用，定期分析慢查询日志，优化索引或分区策略。 - 问：是否考虑缓存？
答：对高频查询（如用户活跃度Top N、热门兴趣标签）使用Redis缓存，设置合理的过期时间，减少数据库压力。
7) 【常见坑/雷区】：
- 坑1：事实表与维度表混淆，导致查询时频繁连接，降低性能。
避免方法：明确事实表存储行为聚合，维度表存储上下文信息，宽表设计减少连接。 - 坑2：时序表未按时间分区，导致查询7天数据时全表扫描。
避免方法：按时间分区（如按天分区），并建立分区索引，加速时间范围查询。 - 坑3：宽表设计导致数据冗余，影响数据一致性。
避免方法：定期同步维度信息（如兴趣标签），或使用主键外键约束，确保数据一致性。 - 坑4：未考虑推荐内容查询的复杂度（如用户兴趣与行为联合查询）。
避免方法：在事实表中嵌入兴趣标签，或建立兴趣标签与用户ID的索引，优化联合查询。 - 坑5：缓存策略不合理，导致缓存击穿或雪崩。
避免方法：设置合理的缓存过期时间，并实现缓存穿透、雪崩的应对策略（如互斥锁、热点数据预热）。