ClickHouse 与 OLAP

#数据库 · #ClickHouse · #OLAP · #列式存储 · #向量化 · #物化视图 · #MergeTree

ClickHouse 是面向 OLAP 场景的列式数据库，以极快的查询速度著称——单表查询可达每秒数亿行。其列式存储、向量化执行、数据压缩和物化视图等设计，使其成为日志分析、实时报表等场景的首选。

为什么 ClickHouse 这么快

优化	原理	提升
列式存储	查询只读需要的列	10-100x I/O 减少
向量化执行	一批数据一起处理（SIMD）	4-8x
数据压缩	同类数据压缩率高（LZ4/ZSTD）	3-10x I/O 减少
MergeTree 引擎	按主键排序 + 稀疏索引	秒级范围查询
无锁并发	无事务冲突，读不阻塞写	高吞吐

列式存储 vs 行式存储

行式存储（MySQL）：
Row 1: [id=1, name=Alice, age=25, city=Beijing]
Row 2: [id=2, name=Bob,   age=30, city=Shanghai]
Row 3: [id=3, name=Carol, age=28, city=Beijing]
→ 扫描 age 列也要读整行 ❌

列式存储（ClickHouse）：
age:   [25, 30, 28]      ← 查询 age > 26 只读这 3 个值
name:  [Alice, Bob, Carol]
city:  [Beijing, Shanghai, Beijing]
→ 每列独立存储，只读需要的列 ✅

MergeTree 引擎

表结构

sql

CREATE TABLE events (
    event_date  Date,
    event_time  DateTime,
    user_id     UInt64,
    event_type  String,
    properties  String,
    INDEX idx_event_type event_type TYPE bloom_filter GRANULARITY 4
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, user_id, event_time)
PRIMARY KEY (event_type, user_id)
SETTINGS index_granularity = 8192;

核心概念

Partition（分区）：
  event_date=202401 → 一个独立目录
  event_date=202402 → 另一个独立目录

Granule（颗粒）：
  每 8192 行（默认）为一个 granule（最小不可分割的数据块）

稀疏索引（Sparse Index）：
  每 N 个 granule 记录一次主键值
  → 内存占用极小（< 1%）
  → 通过二分查找快速定位 granule

写入：LSM-like 机制

写入 → 内存排序 → 持久化 Part → 后台 Merge
        ↑
    (INSERT 进来先排序，保证 Part 内有序)

多个 Part：
  Part_202401_1_1 (1M 行)
  Part_202401_2_2 (500K 行)  ← 小 Part
  Part_202401_3_3 (200K 行)  ← 小 Part

后台 Merge：合并同分区的小 Part → 大 Part

查询示例

聚合分析

sql

-- 每小时事件数（含去重）
SELECT
    toStartOfHour(event_time) AS hour,
    event_type,
    count() AS cnt,
    uniq(user_id) AS uv
FROM events
WHERE event_date >= '2024-01-01'
  AND event_type = 'click'
GROUP BY hour, event_type
ORDER BY hour;

-- 漏斗分析
SELECT
    user_id,
    windowFunnel(3600)(event_time,
        event_type = 'view',
        event_type = 'cart',
        event_type = 'pay'
    ) AS funnel_level
FROM events
WHERE event_date = today()
GROUP BY user_id;

聚合函数

函数	用途
`uniq(x)`	近似去重计数（HyperLogLog）
`uniqExact(x)`	精确去重计数
`quantile(0.95)(x)`	P95 分位数
`argMax(x, y)`	y 最大时 x 的值
`topK(10)(x)`	TopK 值
`groupArray(x)`	将列转为数组
`windowFunnel`	漏斗分析

物化视图

物化视图将查询结果预先计算并存储，查询时直接读取结果。

sql

-- 创建物化视图：每小时自动聚合
CREATE MATERIALIZED VIEW events_hourly
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, event_type)
AS SELECT
    toStartOfHour(event_time) AS hour,
    event_type,
    count() AS cnt,
    uniqState(user_id) AS uv_state  -- 中间状态，不直接可读
FROM events
GROUP BY hour, event_type;

-- 查询物化视图
SELECT
    hour,
    event_type,
    sum(cnt) AS cnt,  -- SummingMergeTree 自动合并
    uniqMerge(uv_state) AS uv
FROM events_hourly
WHERE hour > now() - INTERVAL 7 DAY
GROUP BY hour, event_type;

物化视图项目日志

sql

-- 1. 消费 Kafka 消息
CREATE TABLE kafka_events (
    event_time DateTime,
    user_id UInt64,
    event_type String
) ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'localhost:9092',
    kafka_topic_list = 'events',
    kafka_group_name = 'clickhouse',
    kafka_format = 'JSONEachRow';

-- 2. 物化视图将数据写入 MergeTree
CREATE MATERIALIZED VIEW events_mv TO events AS
SELECT * FROM kafka_events;

数据类型

sql

-- 数值
UInt8/16/32/64, Int8/16/32/64
Float32, Float64
Decimal(P, S)  -- 精确小数

-- 字符串
String        -- 任意长度
FixedString(N) -- 定长

-- 日期时间
Date          -- 天
DateTime      -- 秒
DateTime64(3) -- 毫秒

-- 特殊
LowCardinality(String)  -- 低基数字符串枚举（自动字典编码）
Array(T)                -- 数组
Nullable(T)             -- 允许 NULL
Tuple(T1, T2, ...)      -- 元组
Nested(name1 Type1, ...) -- 嵌套列

常见优化

1. 建表优化

sql

-- 好的设计
ORDER BY (high_card_col, low_card_col)   -- 高基数列在前
PARTITION BY toYYYYMM(date_col)          -- 按月分区
SETTINGS index_granularity = 8192        -- 默认即可

2. 查询优化

sql

-- ❌ 不好：全表扫描
SELECT * FROM events WHERE toDate(event_time) = '2024-01-01'

-- ✅ 好：利用分区裁剪
SELECT * FROM events WHERE event_date = '2024-01-01'

-- ❌ 不好：NOT IN / != 无法用索引
SELECT * FROM events WHERE event_type != 'click'

-- ✅ 好
SELECT * FROM events WHERE event_type IN ('view', 'cart', 'pay')

3. 写入优化

sql

-- 批量写入（避免小批量）
INSERT INTO events VALUES
  (...), (...), (...),  -- 5000-10000 行一批

-- 异步插入（合并小写入）
SET async_insert = 1;

MergeTree 引擎家族对比

引擎	用途	特点
MergeTree	通用基础引擎	按主键排序、分区、稀疏索引
ReplacingMergeTree	去重	按 ORDER BY key 去重，保留最新版本
SummingMergeTree	预聚合求和	自动合并数值列，适合报表
AggregatingMergeTree	预聚合任意函数	配合物化视图做增量聚合
CollapsingMergeTree	折叠更新	用 Sign 列标记增删，异步折叠
VersionedCollapsingMergeTree	版本化折叠	多版本折叠，更安全
GraphiteMergeTree	时序降精度	自动按时间衰减精度

ReplacingMergeTree 示例

sql

CREATE TABLE users (
    id UInt64,
    name String,
    email String,
    updated_at DateTime
) ENGINE = ReplacingMergeTree(updated_at)  -- 按 updated_at 保留最新
ORDER BY id;

-- 插入重复 id 的数据
INSERT INTO users VALUES (1, 'Alice', 'alice@old.com', '2024-01-01');
INSERT INTO users VALUES (1, 'Alice', 'alice@new.com', '2024-06-01');

-- 查询（可能在 Merge 前返回两条）
SELECT * FROM users FINAL;  -- FINAL 强制去重，返回最新的

SummingMergeTree 物化视图组合

sql

-- 明细表
CREATE TABLE orders (
    date Date, product_id UInt64, amount Float64
) ENGINE = MergeTree() ORDER BY (date, product_id);

-- 聚合物化视图
CREATE MATERIALIZED VIEW orders_daily
ENGINE = SummingMergeTree()
ORDER BY (date, product_id)
AS SELECT date, product_id, sum(amount) AS total_amount
FROM orders GROUP BY date, product_id;

-- 查询聚合结果
SELECT sum(total_amount) FROM orders_daily WHERE date = today();

数据 TTL 管理

sql

-- 列 TTL：3 天后将 amount 列清零
CREATE TABLE events (
    date Date,
    amount Float64 TTL date + INTERVAL 3 DAY
) ENGINE = MergeTree() ORDER BY date;

-- 表 TTL：30 天后删除整行
CREATE TABLE logs (
    timestamp DateTime,
    message String
) ENGINE = MergeTree()
ORDER BY timestamp
TTL timestamp + INTERVAL 30 DAY;

-- 分区 TTL：按分区删除（更高效）
ALTER TABLE events
MODIFY TTL date + INTERVAL 90 DAY;

性能调优参数

sql

-- 索引粒度（默认 8192，数据量极大时可增大到 16384）
SETTINGS index_granularity = 16384;

-- 压缩算法（默认 LZ4，可选 ZSTD 更高压缩率）
SETTINGS compression_codec = 'ZSTD(3)';

-- 后台 Merge 线程数
SETTINGS max_bytes_to_merge_at_max_space_in_pool = 161061273600;

-- 查询优化
-- max_threads: 查询并行度（默认为 CPU 核数）
SELECT ... SETTINGS max_threads = 4;

-- optimize_read_in_order: 主键顺序读优化
SETTINGS optimize_read_in_order = 1;

分布式部署

xml

<!-- config.xml -->
<remote_servers>
    <my_cluster>
        <shard>
            <replica>node1:9000</replica>
            <replica>node2:9000</replica>
        </shard>
        <shard>
            <replica>node3:9000</replica>
            <replica>node4:9000</replica>
        </shard>
    </my_cluster>
</remote_servers>

sql

-- 分布式表
CREATE TABLE events_distributed AS events
ENGINE = Distributed(my_cluster, default, events, rand());

-- 查询分布式表 → 自动路由到各 shard
SELECT count() FROM events_distributed;

ClickHouse vs Elasticsearch vs MySQL OLAP

mermaid

flowchart LR
    subgraph OLAP_Scene["OLAP 分析场景"]
        A["用户行为日志<br/>100TB, 10亿行/天"]
    end

    subgraph Decisions["引擎选型"]
        B["ClickHouse: 列存 + 向量化<br/>聚合查询 100倍于MySQL<br/>物化视图预聚合"]
        C["Elasticsearch: 倒排索引<br/>全文搜索 + 日志检索<br/>近实时 Kibana 可视化"]
        D["MySQL: 行存 + B-Tree<br/>OLTP 事务 + 点查<br/>不适合大数据量聚合"]
    end

列式存储 vs 行式存储 — I/O 对比

这是 ClickHouse 快的根本原因——不是"它代码写得好"，而是存储结构决定了 I/O 量：

mermaid

flowchart TB
    subgraph RowStore["行式存储 (MySQL InnoDB)"]
        R1["行1: Alice | 25 | Beijing | 2024-01-01"]
        R2["行2: Bob   | 30 | Shanghai| 2024-01-02"]
        R3["行3: Carol | 28 | Beijing | 2024-01-03"]

        R_Query["SELECT AVG(age) FROM users<br/>需要读所有行的 age 字段<br/>但整行都必须加载!<br/>→ 读入无用数据 I/O 浪费 90%"]
    end

    subgraph ColStore["列式存储 (ClickHouse MergeTree)"]
        C1["列 age:  [25, 30,  28, ... ]"]
        C2["列 name: [Alice, Bob, Carol, ...]"]
        C3["列 city: [Beijing, Shanghai, ...]"]
        C4["列 date: [2024-01-01, ...]"]

        C_Query["SELECT AVG(age) FROM users<br/>只需读 age 列!<br/>→ 精确 I/O, 不浪费一个字节"]
    end

    RowStore -->|"为什么快?"| ColStore

维度	行式存储 (MySQL)	列式存储 (ClickHouse)
`SELECT AVG(age)`	读所有列 → 90% I/O 浪费	只读 age 列 → 精确
`SELECT * WHERE id=1`	✅ 高效（一行全字段）	🟡 需读多列
压缩率	2-3x	5-10x（同列值相似）
向量化执行	❌ 逐行处理	✅ SIMD 批量处理

MergeTree 家族 — 什么时候用哪张表

sql

-- MergeTree: 基础款，允许重复
CREATE TABLE events (...) ENGINE = MergeTree()
ORDER BY (event_time, user_id);
-- 适用：原始日志、需要全量明细的场景

-- ReplacingMergeTree: 去重版，按 ORDER BY 键去重
CREATE TABLE users (...) ENGINE = ReplacingMergeTree(version)
ORDER BY user_id;
-- 适用：可更新的维表（只保留最新 version）

-- SummingMergeTree: 累加版，数值列自动 SUM
CREATE TABLE sales (...) ENGINE = SummingMergeTree()
ORDER BY (date, product_id);
-- 适用：需要 SUM 聚合的流水（订单金额、PV）

-- AggregatingMergeTree: 通用聚合版，配合 -State/-Merge 函数
CREATE TABLE stats (...) ENGINE = AggregatingMergeTree()
ORDER BY (date, channel);
-- 适用：复杂聚合指标（UV、漏斗、留存）

-- CollapsingMergeTree: 折叠版（通过 sign 字段标记增/删）
CREATE TABLE ops (...) ENGINE = CollapsingMergeTree(sign)
ORDER BY (id);
-- 适用：需要 UPDATE/DELETE 语义的变更流

选型速查：

需求	推荐引擎
原始明细 + 不关心去重	MergeTree
需要 UPDATE/DELETE	ReplacingMergeTree 或 CollapsingMergeTree
物化视图聚合结果	SummingMergeTree / AggregatingMergeTree
实时 Kafka 消费	Kafka Engine + MaterializedView
仅存 N 天，过期自动删	MergeTree + TTL

工程实践：MergeTree 写入流程与排障

1. MergeTree Part/Granule/Mark/Sparse Index 结构

这是 ClickHouse 高性能的核心：

mermaid

flowchart TD
    subgraph "物理层: Part"
        P1["Part 0<br/>(冷数据, 已合并)"]
        P2["Part 1<br/>(热数据, 活跃写入)"]
        P3["Part 2<br/>(新写入, 等待合并)"]
    end

    subgraph "Part 内部结构"
        P2 --> G["Granule<br/>(最小读取单元, 8192行)"]
        P2 --> MI["Mark Range<br/>每个列文件每 8192 行一个 mark<br/>存: 文件偏移量"]
        P2 --> SI["Sparse Primary Index<br/>每 8192 行一个索引项<br/>按主键排序"]
    end

    subgraph "查询路径"
        Q["SELECT ... WHERE key=x"] --> SI
        SI -->|"定位到目标 granule"| MI
        MI -->|"从 .bin 文件读取对应位置"| G
    end

text

MergeTree 写入流程:
  1. INSERT 数据 → 生成新的 Part (按主键排序)
  2. Part 目录结构:
     primary.idx    ← 稀疏主键索引 (每 8192 行一个 key)
     columns.txt    ← 列名和类型
     data.bin       ← 列数据 (按列存储)
     data.mrk3      ← Mark 文件: 每个 granule 的文件偏移和行数

  3. 后台 Merge: 多个小 Part → 合并为大 Part (减少文件数)
  4. 后台 Mutation: ALTER DELETE/UPDATE 的数据重写

2. ClickHouse vs MySQL vs Elasticsearch vs Druid 横向对比

维度	ClickHouse	MySQL (InnoDB)	Elasticsearch	Apache Druid
存储引擎	MergeTree (列存)	B+Tree (行存)	倒排索引 + Doc Values (列存)	Segment (列存)
写入模式	批量追加 (不可单行更新)	随机写 (可单行更新)	近实时 (1s refresh)	实时摄入 + 批量
压缩率	极高 (10-30×)	低 (2-3×)	中 (5-10×)	高 (10-20×)
聚合查询	⭐⭐⭐⭐⭐ (向量化)	⭐⭐	⭐⭐⭐	⭐⭐⭐⭐
点查	⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐
全文搜索	⭐ (不擅长)	⭐⭐ (LIKE)	⭐⭐⭐⭐⭐ (倒排)	⭐ (不擅长)
更新	❌ 不支持单行	✅ 完美	✅ (但内部标记删除)	❌ 不可变
SQL 能力	标准 SQL (大部分)	标准 SQL	DSL (有限 SQL)	类 SQL
适合	实时分析/报表/日志分析	OLTP 交易	全文搜索/日志检索	实时 OLAP/广告/指标

3. 常见问题排查

大查询慢

sql

-- 1. 看查询处理阶段
EXPLAIN PIPELINE SELECT ...;

-- 2. 看哪些分片/data part 被扫描
EXPLAIN indexes=1 SELECT ...;

-- 3. 看当前运行的查询
SELECT query_id, elapsed, read_rows, memory_usage
FROM system.processes;

-- 4. kill 长查询
KILL QUERY WHERE query_id = 'xxx';

原因	表现	修复
没有命中主键索引	扫描所有 parts	加 WHERE 主键条件
MatView 过多	写入时触发大量物化视图	减少不必要的 MV
JOIN 过大	右表无法放入内存	用字典表代替小表 JOIN
排序/聚合内存不足	`Memory limit exceeded`	调大 `max_bytes_before_external_sort`

Part 过多

sql

-- 看 Part 数量
SELECT database, table, active_parts
FROM system.parts
WHERE active
GROUP BY database, table;

-- 如果 active_parts > 1000 → Too many parts 错误
-- 原因: 频繁小批量 INSERT
-- 修复: 批量写入 (每次 10000+ 行) 或降低 insert_quorum

内存爆

text

常见内存大户:
  - 大 GROUP BY (hash table 在内存中)
  - 大 JOIN (右表 hash table)
  - arrayJoin 展开大数组
  - 大量 concurrent queries

修复:
  SET max_memory_usage = 10G;  -- 单查询内存上限
  SET max_bytes_before_external_group_by = 2G;  -- 溢出到磁盘

物化视图实战

物化视图（Materialized View）是 ClickHouse 在写入时自动预聚合的机制——数据写入原表时，自动触发视图的 SELECT 计算。

sql

-- 原表：存储原始事件数据
CREATE TABLE events (
    event_time DateTime,
    event_type String,
    user_id    UInt64,
    amount     Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time);

-- 物化视图：按分钟聚合统计
CREATE MATERIALIZED VIEW events_per_minute
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time)
AS
SELECT
    event_type,
    toStartOfMinute(event_time) AS event_time,
    count() AS cnt,
    sum(amount) AS total_amount
FROM events
GROUP BY event_type, event_time;

-- 查询时：直接查物化视图（已预聚合，数据量是原表的 1/1000）
SELECT event_type, sum(cnt), sum(total_amount)
FROM events_per_minute
WHERE event_time >= today() - 7
GROUP BY event_type;

mermaid

flowchart LR
    A["INSERT INTO events<br/>原始数据"] --> B["MergeTree<br/>events 表"]
    B --> C["物化视图触发"]
    C --> D["events_per_minute<br/>SummingMergeTree<br/>自动聚合"]
    D --> E["查询物化视图<br/>速度提升 100-1000×"]

text

物化视图的代价:
  - 写入放大: 每次 INSERT 需要同时写原表和所有物化视图
  - 延迟: 物化视图是触发器模式 → 写入后立即可查（与流计算不同，不需要等待）
  - 内存: SummingMergeTree 合并时需要额外内存

物化视图 vs 实时聚合 vs 流计算:
  物化视图: 写入时触发，查询预聚合结果 → 适合固定维度的 Dashboard
  AggregatingMergeTree: 写入原始数据，查询时按需聚合 → 适合灵活分析
  流计算 (Flink): 独立计算引擎，结果下沉到 Redis/ClickHouse → 适合复杂业务逻辑

EXPLAIN 分析:
  EXPLAIN SELECT ... FROM events_per_minute WHERE ...
  输出: Selected 1000 parts → 只扫描少量分区 ✅
  vs 直接查 events 表: Selected 1000000 parts → 全表扫描 ❌

ClickHouse 与 OLAP ​

为什么 ClickHouse 这么快 ​

列式存储 vs 行式存储 ​

MergeTree 引擎 ​

表结构 ​

核心概念 ​

写入：LSM-like 机制 ​

查询示例 ​

聚合分析 ​

聚合函数 ​

物化视图 ​

物化视图项目日志 ​

数据类型 ​

常见优化 ​

1. 建表优化 ​

2. 查询优化 ​

3. 写入优化 ​

MergeTree 引擎家族对比 ​

ReplacingMergeTree 示例 ​

SummingMergeTree 物化视图组合 ​

数据 TTL 管理 ​

性能调优参数 ​

分布式部署 ​

ClickHouse vs Elasticsearch vs MySQL OLAP ​

列式存储 vs 行式存储 — I/O 对比 ​

MergeTree 家族 — 什么时候用哪张表 ​

工程实践：MergeTree 写入流程与排障 ​

1. MergeTree Part/Granule/Mark/Sparse Index 结构 ​

2. ClickHouse vs MySQL vs Elasticsearch vs Druid 横向对比 ​

3. 常见问题排查 ​

大查询慢 ​

Part 过多 ​

内存爆 ​

物化视图实战 ​

💬 文章评论

ClickHouse 与 OLAP

为什么 ClickHouse 这么快

列式存储 vs 行式存储

MergeTree 引擎

表结构

核心概念

写入：LSM-like 机制

查询示例

聚合分析

聚合函数

物化视图

物化视图项目日志

数据类型

常见优化

1. 建表优化

2. 查询优化

3. 写入优化

MergeTree 引擎家族对比

ReplacingMergeTree 示例

SummingMergeTree 物化视图组合

数据 TTL 管理

性能调优参数

分布式部署

ClickHouse vs Elasticsearch vs MySQL OLAP

列式存储 vs 行式存储 — I/O 对比

MergeTree 家族 — 什么时候用哪张表

工程实践：MergeTree 写入流程与排障

1. MergeTree Part/Granule/Mark/Sparse Index 结构

2. ClickHouse vs MySQL vs Elasticsearch vs Druid 横向对比

3. 常见问题排查

大查询慢

Part 过多

内存爆

物化视图实战