做培训系统时通知和消息这模块经常被一句话带过去“给用户发个站内信就行”。等真正开始建表才发现问题没这么简单有的通知是给所有人广播有的是指定某个学员有的是课程即将开始的提醒还有的是作业批改后的结果反馈。这些消息形态不同、发送时机不同、用户操作也不同如果上来就建一张message表后面迟早要重构。我经历过不止一次在这种模块上返工所以这篇把培训系统里通知和消息的表设计思路完整梳理一遍。文章会从需求拆分、核心表结构、模板与触发表、已读撤回等扩展点、索引与事务、不同方案取舍这些维度展开适合正准备动手设计消息模块的后端开发同学参考。1. 培训系统里的通知消息先按形态拆明白1.1 先想清楚通知和普通业务数据的区别通知和消息虽然操作上很像“插入一条记录”但它跟普通业务表的本质区别在于消息是写给用户看的用户会反复查询自己的消息列表和未读数量而且每条消息的生命周期可能很短也可能很长。课程信息、学员信息这些是核心业务数据删除要谨慎而一条“恭喜你完成课程”的通知过几个月可能就没人关心了。如果把通知当成普通业务数据去设计最常见的后果就是消息表跟业务表耦合太深。比如为了显示“这门课的提醒消息”直接在消息表里存course_id然后在业务查询里反复JOIN课程表。一旦课程被修改或者删除消息内容也跟着变用户看到的历史通知和实际课程对不上。我的建议是通知模块在设计上要独立成域不完全依赖培训业务表。它记录的是“某时某刻发生了什么”而不是“业务对象现在长什么样”。业务对象的信息通过biz_type和biz_id去关联查询一旦发生通知内容就快照存下来不再跟着业务表实时变化。1.2 从用户视角把消息分成四类设计表之前把消息形态分清楚后面所有字段和索引才有依据。我在培训系统里通常把消息分成四类第一类是系统公告面向全部用户比如培训平台升级维护、新课程上线。这种消息一对多广播所有人看到的都一样基本不存在个性化的内容。第二类是业务提醒比如课程开课提醒、作业提交截止提醒、考试安排通知。这类消息往往带有一个发生时间可能提前几天生成也可能到时间才触发。第三类是个人结果反馈比如考试成绩发布、作业批改结果、证书发放通知。这类消息针对单个用户而且用户操作后通常希望保留历史记录。第四类是用户间互动消息比如学员向讲师提问、讲师的回复。这类消息有明确的双向性需要关注“会话”的概念跟前面三类单向通知的差异很大。四类消息对表结构的要求不一样前三类可以用“一条主消息用户关系表”的方式处理但第四类如果量不大可以单独拆会话表如果量很大甚至要单独设计消息流。大多数培训系统里第四类不是核心可以先跟前三类一样处理但接口上预留类型区分。1.3 核心元数据先定下来发送方、接收方、渠道和时机不管哪种消息表里至少要能回答这几个问题谁发的、发给谁、通过什么渠道、什么时候发、发了没有。这里“谁发的”不要只存一个user_id因为很多消息是系统自动触发的。我会用sender_type和sender_id两个字段sender_type区分系统、管理员、讲师、学员sender_id存具体用户ID系统触发时sender_id为空或者存任务ID。接收方也不是所有场景都是单个用户。一个课程开课提醒接收方可能是选课的全部学员一个班级公告接收方是班级成员。这种“动态接收人”的设计很关键我后面会单独讲。渠道也要提前想清楚。站内信、短信、邮件、企业微信模板消息这些渠道的消息字段不一样。比如短信有字数限制站内信可以带HTML。表设计上建议用channel字段区分但不要把各渠道的扩展字段都堆在主表里可以预留一个extra JSON字段放渠道特有的参数。时机是另一个容易漏掉的地方。很多通知是在业务动作发生时立刻触发的但也有定时通知比如开课前一天上午10点发送。主表里需要planned_send_time和actual_send_time两个字段分别记录计划发送时间和实际发送时间配合一个status字段做发送状态流转后面要接定时任务也方便。2. 表结构第一版用两张核心表支撑大部分场景2.1 通知主表 notification一条消息一份内容第一版表设计我不建议一上来就搞六七张表先把核心的两张表建好跑通业务再慢慢加。第一张表是通知主表存的是消息本身不关心发给谁。字段设计如下字段名类型说明idbigint主键notify_novarchar(64)业务编号便于人工排查notify_typevarchar(32)公告、提醒、反馈、互动titlevarchar(255)消息标题contenttext消息内容可按渠道存不同格式sender_typevarchar(16)发送方类型system/user/adminsender_idbigint发送方用户ID系统消息为空biz_typevarchar(32)关联业务类型如course、exambiz_idbigint关联业务IDchannelvarchar(32)渠道inbox/sms/email/workprioritytinyint优先级用于控制推送顺序statustinyint草稿、待发送、发送中、已发送、失败、撤回planned_send_timedatetime计划发送时间actual_send_timedatetime实际发送时间extrajson扩展信息expired_atdatetime过期时间用于自动下线公告created_atdatetime创建时间updated_atdatetime更新时间is_deletedtinyint逻辑删除标记这个表最关键的设计是biz_type和biz_id。不要在通知表里冗余存“课程名称”等业务字段查询的时候通过biz_type和biz_id关联业务服务。这样课程改名后历史通知虽然显示的是快照内容但如果你需要跳转详情仍能通过业务ID定位到最新信息。content字段要小心。如果你只做站内信text够用如果同时要发短信和邮件建议再加一个content_text纯文本版本。有的项目会在content里存JSON把渠道模板分别放进去这也可以但查询和统计时会麻烦。我的做法是正文存text额外字段放extra短信内容单独用模板渲染。2.2 用户通知关系表 user_notification解决“发给谁”和“已读状态”第二张表是用户通知关系表这是通知模块能灵活支持定向和广播的关键。字段设计如下字段名类型说明idbigint主键notification_idbigint对应通知主表IDuser_idbigint接收用户IDis_readtinyint是否已读0未读 1已读read_timedatetime阅读时间receive_timedatetime用户收到时间is_withdrawntinyint是否已撤回withdrawn_timedatetime撤回时间created_atdatetime创建时间updated_atdatetime更新时间看到这张表可能有人会问广播通知给一万个用户user_notification就要插一万条记录是否太浪费这个问题要分场景。如果系统用户量在几万以内直接插关系记录最简单查询用户消息列表时一条SQL就能把内容带出来未读状态天然在关系表里不需要额外维护。如果用户量达到几十万上百万广播全量插记录确实会膨胀很快。这时候可以改为不落关系表只记录通知主表和接收人规则查询时动态判断用户是否在接收范围内。这种方案省空间但费CPU而且已读状态要单独放Redis或者另一张窄表。大多数培训系统的用户量远没到大厂级别我建议先用关系表后面真遇到性能瓶颈再优化。表设计里预留一个message_target_type字段可以做区分。这里有一个容易踩的坑列表查询时如果每条消息都要JOIN通知主表拿内容通知主表数据量大以后IO会成问题。所以关系表上一定要建联合索引推荐组合是(user_id, is_read, id)既能支持用户消息列表分页也能高效统计未读数量。3. 模板与触发表让消息可维护而不是在代码里拼字符串3.1 消息模板表把内容变量从代码里解放出来培训系统的通知内容高度重复最常见的就是“XX课程将于XX时间开课”“您的作业已批改得分XX”。很多项目把这些文案直接写在代码里等到运营想改措辞就得发版。正确做法是引入消息模板表。模板表字段id、template_code、template_name、title_tpl、content_tpl、channel、variables(json类型声明这个模板支持的变量名)、status、version、created_at、updated_at。这里核心是title_tpl和content_tpl里面用占位符表示变量比如”{course_name}课程将于{course_start_time}开课“。渲染时从变量字典里取对应文案替换。为什么强调version字段因为模板改文案时历史通知内容不能跟着变。通知产生时应该把渲染后的内容快照存入notification表的title和content模板后续修改不影响已经发出的消息。如果要记录模板起源notification表里可以留template_id和template_version字段排查问题时会非常有用。3.2 通知任务表和触发记录消息不能只管发出去第二版设计还要加一张通知任务表。当运营在后台勾选一万个学员选择“发送开课提醒”这个操作可以同步循环发送但更好的做法是创建一条通知任务记录发送批次由后台异步执行。通知任务表字段id、task_no、notification_id、target_rule(JSON接收人查询条件)、total_count、success_count、fail_count、send_status、retry_count、start_time、finish_time。用这张表的好处是批量通知真正执行时可能失败有了任务表就能重试运营也能在管理后台看到这个批次发了几条、成功几条。同时还要一张发送流水表记录每条通知通过哪个渠道发送的结果。尤其短信、邮件这类外部渠道经常遇到渠道方返回“发送失败”或“用户拒收”如果只有任务表的成功失败总数没法定位是哪个人没收到。流水表字段id、task_id、notification_id、user_id、channel、channel_msg_id、send_status、fail_reason、created_at。channel_msg_id是渠道方返回的消息ID后续查渠道侧问题只能靠它。3.3 定时通知加一个调度状态让消息按计划走培训系统里大量通知是定时触发的比如“开课前24小时提醒”“考试日前3天提醒”。这类需求如果每次都在业务代码里判断当前时间再拼一条通知逻辑会非常分散。我的做法是业务触发时只生成通知主表记录status设为pending_sendplanned_send_time设为计划发送时间由统一调度任务每隔一分钟扫描一次planned_send_time小于当前时间且status为pending_send的记录把它们置为sending并执行发送。这个设计的优点是把“什么时候发”和“发什么内容”解耦了。业务方不用关心调度细节只需要在创建通知时带上planned_send_time。调度层统一控制频率后续想接入延迟队列、消息队列也容易不用改动业务方代码。需要强调的一点是扫描任务一定要按状态加索引否则表大了以后定时任务会越跑越慢影响其他业务。4. 已读、撤回和离线消息三个高频扩展点一次到位4.1 已读状态放哪决定了未读角标的查询效率用户消息列表右上角那个未读红点看似简单做起来容易栽跟头。常见做法是查user_notification表count(is_read0)来统计未读数量。用户量小这没问题用户操作一多每次进系统首页都count一次数据库压力就上来了。优化思路分两步第一步给(user_id, is_read)加联合索引让count走索引覆盖避免回表第二步如果还是很慢就再加一张user_message_stat表字段就是user_id、unread_count、last_read_time在发送通知和用户读消息时异步更新。这样未读数不需要实时count而是读统计表的缓存值性能高很多。但统计表的更新存在一致性问题。用户并发读消息、通知发送延迟、异步任务失败都可能导致未读数不准确。我的经验是统计表只服务未读角标这种展示场景真正详情页的已读状态仍然以user_notification表为准。两者不一致时允许角标短暂不准用户点进列表刷新一下即可没必要强一致。4.2 撤回消息时怎么处理已读状态管理员发错通知或者课程时间调了旧提醒要收回。撤回不是简单的delete因为用户可能已经读了这条消息。我的做法是撤回逻辑在user_notification表上打is_withdrawn标记同时更新通知主表状态为withdrawn。用户查询消息列表时过滤掉is_withdrawn1的记录。如果消息未读就被撤回未读数也要相应减掉。但“已读消息撤回”怎么显示是个产品决策。有的产品要求撤回后用户端彻底不可见有的要求显示一条“该通知已被撤回”的占位。这会影响表设计如果彻底不可见查询过滤is_withdrawn即可不需要额外存如果要显示占位撤回后就不能过滤只能把content字段替换为撤回提示。我倾向于占位方案因为用户可能已经读到一半突然消失会让人困惑留一个占位更透明。占位内容可以在撤回时更新notification表或者在查询时根据is_withdrawn字段动态判断。4.3 离线消息和消息列表分页的取舍培训系统里用户可能几周都不登录登录后要能看到所有历史通知这就是离线消息的本质消息持久化在数据库用户随时拉取。所以站内信天然支持离线消息不需要额外处理。需要处理的是消息列表分页。常规做法是用user_notification的id做游标分页即WHERE user_id? AND id ? ORDER BY id DESC LIMIT 20。这个方案比LIMIT/OFFSET稳定深翻页也不会变慢。为什么不用通知主表的id因为一个用户看到的列表顺序取决于他收到消息的时间顺序关系表的id递增就是这个顺序用它做游标最自然。列表查询还要避免一次查太多字段拖慢速度。常见的性能杀手是把content全文带出来用户列表页往往只显示标题、时间和已读状态进入详情才需要正文。我建议列表查询只select必要字段content留在详情查询再取。这一条看着是小事但用户几千条消息时返回体大小差好几倍。5. 索引、事务和性能消息量不大但同样不能乱来5.1 建索引前先想清楚查询路径消息模块的查询大概就几类用户查自己的消息列表、查未读数、管理端查通知发送状态、定时任务查待发送消息。每一条查询路径对应一个关键索引我的建议是user_notification表(user_id, is_read, id)联合索引支撑用户消息列表游标分页和未读统计。user_notification表(notification_id)索引支撑撤回和后台查询某条通知的所有接收人。notification表(status, planned_send_time)联合索引支撑定时任务扫描待发送消息。notification表(biz_type, biz_id, status)联合索引支撑业务侧查某个课程的所有通知。notification表(notify_type, created_at)联合索引支撑运营后台按类型和时间筛选。这里特别说明为什么不是(user_id, created_at)而是(user_id, is_read, id)。消息列表基本都要按时间倒序id的递增顺序和创建时间一致用id排序能走索引避免文件排序再配合is_read过滤已读状态统计未读时不需要额外扫描。如果你的数据库主键不是自增ID而是雪花ID那排序字段就要显式用created_at。5.2 批量发送通知时的事务边界给一万个学员发一条通知最容易犯的错就是用一个事务插入一万条关系记录。我的第一个版本就这么干过事务时间过长数据库连接被长时间占用业务高峰期直接把其他接口拖垮了。正确的做法是以任务表为边界把事务控制在一个较小的粒度。比如每个事务只处理500条user_notification记录循环执行每批次独立提交。某一批失败就记录到任务表的fail_count整批重试不要整体回滚。这样即使渠道方挂了任务表还在可以等恢复后继续发送。另一个事务陷阱是创建通知然后立刻查询未读数量。假设你先插notification再插user_notification统计服务刚好在这之前查了一次这个用户可能暂时看不到未读数变化。这种“最终一致”在消息模块可接受。如果你的产品要求发消息后未读数立刻同步那就只能在同一个事务里同时更新统计表但并发高时要处理锁冲突代价不小。5.3 深分页和未读角标的几个性能解法如果你已经按游标分页做了消息列表深分页问题基本不会出现。但还有一种情况管理后台查看某条通知的所有接收人要显示“已发10000人、已读5000人”这个管理列表可能用LIMIT/OFFSET分页数据量大以后就会慢。解法是后台列表也改成游标分页或者固定加上时间范围过滤避免全表扫描。未读角标在用户量大的时候数据库count确实扛不住。除了前面说的统计表方案还有一个是本地缓存加延迟失效用户进入首页时先取缓存值同时后台在发送和读取消息时更新缓存。如果缓存更新失败就给一个过期时间比如五分钟内失效下次重新查数据库。这个方案最实用也不引入额外中间件。给表加索引不是越多越好。有一个常见的坑在user_notification表上同时建(user_id, is_read)、(user_id, id)、(is_read, notification_id)多个索引结果数据量一大写入变慢索引空间膨胀。索引设计一定要回到查询路径一个查询路径对应一个最合适的联合索引多余索引删掉。6. 站内信、用户会话、广播通知三种典型方案的取舍6.1 直接落单表的简单方案适合什么情况如果培训系统刚起步用户量在一万以内通知类型也不复杂直接用一张表搞定也不是不行notification_id、sender_id、receiver_id、title、content、is_read、send_time。所有消息不管广播还是定向都插一条记录接收人receiver_id为空代表所有人。这个方案的最大优点就是简单。写入一条SQL查询也是一条SQL不需要JOIN两张表。缺点是广播给一万个用户就要插一万条记录而且每次查询最近消息时要扫描全表再去重。这个方案只适合快速上线Demo或者给内部小团队用的工具。如果是要长期迭代的产品我建议至少按前面的两张表来设计因为后面的扩展基本都是围绕关系表展开的越早改越省事。6.2 用户维度表什么时候才是必需品当用户量起来或者业务要求消息主表和接收人关系明确分离时用户通知关系表就是必需品。它解决了三个单表方案解决不了的问题消息内容和接收人解耦、已读状态独立存储、批量通知和定向通知统一处理。比如同一个开课提醒接收人是某班级30人这30人里可能有三个已经退课了。如果消息跟着接收人字段直接记录用户退课时就不知道该拿这些历史消息怎么办。存在user_notification表里退课用户的消息可以单独保留也可以定时清理不影响其他用户的列表。这个灵活性在后期运营中非常重要。6.3 广播大通知的汇总表方案先别急着设计除了前面提到的动态判断接收人方案还有一个中庸方案是“通知主表广播标记用户读取表”。广播消息只存notification表不落关系表用户第一次拉取消息列表时把主表的广播消息写入自己的关系表标记为已拉取。这样既避免了全量插入又能让用户看到所有广播。缺点是如果广播消息发布时用户已经离线很久第一次上线时要把几十条广播一次性insert到自己的关系表写入量大。而且每条广播的”已读“在用户拉取后才初始化未读统计的语义要理清。我的建议是培训系统一般用不上这个方案知道有这种思路就行。真到用户量百万级还能用消息队列保证广播消息实时推送到在线用户服务器不认识离线用户则靠拉取弥补。但这是另一个复杂度等级了。7. 我踩过的坑和现在推荐的分层落库方案7.1 教训一通知内容跟业务字段强耦合最早我设计培训系统消息表时直接把课程名称、作业标题这些业务字段冗余存进通知表好处是查询方便。但后来运营要求改课程名称历史通知里的老名称跟着新名称一起变用户莫名其妙收到指向同一课程却出现两个名称的消息。现在我的做法前面也提到了通知表只存biz_type和biz_id内容用模板在发送时渲染快照。这样课程改名对历史通知没有影响。如果需要根据业务ID回看关联查询业务服务即可只是多一次查询但换来的是数据一致性。7.2 教训二撤回消息时未读数忘了减撤回逻辑上线后有个现象通知明明被撤回了用户未读角标还是显示有好几条。原因是撤回时只改is_withdrawn没有处理未读统计。后来我在撤回逻辑里强制检查如果这条用户消息is_read0撤回后要把user_message_stat里的unread_count减一。这个逻辑看着简单但它分布在两个地方漏一处就会造成“幽灵未读”。建议在代码里设计一个消息状态机明确通知从创建到发送、已读、撤回经过的所有状态流转每个流转里对应哪些表要更新全部列出来。消息模块最乱的就是这种跨表联动没有状态机全靠脑子里记早晚出事。7.3 我现在推荐的核心落库结构最后把我目前比较推荐的结构完整列一下供直接参考notification通知主表一条记录是“一件消息”存储消息内容、发送方、计划时间、渠道、状态。user_notification用户关系表一条记录是“某个用户收到了这条消息”存储接收人、已读状态、撤回状态。notify_template模板表存储消息文案模板。notify_task通知任务表批量发送时记录批次和统计数据。notify_send_log发送流水表记录每条消息通过各渠道的发送结果。user_message_stat用户消息统计表存储未读数等聚合数据。这个结构能覆盖培训系统里绝大多数通知消息需求站内信、短信、邮件、定时提醒、批量公告、撤回、未读角标。如果后面要加实时聊天或者IM那需要单独设计会话和消息流表跟这套通知体系分开。我个人测试下来这套结构在几千到几十万用户量级都够用没有明显性能瓶颈。最后再分享一个经验消息模块的表设计永远不要天真地以为“发一条消息”只是insert一行。从业务触发、模板渲染、批量发送、渠道投递、状态回写、未读统计到消息撤回每一环都可能出问题表结构不把这些环节的数据留清楚后面排查问题就像在没有日志的系统里找bug一样痛苦。
