ClickHouse

本章目标：能讲清行存和列存、MergeTree 的分区与排序键，能建表、查询、做物化视图和 TTL，并知道它适合什么、不适合什么。 前置知识：会写 GROUP BY 聚合 SQL；读过 实时数仓 更顺。

1. 为什么需要它

MySQL 擅长 OLTP：按主键查一行、下单、扣库存、事务提交。但报表要扫几千万行做聚合，SELECT category, SUM(...) FROM ... GROUP BY category 会把 MySQL 拖慢，影响线上交易。

Hive 能扫大表，但一条报表要几十秒到几分钟。OLAP 引擎填的是这个空档：大范围扫描 + 聚合 + 低延迟查询。

1.1 行存和列存

行存（MySQL）：
[O1001, 手机, 2999, 2018-08-01]
[O1002, 图书, 59,   2018-08-01]

列存（ClickHouse）：
order_id: [O1001, O1002]
category: [手机, 图书]
amount:   [2999, 59]
dt:       [2018-08-01, 2018-08-01]

行存按整行连续存储，适合“取一整行”；列存按列连续存储，适合“只读几列、对整列聚合”。同一列类型一致、重复度高，压缩效果也更好。

面试高频：列存不是“一定比行存快”。点查一行、频繁更新，行存更合适；全表扫描、按列聚合，列存更合适。

2. 核心概念

2.1 架构：shard、replica、Keeper

shard 1 (replica A/B) ─┐
shard 2 (replica A/B) ─┴──→ 入口节点合并结果

• shard：数据水平切分，不同 shard 存不同数据；
• replica：同一 shard 的副本，用 ReplicatedMergeTree + Keeper 协调；
• Distributed 表：只存元数据的查询入口，查询分发到各 shard 再汇总；
• Keeper：替代 ZooKeeper，存副本元数据和分布式 DDL 信息。

ClickHouse 不会自动在 shard 间均衡数据，扩容要迁移或重分布。

2.2 MergeTree：写入、合并、索引

一次 insert 生成一个 part，part 内按 ORDER BY 排序，后台线程不断把 part 合并。

insert 1 → part_1
insert 2 → part_2
insert 3 → part_3
后台 merge → part_1_2_3（按 ORDER BY 有序）

• 分区键：决定磁盘目录，常用按月 toYYYYMM(order_date)；分区太多会产生大量小文件。
• 排序键：决定 part 内顺序，也是稀疏主索引的依据；查询命中排序键前缀可跳过大量数据。
• 主索引：默认每 8192 行一条索引，不是每行一条，所以叫稀疏索引。
• merge：后台自动合并；OPTIMIZE TABLE ... FINAL 可以手动触发，但开销大，不适合线上频繁执行。

排序键要匹配查询模式：常按 tenant_id + 日期 过滤，就放前面；高基数列放后面提升点查精度，但索引更大。

2.3 MergeTree 家族

CREATE TABLE dwd_order_status (
  order_id String,
  order_status String,
  update_time DateTime,
  version UInt64
) ENGINE = ReplacingMergeTree(version)
PARTITION BY toYYYYMM(update_time)
ORDER BY (order_id);

merge 时保留 version 最大的行，但 merge 何时发生不确定。查询要 FINAL 或按主键 GROUP BY，不要假设“写完立刻去重”。需要马上看到去重结果时，可以执行 OPTIMIZE TABLE dwd_order_status FINAL 强制合并；它会把整个表或分区重写一遍，数据量大时很慢，只能用于离线修数据，不能当成常规查询手段。

还要注意：ReplacingMergeTree 只在同一分区内去重。上面的示例按 update_time 月分区，同一订单如果在不同月份各更新一次，新旧两行会落在不同分区，FINAL 也留不下唯一结果。要求全局唯一时，分区键要选不随更新变化的字段（例如固定按 order_date 分区），或者查询时按 order_id 再聚合一层。

2.4 物化视图与 TTL

ClickHouse 物化视图是插入触发器：源表写入时按定义好的查询把结果写进目标表，常用 SummingMergeTree 或 AggregatingMergeTree，不会自动回填历史，也不会因源表更新而重算。查询时要再 SUM 一次，因为未合并的 part 可能有重复 key。

TTL 可以按时间删除或冷热分层：

ALTER TABLE dwd_order_detail
MODIFY TTL order_date + INTERVAL 365 DAY;

3. 动手示例

3.1 Docker 启动

docker run -d --name clickhouse -p 8123:8123 -p 9000:9000 clickhouse/clickhouse-server:24.3
docker exec -it clickhouse clickhouse-client

8123 是 HTTP 端口，9000 是 native 端口。

3.2 建表与写入

CREATE DATABASE IF NOT EXISTS ecom;
USE ecom;

CREATE TABLE dwd_order_detail (
  order_id String, item_seq UInt16, order_date Date, category String,
  product_id String, price Float64, freight Float64, quantity UInt16
) ENGINE = MergeTree
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_date, category, order_id)
TTL order_date + INTERVAL 365 DAY;

INSERT INTO dwd_order_detail VALUES
('O1001',1,'2018-08-01','手机','P001',2999,0,1),
('O1001',2,'2018-08-01','图书','P002',59,8,2),
('O1002',1,'2018-08-01','家电','P003',399,0,1),
('O1003',1,'2018-08-02','手机','P001',1999,0,1);

注意：这里省略了 ODS 到 DWD 的清洗打宽。真实项目先用 Spark/Flink 清洗关联，再写进 ClickHouse。

3.3 查询

-- 各品类 GMV
SELECT category, ROUND(SUM(price * quantity + freight), 2) AS gmv
FROM dwd_order_detail
GROUP BY category
ORDER BY gmv DESC;

-- 每日订单数和 GMV
SELECT order_date, COUNT(DISTINCT order_id) AS order_cnt,
       ROUND(SUM(price * quantity + freight), 2) AS gmv
FROM dwd_order_detail
GROUP BY order_date
ORDER BY order_date;

3.4 物化视图和 TTL

CREATE TABLE ads_category_gmv (
  dt Date,
  category String,
  gmv Float64
) ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(dt)
ORDER BY (dt, category);

CREATE MATERIALIZED VIEW ads_category_gmv_mv TO ads_category_gmv
AS
SELECT order_date AS dt, category, SUM(price * quantity + freight) AS gmv
FROM dwd_order_detail
GROUP BY dt, category;

-- 回填历史（MV 不会自动补历史），直接写入目标表
INSERT INTO ads_category_gmv
SELECT order_date, category, SUM(price * quantity + freight)
FROM dwd_order_detail
GROUP BY order_date, category;

SELECT dt, category, SUM(gmv) AS gmv
FROM ads_category_gmv
GROUP BY dt, category
ORDER BY dt, gmv DESC;

4. 常见坑

1. 把 ClickHouse 当 MySQL 用。频繁单行 update/delete、事务、强一致点查都不是强项。
2. 分区粒度过细。按天甚至按小时分区，小文件太多；通常按月或按周。
3. ORDER BY 随便写。排序键不匹配查询模式，索引形同虚设。
4. 以为 ReplacingMergeTree 写完立刻去重。merge 时机不确定，要 FINAL 或按主键聚合。
5. 以为物化视图会自动回填。它只对新插入生效，历史要手动 INSERT SELECT。
6. 大表 join 大表。ClickHouse 的 join 不是强项，容易内存爆炸；优先预打宽或小表广播。
7. SELECT * 和频繁小批量 insert。列存只读需要的列才快；每次 insert 生成 part，要攒批写入。

5. 在线练习

自测：

• 能解释列式存储为什么适合 COUNT、SUM 这类聚合。
• 能根据查询模式设计 PARTITION BY 和 ORDER BY。
• 能说出 ReplacingMergeTree 的去重语义和 FINAL 的代价。
• 能解释 toYYYYMM 分区裁剪为什么能减少扫描，查询要带分区键条件。

6. 面试延伸

面试题：ClickHouse 为什么快？

是什么：面向 OLAP 的列式数据库，核心是列存 + MergeTree + 向量化执行。

解决什么问题：大范围扫描和聚合时，行存 I/O 浪费、压缩率低；ClickHouse 只读需要的列，按分区和排序键裁剪，批量计算。

我的实操边界：我在本地 Docker 跑过建表、聚合、物化视图和 TTL，理解稀疏索引和 merge；没有做过生产集群的 shard/replica 运维。

面试题：MergeTree 的写入和去重过程？

是什么：每次 insert 生成按 ORDER BY 排序的 part，后台 merge 成更大的 part；ReplacingMergeTree 在 merge 时按排序键保留最新版本。

解决什么问题：高吞吐写入不要求随机更新，用批量 part + 后台合并替代 B+ 树随机写。

我的实操边界：我建过 MergeTree 和 ReplacingMergeTree 表，验证过 FINAL 的效果；没有处理过生产环境的大规模 merge 延迟。答题时要强调：去重是最终一致，不是写入即去重。

面试题：ClickHouse 和 MySQL 怎么分工？

是什么：MySQL 是 OLTP 行存数据库，ClickHouse 是 OLAP 列存数据库。

解决什么问题：交易要事务和点查，分析要扫描和聚合，一个引擎很难同时做好。

我的实操边界：我项目里用 MySQL 存业务数据、用 DuckDB/ClickHouse 做分析；没有在生产环境设计过双写或 CDC 同步链路。常见做法：MySQL 负责订单、支付、库存；通过 CDC/定时同步送到 ClickHouse；ADS 结果再同步回 MySQL 供接口查询。

7. 本章验收

• 能解释行存和列存的区别，并说出各自适合的查询。
• 能画出 shard、replica、Distributed 表、Keeper 的关系。
• 能解释 MergeTree 的 part、merge、分区键、排序键和稀疏索引。
• 能独立执行本章的建表、写入、聚合查询和物化视图示例。
• 能说出 ReplacingMergeTree 为什么查询时要 FINAL 或按主键聚合。
• 能列出 ClickHouse 不适合的三个场景，并说明 MySQL 的分工。
• 能给一张日志表设计分区键、排序键和 TTL。