附录 C：ClickHouse 踩坑案例集

学好 ClickHouse = 看完手册 + 踩过 30 个坑。本集收录生产中最常见的 15 个真实踩坑，每条用「现象 → 原因 → 解决 → 预防」四段式描述，配 SQL / 配置片段。适配版本：ClickHouse 24.x。低版本差异会注明。

#	案例	关键词
1	单条小批写入导致 Part 爆炸	INSERT / Part / async
2	滥用 `FINAL` / `OPTIMIZE FINAL`	FINAL / Merge
3	大表 JOIN OOM	JOIN / 内存
4	`Nullable` 列带来的额外开销	Nullable / 性能
5	`LowCardinality` 用在高基数列反而更慢	LowCardinality
6	Mutation 阻塞 Merge	Mutation / Merge
7	物化视图 `POPULATE` 期间数据丢失	MV / POPULATE
8	分区粒度过细导致 Part 数过多	PARTITION BY
9	Distributed 表 `internal_replication` 写错导致重复	Distributed / 副本
10	副本同步 ZooKeeper / Keeper 卡住的排查	副本 / Keeper
11	`OPTIMIZE TABLE … DEDUPLICATE` 误用	DEDUPLICATE
12	时间字段没有时区导致大屏错位	DateTime / 时区
13	物化视图链路因为某一层报错导致全链断流	MV 链
14	升级版本后 SQL 行为变化（Settings 兼容）	升级 / Settings
15	`KILL QUERY` 杀不掉的查询	KILL / 内核

1. 单条小批写入导致 Part 爆炸

现象

业务程序用 Python for row in batch: client.insert(row) 这种姿势，每秒上千次 INSERT。半天后查询变慢到几秒，system.parts 显示 events_raw 活跃 Part 数飙到 5 万。继而出现 Too many parts (5012). Merges are processing significantly slower than inserts 错误，写入直接报错。

原因

ClickHouse 每次 INSERT 都至少生成 一个新 Part（即便只 1 行）。Part 太多会：

后台 Merge 跟不上（Merge 是按容量平方级合并的）；
查询时要打开 N 个 Part，IO 放大；
系统硬上限：parts_to_throw_insert = 3000 之后直接拒写。

解决

sql

-- ① 应急：暂停写入 + 强制合并最近的分区
SYSTEM STOP MERGES learn_ck.events_raw;          -- 排查后再 START
OPTIMIZE TABLE learn_ck.events_raw PARTITION '20260417';

-- ② 业务侧改批：每批 ≥ 10 000 行（推荐 5 万 ~ 10 万）

-- ③ 服务端兜底：开启 async_insert
SET async_insert = 1, wait_for_async_insert = 0,
    async_insert_max_data_size = 10000000,        -- 10MB
    async_insert_busy_timeout_ms = 200;

预防

业务必须攒批写，不允许逐行 INSERT；
使用 Kafka 引擎接入时把 kafka_max_block_size 调到 65536；
监控 system.parts 中单表 active Part 数，超过 1000 就告警；
写入端禁止用 INSERT INTO t VALUES (1),(2),... 拼成几行的小语句。

2. 滥用 `FINAL` / `OPTIMIZE FINAL`

现象

为了让 ReplacingMergeTree 「立刻去重」，业务方在每条查询后面都加 SELECT ... FROM t FINAL；运维同学嫌 Part 多，每天定时跑 OPTIMIZE TABLE t FINAL。

结果：查询 P99 涨 10 倍；OPTIMIZE FINAL 跑 8 小时，磁盘 IO 拉满，业务被拖崩。

原因

FINAL 在查询时把所有 Part 中同主键的行实时合并去重，相当于在线 Merge，CPU + 内存 + IO 三杀；
OPTIMIZE TABLE t FINAL 强制把整个表所有 Part 合并成一个 Part，写放大 = 整表大小，且会持有 Merge 锁。

解决

sql

-- ① 用 argMax / row_number 替代 FINAL 取最新
SELECT user_id, argMax(name, version) AS name
FROM users GROUP BY user_id;

-- ② 必须 FINAL 时配合分区裁剪
SELECT * FROM t FINAL
WHERE event_date = today()        -- 只 FINAL 一个分区
  AND event_type = 'pay';

-- ③ 后台 Merge 已经基本去过重，FINAL 通常**不必要**
SELECT count() FROM t;             -- 不加 FINAL，差异 < 1%

预防

把「FINAL」当作偶尔的 debug 工具，不是日常 SQL；
永远不要 OPTIMIZE TABLE 全表 FINAL；要合并就指定 PARTITION；
业务真要严格去重 → 写入侧保证幂等 / 用 insert_deduplicate / 业务层做。

3. 大表 JOIN OOM

现象

sql

SELECT e.user_id, u.name, count()
FROM events_raw e
JOIN user_profile u ON e.user_id = u.user_id     -- u 1.2 亿行
WHERE e.event_date = today()
GROUP BY e.user_id, u.name;

执行 30 秒后报 Memory limit (for query) exceeded: 20.00 GiB。

原因

ClickHouse 默认 Hash JOIN，右表全部塞进内存形成哈希表。user_profile 1.2 亿行 × 100 字节 ≈ 12GB，单机扛不住；多副本并发查时雪崩。

解决

sql

-- ① 把右表改成 Dictionary（最佳）
CREATE DICTIONARY dict_user (...) PRIMARY KEY user_id
  SOURCE(MYSQL(...)) LIFETIME(600) LAYOUT(HASHED());

SELECT e.user_id, dictGet('dict_user','name',e.user_id), count()
FROM events_raw e
WHERE e.event_date = today()
GROUP BY e.user_id;

-- ② 切 Grace Hash JOIN（24.x，分批 spill 到磁盘）
SELECT ... SETTINGS join_algorithm = 'grace_hash';

-- ③ 小表放右、ANY LEFT JOIN
SELECT ... ANY LEFT JOIN small_dim USING (k);

-- ④ 预聚合后再 JOIN
WITH agg AS (SELECT user_id, count() c FROM events_raw WHERE ... GROUP BY user_id)
SELECT u.name, a.c FROM agg a JOIN user_profile u USING user_id;

预防

右表 ≤ 千万行：直接 Dictionary；
千万 ~ 亿行：先聚合或拆分；
亿级以上：彻底放弃 JOIN，用预聚合 / 大宽表 / GLOBAL JOIN 分发。

4. `Nullable` 列带来的额外开销

现象

把所有列都建成 Nullable(String)、Nullable(Float64)，「跟 MySQL 习惯一致」。结果同样数据量下 CK 比预期多用 30~50% 磁盘，查询慢 20%。

原因

Nullable(T) 在物理上会额外维护一个 <col>.null.bin 位图文件，记录每行是否为 NULL。两点代价：

空间：每行多 1 bit；
性能：向量化执行需要先掩码再算，CPU 流水线被打断。

解决

sql

-- 用「业务上不可能出现的值」替代 NULL
amount   Decimal(12,2)            DEFAULT 0,         -- 而不是 Nullable
country  LowCardinality(String)   DEFAULT '',        -- 空串而非 NULL
event_at DateTime64(3)            DEFAULT toDateTime64(0,3)

预防

只有真正需要区分「未知 vs 0」的字段才用 Nullable；
文档化所有列的 NULL 替代值；
如果非要 NULL，用 SimpleAggregateFunction 类聚合时尤其要测一下性能。

5. `LowCardinality` 用在高基数列反而更慢

现象

把 user_id（10 亿基数）也改成 LowCardinality(UInt64)，「听说能加速」。结果写入慢 2 倍、磁盘多用 20%。

原因

LowCardinality(T) 是列级字典编码：单个 Part 内维护一份 dict 字典 + 用 UInt8/16/32 做编码索引。

基数 ≤ 万级：字典极小，索引极短 → 查询、扫描、过滤都飞起；
基数 > 几十万：字典本身就比原值大，索引退化为 UInt32，没有任何收益反而增加额外开销；
基数 ≈ 行数（如 user_id、session_id）：字典越长越无意义，纯负优化。

解决

sql

-- ✓ 适合 LowCardinality 的列
event_type   LowCardinality(String)        -- 几十种
channel      LowCardinality(String)        -- < 100
country      LowCardinality(String)        -- < 300
status_code  LowCardinality(UInt16)        -- 几百

-- ✗ 不适合
user_id      UInt64                        -- 别加 LowCardinality
session_id   String
order_id     UInt64
ip           IPv4                          -- 已经是定长 4 字节，不要包 LC

预防

经验阈值：基数 > 10 万就别用 LowCardinality；
写完表用 uniqExact(col) 看一眼基数再决定；
文档化每列的取值范围与基数预期。

6. Mutation 阻塞 Merge

现象

误发 ALTER TABLE events_raw DELETE WHERE event_date = '2026-03-01'，几分钟后所有写入和查询变慢；SHOW MERGES 全堆着，system.mutations 里 is_done = 0。

原因

Mutation 本质是重写整个 Part：每个匹配条件的 Part 会被读出来 → 过滤 → 写新 Part。这件事和 Merge 共用同一个线程池，于是：

Mutation 没跑完 → 后续 Merge 排队；
后续 INSERT 又生成新 Part → Part 数继续涨；
整个表性能骤降。

解决

sql

-- ① 立即 KILL
KILL MUTATION WHERE database='learn_ck' AND table='events_raw'
  AND mutation_id='mutation_42.txt';

-- ② 这种「整天数据」的删除，永远用 DROP PARTITION
ALTER TABLE events_raw DROP PARTITION '20260301';

-- ③ 大范围 WHERE 删除用 lightweight DELETE（24.x）
DELETE FROM events_raw WHERE event_date = '2026-03-01';
-- lightweight 只标记 _row_exists，不立即重写

预防

任何 ALTER ... DELETE/UPDATE 上线前先在测试库测时长；
提供「按分区批量删除」的运维脚本，禁止裸跑 ALTER ... DELETE；
监控 system.mutations 里 is_done=0 且 create_time > 30 分钟的告警。

7. 物化视图 `POPULATE` 期间数据丢失

现象

新建 MV 用了 POPULATE 把历史数据拉一遍，看似很方便。一周后业务方反馈：MV 里少了 POPULATE 期间的几百万行。

原因

POPULATE 的语义是：先用 INSERT ... SELECT 把当前源表的快照灌进目标表，再把 MV 挂上去。这两步之间的窗口期里，源表新写入的数据既没有进 MV、也没有走 INSERT … SELECT，直接丢。

解决与预防

sql

-- ✗ 千万别这么写（生产）
CREATE MATERIALIZED VIEW mv ... POPULATE AS SELECT ...;

-- ✓ 正确姿势：先 MV，后回填
CREATE MATERIALIZED VIEW mv TO target AS SELECT ...;     -- 立刻挂上，不丢实时数据
INSERT INTO target SELECT ... FROM source                -- 手动回填历史
  WHERE event_date < today();

如果数据量太大，把回填按分区批量做，每批一个分区。

8. 分区粒度过细导致 Part 数过多

现象

业务上 PARTITION BY (toYYYYMMDD(event_date), user_country, channel)，「以为能更细分裂」。结果 1 周后单表 Part 数破 10 万，查询慢得离谱。

原因

Part 数 = 分区数 × 每分区 Part 数。分区粒度细 → 分区数爆炸 → 每个分区写入的 Part 没有足够的「邻居」可合 → Merge 收益急剧下降，整体 Part 数居高不下。

解决

sql

-- ✗ 错的
PARTITION BY (toYYYYMMDD(event_date), country, channel)

-- ✓ 一般推荐
PARTITION BY toYYYYMMDD(event_date)        -- 高频分区，每天一个
PARTITION BY toYYYYMM(event_date)          -- 中频，每月一个

-- 排序键里再加细分维度
ORDER BY (event_date, event_type, country, channel, user_id)

预防

分区只用「时间」一个维度，且粒度别比天更细（除非真的每天 > 50 亿行）；
总分区数控制在几百量级，超过 1000 就要警惕；
维度筛选靠 ORDER BY 排序键 + Skip Index，不靠分区。

9. Distributed 表 `internal_replication` 写错导致重复

现象

3 分片 × 2 副本集群，往 Distributed 表 INSERT 1 万行后 SELECT count() 出来 2~3 万行。

原因

集群配置中：

xml

<shard>
  <internal_replication>false</internal_replication>      <!-- ❌ -->
  <replica><host>r1</host></replica>
  <replica><host>r2</host></replica>
</shard>

internal_replication = false 表示 Distributed 表负责把同一行往每个副本各 INSERT 一次。同时本地表又是 ReplicatedMergeTree，副本之间还会自己再同步一次 → 数据被插了 2~3 倍。

解决

xml

<!-- 本地表是 ReplicatedMergeTree 时，永远用 true -->
<shard>
  <internal_replication>true</internal_replication>
  <replica><host>r1</host></replica>
  <replica><host>r2</host></replica>
</shard>

口诀：「Replicated → internal_replication = true」；只有非 Replicated 引擎（如 MergeTree）做副本时才 false。

预防

上线前务必跑一次「写 100 行，查 100 行」的对账脚本；
配置变更走 review；
加监控：发现某分片本地表行数 ≠ Distributed 写入行数，立即告警。

10. 副本同步 ZooKeeper / Keeper 卡住的排查

现象

某副本写入正常但 system.replicas.queue_size > 5000 越涨越多；其他副本数据看着都新，唯独这台越落越远。

原因（按概率从高到低）

Keeper / ZooKeeper 网络卡顿：副本拉 Part 列表慢；
磁盘满 / 慢盘：Fetch Part 但写不进去；
副本的 replicated_fetches_pool_size 太小：并发拉 Part 不够；
某个 Part fetch 失败反复重试：system.replication_queue.last_exception 有线索；
DDL 卡在 Keeper：ZooKeeper 连接断 → 重连风暴。

排查 SQL

sql

-- ① 看队列堆积
SELECT database, table, queue_size, absolute_delay,
       inserts_in_queue, merges_in_queue, queue_oldest_time
FROM system.replicas
WHERE queue_size > 100 ORDER BY queue_size DESC;

-- ② 看队列里具体在卡什么
SELECT database, table, type, source_replica, last_exception, num_tries
FROM system.replication_queue
WHERE num_tries > 3
ORDER BY num_tries DESC LIMIT 20;

-- ③ Keeper 健康度
SELECT * FROM system.zookeeper WHERE path = '/clickhouse/task_queue/ddl';

解决

Fetch 卡：SYSTEM RESTART REPLICA t 重新挂载；或 SYSTEM SYNC REPLICA t TIMEOUT 600 强同步；
Keeper 慢：升级到 ClickHouse Keeper 或扩 ZK 集群；
调大 <background_fetches_pool_size>、<replicated_fetches_http_connection_timeout>；
实在恢复不了：DETACH PARTITION → 从其他副本物理拷过来 → ATTACH。

预防

监控 queue_size / absolute_delay / last_exception；
Keeper 节点数推荐 3 ~ 5，并独立部署；
集群升级时先升一台副本观察。

11. `OPTIMIZE TABLE … DEDUPLICATE` 误用

现象

业务方发现 ReplacingMergeTree 没去干净，跑了 OPTIMIZE TABLE t FINAL DEDUPLICATE，期望全表去重。结果 6 小时后查询发现：部分应该保留的行也被删了！

原因

DEDUPLICATE 默认按所有列比对，不是按主键。

sql

-- 等价于 DEDUPLICATE BY *
OPTIMIZE TABLE t FINAL DEDUPLICATE;

如果两行在主键之外某个字段上不同（比如 created_at 是写入时的 now()），DEDUPLICATE 不会认为它们重复；反过来如果两行在主键 + 业务字段上完全一致，业务上有意义的两行也会被删一。

解决

sql

-- ✓ 显式指定按业务唯一键去重
OPTIMIZE TABLE t FINAL DEDUPLICATE BY user_id, order_id;

-- ✓ 排除不该参与比较的列
OPTIMIZE TABLE t FINAL DEDUPLICATE BY * EXCEPT (created_at, ingest_time);

预防

DEDUPLICATE 必须明确写 BY 列名；
生产去重优先靠 ReplacingMergeTree + 写入幂等 + argMax 查询，少用 OPTIMIZE；
跑前先在镜像库验证。

12. 时间字段没有时区导致大屏错位

现象

大屏显示「今日 PV 走势」，业务在 8 点上班看到「今天 0 点 - 8 点」全是 0；到 10 点反而看到所谓的「昨天数据」。运营怀疑数据丢了。

原因

DateTime / DateTime64 不带时区参数时，CK 内部按 UTC 存储 + 默认按服务器时区显示。如果：

服务器时区是 UTC；
应用上报的 event_time 已经是 UTC；
但前端按本地时区（UTC+8）显示，

那么写入 event_time = '2026-04-17 00:00'（UTC）会在 UTC+8 看作 08:00，导致大屏错位 8 小时。

解决

sql

-- ① 建表时显式带时区
event_time DateTime64(3, 'Asia/Shanghai')

-- ② 查询时显式转
SELECT toTimeZone(event_time, 'Asia/Shanghai') AS t, count()
FROM events_raw
WHERE event_date = today()                  -- today() 跟随会话时区
GROUP BY t;

-- ③ 设置会话时区
SET timezone = 'Asia/Shanghai';

预防

全教程统一约定：所有时间列必须带时区；
SDK 上报时统一用 ISO-8601 带时区字符串（2026-04-17T08:00:00+08:00）；
服务器、客户端、文档时区写在 README 第一行。

13. 物化视图链路因为某一层报错导致全链断流

现象

凌晨发现大屏不更新了。events_raw 还在涨；events_agg_pv_uv 不涨。重启不解决。

原因

链式 MV events_kafka → mv_kafka_to_raw → events_raw → mv_raw_to_pv_uv → events_agg_pv_uv 中，某次 INSERT 有一行 ext 列含非 UTF-8 字节 → mv_raw_to_pv_uv 计算 groupArray 时报错 → ClickHouse 默认行为是整个 INSERT 回滚：连带 events_raw 和 events_agg_pv_uv 都没写进去。Kafka 引擎下次又拉同一批 → 死循环。

排查

sql

SELECT database, table, last_exception
FROM system.materialized_views
WHERE last_exception != '';

SELECT event_time, message
FROM system.text_log
WHERE event_time > now() - 600
  AND message LIKE '%mv_raw_to_pv_uv%'
ORDER BY event_time DESC LIMIT 50;

解决

sql

-- ① 单独这条 INSERT 失败不影响其他 MV
SET materialized_views_ignore_errors = 1;

-- ② 跳过损坏数据继续消费
SET kafka_skip_broken_messages = 100;

-- ③ 在 MV 里加防御性处理
CREATE MATERIALIZED VIEW mv_raw_to_pv_uv
TO events_agg_pv_uv AS
SELECT
    event_date,
    coalesce(toString(channel), '') AS channel,
    sumState(toUInt64(1)) AS pv,
    uniqExactState(user_id) AS uv
FROM events_raw
GROUP BY event_date, channel;

预防

上游 SDK 数据校验（schema validate / 字段强类型）；
链路中每层 MV 都加 try-catch 类设置；
system.text_log 接入告警；
每天对账 events_raw 行数 vs events_agg_pv_uv 总和。

14. 升级版本后 SQL 行为变化（Settings 兼容）

现象

集群从 22.8 升级到 24.8 后，部分 SQL 报错或结果与升级前不一致。例如：

JOIN 默认算法从 partial_merge 变成 direct，某些大查询变快但某些 OOM；
optimize_aggregation_in_order 默认开启，结果集顺序改变；
enable_filesystem_cache 默认开 → 重启后第一次查询慢；
allow_experimental_* 一堆实验特性正式化，旧 SQL 走了新路径。

排查

sql

-- 看哪些 Settings 在新版默认值变了
SELECT name, value, changed, default_value, description
FROM system.settings
WHERE changed = 0 AND default_value != ''
  AND name LIKE '%join%';

-- 抓取错误
SELECT name, count(), max(last_error_message)
FROM system.errors
GROUP BY name ORDER BY count() DESC LIMIT 20;

解决与预防

xml

<!-- 在 users.d/profile.xml 给关键 Settings 锁定值 -->
<profiles>
  <default>
    <join_algorithm>hash</join_algorithm>
    <optimize_aggregation_in_order>0</optimize_aggregation_in_order>
    <materialized_views_ignore_errors>1</materialized_views_ignore_errors>
  </default>
</profiles>

升级前全文 diff changelog；
跨大版本走「灰度 1 副本 → 观察 1 周 → 全集群」三步；
把核心 SQL 加进自动化回归套件（clickhouse-test）。

15. `KILL QUERY` 杀不掉的查询

现象

某条 SELECT 跑了 30 分钟，运维 KILL QUERY WHERE query_id='...'，结果 system.processes 还在。KILL QUERY ... SYNC 也卡着不返回。

原因

KILL 是「协作式」终止：内核要等查询执行到一个可中断点（每个算子的循环边界）才能响应。如果查询正卡在：

一段大 hash 表构建里（buildHashTable），可能要等 GB 级数据全部读完才检查 cancel；
网络读 Distributed 远端结果，等远端先响应；
C++ 内部死循环 / 第三方库（罕见但发生过）；

KILL 自然就「杀不动」。

解决

sql

-- ① 优雅 KILL，先发信号
KILL QUERY WHERE query_id = '<uuid>';

-- ② 等不及的话，硬 KILL（24.x 在某些版本支持）
KILL QUERY WHERE query_id = '<uuid>' SYNC;

-- ③ 再不行，只能：
--    a) 限制内存让它自己 OOM 退出
--    b) 远程拒绝它的 sources（Distributed 场景）
--    c) 重启该实例（最后手段）

-- 预防型：永远配 statement_timeout / max_execution_time
SET max_execution_time = 60;
SET timeout_overflow_mode = 'throw';

预防

给 BI / 分析师 Profile 强制 max_execution_time = 60、max_memory_usage = 8G；
大查询路由到只读副本，不影响主写；
对 OLAP 集群额外配 query_thread_log 抓「有线程 alive 但 query 已 cancel」类异常。

📌 结语：踩坑案例的最大价值不是「记住每条」，而是「形成直觉」 —— 看到任何违反 ClickHouse 的设计哲学（小批写、行级 update、强一致 join、跨表 transactions）的需求时，先停一下问自己：「我是不是在拿 OLTP 思维用 OLAP 引擎？」
推荐三件套搭配阅读：appendix_cheatsheet.md（速查）· appendix_ck_vs_mysql.md（对比）· interview.md（面试索引）。

附录 C：ClickHouse 踩坑案例集 ​

目录 ​

1. 单条小批写入导致 Part 爆炸 ​

2. 滥用 FINAL / OPTIMIZE FINAL ​

3. 大表 JOIN OOM ​

4. Nullable 列带来的额外开销 ​

5. LowCardinality 用在高基数列反而更慢 ​

6. Mutation 阻塞 Merge ​

7. 物化视图 POPULATE 期间数据丢失 ​

8. 分区粒度过细导致 Part 数过多 ​

9. Distributed 表 internal_replication 写错导致重复 ​

10. 副本同步 ZooKeeper / Keeper 卡住的排查 ​

11. OPTIMIZE TABLE … DEDUPLICATE 误用 ​

12. 时间字段没有时区导致大屏错位 ​

13. 物化视图链路因为某一层报错导致全链断流 ​

14. 升级版本后 SQL 行为变化（Settings 兼容） ​

15. KILL QUERY 杀不掉的查询 ​