优化的核心思路
ClickHouse 擅长扫描和聚合海量列式数据。优化重点不是让每条语句都“走索引”，而是尽量少读分区、少读数据块、少读列，并减少高基数聚合和不必要的数据交换。
建表阶段决定上限
PARTITION BY 用于数据生命周期管理和粗粒度裁剪，不应使用高基数字段制造海量小分区。ORDER BY 决定数据在磁盘上的排序方式，是查询跳过数据的关键，应优先放常用过滤条件，并兼顾基数和查询模式。
CREATE TABLE events
(
event_date Date,
tenant_id UInt32,
event_type LowCardinality(String),
user_id UInt64,
payload String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date, event_type);
分区键和排序键职责不同；排序键也不等同于传统数据库的唯一主键。
只读取需要的数据
- 避免
SELECT *，列式存储读取无关列仍会产生 IO 和解压成本。 - 尽早增加可裁剪的过滤条件。
- 时间范围使用与字段类型一致的表达式，避免阻碍裁剪。
- 大结果集不要直接返回客户端，先聚合或分页导出。
PREWHERE 可以先读取过滤列，命中后再读取其余列。ClickHouse 通常会自动优化，但理解它有助于分析实际读取量。
数据类型与编码
选择满足范围的最小数值类型；枚举值或重复字符串可以评估 LowCardinality；时间字段使用 Date 或 DateTime，不要全部保存为字符串。合理类型能够降低磁盘占用、内存和 CPU 解码成本。
聚合与物化
重复执行的固定口径聚合可以通过物化视图在写入阶段预计算，把查询成本转移到写入。它适合稳定指标，不适合维度频繁变化的临时分析。使用前需要规划历史数据回填、幂等以及源表变更。
定位慢查询
通过 system.query_log 关注 read_rows、read_bytes、执行时间和内存使用量。若返回几百行却读取数十亿行，优先检查分区与排序键是否匹配查询。
EXPLAIN indexes = 1 可以观察分区、主键等裁剪效果。优化后要比较实际读取行数和字节数，而不仅是单次耗时，因为缓存和并发负载会干扰耗时。
常见反模式
- 每条数据单独插入，产生大量小 Part；应批量写入。
- 把高基数 UUID 直接设为首个排序键，却很少按它过滤。
- 使用海量小分区，把分区当作二级索引。
- 在大表之间无约束 JOIN；应过滤、预聚合并选择合理 JOIN 策略。
- 依赖
FINAL修正所有查询，它可能带来显著额外开销。
核心考点清单
- 优化目标是少读分区、数据块和列，而不只是“走索引”。
- 分区键控制粗粒度裁剪与生命周期，排序键决定主要跳读能力。
- 批量写入、合理数据类型和预聚合能显著降低资源成本。
- 用 query_log 和
EXPLAIN indexes = 1验证读取行数，不能只看单次耗时。
高频追问与参考回答
追问：为什么分区不能越细越好？
过多分区会增加元数据、小 Part 和文件系统管理成本。分区首先服务数据生命周期管理，查询加速更多依赖排序键与跳数索引。
追问：什么时候使用物化视图？
适合口径稳定、频繁重复的聚合，把计算移到写入阶段；维度频繁变化的临时分析不适合，且需设计历史回填与幂等。
机制全景图
下面把「ClickHouse 查询优化实战指南」从输入到结果压缩成一条可复述的主链路。面试时先用图建立全局坐标，再进入局部实现，能避免只背零散结论。
flowchart LR
A["解析 SQL 与谓词"]
A --> B["分区/主键/跳数索引裁剪"]
B --> C["并行读取所需列"]
C --> D["执行聚合连接排序"]
D --> E["输出结果并记录 query_log"]
完整链路：从输入到结果
沿着「解析 SQL 与谓词 → 分区/主键/跳数索引裁剪 → 并行读取所需列 → 执行聚合连接排序 → 输出结果并记录 query_log」观察输入、状态与输出，下面每个阶段都对应一个可以在源码、日志或系统表中验证的位置。
1. 解析 SQL 与谓词
查询优化从读取量开始：PREWHERE 可先读过滤列，再读取命中行的其他列。
2. 分区/主键/跳数索引裁剪
分区和主键裁剪依赖条件能映射到表定义，函数包装和弱相关排序会扩大扫描。
3. 并行读取所需列
ClickHouse 按数据片段并行读列，线程过多会竞争 CPU、内存和磁盘，单查询快不等于并发稳定。
4. 执行聚合连接排序
GROUP BY、JOIN 和 ORDER BY 可能建立大哈希表或中间结果，应利用字典、预聚合和外部溢写控制。
5. 输出结果并记录 query_log
system.query_log 保存 read_rows、read_bytes、memory_usage 等证据，应以其验证优化而非只看耗时。
源码与实现定位
| 入口 | 阅读重点 |
|---|---|
| system.query_log | read_rows、memory_usage、ProfileEvents |
| EXPLAIN PIPELINE | 并行执行管线 |
源码或系统表应按上表顺序追踪：先确认入口实际走到哪条路径，再用运行时数据验证，而不是仅凭类名或配置推测。
参数配置与可复现实验
SELECT query_duration_ms,read_rows,memory_usage FROM system.query_log WHERE type='QueryFinish' ORDER BY event_time DESC LIMIT 20;
固定数据快照对比 PREWHERE、投影和预聚合，混合并发下验证。
验证步骤与预期结果
1. 固定输入和基线
先在没有故障注入的环境执行上述配置，固定数据规模、并发度、运行时版本和预热时间。以「read_rows/result_rows」为主基线，记录值应满足「记录稳态基线」；同时保存 read_rows/read_bytes、result_rows 比例，使后续变化能够回到同一时间轴比较。
2. 从实现入口确认路径
在「system.query_log」确认请求确实进入「read_rows、memory_usage、ProfileEvents」对应的实现，再沿「EXPLAIN PIPELINE」观察「并行执行管线」。如果入口路径都未命中，就不应继续调整下游参数，而应先检查调用条件、版本或路由是否与假设一致。
3. 注入本文特有的失败模式
优先复现「只调 max_threads 掩盖扫描过大」，并把单一变量逐级放大，直到「read_rows/result_rows」越过「>1000」。随后再分别验证「SELECT * 读取无关列」和「大 JOIN 全部放入内存」，三类故障分开执行，避免多个变量同时变化而无法归因。
4. 执行止损和根因修复
第一轮只应用「先减少扫描列和行」，确认它能控制影响范围；第二轮应用「限制高基数聚合」，验证核心链路恢复；最后落实「按并发而非单查询调线程」，消除同类问题再次出现的条件。每一步都保留变更前后数据，不用“感觉变快了”替代测量。
5. 通过退出条件
实验只有同时满足三项才算通过：「read_rows/result_rows」回到「记录稳态基线」、「read_rows/result_rows P99」回到「同负载可复现」、「结果核对」回到「差异为 0」，并且业务结果差异为零。若性能恢复但结果不一致，仍应视为失败；若指标恢复后很快再次越线，则说明只完成了临时止损，没有消除根因。
量化基线
| 指标 | 样例基线/口径 | 风险线 | 结论 |
|---|---|---|---|
| read_rows/result_rows | 记录稳态基线 | >1000 | 读放大 |
| read_rows/result_rows P99 | 同负载可复现 | 超过基线 2 倍 | 读放大 |
| 结果核对 | 差异为 0 | 任意非零 | 停止切换并修复 |
这些数值是实验口径或示例告警线，不是可复制到所有系统的固定答案；上线阈值应由本系统稳态、峰值和故障演练共同确定。
事故复盘：报表只返回百行却扫描数十亿行
WHERE 对 event_time 使用 toDate 函数且表按原时间排序，无法形成有效范围。改写为半开时间区间，并让租户列位于 ORDER BY 前缀后，读取行数下降几个数量级。
| 失败模式 | 首要证据 | 第一处置动作 |
|---|---|---|
| 只调 max_threads 掩盖扫描过大 | read_rows/read_bytes | 先减少扫描列和行 |
| SELECT * 读取无关列 | result_rows 比例 | 限制高基数聚合 |
| 大 JOIN 全部放入内存 | peak_memory_usage | 按并发而非单查询调线程 |
发布与回滚检查点
- 发布前：确认「system.query_log」对应实现和上述配置在目标版本仍然有效，并保存「read_rows/result_rows」基线。
- 灰度中：同时观察 read_rows/read_bytes、result_rows 比例、peak_memory_usage；任一指标越过表中风险线，就停止继续扩量。
- 回滚时：先执行「先减少扫描列和行」控制影响，再回退代码或参数；涉及持久状态时必须额外核对结果差异。
- 发布后：至少覆盖一个完整峰值周期，确认「只调 max_threads 掩盖扫描过大」没有再次出现，才关闭变更观察窗口。
方案对比与选型
| 方案 | 更适合的场景 | 主要收益 | 代价与边界 |
|---|---|---|---|
| 改写查询利用排序键 | 过滤条件与主键相关 | 无需额外存储、收益直接 | 受现有表布局限制 |
| 物化视图预聚合 | 固定聚合高频重复 | 查询读取极少 | 写放大与回填一致性 |
| 新建投影/宽表 | 多种稳定访问路径 | 优化器可选择更合适布局 | 存储与维护成本增加 |
选型至少带上 日增量、分区规模、查询并发、扫描行数和压缩比，并用上面的量化基线验证；未知数据应明确为待测假设。
设计边界与工程取舍
ClickHouse 优化的第一原则是少读数据，其次才是增加线程；扫描量不降时，并行只会更快耗尽共享资源。
工程落地遵循：以数据布局减少扫描，以批量写入减少小 Part，避免照搬行存思路。回答时直接引用「system.query_log」、配置实验和事故数据，比复述固定模板更有说服力。