clickhouse-architect

ClickHouse 架构设计师

为 ClickHouse（v24.4+）提供规范化的架构设计、压缩方式选择和性能优化。涵盖 ClickHouse Cloud（SharedMergeTree）和自托管（ReplicatedMergeTree）部署。

自我进化技能：此技能会随着使用不断改进。如果说明有误、参数发生偏移，或需要采用变通方案，请立即修复此文件，不要拖延。仅针对真实且可复现的问题进行更新。

何时使用此技能

在以下情况下使用此技能：

• 设计 ClickHouse 表架构并选择 ORDER BY 键
• 为列类型选择压缩编解码器
• 配置分区键以管理数据生命周期
• 添加性能加速器（投影、索引、字典）
• 审查并优化现有 ClickHouse 架构

核心方法

架构设计工作流

设计或审查 ClickHouse 架构时，请按以下顺序进行：

1. 定义 ORDER BY 键（3-5 列，基数最低的列排在前面）
2. 为各列选择压缩编解码器
3. 配置 PARTITION BY 以管理数据生命周期
4. 添加性能加速器（投影、索引）
5. 使用审计查询进行验证（参见 scripts/）
6. 使用 COMMENT 语句编写文档 — ClickHouse 表和列上的 COMMENT 是说明各列含义、计算方式及适用约束的唯一事实来源（SSoT）。任何外部文档、技能或 wiki 均不能取代 COMMENT。参见 references/schema-documentation.md

ORDER BY 键选择

ORDER BY 子句是 ClickHouse 架构设计中最关键的决策。

规则：

• 最多限制为 3-5 列（每增加一列，收益都会递减）
• 基数最低的列放在前面（例如，将 tenant_id 放在 timestamp 之前）
• 包含 WHERE 子句中用于范围查询的所有列
• PRIMARY KEY 必须是 ORDER BY 的前缀（或者省略 PRIMARY KEY，以使用完整的 ORDER BY）

示例：

-- Correct: Low cardinality first, 4 columns
CREATE TABLE trades (
    exchange LowCardinality(String),
    symbol LowCardinality(String),
    timestamp DateTime64(3),
    trade_id UInt64,
    price Float64,
    quantity Float64
) ENGINE = MergeTree()
ORDER BY (exchange, symbol, timestamp, trade_id);

-- Wrong: High cardinality first (10x slower queries)
ORDER BY (trade_id, timestamp, symbol, exchange);

压缩编解码器快速参考

列类型	默认编解码器	读密集型替代方案	示例
DateTime/DateTime64	CODEC(DoubleDelta, ZSTD)	CODEC(DoubleDelta, LZ4)	timestamp DateTime64(3) CODEC(DoubleDelta, ZSTD)
Float 价格/仪表值	CODEC(Gorilla, ZSTD)	CODEC(Gorilla, LZ4)	price Float64 CODEC(Gorilla, ZSTD)
整数计数器	CODEC(T64, ZSTD)	—	count UInt64 CODEC(T64, ZSTD)
变化缓慢的整数	CODEC(Delta, ZSTD)	CODEC(Delta, LZ4)	version UInt32 CODEC(Delta, ZSTD)
字符串（低基数）	LowCardinality(String)	—	status LowCardinality(String)
通用数据	CODEC(ZSTD(3))	CODEC(LZ4)	默认压缩级别为 3

何时使用 LZ4 而非 ZSTD：LZ4 的解压速度快 1.76 倍。对于包含单调序列（时间戳、计数器）的读密集型工作负载，请使用 LZ4。压缩率很重要或数据模式未知时，请使用 ZSTD（默认值）。

关于编解码器组合的说明：

Delta/DoubleDelta + Gorilla 组合默认被禁止（allow_suspicious_codecs），因为 Gorilla 已在内部执行隐式增量压缩，再与其组合属于冗余，而非危险操作。历史上的损坏漏洞（PR #45615，2023 年 1 月）已修复，但保留此限制是最佳实践防护措施。

请根据数据类型分别使用适合的编解码器系列：

-- Correct usage
price Float64 CODEC(Gorilla, ZSTD)              -- Floats: use Gorilla
timestamp DateTime64 CODEC(DoubleDelta, ZSTD)   -- Timestamps: use DoubleDelta
timestamp DateTime64 CODEC(DoubleDelta, LZ4)    -- Read-heavy: use LZ4

PARTITION BY 指南

PARTITION BY 用于数据生命周期管理，而非查询优化。

规则：

• 按时间单位（月份、星期）分区，以便设置 TTL 和管理数据
• 所有表的分区总数保持在 1000 个以内
• 每个分区最多应包含 1-300 个 part
• 切勿按高基数列分区

示例：

-- Correct: Monthly partitions for TTL management
PARTITION BY toYYYYMM(timestamp)

-- Wrong: Daily partitions (too many parts)
PARTITION BY toYYYYMMDD(timestamp)

-- Wrong: High-cardinality partition key
PARTITION BY user_id

反模式检查清单（v24.4+）

模式	严重程度	当前状态	修复
part 过多（每个分区 >300 个）	严重	仍然严重	降低分区粒度
小批量插入（<1000 行）	严重	仍然严重	每批插入 10k-100k 行
ORDER BY 首列为高基数列	严重	仍然严重	重新排序：基数最低的列放在最前面
未设置内存限制	高	仍然严重	设置 max_memory_usage
过度使用反规范化	高	仍然严重	使用字典 + 物化视图
大型 JOIN	中	性能提升 180 倍	超低延迟场景仍应避免
Mutations（UPDATE/DELETE）	中	性能提升 1700 倍	使用轻量级 UPDATE（v24.4+）；参见下方的 DELETE 策略指南

DELETE 策略指南（v13.49.0+ 最佳实践）

根据影响范围选择合适的 DELETE 策略。按速度从快到慢排序：

策略	语法	速度	适用场景
DROP PARTITION	ALTER TABLE t DROP PARTITION (key1, key2, keyN)	即时（仅修改元数据）	清除整个分区范围（月度数据、损坏数据、测试数据）
DELETE IN PARTITION	ALTER TABLE t DELETE IN PARTITION (...) WHERE condition	快速（扫描 1 个分区）	在已知分区内有针对性地删除行
ALTER TABLE DELETE	ALTER TABLE t DELETE WHERE condition	缓慢（扫描所有 part）	分区未知时的备用方案
DELETE FROM（轻量级）	DELETE FROM t WHERE condition	不定	写入流水线中的反模式——参见下方警告

反模式：INSERT 前执行轻量级 DELETE FROM

DELETE FROM 会设置 _row_exists=0 标记，而不是从物理上删除行。这些幽灵行：

• 会一直保留到 ClickHouse 后台合并完成（时间不可预测）
• 在不使用 FINAL 的查询中会显示为虚假数据
• 会导致监控和完整性检查出现错误异常
• 是生产环境中 Stathera 数月来出现幽灵缺口的根本原因（opendeviationbar #269）

仅在以下情况下使用 DELETE FROM：临时修正数据，且不受幽灵行影响（分析数据清理、开发/测试）。在 INSERT 紧随 DELETE 的写入流水线中，切勿使用。

所有 DELETE mutations 都应使用：SETTINGS mutations_sync = 1，以阻塞操作直至完成（防止 INSERT-DELETE 竞态条件）。

分区感知 DELETE 提示：如果分区键包含用于筛选的列（例如 PARTITION BY (symbol, threshold, toYYYYMM(timestamp))），请使用 DELETE IN PARTITION，将扫描范围限定为单个分区，而不是扫描所有 part。

表引擎选择

部署方式	引擎	用途
ClickHouse Cloud	SharedMergeTree	云部署的默认选择
自托管集群	ReplicatedMergeTree	多节点复制
自托管单节点	MergeTree	单节点开发／测试

云端（SharedMergeTree）：

CREATE TABLE trades (...)
ENGINE = SharedMergeTree('/clickhouse/tables/{shard}/trades', '{replica}')
ORDER BY (exchange, symbol, timestamp);

自托管（ReplicatedMergeTree）：

CREATE TABLE trades (...)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/trades', '{replica}')
ORDER BY (exchange, symbol, timestamp);

技能委派指南

此技能是 ClickHouse 相关任务的中心。当用户的需求超出架构设计范围时，请调用以下相关技能。

委派决策矩阵

用户需求	调用技能	触发短语
创建数据库用户、管理权限	devops-tools:clickhouse-cloud-management	“创建用户”、“GRANT”、“权限”、“凭据”
配置 DBeaver、生成连接 JSON	devops-tools:clickhouse-pydantic-config	“DBeaver”、“客户端配置”、“连接设置”
根据实时数据库验证架构契约	quality-tools:schema-e2e-validation	“验证架构”、“Earthly E2E”、“架构契约”

典型工作流顺序

1. 架构设计（本技能）→ 设计 ORDER BY、压缩和分区
2. 用户设置 → clickhouse-cloud-management（需要云凭据时）
3. 客户端配置 → clickhouse-pydantic-config（生成 DBeaver JSON）
4. 验证 → schema-e2e-validation（CI/CD 架构契约）

示例：全栈请求

用户：“我需要为 ClickHouse Cloud 设计一张交易表，并设置 DBeaver 以便查询。”

预期行为：

1. 使用本技能进行架构设计
2. 调用 clickhouse-cloud-management 创建数据库用户
3. 调用 clickhouse-pydantic-config 配置 DBeaver

性能加速器

投影

创建备选排序方式，由 ClickHouse 自动选择：

ALTER TABLE trades ADD PROJECTION trades_by_symbol (
    SELECT * ORDER BY symbol, timestamp
);
ALTER TABLE trades MATERIALIZE PROJECTION trades_by_symbol;

物化视图

为仪表板查询预先计算聚合结果：

CREATE MATERIALIZED VIEW trades_hourly_mv
ENGINE = SummingMergeTree()
ORDER BY (exchange, symbol, hour)
AS SELECT
    exchange,
    symbol,
    toStartOfHour(timestamp) AS hour,
    sum(quantity) AS total_volume,
    count() AS trade_count
FROM trades
GROUP BY exchange, symbol, hour;

字典

使用 O(1) 字典查找替代 JOIN，以支持大规模星型架构：

适合使用字典的情况（v24.4+）：

• 事实表有 1 亿行以上，且需联接维度表
• 维度表有 1 千至 50 万行，且键单调递增
• 需要 LEFT ANY JOIN 语义

JOIN 已足够的情况（v24.4+）：

• 维度表少于 500 行（JOIN 开销可忽略）
• v24.4+ 谓词下推可带来 8–180 倍的性能提升
• 复杂 JOIN 类型（FULL、RIGHT、多条件）

基准测试背景：在星型架构基准测试（14 亿行）中测得 6.6 倍的加速。

CREATE DICTIONARY symbol_info (
    symbol String,
    name String,
    sector String
)
PRIMARY KEY symbol
SOURCE(CLICKHOUSE(TABLE 'symbols'))
LAYOUT(FLAT())  -- Best for <500k entries with monotonic keys
LIFETIME(3600);

-- Use in queries (O(1) lookup)
SELECT
    symbol,
    dictGet('symbol_info', 'name', symbol) AS symbol_name
FROM trades;

脚本

执行全面的架构审计：

clickhouse-client --multiquery < scripts/schema-audit.sql

审计脚本会检查：

• 每个分区的部件数量（阈值：300）
• 按列统计的压缩率
• 查询性能模式
• 复制延迟（如适用）
• 内存使用模式

附加资源

参考文件

参考	内容
references/schema-design-workflow.md	完整工作流和示例
references/compression-codec-selection.md	决策树和基准测试
references/anti-patterns-and-fixes.md	13 个致命错误及 v24.4+ 状态
references/audit-and-diagnostics.md	查询解读指南
references/idiomatic-architecture.md	参数化视图、字典、去重
references/schema-documentation.md	COMMENT 模式及便于 AI 理解的命名
references/cache-schema-evolution.md	缓存失效和模式演进模式

外部文档

• ClickHouse 最佳实践
• Altinity 知识库
• ClickHouse 博客

Python 驱动策略

所有 Python 集成都使用官方的 clickhouse-connect。

# ✅ RECOMMENDED: clickhouse-connect (official, HTTP)
import clickhouse_connect

client = clickhouse_connect.get_client(
    host='localhost',
    port=8123,  # HTTP port
    username='default',
    password=''
)
result = client.query("SELECT * FROM trades LIMIT 1000")
df = client.query_df("SELECT * FROM trades")  # Pandas integration

为什么不使用 clickhouse-driver

因素	clickhouse-connect	clickhouse-driver
维护者	ClickHouse Inc.	独立开发者
每周提交次数	是（活跃）	很少（数月一次）
未解决的问题	41（已处理）	76（持续累积）
每周下载量	2.7M	1.5M
单点故障风险	低（公司）	高（1 人）

尽管 clickhouse-driver 在大型导出任务中速度快约 26%，也不要使用它。维护风险超过性能收益：

• 只有一名维护者（mymarilyn），且没有继任计划
• 问题持续累积，无人响应
• 项目被弃用的风险会导致生产代码故障

例外：只有在性能要求极高（导出数百万行）且接受维护风险时，才考虑使用 clickhouse-driver。

ClickHouse COMMENT = 唯一事实来源

每个 ClickHouse 表和列都必须有 COMMENT，完整说明其含义、计算方法和限制条件。 COMMENT 是唯一事实来源（SSoT）——任何外部文档、技能或 wiki 都不能取代它。

原因

• ClickHouse COMMENT 没有长度限制，支持换行、URL 和 Unicode
• 对查询没有任何性能影响（纯元数据，不会进入数据路径）
• 可通过 DESCRIBE table、SHOW CREATE TABLE、system.columns 查看
• 可在模式迁移中保留（ALTER 操作会保留）

COMMENT 中应包含的内容

• 列的用途，以通俗易懂的英文描述
• 计算公式（适用于派生或计算所得的列）
• 单位（秒、毫秒、bps、比率）
• 有效范围或枚举值
• 反模式（此列不应如何使用）
• GitHub issue 链接，用于追溯来源
• 填充该列的源脚本

示例

ALTER TABLE t COMMENT COLUMN session_label
'STRICT session label. 8 values: sydney_only, tokyo_only, ...
Only set when ENTIRE bar (open→close) falls within one session.
cross_session = bar spans boundary. Use WHERE is_pure_session=1.
GitHub: https://github.com/org/repo/issues/54
Source: scripts/populate-sessions/populate_v3.py';

反模式

绝不要创建没有 COMMENT 的 ClickHouse 列。没有文档说明的列，终将被误用。

相关技能

技能	用途
devops-tools:clickhouse-cloud-management	用户／权限管理
devops-tools:clickhouse-pydantic-config	DBeaver 连接生成
quality-tools:schema-e2e-validation	YAML 架构契约
quality-tools:multi-agent-e2e-validation	数据库迁移验证

---

故障排除

问题	原因	解决方案
分区片段过多	分区过细	降低分区粒度（按月而非按日）
查询缓慢	ORDER BY 顺序错误	将基数最低的列放在最前面
内存使用量高	未设置内存限制	配置 max_memory_usage 设置
Delta+Gorilla 编解码器错误	可疑的编解码器组合	单独使用每种编解码器系列
未使用投影	优化器选择了不同的计划	检查 EXPLAIN，确认是否选择了投影
字典过期	有效期已结束	增加 LIFETIME 或触发刷新
复制延迟	部件合并落后	检查 merge_tree 设置并增加资源
INSERT 过慢	批次太小	每次 INSERT 批量写入 10k-100k 行

执行后反思

此技能完成后，请在结束前检查：

1. 命令是否成功？ — 如果没有，请修正导致失败的说明或错误表。
2. 参数或输出是否发生变化？ — 如果底层工具的接口发生了变化，请更新 Usage 示例和 Parameters 表，使其保持一致。
3. 是否需要变通方案？ — 如果你不得不临时调整（使用不同的标志、增加额外步骤），请更新此 SKILL.md，避免下次调用时再次需要相同的变通方案。

仅在问题真实且可复现时更新，不要更新推测性问题。