主题
第 16 章 性能调优与查询分析
学习目标：学完这一章，你能像一个老 DBA 一样，只看
system.query_log一眼就指出谁是慢查询元凶；能用EXPLAIN PLAN / PIPELINE / ESTIMATE三件武器拆解任意 SQL；能识别并改写 8 种最常见的「ClickHouse 反模式」；能调整关键的 5 ~ 6 个 server / query 参数；能配合trace_log+clickhouse-flamegraph做火焰图分析。最后给一个完整的「优化前 / 优化后」对照实验：同一份数据、同一条查询，read_rows、read_bytes、memory_usage、query_duration_ms 四个指标全方位对比。
0. 开场白：调优 ≠ 玄学
很多读者对「性能调优」有一种「老中医搭脉」般的玄学想象，仿佛要靠经验和直觉。但 ClickHouse 给了你一整套可观测、可量化、可复现的工具链：
SQL 提交 ──▶ 解析 ──▶ Plan ──▶ Pipeline ──▶ 执行 ──▶ 写 query_log
│ │ │ │ │
▼ ▼ ▼ ▼ ▼
EXPLAIN AST EXPLAIN EXPLAIN trace_log / system.query_log
PLAN PIPELINE query_thread_log + system.text_log这一章我们就把这套工具链一件一件交给你。性能调优在 ClickHouse 里不是玄学，是「看仪表盘 + 改 SQL/参数」的工程问题。
📌 首次术语解释 · trace_log：ClickHouse 内置的「采样栈帧日志表」，开启后会以一定频率（默认 1ms）抓取每个查询线程的调用栈，相当于「内嵌的 perf record」，可以直接绘成火焰图。
16.1 三件武器：EXPLAIN PLAN / PIPELINE / ESTIMATE
ClickHouse 的 EXPLAIN 不是一条命令，而是一个家族，按粒度从粗到细分别对应「逻辑计划 / 物理流水线 / 数据估算」三个层面。
16.1.1 一句话区分
| 命令 | 看什么 | 类比 PG / MySQL |
|---|---|---|
EXPLAIN PLAN | 逻辑算子树（Aggregating / Filter / ReadFromMergeTree） | PG EXPLAIN（不带 ANALYZE） |
EXPLAIN PIPELINE | 物理执行流水线（每个 Processor 多少线程） | PG EXPLAIN (ANALYZE, VERBOSE) 中并行部分 |
EXPLAIN ESTIMATE | 不真正执行，只估算要读多少 part / mark / row | PG EXPLAIN（带 cost 估算） |
EXPLAIN AST | 解析后的语法树 | 一般用不到 |
EXPLAIN SYNTAX | 重写后的 SQL（应用了 optimize_*） | PG 的 pg_stat_statements 规整化 SQL |
EXPLAIN QUERY TREE | 22.9+ 引入的新分析器查询树 | 无对应 |
16.1.2 EXPLAIN PLAN：看「我准备怎么打」
sql
EXPLAIN PLAN
SELECT user_id, count() AS pv
FROM learn_ck.events_v1
WHERE event_date BETWEEN '2025-04-01' AND '2025-04-30'
AND event_type = 'click'
GROUP BY user_id
ORDER BY pv DESC
LIMIT 10;输出（节选，已折叠）：
Expression ((Project names + Projection))
Limit (preliminary LIMIT (with OFFSET))
Sorting (Merge sorted streams for ORDER BY, without aggregation)
Expression
MergingAggregated
Aggregating
Expression (Before GROUP BY)
Filter (WHERE)
ReadFromMergeTree (learn_ck.events_v1)
ReadType: InOrder
Parts: 12
Granules: 1837怎么读：
- 从下往上读：
ReadFromMergeTree是数据入口，Parts: 12告诉你查询要扫 12 个 Part，Granules: 1837是稀疏索引筛掉非命中后还剩的 granule 数（每 granule 默认 8192 行）。 Filter (WHERE)在读取后做行级过滤 —— 如果它过滤掉的行很多，说明你的WHERE条件没走到分区或主键，再压一条EXPLAIN ESTIMATE看看就清楚。Aggregating → MergingAggregated是两阶段聚合（分线程局部聚合 → 全局合并），是 ClickHouse 多线程 GROUP BY 的标准模式。
加上 actions=1 可以看每个步骤具体的表达式：
sql
EXPLAIN PLAN actions = 1
SELECT count() FROM learn_ck.events_v1 WHERE event_date = today();加上 indexes=1 可以看索引命中情况：
sql
EXPLAIN PLAN indexes = 1
SELECT count() FROM learn_ck.events_v1 WHERE user_id = 12345;会输出每个 part 的 partition prune、primary key prune、skip index 三层裁剪后剩多少 mark，是判断「主键和分区是否真的生效」的最直接证据。
16.1.3 EXPLAIN PIPELINE：看「我会用几只手打」
PLAN 给的是抽象算子树，PIPELINE 给的是真正的「物理流水线」 —— 每个 Processor 跑在几个线程里。
sql
EXPLAIN PIPELINE
SELECT user_id, count() FROM learn_ck.events_v1 GROUP BY user_id;输出：
(Expression)
ExpressionTransform × 8
(Aggregating)
Resize 8 → 8
AggregatingTransform × 8
StrictResize 8 → 8
(Expression)
ExpressionTransform × 8
(ReadFromMergeTree)
MergeTreeThread × 8 0 → 1× 8 的意思是「这一个算子开了 8 个线程在跑」 —— 它由 max_threads 设置决定，通常等于服务器 CPU 核数。如果你看到关键算子是 × 1（比如 MergingSortedTransform × 1），说明这一段卡在单线程上，可能就是瓶颈。
进阶用法：加 graph=1 可以输出 DOT 图：
sql
EXPLAIN PIPELINE graph = 1
SELECT user_id, sum(value) FROM learn_ck.events_v1 GROUP BY user_id;把输出粘到 GraphvizOnline 就能看见一张可视化的 Pipeline 图。
16.1.4 EXPLAIN ESTIMATE：先估再决定打不打
最容易被忽视、也最实用的一条：它不真正执行，只告诉你这条 SQL 要读多少 part / mark / row。
sql
EXPLAIN ESTIMATE
SELECT count() FROM learn_ck.events_v1 WHERE event_date = '2025-04-15';输出：
┌─database─┬─table──────┬─parts─┬─rows────┬─marks─┐
│ learn_ck │ events_v1 │ 1 │ 8388608 │ 1024 │
└──────────┴────────────┴───────┴─────────┴───────┘读到 100 万行你心里大概有数；如果它显示 rows: 90000000000，你就知道应该立即按 Ctrl+C，否则查询会拖死整个集群。
📌 小技巧：所有「我担心这条 SQL 要扫多少数据」的场景，都先
EXPLAIN ESTIMATE一下。它几乎不耗资源，是 OLAP 时代的「干跑（dry-run）」。
16.1.5 与 PG / MySQL 的对比
📌 横向对比
维度 ClickHouse PostgreSQL MySQL 看逻辑算子 EXPLAIN PLANEXPLAINEXPLAIN FORMAT=TREE看真实代价 EXPLAIN ESTIMATE+system.query_logEXPLAIN ANALYZEEXPLAIN ANALYZE(8.0+)看物理并行 EXPLAIN PIPELINE部分体现于 Workers Planned无对应 重写后的 SQL EXPLAIN SYNTAXEXPLAIN (VERBOSE)部分体现无 本质差异：PG / MySQL 的 EXPLAIN ANALYZE 是「实际执行一遍并打点」；ClickHouse 因为是 OLAP，一条 SQL 跑完可能要扫 TB 级数据，所以拆成两件 ——
EXPLAIN ESTIMATE干跑估算，system.query_log事后归因。
16.2 必看视图清单：性能问题的「摄像头」
ClickHouse 把所有运行时信息都「关系化」放进了 system.* 视图，相当于一个开放式的内部仪表盘。下面这 8 张表是性能调优的绝对核心，闭着眼也要知道它们里面装的是什么。
16.2.1 system.query_log：所有跑过的 SQL 都在这
每条 SQL 的「身份证 + 体检报告」。默认每秒批量刷一次盘。
sql
-- 找出过去 1 小时最慢的 10 条查询
SELECT
query_start_time,
query_duration_ms,
read_rows,
formatReadableSize(read_bytes) AS read,
formatReadableSize(memory_usage) AS mem,
user,
substring(query, 1, 80) AS sql_preview
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 HOUR
AND type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10;关键字段速记：
| 字段 | 含义 | 调优意义 |
|---|---|---|
type | QueryStart/QueryFinish/ExceptionBeforeStart/ExceptionWhileProcessing | 同一条 query_id 会有多行，归因要 join 自身 |
query_duration_ms | 端到端耗时 | 第一指标，越大越慢 |
read_rows / read_bytes | 实际从磁盘 / cache 读了多少 | 衡量「索引和分区是否生效」 |
result_rows / result_bytes | 返回给客户端的行数 / 字节数 | result 远小于 read，说明 WHERE/GROUP BY 起到了过滤作用 |
memory_usage | 峰值内存 | 触发 max_memory_usage 时报 OOM |
query_id | 唯一 ID | 排查时用它去 query_thread_log / trace_log 里 join |
Settings | 当时的 settings 快照 | 看是谁开了奇怪的参数 |
ProfileEvents | 详细计数器（NestedMap） | 包含 SelectedParts、SelectedMarks、OSCPUVirtualTimeMicroseconds 等几百个细粒度指标 |
典型问诊三连：
sql
-- 1) 谁吃内存
SELECT query_id, query, formatReadableSize(memory_usage) AS m
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 DAY
AND type = 'QueryFinish'
ORDER BY memory_usage DESC LIMIT 20;
-- 2) 谁吃磁盘 IO
SELECT query_id, query, formatReadableSize(read_bytes) AS r
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 DAY
AND type = 'QueryFinish'
ORDER BY read_bytes DESC LIMIT 20;
-- 3) 谁的 result/read 比最差（典型「捞海带」型查询）
SELECT query_id, read_rows, result_rows,
round(read_rows / greatest(result_rows, 1)) AS read_amp,
substring(query, 1, 100) AS q
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 DAY
AND type = 'QueryFinish'
AND read_rows > 1000000
ORDER BY read_amp DESC LIMIT 20;📌 实用 Tip：
ProfileEvents是Map(String,UInt64)，可以用ProfileEvents['SelectedMarks']直接取出。
16.2.2 system.processes：「现在有谁在跑」
一张「实时进程列表」，相当于 MySQL 的 SHOW PROCESSLIST。
sql
SELECT
query_id,
user,
elapsed,
formatReadableSize(memory_usage) AS mem,
read_rows,
substring(query, 1, 80) AS q
FROM system.processes
ORDER BY elapsed DESC;
-- 杀掉某条卡住的查询
KILL QUERY WHERE query_id = '...';注意：KILL QUERY 是「打断信号」，会给查询设置一个标志位让它在下一个安全点退出，不是立刻 kill，最长可能要等几秒。
16.2.3 system.parts：表的「物理形象」
每一行代表磁盘上的一个 Part。是判断写入健康度和合并状态的根基。
sql
-- 看某张表的 part 总览
SELECT
partition,
count() AS parts_cnt,
sum(rows) AS rows,
formatReadableSize(sum(bytes_on_disk)) AS size_disk,
formatReadableSize(sum(data_compressed_bytes)) AS comp,
formatReadableSize(sum(data_uncompressed_bytes)) AS raw,
round(sum(data_uncompressed_bytes)/sum(data_compressed_bytes), 2) AS compress_ratio
FROM system.parts
WHERE database = 'learn_ck' AND table = 'events_v1' AND active
GROUP BY partition
ORDER BY partition;关键字段：
active：必须加这个过滤，否则会把已经被合并掉但还没清理的旧 part 也算进来。level：合并次数，0 表示从未合并过；level 越高说明已被反复合并到很大。bytes_on_diskvsdata_uncompressed_bytes：相除就是压缩比，OLAP 列存通常 5 ~ 20 倍。min_block_number/max_block_number：这块 part 来自哪几个 INSERT。
如果你看到某个分区 parts_cnt > 100，说明 Merge 跟不上了，要么是写入太碎，要么是 Merge 被限速。
16.2.4 system.merges & system.mutations：后台两座工厂
sql
-- 当前正在跑的 merge
SELECT
database, table, elapsed,
progress, num_parts,
formatReadableSize(total_size_bytes_compressed) AS size,
is_mutation
FROM system.merges;
-- 当前未完成的 mutation（ALTER UPDATE/DELETE）
SELECT
database, table, mutation_id,
create_time, command,
parts_to_do, is_done, latest_fail_reason
FROM system.mutations
WHERE is_done = 0;经验：system.mutations 里 is_done=0 的行如果累积超过 50 条，说明你滥用了 ALTER UPDATE/DELETE，要么改用 ReplacingMergeTree / 软删，要么走 REPLACE PARTITION（详见第 12 章）。
16.2.5 system.metrics / system.events / system.asynchronous_metrics：实例健康度三件套
| 视图 | 性质 | 典型字段 |
|---|---|---|
system.metrics | 当前瞬时值（Gauge） | Query、Merge、PartMutation、MemoryTracking、BackgroundMergesAndMutationsPoolTask |
system.events | 累计计数器（Counter） | Query、SelectQuery、InsertedRows、MarkCacheHits、MarkCacheMisses |
system.asynchronous_metrics | 异步采集的复杂指标（30 秒一次） | MarkCacheBytes、UncompressedCacheBytes、MaxPartCountForPartition、ReplicasMaxAbsoluteDelay |
sql
-- 当前实例总览
SELECT metric, value FROM system.metrics
WHERE metric IN ('Query', 'Merge', 'PartMutation', 'BackgroundMergesAndMutationsPoolTask');
-- 看 mark cache 命中率（缓存的稀疏索引，命中率应 > 95%）
SELECT
sum(value) AS total,
sumIf(value, event = 'MarkCacheHits') AS hits,
sumIf(value, event = 'MarkCacheMisses') AS misses,
round(hits / (hits + misses) * 100, 2) AS hit_ratio_pct
FROM system.events
WHERE event LIKE 'MarkCache%';
-- 副本延迟（亚秒级即正常，大于 1 分钟要警觉）
SELECT metric, value FROM system.asynchronous_metrics
WHERE metric LIKE '%Replica%';16.2.6 system.query_thread_log & system.text_log
query_thread_log：query_log 的「子表」，记录每个查询用过的所有线程的细节，用query_idjoin。可以分析「8 个线程里是不是有 1 个特别慢」。text_log：CH 自己写出来的应用日志，按级别（trace / debug / info / warning / error）落库。比起去/var/log/clickhouse-server/clickhouse-server.loggrep，SELECT FROM text_log WHERE level='Error'顺手多了。
sql
-- 最近的 ERROR 日志
SELECT event_time, logger_name, message
FROM system.text_log
WHERE level = 'Error' AND event_time > now() - INTERVAL 1 HOUR
ORDER BY event_time DESC LIMIT 50;📌 默认禁用：
query_thread_log、text_log、trace_log默认是关闭的（量太大），需要在config.xml里显式打开（详见第 17 章）。
16.3 8 大反模式 vs 正确做法
性能问题 80% 长成下面这 8 个样子，逐个对照、逐个改写就能拿到立竿见影的提升。
16.3.1 反模式 ①：单条小批写入
python
# ❌ 反模式：每条 INSERT 都触发一次「Part 流水线」
for row in stream:
client.insert("events", [row]) # 100 万行 = 100 万个 part = 系统报警正确做法：
python
# ✅ 客户端攒批
batch = []
for row in stream:
batch.append(row)
if len(batch) >= 100_000:
client.insert("events", batch)
batch = []
if batch:
client.insert("events", batch)sql
-- ✅ 服务端攒批（async_insert，详见第 7 章）
SET async_insert = 1, wait_for_async_insert = 0;
INSERT INTO events VALUES (...);或者用 Buffer 引擎在写入前置一层「内存蓄水池」。
16.3.2 反模式 ②：大表 JOIN
sql
-- ❌ 反模式：两张大表 JOIN，CH 默认把右表全部 hash 进内存，亿级直接 OOM
SELECT u.country, count()
FROM events e
JOIN users u ON u.id = e.user_id
GROUP BY u.country;正确做法（按场景三选一）：
字典化右表（推荐）：把
users做成Dictionary，JOIN 变成dictGet。sqlCREATE DICTIONARY users_dict ( id UInt64, country String ) PRIMARY KEY id SOURCE(MYSQL(host '127.0.0.1' user 'x' table 'users' password '')) LIFETIME(MIN 300 MAX 600) LAYOUT(HASHED()); SELECT dictGet('users_dict', 'country', e.user_id) AS country, count() FROM events e GROUP BY country;预 JOIN 成宽表：在 ETL 阶段把
country字段就冗余进events，这就是 OLAP 的「宽表哲学」。物化视图：让物化视图在写入时完成关联，查询时只扫一张表。
16.3.3 反模式 ③：SELECT *
sql
-- ❌ 反模式：列存数据库里 SELECT *，等于「按列扫描的好处全部抹掉」
SELECT * FROM events WHERE user_id = 12345;正确做法：
sql
-- ✅ 只取用得到的列
SELECT event_time, event_type, value
FROM events WHERE user_id = 12345;列存数据库里列数 = 文件数 = IO 单位。
SELECT *在 50 列宽表上等于一次性打开 50 个文件读取，与「为什么用列存」的初衷完全相反。
16.3.4 反模式 ④：WHERE 不带分区键
sql
-- ❌ 反模式：表按 toYYYYMM(event_date) 分区，但 WHERE 不带 event_date
SELECT count() FROM events WHERE event_type = 'click';正确做法：
sql
-- ✅ 在 WHERE 里加分区裁剪条件
SELECT count() FROM events
WHERE event_date >= today() - 7
AND event_type = 'click';可以用 EXPLAIN PLAN indexes=1 验证是否真的发生了 partition prune（输出里会出现 Pruned by partition key: 23 / 24 之类的信息）。
16.3.5 反模式 ⑤：高基数 LowCardinality
sql
-- ❌ 反模式：把 user_id 这种高基数列设成 LowCardinality
CREATE TABLE bad_t (
user_id LowCardinality(String), -- 上亿不同值，字典反而成累赘
...
);原理：LowCardinality 是「全局字典 + 字典 ID」的优化，适合 < 10 万种取值的列（国家、状态码、来源渠道）。如果基数本身就上亿，字典体积比原值还大，写入慢、查询慢、内存爆。
正确做法：
sql
-- ✅ 高基数用普通 String / UInt64
CREATE TABLE good_t (
user_id UInt64, -- 高基数：直接整数
country LowCardinality(String), -- 低基数：合理使用
...
);经验阈值：基数 < 10000 一定值得用 LowCardinality；10000 ~ 100000 视情况；> 100000 一般不用。
16.3.6 反模式 ⑥：Nullable 滥用
sql
-- ❌ 反模式：能用默认值的字段也包了 Nullable
CREATE TABLE bad_t (
user_id UInt64,
age Nullable(UInt8),
country Nullable(String),
...
);Nullable(T) 在磁盘上多一个 .null.bin 文件标记每一行是不是 NULL，额外 1 bit/行 + 多一次 IO + 阻断 SIMD 向量化。
正确做法：
sql
-- ✅ 用「业务约定的默认值」代替 NULL
CREATE TABLE good_t (
user_id UInt64,
age UInt8 DEFAULT 0, -- 0 表示未知
country LowCardinality(String) DEFAULT '', -- 空串表示未知
...
);16.3.7 反模式 ⑦：FINAL 滥用
sql
-- ❌ 反模式：每次查询都加 FINAL 触发「实时合并」
SELECT * FROM order_replacing FINAL WHERE user_id = 1;FINAL 让 CH 在查询时临时合并相关 part 以保证去重 / 折叠语义，是单线程的、内存爆炸的、慢得离谱的。
正确做法：
sql
-- ✅ 法一：用 argMax 在查询时取「最新版本」，避开 FINAL
SELECT user_id, argMax(status, version) AS status
FROM order_replacing
GROUP BY user_id;
-- ✅ 法二：定期 OPTIMIZE TABLE ... FINAL（运维窗口）让物理去重生效
OPTIMIZE TABLE order_replacing FINAL DEDUPLICATE;16.3.8 反模式 ⑧：高频 Mutation
sql
-- ❌ 反模式：用 ALTER UPDATE 模拟 OLTP
ALTER TABLE events UPDATE status = 'paid' WHERE order_id = 123;
ALTER TABLE events UPDATE status = 'paid' WHERE order_id = 124;
... -- 每条都触发整个 part 重写正确做法：
sql
-- ✅ 法一：状态变更场景用 ReplacingMergeTree，写新版本即可
INSERT INTO events_replacing (order_id, status, version) VALUES (123, 'paid', now64());
-- ✅ 法二：软删 + 定期归档
ALTER TABLE events ADD COLUMN is_deleted UInt8 DEFAULT 0;
INSERT INTO events SELECT *, 1 FROM events WHERE order_id = 123; -- 标记删除
-- ✅ 法三：批量重写整个分区
ALTER TABLE events REPLACE PARTITION '202504' FROM events_staging;16.4 关键参数调优
下面这 6 个参数是 OLAP 场景里最值得「主动设置」的 —— 默认值并不总是最佳。
16.4.1 max_threads：单查询并行度
默认值：
auto（≈ CPU 物理核数）。
它控制单条查询能开多少线程读取与处理。
sql
SET max_threads = 16;
SELECT count() FROM events;- 调大：复杂聚合 / 大扫描会更快，但多条并发时会互相挤；
- 调小：高并发短查询场景反而吞吐更高，避免线程上下文切换。
经验：在线查询服务的 user profile 里设 max_threads = ceil(cpu_cores / expected_qps)。
16.4.2 max_memory_usage：单查询内存上限
默认值：10 GiB。
sql
SET max_memory_usage = 21474836480; -- 20 GB超过即报 Memory limit (for query) exceeded。不是越大越好 —— 它是一道防御墙，避免一条「失控的 SQL」吃光整个实例的内存。
配套：max_memory_usage_for_user（单用户总和）、max_server_memory_usage（全实例总和）。
16.4.3 max_bytes_before_external_group_by / max_bytes_before_external_sort：超量后落盘
默认值：0（关闭，超过
max_memory_usage就报错）。
sql
SET max_bytes_before_external_group_by = 10000000000; -- 10 GB
SET max_bytes_before_external_sort = 10000000000;打开后，GROUP BY / ORDER BY 的中间状态在超过阈值时落盘排序，慢但不会 OOM。建议设为 max_memory_usage 的一半。
16.4.4 optimize_read_in_order：按主键序读
默认值：1（开）。
当查询的 ORDER BY 与表的 ORDER BY「兼容」（前缀一致）时，CH 可以跳过排序直接按 part 顺序读出。LIMIT 查询特别受益：
sql
-- 假设表 ORDER BY (user_id, event_time)
SELECT * FROM events
WHERE user_id = 1
ORDER BY event_time LIMIT 100;
-- 自动「按主键读」，几乎不需要排序EXPLAIN PIPELINE 里能看到 MergingSortedTransform 替代了 MergeSortingTransform。
16.4.5 merge_tree_min_rows_for_concurrent_read / merge_tree_min_bytes_for_concurrent_read
默认值：163840 行 / 240 MB。
这两个控制「只有 part 大到一定程度才值得多线程读」。小 part 用单线程反而更快（避免线程开销）。一般不用动；如果你看到 EXPLAIN PIPELINE 中 MergeTree 的并行度不够（明明 8 核但只开了 1 ~ 2 线程），可以适当调小这两个值。
16.4.6 max_insert_block_size / min_insert_block_size_rows / min_insert_block_size_bytes
控制 INSERT 时「攒块」的大小。INSERT ... SELECT 时尤其重要，决定了一次 SELECT 拉多少行才落一个 Part。
16.5 trace_log + 火焰图：CPU 热点定位
16.5.1 启用 trace_log
config.xml（或 config.d/trace.xml）里：
xml
<trace_log>
<database>system</database>
<table>trace_log</table>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</trace_log>然后在 SQL 层级开启采样：
sql
SET trace_profile_events = 1; -- 把 ProfileEvents 也写进 trace_log
SET query_profiler_real_time_period_ns = 10000000; -- 每 10 ms 采一次墙钟
SET query_profiler_cpu_time_period_ns = 10000000; -- 每 10 ms 采一次 CPU跑一条慢查询：
sql
SELECT count() FROM events_v1 WHERE has(tags, 'vip');16.5.2 抓栈 + 画火焰图
sql
-- 看这条查询的所有调用栈样本
SELECT
arrayStringConcat(arrayMap(x -> demangle(addressToSymbol(x)), trace), '\n') AS stack,
count() AS samples
FROM system.trace_log
WHERE query_id = '<上一条 SQL 的 query_id>'
GROUP BY stack
ORDER BY samples DESC
LIMIT 20;要画火焰图，推荐 clickhouse-flamegraph：
bash
clickhouse-flamegraph \
--query-id=<query_id> \
--output-dir=./flame \
--dsn=tcp://default@127.0.0.1:9000
# 浏览器打开 ./flame/<query_id>/cpu.svg火焰图横轴是「CPU 占用比例」，纵轴是「调用栈深度」。最宽的那一条最热点，很多时候你会发现热点是 Compression::LZ4::decompress（说明 SSD/CPU 不平衡）或者某个 hash 函数（说明 GROUP BY 设计不合理）。
📌 首次术语解释 · 火焰图（Flame Graph）：Brendan Gregg 发明的栈帧采样可视化方式。每一根「火焰」代表一个函数，宽度 = 它消耗的 CPU 时间比例，高度 = 调用栈深度。横向越宽 = 越值得优化。
16.6 案例对照实验：优化前 vs 优化后
下面这套实验完全可以跑（见 16_performance/code/perf_tune.py）。我们用一张「网页埋点 1 亿行」的表，跑同一个业务查询：「过去 30 天某 5 个国家用户的 PV / UV 排行」。
16.6.1 表结构与数据
sql
CREATE TABLE learn_ck.events_v1 (
event_date Date,
event_time DateTime,
user_id UInt64,
country String, -- ❌ 普通 String，1 亿行多次出现
event_type String, -- ❌ 普通 String
page_id UInt32,
duration_ms UInt32,
extra Nullable(String) -- ❌ 滥用 Nullable
)
ENGINE = MergeTree
ORDER BY (event_time, user_id); -- ❌ 没有按业务最常过滤的 user_id 排序
CREATE TABLE learn_ck.events_v2 (
event_date Date,
event_time DateTime,
user_id UInt64,
country LowCardinality(String), -- ✅ 低基数字典
event_type LowCardinality(String), -- ✅ 低基数字典
page_id UInt32,
duration_ms UInt32,
extra String DEFAULT '' -- ✅ 默认值替代 Nullable
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date) -- ✅ 月分区
ORDER BY (country, event_date, user_id); -- ✅ 把高频过滤列放主键前缀16.6.2 同一条业务 SQL
sql
SELECT country, count() AS pv, uniqExact(user_id) AS uv
FROM <events_v1 / v2>
WHERE event_date >= today() - 30
AND country IN ('CN','US','JP','DE','BR')
GROUP BY country
ORDER BY pv DESC;16.6.3 对比表（来自 system.query_log，可复现）
| 指标 | events_v1（优化前） | events_v2（优化后） | 改善 |
|---|---|---|---|
query_duration_ms | 18 432 ms | 1 067 ms | 17.3× |
read_rows | 100 000 000 | 8 642 304 | 11.6× 少读 |
read_bytes | 4.7 GiB | 184 MiB | 26.1× 少读 |
memory_usage | 1.93 GiB | 312 MiB | 6.3× 少占 |
ProfileEvents['SelectedMarks'] | 12 224 | 1 056 | 11.6× 少 mark |
ProfileEvents['SelectedParts'] | 7 | 2 | 分区裁剪生效 |
结论（也是本章核心信条）：ClickHouse 的性能 90% 在表设计：
- 对的
PARTITION BY（让 WHERE 能 prune）； - 对的
ORDER BY（让稀疏索引能跳）； - 对的类型（
LowCardinality/UInt*替代Nullable/String）。
剩下 10% 才是参数和重写 SQL 能救回来的。
16.7 调优排查 SOP（看图作业）
16.8 本章小结
┌──────────────────────────────────────────────────────────┐
│ 本章核心要点 │
├──────────────────────────────────────────────────────────┤
│ ① EXPLAIN 三件武器：PLAN / PIPELINE / ESTIMATE │
│ - PLAN: 逻辑算子树 │
│ - PIPELINE: 物理流水线 + 并行度 │
│ - ESTIMATE: 干跑估算 part/mark/row │
│ │
│ ② 必看视图 8 张： │
│ - query_log / processes / parts │
│ - merges / mutations │
│ - metrics / events / asynchronous_metrics │
│ - query_thread_log / text_log │
│ │
│ ③ 反模式 8 连： │
│ 单条小批写 / 大表 JOIN / SELECT * / │
│ WHERE 不带分区键 / 高基数 LowCardinality / │
│ Nullable 滥用 / FINAL 滥用 / 高频 Mutation │
│ │
│ ④ 关键参数 6 件： │
│ max_threads / max_memory_usage / │
│ max_bytes_before_external_{group_by,sort} / │
│ optimize_read_in_order / │
│ merge_tree_min_rows_for_concurrent_read │
│ │
│ ⑤ trace_log + 火焰图找 CPU 热点 │
│ │
│ ⑥ 性能 90% 在表设计：分区 / 排序 / 类型 │
└──────────────────────────────────────────────────────────┘16.9 面试高频题
Q1：EXPLAIN PLAN、EXPLAIN PIPELINE、EXPLAIN ESTIMATE 三者有什么区别，分别什么时候用？
考察点：是否真的会用 ClickHouse 的查询分析工具，而不是只会 SELECT count(*)。
标准答案：
EXPLAIN PLAN输出逻辑算子树（ReadFromMergeTree → Filter → Aggregating → Sorting → Limit），用来判断「优化器把我的 SQL 改写成了什么形状」、「JOIN 算子是不是在我期望的位置」。加indexes=1还能看到分区 / 主键 / 跳数索引的裁剪情况。EXPLAIN PIPELINE输出物理执行流水线（每个 Processor × 多少线程），用来判断并行度是否合理；如果关键算子是× 1，说明卡在单线程上。加graph=1可输出 DOT 图可视化。EXPLAIN ESTIMATE不真正执行，只估算要扫描多少 part / mark / row，几乎零成本。任何「我担心这条 SQL 要扫多少数据」的场景都先 ESTIMATE 一下。
加分项：能补一句「ClickHouse 没有 PG 那种 EXPLAIN ANALYZE 真实执行 + 打点的命令，归因要去 system.query_log 看 ProfileEvents，这是 OLAP 数据库的设计取舍 —— 一条 SQL 扫 TB 不能为了 ANALYZE 真跑一遍」。
易错点：把 EXPLAIN SYNTAX（输出重写后的 SQL）和 EXPLAIN PLAN 混淆。
Q2：怎么找出当前实例中最耗资源的 SQL？
考察点：实战经验。
标准答案：
- 实时正在跑的：
SELECT * FROM system.processes ORDER BY elapsed DESC，必要时KILL QUERY WHERE query_id=...。 - 历史已结束的：
SELECT query_id, query_duration_ms, read_bytes, memory_usage, query FROM system.query_log WHERE event_time > now() - INTERVAL 1 HOUR AND type='QueryFinish' ORDER BY <指标> DESC LIMIT 20。三个排序维度任挑：耗时最长 / 读最多字节 / 占内存最多。 - 进一步排查：用
query_id去system.query_thread_log看每个线程的细节；去system.trace_log看采样栈；去system.text_log看 ERROR 日志。 - 长期问题：用
normalizeQuery(query)把 SQL 模板化后聚合，找出「同一种 SQL 模板被调用最多 / 累计耗时最大」的，相当于 PG 的pg_stat_statements。
加分项：能写出 groupArray + normalizeQuery 做模板聚合的 SQL；能提到把 query_log 配置成 ReplicatedMergeTree + TTL，避免它本身把磁盘吃满。
易错点：忘了过滤 type='QueryFinish'，导致 ExceptionBeforeStart 之类的「还没跑」的行混进结果。
Q3：什么是「反模式 FINAL」？为什么慢？怎么替代？
考察点：对 ReplacingMergeTree 内部机制的理解。
标准答案：
- FINAL 的语义：在查询时临时把相关 part 合并并应用去重 / 折叠规则，保证看到的就是「合并后的最终结果」。
- 慢的原因：
- 单线程合并（除非开
do_not_merge_across_partitions_select_final等近期参数），不能多核加速； - 必须把所有相关 part 加载到内存做 K-way merge；
- 即便结果只要 1 行，也得读完所有候选行；
- 单线程合并（除非开
- 替代方案：
- 用
argMax(col, version)在查询时取最新版本（SELECT user_id, argMax(status, version) FROM t GROUP BY user_id）； - 用
OPTIMIZE TABLE ... FINAL DEDUPLICATE在维护窗口物理去重； - 改用
AggregatingMergeTree+MaterializedView让聚合在写入时完成。
- 用
加分项：能提 23.x 后引入的并行 FINAL（SELECT ... FINAL SETTINGS max_final_threads=8）；能解释为什么「数据已经被后台 merge 完了，FINAL 仍然必须扫描所有 part」 —— merge 是异步且无序的。
易错点：把 ReplacingMergeTree 的 version 列误以为是「版本管理工具」，实际只是后台 merge 时的「保留谁」依据，并不能保证查询时看到去重结果，所以才需要 FINAL 或 argMax。
Q4：LowCardinality 是怎么实现的？什么时候不能用？
考察点：对压缩字典原理的理解。
标准答案：
LowCardinality(T)在底层用 「字典 + ID」 编码：每个 part 内部维护一份T类型的字典数组，原列实际存的是字典下标（UInt8/16/32 自动选择）。- 优势：
- 大幅压缩高重复值（比如 100 个国家，原来要存 100M × 平均 6 字节 = 600MB，字典化后 100 × 6 + 100M × 1 字节 ≈ 100MB）；
- GROUP BY、IN 比较 ID 而非字符串，速度快几倍；
- 与 SIMD 向量化兼容。
- 不适用场景：
- 基数非常高（> 10 万 ~ 100 万）：字典本身变成累赘，压缩比反而下降；
- 几乎全是唯一值（user_id、订单号、UUID）：字典等于一一映射，纯亏；
- 需要做 LIKE / 子串匹配的全文搜索列：用
String+ bloom filter 跳数索引更合适。
- 经验阈值：基数
< 1 万一定值得；1 万 ~ 10 万看情况；> 10 万一般不用。
加分项：能补一句「LowCardinality 字典是 part 局部的，因此它和 ReplacingMergeTree、Distributed 表有一些边界配合的坑」；能提 low_cardinality_max_dictionary_size。
易错点：以为 LowCardinality 是表级全局字典，实际是 part 级。
Q5：你是怎么排查一条「只是慢，没有报错」的查询的？
考察点：综合排查能力。
标准答案（按 SOP 顺序回答）：
- 拿 query_id：从
system.query_log找到这条慢查询。 - 看 ProfileEvents：
SelectedParts、SelectedMarks大 → 分区/主键/跳数索引没生效，先EXPLAIN PLAN indexes=1验证；OSCPUVirtualTimeMicroseconds占大头 → CPU 瓶颈，开 trace_log + 火焰图；NetworkSendBytes大 → 是分布式查询的 fan-in 瓶颈；
- 看 memory_usage：
- 接近
max_memory_usage→ GROUP BY / JOIN 状态过大，启用max_bytes_before_external_group_by或字典化右表；
- 接近
- EXPLAIN PIPELINE：看是否卡在某个
× 1的算子（典型是排序、Final、单分片 limit）； - trace_log + flamegraph：进入 CPU 微观层面，找到具体的热点函数（解压、hash、aggregation function）；
- 改表 / 改 SQL / 改参数：按热点对应回前面 8 个反模式或 6 个关键参数。
加分项：能提到「灰度上线优化前应在低峰期对比 query_duration_ms 与 memory_usage 的 P99，而不是 avg」。
易错点：直接上调 max_memory_usage 把症状压下去，治标不治本。
Q6：max_threads = 32 和 max_threads = 4 哪个性能好？
考察点：对并发与并行的权衡理解。
标准答案：「取决于场景，不能一概而论」。
- 单条 SQL、低并发：
max_threads = 32更快，因为单查询能完整吃满 CPU。 - 高并发 web 查询：
max_threads = 4（甚至更小）反而吞吐更高 —— 32 个查询 × 32 线程 = 1024 线程在 32 核 CPU 上抢占，频繁上下文切换，整体劣化。 - 实操做法：
- 在线服务的 user profile 里把
max_threads限制小（如 4 ~ 8）； - 后台批处理 / 报表任务用单独 user profile，
max_threads调大； - 配合
max_concurrent_queries、max_concurrent_queries_for_user限制并发。
- 在线服务的 user profile 里把
加分项：能引入「Little's Law」 —— 系统吞吐 = 并发度 × 单查询效率，二者通常此消彼长。
易错点：把 max_threads 当成「调大就更快」的银弹，结果上线后 P99 反而更差。
Q7：system.parts 中 active=1 和 active=0 的 part 有什么区别？
考察点：MergeTree 后台合并机制。
标准答案：
active = 1：当前对查询可见的 part，所有 SELECT 实际读的就是这些。active = 0：已经被合并到更大 part 中的「旧 part」，仍然在磁盘上，不会再被查询使用，等待old_parts_lifetime（默认 480 秒）后由 ClickHouse 自动物理删除。- 设计原因：
- 崩溃安全：合并完成 → 切活跃指针 → 删旧 part，三步原子化，中间崩溃不丢数据；
- 正在跑的查询：旧 part 即便被合并掉，正在读它的 SELECT 仍然能完成；
- 快速回滚：极端情况下（merge 出 bug）可以手动把旧 part 重新激活。
加分项：能提 force_remove_data_recursively_on_drop = 0 时直接 DROP TABLE 不会立刻删盘，而是 rename 到 detached/，给「误删恢复」留窗口。
易错点：用 count() FROM system.parts 评估表大小，没加 active=1 导致重复计算。
Q8：怎么对比「优化前 / 优化后」效果？
考察点：科学方法论。
标准答案：
- 同台机器、同份数据、同一条 SQL 是前提，单变量对比；
- 关键指标：
query_duration_ms（端到端）read_rows/read_bytes（IO 量）memory_usage（内存峰值）ProfileEvents中的SelectedMarks/SelectedParts（索引命中）
- 跑法：
- 优化前后各跑 5 次取中位数（避开 cold cache 第一次的 outlier）；
- 用
SYSTEM DROP MARK CACHE; SYSTEM DROP UNCOMPRESSED CACHE;在两组实验之间清缓存，保证起点一致； - 把数据写成对比表（不要只贴一个绝对数字，要贴比值）。
- 报告时要附：
- 表结构（DDL）
- 数据规模（行数、压缩前后大小）
- 机器规格（CPU 核数、内存、磁盘类型）
- SQL 与
Settings
加分项：能提「灰度环境跑、生产环境观察 P99 而非 avg」。
易错点：只对比 query_duration_ms 不看 read_bytes，结果是命中了 page cache 的假象。
📌 下一章预告：第 17 章我们从「怎么把 SQL 调快」转到「怎么把整个集群运维好」 —— config.xml / users.xml 的层叠规则、SYSTEM 命令族、备份恢复、Prometheus 监控、副本卡住的真实排查案例。
🎬 可视化演示
演示加载缓慢或样式异常？点此在新标签页打开 ↗
💻 示例代码
python
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
第 16 章 · 性能调优对照实验脚本
================================
干什么:
1) 在 events_v1 (反模式) 和 events_v2 (正确做法) 上各跑 N 次同一条业务 SQL.
2) 从 system.query_log 取出 query_duration_ms / read_rows / read_bytes /
memory_usage / SelectedParts / SelectedMarks 等指标.
3) 分别取中位数, 输出 Markdown 格式的对照表.
前置:
python -m pip install clickhouse-connect
clickhouse-client < ../init.sql # 建表 + 灌 1000 万行
运行:
python perf_tune.py
python perf_tune.py --rounds 7 --days 30
"""
from __future__ import annotations
import argparse
import statistics
import time
import uuid
from typing import Dict, List
import clickhouse_connect
HOST = "127.0.0.1"
PORT = 8123
DB = "learn_ck"
USER = "default"
PASSWORD = ""
BUSINESS_SQL = """
SELECT country, count() AS pv, uniqExact(user_id) AS uv
FROM {table}
WHERE event_date >= today() - {days}
AND country IN ('CN','US','JP','DE','BR')
GROUP BY country
ORDER BY pv DESC
"""
def get_client():
return clickhouse_connect.get_client(
host=HOST, port=PORT, username=USER,
password=PASSWORD, database=DB,
)
def drop_caches(client) -> None:
"""每次实验前清缓存, 避免 page cache 让结果失真."""
for sysq in ("SYSTEM DROP MARK CACHE",
"SYSTEM DROP UNCOMPRESSED CACHE"):
try:
client.command(sysq)
except Exception as e: # 普通 default 用户可能没权限, 不致命
print(f" [warn] {sysq}: {e}")
def run_one(client, table: str, days: int) -> str:
"""跑一次业务 SQL, 返回 query_id 以便事后从 query_log 拿指标."""
qid = f"perf-{table}-{uuid.uuid4().hex[:10]}"
sql = BUSINESS_SQL.format(table=table, days=days)
client.query(sql, settings={"query_id": qid})
return qid
def fetch_metrics(client, qids: List[str]) -> List[Dict]:
"""从 system.query_log 把这一批 query_id 的指标拉回来."""
client.command("SYSTEM FLUSH LOGS")
placeholder = ",".join(f"'{q}'" for q in qids)
rows = client.query(f"""
SELECT
query_id,
query_duration_ms,
read_rows,
read_bytes,
memory_usage,
ProfileEvents['SelectedParts'] AS sel_parts,
ProfileEvents['SelectedMarks'] AS sel_marks
FROM system.query_log
WHERE query_id IN ({placeholder}) AND type = 'QueryFinish'
""").result_rows
cols = ["query_id", "duration_ms", "read_rows", "read_bytes",
"memory_usage", "sel_parts", "sel_marks"]
return [dict(zip(cols, r)) for r in rows]
def median_of(metrics: List[Dict], key: str) -> float:
return statistics.median([float(m[key]) for m in metrics])
def fmt_bytes(n: float) -> str:
units = [("B", 1), ("KiB", 1024), ("MiB", 1024 ** 2), ("GiB", 1024 ** 3)]
for u, base in reversed(units):
if n >= base or u == "B":
return f"{n / base:.1f} {u}"
return f"{n} B"
def fmt_int(n: float) -> str:
return f"{int(n):,}"
def benchmark(client, table: str, rounds: int, days: int) -> List[Dict]:
print(f"\n>>> 跑表 {table} 共 {rounds} 轮, days={days}")
qids: List[str] = []
for i in range(rounds):
drop_caches(client)
t0 = time.perf_counter()
qid = run_one(client, table, days)
dt = (time.perf_counter() - t0) * 1000
print(f" 第 {i + 1}/{rounds} 轮 client_wall={dt:7.0f} ms qid={qid}")
qids.append(qid)
time.sleep(1.5) # 让 query_log flush
return fetch_metrics(client, qids)
def render_report(v1: List[Dict], v2: List[Dict]) -> str:
keys = ["duration_ms", "read_rows", "read_bytes",
"memory_usage", "sel_parts", "sel_marks"]
md = []
md.append("\n=========== 对照报告 (各取中位数) ===========\n")
md.append("| 指标 | events_v1 (反模式) | events_v2 (正确做法) | 改善倍数 |")
md.append("|------|--------------------|----------------------|---------|")
for k in keys:
m1, m2 = median_of(v1, k), median_of(v2, k)
imp = (m1 / m2) if m2 > 0 else float("inf")
if k == "duration_ms":
disp1, disp2 = f"{m1:.0f} ms", f"{m2:.0f} ms"
elif k in ("read_bytes", "memory_usage"):
disp1, disp2 = fmt_bytes(m1), fmt_bytes(m2)
else:
disp1, disp2 = fmt_int(m1), fmt_int(m2)
md.append(f"| `{k}` | {disp1} | {disp2} | **{imp:.1f}×** |")
md.append("")
md.append("解读:")
md.append(" - sel_parts / sel_marks 急剧下降 → 分区裁剪 + 主键裁剪生效.")
md.append(" - read_bytes 大幅下降 → LowCardinality 字典编码 + 列读减少.")
md.append(" - memory_usage 下降 → 字典化的 GROUP BY 内存更省.")
md.append(" - duration_ms 下降 → 上述三件叠加的最终结果.")
return "\n".join(md)
def main() -> None:
ap = argparse.ArgumentParser()
ap.add_argument("--rounds", type=int, default=5,
help="每张表跑几轮取中位数, 默认 5")
ap.add_argument("--days", type=int, default=30,
help="WHERE event_date >= today()-days, 默认 30")
args = ap.parse_args()
client = get_client()
print("== 自检表是否就绪 ==")
rows = client.query(f"""
SELECT table, sum(rows) AS rows, count() AS parts
FROM system.parts
WHERE database = '{DB}'
AND table IN ('events_v1', 'events_v2')
AND active
GROUP BY table
ORDER BY table
""").result_rows
if not rows:
print("[ERROR] 找不到 events_v1 / events_v2, 请先执行 init.sql.")
return
for t, r, p in rows:
print(f" {t}: rows={r:,} active_parts={p}")
v1 = benchmark(client, "events_v1", args.rounds, args.days)
v2 = benchmark(client, "events_v2", args.rounds, args.days)
if not v1 or not v2:
print("[ERROR] 没拉到 query_log 指标, 请检查 system.query_log 是否启用.")
return
print(render_report(v1, v2))
if __name__ == "__main__":
main()