ClickHouse数据库模式、查询优化、分析和数据工程最佳实践,适用于高性能分析工作负载。
技能概述
clickhouse-io 技能提供了ClickHouse列式数据库的专门开发模式和最佳实践。ClickHouse是一个面向列的数据库管理系统(DBMS),专为在线分析处理(OLAP)而优化,特别适合处理大规模数据集的快速分析查询。
下载地址:https://github.com/affaan-m/everything-claude-code/tree/main/skills
主要功能
- 表设计模式: MergeTree、ReplacingMergeTree、AggregatingMergeTree等引擎使用
- 查询优化: 高效过滤、聚合函数、窗口函数优化
- 数据插入: 批量插入、流式插入最佳实践
- 分区策略: 数据分区和索引设计
- 实时分析: 高性能实时数据处理
- 数据压缩: 列式存储和压缩优化
触发条件
在以下情况下应该调用此技能:
- 设计ClickHouse数据库表结构
- 优化分析查询性能
- 实现大规模数据聚合和统计
- 构建实时分析系统
- 处理时序数据和日志分析
- 优化数据插入和导入流程
使用场景
场景1: 构建分析仪表板
当需要为业务指标构建实时分析仪表板时,使用此技能提供的聚合模式和查询优化技术。
场景2: 日志数据分析
当需要处理和分析大量日志数据时,使用MergeTree引擎和分区策略实现高效查询。
场景3: 实时指标计算
当需要实时计算业务指标时,使用AggregatingMergeTree实现预聚合和快速查询。
处理过程
1. 表设计阶段
根据数据特性和查询需求选择合适的表引擎,设计分区键和排序键。
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;
2. 查询优化
使用索引列优先过滤、ClickHouse特定的聚合函数、窗口函数等技术优化查询性能。
3. 数据插入
使用批量插入代替单条插入,实现高效的数据写入。
4. 预聚合
对于频繁查询的聚合数据,使用AggregatingMergeTree实现物化视图。
输入要求
使用此技能时,用户需要提供:
- 数据规模和增长预期
- 查询模式和性能要求
- 数据更新和删除需求
- 分区和索引策略偏好
- 实时性要求
输出说明
技能将提供:
- 优化的表结构设计SQL
- 高性能查询语句示例
- 数据插入最佳实践代码
- 分区和索引策略建议
- 聚合函数使用指南
- 性能优化建议
使用示例
示例1: 高效过滤查询
-- ✅ GOOD: 使用索引列优先
SELECT *
FROM markets_analytics
WHERE date >= '2025-01-01'
AND market_id = 'market-123'
AND volume > 1000
ORDER BY date DESC
LIMIT 100;
-- ❌ BAD: 非索引列优先过滤
SELECT *
FROM markets_analytics
WHERE volume > 1000
AND market_name LIKE '%election%'
AND date >= '2025-01-01';
示例2: 聚合查询
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;
示例3: 批量插入
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()
}
最佳实践
- 选择合适的引擎: 根据数据特性选择MergeTree、ReplacingMergeTree或AggregatingMergeTree
- 合理分区: 按时间或其他维度分区,提高查询效率
- 优化排序键: 将常用过滤列放在排序键前面
- 批量插入: 避免单条插入,使用批量插入提高性能
- 使用特定函数: 使用uniq代替count(DISTINCT),使用quantile代替percentile
- 预聚合: 对频繁查询的聚合使用物化视图
ClickHouse特性
- 列式存储: 高效的数据压缩和查询性能
- 数据压缩: 自动数据压缩减少存储空间
- 并行查询: 多核并行查询执行
- 分布式查询: 支持分布式表查询
- 实时分析: 支持实时数据插入和查询
