
关于
ClickHouse 数据库模式、查询优化、分析和数据工程最佳实践,面向高性能分析工作负载。
name: cc-skill-clickhouse-io description: "ClickHouse 数据库模式、查询优化、分析和数据工程最佳实践,用于高性能分析工作负载。" risk: unknown source: community date_added: "2026-02-27"
ClickHouse 分析模式
ClickHouse 特定的高性能分析和数据工程模式。
概述
ClickHouse 是面向列的数据库管理系统(DBMS),用于在线分析处理(OLAP)。它针对大数据集上的快速分析查询进行了优化。
核心特性:
- 列式存储
- 数据压缩
- 并行查询执行
- 分布式查询
- 实时分析
表设计模式
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;
ReplacingMergeTree(去重)
-- 用于可能有重复的数据(如来自多个源)
CREATE TABLE user_events (
event_id String,
user_id String,
event_type String,
timestamp DateTime,
properties String
) ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (user_id, event_id, timestamp)
PRIMARY KEY (user_id, event_id);
AggregatingMergeTree(预聚合)
-- 用于维护聚合指标
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);
-- 查询聚合数据
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 >= toStartOfHour(now() - INTERVAL 24 HOUR)
GROUP BY hour, market_id
ORDER BY hour DESC;
查询优化模式
高效过滤
-- ✅ 好:先使用索引列
SELECT *
FROM markets_analytics
WHERE date >= '2025-01-01'
AND market_id = 'market-123'
AND volume > 1000
ORDER BY date DESC
LIMIT 100;
-- ❌ 差:先过滤非索引列
SELECT *
FROM markets_analytics
WHERE volume > 1000
AND market_name LIKE '%election%'
AND date >= '2025-01-01';
聚合
-- ✅ 好:使用 ClickHouse 特定的聚合函数
SELECT
toStartOfDay(created_at) AS day,
market_id,
sum(volume) AS total_volume,
count() AS total_trades,
uniq(trader_id) AS unique_traders,
avg(trade_size) AS avg_size
FROM trades
WHERE created_at >= today() - INTERVAL 7 DAY
GROUP BY day, market_id
ORDER BY day DESC, total_volume DESC;
-- ✅ 使用 quantile 计算百分位数(比 percentile 更高效)
SELECT
quantile(0.50)(trade_size) AS median,
quantile(0.95)(trade_size) AS p95,
quantile(0.99)(trade_size) AS p99
FROM trades
WHERE created_at >= now() - INTERVAL 1 HOUR;
窗口函数
-- 计算累计总量
SELECT
date,
market_id,
volume,
sum(volume) OVER (
PARTITION BY market_id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_volume
FROM markets_analytics
WHERE date >= today() - INTERVAL 30 DAY
ORDER BY market_id, date;
数据插入模式
批量插入(推荐)
import { ClickHouse } from 'clickhouse'
const clickhouse = new ClickHouse({
url: process.env.CLICKHOUSE_URL,
port: 8123,
basicAuth: {
username: process.env.CLICKHOUSE_USER,
password: process.env.CLICKHOUSE_PASSWORD
}
})
// ✅ 批量插入(高效)
async function bulkInsertTrades(trades: Trade[]) {
const values = trades.map(trade => \`(
'\${trade.id}',
'\${trade.market_id}',
'\${trade.user_id}',
\${trade.amount},
'\${trade.timestamp.toISOString()}'
)\`).join(',')
await clickhouse.query(\`
INSERT INTO trades (id, market_id, user_id, amount, timestamp)
VALUES \${values}
\`).toPromise()
}
// ❌ 单条插入(慢)
async function insertTrade(trade: Trade) {
// 不要在循环中这样做!
await clickhouse.query(\`
INSERT INTO trades VALUES ('\${trade.id}', ...)
\`).toPromise()
}
流式插入
// 用于持续数据摄取
import { createWriteStream } from 'fs'
import { pipeline } from 'stream/promises'
async function streamInserts() {
const stream = clickhouse.insert('trades').stream()
for await (const batch of dataSource) {
stream.write(batch)
}
await stream.end()
}
物化视图
实时聚合
-- 创建每小时统计的物化视图
CREATE MATERIALIZED VIEW market_stats_hourly
兼容工具
Claude CodeCursor
标签
数据工程
