跳到主要内容

设计数据分析平台

问题​

如何设计支撑业务数据分析的数据平台？

答案​

一、OLTP vs OLAP​

维度OLTP（在线事务）OLAP（在线分析）
典型操作INSERT / UPDATESELECT 聚合
数据量单次几条单次扫描百万~亿
响应时间毫秒级秒~分钟
存储行式（MySQL）列式（ClickHouse）
索引B+ 树稀疏索引 / Zone Map

二、数仓分层架构​

层级说明示例
ODS原始数据，不做处理原始订单日志
DWD清洗、标准化、关联订单明细宽表（关联用户、商品）
DWS按主题聚合用户日活跃汇总、GMV 日汇总
ADS面向应用的指标首页 Dashboard 数据

三、宽表设计​

-- DWD 层：订单明细宽表（反范式化，预 JOIN）
CREATE TABLE dwd_order_detail (
    order_id BIGINT,
    order_time DateTime,
    -- 订单信息
    order_status String,
    payment_method String,
    total_amount Decimal(10,2),
    -- 用户信息（预 JOIN）
    user_id BIGINT,
    user_name String,
    user_level String,
    user_city String,
    -- 商品信息（预 JOIN）
    product_id BIGINT,
    product_name String,
    category_l1 String,   -- 一级品类
    category_l2 String,   -- 二级品类
    brand String,
    -- 时间维度
    dt Date              -- 分区键
) ENGINE = MergeTree()
PARTITION BY dt
ORDER BY (order_time, user_id);
宽表 vs 范式化
  • 范式化（OLTP）：避免冗余，保证写入一致性
  • 宽表（OLAP）：预 JOIN，用空间换查询时间
  • 宽表在分析场景下可减少 90%+ 的 JOIN 操作

四、技术选型​

场景推荐方案原因
实时指标（秒级）ClickHouse / Doris列式存储，聚合极快
离线分析Hive / Spark SQL批处理大数据集
交互式查询Presto / Trino多数据源联邦查询
实时流计算Flink SQL实时聚合、窗口计算
BI 展示Metabase / Superset开源 BI 工具

五、ClickHouse 实践​

-- 创建表（MergeTree 引擎）
CREATE TABLE events (
    event_id UUID,
    user_id UInt64,
    event_type LowCardinality(String),  -- 低基数优化
    properties String,                    -- JSON 属性
    event_time DateTime,
    dt Date MATERIALIZED toDate(event_time)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)       -- 按月分区
ORDER BY (event_type, user_id, event_time);  -- 排序键

-- 典型分析查询
-- 最近 7 天各事件类型的 UV
SELECT
    event_type,
    uniqExact(user_id) AS uv,
    count() AS pv
FROM events
WHERE dt >= today() - 7
GROUP BY event_type
ORDER BY uv DESC;

六、实时 + 离线混合架构（Lambda）​

         ┌── 实时流 ──→ Flink ──→ Redis/ClickHouse ──→ 实时看板
数据源 ──┤
         └── 离线批 ──→ Spark ──→ Hive/ClickHouse ──→ 离线报表

常见面试问题​

Q1: 为什么不直接在 MySQL 上做分析查询？​

答案：

  1. 行式存储：MySQL 需要读取整行，分析查询浪费 IO
  2. 锁竞争：大查询可能阻塞在线业务
  3. 性能瓶颈：百万行以上的 GROUP BY / 聚合很慢
  4. 缺少分析函数：MySQL 窗口函数支持有限

正确做法：数据同步到 OLAP 引擎（ClickHouse/Doris），与 OLTP 隔离。

Q2: 如何设计一个实时 Dashboard？​

答案：

1. 数据采集：埋点 SDK → Kafka
2. 实时计算：Flink SQL 窗口聚合（1 分钟/5 分钟）
3. 结果存储：
   - 实时指标 → Redis（TTL 24h）
   - 明细 → ClickHouse
4. 查询层：API 先查 Redis，fallback 查 ClickHouse
5. 展示：Grafana / 自研 Dashboard

Q3: ClickHouse 和 Doris 怎么选？​

答案：

维度ClickHouseDoris（StarRocks）
并发低（单查询极快）高（MPP 架构）
实时写入批量写入好支持实时更新
JOIN弱强
运维中等简单（FE+BE）
适合场景日志分析、固定报表多维分析、Ad hoc

相关链接​