实践技巧

原文：ClickHouse 十大最佳实践技巧

ClickHouse 的性能不仅取决于引擎本身，也取决于 Schema、排序键、数据类型、摄取方式和查询模式是否匹配。很多数量级优化来自建模决策，而不是硬件升级。

1. 选择合适的主键和排序键

MergeTree 表里的 ORDER BY 会决定数据的物理排序方式，也决定主索引剪枝和压缩效果。

ClickHouse 写入数据时，会按 ORDER BY 排序，并记录每个 granule（默认 8192 行）的首个值。查询条件如果命中排序键，ClickHouse 就能跳过不可能包含目标数据的 granule。

排序键设计原则：

• 让 ORDER BY 匹配最常见的查询过滤条件。
• 优先放低基数列，例如 tenant_id、region、category。
• 时间列通常放在低基数过滤列之后。
• 避免以 UUID、精确时间戳等高基数字段开头。

例如经常按品类和日期查询时，ORDER BY (product_category, review_date) 会比 (marketplace, customer_id, review_date) 更容易减少扫描范围。原文基准中，同一查询在调整排序键后扫描量减少 347 倍，查询速度提升约 3 倍。

2. 使用高效数据类型

数据类型会直接影响存储大小、压缩率和查询速度。

建议：

• 使用能覆盖范围的最小数值类型，例如 UInt8、UInt32，不要默认使用 UInt64。
• 除非业务必须区分 NULL，否则避免 Nullable。
• 低基数字符串使用 LowCardinality(String)。
• 固定值集合优先考虑 Enum，不要长期使用自由文本。

Nullable 会额外存储一列 UInt8 作为空值标记，增加存储和执行开销。很多场景可以用默认值替代，例如空字符串、0 或约定的哨兵值。

LowCardinality(String) 使用字典编码，适合 distinct 值较少的字符串列。原文示例中，通过移除不必要的 Nullable、缩小数值类型并使用 LowCardinality(String)，存储从 30.16 GB 降到 26.8 GB，查询速度提升约 2 倍。

3. 谨慎分区，必要时不分区

ClickHouse 的分区主要是数据管理能力，不是通用查询加速手段。主索引剪枝已经很快，额外分区如果没有匹配查询条件，往往只会制造更多 part。

过度分区的风险：

• 大量小 part。
• 后台 merge 变慢。
• 内存占用上升。
• 查询调度和合并开销增加。

经验规则：如果分区数量超过几十个，通常要重新评估。

适合分区的场景：

• TTL 删除历史数据，按月或按年删除整块旧分区。
• 配合 ReplacingMergeTree、CollapsingMergeTree、AggregatingMergeTree 等依赖合并语义的表引擎，让历史分区更容易合并到较少 part。

不推荐为了查询性能随手 PARTITION BY，尤其不要按天或按 tenant_id 这类高基数字段过细分区。默认不分区，或按月、按年粗粒度分区，通常更稳。

4. 使用跳过索引优化扫描

主索引只基于 ORDER BY 字段。如果查询经常按其他列过滤，可以使用 Skipping Indexes 扩展 granule 级别的剪枝能力。

轻量级索引：

• minmax：保存每个 granule 的最小值和最大值，适合数值、日期列，维护成本低。
• set：保存每个 granule 内的一组 distinct 值，适合未进入排序键的低基数过滤列。

重量级索引：

• bloom_filter：适合高基数字符串、ID、URL 等精确匹配，有误判但无漏判。
• ngrambf_v1 / tokenbf_v1：适合 LIKE、hasToken 等文本检索。
• Text：面向文本搜索的倒排索引，能力强，但存储和写入放大也更高。

优先使用低成本索引。只有当查询加速能抵消写入和存储成本时，才考虑重量级索引。

5. 用 JSON 类型处理半结构化数据

原生 JSON 类型适合键不稳定、路径变化频繁或值类型不固定的半结构化数据。ClickHouse 会在插入时推断类型，并把发现的路径存为子列。

需要注意：

• 插入时类型推断有额外开销。
• 同一路径混合多种类型会增加存储成本。
• 结构明确的数据，即使来源是 JSON，也优先建成显式列。

max_dynamic_paths 控制最多有多少 JSON 路径被单独存储为子列。默认值是 1024；如果路径集合有限，可以调低，让结构更可控。

对稳定路径可以使用 hints：

CREATE TABLE events
(
    id UInt64,
    payload JSON(`timestamp` DateTime, `level` LowCardinality(String))
)
ENGINE = MergeTree
ORDER BY id;

选择建议：

• 扁平且稳定：使用显式列。
• 有稳定核心字段，同时保留动态扩展：核心字段建列，其余放 JSON。
• 完全不可预测：再考虑完全动态的 JSON 列。

6. 正确导入数据

ClickHouse 更适合大批量、低频率写入，而不是一行一行频繁插入。

对象存储导入：

• 优先使用 Parquet 或 ORC。
• 避免把 JSON 作为大批量导入的首选格式。
• 列式格式允许 ClickHouse 只读取需要的列，转换成本也更低。

CDC 和事件流：

• 托管场景可使用 ClickPipes 处理 Postgres、MySQL、MongoDB、Kafka、Kinesis 等数据源。
• 关注模式映射、偏移量管理、错误处理和背压。

应用直接写入：

• 客户端累积批次，按行数或时间间隔 flush。
• 或启用异步插入，让 ClickHouse 自动缓冲小批量写入。

SET async_insert = 1;
SET wait_for_async_insert = 1;

wait_for_async_insert = 1 会等待数据写入 part 后确认，兼顾小批量便利性和可靠错误处理。可以通过 system.asynchronous_insert_log 观察异步插入行为。

无论哪种方式，都应避免单行插入，并监控 system.parts 中的 part 数量。

7. 写入时计算：物化视图和投影

物化视图和投影的共同思路是：写入时多做计算，读取时少扫描、少聚合。

投影（Projection）是同一张表内部的替代排序或预聚合数据。查询命中投影时，ClickHouse 可以自动选择它，应用层无需改 SQL。代价是每次写入都要额外写入和排序投影数据。

验证投影是否真的被使用：

SET force_optimize_projection = 1;

如果查询找不到合适投影，ClickHouse 会报错，避免投影被静默忽略。不要为了“以防万一”添加大量投影，应先优化主键，再针对真实慢查询添加。

物化视图分两类：

• Refreshable materialized views：按计划重算，适合复杂转换，但需要考虑书签、回填和幂等。
• Incremental materialized views：作为插入触发器，对每个写入批次执行 SELECT 并写入目标表，适合维护持续聚合和汇总表。

增量物化视图只在插入时触发，源表删除或更新不会自动传播。包含 JOIN 时，也只有左表写入会触发视图更新，右表变化不会刷新已有结果。

常见模式是保留原始明细表，同时用物化视图维护仪表盘和高频查询所需的预聚合表。

8. 熟悉系统表

系统表提供 ClickHouse 内置可观测性，可以用 SQL 查看查询、合并、后台任务、错误和存储状态。

多副本环境中，直接查系统表通常只看到当前副本。需要完整视图时使用 clusterAllReplicas：

SELECT
    event_time,
    query_id,
    query,
    type
FROM clusterAllReplicas('default', merge('system', '^query_log*'))
WHERE event_time > now() - toIntervalMinute(5);

重点关注：

• system.query_log：查询生命周期、耗时、读行数、读字节、内存、异常、ProfileEvents。
• system.parts：MergeTree 表的 part 数量、大小、压缩率、分区和 active 状态。

查看各表 part 数量和大小：

SELECT
    table,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active
GROUP BY table
ORDER BY parts DESC;

定位过度分区：

SELECT
    table,
    partition,
    count() AS parts
FROM system.parts
WHERE active
GROUP BY table, partition
HAVING parts > 10
ORDER BY parts DESC;

9. 理解 ReplacingMergeTree

ReplacingMergeTree 常用于去重和 upsert。它会基于 ORDER BY 列判断重复行，并按版本列或时间戳保留最新版本。但去重发生在后台 merge 过程中，是异步的；查询时可能仍能看到重复数据。

获取正确结果通常有两种方式。

使用 FINAL：

SELECT star_rating
FROM mytests.amazon_reviews_rmt FINAL
WHERE review_id = '';

FINAL 简单直接，但查询前需要协调多个 part。part 越多，开销越大；历史分区充分合并后，开销会明显降低。

使用 argMax：

SELECT argMax(star_rating, review_date)
FROM mytests.amazon_reviews_rmt
WHERE review_id = '';

argMax 把去重逻辑放进聚合中，通常在活跃写入、part 较多时延迟更稳定。

选择建议：

• 表合并良好、日常查询追求简单：使用 FINAL。
• 活跃写入、需要更可预测延迟：使用 argMax。

可以通过更积极的后台合并改善历史分区上的 FINAL：

min_age_to_force_merge_seconds = 600,
min_age_to_force_merge_on_partition_only = 1

这会增加后台 CPU 和 I/O 负载，应结合写入压力评估。该策略通常需要合理分区，例如按月或按年，让每个分区保持在可控大小内。

10. 优化 JOIN

ClickHouse 的 JOIN 能力已经增强，例如谓词下推、双表 JOIN 自动重排，以及更多表 JOIN 的优化。但 JOIN 在分析型数据库中仍有成本。

建议：

• 对毫秒级延迟敏感的实时查询，尽量控制在 3 到 4 个 JOIN 以内。
• 小型、低频变化的维度表优先考虑 Dictionary。
• 高频聚合可用物化视图预计算。
• 必要时通过反范式化减少实时 JOIN。

字典适合静态或缓慢变化的查找数据。字典加载到内存后，可以通过 dictGet 访问，绕过常规哈希 JOIN 构建过程。原文基准中，在 1.5 亿行数据上，普通 JOIN 耗时 2.3 秒，字典 JOIN 耗时 1.36 秒，dictGet 耗时 0.86 秒。

总结

这些实践不是孤立的：

• 好的 ORDER BY 能减少扫描，也能让跳过索引更有效。
• 合适的数据类型能降低存储、解压和物化视图成本。
• 合理分区能配合 TTL 和 ReplacingMergeTree，但过度分区会伤害查询。
• 健康的数据摄取能减少小 part，从而改善 merge 和 FINAL。
• 高频查询可以用投影、物化视图、字典或反范式化前移计算。

新表设计时，优先关注排序键和数据类型；它们影响最大，也最难低成本修改。随后根据真实查询瓶颈，再选择跳过索引、分区、JSON、物化视图、投影或 JOIN 优化策略。