clickhouse-io

clickhouse-io Skill 多维度简评

类别：数据库 / OLAP 性能 仓库：affaan-m/everything-claude-code 维护者：Affaan Mustafa / ECC 社区

一、核心定位与价值

ClickHouse 是用于在线分析处理（OLAP）的列式数据库，在 PB 级数据上提供亚秒级查询速度。clickhouse-io Skill 专门为让 Agent 高效操作 ClickHouse而设计，覆盖：

• Schema 设计（MergeTree、PARTITION BY、ORDER BY）
• 查询优化（索引、采样、PREWHERE）
• 写入策略（批量、异步、Parquet）
• 性能调优（settings、merge、TTL）
• Agent 集成（MCP、SQL 安全、探索）

关键洞见：ClickHouse 的”反直觉”决策（如避免 nullable、谨慎 partition、优先 wide table）经常让通用 SQL 提示词给出错误建议——本 Skill 强制 ClickHouse 专属最佳实践。

适用场景

• 设计 ClickHouse 表结构
• 优化慢查询
• 数据导入策略（Kafka / S3 / file）
• 与 Agent 集成（安全连接、查询、schema 探查）
• 避免 FINAL、避免 nullable、避免过度 partition

不适用场景

• 通用 SQL 教学
• 事务型数据库（Postgres / MySQL）
• 实时强一致场景
• 极小数据量（< 1M 行用 SQLite 即可）

二、核心模式库

2.1 Schema 设计（31 条原子规则）

规则 1：选对 Engine

规则 2：ORDER BY 决定查询性能

-- ✅ GOOD: 把高基数过滤字段放前
CREATE TABLE events (
  event_date Date,
  tenant_id UInt64,
  user_id UInt64,
  event_type LowCardinality(String),
  payload JSON
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, user_id, event_date)
SETTINGS index_granularity = 8192;

关键原则：

• 等值字段在前，范围字段在后
• 高基数在前（user_id 比 event_type 优先）
• 不要把 event_date 放 ORDER BY 第一（除非主要按日期查询）

规则 3：分区（PARTITION BY）要克制

-- ❌ BAD: 每天一个分区，2 年 = 730 个分区
PARTITION BY toYYYYMMDD(event_date)

-- ✅ GOOD: 每月一个分区，2 年 = 24 个分区
PARTITION BY toYYYYMM(event_date)

警告：ClickHouse 官方明确——不要按高基数列分区（如 user_id），否则 merge 失控。

规则 4：避免 Nullable 列

-- ❌ BAD
CREATE TABLE t (a Nullable(String), b Nullable(Int32))

-- ✅ GOOD: 用默认值代替
CREATE TABLE t (
  a String DEFAULT '',
  b Int32 DEFAULT 0
)

原因：Nullable 列会引入额外的内存和性能开销。

规则 5：用 LowCardinality 优化枚举

-- ✅ GOOD: 字典编码
event_type LowCardinality(String)
country LowCardinality(String)
status LowCardinality(String)

适用：< 1 万种不同值的高频枚举字段。

规则 6：JSON / Tuple / Array 灵活建模

-- JSON 字段
payload JSON

-- Tuple（元组）
coordinates Tuple(latitude Float64, longitude Float64)

-- Array
tags Array(LowCardinality(String))

2.2 查询优化

规则 7：PREWHERE 自动列裁剪

-- ClickHouse 自动应用 PREWHERE 优化
-- 写法不用变，引擎会自动把过滤下推到最小列集
SELECT * FROM events
WHERE event_date >= '2026-01-01'
  AND tenant_id = 123
  AND event_type = 'click'

规则 8：用 SAMPLE 抽样加速

-- 查 10% 样本
SELECT count() FROM events SAMPLE 0.1
WHERE event_date >= '2026-01-01'

-- 固定 hash 抽样（保证一致性）
SELECT count() FROM events SAMPLE 1/10

规则 9：避免 SELECT *

-- ❌ BAD
SELECT * FROM events

-- ✅ GOOD: 显式列
SELECT event_date, tenant_id, user_id, event_type
FROM events
WHERE ...

规则 10：避免 FINAL（性能杀手）

-- ❌ BAD: 触发 merge，巨慢
SELECT * FROM events FINAL WHERE user_id = 123

-- ✅ GOOD: 容忍重复 + 后端去重
SELECT * FROM events
WHERE user_id = 123
GROUP BY user_id, event_date, event_type
  -- 或用 ReplacingMergeTree + 显式 ORDER BY
ORDER BY event_date DESC
LIMIT 1 BY user_id

规则 11：JOIN 选对算法

-- 哈希 JOIN（默认）
SELECT * FROM users u JOIN events e ON u.id = e.user_id

-- 并行哈希 JOIN（大表）
SETTINGS join_algorithm = 'parallel_hash'

-- 合并 JOIN（已排序数据）
SETTINGS join_algorithm = 'merge'

规则 12：先过滤再 JOIN

-- ❌ BAD: 全表 JOIN
SELECT * FROM events e
JOIN users u ON e.user_id = u.id

-- ✅ GOOD: 先过滤
WITH filtered_events AS (
  SELECT * FROM events
  WHERE event_date >= '2026-01-01'
)
SELECT * FROM filtered_events e
JOIN users u ON e.user_id = u.id

2.3 写入策略

规则 13：批量插入（不要单行）

# ❌ BAD: 单行插入
client.execute("INSERT INTO events VALUES", [row1])
client.execute("INSERT INTO events VALUES", [row2])

# ✅ GOOD: 批量 10k+ 行
batch = []
for row in stream:
    batch.append(row)
    if len(batch) >= 10000:
        client.execute("INSERT INTO events VALUES", batch)
        batch = []

规则 14：用 Parquet / Native 格式

-- 从 Parquet 导入（最快）
INSERT INTO events
SELECT * FROM s3('https://bucket/data/*.parquet', 'Parquet')

-- 从 CSV（最慢）
INSERT INTO events
SELECT * FROM file('data.csv', 'CSV', 'id UInt64, name String')

规则 15：异步小写入合并

-- 启用 async_insert
SETTINGS async_insert = 1,
        async_insert_max_data_size = 10485760,  -- 10MB
        wait_for_async_insert = 0

规则 16：不要 UPDATE / DELETE

-- ❌ BAD: mutation，慢
ALTER TABLE events DELETE WHERE event_date < '2025-01-01'

-- ✅ GOOD: 用 TTL 自动清理
ALTER TABLE events MODIFY TTL event_date + INTERVAL 2 YEAR

规则 17：避免高基数字典

-- ❌ BAD: 100 万种 user_id
ORDER BY (user_id)

-- ✅ GOOD: 哈希取模分桶
ORDER BY (cityHash64(user_id) % 16, user_id)

2.4 性能 Settings 速查

-- 大查询：增加内存
SETTINGS max_memory_usage = 20000000000  -- 20GB
         max_threads = 16
         max_block_size = 100000

-- 实时查询：低延迟
SETTINGS max_execution_time = 30
         timeout_before_checking_execution_speed = 1

-- 批量加载
SETTINGS max_insert_block_size = 1048576
         input_format_parallel_parsing = 1

三、Agent 集成（来自 ClickHouse 官方 MCP）

3.1 ClickHouse MCP Server

// .mcp.json
{
  "mcpServers": {
    "clickhouse": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-clickhouse"],
      "env": {
        "CLICKHOUSE_URL": "http://localhost:8123",
        "CLICKHOUSE_USER": "default",
        "CLICKHOUSE_PASSWORD": "${CLICKHOUSE_PASSWORD}",
        "CLICKHOUSE_DATABASE": "analytics"
      }
    }
  }
}

3.2 Skill 引导 Claude 安全操作

Step 1：先探索 schema

用 clickhouse-io Skill，列出 analytics 数据库所有表，
并展示每个表的列、engine、partition 策略。

Step 2：先小查询试水

查询前 100 行 events 表，验证 schema 假设。

Step 3：渐进式深入

统计 events 表的行数、磁盘大小、压缩比。

Step 4：避免无界扫描

禁止：SELECT count() FROM events
允许：SELECT count() FROM events
       WHERE event_date = today()

3.3 28 条 Agent 安全规则

# SKILL.md 中强制规则
1. 必须先 LIMIT 探索，再大查询
2. 禁止 SELECT count() 全表
3. 禁止无 WHERE 的 SELECT
4. 禁止 mutation（DELETE/UPDATE）
5. 必须用参数化查询，防止注入
6. 禁止生产环境执行 DDL
7. 大查询必须 EXPLAIN
8. 写入前先验证 schema
9. 重要操作前 SHOW CREATE TABLE
10. 监控 query_log 看慢查询
...

四、完整工作流示例

4.1 场景：优化慢查询

[用户] 这个查询跑了 30 秒：
SELECT count() FROM events
WHERE event_date >= '2026-01-01'
  AND tenant_id = 123

[Skill 引导 Claude 思考]
1. 查 schema：SHOW CREATE TABLE events
2. 检查 ORDER BY 是否有 (tenant_id, event_date)
3. 检查分区策略
4. 检查表大小、压缩率
5. 检查系统表：system.query_log

[Skill 输出修复方案]
- 增加 ORDER BY (tenant_id, event_date)
- 增加 SETTINGS 优化
- 考虑用物化视图

4.2 场景：设计新表

[用户] 我要存用户点击事件，预计每天 1 亿条，保留 2 年。
[要求] 按 user_id 查询快，按时间范围查询快。

[Skill 输出]
```sql
CREATE TABLE user_clicks (
  event_date Date,
  event_time DateTime,
  user_id UInt64,
  session_id UUID,
  event_type LowCardinality(String),
  page_path String,
  referrer String,
  user_agent String,
  ip_address IPv4,
  country LowCardinality(FixedString(2)),
  device_type LowCardinality(String),
  payload JSON
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (user_id, event_date, event_time)
SETTINGS index_granularity = 8192;

[解释]

• 1 亿条/天 → 每月 30 亿条 → 24 月 720 亿条
• 分区按月，避免过度碎片
• ORDER BY 把 user_id 放第一，按用户查快
• 时间和事件类型走 secondary index

### 4.3 场景：从 Kafka 实时导入

```sql
CREATE TABLE events_kafka (
  event_time DateTime,
  user_id UInt64,
  payload String
)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka:9092',
         kafka_topic_list = 'events',
         kafka_group_name = 'clickhouse-consumer',
         kafka_format = 'JSONEachRow';

-- Materialized View 转换并写入 MergeTree
CREATE MATERIALIZED VIEW events_mv TO events AS
SELECT
  toDate(event_time) AS event_date,
  event_time,
  user_id,
  payload
FROM events_kafka;

五、真实踩坑案例

案例 1：Nullable 列导致查询慢 10 倍

现象：SELECT count(*) FROM t WHERE a IS NULL 跑了 60 秒。 根因：Nullable 列内部有额外的 null 标志位 + 索引膨胀。 解决：去掉 Nullable，用默认值。

案例 2：过度分区（10 万个 partition）

现象：merge 永远追不上，磁盘爆。 根因：按 user_id 分区。 解决：改按月分区 + ORDER BY (user_id)。

案例 3：FINAL 关键字滥用

现象：用 ReplacingMergeTree 但查询时全加 FINAL，慢到无法用。 根因：FINAL 强制后台 merge 完成。 解决：用 ORDER BY ... LIMIT 1 BY user_id。

案例 4：JOIN 顺序反了

现象：大表在前，小表在后，跑了 1 小时。 根因：ClickHouse 不会自动优化 JOIN 顺序。 解决：小表在前，或显式用 ASOF / SEMI JOIN。

案例 5：异步插入丢数据

现象：async_insert 启用后，崩溃时丢失 1 分钟数据。 根因：wait_for_async_insert = 0 立即返回，buffer 在 OS cache。 解决：wait_for_async_insert = 1 同步等待。

案例 6：Materialized View 卡住

现象：MV 不更新数据。 根因：source Kafka 表暂停消费。 解决：检查 Kafka 消费进度，必要时重启。

案例 7：Distributed 表查询慢

现象：跨分片查询比单分片慢 5 倍。 根因：数据分布不均（个别分片热点）。 解决：检查 system.distribution_queue，重新分片。

案例 8：JSON 字段查询慢

现象：WHERE payload.country = 'US' 慢。 根因：JSON 字段没有索引。 解决：在 schema 阶段显式拆字段，或用 JSONAllPathsWithTypes 索引。

案例 9：冷热数据没分离

现象：查询近 7 天数据慢。 根因：老数据在冷盘（HDD），新数据在热盘（SSD）。 解决：用 storage_policy 分层存储。

案例 10：TTL 设置错误

现象：TTL 到期后查询仍能查到。 根因：TTL 删除是异步的（后台 merge 时才删）。 解决：强制 ALTER TABLE … MATERIALIZE TTL，或改用 DELETE WHERE。

六、性能基准

6.1 扫描速度（来自 ClickHouse 官方 benchmark）

6.2 压缩比

6.3 写入吞吐

七、Q&A

Q: 必须用 ClickHouse Cloud 吗？ A: 不必须。开源版本功能一样；Cloud 节省运维。

Q: 跟 Postgres 比？ A: ClickHouse = OLAP（分析）；Postgres = OLTP（事务）。不要混用。

Q: 跟 Snowflake / BigQuery 比？ A: 性能相当，价格便宜 5-10 倍。ClickHouse 自部署。

Q: 必须用 SSD 吗？ A: 强烈建议。HDD 上性能差 5-10 倍。

Q: 实时性如何？ A: 毫秒级 INSERT，秒级可查询。

Q: 支持事务吗？ A: 不支持 ACID 事务。只支持原子单 INSERT。

Q: 跟 Doris / StarRocks 比？ A: 性能相当，但 ClickHouse 社区更大、文档更全、Cloud 部署更简单。

Q: 中文支持？ A: 完全 UTF-8 支持，中文无障碍。

八、Q&A 实战相关

Q: 触发 Skill 后 Claude 怎么行动？ A: SKILL.md 引导：先探索 schema → 小查询验证 → 大查询 → 输出 EXPLAIN → 给出建议。

Q: 误操作危险吗？ A: 高。Skill 强制禁止无 WHERE 查询、禁止 mutation、禁止生产环境 DDL。

Q: 能自托管吗？ A: 可以。Docker / deb / rpm 都支持。

Q: 学习曲线？ A: 中等。懂 SQL 即可上手，深度优化需理解 MergeTree。

Q: 跟 Snowflake 集成？ A: 通过 S3 / Iceberg / JDBC 互通。

Q: License？ A: 核心是 Apache 2.0，ClickHouse Cloud 商业版。

Q: 跟 ClickHouse 官方文档关系？ A: Skill 是文档的浓缩 + AI 友好版本，文档是权威参考。

九、5 条反合理化

十、5 条实战技巧

1. 从 system.query_log 入手：找慢查询
2. EXPLAIN PIPELINE 调试：看执行计划
3. **避免 SELECT ***：用显式列
4. TTL + 冷热分层：节省 50% 成本
5. Materialized View 预聚合：把 1 秒查询变 50ms

十一、安装

# Claude Code
/plugin marketplace add affaan-m/everything-claude-code
/plugin install everything-claude-code@everything-claude-code
cp -r everything-claude-code/rules/common ~/.claude/rules/

# ClickHouse MCP（推荐）
claude mcp add clickhouse npx @modelcontextprotocol/server-clickhouse

# 通用
npx skills add affaan-m/everything-claude-code --skill clickhouse-io

十二、总结

核心价值：

• ClickHouse 专属最佳实践
• 31 条原子规则 + Agent 安全集成
• 避免通用 SQL 提示词给 ClickHouse 错误建议
• 性能调优 + 写入策略 + Schema 设计全覆盖

适用人群：

• 数据工程师
• 实时分析团队
• 大数据 ETL 工程师
• SaaS 监控 / 指标平台

投入产出比：⭐⭐⭐⭐（4/5）—— OLAP 项目必装。

何时不要用：

• 事务型业务（用 Postgres）
• 极小数据量
• 强一致场景
• 没用过 ClickHouse（先看官方文档）

配套文档：postgres-patterns 关系型 | backend-patterns 后端 | verification-loop 验证

参考资料

1. affaan-m/everything-claude-code GitHub
2. ClickHouse 官方文档
3. ClickHouse MCP Server
4. ClickHouse 最佳实践指南