数据库三剑客 · 第10节 · 分析型与嵌入式 OLAP：ClickHouse 与 DuckDB

分析型与嵌入式 OLAP：ClickHouse 与 DuckDB

Analytical Databases · ClickHouse & DuckDB

前三章我们学的 MySQL / Oracle / PG，全部都是为"一次处理几行、但要扛住几千并发"设计的——这叫 OLTP（联机事务处理）。可一旦有人要问"过去一年每个品类每天的成交额趋势"，你会立刻发现：同一条 SQL 在 1 亿行明细表上跑，MySQL 要几十秒甚至几分钟，而且它一边跑一边把线上业务的 CPU 和 IO 抢光。这不是 MySQL 不行，是你拿货车去跑赛道了。这一章讲两件事：用 ClickHouse 搭一套能扛 PB 级、毫秒级聚合的分析库；用 DuckDB 把同一套分析能力塞进你本机的 Python 脚本里——两条路线，一个面向线上、一个面向你自己的笔记本。

10.1 OLTP 和 OLAP 是两种完全不同的活

先在心里立起这道分界线，后面所有取舍都好理解了
维度	OLTP（MySQL / Oracle / PG）	OLAP（ClickHouse / DuckDB / Doris）
典型查询	`SELECT * FROM orders WHERE id = 1001` —— 按主键捞几行	`SELECT category, sum(amount) FROM sales GROUP BY category` —— 扫几亿行算聚合
并发要求	几千到几万 QPS，每个请求都要毫秒级	几十个并发就算多，单查慢一两秒也能接受
存储布局	行存：一行的所有列连续放在一起	列存：同一列的所有值连续放在一起
写入模式	频繁的单行 INSERT / UPDATE / DELETE	大批量追加写入（一次几万到几百万行），几乎不更新
索引方式	B+ 树，精确查找 O(log n)	稀疏主键索引 + 分区裁剪 + 跳数索引，靠扫描少量列取胜
事务	强 ACID、行锁、MVCC	基本只有批量写入的原子性，不提供跨行事务

论列存为什么能快几百倍：三个原因叠加

① IO 大幅减少（最重要）。行存下查"所有订单的金额"，数据库要把每一行的所有列都从磁盘读出来，再丢掉不要的——这叫"读放大"。一张 50 列的表只查 2 列，等于浪费了 96% 的 IO。列存下，这两列在磁盘上是连续的，只读需要的列。

② 压缩率高。同一列的数据类型相同、取值范围相近（比如都是"北京/上海/广州"、都是有序的时间戳），压缩算法能压到原来的十分之一甚至更低。数据量小了，IO 又少一个数量级。

③ 向量化执行 + SIMD。行存只能一行一行处理（还要不断判断"下一列是什么类型"）。列存可以把一列 1024 个值打包成一个数组，交给 CPU 的 SIMD 指令一次算一批，还能多核并行。CPU 分支预测失败率大幅下降，这是"向量化"三个字的真正收益。

一句话总结：OLAP 引擎不是"更快的 MySQL"，它的快来自"只读要读的列 + 压得更小 + 一批一批算"。

最常见的生产事故：在业务库上跑报表

现象：运营想要一张"近 30 天各渠道转化率"的报表，开发直接写 SQL 查生产库。上线后发现早上九点（运营看报表的高峰）MySQL 的 CPU 打到 100%，接口 P99 从 50ms 涨到 3 秒，用户开始投诉。

原因：报表 SQL 要全表扫几千万行 + 多个 GROUP BY，把 InnoDB 的 buffer pool 冲掉、把 IO 打满，直接影响在线业务。索引在这类查询面前基本没用——它要读的是"全部数据"，不是"某几行"。

正确做法：① 报表走从库（至少隔离主库压力）；② 数据量大就同步到专门的 OLAP 库（ClickHouse / Doris / StarRocks）；③ 定期预聚合（物化视图 / 汇总表），让报表查汇总而不是查明细。

判断信号：当一个查询的扫描行数超过表行数的 10%，或者耗时超过 1 秒，就该考虑它不该待在业务库了。

10.2 ClickHouse：为聚合而生的列存引擎

ClickHouse 是俄罗斯 Yandex 开源的列式数据库，现在版本按季度滚动发布（以官网最新稳定版为准，线上建议选 LTS 分支）。它的定位非常纯粹：大规模数据的实时聚合查询。日志分析、用户行为分析、实时报表、BI 加速——这些场景它几乎是最快的一档。反过来，它不是用来替 MySQL 的。

论MergeTree：ClickHouse 的灵魂

ClickHouse 里 90% 的表都用 MergeTree 家族引擎。理解它只要抓住四件事：

① 数据按"分区（PARTITION）"目录存放。通常是按月/按天分区。查询时如果 WHERE 命中了分区键，ClickHouse 直接跳过整个分区目录，连文件都不打开——这是最快的一层过滤，叫"分区裁剪"。分区键要选在 WHERE 里最常出现的粗粒度时间维度上。

② 数据在分区内按 ORDER BY（排序键）物理有序。ClickHouse 会基于排序键建一个稀疏索引：不是每一行都记，而是每 8192 行（一个 granule）记一条摘要。索引小到能常驻内存，查一个范围只需要读几个 granule。

③ 每次 INSERT 生成一个不可变的 part，后台线程不断把 part 合并（merge）成大 part。"MergeTree"的名字就来自这里。这解释了为什么它不擅长 UPDATE —— 更新需要重写整个 part（mutation），是异步的重活。

④ 没有主键唯一约束。排序键不等于唯一键，你插两条一样的行它都收。要"去重"得用 ReplacingMergeTree + 后台合并（并提供 FINAL 来强制查询时去重，代价是慢）。所以 ClickHouse 的"最终一致"和你想象的最终一致，需要先对齐预期。

建一张能扛住的分析明细表（带分区、排序键、TTL 自动清理）

CREATE TABLE dwd_order_events
(
    event_date   Date,                    -- 分区用，粗粒度时间
    event_time   DateTime,                -- 精确时间
    user_id      UInt64,
    channel      LowCardinality(String), -- 低基数字典编码，省空间又更快
    category     LowCardinality(String),
    sku_id       UInt64,
    amount       Decimal(18, 2),
    qty          UInt32,
    province     String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)          -- 按月分区：报表最常按时间范围过滤
ORDER BY (event_date, channel, category)   -- 排序键：把"最常用的过滤+分组维度"放在前面
TTL event_date + INTERVAL 24 MONTH       -- 两年后自动删旧数据，省得人工清理
SETTINGS index_granularity = 8192;       -- 稀疏索引粒度：每 8192 行一条索引记录

MergeTree 家族：按"你要怎么合并数据"来选引擎
引擎	合并时做什么	典型用途
MergeTree	什么都不做，只把 part 合并	追加型的原始明细、日志、埋点
ReplacingMergeTree	按排序键保留版本最大的那一行（需指定 ver 列）	需要"最后一版状态"的场景，如用户维度表（去重是最终一致的）
SummingMergeTree	排序键相同的行，数值列自动相加	预聚合指标表，减少存储和查询量
AggregatingMergeTree	只合并"中间状态"（如 `uniq`、`quantile` 的中间结果）	精确去重计数、分位数统计，是物化视图的标准搭档
ReplicatedMergeTree	上面任意一种 + 副本（靠 ZooKeeper/ClickHouse Keeper 协调）	生产环境实际用的都是它，配合副本实现高可用

ClickHouse 的六个"不行"，踩中就是事故

① 不适合高并发点查。ClickHouse 没有连接池式的快速点查优化，单条 WHERE id=? 反而可能比 MySQL 慢，且并发上不去。典型是"每页 50 QPS 的详情页查询"——别用它。它擅长的是"一次扫几亿行、每秒查几次"。

② UPDATE / DELETE 代价极高。它实现为 ALTER TABLE ... UPDATE/DELETE（mutation），是异步重写 part 的重活。正确的做法是靠分区整块删除（DROP PARTITION）或用 ReplacingMergeTree 追加新版本，而不是逐行改。

③ INSERT 必须批量。每次 INSERT 都产生一个 part，一秒插一万次单行数据会把后台合并线程拖死（经典报错 "Too many parts"）。规矩是：单次至少 1000 行、每秒不超过几十次插入，用 Buffer 表或批量攒批。

④ JOIN 能力弱于 PG。大表 JOIN 大表容易内存爆掉。官方推荐的做法是"小表做字典（Dictionary）或提前展开成大宽表"，也就是用空间换掉 JOIN。

⑤ 没有真正的唯一约束和事务。数据可能短暂重复或延迟可见。业务上"实时精确"的要求要降级为"准实时"。

⑥ 不要拿它当缓存或 KV。它是分析引擎，不是 Redis，也不是 MySQL 的替代品。

10.3 ClickHouse 实战：明细 + 物化视图预聚合

真实场景里，报表需求往往是固定几类（日报、小时报、渠道转化）。与其每次都扫明细，不如用物化视图（Materialized View）在写入时就聚合好——这是 ClickHouse 最实用的一个特性：MV 会像一个"插入触发器"一样，把新写入的数据同时投递到目标表里做预聚合。

第一步：建一个按天聚合的目标表（SummingMergeTree 自动累加）

CREATE TABLE dws_channel_daily
(
    event_date Date,
    channel    LowCardinality(String),
    orders     UInt64,
    gmv        Decimal(18, 2)
)
ENGINE = SummingMergeTree
ORDER BY (event_date, channel)             -- 排序键相同的行，合并时 orders/gmv 自动相加
PARTITION BY toYYYYMM(event_date);

第二步：建物化视图，把明细表的写入"实时"喂给汇总表

CREATE MATERIALIZED VIEW mv_channel_daily
TO dws_channel_daily                        -- TO 目标表：数据插入时同步写入汇总表
AS
SELECT
    event_date,
    channel,
    count()          AS orders,
    sum(amount)      AS gmv
FROM dwd_order_events
GROUP BY event_date, channel;

-- 之后往明细表 INSERT，汇总表会自动更新，不需要任何调度任务
INSERT INTO dwd_order_events VALUES
    ('2026-09-20', '2026-09-20 10:00:01', 1001, 'app', '数码', 88, 2999.00, 1, '广东'),
    ('2026-09-20', '2026-09-20 10:00:03', 1002, 'app', '服饰', 91,   199.00, 2, '浙江');

第三步：查询（从几亿行明细变成查几千行汇总），以及排障常用命令

-- 报表查询：命中了排序键前缀 + 分区裁剪，通常几十毫秒
SELECT event_date, sum(gmv) AS total_gmv
FROM dws_channel_daily
WHERE event_date BETWEEN '2026-09-01' AND '2026-09-20'
GROUP BY event_date ORDER BY event_date;

-- 排障：看查询扫描了多少行、走了哪些索引（分析慢查询的第一步永远是它）
EXPLAIN indexes = 1
SELECT count() FROM dwd_order_events WHERE channel = 'app';

-- 看 system.query_log 找最耗资源的查询
SELECT query_duration_ms, read_rows, substring(query, 1, 80) AS q
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC LIMIT 10;

-- 手动合并 part（一般不手动调，除非要立刻让 ReplacingMergeTree 去重生效）
OPTIMIZE TABLE dwd_order_events FINAL;

-- 数据同步：用 ClickHouse 自己的 Kafka 引擎或外部工具（Flink CDC / Vector / Seatunnel）
-- 从 MySQL binlog / Kafka 实时灌进来，形成"业务库 → OLAP"的链路

论为什么"预聚合"是分析场景的第一优化手段

报表查询有个特点：维度固定、结果规模小。查"每渠道每天的 GMV"，几亿行明细最终只产出几百行结果。既然如此，为什么每次都要重新算一遍？

预聚合就是把"计算"从查询时搬到写入时：写入一次贵一点，但之后每一次查询都快到几乎免费。代价是灵活性下降——物化视图只能回答它定义好的维度组合，突然要"按省份 + 品类交叉分析"就得再建一个 MV（或者退回查明细）。

实践中的分层是：明细层（dwd，查得少但什么都能查）→ 汇总层（dws，覆盖 80% 的固定报表）→ 应用层（ads，直接给接口/大屏用）。这就是数据仓库的分层思想在 OLAP 库里最朴素的落地。

10.4 DuckDB：把数据库装进你的进程里

上一节的 ClickHouse 是"服务器"——要装、要运维、要导数据。但很多时候你只是想在自己的笔记本上分析一个 2GB 的 CSV，或者在一个 Python 脚本里 join 一堆 Parquet 文件。为这个装一套集群显然荒唐。这正是 DuckDB 的位置：

嵌入式（in-process）：没有独立服务进程，就是一个库（Python 里 import duckdb、命令行里一个 duckdb 二进制）。类比是"OLAP 界的 SQLite"。
列存 + 向量化执行：内核同样是列存、向量化的分析引擎，所以它能在笔记本上几秒扫完上亿行。
直接查询外部文件：CSV / Parquet / JSON 可以不经导入直接 SELECT，甚至能对一堆文件用通配符当一张表（'data/*.parquet'）。
零依赖、单文件、跨平台：数据库就是一个 .duckdb 文件，拷走就能在另一台机器打开。
和 Python 生态无缝：查询结果可以直接当 pandas / arrow 表用，也能直接读 pandas / polars 的 DataFrame。

DuckDB 三种用法：CLI、Python、直接查 Parquet

-- ① 命令行：duckdb mydata.duckdb
-- 不用建表就能直接查文件，列类型自动推断
SELECT channel, count(*) AS n, sum(amount) AS gmv
FROM 'orders_2026_*.parquet'          -- 通配符：一批文件当成一张表
WHERE event_date >= '2026-09-01'
GROUP BY channel ORDER BY gmv DESC;

-- 建持久化表（存在 mydata.duckdb 文件里），写入比 MySQL 快得多
CREATE TABLE orders AS SELECT * FROM 'orders_2026_*.parquet';

-- 把查询结果直接导出成压缩 Parquet
COPY (SELECT * FROM orders) TO 'orders.parquet' (FORMAT parquet, COMPRESSION zstd);

# ② Python：pip install duckdb，和 pandas/polars 直接互通
import duckdb, pandas as pd

con = duckdb.connect("analytics.duckdb")

# pandas DataFrame 直接当表查，不用先导入数据库
df = pd.read_csv("users.csv")
con.register("users_df", df)

result = con.execute("""
    SELECT u.city, count(*) AS orders, sum(o.amount) AS gmv
    FROM 'orders/*.parquet' o
    JOIN users_df u ON u.user_id = o.user_id
    GROUP BY u.city
    ORDER BY gmv DESC
""").df()          # 结果直接是 DataFrame

print(result.head())

DuckDB / ClickHouse / SQLite / PostgreSQL 该怎么摆
维度	DuckDB	ClickHouse	SQLite	PostgreSQL
形态	嵌入式、单文件	服务端、集群	嵌入式、单文件	服务端
引擎	列存 + 向量化（OLAP）	列存 + 向量化（OLAP）	行存（OLTP）	行存（OLTP）
并发写入	单写多读（写锁）	批量追加快，不适合单行频繁写	单进程写	高并发写
数据规模舒适区	几 GB 到几百 GB（本机内存/磁盘）	TB 到 PB（集群）	MB 到几 GB	GB 到 TB
典型用途	数据科学、本地 ETL、脚本分析、读 Parquet	实时数仓、BI 加速、日志分析	App 本地存储、配置	在线业务主库

DuckDB 的适用边界（别把它当服务端数据库用）

① 它是"单写入者"模型。一个进程持有写权限时，别的进程只能读，写入并发上不去。不要把它部署成给几百个请求同时写的小服务。

② 它适合"分析"，不适合"业务"。没有用户权限体系、没有高可用、没有连接池概念。它天生就是数据科学工具，不是 MySQL 的替代品。

③ 内存要留够。默认会尽量用内存做聚合和排序，处理远大于内存的数据时要么调低 memory_limit（会溢写到临时文件，变慢），要么分批处理。

④ 它和 ClickHouse 是互补不是替代。很实用的组合：本地用 DuckDB 做数据探索和清洗，产出干净的 Parquet，再批量导入 ClickHouse 供线上查询。DuckDB 甚至能当分析层的查询引擎读远端文件——"轻查询层 + 重存储"是近年很常见的用法。

10.5 选型：从"谁在用"倒推该上哪一套

OLAP 选型速查
你的场景	推荐
在本机分析几个 GB 的 CSV/Parquet，写 Python 脚本	DuckDB。零运维、直接读文件、结果直接是 DataFrame。这是它无可替代的场景。
要把分析能力嵌进自己的应用（离线工具、桌面软件、边缘端）	DuckDB（进程内）。不必让用户装服务端。
线上实时报表、日志/埋点分析，数据量 TB 级	ClickHouse（或 Doris / StarRocks，后两者对 JOIN 和 update 更友好）。配合 Kafka 实时入仓。
需要频繁 UPDATE、要标准 SQL 事务、又要一点分析能力	别硬上 ClickHouse。用 PostgreSQL（加列存扩展或物化视图），或"PG 存明细 + CK 存汇总"组合。
预算充足、要求存算分离和弹性	云上的托管数据仓库（ClickHouse Cloud / BigQuery / Snowflake / Doris）。

配一个常见的真实架构

MySQL（业务主库）→ CDC（Canal / Flink CDC / Seatunnel）→ Kafka → ClickHouse（明细层 + 物化视图汇总层）→ BI / 接口查询；同时在数据侧用 DuckDB 做临时的探索和清洗，产出的结果再回灌。

注意这条链路上的每个组件都只干自己擅长的事：MySQL 管交易，Kafka 缓冲削峰，ClickHouse 管聚合，DuckDB 管个人分析。把"实时精确"的要求压在 ClickHouse 上，往往就是设计错误的开始。

记

本章小结

① OLTP 和 OLAP 是两种活：一个按行取几行、要求高并发低延迟；一个按列扫几亿行、要求聚合快。不要在业务库上跑报表。

② 列存快的三个原因：只读需要的列（IO 大减）、压缩率高（又减少 IO）、向量化 + SIMD（CPU 一次算一批）。

③ ClickHouse 的灵魂是 MergeTree：分区裁剪 + 稀疏排序键 + 不可变 part 后台合并。它强在聚合，弱在点查、UPDATE、JOIN、高并发——用错地方就是灾难。

④ 物化视图把计算从查询时搬到写入时，是分析场景性价比最高的优化手段，代价是灵活性。

⑤ DuckDB 是"OLAP 界的 SQLite"：嵌入式、单文件、直接读 Parquet、和 Python 无缝。本地分析选它，线上服务选 ClickHouse。

← 上一节数据库三剑客 · 课程总览下一节 →