Clickhouse analytics io

ClickHouse 查询优化与分析

何时使用

• 在 ClickHouse 上做高吞吐 OLAP 分析：时序、漏斗、留存、cohort、Top-N、分位数等大规模聚合。
• 设计列存表：选 MergeTree 家族引擎、定分区键 / 排序键（ORDER BY）/ 主键，做容量与压缩取舍。
• 优化慢分析查询、用 ClickHouse 专有聚合函数与窗口函数改写。
• 用物化视图做实时预聚合；用 AggregatingMergeTree + *State/*Merge 维护增量指标。
• 批量 / 流式灌数，以及用 system.* 表做查询性能与表体积监控。

不该用的边界：

• OLTP 场景、高频单行点写 / 点改 / 删除、需要事务强一致或行级锁 → ClickHouse 不擅长，改用 PostgreSQL/MySQL（见 postgresql-optimization）。
• 仅写一条普通 SQL、不涉及列存特性或性能 → 用 sql-query-builder。
• 非 ClickHouse 数据库 → 引擎、分区语义、系统表、函数名均不同，勿照搬本技能 DDL。
• 频繁小批量 insert / 大量 JOIN / 依赖 FINAL 实时去重的负载 → 先重设数据模型，否则性能反受其害。

步骤 / 指令

1. 选引擎（按数据语义）
   - 普通明细/事实表 → MergeTree
   - 同主键需去重（多源、重放）→ ReplacingMergeTree（后台异步去重，查询非实时唯一）
   - 维护预聚合指标 → AggregatingMergeTree（配 *State 写入、*Merge 读出）
2. 定分区键与排序键（最影响性能）
   - PARTITION BY 用时间（toYYYYMM(date) 月级常用），避免分区过多拖垮 merge
   - ORDER BY 把最常过滤的列放最前；兼顾基数与压缩，等值列在前、范围列在后
   - index_granularity 默认 8192，无特殊需求勿动
3. 写高效查询
   - 过滤优先命中 ORDER BY 前缀列（date/market_id…），别先过滤非索引列
   - 用 ClickHouse 聚合函数：uniq() 近似去重、quantile(p)() 取分位数（比 percentile 高效）
   - 累计/排名用窗口函数 sum(...) OVER (PARTITION BY ... ORDER BY ...)
   - 只 SELECT 需要的列，禁 SELECT *
4. 批量插入（关键）
   - 单条大 INSERT 携多行，按千~万行成批；切忌循环里逐行 insert
   - 持续摄入用流式/批缓冲，攒够一批再落盘
5. 物化视图做实时聚合
   - CREATE MATERIALIZED VIEW ... TO <聚合表> AS SELECT ... sumState()/countState()/uniqState()
   - 读取时对聚合表用 sumMerge()/countMerge()/uniqMerge() 合并
6. 监控
   - 慢查询：system.query_log（type='QueryFinish'，按 query_duration_ms 排序）
   - 表体积：system.parts（active），formatReadableSize(sum(bytes))

示例

建明细表（MergeTree，月分区）：

CREATE TABLE markets_analytics (
    date Date, market_id String, market_name String,
    volume UInt64, trades UInt32, unique_traders UInt32,
    avg_trade_size Float64, created_at DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, market_id)
SETTINGS index_granularity = 8192;

预聚合表 + 物化视图（写入端 *State，读取端 *Merge）：

CREATE TABLE market_stats_hourly (
    hour DateTime, market_id String,
    total_volume AggregateFunction(sum, UInt64),
    total_trades AggregateFunction(count, UInt32),
    unique_users AggregateFunction(uniq, String)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour) ORDER BY (hour, market_id);

CREATE MATERIALIZED VIEW market_stats_hourly_mv TO market_stats_hourly AS
SELECT toStartOfHour(timestamp) AS hour, market_id,
       sumState(amount) AS total_volume,
       countState() AS total_trades,
       uniqState(user_id) AS unique_users
FROM trades GROUP BY hour, market_id;

-- 读取必须用 *Merge 合并状态
SELECT hour, market_id,
       sumMerge(total_volume) AS volume,
       countMerge(total_trades) AS trades,
       uniqMerge(unique_users) AS users
FROM market_stats_hourly
WHERE hour >= now() - INTERVAL 24 HOUR
GROUP BY hour, market_id ORDER BY hour DESC;

高效过滤与分位数：

-- GOOD：先命中排序键列
SELECT * FROM markets_analytics
WHERE date >= '2025-01-01' AND market_id = 'market-123' AND volume > 1000
ORDER BY date DESC LIMIT 100;

-- 分位数用 quantile，远比 percentile 高效
SELECT quantile(0.5)(trade_size) AS p50,
       quantile(0.95)(trade_size) AS p95,
       quantile(0.99)(trade_size) AS p99
FROM trades WHERE created_at >= now() - INTERVAL 1 HOUR;

留存分析（嵌套子查询算 days_since_signup）：

SELECT signup_date,
       countIf(days_since_signup = 0)  AS day_0,
       countIf(days_since_signup = 1)  AS day_1,
       countIf(days_since_signup = 7)  AS day_7,
       countIf(days_since_signup = 30) AS day_30
FROM (
    SELECT user_id,
           min(toDate(timestamp)) AS signup_date,
           toDate(timestamp) AS activity_date,
           dateDiff('day', signup_date, activity_date) AS days_since_signup
    FROM events GROUP BY user_id, activity_date
)
GROUP BY signup_date ORDER BY signup_date DESC;

批量插入（TypeScript，攒批一次写）：

// 单条 INSERT 携多行 = 高效；切勿循环逐行 insert
async function bulkInsertTrades(trades: Trade[]) {
  const values = trades.map(t =>
    `('${t.id}','${t.market_id}','${t.user_id}',${t.amount},'${t.timestamp.toISOString()}')`
  ).join(',')
  await clickhouse.query(
    `INSERT INTO trades (id, market_id, user_id, amount, timestamp) VALUES ${values}`
  ).toPromise()
}

监控慢查询与表体积：

SELECT query_id, query, query_duration_ms, read_rows, read_bytes, memory_usage
FROM system.query_log
WHERE type = 'QueryFinish' AND query_duration_ms > 1000
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC LIMIT 10;

SELECT database, table, formatReadableSize(sum(bytes)) AS size, sum(rows) AS rows
FROM system.parts WHERE active GROUP BY database, table
ORDER BY sum(bytes) DESC;

注意事项

• 分区别太碎：分区数过多严重拖累后台 merge 与查询规划；时间维度按月（必要时按天）即可，别用高基数列做分区。
• 排序键决定一切：最常过滤的列放 ORDER BY 最前，过滤未命中前缀列时退化为全表/全分区扫描；排序还直接影响压缩率。
• 聚合状态读写要配对：AggregatingMergeTree / 物化视图写入用 *State，读取必须用 *Merge，否则拿到的是二进制中间态而非结果。
• 批量灌数：ClickHouse 为大批写优化，频繁小 insert 会生成大量 part 触发持续 merge；务必攒批。
• 少用 FINAL：ReplacingMergeTree 去重是后台异步的，查询期 FINAL 强制合并代价高；优先在数据模型层规避重复，或接受最终一致。
• 数据类型省着用：选最小够用类型（UInt32 优于 UInt64）；重复字符串用 LowCardinality，类别字段用 Enum，显著省空间提速。
• JOIN 是弱项：分析负载尽量反范式化、宽表化，避免多表 JOIN；必要时小表放右侧。
• 任何建表/改引擎/删分区等 DDL 在生产执行前须确认并具备回滚预案；本技能产出方案，不擅自在生产执行。

互见

• requires：无。
• related：sql-query-builder（先写对查询，再来本技能用列存特性调优）、postgresql-optimization（OLTP/事务侧用 PG，OLAP 侧用 ClickHouse，常配 CDC 同步）、nosql-distributed-db、snowflake-development（另一类云数仓选型对照）。
• combines_with：data-pipeline-engineer、dbt-transformation-modeler（建模/转换层把数据物化进 ClickHouse）、spark-job-optimization、polars-dataframe（上游大规模 ETL/清洗后批量灌入）。

---

采编自 sickn33/antigravity-awesome-skills（MIT）。已适配重写：将原按主题罗列的 patterns 文档改写为「选引擎→分区/排序键→查询→灌数→物化视图→监控」可执行 playbook，补全负边界（OLTP/小批写/FINAL/JOIN），保留 MergeTree/ReplacingMergeTree/AggregatingMergeTree DDL、*State/*Merge 物化视图、quantile/uniq/窗口函数、批量插入、system.query_log/system.parts 监控及 LowCardinality/Enum/反范式化等关键约束与代码。