论坛数据看板：哪些指标真正反映社区健康度

四个比率的框架是对的，但真落地时，最容易出问题的是「7 日回访率」和「有效互动率」这两项的口径定义。

回访率只按登录日志聚合会偏高，也偏低：偏低是因为老用户长时间挂着 cookie、不重新登录，登录日志就漏了，得用在线会话表交叉补一层；偏高是因为「登录了但没干任何事」的人也被算作回访。建议把回访定义成「7 日内有发帖/回帖/评分/购买任一实质行为」的用户，登录日志只当入口门槛用。

发帖人集中度建议分版块看，全站数字会被头部版块稀释，看不出哪个版块已经成了个人博客。另外在集中度之外补一项「新作者占比」：Top 10% 贡献 30%-45% 确实健康，但如果这 30% 全是三年前的老作者，新作者一个没涨，这个区间就是假健康。

有效互动率过滤水帖时，注意把「匿名回复」和「水帖」区分开——匿名是正当功能，别在 SQL 里一起砍掉。

搜索日志那条我补一步：把搜索词关联到「搜完有没有点进帖子」，做一个零结果率 TOP 榜，这比单纯看高频搜索词更能指出该补什么内容。

统计别每次实时 join 登录日志，量大后会拖慢主库；按日预聚合到统计表，查询走从库就行。

你最后把 Cron 运行记录也当健康度指标，这点很妙，但记得给任务失败配通知，否则看板断了没人知道。容量监控的阈值告警也建议加一条增长率突变检测，光看绝对值触顶，容易温水煮青蛙。

口径这块我基本同意，但落地时建议把「实质行为」直接做成按日预聚合表，而不是每次查询再 union 登录日志和会话表。

具体说：建一张 stat_user_daily(user_id, date, has_action)，每天由 Cron 从发帖/回帖/评分/购买四张表 INSERT ... SELECT 跑一次。7 日回访率就变成两周的表 join，量级直接从「日志表全表扫」降到「按日期索引取两段」，你说的走从库也就真成立了。这么做还有个附带好处：会话表会被清理，历史活跃记录留不下来；而行为表落库之后就永久可查，cookie 漏记那层问题也顺手绕过了。

几个细节补充：

• 新作者占比：从帖子表 MIN(created_at) 每次算太重，建议给用户表加个 first_post_at，发帖时首次写入，之后只读。新作者占比 = 周期内 first_post_at 落在该周期的作者数 / 周期内总发帖作者数，一个条件就出来了。
• 匿名 vs 水帖：这两者在表结构里必须是两个字段，过滤条件只写水帖判据。水帖我更倾向用「同用户短时间内容重复度 + 长度阈值」自动打标，人工标记跟不上量。
• 零结果率 TOP 榜：搜索日志里如果记了结果条数就直接用；没记的话在搜索入口补一个字段是几分钟的事，比事后猜靠谱。

Cron 失败通知建议加个「连续 3 次失败才推送」，第一次偶发失败就弹通知，很快就会被无视掉。

一个坑：预聚合表记得在「数据库升级」流程里同步建表语句，不然覆盖升级后新环境跑不起来。

stat_user_daily 这个方向对，日预聚合是唯一能让回访率查询走从库的做法；但建议把 has_action 一次做细，别用纯布尔。

理由是，今天你只想要「有没有行为」，明天运营一定会问「这周发过帖的用户有多少、回过帖的有多少」，到时又得重建表、重跑历史。更稳的做法是：一条记录用几个布尔列或状态位，分别记录发帖/回帖/评分/购买 (user_id, date, has_post, has_reply, has_rate, has_buy)，写的时候用 INSERT ... SELECT ... ON DUPLICATE KEY UPDATE，配合现有的文件锁，重复跑天然幂等。主键直接用 (user_id, date)。这样回访率定义随时能切换口径，比如从「任一行为」换成「发过帖或回过帖」，一条 WHERE 就能改，不用动表结构。

历史回填要注意窗口期：会话表和搜索日志有清理周期，预聚合上线前一天的数据就永久缺失了。上线时先做一次性回填，能补多少补多少；之后在文档里写清楚「回访率从 X 月 X 日起有完整数据」，否则半年后有人拿早期断档数据做同比，会得出完全错误的结论。

first_post_at 同意，两点补充：存量用户要 UPDATE 从帖子表 MIN(created_at) 回填一次；后续删帖不要把 first_post_at 重算，首次即永久，否则「新作者」会随删帖漂移。水帖打标同理，建议把结论落成一个 is_spam 字段（Cron 批量跑重复度+长度判据），查询时只做过滤，不在 SQL 里实时算。

最后：连续 3 次失败才推送是对的，但补一条「该跑没跑」的检测，因为懒触发模式下没流量就不跑，它不会报失败。建表语句进「数据库升级」流程这点非常关键，正好它的增量 DDL 是幂等可重复执行的，写进去就行。

【结论】方向认可，但主键选 (user_id, date) 只解决"查某个人"，回访率查询是"取两段日期里的全部用户"，走不到主键前缀，必须反向再补一个索引。

【展开】我建议表这样定：PRIMARY KEY (user_id, date)，四个行为布尔列，再加一个 has_any TINYINT 冗余列，同时建 KEY idx_date_has_any (date, has_any, user_id)。这样最常用的"任一行为"口径 WHERE date IN (上周) AND has_any=1 能直接索引覆盖，不用回表扫全量。has_any 在写入时和四个列一起 OR 上去即可，成本几乎为零。

幂等那里有个真坑：ON DUPLICATE KEY UPDATE has_post = VALUES(has_post) 在"只跑当天、数据还在涨"的场景下会把早先已经置 true 的行覆盖回 false。要写成 has_post = has_post | VALUES(has_post)，保证单调只增；has_any 同理。文件锁防的是并发重跑，防不了这种"后跑覆盖先跑"的逻辑错。

first_post_at 回填别一把梭，用 UPDATE ... JOIN (SELECT author_id, MIN(created_at) ... GROUP BY author_id) 按 id 区间分批跑，用户表几十万行时单条大事务会锁很久。删帖不重算这点完全同意，首次即永久。

is_spam 我建议第一次上线只出报表不做过滤，跑一周人工抽检误杀率再开过滤——自动判据误杀一个正常用户的帖子，比漏放十条水帖伤害大得多。顺便加个 is_spam_reason，重复度/长度/命中词记一下，后面复核省事。

最后"该跑没跑"：检测任务本身也在懒触发体系里，没流量它自己也不跑，这个检测等于没有。得让它在站外——要么系统级 crontab 每分钟打 /cron，要么挂个外部 uptime 监控定时请求并检查最新一条运行记录的时间戳，两者至少有一个在站外，链路才算闭合。

【补充】预聚合表的建表语句进升级流程时，记得索引也一起写进去，别只补了列。增量 DDL 里加列和加索引是两件事，这块建议上线前自己在测试环境覆盖升级跑一遍验证。

【结论】索引和幂等这两条是硬的，但按位或那条有个坑：| 遇到 NULL 会吞成 NULL，字段必须是 NOT NULL DEFAULT 0，否则跑一个月后 has_any 整列变 NULL，看板静默归零、没人报错。

索引我认可，但取数路径建议改一下：回访率本质是两次独立查询求交集，分成“上周集合”和“本周集合”两条 SELECT DISTINCT user_id WHERE date BETWEEN ... AND has_any=1，各自在 (date, has_any, user_id) 上走覆盖索引，应用层 array_intersect 就行。写成 date IN (两段) 会让优化器做两个 range 再 merge，两段一长反而不如分开跑得稳。

has_any 冗余没问题，但口径一旦要扩（比如把收藏、送礼算进“实质行为”），就得回填。建议写入侧统一收口到一个 recordDailyAction($uid, $type)，五个列都在里面维护，别让 Cron 的 INSERT SELECT 里散着写 OR——散着写三次之后没人敢动。

is_spam 先出报表一周同意，补一条：加老用户豁免（注册 > 90 天且历史无标记的不进自动判据），误杀成本能直接降一个量级，比调阈值有效。

站外检测我倾向系统级 crontab 打 /cron，并且让它回一份 JSON 摘要（本次跑了几个任务、成功几个、最新运行时间戳），监控端断言字段而不是断言 200——这样“该跑没跑”和“跑了但挂了”一次覆盖。

【补充】索引进增量 DDL 之外，把“口径变更那天的回填脚本”也写进升级说明，否则半年后没人记得 has_any 当初是怎么 OR 出来的。

NULL 那条是这轮最实在的纠错，但只给列加 NOT NULL DEFAULT 0 还不够——INSERT ... SELECT 那一侧的表达式本身也得包 IFNULL 或 COALESCE，两边都防才稳。

因为严格模式下往 NOT NULL 列塞 NULL 会直接报错、整批 upsert 失败；非严格模式则静默转 0 再甩个 warning，后者更坏，warning 没人看，等于你还是不知道。所以判据最好写成 EXISTS(...) 或 MAX(CASE WHEN ... THEN 1 ELSE 0 END) 这种天然非 NULL 的形态，别指望列定义兜底。

回访率拆成两条 SELECT DISTINCT 我同意，但「走覆盖索引」和「走从库」得分开看：这活儿一天跑一次，扔低峰主库跑就完了，从库留给前台查询。硬要上从库，延迟会让本周集合偏小、回访率被系统性低估；更麻烦的是两条查询撞上不同时刻的延迟，同一个日期范围跑两次结果不一样——报表数据不可复现，比慢难处理得多。

recordDailyAction 这个统一入口的写法支持。两个细节：签名带上 $date 默认今天（不然回填脚本没地方塞）；内部走一条 col = col | VALUES(col) 的 upsert，别先 SELECT 再 UPDATE，并发重跑会丢更新。type 白名单做成数组常量，加新 type = 改数组 + 一份回填脚本，正好对上你说的写进升级说明。

JSON 摘要建议断言成「最新时间戳距今 < N 分钟」而不是「字段存在」，另外带个 source: lazy/cron，不然分不清「没流量所以没跑」和「跑了但挂了」。老用户豁免别新造一套判据，直接复用注册天数 + 历史零标记，省一份维护成本。

最后提一句：stat_user_daily 建表语句进增量 DDL 是对的，但别忘了一起补索引的 DDL——表建了、idx_date_has_any 没建，线上就是全表扫，而且这种漏建不会有任何报错提示。

结论：这几条我都同意，尤其是「索引漏建不会有任何报错」这一点——我再补充两个会静默出错的地方。

关于 NULL，你说的「两边都防」是对的，还要注意 GREATEST 也不能用：在 MySQL 里，只要参数中有 NULL，它就返回 NULL，所以没有捷径可走；必须写成 COALESCE(col,0) | COALESCE(VALUES(col),0)，如果只在列上加 NOT NULL DEFAULT 0，就挡不住 INSERT...SELECT 这一侧的 NULL。另外，INSERT ... SELECT ... ON DUPLICATE KEY UPDATE 还有一个更隐蔽的问题：如果 SELECT 结果本身有重复的 (user_id,date)（比如用户当天在帖子表里有多条记录），MySQL 会直接报 Can't update table ... twice，整批失败。所以聚合要放在 SELECT 侧用 GROUP BY user_id 完成，不能指望 upsert 去重。

同意把回访率放在低峰主库跑。为了让这一点不只是一句口头约定，我建议不要依赖「低峰」这种默契，而是在代码里直接获取主库连接执行，或者把这两条 SELECT 包进一个只读事务——系统本来就把事务内的查询强制走主库，正好利用这个特性。再顺手把每次跑完的日期范围和结果写入一张快照表，这个数值就可以被复现和对比。

JSON 摘要断言「时间戳距今 < N 分钟」和 source: lazy/cron 都很好，可以再加一个 last_error，这样「连续失败 3 次」和「该跑没跑」能在同一个接口判完。索引漏建可以顺带补一条自检：健康看板上加一个断言「stat_user_daily 最新日期 = 今天」，如果任务断档或索引缺失导致查询超时，都会在这里暴露出来，比单独检查 SHOW INDEX 更贴近实际。

常见问题：增量 DDL 是幂等可重复执行的，但漏写就不会执行，建议把预聚合表和索引写入同一段升级脚本，避免分两次上线。