ClickHouse(ck)数据集介绍：从原理到实战的数据分析指南

1次阅读

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

在 PB 级数据分析场景下，传统行式数据库（如 MySQL、PostgreSQL）面临三个核心问题：

I/ O 效率低下 ：全表扫描需读取所有列数据，即使只需查询少数列
压缩率不足 ：行式存储难以应用列级压缩算法（平均压缩比仅 2 - 3 倍）
CPU 利用率低 ：无法有效利用现代 CPU 的 SIMD 指令集（单指令多数据流）

实测环境（AWS r5.2xlarge, 100GB TPC- H 数据集）：
– MySQL 8.0：Q6 分组聚合查询耗时 47 秒
– ClickHouse 23.8：相同查询仅需 0.8 秒

行式存储 (Row-oriented)
数据按行连续存储：[Row1Col1,Row1Col2,Row1Col3,Row2Col1...]
适合 OLTP 场景（高并发点查询）
列式存储 (Column-oriented)
数据按列连续存储：[Col1Val1,Col1Val2,...], [Col2Val1,Col2Val2,...]
适合 OLAP 场景（全列扫描 / 聚合）

指标	行式存储	列式存储
压缩比	2-3x	5-10x
扫描 100 万行耗时	1200ms	200ms
更新效率	高	低
点查询延迟	<1ms	5-10ms

数据写入流程
写入先存入内存缓冲区 (MemTable)
达到阈值后刷盘为不可变数据段 (Immutable Part)
后台线程定期合并小 Part（默认 15 分钟）

合并策略示例

CREATE TABLE sales (
    date Date,
    product_id UInt32,
    amount Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (product_id, date)
SETTINGS index_granularity = 8192;

利用 CPU 的 AVX-512 指令集，单次处理 512 位数据（16 个 float32）
查询执行流程：
将列数据加载到 CPU 寄存器
应用 SIMD 指令批量计算
写回结果内存区域

from clickhouse_driver import Client
import pandas as pd

client = Client('localhost')

def batch_insert(csv_path, batch_size=100000):
    df_iter = pd.read_csv(csv_path, chunksize=batch_size)
    for chunk in df_iter:
        client.execute(
            'INSERT INTO sales VALUES',
            chunk.to_dict('records'),
            types_check=True
        )

-- 原始查询（耗时 1.2 秒）SELECT 
    product_id,
    sum(amount) 
FROM sales 
WHERE date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY product_id;

-- 创建物化视图（查询降至 80ms）CREATE MATERIALIZED VIEW sales_daily_mv
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (product_id, date)
AS SELECT
    date,
    product_id,
    sum(amount) AS amount
FROM sales
GROUP BY date, product_id;

时间维度优先 ：99% 场景建议按日 / 月分区
控制分片大小 ：单个分区 10-100GB 为宜
避免高频更新 ：选择低频变更的列

-- 错误示例（导致分区修剪失效）ALTER TABLE sales ADD CONSTRAINT chk_date 
CHECK date > '2020-01-01';

-- 正确做法（使用 PARTITION BY 表达式过滤）CREATE TABLE sales (date Date) ENGINE = MergeTree()
PARTITION BY 
    if(date > '2020-01-01', 'valid', 'invalid');

Star Schema Benchmark(SSB) 测试结果（ClickHouse 23.8 vs PostgreSQL 15）