ClickHouse学习笔记（五）：查询优化与常见问题总结

学完建表、索引和物化视图后，再遇到“这条 SQL 为什么慢”，应该从哪里下手？

直接加索引、加线程或换更大的机器，都需要先弄清楚查询究竟在花什么成本。读取太多记录、保留过大的聚合状态和排序太多结果，对应的处理方式不同。

这一篇先练习一个可以重复观察的排查过程，再把前四篇的知识整理成常见问题。

系列导航：

本篇沿用 ClickHouse 25.8 的单节点学习环境，性能练习另外创建合成数据表。文中的数据规模和 SQL 是实验设计，没有给出未经实际测量的耗时或加速倍数。

一、先把“慢”拆成几个可观察的问题

观察到什么	可能的成本	下一步关注什么
`read_rows` 很大	读取了大量候选记录	过滤条件、分区、排序键与索引
`read_bytes` 很大	读取了较多列或较宽字段	列裁剪、过滤时机与字段类型
内存较高	高基数聚合、JOIN 或排序	维度数量、状态大小、连接输入
很多小 part	查询需要处理很多片段	插入批次、分区数量与合并压力
服务端执行快，页面仍慢	网络、结果传输或客户端处理	返回行数、字段宽度与接口链路

这些都是排查线索，不是单个指标就能得出的结论。

先保留原始 SQL、输入范围、查询结果和数据规模，再进行修改。 修改前后不仅要比较速度，也要确认统计口径没有被改坏。

二、准备百万行合成数据

第二篇的八条记录足够学习 SQL，但不足以观察索引剪枝。这里创建独立实验表，保留相同的列和排序设计：

CREATE TABLE clickhouse_learning.order_payments_bench
AS clickhouse_learning.order_payments;

INSERT INTO clickhouse_learning.order_payments_bench
    (order_id, user_id, city, channel, amount, paid_at)
SELECT
    number + 1 AS order_id,
    number % 100000 AS user_id,
    arrayElement(['上海', '北京', '广州'], (number % 3) + 1) AS city,
    if(number % 2 = 0, 'app', 'web') AS channel,
    toDecimal64((number % 500) + 1, 2) AS amount,
    toDateTime('2026-09-01 00:00:00', 'Asia/Shanghai')
        + toIntervalSecond(number * 3) AS paid_at
FROM numbers(1000000);

SELECT
    count() AS rows,
    min(paid_at) AS start_time,
    max(paid_at) AS end_time
FROM clickhouse_learning.order_payments_bench;

CREATE TABLE ... AS ... 复制第二篇表的结构与引擎，不复制其八条记录。输入由 numbers() 生成，不需要下载外部数据，见官方 numbers 表函数。

预期为 100 万行，每三秒生成一条，跨越 9 月和 10 月。这里的规律分布只是为了便于实验，不能代表真实订单中的城市倾斜、热门用户或突发流量。

建表和插入执行一次即可。重复实验时使用新的表名，避免把累计的数据误认为相同规模。

三、从计划、扫描量和内存判断

3.1 先用 EXPLAIN 看候选范围

查询 10 月 1 日上海的支付金额：

EXPLAIN indexes = 1
SELECT sum(amount)
FROM clickhouse_learning.order_payments_bench
WHERE city = '上海'
  AND paid_at >= toDateTime('2026-10-01 00:00:00', 'Asia/Shanghai')
  AND paid_at <  toDateTime('2026-10-02 00:00:00', 'Asia/Shanghai');

观察分区与主键筛选后的 parts、granules。然后对比一条仅按时间过滤的查询：

EXPLAIN indexes = 1
SELECT sum(amount)
FROM clickhouse_learning.order_payments_bench
WHERE paid_at >= toDateTime('2026-10-01 00:00:00', 'Asia/Shanghai')
  AND paid_at <  toDateTime('2026-10-02 00:00:00', 'Asia/Shanghai');

两条查询的业务范围不同，因此不能把耗时差异当作同一业务的优化成绩；它们适合用来观察“有没有城市条件”如何影响候选范围。

排序键以城市开头，只有时间条件时的剪枝效果需要实际看计划，不能机械套用“没有第一列，后面的条件就完全不能用索引”。EXPLAIN和主索引指南可以帮助理解这种范围分析。

3.2 为实际查询加上可识别的 ID

回到宿主机终端，执行下面这条单行命令：

docker exec clickhouse-learning clickhouse-client --user learner --password LearningOnly_123 --query_id ch-learning-city-day-01 --query "SELECT sum(amount) FROM clickhouse_learning.order_payments_bench WHERE city = '上海' AND paid_at >= toDateTime('2026-10-01 00:00:00', 'Asia/Shanghai') AND paid_at < toDateTime('2026-10-02 00:00:00', 'Asia/Shanghai') SETTINGS log_queries = 1, use_query_cache = 0"

在交互 SQL 客户端中刷新并查看日志：

SYSTEM FLUSH LOGS;

SELECT
    event_time,
    query_id,
    query_duration_ms,
    read_rows,
    formatReadableSize(read_bytes) AS read_size,
    formatReadableSize(memory_usage) AS memory_size,
    result_rows
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_id = 'ch-learning-city-day-01'
ORDER BY event_time DESC
LIMIT 1;

QueryFinish 用于查看成功完成的查询，避免只读到开始事件。字段含义见system.query_log 文档。

本地初始化用户用于这个实验；如果用户没有执行 SYSTEM FLUSH LOGS 的权限，可以等待日志正常刷新。日志也需要服务端启用相应配置。

每次对比使用新的 ID，例如 ch-learning-city-day-02。最好执行多轮，比较稳定的扫描量和耗时分布。这里关闭的是查询结果缓存，操作系统文件缓存、解压缓存和其他缓存仍可能影响重复查询，不能将它当作严格冷启动测试。

四、几种优化手段分别解决什么？

4.1 只读取需要的列

如果页面只展示城市和支付金额，就不必先取出全部支付明细，再让应用汇总。

SELECT city, sum(amount) AS total_amount
FROM clickhouse_learning.order_payments_bench
WHERE paid_at >= toDateTime('2026-10-01 00:00:00', 'Asia/Shanghai')
  AND paid_at <  toDateTime('2026-10-02 00:00:00', 'Asia/Shanghai')
GROUP BY city
ORDER BY total_amount DESC;

数据库聚合通常能把大量明细缩减为较少结果行，也减少传输与应用处理。但明细导出本来就需要每条记录时，不能只为了减少返回量而改变业务需求。

4.2 PREWHERE：先过滤，再读其他列

SELECT sum(amount)
FROM clickhouse_learning.order_payments_bench
PREWHERE city = '上海'
WHERE paid_at >= toDateTime('2026-10-01 00:00:00', 'Asia/Shanghai')
  AND paid_at <  toDateTime('2026-10-02 00:00:00', 'Asia/Shanghai');

PREWHERE 可以先读取用于过滤的列，根据筛选结果减少其他列的读取。它不是一种新索引，也不会让所有过滤都变成逐行随机访问。

ClickHouse 可以自动把合适的条件从 WHERE 移到 PREWHERE，所以手工写法不一定更快。对于 FINAL 等场景，还需要关注过滤与引擎去重的执行顺序，见官方 PREWHERE 文档。

4.3 排序键：优化最重要的访问路径

如果业务主要按城市和时间查询，当前设计有明确理由。如果业务改成主要按用户查询，就要重新评估：

1
2
3

城市分析：考虑 city、paid_at 等组合
用户轨迹：考虑 user_id、paid_at 等组合
全局时间监控：考虑 paid_at 等组合

这是设计方向，不是直接执行的配置。一个排序键未必能同时服务所有负载，可以评估额外表或 projection，但额外的存储和写入成本也要纳入比较。官方排序键指南讨论了查询模式与字段顺序。

4.4 数据跳过索引：看数据在块内的分布

跳过索引记录数据块的摘要，帮助排除不可能匹配的块。例如，某类 minmax 索引使用范围信息，Bloom filter 可以用于判断某个值是否肯定不存在。

如果一个 channel 列在几乎每个块里都同时包含 app 和 web，按渠道筛选时就很难排除块。字段不同值少，不代表建立跳过索引就一定有效。

跳过索引的效果取决于块内分布与过滤条件。 应在代表性数据上比较剪枝收益、索引读取和写入成本，见官方跳过索引指南。

4.5 预聚合：减少重复计算

第四篇已经演示按天、城市汇总。它适合高频、稳定的指标，但必须保留正确的聚合状态与统计维度。

比如“每天支付人数”不能直接相加为“每月支付人数”。同一个人可能在多天出现，需要合并去重状态或重新读取对应明细。

五、查询优化也要看写入方式

MergeTree 持续收到非常小的插入批次时，容易产生大量小 part。即使查询 SQL 没变，碎片和合并压力也可能让查询变慢。

5.1 先考虑客户端批量写入

每条记录发一个 INSERT
          ↓
更多请求与更多小批次

先缓冲一定数量或一定时间的记录
          ↓
批量 INSERT

批次越大，新鲜度和失败重试粒度也会变化。不能无限等待凑齐大批次，需要同时限制记录数、字节数和最大等待时间。官方批量写入建议说明了 part 数量与批次的关系。

5.2 异步写入不等于不检查结果

服务端也可以缓冲小批请求，再进行写入。下面只是配置形式，使用时将 YourTable 和记录替换为独立实验对象：

1
2
3

INSERT INTO YourTable
SETTINGS async_insert = 1, wait_for_async_insert = 1
VALUES (...);

wait_for_async_insert = 1 让请求等待缓冲数据真正刷入后再确认。若设为 0，确认发生在进入缓冲后，后续失败不一定能通过原请求返回，可靠性处理更复杂。官方异步写入文档解释了确认时机。

异步缓冲也不是业务事件去重。源数据重复，仍然需要正确的幂等设计。

六、常见问题：先给回答，再说边界

1. ClickHouse 为什么适合分析？

**简短回答：**列式存储减少无关列读取，压缩降低数据量，批量执行和并行处理提高扫描与聚合效率，合适的排序和索引还能减少候选范围。

**追问要点：**实际性能取决于负载和建模，不能给出脱离数据规模、硬件和 SQL 的固定倍数。

2. 能直接替代业务数据库吗？

**简短回答：**先看业务是否依赖点查、频繁小更新、约束和多操作事务。订单状态与报表分析通常有不同要求，需要分别评估。

**追问要点：**不要用“完全不能更新”概括 ClickHouse。它有多种更新和删除路径，但语义、版本支持与成本需要按实际部署核对。官方更新说明列出了相关机制。

3. ClickHouse 的主键保证唯一吗？

**简短回答：**普通 MergeTree 的主键主要用于稀疏索引，不提供业务唯一约束。

**追问要点：**订单重复是否被阻止，要由摄入过程和表模型明确保证，不能只看 PRIMARY KEY。

4. Part 和 Partition 有什么区别？

**简短回答：**Partition 是根据分区表达式划分的数据范围，part 是存储片段。一个分区可以有多个 part。

**追问要点：**后台合并发生在分区内部，过细分区会限制合并并增加管理开销。

5. ORDER BY 和 PRIMARY KEY 有什么关系？

**简短回答：**前者决定 part 内的数据排序，后者决定主键索引记录的键；不单独指定主键时，默认使用排序键。

**追问要点：**单独指定的主键要是排序键前缀，查询结果的输出顺序仍需在 SELECT 中明确指定。

6. 为什么每个 Granule 不一定都是 8192 行？

**简短回答：**粒度还受字节大小等设置影响，实际行数并不固定。

**追问要点：**稀疏索引会定位候选范围，再读取和过滤其中的记录，不是按主键定位唯一一行。

7. ReplacingMergeTree 能保证立刻查不到旧版本吗？

**简短回答：**不能依赖后台合并立即去重，需要在查询语义中处理版本，例如适当地使用 FINAL。

**追问要点：**重复身份由整个排序键定义，分区和版本字段也必须稳定且符合业务规则。

8. SELECT FINAL 和 OPTIMIZE FINAL 是同一件事吗？

**简短回答：**前者在查询中应用引擎合并规则，后者发起存储层面的强制整理，两者的作用和成本不同。

**追问要点：**不要把反复执行 OPTIMIZE ... FINAL 当作保证业务正确性的日常方式。它可能带来大量读写和资源竞争，见官方 OPTIMIZE 建议。

9. 增量物化视图会重新扫描整个源表吗？

**简短回答：**本系列的增量视图由新插入的数据块触发，通常只处理这个块，再将结果写入目标表。

**追问要点：**创建前的历史数据需要回填，右侧 JOIN 表的变化不会独立触发历史重算。

10. 为什么汇总表还需要 GROUP BY 和 Merge 函数？

**简短回答：**同一维度可能仍有多个状态，查询要正确合并它们，不能假定后台已经整理成一行。

**追问要点：**物理行数、支付笔数和去重人数是不同的指标。

11. uniqExact 和近似去重怎么选？

**简短回答：**前者追求精确结果，但状态成本随不同值数量增长；近似算法可以降低部分成本，但存在算法对应的误差。

**追问要点：**先判断报表是否允许误差，再比较精度、内存和延迟，不要只比较函数执行时间。uniqExact 文档说明了状态成本。

12. PREWHERE 能代替索引吗？

**简短回答：**不能。它主要优化列的读取与过滤顺序，索引则用于排除数据范围，两者可以配合。

**追问要点：**优化器可能自动进行转换，手工改写需要测量，不能预设一定有收益。

13. TTL 到期后会立即删除数据吗？

**简短回答：**通常在后台合并过程中执行，不是每行到期瞬间的定时器。

**追问要点：**如果查询必须严格排除过期记录，需要对应的查询条件。TTL 的执行时机还受调度和资源影响，见官方 TTL 指南。

14. 分片和副本分别解决什么？

**简短回答：**分片把数据分散到多个节点扩大容量和并行能力，副本为同一份数据提供冗余与可用性。

**追问要点：**单节点实验没有这两层能力。传统自管理集群中，分布式查询与路由常用 Distributed 表，数据复制使用 ReplicatedMergeTree 家族，协调可使用 ClickHouse Keeper。不同部署形态不能照搬同一个架构图，见分布式引擎文档与复制说明。

七、三个故障场景练习

场景一：支付金额突然翻倍

先查摄入是否重复、回填是否再次执行，以及视图是否把多个状态事件当作多次支付。源表后来去重，并不意味着已写入汇总表的金额自动撤回。

练习回答：为什么“源表用了 ReplacingMergeTree”不足以证明汇总金额正确？

场景二：加了渠道索引，扫描量没有明显下降

先看渠道在每个数据块中的分布。几乎每块都包含 app 时，筛选 app 无法排除多少块，再看计划是否使用索引及其成本。

练习回答：为什么字段的全局基数很低，跳过索引仍可能没效果？

场景三：查询没改，写入越来越慢，Part 数也在涨

检查是不是每条事件都单独发送 INSERT、分区是否过细、合并是否跟不上，以及磁盘和 CPU 是否有压力。

练习回答：把查询线程数调大，为什么未必能解决摄入侧的小批次问题？

可以按下面的顺序整理自己的排查记录：

业务口径与结果
      ↓
数据规模与查询计划
      ↓
扫描量、执行耗时和内存
      ↓
写入批次、part 与资源状况
      ↓
选择修改 → 复测结果与成本

到这里，已经有了一条从建模到排查的学习路径。后续接入 Java、Kafka 或多节点集群时，仍可以沿着“数据代表什么、怎样写入、怎样读取、结果如何保证正确”这几个问题继续展开。