最近在做在线学习平台的后端服务接到一个任务编号是“Day02-07”内容为分析产品原型、设计查询指定课程学习状态接口。一开始以为是普通的CRUD真正动手才发现这个接口涉及的远不止一张表和一条SQL还牵扯到状态机设计、进度计算口径、缓存策略这些细节。这篇把整个设计和落地过程整理出来给同样做在线教育、知识付费类产品的后端朋友一个参考。1. 拿到产品原型先把页面元素“翻译”成数据需求1.1 原型图上每一个动态元素背后都是一个数据来源产品经理丢过来一份原型页面上有课程卡片、学习进度条、状态标签、继续学习按钮、最近学习时间。看起来都是UI展示但对后端来说这些都是要提供数据的接口契约。我在收到原型后做了一件事把页面上的动态元素全部列出来挨个标注数据来源。做这个动作的时候最好找产品经理或前端一起确认避免信息差。经过确认后的需求来源分析如下课程状态标签需要后端返回用户与课程的关系状态来源是用户课程关系表。学习进度百分比需要返回已完成章节数与总章节数来源是学习记录表和课程章节表。最近学习时间需要返回最后一次学习操作的时间来源是学习记录表。继续学习按钮跳转位置需要返回当前学习到哪个章节来源同样是学习记录表。课程有效期截止时间需要返回课程截止时间来源是课程表或用户课程关系表。很多后端新手拿到原型会直接开始建表写接口跳过这一步。实际上前后端联调时大部分问题都出在“页面要的数据没在接口里定义”或“接口返回了字段但前端用不上”这两种情况上。先做一张“原型元素→数据字段”的映射表可以省下后面大量沟通成本。1.2 从页面交互反推接口的边界界面上的元素不只是静态展示有些交互行为会直接决定接口的边界。比如原型上有一个“继续学习”按钮点击之后要跳转到上次学习的章节。这个交互看着简单但它说明接口不仅要返回课程状态、进度、时间这几个字段还必须返回“当前章节ID”。否则前端是没法定位“继续学习”的位置的。还有一类场景是如果课程已过期原型的按钮状态是置灰不可点击。这意味着接口必须返回“是否过期”或“deadline”字段前端根据这个字段控制按钮状态。此时单靠一个状态枚举可能不够最好把deadline单独返回方便前端做更灵活的展示。再比如原型课程卡片上展示了“已完成 14/25 章”这个数据用进度百分比虽然能展示但前端如果要做“14/25”这种明细展示后端就需要返回completedSectionCount和totalSectionCount两个独立字段。这就是为什么接口设计不能只拍脑袋定一两个“够用就好”的字段。2. 课程学习状态的数据模型先把状态机和进度口径定清楚2.1 状态枚举不要只拍脑袋定两个值最初设计状态时第一反应就是“未开始”和“已完成”两个状态。但仔细看了原型之后发现不对页面上还有“学习中”和“已过期”这两种标签。如果课程是训练营模式有固定的有效期那么“已过期”是必须存在的状态如果用户报名后还没开始学习那“未开始”也要有。最终状态枚举定了四个not_started未开始用户已报名但没有任何学习记录。learning学习中有学习记录但未达到完成条件。completed已完成达到课程完成条件。expired已过期课程有效期已结束不能再学习。这里有几个细节需要注意。第一状态字段用字符串枚举而不是数字。虽然数据库存储上数字更节省空间但接口层用字符串可读性更好前端switch-case也直观。第二状态流转是有向的不是所有状态之间都能互相切换。主链路是not_started → learning → completedexpired是一个终态由课程有效期决定。如果产品允许“过期后重新激活”那还要把expired → learning这条路径补上并且记录激活时间否则对账容易出问题。2.2 学习百分之进度到底怎么算“进度80%”是怎么定义出来的这个口径在原型阶段就要确认清楚。目前业界常见的有两种口径按章节数完成比例计算即已完成章节数除以总章节数。按视频观看时长计算即已观看总时长除以课程总时长。按章节算的好处是实现简单、数据可靠。学习记录表只需要在用户完成某个章节时插入一条记录统计时COUNT一下就行。按视频时长算的好处是更精细能反映“看了但没看完”的真实情况但问题也明显前端需要频繁上报播放心跳数据量大而且存在“挂机刷时长”的作弊问题。我实测下来最终采用的是按章节数计算。原因有三个第一章节是结构化数据天然有稳定的数量口径第二统计逻辑简单COUNT即可完成不需要依赖复杂的时长汇总第三用户对“完成章节数量”有感知进度条变化明确。2.3 章节完成判定的阈值设计如果按章节数算进度那“什么算完成一个章节”就必须定义清楚。最简单的方式是用户点击“下一节”或播放到视频最后一秒时标记完成。但这种方式很脆弱用户中途退出视频或者把播放器拖到结尾骗进度都会导致判定不准。一个更稳定的方案是前端定期上报播放进度后端判断播放位置超过章节总时长的80%或播放到末尾时将章节标记为完成。阈值可以做成后台可配置的参数默认80%。这样即使播放器中间有几次心跳丢失也不容易误判。不过要注意这里“完成”和“学习过”是两回事。只要用户打开过章节页面就应该记录一条“学习过”的痕迹用于更新最近学习时间只有进度达到阈值才标记“完成”。两个事件分开记录后面查询状态时才会准确。3. 接口定义路径、请求参数、响应结构、状态码3.1 属于查询语义用GET而不是POST查询指定课程学习状态这个接口从语义上讲是一个纯读取操作所以第一版设计定的是GET请求。接口路径设计为GET /api/v1/courses/{courseId}/learning-status课程ID作为路径参数因为它在RESTful语义里是资源的标识。用户ID怎么传如果系统有统一的登录态后端从token里解析用户ID即可不需要前端显式传参。如果没有登录态就作为query参数传例如?userId123。为什么不把courseId也放进query参数或者放进request body因为从资源路径表达语义路径参数比query参数更清晰也更方便网关层做权限校验和日志记录。而用POST body的方式虽然也能实现但语义上不够直观还容易被人质疑“查询为什么用POST”。3.2 响应结构里多给几个字段前端好干活响应结构定义为{ code: 0, message: success, data: { courseId: 1001, status: learning, progress: 56, completedSectionCount: 14, totalSectionCount: 25, currentSectionId: 15, lastLearnTime: 2025-01-10T14:30:0008:00, deadline: 2025-03-31T23:59:5908:00 } }这里每个字段都有它的用途status核心字段前端展示状态标签。progress学习进度百分比前端画进度条。completedSectionCount和totalSectionCount前端可以直接展示“14/25”。currentSectionId用于“继续学习”跳转定位。lastLearnTime展示“最近学习1月10日”这类文案。deadline课程有效期截止时间前端据此判断是否需要展示“已过期”标签。有的团队会把completedSectionCount直接省掉只给一个progress百分比。但实际做下来发现给了明细字段以后前端做起来舒服很多还不用自己根据百分比逆推章节数量。3.3 异常场景的错误码不能少接口设计不只包含正常链路还要预判异常场景。我整理了这张错误码表直接在接口文档里同步给前端异常场景HTTP状态码业务错误码提示信息参数缺失或格式错误40040001参数错误课程不存在40440401课程不存在用户未报名该课程40940901用户未报名该课程课程已下架41041001课程已下架服务端异常50050000系统繁忙请稍后重试“用户未报名”这个错误码特别容易漏。原型页面上未报名用户根本不展示学习状态卡片所以前端正常路径下不会触发这个错误。但接口设计要考虑非正常调用比如用户手动构造请求或者前端状态管理出现异常这时候有一个明确的错误信息比空数据可排查得多。4. 底层数据支持表结构、SQL查询与性能优化4.1 三张表的分工各司其职查询课程学习状态底层数据来自三张核心表用户课程关系表user_course记录用户与课程之间的归属关系包括报名时间、有效期、关系状态CREATE TABLE user_course ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, course_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 1 COMMENT 1-正常 0-已退课, expire_time DATETIME DEFAULT NULL COMMENT 课程到期时间, enroll_time DATETIME NOT NULL COMMENT 报名时间, create_time DATETIME NOT NULL, update_time DATETIME NOT NULL, UNIQUE KEY uk_user_course (user_id, course_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;课程章节表course_section记录课程包含哪些章节用于统计总章节数和定位当前章节CREATE TABLE course_section ( id BIGINT PRIMARY KEY AUTO_INCREMENT, course_id BIGINT NOT NULL, title VARCHAR(255) NOT NULL, sort_order INT NOT NULL COMMENT 章节排序, duration INT DEFAULT 0 COMMENT 视频时长(秒), is_deleted TINYINT NOT NULL DEFAULT 0, UNIQUE KEY uk_course_sort (course_id, sort_order) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;学习记录表learning_record记录用户对每个章节的学习痕迹是查询进度和最后学习时间的核心依赖CREATE TABLE learning_record ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, course_id BIGINT NOT NULL, section_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0 COMMENT 0-学习中 1-已完成, last_position INT DEFAULT 0 COMMENT 播放位置(秒), create_time DATETIME NOT NULL, update_time DATETIME NOT NULL, UNIQUE KEY uk_user_section (user_id, course_id, section_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;4.2 核心SQL三次查询组合出最终状态查询逻辑拆解为三步查课程关系、查已完成章节数、查总章节数。-- 第一步查询用户与课程的关系 SELECT user_id, course_id, status, expire_time FROM user_course WHERE user_id #{userId} AND course_id #{courseId} LIMIT 1; -- 第二步查询当前用户已完成章节数 SELECT COUNT(*) FROM learning_record WHERE user_id #{userId} AND course_id #{courseId} AND status 1; -- 第三步查询课程总章节数 SELECT COUNT(*) FROM course_section WHERE course_id #{courseId} AND is_deleted 0;这三步查询在业务量不大时完全够用。如果对性能有更高要求可以将第二和第三步合并通过子查询实现但可读性会变差。实际项目里我倾向于保留三个独立查询逻辑一目了然后续要加缓存也方便。4.3 索引设计和缓存策略索引设计上三张表都有联合索引或唯一索引上面建表语句已经体现。需要强调的是learning_record表上的(user_id, course_id, section_id)唯一索引这个索引除了加速查询外还天然承担了幂等约束防止同一用户对同一章节插入多条重复学习记录。缓存方面课程学习状态是一个读多写少的场景。用户每次打开课程列表页和学习页都会调用这个接口而状态的写入只发生在用户完成章节时。可以直接用Redis缓存key: course:learning:status:{userId}:{courseId} value: JSON字符串status、progress、currentSectionId等 ttl: 600秒在章节完成事件上报时主动删除对应用户课程的缓存key保证下一次查询能拿到最新状态。如果担心缓存雪崩或击穿可以在回源查询时加一个互斥锁或者缓存空值一段时间。实际项目如果流量规模不大不一定要做这么复杂的保护但ttl策略和主动失效是必须的。另外列表页如果同时展示多门课的学习状态不要循环调用单查询接口会出现N1问题。建议单独设计批量查询接口传入courseId列表后端一次查出多门课的状态返回Map结构。5. 从原型到接口落地这几个坑我提前帮你踩过了5.1 学习状态和学习记录不同步项目联调时发现过一个怪问题用户明明在播放器里看了视频回到课程列表页课程状态还是“未开始”。定位后发现视频播放模块上报的只是“播放位置”没有更新“章节完成”状态而课程状态依赖章节完成记录。这属于典型的模块间数据联动缺失。最终处理方案是在用户播放进度达到阈值时同时触发“章节完成”和“课程状态更新”两个事件哪怕状态更新晚一点也要保证事件最终一致。后端的接口层只负责查询状态更新由事件处理程序负责不耦合在视频播放上报接口里。5.2 并发重复学习导致的脏数据有段时间出现过学习记录翻倍的情况。排查后发现是用户同时开了多个标签页前端并发上报了多个“章节完成”事件。虽然唯一索引兜底避免了真正插入重复记录但接口层返回的“操作成功”在不同线程之间竞争时出现了部分记录update_time被旧值覆盖的情况。这里给出一个具体的解决方案就是用INSERT ... ON DUPLICATE KEY UPDATE让写入操作具备幂等性同时将update_time设置为当前时间而不是传参值。另外insert时带上所有业务字段避免老线程把新数据覆盖掉。5.3 用户回退学习进度条倒退了怎么办有的用户学完第五章后回头重新学第三章。按照“最近学习位置”逻辑进度会显示倒退用户会觉得进度条“缩水”。这个问题在原型评审阶段产品经理没提直到测试用例写出来才发现。最终定的规则是章节一旦标记为“完成”状态永远不回退。用户回看章节只更新last_position不改变已完成标记。这样进度是单调递增的用户体验上也更好理解。5.4 查询接口的性能边界与批量场景单查询接口性能没太大压力因为走的是唯一索引。但课程列表页的场景麻烦一些如果用户首页要展示“最近学习的8门课”前端对每门课调一次单查接口就会产生8次HTTP请求后端还要承担8次查询。实际项目里后续补充了批量查询接口路径为POST /api/v1/courses/batch-learning-status请求体传入courseIds数组后端批量查询后返回课程ID为key的Map。批量接口里对courseIds做了上限限制单次最多20个超过则报参数错误。这既控制了数据库压力也防止接口被恶意传超大数组。5.5 时间字段的时区和格式问题lastLearnTime和deadline这两个时间字段在后端返回时必须明确时区。最初接口返回的是UTC时间前端没做转换直接把字符串展示出来导致用户看到的时间和本地时间差了8小时。这是一个很简单但很典型的问题。最终处理方案是接口统一返回带时区偏移的ISO 8601格式例如2025-01-10T14:30:0008:00前端直接用原生Date解析不需要自己拼接时区。数据存储层统一使用UTC时间只在接口出参时转换为东八区。这样一套规则下来三端App、H5、小程序的时间显示就统一了。回到最开始那句话“分析产品原型、设计查询指定课程学习状态接口”这个任务名称看起来平淡但做下来一整轮才发现真正的工作量不在写接口那一下而在于把产品原型里那些标签、按钮、进度条背后的业务规则都拆出来翻译成清楚的数据模型和接口契约。我最大的体会是拿到原型不要急着动工先和产品经理把状态枚举、进度口径、完成判定这三个问题对齐后面可以少走很多弯路。另外接口设计时多给前端返回几个“看似多余”的展示字段联调阶段会顺滑很多。