clickhouse-best-practices

ClickHouse 最佳实践

针对 ClickHouse 的全面指导，涵盖模式设计、查询优化和数据摄入。包含 3 个主要类别（模式、查询、插入）下的 28 条规则，按影响程度排序。

官方文档： ClickHouse 最佳实践

重要：如何应用此技能

在回答 ClickHouse 问题之前，请遵循以下优先级顺序：

1. 在 rules/ 目录中检查适用的规则
2. 如果规则存在： 应用这些规则，并在回复中使用“根据 rule-name……”引用它们
3. 如果不存在规则： 使用大模型的 ClickHouse 知识或搜索文档
4. 如果不确定： 使用 Web 搜索获取当前最佳实践
5. 始终引用来源： 规则名称、“通用 ClickHouse 指导”或 URL

为什么规则优先： ClickHouse 具有特定行为（列式存储、稀疏索引、合并树机制），一般数据库直觉可能会产生误导。这些规则编码了经过验证、针对 ClickHouse 的指导。

Langfuse 特定规则

• 对 events 表的查询请使用 packages/shared/src/server/queries/clickhouse-sql/event-query-builder.ts。除非你先确认查询构建器无法表达该查询，否则不要手写 events 的 SQL。
• 绝不要在 events 表上使用 FINAL；该表的设计本身就不需要 FINAL，而且这个关键字会损害性能。
• ClickHouse 查询归因以 JSON 形式存储在 system.query_log.log_comment 中，来自 packages/shared/src/server/clickhouse/queryTags.ts。使用 JSONExtractString(log_comment, 'surface')、JSONExtractString(log_comment, 'route') 和 JSONExtractString(log_comment, 'projectId') 解析它。已知的 surface 值为 trpc、publicapi、worker、mcp 和 unknown；ClickhouseWriter 的插入使用 projectId = "MULTI_PROJECT"。
• 查询归因通过 OpenTelemetry baggage 传播。入口点调用 packages/shared/src/server/headerPropagation.ts 中的 contextWithLangfuseProps(...)，设置 ClickHouse 的 surface、可选的 route 和可选的 projectId。然后 ClickHouse 仓库层通过 normalizeClickHouseQueryTags(...) 读取 baggage，并将其写入 log_comment。优先在入口点设置归因，而不是在每次仓库调用中传递标签。
• packages/shared/clickhouse/migrations/canonical/** 是唯一的规范模板树，会为集群和非集群安装渲染。在每一处与集群相关的 DDL 位置放置 {CLICKHOUSE_CLUSTER_CLAUSE}。仅在引擎因模式而有意不同时使用 {CLICKHOUSE_REPLICATION_PREFIX}；有些表在两种模式下都刻意保持非复制。
• 在新的规范迁移中，每个元数据 ALTER（ADD/DROP/MODIFY COLUMN、ADD/DROP INDEX）都必须包含 {CLICKHOUSE_CLUSTERED_ONLY: SETTINGS alter_sync = 2}，而每个创建 mutation 的 ALTER（MATERIALIZE …、UPDATE、DELETE）都必须包含 {CLICKHOUSE_CLUSTERED_ONLY: SETTINGS mutations_sync = 2}。这同样适用于只包含单个 ALTER 的文件——竞争发生在迁移文件之间，而不是单个文件内部。alter_sync 默认为 1，因此只要发起副本在 Keeper 中提升了表的元数据版本，该语句就会返回；golang-migrate 随后会立即打开下一个文件，而它对该表的第一个 ALTER 可能落到仍处于上一版本的副本上。ClickHouse 拒绝将其排队，并以 code 517 中止整个运行，因为该副本的元数据版本落后于公共元数据版本。注意，mutations_sync 不能替代 alter_sync：它管的是 mutation 何时完成，而不是元数据传播。渲染器会为非集群的 MergeTree 迁移省略这些片段。仅对有意区分的特定模式差异使用 {CLICKHOUSE_UNCLUSTERED_ONLY:...}。不要仅仅为了规范化，就把同步设置补加到已经发布的迁移中；历史兼容性测试会有意保护它们现有的输出。
• 绝不要在 ClickHouse 迁移中使用 CREATE OR REPLACE VIEW（也不要使用 CREATE OR REPLACE TABLE / EXCHANGE TABLES）。原子替换需要文件系统支持 renameat2，而 NFS 后端的自托管部署（例如 ClickHouse 数据位于 AWS EFS）缺少该支持——迁移会失败，部署会在启动时中止（GitHub issue #14906）。要在同一个迁移文件中用两条语句重新定义普通视图。首先使用 DROP VIEW IF EXISTS <name> {CLICKHOUSE_CLUSTER_CLAUSE};，然后使用 CREATE VIEW <name> {CLICKHOUSE_CLUSTER_CLAUSE} AS …。迁移运行器会传递 x-multi-statement=true，而 golang-migrate 会在不解析 SQL 的情况下按 ; 拆分文件，因此注释和字符串字面量中不要出现分号。让每条语句保持幂等（IF EXISTS/IF NOT EXISTS），这样在 migrate force 之后，一个处于脏状态、只应用了一半的迁移可以重新运行。在 drop→create 窗口内访问该视图的读取方会出现瞬时失败——对于 analytics_* 导出视图来说可以接受，因此不要让普通视图处于产品热路径上。
• 绝不要删除并重新创建源表会接收实时插入的物化视图：在 DROP 和 CREATE 之间插入的每一行都会静默且永久地从目标表中缺失。用 ALTER TABLE <mv> {CLICKHOUSE_CLUSTER_CLAUSE} MODIFY QUERY <select> 修改 MV 的 SELECT，这样可以在不中断写入的情况下替换转换逻辑。当变更需要新增列时，先 ALTER 目标表（ADD COLUMN IF NOT EXISTS …），然后再 MODIFY QUERY；这些目标表的 ALTER 必须带上仅集群使用的 alter_sync 模板片段，以免某个主机在其目标副本拥有新列之前就应用新的 MV 查询。MODIFY QUERY 仅适用于 TO 表 MV（所有 Langfuse MV 都使用 TO）。

---

审查流程

针对 schema 审查（CREATE TABLE、ALTER TABLE）

按顺序阅读以下规则文件：

1. rules/schema-pk-plan-before-creation.md - ORDER BY 不可变
2. rules/schema-pk-cardinality-order.md - 键中的列顺序
3. rules/schema-pk-prioritize-filters.md - 过滤列的包含
4. rules/schema-types-native-types.md - 正确的类型选择
5. rules/schema-types-minimize-bitwidth.md - 数值类型大小设定
6. rules/schema-types-lowcardinality.md - LowCardinality 的使用
7. rules/schema-types-avoid-nullable.md - Nullable 与 DEFAULT
8. rules/schema-partition-low-cardinality.md - 分区数量限制
9. rules/schema-partition-lifecycle.md - 分区的用途

检查以下内容：

• PRIMARY KEY / ORDER BY 列顺序（基数从低到高）
• 数据类型与实际数据范围匹配
• LowCardinality 应用于合适的字符串列
• 分区键基数有界（100-1,000 个值）
• 若使用 ReplacingMergeTree，需有版本列
• 新的规范迁移中每个元数据 ALTER 都包含 {CLICKHOUSE_CLUSTERED_ONLY: SETTINGS alter_sync = 2} — 包括只有一个 ALTER 的文件，因为下一个迁移文件才是破坏点 — 并且每个 MATERIALIZE … / UPDATE / DELETE 都包含相应的 mutations_sync 片段；mutations_sync 不能替代 alter_sync；不要规范化已发布的迁移输出；两种渲染模式都通过 prepareMigrations.test.ts
• 迁移中没有 CREATE OR REPLACE VIEW/TABLE 或 EXCHANGE TABLES（会破坏 NFS/EFS 自托管）；普通视图通过同一文件中的 DROP VIEW IF EXISTS + CREATE VIEW 重新定义
• 物化视图在其源表接收插入时绝不能被删除并重新创建；SELECT 更改通过目标表 ALTER 之后的 ALTER TABLE <mv> MODIFY QUERY 进行

针对查询审查（SELECT、JOIN、聚合）

阅读以下规则文件：

1. rules/query-join-choose-algorithm.md - 算法选择
2. rules/query-join-filter-before.md - 连接前过滤
3. rules/query-join-use-any.md - ANY 与常规 JOIN
4. rules/query-index-skipping-indices.md - 二级索引的使用
5. rules/schema-pk-filter-on-orderby.md - 过滤条件与 ORDER BY 的对齐

检查以下内容：

• 过滤条件使用 ORDER BY 前缀列
• JOIN 在连接前先过滤表（而非连接后）
• 针对表大小使用正确的 JOIN 算法
• 对非 ORDER BY 过滤列使用跳数索引

针对插入策略审查（数据摄取、更新、删除）

阅读以下规则文件：

1. rules/insert-batch-size.md - 批大小要求
2. rules/insert-mutation-avoid-update.md - UPDATE 的替代方案
3. rules/insert-mutation-avoid-delete.md - DELETE 的替代方案
4. rules/insert-async-small-batches.md - 异步插入的使用
5. rules/insert-optimize-avoid-final.md - OPTIMIZE TABLE 的风险

检查以下内容：

• 每次 INSERT 的批大小为 10K-100K 行
• 对频繁变更不使用 ALTER TABLE UPDATE
• 对更新模式使用 ReplacingMergeTree 或 CollapsingMergeTree
• 对高频小批次启用异步插入

---

输出格式

按如下结构组织你的回复：

## Rules Checked
- `rule-name-1` - Compliant / Violation found
- `rule-name-2` - Compliant / Violation found
...

## Findings

### Violations
- **`rule-name`**: Description of the issue
  - Current: [what the code does]
  - Required: [what it should do]
  - Fix: [specific correction]

### Compliant
- `rule-name`: Brief note on why it's correct

## Recommendations
[Prioritized list of changes, citing rules]

---

按优先级划分的规则类别

优先级	类别	影响	前缀	规则数量
1	主键选择	CRITICAL	schema-pk-	4
2	数据类型选择	CRITICAL	schema-types-	5
3	JOIN 优化	CRITICAL	query-join-	5
4	插入批处理	CRITICAL	insert-batch-	1
5	避免变更	CRITICAL	insert-mutation-	2
6	分区策略	HIGH	schema-partition-	4
7	跳数索引	HIGH	query-index-	1
8	物化视图	HIGH	query-mv-	2
9	异步插入	HIGH	insert-async-	2
10	避免 OPTIMIZE	HIGH	insert-optimize-	1
11	JSON 使用	MEDIUM	schema-json-	1

---

快速参考

Schema 设计 - 主键（CRITICAL）

• schema-pk-plan-before-creation - 建表前规划 ORDER BY（不可变）
• schema-pk-cardinality-order - 按基数从低到高排列列
• schema-pk-prioritize-filters - 包含频繁过滤的列
• schema-pk-filter-on-orderby - 查询过滤必须使用 ORDER BY 前缀

Schema 设计 - 数据类型（CRITICAL）

• schema-types-native-types - 使用原生类型，不要所有都用 String
• schema-types-minimize-bitwidth - 使用能容纳的最小数值类型
• schema-types-lowcardinality - 唯一字符串少于 10K 时使用 LowCardinality
• schema-types-enum - 有限值集且需校验时使用 Enum
• schema-types-avoid-nullable - 避免 Nullable；改用 DEFAULT

Schema 设计 - 分区（HIGH）

• schema-partition-low-cardinality - 保持分区数量在 100-1,000
• schema-partition-lifecycle - 将分区用于数据生命周期，而非查询
• schema-partition-query-tradeoffs - 理解分区裁剪的权衡
• schema-partition-start-without - 考虑从不用分区开始

Schema 设计 - JSON（MEDIUM）

• schema-json-when-to-use - 动态 schema 使用 JSON；已知字段使用类型化列

查询优化 - JOIN（CRITICAL）

• query-join-choose-algorithm - 根据表大小选择算法
• query-join-use-any - 只需一个匹配时使用 ANY JOIN
• query-join-filter-before - 在连接前过滤表
• query-join-consider-alternatives - 字典/反规范化 vs JOIN
• query-join-null-handling - join_use_nulls=0 以使用默认值

查询优化 - 索引（HIGH）

• query-index-skipping-indices - 为非 ORDER BY 过滤使用跳数索引

查询优化 - 物化视图（HIGH）

• query-mv-incremental - 使用增量物化视图实现实时聚合
• query-mv-refreshable - 使用可刷新物化视图处理复杂连接

插入策略 - 批处理（CRITICAL）

• insert-batch-size - 每次 INSERT 批量插入 10K-100K 行

插入策略 - 异步（HIGH）

• insert-async-small-batches - 高频小批量使用异步插入
• insert-format-native - 使用 Native 格式以获得最佳性能

插入策略 - 变更（CRITICAL）

• insert-mutation-avoid-update - 使用 ReplacingMergeTree 而非 ALTER UPDATE
• insert-mutation-avoid-delete - 使用轻量级 DELETE 或 DROP PARTITION

插入策略 - 优化（HIGH）

• insert-optimize-avoid-final - 让后台合并自行工作

---

适用时机

当遇到以下情况时，此技能会激活：

• CREATE TABLE 语句
• ALTER TABLE 修改
• ORDER BY 或 PRIMARY KEY 相关讨论
• 数据类型选择问题
• 慢查询排查
• JOIN 优化请求
• 数据摄取管道设计
• 更新/删除策略问题
• ReplacingMergeTree 或其他专用引擎的使用
• 分区策略决策

---

规则文件结构

rules/ 中的每个规则文件包含：

• YAML frontmatter：标题、影响级别、标签
• 简要说明：此规则为何重要
• 错误示例：反模式及说明
• 正确示例：最佳实践及说明
• 补充背景：权衡取舍、适用时机、参考资料