REST后端用户活动日志模块实现最佳实践咨询

首先直接说结论：你提的两个初步方案都有明显缺陷，通用活动日志表的方向是对的，只要做好索引优化和场景分层，完全可以兼顾通用性和性能，不需要做硬耦合的表结构改动。 #

先排除不可行的方案 #

绝对不要在project表加自定义字段存访问相关数据：项目是多用户共享的实体，你加的字段只能存单个值，根本没法对应不同用户各自的访问时间，就算用JSON字段存全量用户的访问记录，后续更新、查询的锁冲突、解析成本都会高到离谱，属于完全违反数据库设计范式的坏方案，后续要加收藏、编辑等其他行为维度的时候根本没法扩展。
也不要查全量行为日志到应用内存做排序：当用户行为日志积累到上万条之后，拉取全量数据、内存去重排序的延迟会非常高，既浪费数据库带宽，又占应用服务器内存，完全没必要。 #

第一步：优化通用活动日志表设计 #

你原本构思的user_action表是通用行为埋点的正确方向，只要补几个关键约束和索引，就能直接在数据库层面完成排序逻辑，不需要内存计算：
表核心字段保持你原来的设计即可：

{
  "id": 1,
  "user_id": 1,
  "target_document_id": 2,
  "action": "view",
  "target_entity": "project",
  "created_at": "2022-06-29 08:22:00"
}

必须加的联合索引：(user_id, target_entity, action, created_at DESC)，这个索引完全覆盖了「查某用户对某类实体做某类行为的记录、按时间倒序」的查询场景，数据库不需要回表就能拿到结果，性能非常高。
查询项目列表的时候直接用SQL聚合完成排序，不要拉全量日志：

SELECT p.*, MAX(ua.created_at) AS last_view_time
FROM projects p
-- 必须关联用户-项目多对多关系表，过滤用户有权限访问的项目，避免越权
INNER JOIN user_project_relations upr 
  ON p.id = upr.project_id AND upr.user_id = #{currentUserId}
LEFT JOIN user_actions ua 
  ON ua.user_id = #{currentUserId}
  AND ua.target_entity = 'project'
  AND ua.action = 'view'
  AND ua.target_document_id = p.id
GROUP BY p.id
-- 最近访问过的按访问时间倒序，没访问过的按项目创建时间兜底
ORDER BY last_view_time DESC, p.created_at DESC;

第二步：高频场景的性能优化 #

如果你的产品用户量很大、项目列表接口QPS很高，每次都聚合查询日志表还是会有压力，这时候加一层轻量缓存做加速即可，不需要改核心表结构：

• 用Redis的ZSet结构存每个用户的最近访问项目记录，key格式为user:recent_view_projects:{user_id}，score存访问时间戳，member存项目ID，每次写入的时候可以裁剪只保留最近100条记录，节省内存。
• 用户点击进入项目的时候，核心接口逻辑正常返回，行为日志、缓存写入都异步做（丢到线程池或者消息队列就行），就算写失败也不影响用户正常使用，后续可以用全量日志表重建缓存，没有硬一致性风险。
• 查项目列表的时候，先从ZSet里拿到用户最近访问的项目ID顺序，再把用户有权限的项目按这个顺序排列，没在最近访问列表里的项目按默认规则（创建时间、项目名排序）补在后面即可，性能可以到毫秒级。

方案的通用性收益 #

这套结构不需要为单个场景做特殊定制，后续其他依赖用户行为的UI逻辑都可以直接复用：

• 要做最近编辑的文档、最近查看的报表排序，只需要改查询时的target_entity和action参数即可，不需要改表结构
• 后续要做常看项目高亮、用户偏好推荐、操作审计等功能，直接基于这张全量行为日志表计算即可，不需要重新做埋点
• 日志存储和业务表完全解耦，就算日志模块出故障，也不会影响项目、用户等核心业务的正常运行

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