ClickHouse实战：CK+数据集的高效存储与查询优化方案

1次阅读

没有评论

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

CK+ 数据集作为典型的高维时间序列数据，具有三个显著特征：

高维度特性：通常包含 50+ 个监测指标列，每列代表不同传感器或业务指标
时间连续性：数据按固定时间间隔采集（如 1 分钟 / 条），时间戳为天然主键
稀疏分布：部分指标列存在大量空值（如夜间停机的设备指标）

这种数据结构导致原生 CSV 格式存储时出现：
– 单条记录字段数过多（宽表结构）
– 时间相邻数据具有高度相似性
– 存储空间被大量 NULL 值占用

使用未经优化的默认配置时，我们遇到以下问题：

存储膨胀：1TB 原始 CSV 导入后占用 2.3TB 磁盘空间
查询延迟：全表扫描简单聚合查询需要 8 -12 秒
写入瓶颈：批量插入速率仅 3 万行 / 秒

通过 clickhouse-client --send_logs_level=trace 分析发现主要瓶颈在于：
1. 未压缩的列数据存储
2. 缺少有效分区导致查询读取过多数据块
3. 缺乏索引导致大量无效 IO

根据列数据特征选择最佳压缩编解码器：

-- 数值型指标采用 DoubleDelta + LZ4 组合
ALTER TABLE metrics MODIFY COLUMN temperature Float32 CODEC(DoubleDelta, LZ4)

-- 低频枚举值使用 LowCardinality
ALTER TABLE metrics MODIFY COLUMN status LowCardinality(String)

-- 高精度时间戳采用 Delta + ZSTD
ALTER TABLE metrics MODIFY COLUMN timestamp DateTime64(3) CODEC(Delta(4), ZSTD(3))

关键参数说明：
– DoubleDelta：适合变化缓慢的数值序列
– ZSTD(3)：压缩级别 3 在 CPU 消耗和压缩率间取得平衡
– Delta(4)：对时间戳类递增数据特别有效

采用两级分区策略减少扫描范围：

CREATE TABLE metrics_optimized (timestamp DateTime64(3) CODEC(Delta(4), ZSTD(3)),
    device_id UInt32,
    -- 其他指标列...
) ENGINE = MergeTree()
PARTITION BY (toYYYYMM(timestamp),  -- 第一级按月分区
    device_id % 10        -- 第二级设备哈希
)
ORDER BY (device_id, timestamp)
TTL timestamp + INTERVAL 1 YEAR DELETE  -- 自动过期旧数据

设计要点：
1. 每月数据约 50GB，分区大小控制在 CH 推荐范围（10-100GB）
2. 设备哈希避免热点集中在单个分区
3. TTL 自动清理过期数据节省存储

为高频查询条件添加聚合数据索引：

ALTER TABLE metrics_optimized 
ADD INDEX idx_anomaly (is_anomaly) TYPE bloom_filter GRANULARITY 4

ALTER TABLE metrics_optimized
ADD INDEX idx_value_range (temperature) 
TYPE minmax GRANULARITY 256

索引选择策略：
– BloomFilter：适合高基数枚举字段快速过滤
– MinMax：对数值范围查询效果显著
– GRANULARITY 参数根据数据分布调整

CREATE TABLE ck_data.optimized_metrics (timestamp DateTime64(3, 'Asia/Shanghai') CODEC(Delta(4), ZSTD(3)),
    device_id UInt32 CODEC(T64, LZ4),
    temperature Decimal(5,2) CODEC(DoubleDelta, LZ4),
    pressure Float32 CODEC(Gorilla, ZSTD(2)),
    status LowCardinality(String) CODEC(ZSTD(1)),
    is_anomaly UInt8 CODEC(NONE),
    metrics_map Map(String, Float32) CODEC(ZSTD(5))
) ENGINE = MergeTree()
PARTITION BY (toYYYYMM(timestamp), device_id % 10)
ORDER BY (device_id, timestamp)
TTL timestamp + INTERVAL 6 MONTH TO DISK 'hdd1'
SETTINGS 
    index_granularity = 8192,
    min_bytes_for_wide_part = 10737418240;  -- 10GB 以上使用 Wide 格式

原始查询：

-- 未优化查询（执行时间：12.4s）SELECT 
    device_id,
    avg(temperature) 
FROM metrics
WHERE timestamp BETWEEN '2023-07-01' AND '2023-07-31'
  AND status = 'normal'
GROUP BY device_id

优化后查询：

-- 使用分区裁剪和索引（执行时间：2.1s）EXPLAIN PIPELINE
SELECT 
    device_id,
    avg(temperature)
FROM metrics_optimized
WHERE timestamp BETWEEN '2023-07-01' AND '2023-07-31'
  AND status = 'normal'
  AND device_id IN (
      SELECT device_id 
      FROM device_info 
      WHERE zone = 'east'
  )
GROUP BY device_id
SETTINGS 
    optimize_skip_unused_partitions = 1,
    allow_experimental_analyzer = 1;

EXPLAIN 分析显示：
1. 跳过 7 月份之外的所有分区
2. 使用 idx_status 索引过滤异常状态
3. 仅扫描目标分区的 27 个数据块（原方案扫描 312 块）

使用相同 10 亿条测试数据集：

指标	原始方案	优化方案	提升幅度
存储空间	2.3TB	1.6TB	-30%
查询延迟(P99)	11.2s	1.8s	5.2x
写入吞吐量	28K 行 /s	75K 行 /s	2.7x
备份时间	4.5h	2.8h	-38%

常见问题 1 ：分区粒度过细
– ❌ 错误：按天分区产生数万小文件
– ✅ 解决：调整到月分区 + 哈希，控制每个表约 200 分区

常见问题 2 ：索引过度使用
– ❌ 错误：为所有列添加跳数索引
– ✅ 解决：只对高频过滤列建索引，监控 system.query_log 确认索引命中率

常见问题 3 ：CODEC 选择不当
– ❌ 错误：对随机数据使用 Delta 编码
– ✅ 解决：先用SELECT column, entropy(column) FROM system.parts_columns WHERE table = 'metrics' GROUP BY column 分析数据熵值