ClickHouseNotes

第 25 章：查询优化

zjc 于 2026-01-25 发布

这是《ClickHouse 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 查询优化的第一步是量化成本，而不是盲目加索引。核心指标是扫描行数、扫描字节、内存、耗时和并发影响。

25.1 优化流程

定位慢 SQL
  -> 查看扫描量和内存
  -> 检查分区剪枝
  -> 检查排序键匹配
  -> 减少列和行
  -> 改聚合或预计算
  -> 压测并发
  -> 固化查询规范

25.2 找慢查询

SELECT
    query_id,
    query,
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date >= today() - 1
ORDER BY query_duration_ms DESC
LIMIT 30;

按总成本统计：

SELECT
    normalized_query_hash,
    count() AS executions,
    sum(read_bytes) AS total_bytes,
    avg(query_duration_ms) AS avg_ms,
    any(query) AS sample_query
FROM system.query_log
WHERE type = 'QueryFinish'
GROUP BY normalized_query_hash
ORDER BY total_bytes DESC
LIMIT 20;

25.3 分区剪枝

较差：

SELECT count()
FROM analytics.events_local
WHERE user_id = 1001;

较好：

SELECT count()
FROM analytics.events_local
WHERE event_date = today()
  AND user_id = 1001;

分区表达式要与写入字段一致。对于：

PARTITION BY toYYYYMM(event_date)

查询建议使用：

WHERE event_date >= '2026-08-01'
  AND event_date < '2026-09-01';

而不是只依赖 toYYYYMM(event_date) = 202608 这类表达式。不同版本优化能力有差异，直接使用列范围更稳妥。

25.4 减少读取列

避免：

SELECT * FROM analytics.events_local;

推荐：

SELECT event_date, city_id, count()
FROM analytics.events_local
WHERE event_date = today()
GROUP BY event_date, city_id;

列存下 SELECT * 会同时放大 IO、网络和内存成本。

25.5 过滤与聚合

先过滤再聚合：

SELECT city_id, count(), sum(amount)
FROM analytics.events_local
WHERE event_date = today()
  AND event_type = 'pay'
GROUP BY city_id;

虽然优化器会做许多下推，但 SQL 保持清晰的过滤条件有利于稳定执行，也便于后续治理。

25.6 聚合优化

高成本：

SELECT uniqExact(user_id)
FROM analytics.events_local;

可评估：

SELECT uniq(user_id)
FROM analytics.events_local;

降基方式：

手段 说明
缩小时间 分区过滤
低基数类型 UInt32 替代 String
近似函数 uniq、quantile
预聚合 物化视图
聚合状态 uniqState
分桶结果 先按用户桶汇总

计费、审计、财务类场景要明确是否允许近似。

25.7 JOIN 优化

大表 JOIN 小表：

SELECT count()
FROM big_table AS b
INNER JOIN small_dim AS d ON b.key = d.key;

优化方向：

  1. 小表使用 Dictionary；
  2. 提前过滤大表；
  3. 只 SELECT 需要列；
  4. 冗余稳定维表字段；
  5. 预计算宽表；
  6. 控制右表大小；
  7. 验证 JOIN 算法设置。

查看计划：

EXPLAIN
SELECT count()
FROM big_table AS b
INNER JOIN small_dim AS d ON b.key = d.key;

25.8 LIMIT 与排序

深分页会放大成本：

LIMIT 100000, 20;

可改为键集分页：

SELECT event_id, event_time
FROM analytics.events_local
WHERE event_date = today()
  AND (event_time, event_id) > ('2026-08-25 10:00:00', 'E999')
ORDER BY event_time, event_id
LIMIT 20;

TopN 也要控制返回规模，避免超大中间结果传输。

25.9 资源限制

ALTER USER report_user
SETTINGS
    max_execution_time = 30,
    max_memory_usage = 4000000000,
    max_rows_to_read = 10000000000;

报表服务应设置查询超时、最大扫描量、最大内存、并发队列和只读权限。查询限流不是刁难用户，而是保住高峰期的确定性。

25.10 常见问题定位

现象 常见原因
CPU 高 大扫描、解压、高基数聚合
内存高 分组多、JOIN、窗口
IO 高 读取列过多、分区未剪枝
网络高 分布式结果集过大
抖动 并发查询、后台合并、副本同步
偶发超时 磁盘争抢、队列等待

本章小结

查询优化围绕扫描量展开：先分区剪枝，再匹配排序键，再减少列和行，最后考虑预聚合、投影、字典和资源限制。用 query_log 建立慢查询台账，避免凭感觉调参。

思考题

  1. 为什么 read_bytes 比单次耗时更能反映查询成本？
  2. 如何验证分区剪枝？
  3. uniqExact 有哪些替代方案？
  4. 深分页如何优化？
  5. 如何防止报表用户拖垮集群？