淘天平台中用户画像数据库设计，需存储用户行为数据（点击、购买、浏览）并支持实时查询和离线分析，请设计数据模型、索引策略及分片方案。

淘天集团T-STAR 日常实习生难度：中等

答案

1) 【一句话结论】

采用混合存储架构，结合时序数据库（如ClickHouse实时表）存储实时行为数据，通过用户ID哈希分片支持低延迟查询，同时离线分析使用宽表（如Doris）存储聚合后的用户画像，满足实时查询与离线分析需求。

2) 【原理/概念讲解】

用户行为数据具有时序性（如点击时间戳）、高写入频率（点击/购买/浏览），需同时支持实时查询（如实时推荐）和离线分析（如用户画像统计）。

数据模型：主表按用户ID作为主键，存储行为类型、时间戳、商品ID等，确保实时查询的快速定位。
索引策略：主键索引（用户ID）用于实时查询（如按用户ID查询最近行为），二级索引（行为类型+时间范围）用于离线分析（如按点击类型统计）。
分片方案：水平分片按用户ID哈希，将数据分散到多个分片节点，避免单点瓶颈，类比“图书馆按书名（用户ID）分类放不同书架（分片），查找时直接到对应书架（分片），快速定位”。

3) 【对比与适用场景】

特性	实时存储（如ClickHouse实时表）	离线宽表（如Doris）
定义	支持低延迟写入和查询的时序数据库	支持海量数据存储和复杂聚合的宽表
特性	写入延迟低（毫秒级），支持实时索引	写入延迟较高（秒级），支持复杂聚合（如聚合函数）
使用场景	实时推荐、实时行为分析（如点击率实时计算）	用户画像离线统计（如用户购买频次、偏好分析）
注意点	索引数量有限，避免过多二级索引	分片后聚合需考虑数据倾斜

4) 【示例】

数据模型（伪代码）：

-- 实时行为表（ClickHouse）
CREATE TABLE user_behavior(realtime) (
  user_id INT PRIMARY KEY,  -- 主键，用户ID
  action_type VARCHAR,      -- 行为类型（点击/购买/浏览）
  item_id INT,              -- 商品ID
  timestamp TIMESTAMP,      -- 行为时间戳
  device_type VARCHAR        -- 设备类型
) PARTITION BY HASH(user_id) PARTITIONS 16;  -- 按用户ID哈希分片

索引策略：
- 主键索引（user_id）：实时查询（如按用户ID查最近行为）。
- 二级索引（action_type, timestamp）：离线聚合（如按行为类型统计）。

查询示例：

实时查询（按用户ID查最近10条行为）：

SELECT * FROM user_behavior WHERE user_id = 1001 ORDER BY timestamp DESC LIMIT 10;

离线聚合（统计用户行为类型）：

SELECT action_type, COUNT(*) as count 
FROM user_behavior 
WHERE user_id IN (1001, 1002) 
GROUP BY action_type;

5) 【面试口播版答案】

面试官您好，针对用户画像数据库设计，我考虑采用混合架构。核心思路是结合实时数据库和离线宽表，满足实时查询和离线分析需求。具体来说，实时行为数据存储在ClickHouse的实时表，按用户ID哈希分片，主键索引支持低延迟查询，同时通过二级索引支持离线聚合。离线分析则使用Doris宽表存储聚合后的用户画像，支持复杂统计。索引策略上，主键索引用于实时查询，行为类型和时间戳的二级索引用于离线分析。分片方案按用户ID哈希分片，确保数据均匀分布，避免热点问题。这样既能支持实时推荐等低延迟场景，也能满足用户画像的离线分析需求。

6) 【追问清单】

问：如何处理热点用户导致的数据倾斜？
答：采用用户ID哈希分片，当热点用户数据量超过阈值时，触发分片扩容，重新哈希分配数据。
问：索引策略如何平衡写入和查询性能？
答：主键索引（用户ID）用于实时查询，二级索引（行为类型+时间范围）用于离线分析，避免过多二级索引影响写入性能，通过定期更新或合并索引优化。
问：离线分析与实时数据如何保持一致性？
答：采用CDC（变更数据捕获）机制，实时数据写入后，通过CDC同步到离线宽表，设置1小时延迟，确保数据一致性。
问：分片后查询复杂度如何？
答：按用户ID路由查询，每个分片节点独立处理，查询结果通过聚合后返回，支持分布式计算，保证查询效率。

7) 【常见坑/雷区】

分片键选择不当：若按时间分片，会导致热点问题（如某天数据集中），应按用户ID哈希分片。
索引过多：二级索引过多会影响写入性能，需根据查询需求合理设计索引。
数据一致性：实时与离线数据延迟过大，导致分析结果不准确，需设置合理的同步延迟。
分片扩容：分片后扩容时数据迁移复杂，需考虑分片键的哈希一致性，避免数据丢失或重复。