设计一个用于存储用户行为日志的数据库系统，需要支持实时查询（如按时间范围统计访问量）和批量分析（如用户行为模式挖掘）。请说明数据库选型、数据模型设计及索引策略。

1) 【一句话结论】
采用混合架构，结合时序数据库（如TimescaleDB）处理实时查询（按时间范围统计访问量），同时使用宽列存储（如ClickHouse）进行批量分析（用户行为模式挖掘），通过时间列主索引和字段分区策略满足需求。

2) 【原理/概念讲解】
老师口吻：首先解释时序数据库的核心特性——专为时间序列数据设计，时间列作为主键，支持时间范围查询（如“过去1小时的数据”），适合实时查询场景，类比“时间轴上的记录本”，快速定位某段时间的日志；然后说明宽列存储的特点——列式存储，适合批量分析（如聚合、模式挖掘），通过列式压缩和复杂查询优化，类比“分类整理的档案柜”，批量提取不同行为模式。两者结合是因为实时查询需要低延迟、时间范围查询能力，而批量分析需要灵活的列处理和复杂聚合能力。

3) 【对比与适用场景】

数据库类型	定义	特性	使用场景	注意点
时序数据库（如TimescaleDB）	专为时间序列数据设计的数据库	时间列为主键，支持时间范围查询，支持实时写入和查询，支持按时间分区	实时查询（如按时间范围统计访问量）、监控数据	需时间列主索引，写入延迟较低，复杂查询（多表连接）较慢
宽列存储（如ClickHouse）	列式存储，适合批量分析	列式存储，支持复杂聚合（如sum、count、group by），支持多表连接，支持SQL	批量分析（如用户行为模式挖掘）、报表生成	写入延迟较高（适合批量写入），适合批量查询，不适合实时低延迟写入
关系型数据库（如PostgreSQL）	传统关系型数据库	行式存储，支持ACID事务，支持复杂查询（多表连接）	需强一致性和事务的场景	写入延迟较高，不适合高并发实时写入，但适合复杂查询

4) 【示例】

• 数据模型设计：

  -- 时序数据库（TimescaleDB）表结构
  CREATE TABLE user_behavior_log (
      id BIGSERIAL PRIMARY KEY,
      user_id BIGINT NOT NULL,
      behavior_type VARCHAR(50) NOT NULL, -- 如 "click", "view", "purchase"
      event_timestamp TIMESTAMPTZ NOT NULL,
      INDEX idx_user_behavior_time (event_timestamp)
  );
  
  -- 宽列存储（ClickHouse）表结构
  CREATE TABLE user_behavior_batch (
      user_id BIGINT NOT NULL,
      behavior_type VARCHAR(50),
      event_timestamp DATETIME,
      PRIMARY KEY (user_id, event_timestamp)
  );
  

• 实时查询示例（TimescaleDB）：

  -- 按时间范围统计访问量
  SELECT behavior_type, COUNT(*) AS count
  FROM user_behavior_log
  WHERE event_timestamp BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'
  GROUP BY behavior_type;
  

• 批量分析示例（ClickHouse）：

  -- 用户行为模式挖掘
  SELECT user_id, behavior_type, COUNT(*) AS behavior_count
  FROM user_behavior_batch
  WHERE event_timestamp >= '2023-01-01'
  GROUP BY user_id, behavior_type
  ORDER BY behavior_count DESC
  LIMIT 10;
  

5) 【面试口播版答案】
“面试官您好，针对存储用户行为日志并支持实时查询和批量分析的需求，我的设计思路是采用混合架构：首先，实时查询（如按时间范围统计访问量）适合用时序数据库（比如TimescaleDB），因为它专为时间序列数据设计，时间列作为主索引能高效支持时间范围查询；然后，批量分析（如用户行为模式挖掘）适合用宽列存储（比如ClickHouse），列式存储和复杂聚合优化能高效处理模式挖掘。具体来说，数据模型上，日志表包含时间戳、用户ID、行为类型等字段，时间列为主索引；索引策略上，时间列为主索引，行为类型为辅助索引，保证实时查询效率。这样既能满足实时查询的低延迟需求，又能通过宽列存储完成批量分析。”

6) 【追问清单】

• 问题1：数据量规模和增长预期？
  回答要点：假设数据量每天1亿条，增长10%每年，需考虑分片和分区策略。
• 问题2：实时查询的延迟要求？
  回答要点：目标延迟<1秒，所以选择时序数据库的实时查询能力。
• 问题3：数据保留策略？
  回答要点：短期日志保留7天，长期分析数据保留1年，通过分区删除旧数据。
• 问题4：数据一致性要求？
  回答要点：实时查询允许最终一致性，批量分析需要强一致性，通过事务控制。
• 问题5：扩展性需求？
  回答要点：水平扩展，时序数据库按时间分区分片，宽列存储按列分区。

7) 【常见坑/雷区】

• 只选单一数据库：如只选关系型数据库，无法满足实时查询的低延迟需求；或只选宽列存储，无法满足实时查询。
• 数据模型设计不合理：没有时间列作为主键，导致实时查询效率低；或字段设计过于复杂，影响批量分析的效率。
• 索引策略错误：没有时间列的主索引，导致实时查询慢；或没有行为类型的辅助索引，导致批量分析慢。
• 未考虑数据分区：数据量增长后，查询性能下降，需提前设计分区策略。
• 未考虑数据一致性：实时查询和批量分析对一致性要求不同，未区分导致设计不合理。