针对360安全卫士的用户行为日志系统，设计数据库表结构，并说明如何优化查询性能（如分库分表、索引策略），同时考虑数据一致性（如事务处理）。

360服务端开发工程师-Golang难度：中等

答案

1) 【一句话结论】
针对360安全卫士用户行为日志系统，采用按周维度分表（滚动表）结合哈希+范围分库分表，通过复合索引优化查询，事务保证数据一致性，30天后数据归档至对象存储，平衡存储与查询性能，同时考虑热点问题与写性能权衡。

2) 【原理/概念讲解】
老师口吻解释关键概念：

分表粒度选择依据：假设日志保留30天，按天分表需30个表，若每天1亿条则单表30亿条，存储与查询压力大；按周分表（7天/表），30周即210天，表大小约7亿条（1KB/条），约7TB，更合理，同时通过数据归档减少数据库压力。类比：就像整理文件，按周归档比按天归档更易管理，避免文件过多导致查找困难。
分库分表实现细节：用户ID用哈希分片（数据集中，便于按用户ID查询），时间用范围分片（按周切分表，便于按时间范围查询）。哈希分片可能导致热点（热门用户数据集中），解决方案是范围分片或冷热分离（如将30天前数据归档至冷存储）。
索引策略权衡：复合索引提升查询效率，但会增加写性能开销。对于高并发写入场景，选择必要索引（主键自增、时间戳、常用查询字段），避免过度索引；同时通过分表降低单表写入压力，平衡读写性能。
事务处理与一致性：高并发写入选READ COMMITTED隔离级别，减少锁竞争（避免写锁阻塞读操作），保证事务原子性。分布式事务用本地事务+补偿机制（如日志插入成功但统计表更新失败，通过定时任务重试），避免两阶段提交的失败风险。

3) 【对比与适用场景】

策略类型	定义	特性	使用场景	注意点
按时间分表（滚动表）	按天/周等时间粒度切分表，旧数据定期归档	只需维护当前表，查询时优化为只查当前表（或分区表）	日志类数据，数据保留周期长（如30天）	需定期归档，避免表过多影响管理；按时间查询为主场景
按用户分库/分表	按用户ID哈希/范围切分到不同库/表	每个用户数据集中，便于按用户ID查询；跨库查询需分布式框架	用户行为分析，需频繁按用户ID查询	跨库查询复杂，需ClickHouse等分布式查询框架；单表写入压力分散但查询跨库成本高
混合分表（时间+用户）	结合时间与用户ID切分	兼顾时间范围与用户维度，支持按用户+时间范围查询	高并发写入+复杂查询（如用户+时间统计）	策略复杂，需平衡扩展性与查询效率；跨表查询需同时涉及时间与用户维度

选择按时间分表的原因：日志数据按时间查询（如最近7天行为）需求更频繁，按时间切分后，查询时只需处理当前表（或通过分区表优化），而按用户分库的跨库查询成本较高。

4) 【示例】

表结构设计（伪代码，按周分表）：

-- 用户行为日志表（按周分表，当前表名：user_action_log_week_20240501_20240507）
CREATE TABLE user_action_log (
    action_id BIGINT PRIMARY KEY AUTO_INCREMENT,  -- 主键，自增
    user_id BIGINT NOT NULL,                       -- 用户ID
    action_type VARCHAR(50) NOT NULL,              -- 行为类型（如"启动"、"扫描"）
    action_data JSON,                              -- 行为具体数据（如扫描结果）
    event_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 事件时间
    INDEX idx_user_time_range (user_id, event_time),  -- 复合索引：按用户+时间范围排序
    INDEX idx_type_time_range (action_type, event_time)  -- 复合索引：按行为类型+时间范围
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (DATE(event_time) / 7) (
    PARTITION p20240501 VALUES LESS THAN (20240508),
    PARTITION p20240508 VALUES LESS THAN (20240515),
    ...
);

（注：实际分表可按周分区，如PARTITION BY RANGE (DATE(event_time) / 7)）

分库分表示例：
用户ID哈希分片到不同库（如库0-库n），每个库按周分表（如库0下的表：user_action_log_week_20240501_20240507）。时间范围查询时，通过分区表优化，只扫描当前周分区。

事务处理示例（Golang）：

db := dbPool.Get()
tx, err := db.Begin()
if err != nil { return err }

// 插入行为日志
_, err = tx.Exec("INSERT INTO user_action_log (user_id, action_type, action_data, event_time) VALUES (?, ?, ?, ?)",
    userId, actionType, actionData, time.Now())
if err != nil { tx.Rollback(); return err }

// 更新统计表
_, err = tx.Exec("UPDATE user_active_stats SET active_count = active_count + 1 WHERE user_id = ?", userId)
if err != nil { tx.Rollback(); return err }

tx.Commit()

隔离级别设置：db.SetMaxIdleConns(100), db.SetConnMaxLifetime(time.Hour), 并通过db.SetReadOnly(false)确保事务为读写事务（READ COMMITTED）。

数据归档示例：
定时任务（如每天凌晨）执行：

// 归档30天前的数据
db.Exec("INSERT INTO user_action_log_archive SELECT * FROM user_action_log WHERE event_time < DATE_SUB(NOW(), INTERVAL 30 DAY)")
db.Exec("DELETE FROM user_action_log WHERE event_time < DATE_SUB(NOW(), INTERVAL 30 DAY)")

5) 【面试口播版答案】
“面试官您好，针对360安全卫士用户行为日志系统，我设计的数据库表结构核心是按周维度分表（滚动表），表名包含时间范围，字段包括行为ID、用户ID、行为类型、事件时间等。首先，表按周切分（如20240501-20240507），字段有主键action_id（自增，覆盖所有字段）、用户ID、行为类型（如启动、扫描）、行为数据（JSON）、事件时间（时间戳）。索引策略上，添加了复合索引：按用户ID+时间范围排序（idx_user_time_range）用于按用户+时间查询，按行为类型+时间范围（idx_type_time_range）用于按行为类型过滤。分库分表优化方面，用户ID用哈希分片到不同库，时间用范围分片到周表，每个表数据量控制在7亿条左右（按周分表更合理，30周即210天，表大小约7TB），30天后数据归档到对象存储（如S3）减少数据库压力。数据一致性通过数据库事务保证，比如记录行为日志并更新用户活跃度统计表时，使用事务确保原子性，隔离级别选READ COMMITTED减少锁竞争。总结来说，通过分表分库降低单库负载，索引优化查询效率，事务保证数据一致性，并配套数据归档策略，能支撑高并发写入与复杂查询需求。”

6) 【追问清单】

问：分表粒度选择为什么按周而不是按天？
回答要点：按天分表会导致30天有30个表，单表数据量过大（如每天1亿条则30亿条），存储与查询压力大；按周分表（7天/表）30周即210天，表大小约7亿条（1KB/条），约7TB，更合理，同时通过数据归档减少数据库压力。
问：索引选择是否考虑了写性能？比如复合索引是否影响插入速度？
回答要点：索引提升查询效率，但会增加写性能开销。对于高并发写入场景，选择必要索引（主键自增、时间戳、常用查询字段），避免过度索引；同时通过分表降低单表写入压力，平衡读写性能。
问：如何处理跨库查询？比如按用户ID查询过去7天日志？
回答要点：跨库查询（如按用户ID查询过去7天日志）可通过分布式查询框架（如ClickHouse的分布式查询）或数据仓库（如Hive）实现，将数据归档后聚合；也可通过分区表优化，只扫描当前周分区，减少扫描范围。
问：事务处理中，如果分布式事务失败，如何回滚？
回答要点：使用两阶段提交（2PC）风险高，推荐本地事务结合补偿机制。例如，日志插入成功但统计表更新失败，则通过定时任务重试更新；或使用TCC模式，定义补偿操作。
问：数据量增长后，如何调整分库分表策略？
回答要点：动态调整分表策略（如从按周扩展为按月分表），监控表大小与查询性能，当表达到阈值（如100亿条）时，进行数据归档或迁移；同时优化索引与分片键，提升查询效率。

7) 【常见坑/雷区】

坑1：过度索引导致写性能下降。
坑2：分表策略不合理（如按天分表导致表过多），影响查询与管理。
坑3：事务处理未考虑隔离级别（如选REPEATABLE READ导致高锁竞争）。
坑4：未说明数据归档策略，导致长期数据存储成本高。
坑5：跨库查询方案缺失，无法支撑复杂查询需求。