mysql搭建在线教育平台数据库架构与设计

在线教育平台数据库需定制设计：课程与章节用闭包表优化查询，学习进度用设备维度唯一键 + 时间戳防覆盖，JSON 仅存非结构化元数据，分库分表前须验证真实瓶颈。

在线教育平台的数据库不能直接套用通用 CMS 或电商模型——课程原子性、学习状态强时序、多角色权限交叉、实时互动数据高频写入，这些会立刻暴露简单表结构的缺陷。

课程与章节如何避免 N+1 查询和树形遍历性能崩塌

把 course 和 chapter 设计成父子 ID 关系（parent_id）是常见错误：递归查目录、前端 懒加载 展开、后台导出全路径都会触发深度 JOIN 或多次查询。

• 用闭包表（course_chapter_closure）预存所有祖先 - 后代关系，查某课程全部章节只需 SELECT child_id FROM course_chapter_closure WHERE ancestor_id = ?
• chapter 表保留 course_id（直连课程）、parent_id（支持单层嵌套），但禁止跨层递归；真实多级目录由前端按 sort_order 渲染，后端 只提供扁平列表 + 层级标识字段 level
• 所有课程大纲接口必须返回带 path 字段的扁平数组（如 "1.2.5"），避免前端拼接或服务端递归

学习进度同步 为什么 不能只靠 user_id + video_id 唯一索引

用户拖拽进度、倍速播放、多端切换、离线缓存回传，会导致同一视频的多个 watch_progress 记录高频写入，仅靠 UNIQUE(user_id, video_id) 会因并发更新失败或覆盖旧值。

• 用 REPLACE INTO 或 INSERT …… ON DUPLICATE KEY UPDATE 替代普通 INSERT，但必须确保 updated_at 时间戳严格递增，否则旧客户端回传会覆盖新进度
• 增加 device_id 字段组成联合唯一键（user_id, video_id, device_id），允许同视频在不同设备有独立进度，合并逻辑交给聚合任务而非实时写入
• 进度表不存绝对时间点，只存 last_second（秒级整数）和 play_rate（1.0/1.5/2.0），减少浮点精度问题和存储体积

MySQL 8.0 的 JSON 列适合存哪些教育业务字段

不是所有“动态字段”都该进 JSON：搜索、排序、索引字段（如课程标签、适用年级）仍需拆成关联表；JSON 只用于真正非结构化、低频查询、且无需事务强一致的数据。

• 课程介绍中的富文本元信息（course.meta）：含视频分辨率、字幕语言列表、配套文档 URL 数组
• 考试题目的选项扩展（exam_question.options）：每道题可有 2–8 个选项，每个选项含 is_image、explanation 等不定字段
• 禁用 JSON 存用户答题记录——它需要被统计、分页、按正确率筛选，必须落物理列（answer_choice、is_correct、submit_time）

CREATE TABLE course (id BIGINT PRIMARY KEY,   title VARCHAR(255),   meta JSON,   CHECK (JSON_VALID(meta)) );

分库分表前必须验证的三个真实瓶颈点

教育平台早期就分库分表，90% 情况下只是提前引入 路由 复杂度，而不是解决实际问题。先确认以下三点是否真卡住：

• 单表 user_study_log 超过 2000 万行？没到就用 PARTITION BY RANGE (created_date) 按月分区，比 ShardingSphere 更轻量
• 慢查询集中在 SELECT * FROM order WHERE status = 'paid' AND created_at > '2024-01-01'？加联合索引 (status, created_at) 比分表见效更快
• 讲师后台导出“近 30 天所有学生答题详情”超时？这是 OLAP 场景，应走 异步任务 + 预聚合表（daily_student_answer_summary），而非硬扛大表 JOIN

最常被忽略的是事务边界：一个“报名课程并生成首章学习任务”的操作，涉及 order、user_course、study_task 三张表，它们必须在同一库同一事务内完成；跨库事务会直接导致数据不一致，而多数教育 SaaS 在订单量破万前根本不需要分库。