在360安全产品中，用户行为日志（如点击、扫描记录）每天产生TB级数据，如何设计数据库表结构并优化查询性能？请说明表结构设计、索引策略、分库分表方案及查询优化措施。
360Web服务端开发工程师难度：中等
答案
1) 【一句话结论】：针对TB级用户行为日志，采用宽表模型结合时间分区+用户ID哈希分库的分库分表策略，通过虚拟节点技术减少数据倾斜、批量写入控制延迟，复合索引加速查询，预聚合表（增量更新）减少实时计算，冷热分离（数据恢复）优化存储成本，并辅以Redis缓存热点数据，降低跨分片查询压力。
2) 【原理/概念讲解】：用户行为日志属于宽表场景（所有行为字段存于一张表，适合关联查询，如用户点击路径分析）。分库分表是解决数据量大的核心手段：
- 宽表模型：将用户ID、时间戳、操作类型、设备信息等所有字段存于一张表，主键为
user_id + event_time保证数据有序，便于关联查询。类比：超市的“行为货架”，所有商品（行为字段）放一起，方便找关联。 - 分库分表：水平分库（按用户ID哈希拆分库，如库0-库N，用虚拟节点技术减少数据倾斜）+ 水平分表（按时间按天拆分表，如
20240101），避免单库压力。虚拟节点技术（如ShardingSphere的哈希分片）通过增加虚拟节点数量，将实际节点映射到虚拟空间，确保数据均匀分布，减少数据倾斜风险。 - 索引策略：建
event_time + user_id复合索引，加速时间范围查询（如最近7天行为）。索引列顺序影响性能，时间列在前，用户ID在后，适合范围查询。 - 预聚合表：按天统计行为量（如
daily_behavior_stats），减少实时计算压力。增量更新通过CDC捕获新增数据，按时间/用户ID过滤，仅更新预聚合表，避免全量重建。 - 冷热分离：旧数据（如30天前）迁移至冷存储（如HDFS），降低存储成本。数据恢复时，从HDFS读取冷数据并重新导入数据库，确保数据不丢失。
3) 【对比与适用场景】：
| 方案 | 定义 | 特性 | 使用场景 | 注意点 |
|---|---|---|---|---|
| 宽表模型 | 所有行为字段存一张表，主键复合 | 适合关联查询，减少JOIN | 用户行为分析（点击路径、扫描记录关联） | 字段多，存储成本高 |
| 分库分表（水平） | 按时间/用户ID拆分表 | 扩展性好，按分区查询 | 日志数据量大（按天/小时拆分） | 分区键选择影响查询效率 |
| 复合索引 | 多列组合索引（如时间+用户ID） | 提升范围查询性能 | 时间范围查询（最近7天行为） | 索引列顺序影响性能 |
| 预聚合表 | 按天/小时统计行为量 | 减少实时计算压力 | 高频统计查询（如日活、行为量） | 更新策略（增量更新）需合理设计 |
| 冷热分离 | 旧数据迁移至冷存储 | 降低存储成本 | 数据量持续增长 | 数据访问频率变化需动态调整 |
4) 【示例】：
- 表结构（宽表+分库分表）：
CREATE TABLE user_behavior ( user_id BIGINT NOT NULL, event_time TIMESTAMP NOT NULL, action_type VARCHAR(20), device_info JSON, location VARCHAR(100), PRIMARY KEY (user_id, event_time) ) ENGINE=InnoDB SHARDING_KEY(user_id) SHARDING_COUNT 16 -- 分16库（虚拟节点技术） PARTITION_BY_RANGE (event_time) PARTITIONS 365 -- 按天分区（一年365天） - 索引：
CREATE INDEX idx_time_user ON user_behavior (event_time, user_id); - 预聚合表（增量更新）：
CREATE TABLE daily_behavior_stats ( day DATE NOT NULL, user_id BIGINT, action_count INT, PRIMARY KEY (day, user_id) ) ENGINE=InnoDB PARTITION_BY_RANGE (day) PARTITIONS 365; -- CDC捕获新增数据，每小时更新预聚合表 INSERT INTO daily_behavior_stats (day, user_id, action_count) SELECT DATE(event_time), user_id, COUNT(*) FROM user_behavior WHERE event_time >= now() - interval 1 hour GROUP BY day, user_id ON CONFLICT (day, user_id) DO UPDATE SET action_count = EXCLUDED.action_count; - 冷热分离（数据恢复）：
定时任务（每天凌晨2点）：-- 从HDFS读取冷数据 INSERT INTO user_behavior (user_id, event_time, action_type, device_info, location) SELECT user_id, event_time, action_type, device_info, location FROM hdfs_behavior_logs WHERE event_time < now() - interval 30 day; -- 删除原表分区 ALTER TABLE user_behavior DROP PARTITION (event_time < now() - interval 30 day);
5) 【面试口播版答案】：面试官您好，针对TB级用户行为日志，我设计思路是采用宽表模型+分库分表+预聚合+冷热分离。首先，表结构用宽表存储所有行为字段（用户ID、时间戳、操作类型等），主键为user_id + event_time保证数据有序。分库分表上，按时间按天拆分表（如20240101），按用户ID哈希分库（库0-库N），用虚拟节点技术（如ShardingSphere的哈希分片）避免数据倾斜，批量写入（每批1万条）减少延迟。索引方面建event_time + user_id复合索引，加速时间范围查询。查询优化用预聚合表（按天统计行为量），通过CDC捕获新增数据增量更新，减少实时计算压力，同时缓存热点数据（如Redis）。冷热分离上，将30天前的日志迁移到HDFS，数据恢复时从HDFS读取并重新导入数据库。这样既能支持实时查询（如用户点击路径），又能应对TB级数据扩展，写入延迟控制在秒级，且存储成本显著降低。
6) 【追问清单】：
- 问：分库分表后跨分片查询（如查询某用户所有时间的行为）如何优化？
答：预聚合表存储所有时间的行为量，或者用Redis缓存用户行为数据（如用户行为快照），减少数据库跨分片查询压力。 - 问：预聚合表的增量更新逻辑具体如何实现？
答：通过CDC（如Debezium）捕获宽表的新数据，定时任务（如每小时）按时间/用户ID过滤，仅更新预聚合表，避免全量重建。 - 问：冷热分离的数据恢复机制是否可靠？
答：从HDFS读取冷数据并重新导入数据库，确保数据不丢失，恢复后验证数据一致性（如统计量匹配）。 - 问：虚拟节点技术如何减少数据倾斜？
答：假设使用ShardingSphere，配置哈希分片算法，虚拟节点数量大于实际节点，将用户ID哈希后取模，分配到不同库，减少数据集中。 - 问：宽表模型是否会影响写入性能？
答：批量写入（如每批1万条）减少锁竞争，同时优化索引（如复合索引）提升查询性能，写入延迟控制在秒级。
7) 【常见坑/雷区】：
- 坑1：主键设计为自增ID，导致分库分表时数据倾斜（如按时间分区，自增ID按时间顺序插入，所有数据集中在一个库），应使用复合主键或UUID。
- 坑2：索引过多导致写性能下降，如为每个字段建索引，应只对常用查询列建索引，并优化批量插入。
- 坑3：分库分表后查询时未考虑分区键，导致全表扫描（如查询某用户最近7天行为，若分区键是时间，需按时间范围查询，否则效率低），应明确分区键选择。
- 坑4：预聚合表增量更新时未过滤新增数据，导致数据不一致，需通过CDC过滤或时间窗口过滤。
- 坑5：冷热分离时未考虑数据恢复，导致冷数据丢失，应保留HDFS备份，并定期验证数据完整性。