ClickHouse实战：如何高效处理TB级ck+数据集

1次阅读

共计 3497 个字符，预计需要花费 9 分钟才能阅读完成。

处理 TB 级时序数据时，开发团队常遇到三个致命问题：

查询延迟高：即使建立了时间索引，传统行存数据库扫描亿级数据仍需分钟级响应，无法满足实时分析需求
存储成本爆炸：传感器数据往往包含大量重复值（如设备 ID），行存储导致冗余数据占用 3 - 5 倍物理空间
写入瓶颈：高并发写入时 B + 树索引频繁分裂，MySQL 类数据库的 TPS 很难突破 1 万 / 秒

我们曾用某云数据库处理 1.2TB IoT 设备数据，单日数据增量 30GB，简单按设备分组查询竟耗时 47 秒——直到遇见 ClickHouse。

横向对比主流时序数据处理方案：

方案	写入吞吐	压缩率	点查询延迟	复杂分析能力
ClickHouse	50 万行 / 秒	5-10x	50ms	★★★★
Elasticsearch	5 万行 / 秒	1.5-3x	10ms	★★
Druid	20 万行 / 秒	3-5x	100ms	★★★

ClickHouse 的胜出关键在于：

列式存储：单独压缩每列数据，设备 ID 这类高重复值压缩比可达 20:1
向量化引擎：利用 SIMD 指令批量处理数据，CPU 缓存命中率提升 8 倍
稀疏索引：每 8192 行一个主键标记，1TB 数据只需约 1MB 索引

我们的 ck+ 数据集按如下规则分布到 6 个分片：

-- 创建分布式表
CREATE TABLE distributed_metrics ON CLUSTER main_cluster
(
    device_id String,
    timestamp DateTime64(3),
    temperature Float32,
    voltage Float32
)
ENGINE = Distributed(
    main_cluster,   -- 集群名称
    default,        -- 数据库名
    local_metrics,  -- 本地表名
    cityHash64(device_id, toYYYYMMDD(timestamp))  -- 分片键
)

分片键设计：结合设备 ID 哈希与日期，确保同设备同天的数据位于相同分片
本地表结构：每个分片上的物理表采用分区 + 主键双重优化

-- 本地表结构（每个分片独立存储）CREATE TABLE local_metrics
(
    device_id String,
    timestamp DateTime64(3),
    temperature Float32,
    voltage Float32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (device_id, timestamp)
SETTINGS index_granularity = 8192;

index_granularity：默认 8192 适合大多数场景，若查询常精确到单个设备，可设为 4096 提升点查性能
min_bytes_for_wide_part：小于该值 (默认 10MB) 的数据块以紧凑格式存储，机械硬盘建议调大到 50MB
ttl_only_drop_parts：启用后 TTL 过期数据整块删除，避免频繁小文件合并

对高频查询的 device_id+ 分钟级聚合：

CREATE MATERIALIZED VIEW metrics_1min_mv
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (device_id, timestamp)
AS SELECT
    device_id,
    toStartOfMinute(timestamp) AS timestamp,
    avgState(temperature) AS temp_avg,
    maxState(voltage) AS voltage_max
FROM local_metrics
GROUP BY device_id, timestamp;

查询时直接命中预计算结果：

-- 原始查询（扫描 2.3 亿行）SELECT 
    device_id,
    avg(temperature) 
FROM local_metrics 
WHERE timestamp >= now() - INTERVAL 1 HOUR
GROUP BY device_id;

-- 优化后（扫描 36 万行）SELECT
    device_id,
    avgMerge(temp_avg)
FROM metrics_1min_mv
WHERE timestamp >= now() - INTERVAL 1 HOUR
GROUP BY device_id;

from clickhouse_driver import Client
import pandas as pd

# 连接配置
client = Client(
    host='ch-server1', 
    port=9000,
    user='loader',
    password='SecurePwd!',
    settings={'max_memory_usage': '20GB'}
)

# 生成模拟数据（500 万行）df = pd.DataFrame({'device_id': [f'D{i%1000}' for i in range(5_000_000)],
    'timestamp': pd.date_range('2023-01-01', periods=5_000_000, freq='s'),
    'temperature': np.random.uniform(20, 40, 5_000_000),
    'voltage': np.random.normal(220, 5, 5_000_000)
})

# 使用 native 协议高效导入
client.execute(
    "INSERT INTO local_metrics VALUES",
    df.to_dict('records'),
    types_check=True
)

EXPLAIN PIPELINE
SELECT 
    device_id,
    argMax(temperature, timestamp)
FROM local_metrics
WHERE timestamp > '2023-06-01 00:00:00'
GROUP BY device_id
SETTINGS max_threads = 8;

/* 输出示例
┌─explain─────────────────────────────────┐
│ (Expression)                            │
│ ExpressionTransform × 8                 │
│   (Aggregating)                         │
│   Resize 8 → 1                          │
│     AggregatingTransform × 8            │
│       (Expression)                      │
│       ExpressionTransform × 8           │
│         (Filter)                        │
│         FilterTransform × 8             │
│           (ReadFromMergeTree)           │
│           MergeTreeThread × 8 0 → 1     │
└─────────────────────────────────────────┘
*/

内存：每 TB 原始数据至少配置 64GB 内存（实际压缩后约 100GB）
磁盘：优先选用 NVMe SSD，建议预留 3 倍压缩后数据的空间
CPU：每个物理核心可处理 2 - 3 个查询线程

方案对比：

方案	适用场景	示例实现
字典编码	维度表 <10 万行	`WITH dictGet('device_info')`
预聚合宽表	固定分析维度	定时 ETL 生成宽表
引擎级 JOIN	大表关联	`JOIN` + `join_use_nulls=1`

-- 监控 parts 数量（超过 200 需警惕）SELECT 
    table,
    count() AS parts,
    sum(bytes_on_disk) AS disk_size
FROM system.parts
WHERE active
GROUP BY table;

-- 慢查询分析
SELECT 
    query,
    elapsed,
    read_rows,
    memory_usage
FROM system.query_log
WHERE event_date = today()
ORDER BY elapsed DESC
LIMIT 10;

在 1TB ck+ 数据集上的对比测试：