设计一个存储用户课程学习进度的数据库表，需要支持快速查询用户当前课程进度、历史学习记录，并考虑数据一致性（如多端同步时避免冲突）。
答案
1) 【一句话结论】
核心方案采用“三表设计（用户课程进度表+历史记录表+课程章节表）+分布式乐观锁+最终一致性+缓存优化”，通过事务+分布式版本号保证多端同步一致性，同时针对大规模场景优化索引和缓存，满足快速查询与历史记录需求。
2) 【原理/概念讲解】
首先拆解需求为当前进度（快速查询用户在每门课程的最新学习位置，如章节ID）和历史记录（保留每次学习的详细日志，如时间、章节、时长）。
- 当前进度表：存储用户与课程的关联状态（当前章节、最后更新时间），通过分布式乐观锁（如Redis分布式锁或数据库版本号递增）保证多端同步时的一致性。
- 历史记录表：记录每次学习的完整日志，按时间倒序存储。
- 课程章节表：维护课程的结构（如章节ID、父级章节、顺序），当课程结构变更（如新增章节）时，通过事件触发用户进度表同步。
类比：分布式环境下的版本号就像“分布式锁”，确保不同节点的更新不冲突，类似银行账户的并发扣款，需要版本号检查避免资金不一致。
3) 【对比与适用场景】
| 设计方式 | 定义 | 特性 | 使用场景 | 注意点 |
|---|---|---|---|---|
| 单表设计 | 将用户、课程、进度、历史合并为一表 | 结构简单，但查询复杂（需多字段筛选） | 数据量小，查询需求单一 | 查询性能差，难以扩展，分布式下事务无法保证一致性 |
| 多表设计（当前方案） | 用户表+课程表+用户课程进度表+用户学习记录表+课程章节表 | 分表存储，职责明确，支持复杂查询 | 大规模用户、多课程、频繁查询 | 需分布式乐观锁保证一致性，设计复杂，需缓存优化 |
| 分布式优化（当前方案） | 在多表基础上加入分布式版本号、缓存 | 适用于分布式环境，保证多端同步 | 跨机房、多端同步场景 | 需考虑最终一致性+补偿机制，避免数据延迟 |
| 最终一致性方案 | 采用事件驱动+补偿逻辑 | 适用于高并发，允许短暂不一致 | 实时性要求不高的场景 | 需设计补偿任务，避免数据积压 |
4) 【示例】
以MySQL+Redis为例，表结构及操作示例：
- 用户表（users）：id（主键）、username等；
- 课程表（courses）：id（主键）、course_name等；
- 课程章节表（course_chapters）：id（主键）、course_id（外键）、chapter_id（章节ID）、parent_id（父级章节）、order（顺序）；
- 用户课程进度表（user_course_progress）：id（主键）、user_id（外键）、course_id（外键）、current_chapter_id（当前章节ID）、last_update_time（最后更新时间）、version（分布式版本号，初始1）；
- 用户学习记录表（user_study_records）：id（主键）、user_id（外键）、course_id（外键）、chapter_id（学习章节）、study_duration（学习时长，秒）、study_timestamp（学习时间戳）、create_time（记录时间）；
分布式乐观锁更新进度（伪代码）：
-- 检查版本号（分布式乐观锁）
SELECT version FROM user_course_progress WHERE user_id = 1 AND course_id = 101 AND version = 1 FOR UPDATE;
-- 更新进度（递增版本号）
UPDATE user_course_progress
SET current_chapter_id = 4, last_update_time = NOW(), version = version + 1
WHERE user_id = 1 AND course_id = 101 AND version = 1;
课程新增章节后同步用户进度（事件触发）：
当课程新增章节（chapter_id=5），系统通过消息队列（如Kafka）发送“课程章节变更”事件，触发用户进度表更新：
UPDATE user_course_progress
SET current_chapter_id = 5
WHERE user_id = 1 AND course_id = 101 AND current_chapter_id < (SELECT MAX(chapter_id) FROM course_chapters WHERE course_id = 101);
5) 【面试口播版答案】
“面试官您好，针对存储用户课程学习进度的需求，我的核心方案是采用‘三表设计+分布式乐观锁+最终一致性’的组合。首先，拆解为当前进度（快速查询用户在每门课程的最新学习位置）和历史记录（保留学习日志），设计用户课程进度表（存储当前章节、最后更新时间、分布式版本号）、用户学习记录表（存储章节、学习时长、时间戳），以及课程章节表（维护课程结构）。多端同步时，通过分布式乐观锁（如Redis分布式锁或数据库版本号递增）保证更新不冲突，比如更新进度时先检查版本号，若匹配则更新并递增版本号，否则重试。对于课程章节变更（如新增章节），通过课程变更事件触发用户进度表同步，确保进度与课程结构一致。数据量大的优化包括对关键字段加复合索引（如user_id+course_id），并缓存当前进度到Redis（TTL 5分钟），减少数据库压力。这样既保证了查询性能，又解决了分布式环境下的数据一致性问题。”
6) 【追问清单】
- 问题1：课程新增章节后，用户进度如何同步？
回答要点：通过课程变更事件（如消息队列）触发用户进度表更新，将用户当前章节更新为新增章节的ID，或标记为未完成，确保进度与课程结构一致。 - 问题2：百万级用户下如何优化查询性能？
回答要点：对用户课程进度表按用户ID分库分表，对历史记录表按时间倒序索引，并缓存当前进度到Redis集群，减少数据库压力。 - 问题3：多端同步时，网络延迟导致版本号检查失败，如何处理？
回答要点：采用重试机制（如指数退避），避免死锁，同时设置超时时间，确保最终一致性。 - 问题4：如何保证历史记录的顺序正确？
回答要点：历史记录表按时间戳（create_time）排序存储，多端同步时通过时间戳确保记录插入的先后顺序，避免乱序。 - 问题5：是否考虑过缓存与数据库的一致性？
回答要点：是的，使用Redis事务或数据库事务同步，确保缓存与数据库数据一致（如更新进度时，先更新数据库，再更新Redis）。
7) 【常见坑/雷区】
- 坑1：忽略分布式环境，单表设计在分布式下事务无法保证一致性，导致多端更新冲突。
- 坑2：字段命名混乱（如study_time同时表示时长和时间戳），导致数据存储和查询歧义。
- 坑3：课程章节变更时未同步用户进度，导致用户进度与课程结构不一致。
- 坑4：未考虑缓存导致数据不一致，如缓存未及时更新，用户看到过期进度。
- 坑5：乐观锁版本号未递增或检查逻辑错误，导致并发更新时误判，无法保证一致性。