假设要存储学生的学习行为日志（如点击课程视频、提交作业、参与讨论的时间、答题正确率等），数据量每天约50万条，如何设计数据库表结构，并优化查询性能？请说明索引策略、分区表选择及时序数据库的适用场景。
深圳大学中国石化难度：中等
答案
1) 【一句话结论】采用混合数据库架构，关系型数据库（如MySQL）存储原始日志并按天分区，建立复合索引；时序数据库（如InfluxDB）存储聚合指标，支持时间序列分析，平衡存储与查询性能。
2) 【原理/概念讲解】
针对学习行为日志，核心是设计结构化表并优化查询路径。日志表需包含行为类型（如视频点击、作业提交）、时间戳（事件发生时间）、用户ID/课程ID（关联主体）、正确率（答题指标）等字段。
- 索引策略：建立复合索引（如
user_id + event_time、course_id + event_time、behavior_type + event_time），覆盖常用查询场景（如查询用户历史行为、课程参与情况），通过索引快速定位数据。 - 分区表：按时间范围（如按天）对表进行分区（如
PARTITION BY RANGE (TO_DAYS(event_time))），将每日数据分散存储，减少单表数据量，提升查询效率。 - 时序数据库适用场景：时序数据库（如InfluxDB）专为时间序列数据设计，采用列式存储和自动压缩，适合存储聚合后的指标（如每日正确率趋势），支持快速时间序列分析（如趋势、异常检测）。
（类比：日志表像“行为记录本”，按天分区像把每天的记录装进不同文件夹，索引像给记录本标页码，方便快速找到某天的某条记录；时序数据库像“趋势图”，专门记录数据随时间的变化，适合看趋势。）
3) 【对比与适用场景】
| 数据库类型 | 定义 | 特性 | 使用场景 | 注意点 |
|---|---|---|---|---|
| 关系型（如MySQL） | 结构化数据，支持ACID事务 | 强一致性，支持复杂查询，事务处理 | 用户信息、课程信息等结构化数据 | 不适合高吞吐时间序列，查询复杂时性能下降 |
| 时序数据库（如InfluxDB） | 专为时间序列设计，列式存储 | 高吞吐，时间聚合，自动压缩 | 日志、指标、传感器数据（如正确率趋势） | 不支持复杂关联查询，适合聚合分析 |
4) 【示例】
- MySQL表创建与分区：
CREATE TABLE student_behavior (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
behavior_type ENUM('video_click', 'assignment_submit', 'discussion_participate') NOT NULL,
event_time TIMESTAMP NOT NULL,
correct_rate DECIMAL(5,2),
INDEX idx_user_time (user_id, event_time),
INDEX idx_course_time (course_id, event_time),
INDEX idx_type_time (behavior_type, event_time)
);
PARTITION BY RANGE (TO_DAYS(event_time)) (
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
PARTITION MAXVALUE
);
- InfluxDB插入示例：
INSERT student_behavior, user_id=1001, course_id=101, behavior_type="video_click", event_time=1701234567, correct_rate=0.85
5) 【面试口播版答案】
面试官您好，针对每天50万条学习行为日志，我会设计混合数据库方案。首先，在关系型数据库（如MySQL）中创建日志表，包含行为类型、时间戳、用户/课程ID等字段，并按天分区存储，减少单表数据量。然后建立复合索引（如按用户和时间排序的索引），覆盖常用查询路径（如用户行为历史、课程参与情况），提升查询性能。对于高频时间序列分析（如每日正确率趋势），引入时序数据库（如InfluxDB），存储聚合后的指标，支持快速查询趋势。具体来说，MySQL表通过分区和索引优化了原始日志的存储与查询，时序数据库则针对时间序列分析进行了专门优化，平衡了存储成本与查询效率。
6) 【追问清单】
- 问题1：如果数据量增长到每天100万条，如何扩展？
回答：增加分区（按周或月），或使用分库分表，时序数据库提升写入吞吐。 - 问题2：复合索引的维护成本？
回答：索引维护成本较低，因为分区后单表数据量减少，查询时只扫描相关分区。 - 问题3：如何保证数据一致性？
回答：关系型数据库保证事务一致性，时序数据库通过写入缓冲保证最终一致性，适合分析场景。 - 问题4：对于实时分析（如实时正确率统计），如何优化？
回答：在时序数据库中设置实时聚合，或使用物化视图。
7) 【常见坑/雷区】
- 坑1：仅用关系型数据库存储日志，忽略时序需求，导致查询复杂时性能下降。
- 坑2：索引设计不合理（如单字段索引，导致查询时全表扫描）。
- 坑3：分区策略错误（如按用户分区，导致查询时扫描所有分区，性能低）。
- 坑4：时序数据库与关系型数据同步不及时，导致分析数据不一致。
- 坑5：未考虑数据压缩，时序数据冗余导致存储浪费。