好未来的学而思培优课程内容管理系统需要支持多校区、多教师、多版本课程内容（如不同年级的教材版本）。请设计一个数据库模型，用于存储课程结构（章节-课时-知识点）、教师信息、版本信息，并说明如何保证数据的一致性（如教师修改课程内容后，学生端能实时看到更新）？同时，考虑数据量增长时，如何优化查询性能（如课程搜索、版本历史查询）？

好未来C++难度：困难

答案

1) 【一句话结论】
核心是构建“课程-章节-课时-知识点”层级模型，通过多表关联实现多校区、多教师、多版本管理，结合乐观锁+事件驱动保证数据实时一致性，并利用索引、分库分表、缓存优化查询性能。

2) 【原理/概念讲解】

多校区维度：在Course表中增加campus_id字段，明确不同校区课程版本差异，类似“给每个课程贴上校区标签，确保不同校区看到的是对应版本”。
多版本管理：通过Version表存储版本信息（字段version_num），教师修改课时内容时生成新版本记录，历史版本保留以支持追溯，类似“版本就像课程内容的“快照”，每次修改都生成新快照，方便回溯”。
多对多关联：教师与课程是多对多关系（通过Teacher_Course中间表，字段role区分主讲/助教），支持多校区教师管理多课程，类似“教师和课程的关系是多对多，比如一位教师可以主讲多个课程，同时多个教师可以参与一个课程”。
数据一致性：教师修改课时内容时，先更新Version表（记录版本号、时间），再发布事件（如“课程更新”），学生端订阅该事件后实时拉取最新数据（事件驱动实现最终一致性，延迟控制在秒级），类似“教师修改后，通过消息队列通知所有学生端，学生端收到后更新本地数据，保证实时性”。
性能优化：对课程搜索（倒排索引）、版本历史查询（按version_num分页）使用索引；数据量大时按course_id范围分片（如course_id % 100），避免热点数据集中；缓存热点数据（如热门课程）到Redis提升查询速度，类似“给常用查询建索引，分库分表避免单库压力，缓存热点数据减少数据库访问”。

3) 【对比与适用场景】

设计方案	定义	特性	使用场景	注意点
乐观锁（版本号）	通过版本号校验冲突，冲突时回滚	低并发，冲突时回滚	低并发场景	需要频繁回滚处理
事件驱动	发布-订阅模式，教师修改后发布事件，学生端订阅	高并发，实时更新	高并发实时更新需求	需要消息队列支撑
范围分片	按主键范围（如`course_id % N`）分片	扩展性好，负载均衡	数据量增长，避免单库瓶颈	需要热点数据分布均匀
哈希分片	按哈希值（如`course_id % N`）分片	避免热点数据集中	热点数据较多	分片键选择不当可能导致数据倾斜

4) 【示例】

表结构：

-- 课程表（含多校区）
CREATE TABLE Course (
  course_id INT PRIMARY KEY,
  name VARCHAR(100),
  campus_id INT,  -- 多校区标识
  version INT DEFAULT 1,
  create_time TIMESTAMP
);

-- 章节-课时-知识点层级
CREATE TABLE Chapter (
  chapter_id INT PRIMARY KEY,
  course_id INT,
  order INT,
  name VARCHAR(50),
  FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

CREATE TABLE Lesson (
  lesson_id INT PRIMARY KEY,
  chapter_id INT,
  knowledge_point_id INT,
  teacher_id INT,
  content TEXT,
  version_num INT,  -- 课时版本号
  create_time TIMESTAMP,
  FOREIGN KEY (chapter_id) REFERENCES Chapter(chapter_id)
);

CREATE TABLE KnowledgePoint (
  knowledge_point_id INT PRIMARY KEY,
  name VARCHAR(100),
  description TEXT
);

-- 教师表（含校区）
CREATE TABLE Teacher (
  teacher_id INT PRIMARY KEY,
  name VARCHAR(50),
  campus_id INT
);

-- 教师与课程多对多关联
CREATE TABLE Teacher_Course (
  teacher_id INT,
  course_id INT,
  role ENUM('主讲','助教'),
  PRIMARY KEY (teacher_id, course_id),
  FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id),
  FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

-- 版本历史表
CREATE TABLE Version (
  version_id INT PRIMARY KEY,
  course_id INT,
  version_num INT,
  create_time TIMESTAMP,
  description VARCHAR(200),
  FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

事件示例（教师修改课时内容）：
```
POST /api/lesson/update
{
  "course_id": 101,
  "lesson_id": 201,
  "content": "更新后的课时内容",
  "version_num": 2
}
```
接口逻辑：
1. 校验课时版本号（lesson.version_num）是否与请求中的version_num一致，不一致则返回冲突提示；
2. 更新Lesson表（content更新，version_num递增）；
3. 插入Version表（course_id=101, version_num=2, description="教师修改内容"）；
4. 发布事件“CourseUpdated(101,2)”到Kafka；
5. 学生端订阅该事件，消费后更新本地缓存。

5) 【面试口播版答案】
“面试官您好，针对多校区、多教师、多版本课程的需求，我设计的数据库模型核心是围绕‘课程-章节-课时-知识点’的层级结构，通过多表关联实现复杂关系。具体来说，课程表存储基础信息，章节、课时、知识点表按层级关联；教师与课程是多对多，用中间表关联。为了支持多版本，每个课程有版本号，修改时生成新版本记录到Version表，通过事件驱动（教师修改后发布事件，学生端订阅）保证实时更新。多校区方面，课程表增加campus_id字段明确校区归属。性能优化方面，对课程搜索（倒排索引）和版本历史查询（按版本号分页）使用索引，数据量大时按课程ID范围分片，缓存热点数据到Redis，确保查询性能。”

6) 【追问清单】

问题1：版本历史查询如何实现？
回答要点：Version表按version_num排序，分页查询（如SELECT * FROM Version WHERE course_id=xxx ORDER BY version_num DESC LIMIT 10,20）。
问题2：多校区跨网络时，数据一致性如何保证？
回答要点：事件发布订阅，结合最终一致性，延迟控制在秒级（如使用Kafka发布事件，消费者订阅后更新本地缓存）。
问题3：并发修改同一课时内容时，如何避免冲突？
回答要点：使用乐观锁（版本号），修改时检查当前版本号是否匹配，不匹配则回滚。
问题4：如何优化课程搜索性能？
回答要点：对课程名称、教师名称、版本号建立倒排索引，结合ES实现全文搜索。
问题5：数据量增长时，分库分表的具体策略是什么？
回答要点：按课程ID范围分片（如course_id % 100），或按版本号分片，避免热点数据集中。

7) 【常见坑/雷区】

忽略多校区关联：未设计campus_id字段或Course_Campus表，导致多校区课程版本差异无法管理。
未设计版本历史：仅存储当前版本，无法追溯历史修改，影响数据可追溯性。
未考虑分库分表：数据量增长后查询性能下降，如全表扫描，导致系统慢。
一致性模型选择错误：强一致性导致延迟过高，影响用户体验，如实时更新延迟超过秒级。
缺乏缓存策略：频繁数据库查询，导致性能瓶颈，如热点数据未缓存。