ClickHouse 列式OLAP数据库

ClickHouse 是一个高性能、面向列的 SQL 数据库管理系统（DBMS），专为在线分析处理（OLAP）设计。它以极快的查询速度著称，能够在亚秒级时间内处理数十亿行数据的聚合查询。

ClickHouse 概述

什么是 ClickHouse？

ClickHouse 最初由俄罗斯搜索引擎公司 Yandex 开发，用于处理海量数据分析和报表需求。2016 年开源后，迅速成为数据分析领域最受欢迎的 OLAP 数据库之一。

ClickHouse 的核心设计目标：

1. 极致性能：在亚秒级时间内处理数十亿行数据的查询
2. 列式存储：数据按列存储，优化分析查询性能
3. SQL 兼容：支持标准 SQL 语法，降低学习成本
4. 线性扩展：支持水平扩展，处理 PB 级数据
5. 实时更新：支持数据实时写入和查询

OLAP vs OLTP

理解 OLAP（在线分析处理）与 OLTP（在线事务处理）的区别，有助于正确选择数据库：

维度	OLAP（ClickHouse）	OLTP（MySQL、PostgreSQL）
查询类型	复杂聚合、分析查询	简单增删改查
数据量	十亿级行，TB/PB 级	百万级行，GB/TB 级
响应时间	毫秒到秒级	毫秒级
写入模式	批量写入为主	随机写入
更新频率	低（追加写入为主）	高（频繁更新）
典型场景	数据分析、报表、BI	业务系统、交易系统

行式存储 vs 列式存储

ClickHouse 采用列式存储，这是其高性能的关键原因之一：

行式存储：

数据按行存储，同一行的所有列值连续存储在一起。适合 OLTP 场景，因为查询通常只涉及少量行，可以快速获取完整行数据。

行式存储布局：
┌─────────────────────────────────────────┐
│ Row 1: [ID=1, Name='Alice', Age=25]     │
│ Row 2: [ID=2, Name='Bob', Age=30]       │
│ Row 3: [ID=3, Name='Charlie', Age=35]   │
└─────────────────────────────────────────┘

列式存储：

数据按列存储，同一列的所有值连续存储在一起。适合 OLAP 场景，因为分析查询通常只涉及少数列，可以只读取需要的列，避免不必要的 I/O。

列式存储布局：
┌─────────────────────────────────────────┐
│ ID 列:   [1, 2, 3, 4, 5, ...]           │
│ Name 列: ['Alice', 'Bob', 'Charlie'...] │
│ Age 列:  [25, 30, 35, 28, 32, ...]      │
└─────────────────────────────────────────┘

性能差异示例：

假设查询 SELECT AVG(age) FROM users WHERE city = 'Beijing'，表有 1 亿行、100 列：

• 行式存储：需要读取所有列的数据（即使只用 2 列），I/O 开销巨大
• 列式存储：只读取 age 和 city 两列，I/O 减少 98%

ClickHouse 为什么快？

ClickHouse 的高性能源于多个技术的组合：

1. 列式存储与压缩

列式存储使得同类数据连续存储，压缩效率极高。ClickHouse 支持多种压缩算法：

• LZ4：快速压缩，适合热数据
• ZSTD：高压缩比，适合冷数据
• Delta、Gorilla：针对数值和时间序列的专用压缩

2. 向量化执行

ClickHouse 利用 CPU 的 SIMD（单指令多数据）指令集，一次处理多条数据：

-- 传统执行：逐行处理
for row in rows:
    result = row.col1 + row.col2

-- 向量化执行：批量处理
for batch in batches:
    result_vector = cpu_simd_add(batch.col1, batch.col2)

向量化执行可以减少函数调用开销，提高 CPU 缓存命中率。

3. 稀疏索引

ClickHouse 使用稀疏索引，而非传统的 B+ 树索引：

• 索引粒度（index_granularity）默认 8192 行
• 每个索引项指向一个数据块（Granule）
• 索引占用内存极小，可常驻内存

稀疏索引示例：
┌─────────────────────────────────────────┐
│ 索引项    数据块（8192行）              │
│ [1]    → Granule 1 (行 1-8192)         │
│ [8193] → Granule 2 (行 8193-16384)     │
│ [16385]→ Granule 3 (行 16385-24576)    │
└─────────────────────────────────────────┘

4. LSM-Tree 架构

ClickHouse 的 MergeTree 引擎基于 LSM-Tree 思想：

• 写入：追加写入新数据部分（Part），速度快
• 合并：后台异步合并小 Part 为大 Part
• 读取：合并多个 Part 的查询结果

这种设计使写入和查询分离，两者互不影响。

5. 多级并行

• 查询级并行：一个查询在多个线程上并行执行
• 数据级并行：数据分片存储在多个节点
• I/O 并行：并行读取多个数据文件

适用场景

适合 ClickHouse 的场景：

场景	说明
日志分析	服务器日志、应用日志、访问日志分析
用户行为分析	网站访问、App 行为、用户画像
实时监控	系统监控、业务监控、告警分析
数据仓库	构建实时数仓、维度建模
时序数据	IoT 数据、传感器数据、监控指标
广告分析	广告投放效果、流量分析

不适合 ClickHouse 的场景：

• 需要频繁 UPDATE/DELETE 的场景
• 需要事务支持（ACID）的场景
• 高并发的点查询场景
• 需要复杂 JOIN 的场景

ClickHouse 版本演进

版本	时间	重要特性
ClickHouse 1.x	2016-2019	列式存储、MergeTree 引擎
ClickHouse 20.x	2020	Projection、多线程 JOIN
ClickHouse 21.x	2021	Window 函数、JSON 支持
ClickHouse 22.x	2022	字典优化、并行复制
ClickHouse 23.x	2023	Lightweight DELETE、Vector Search
ClickHouse 24.x	2024	JSON类型Beta、BFloat16数据类型、主键索引缓存、降序排序键支持
ClickHouse 25.x	2025	增强的向量化执行、更好的云原生支持、Iceberg REST Catalog集成

ClickHouse 24.x 重要更新：

1. JSON 类型正式进入 Beta：支持动态 JSON 数据存储和查询，可从 String 类型直接 ALTER 转换
2. BFloat16 数据类型：16位浮点数，适合机器学习场景
3. 主键索引缓存：支持按需加载主键索引，大幅降低大表内存占用
4. 降序排序键：MergeTree 支持 ORDER BY (time DESC, key)，优化 TopN 查询
5. JOIN 优化：自动 JOIN 重排序、并行 Hash JOIN 默认启用
6. Iceberg 集成：支持 Iceberg REST Catalog，Schema Evolution

架构设计

整体架构

ClickHouse 采用分布式、无共享（Shared-Nothing）架构：

┌─────────────────────────────────────────────────────────────────────┐
│                           Client                                      │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐                  │
│  │ CLI         │  │ JDBC/ODBC   │  │ HTTP API    │                  │
│  └──────┬──────┘  └──────┬──────┘  └──────┬──────┘                  │
└─────────┼────────────────┼────────────────┼──────────────────────────┘
          │                │                │
          ▼                ▼                ▼
┌─────────────────────────────────────────────────────────────────────┐
│                        ClickHouse Server                              │
│  ┌─────────────────────────────────────────────────────────────┐    │
│  │                    Query Pipeline                            │    │
│  │  Parser → Analyzer → Optimizer → Planner → Executor         │    │
│  └─────────────────────────────────────────────────────────────┘    │
│  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐      │
│  │ Table Engine    │  │ Storage Layer   │  │ Cache           │      │
│  │ (MergeTree)     │  │ (Part Manager)  │  │ (Mark Cache)    │      │
│  └─────────────────┘  └─────────────────┘  └─────────────────┘      │
└─────────────────────────────────────────────────────────────────────┘
          │                │                │
          ▼                ▼                ▼
┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐
│  Node 1         │  │  Node 2         │  │  Node 3         │
│  (Replica 1)    │  │  (Replica 2)    │  │  (Replica 3)    │
│ ┌─────────────┐ │  │ ┌─────────────┐ │  │ ┌─────────────┐ │
│ │ Partition 1 │ │  │ │ Partition 1 │ │  │ │ Partition 2 │ │
│ │ Partition 2 │ │  │ │ Partition 2 │ │  │ │ Partition 3 │ │
│ └─────────────┘ │  │ └─────────────┘ │  │ └─────────────┘ │
└─────────────────┘  └─────────────────┘  └─────────────────┘
          │                │                │
          └────────────────┼────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────────┐
│                        ZooKeeper                                     │
│  ┌─────────────────────────────────────────────────────────────┐    │
│  │ - 集群元数据管理                                              │    │
│  │ - 副本同步协调                                                │    │
│  │ - 分布式 DDL 执行                                             │    │
│  └─────────────────────────────────────────────────────────────┘    │
└─────────────────────────────────────────────────────────────────────┘

查询执行流程

ClickHouse 查询执行流程：

SQL 字符串
    ↓
Parser（解析器）
    ↓
AST（抽象语法树）
    ↓
Analyzer（语义分析器）→ 检查表、列存在性，解析函数
    ↓
Optimizer（优化器）→ 谓词下推、列裁剪、常量折叠
    ↓
Query Plan（执行计划）
    ↓
Pipeline（执行管道）→ 构建 Processor 链
    ↓
Executor（执行器）→ 多线程并行执行
    ↓
Result（结果）

数据组织结构

ClickHouse 数据按层次组织：

1. Table（表）

逻辑上的数据集合，由多个 Partition 组成。

2. Partition（分区）

按分区键划分的数据子集。每个分区对应一个目录。

/user_events/
├── 202401/           # 分区目录
│   ├── all_1_1_0/    # 数据部分（Part）
│   ├── all_2_2_0/
│   └── ...
├── 202402/
└── ...

3. Part（数据部分）

实际存储数据的单元，每次 INSERT 生成一个新的 Part。后台合并线程会将小 Part 合并为大 Part。

4. Column（列）

每个列存储为独立的文件：

all_1_1_0/
├── user_id.bin      # 列数据文件
├── user_id.mrk      # 标记文件（索引）
├── event_time.bin
├── event_time.mrk
├── event_type.bin
├── event_type.mrk
└── primary.idx      # 主键索引

5. Granule（颗粒）

数据读取的最小单位，默认 8192 行。一个 Granule 内的数据连续存储，利用 CPU 缓存。

MergeTree 合并机制

MergeTree 的核心是后台合并机制：

写入流程：
INSERT → 生成新 Part（磁盘顺序写入）→ 返回成功

后台合并：
Part1 + Part2 + Part3 → 合并 → Part_merged
        ↓
      删除旧 Part

合并触发条件：
1. Part 数量超过阈值
2. Part 总大小超过阈值
3. Part 存在时间超过阈值

合并的好处：

1. 减少文件数量：小文件合并为大文件，减少文件句柄
2. 优化查询性能：数据更紧凑，减少 I/O
3. 清理过期数据：支持 TTL 自动清理
4. 去重：ReplacingMergeTree 等引擎可在合并时去重

表引擎家族

ClickHouse 提供多种表引擎，MergeTree 家族是最核心的存储引擎。

MergeTree 引擎概述

MergeTree 家族引擎共同特点：

• 支持主键索引
• 支持分区
• 支持二级索引（数据跳过索引）
• 支持 TTL
• 支持数据压缩

MergeTree（基础引擎）

MergeTree 是最基础的表引擎，适用于大多数场景：

CREATE TABLE events (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    event_data String,
    platform LowCardinality(String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)           -- 按月分区
ORDER BY (event_time, user_id)              -- 排序键（决定物理存储顺序）
PRIMARY KEY (event_time, user_id)           -- 主键（默认与排序键相同）
SETTINGS 
    index_granularity = 8192,               -- 索引粒度
    min_bytes_for_wide_part = '10M';        -- 宽格式阈值

-- 插入数据
INSERT INTO events VALUES 
    ('2024-01-15 10:00:00', 1001, 'click', '{"page": "home"}', 'web'),
    ('2024-01-15 10:01:00', 1002, 'view', '{"page": "product"}', 'mobile');

-- 查询数据
SELECT 
    event_type,
    COUNT() as count,
    uniqExact(user_id) as unique_users
FROM events
WHERE event_time >= '2024-01-01'
GROUP BY event_type;

关键参数说明：

• ORDER BY：排序键，决定数据在磁盘上的物理存储顺序。查询时按此顺序读取更高效。
• PRIMARY KEY：主键，用于构建稀疏索引。默认与 ORDER BY 相同，可以不同。
• PARTITION BY：分区键，数据按此划分到不同目录。常用于按时间分区，便于数据生命周期管理。
• SETTINGS：引擎特定设置。

ReplacingMergeTree（去重引擎）

ReplacingMergeTree 在合并时删除重复数据：

CREATE TABLE user_profiles (
    user_id UInt64,
    name String,
    email String,
    update_time DateTime,
    version UInt64
)
ENGINE = ReplacingMergeTree(version)  -- 指定版本列，保留版本最大的行
ORDER BY user_id                      -- 按用户ID去重
PARTITION BY toYYYYMM(update_time);

-- 插入数据（可能有重复）
INSERT INTO user_profiles VALUES 
    (1001, 'Alice', '[email protected]', '2024-01-01 10:00:00', 1),
    (1001, 'Alice W', '[email protected]', '2024-01-02 11:00:00', 2),
    (1001, 'Alice Wang', '[email protected]', '2024-01-03 12:00:00', 3);

-- 注意：去重只在后台合并时发生，查询时可能仍有重复
-- 使用 FINAL 关键字强制合并查询（影响性能）
SELECT * FROM user_profiles FINAL WHERE user_id = 1001;

-- 推荐：使用 argMax 函数获取最新值
SELECT 
    user_id,
    argMax(name, version) as name,
    argMax(email, version) as email,
    max(update_time) as update_time
FROM user_profiles
GROUP BY user_id;

ReplacingMergeTree 特点：

• 去重基于 ORDER BY 键
• 去重只在后台合并时执行，不是实时的
• 可选版本列，保留版本最大的行
• 适合数据更新场景

SummingMergeTree（预聚合引擎）

SummingMergeTree 在合并时对数值列求和：

CREATE TABLE user_stats (
    user_id UInt64,
    date Date,
    clicks UInt64,
    views UInt64,
    purchases UInt64,
    revenue Decimal(12, 2)
)
ENGINE = SummingMergeTree((clicks, views, purchases, revenue))  -- 指定聚合列
ORDER BY (user_id, date)
PARTITION BY toYYYYMM(date);

-- 插入数据（同一用户同一天可能有多条记录）
INSERT INTO user_stats VALUES 
    (1001, '2024-01-15', 10, 50, 2, 100.00),
    (1001, '2024-01-15', 15, 30, 1, 50.00),  -- 合并后 clicks = 25
    (1001, '2024-01-15', 5, 20, 0, 0.00);    -- 合并后 views = 100

-- 查询（合并后数据已预聚合）
SELECT 
    user_id,
    date,
    sum(clicks) as total_clicks,    -- 即使没有合并，SUM 也是正确的
    sum(views) as total_views
FROM user_stats
GROUP BY user_id, date;

SummingMergeTree 特点：

• 合并时对指定列求和
• 基于 ORDER BY 键聚合
• 减少存储空间，提高查询性能
• 适合预聚合场景

AggregatingMergeTree（高级聚合引擎）

AggregatingMergeTree 支持更复杂的聚合状态：

CREATE TABLE user_metrics (
    user_id UInt64,
    date Date,
    unique_visitors AggregateFunction(uniq, UInt64),      -- 存储唯一值计数状态
    avg_duration AggregateFunction(avg, Float64),         -- 存储平均值状态
    quantiles_duration AggregateFunction(quantiles(0.5, 0.9, 0.99), Float64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (user_id, date)
PARTITION BY toYYYYMM(date);

-- 使用 -State 后缀函数插入聚合状态
INSERT INTO user_metrics 
SELECT 
    user_id,
    date,
    uniqState(visitor_id) as unique_visitors,
    avgState(duration) as avg_duration,
    quantilesState(0.5, 0.9, 0.99)(duration) as quantiles_duration
FROM raw_events
GROUP BY user_id, date;

-- 使用 -Merge 后缀函数查询聚合结果
SELECT 
    user_id,
    uniqMerge(unique_visitors) as unique_visitors,
    avgMerge(avg_duration) as avg_duration,
    quantilesMerge(0.5, 0.9, 0.99)(quantiles_duration) as quantiles
FROM user_metrics
GROUP BY user_id;

AggregatingMergeTree 适用场景：

• 需要存储聚合状态而非原始数据
• 支持分布式聚合计算
• 适合计算 UV、PV、分位数等指标

CollapsingMergeTree（折叠引擎）

CollapsingMergeTree 通过 sign 列实现数据删除：

CREATE TABLE user_sessions (
    user_id UInt64,
    session_id String,
    start_time DateTime,
    end_time DateTime,
    sign Int8  -- 1 表示插入，-1 表示取消
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY (user_id, session_id);

-- 插入数据
INSERT INTO user_sessions VALUES 
    (1001, 's001', '2024-01-15 10:00:00', '2024-01-15 10:30:00', 1);

-- 取消/删除数据（插入 sign=-1 的相同行）
INSERT INTO user_sessions VALUES 
    (1001, 's001', '2024-01-15 10:00:00', '2024-01-15 10:30:00', -1);

-- 合并后，sign=1 和 sign=-1 的行会相互抵消（删除）

VersionedCollapsingMergeTree：

CollapsingMergeTree 要求 sign=1 和 sign=-1 的行完全相同才能折叠。VersionedCollapsingMergeTree 引入版本列，允许行内容不同：

CREATE TABLE user_sessions_v2 (
    user_id UInt64,
    session_id String,
    start_time DateTime,
    end_time DateTime,
    sign Int8,
    version UInt64  -- 版本列
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY (user_id, session_id);

ReplicatedMergeTree（复制引擎）

ReplicatedMergeTree 为 MergeTree 添加高可用和数据复制能力：

-- 在配置文件中定义 ZooKeeper
<!-- config.xml -->
<zookeeper>
    <node index="1">
        <host>zk1</host>
        <port>2181</port>
    </node>
    <node index="2">
        <host>zk2</host>
        <port>2181</port>
    </node>
    <node index="3">
        <host>zk3</host>
        <port>2181</port>
    </node>
</zookeeper>

-- 创建复制表
-- 在节点1上执行
CREATE TABLE events_replicated (
    event_time DateTime,
    user_id UInt64,
    event_type String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{database}/events_replicated', 'replica1')
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

-- 在节点2上执行（相同的 ZooKeeper 路径，不同的副本名称）
CREATE TABLE events_replicated (
    event_time DateTime,
    user_id UInt64,
    event_type String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{database}/events_replicated', 'replica2')
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

ReplicatedMergeTree 特点：

• 自动数据同步：写入任一副本，自动同步到其他副本
• 故障恢复：一个副本故障不影响读写
• 数据一致性：保证最终一致性
• 去重：自动去除重复写入

表引擎选择指南

引擎	适用场景	特点
MergeTree	通用分析场景	最灵活，性能最好
ReplacingMergeTree	需要去重的场景	后台合并时去重
SummingMergeTree	预聚合统计	合并时数值求和
AggregatingMergeTree	复杂聚合计算	存储聚合状态
CollapsingMergeTree	数据更新/删除	通过 sign 列折叠
ReplicatedMergeTree	高可用生产环境	数据复制、故障转移

数据类型

ClickHouse 提供丰富的数据类型，分为以下类别：

数值类型

-- 整数类型
Int8      -- 有符号 8 位整数，-128 到 127
Int16     -- 有符号 16 位整数，-32768 到 32767
Int32     -- 有符号 32 位整数
Int64     -- 有符号 64 位整数
Int128    -- 有符号 128 位整数
Int256    -- 有符号 256 位整数

UInt8     -- 无符号 8 位整数，0 到 255
UInt16    -- 无符号 16 位整数，0 到 65535
UInt32    -- 无符号 32 位整数
UInt64    -- 无符号 64 位整数
UInt128   -- 无符号 128 位整数
UInt256   -- 无符号 256 位整数

-- 浮点类型
Float32   -- 单精度浮点数
Float64   -- 双精度浮点数

-- 定点数
Decimal(P, S)   -- P 总精度，S 小数位数
Decimal32(S)    -- 相当于 Decimal(9, S)
Decimal64(S)    -- 相当于 Decimal(18, S)
Decimal128(S)   -- 相当于 Decimal(38, S)

-- 示例
CREATE TABLE numeric_types (
    id UInt64,
    small_num Int8,
    big_num Int64,
    price Decimal(10, 2),
    ratio Float64
);

字符串类型

-- 字符串类型
String          -- 变长字符串，无长度限制
FixedString(N)  -- 定长字符串，N 字节

-- 低基数字符串（优化存储和查询）
LowCardinality(String)  -- 适用于重复值多的列

-- 示例
CREATE TABLE string_types (
    id UInt64,
    name String,
    country LowCardinality(String),  -- 国家名重复度高，适合低基数优化
    code FixedString(10)             -- 固定 10 字节
);

-- 低基数优化原理
-- 原始数据：['China', 'USA', 'China', 'China', 'USA', 'Japan', ...]
-- 内部存储：
--   字典：{0: 'China', 1: 'USA', 2: 'Japan'}
--   索引：[0, 1, 0, 0, 1, 2, ...]
-- 当重复值多时，存储和查询效率大幅提升

日期时间类型

-- 日期类型
Date        -- 日期，精度到天，2 字节存储
Date32      -- 日期，支持更大范围，4 字节存储

-- 日期时间类型
DateTime        -- 日期时间，精度到秒
DateTime64(N)   -- 日期时间，亚秒精度（N 位小数）

-- 示例
CREATE TABLE datetime_types (
    event_date Date,
    event_time DateTime,
    precise_time DateTime64(3),     -- 毫秒精度
    nano_time DateTime64(9)         -- 纳秒精度
);

-- 插入数据
INSERT INTO datetime_types VALUES 
    ('2024-01-15', '2024-01-15 10:30:00', '2024-01-15 10:30:00.123', '2024-01-15 10:30:00.123456789');

-- 日期时间函数
SELECT 
    now() as current_time,
    today() as today,
    yesterday() as yesterday,
    toDateTime('2024-01-15 10:30:00') as parsed_time,
    toDateTime64('2024-01-15 10:30:00.123', 3) as parsed_time_ms,
    formatDateTime(now(), '%Y-%m-%d %H:%M:%S') as formatted;

复合类型

-- 数组
Array(T)    -- 元素类型为 T 的数组

CREATE TABLE array_example (
    id UInt64,
    tags Array(String),
    scores Array(Int32)
);

INSERT INTO array_example VALUES 
    (1, ['tag1', 'tag2', 'tag3'], [90, 85, 92]);

-- 数组操作
SELECT 
    id,
    tags[1] as first_tag,           -- 访问元素（索引从1开始）
    length(tags) as tag_count,      -- 数组长度
    has(tags, 'tag1') as has_tag1,  -- 是否包含元素
    arrayJoin(tags) as tag          -- 展开数组为多行
FROM array_example;

-- 映射
Map(K, V)   -- 键类型 K，值类型 V 的映射

CREATE TABLE map_example (
    id UInt64,
    properties Map(String, String)
);

INSERT INTO map_example VALUES 
    (1, {'color': 'red', 'size': 'large', 'brand': 'Nike'});

-- 映射操作
SELECT 
    id,
    properties['color'] as color,   -- 访问元素
    mapKeys(properties) as keys,    -- 获取所有键
    mapValues(properties) as values -- 获取所有值
FROM map_example;

-- 元组
Tuple(T1, T2, ...)  -- 固定类型序列

CREATE TABLE tuple_example (
    id UInt64,
    location Tuple(Float64, Float64)  -- (纬度, 经度)
);

INSERT INTO tuple_example VALUES 
    (1, (39.9042, 116.4074));

-- 元组操作
SELECT 
    id,
    location.1 as latitude,
    location.2 as longitude
FROM tuple_example;

-- 嵌套类型
Nested(Name1 Type1, Name2 Type2, ...)

CREATE TABLE nested_example (
    id UInt64,
    events Nested(
        time DateTime,
        type String,
        value UInt64
    )
);

-- 等价于多个数组
-- events.time Array(DateTime)
-- events.type Array(String)
-- events.value Array(UInt64)

INSERT INTO nested_example VALUES 
    (1, ['2024-01-15 10:00:00', '2024-01-15 11:00:00'], ['click', 'view'], [1, 1]);

-- 展开嵌套数据
SELECT 
    id,
    event.time as event_time,
    event.type as event_type
FROM nested_example
ARRAY JOIN events as event;

特殊类型

-- 枚举
Enum8('name1' = value1, 'name2' = value2, ...)   -- 8 位枚举
Enum16('name1' = value1, 'name2' = value2, ...)  -- 16 位枚举

CREATE TABLE enum_example (
    id UInt64,
    status Enum8('pending' = 0, 'processing' = 1, 'completed' = 2, 'failed' = 3)
);

INSERT INTO enum_example VALUES (1, 'pending'), (2, 'completed');

-- 枚举存储整数，但操作用字符串
SELECT id, status, CAST(status, 'Int8') as status_value FROM enum_example;

-- 可空类型
Nullable(T)  -- 允许 NULL 值

CREATE TABLE nullable_example (
    id UInt64,
    name String,
    nickname Nullable(String),  -- 可为 NULL
    age Nullable(UInt32)
);

INSERT INTO nullable_example VALUES 
    (1, 'Alice', 'Ali', 25),
    (2, 'Bob', NULL, NULL);  -- NULL 值

-- NULL 处理
SELECT 
    id,
    name,
    coalesce(nickname, name) as display_name,  -- NULL 替换
    ifNull(age, 0) as age_or_zero,
    isNull(nickname) as has_no_nickname
FROM nullable_example;

-- 注意：Nullable 影响性能，默认值优先

-- UUID
UUID  -- 通用唯一标识符

CREATE TABLE uuid_example (
    id UUID,
    name String
) ENGINE = MergeTree()
ORDER BY id;

INSERT INTO uuid_example VALUES (generateUUIDv4(), 'Alice');

-- JSON（ClickHouse 22.0+）
JSON  -- 动态 JSON 类型

CREATE TABLE json_example (
    id UInt64,
    data JSON
);

INSERT INTO json_example VALUES 
    (1, '{"name": "Alice", "age": 25, "tags": ["a", "b"]}');

SELECT 
    id,
    data.name,
    data.age,
    data.tags
FROM json_example;

SQL 语法

ClickHouse 支持标准 SQL 的大部分语法，同时提供特有的扩展。

数据库操作

-- 创建数据库
CREATE DATABASE IF NOT EXISTS analytics
ENGINE = Atomic;  -- 默认引擎，支持异步 DDL

-- 其他数据库引擎
CREATE DATABASE mysql_db
ENGINE = MySQL('host:port', 'database', 'user', 'password');

CREATE DATABASE postgres_db
ENGINE = PostgreSQL('host:port', 'database', 'user', 'password');

-- 查看数据库
SHOW DATABASES;
SHOW DATABASES LIKE 'ana%';

-- 切换数据库
USE analytics;

-- 删除数据库
DROP DATABASE IF EXISTS analytics;

表操作

-- 创建表
CREATE TABLE IF NOT EXISTS events (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    platform LowCardinality(String),
    event_data String,
    revenue Decimal(12, 2) DEFAULT 0
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id)
SETTINGS index_granularity = 8192
COMMENT '用户事件表';

-- 查看表结构
DESCRIBE events;
DESCRIBE TABLE events;
SHOW CREATE TABLE events;

-- 修改表结构
-- 添加列
ALTER TABLE events ADD COLUMN IF NOT EXISTS session_id String AFTER user_id;

-- 修改列类型
ALTER TABLE events MODIFY COLUMN event_data Nullable(String);

-- 删除列
ALTER TABLE events DROP COLUMN IF EXISTS session_id;

-- 添加注释
ALTER TABLE events COMMENT COLUMN user_id '用户唯一标识';

-- 重命名表
RENAME TABLE events TO user_events;

-- 删除表
DROP TABLE IF EXISTS events;

-- 清空表
TRUNCATE TABLE events;

数据操作

-- 插入数据
-- 基本插入
INSERT INTO events (event_time, user_id, event_type, platform) VALUES 
    ('2024-01-15 10:00:00', 1001, 'click', 'web'),
    ('2024-01-15 10:01:00', 1002, 'view', 'mobile');

-- 从查询插入
INSERT INTO events_archive
SELECT * FROM events WHERE event_time < '2024-01-01';

-- 从文件插入
INSERT INTO events FROM INFILE '/data/events.csv'
FORMAT CSV;

-- 更新和删除（轻量级）
-- 注意：ClickHouse 不适合频繁更新删除
-- UPDATE/DELETE 是异步操作，会创建新的 Part

-- 更新
ALTER TABLE events 
UPDATE platform = 'web' 
WHERE user_id = 1001;

-- 删除
ALTER TABLE events DELETE WHERE user_id = 1001;

-- 批量删除（更高效）
ALTER TABLE events DROP PARTITION '202401';

查询语法

-- 基本查询
SELECT 
    event_type,
    COUNT() as event_count,
    uniqExact(user_id) as unique_users,
    SUM(revenue) as total_revenue
FROM events
WHERE event_time >= '2024-01-01' 
  AND event_time < '2024-02-01'
GROUP BY event_type
HAVING event_count > 100
ORDER BY event_count DESC
LIMIT 10;

-- LIMIT BY（分组内限制）
-- 每个用户取最近 3 个事件
SELECT 
    user_id,
    event_time,
    event_type
FROM events
ORDER BY user_id, event_time DESC
LIMIT 3 BY user_id;

-- WITH 子句
WITH 
    (SELECT AVG(revenue) FROM events) as avg_revenue
SELECT 
    user_id,
    SUM(revenue) as total_revenue,
    total_revenue / avg_revenue as revenue_ratio
FROM events
GROUP BY user_id
HAVING total_revenue > avg_revenue;

-- 数组连接（ARRAY JOIN）
SELECT 
    user_id,
    tag
FROM events
ARRAY JOIN splitByComma(tags) as tag;

-- Join 操作
SELECT 
    e.user_id,
    e.event_type,
    u.user_name
FROM events e
LEFT JOIN users u ON e.user_id = u.id;

-- Join 类型
-- INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN
-- ASOF JOIN（近似连接，用于时间序列）
SELECT 
    t1.time,
    t1.value,
    t2.value as prev_value
FROM table1 t1
ASOF LEFT JOIN table2 t2 
ON t1.key = t2.key AND t1.time > t2.time;

-- 窗口函数
SELECT 
    user_id,
    event_time,
    event_type,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) as event_seq,
    LAG(event_type, 1) OVER (PARTITION BY user_id ORDER BY event_time) as prev_event,
    LEAD(event_type, 1) OVER (PARTITION BY user_id ORDER BY event_time) as next_event
FROM events;

-- 聚合窗口
SELECT 
    user_id,
    event_time,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY user_id 
        ORDER BY event_time 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_revenue
FROM events;

常用函数

-- 聚合函数
SELECT 
    COUNT() as total,
    COUNT(DISTINCT user_id) as unique_users,
    uniqExact(user_id) as exact_unique_users,     -- 精确去重
    uniq(user_id) as approx_unique_users,          -- 近似去重（HyperLogLog）
    uniqCombined(user_id) as combined_unique,      -- 更精确的近似去重
    SUM(revenue) as total_revenue,
    AVG(revenue) as avg_revenue,
    MIN(event_time) as first_event,
    MAX(event_time) as last_event,
    quantile(0.5)(revenue) as median,              -- 中位数
    quantiles(0.25, 0.5, 0.75)(revenue) as quartiles  -- 四分位数
FROM events;

-- 字符串函数
SELECT 
    concat('Hello', ' ', 'World') as greeting,
    concat_ws('-', 'a', 'b', 'c') as joined,       -- 带分隔符连接
    substring('Hello World', 1, 5) as sub,
    lower('HELLO') as lower_str,
    upper('hello') as upper_str,
    trim('  hello  ') as trimmed,
    splitByComma('a,b,c') as split_arr,            -- 分割为数组
    splitByString('|', 'a|b|c') as split_arr2,
    arrayStringConcat(['a', 'b', 'c'], '-') as joined_arr,  -- 数组连接
    replaceAll('hello world', 'o', '0') as replaced,
    extract('user_123', 'user_(\\d+)') as extracted,  -- 正则提取
    match('hello', 'h.*o') as is_match,            -- 正则匹配
    length('hello') as str_length,
    empty('') as is_empty,
    notEmpty('hello') as not_empty;

-- 日期时间函数
SELECT 
    now() as current_time,
    now64(3) as current_time_ms,                   -- 毫秒精度
    today() as today_date,
    yesterday() as yesterday_date,
    toDateTime('2024-01-15 10:30:00') as parsed,
    toDateTime64('2024-01-15 10:30:00.123', 3) as parsed_ms,
    toDate('2024-01-15') as parsed_date,
    formatDateTime(now(), '%Y-%m-%d %H:%M:%S') as formatted,
    toYear(now()) as year,
    toMonth(now()) as month,
    toDayOfMonth(now()) as day,
    toHour(now()) as hour,
    toMonday(now()) as week_start,                 -- 本周一
    toStartOfMonth(now()) as month_start,          -- 本月第一天
    toStartOfYear(now()) as year_start,            -- 本年第一天
    addDays(now(), 7) as next_week,
    addMonths(now(), 1) as next_month,
    subtractDays(now(), 7) as last_week,
    dateDiff('day', '2024-01-01', '2024-01-15') as days_diff;

-- 条件函数
SELECT 
    if(revenue > 100, 'high', 'low') as revenue_level,
    multiIf(
        revenue > 1000, 'very high',
        revenue > 100, 'high',
        revenue > 10, 'medium',
        'low'
    ) as detailed_level,
    coalesce(NULL, 'default') as value,
    ifNull(NULL, 'default') as value2,
    nullIf(0, 0) as null_result;

-- 类型转换
SELECT 
    CAST('123' AS UInt64) as num,
    toUInt64('123') as num2,
    toString(123) as str,
    toDateTime('2024-01-15 10:00:00') as dt,
    toDate('2024-01-15') as d,
    toDecimal64(123.456, 2) as decimal_val;

查询优化

-- 使用 PREWHERE 优化查询
-- PREWHERE 先过滤再读取数据，比 WHERE 更高效
SELECT 
    user_id,
    event_type
FROM events
PREWHERE event_time >= '2024-01-01'  -- 先过滤
WHERE event_type = 'click';          -- 再过滤

-- 使用 SAMPLE 采样查询
SELECT 
    event_type,
    COUNT() as count
FROM events
SAMPLE 0.1  -- 采样 10% 的数据
GROUP BY event_type;

-- 使用 optimize_skip_unused_shards
-- 跳过不包含目标数据的分片
SET optimize_skip_unused_shards = 1;

-- 使用 FINAL 查询（谨慎使用）
-- 强制合并后查询，确保去重
SELECT * FROM user_profiles FINAL;

索引与分区

主键索引

ClickHouse 使用稀疏索引，索引粒度默认 8192 行：

CREATE TABLE indexed_table (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    INDEX idx_type event_type TYPE bloom_filter GRANULARITY 1
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);  -- 主键索引

-- 索引结构
-- 索引粒度：每 8192 行创建一个索引标记
-- 索引存储：event_time, user_id -> mark number
-- 查询时：根据条件定位 mark，只读取相关 granule

主键设计原则：

1. 查询频率高的列放前面：主键决定数据物理排序，影响查询性能
2. 基数适中的列：基数太高（如 UUID）或太低（如布尔值）都不适合
3. 范围查询列：时间列常作为主键，支持范围查询

二级索引（数据跳过索引）

ClickHouse 支持多种数据跳过索引：

CREATE TABLE events_with_index (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    platform String,
    amount Float64,
    
    -- Bloom Filter 索引：适合等值查询
    INDEX idx_type event_type TYPE bloom_filter GRANULARITY 1,
    
    -- MinMax 索引：适合范围查询
    INDEX idx_amount amount TYPE minmax GRANULARITY 1,
    
    -- Set 索引：适合低基数列
    INDEX idx_platform platform TYPE set(0) GRANULARITY 1,
    
    -- Token Bloom Filter：适合全文搜索
    INDEX idx_token platform TYPE tokenbf_v1(512, 3, 0) GRANULARITY 1
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);

-- 索引类型说明
-- bloom_filter: 布隆过滤器，判断值是否存在
-- minmax: 存储每个 granule 的最小最大值
-- set: 存储所有唯一值
-- tokenbf_v1: 对分词后的 token 建布隆过滤器

分区策略

分区用于数据管理和生命周期控制：

-- 按月分区
CREATE TABLE events_partitioned (
    event_time DateTime,
    user_id UInt64,
    event_type String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

-- 按天分区
PARTITION BY toDate(event_time)

-- 多级分区（按月和平台）
PARTITION BY (toYYYYMM(event_time), platform)
ORDER BY (event_time, user_id);

-- 分区操作
-- 查看分区
SELECT 
    partition,
    name,
    rows,
    bytes_on_disk
FROM system.parts
WHERE table = 'events_partitioned' AND active = 1;

-- 删除分区（快速删除大量数据）
ALTER TABLE events_partitioned DROP PARTITION '202401';

-- 分离分区（保留数据，不可查询）
ALTER TABLE events_partitioned DETACH PARTITION '202401';

-- 附加分区
ALTER TABLE events_partitioned ATTACH PARTITION '202401';

-- 移动分区到其他表
ALTER TABLE events_partitioned MOVE PARTITION '202401' TO events_archive;

-- 冻结分区（创建备份）
ALTER TABLE events_partitioned FREEZE PARTITION '202401';

分区设计原则：

1. 分区不宜过多：每个分区会创建独立的目录，过多分区影响性能
2. 分区粒度适当：建议单个分区至少 1GB 数据
3. 按时间分区：便于数据生命周期管理，按时间删除旧数据
4. 避免过度分区：不要按高基数列（如 user_id）分区

TTL（数据生命周期）

ClickHouse 支持 TTL 自动管理数据生命周期：

CREATE TABLE events_with_ttl (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    amount Float64
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id)
PARTITION BY toYYYYMM(event_time)
-- 90 天后删除数据
TTL event_time + INTERVAL 90 DAY
-- 30 天后移动到冷存储
TTL event_time + INTERVAL 30 DAY TO VOLUME 'cold'
-- 60 天后将 amount 列压缩
TTL event_time + INTERVAL 60 DAY RECOMPRESS CODEC(ZSTD(9));

-- 修改 TTL
ALTER TABLE events_with_ttl 
MODIFY TTL event_time + INTERVAL 180 DAY;

-- 删除 TTL
ALTER TABLE events_with_ttl REMOVE TTL;

性能优化

写入优化

-- 1. 批量写入
-- 推荐：每次写入 1000-10000 行
INSERT INTO events VALUES (...), (...), ...;

-- 2. 异步写入（ClickHouse 22.0+）
-- 客户端缓冲，定期批量发送，适合高频小批量写入场景
-- 优势：减少Part数量、降低合并压力、提高写入吞吐量
SET async_insert = 1;                      -- 启用异步插入
SET wait_for_async_insert = 1;             -- 等待异步插入完成
SET async_insert_max_data_size = 1000000;  -- 缓冲区最大大小（字节）
SET async_insert_busy_timeout_ms = 10000;  -- 最大等待时间（毫秒）
SET async_insert_max_query_number = 500;   -- 最大缓存查询数

-- 异步写入模式对比
-- 模式1：wait_for_async_insert=1，等待数据落盘后返回
-- 模式2：wait_for_async_insert=0，立即返回，不保证数据持久化

-- 3. 并行写入
-- 多线程并发写入不同分区

-- 4. 写入前预排序
-- 数据按主键排序后写入，减少后台合并开销
INSERT INTO events SELECT * FROM staging ORDER BY (event_time, user_id);

查询优化

-- 1. 使用 PREWHERE
SELECT user_id, event_type
FROM events
PREWHERE event_time >= '2024-01-01'  -- 先过滤
WHERE event_type = 'click';

-- 2. 避免 SELECT *
SELECT event_time, user_id FROM events;  -- 好
SELECT * FROM events;                     -- 差

-- 3. 使用分区裁剪
SELECT * FROM events
WHERE event_time >= '2024-01-01' 
  AND event_time < '2024-02-01';  -- 只扫描 202401 分区

-- 4. 使用近似聚合
SELECT 
    uniq(user_id) as unique_users,          -- 近似去重，快
    uniqExact(user_id) as exact_unique_users -- 精确去重，慢
FROM events;

-- 5. 使用物化视图
CREATE MATERIALIZED VIEW events_daily_mv
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(day)
ORDER BY (day, event_type)
AS SELECT 
    toDate(event_time) as day,
    event_type,
    COUNT() as event_count,
    uniqState(user_id) as unique_users
FROM events
GROUP BY day, event_type;

-- 查询物化视图
SELECT 
    day,
    event_type,
    event_count,
    uniqMerge(unique_users) as unique_users
FROM events_daily_mv
GROUP BY day, event_type, event_count;

内存优化

-- 1. 限制查询内存
SET max_memory_usage = 10000000000;  -- 10GB

-- 2. 使用外部排序
SET max_bytes_before_external_sort = 100000000;  -- 100MB

-- 3. 使用磁盘聚合
SET aggregation_memory_efficient_merge_threads = 4;

-- 4. 限制并发查询
SET max_concurrent_queries_for_user = 10;

查询优化技巧

使用 PREWHERE 优化查询

PREWHERE 是 ClickHouse 特有的优化技术，它先过滤再读取数据列，比 WHERE 更高效：

-- 差：使用 WHERE
SELECT user_id, event_type
FROM events
WHERE event_time >= '2024-01-01'  -- 先读取所有列，再过滤
  AND event_type = 'click';

-- 好：使用 PREWHERE
SELECT user_id, event_type
FROM events
PREWHERE event_time >= '2024-01-01'  -- 先根据条件过滤行
WHERE event_type = 'click';          -- 再读取需要的列

-- PREWHERE 工作原理：
-- 1. 读取主键列判断符合条件的 Granule
-- 2. 只读取这些 Granule 中需要的列
-- 3. 应用 WHERE 条件进一步过滤

PREWHERE 使用建议：

• 将过滤性最强的条件放在 PREWHERE 中
• 过滤条件涉及的列应该是主键或排序键的一部分
• 如果条件列不在主键中，PREWHERE 仍然有效，但效果会降低

分区裁剪优化

合理使用分区裁剪可以大幅减少扫描数据量：

-- 好：分区裁剪生效
SELECT * FROM events
WHERE event_time >= '2024-01-01' 
  AND event_time < '2024-02-01';  -- 只扫描 202401 分区

-- 差：分区裁剪失效
SELECT * FROM events
WHERE toYYYYMM(event_time) = 202401;  -- 无法利用分区裁剪

-- 查看查询扫描的分区
SELECT 
    table,
    partition,
    rows_read,
    bytes_read
FROM system.query_log
WHERE query_id = '<query_id>';

使用近似聚合函数

对于大数据量的统计分析，近似聚合函数可以大幅提升性能：

-- 精确去重（慢）
SELECT uniqExact(user_id) FROM events;

-- 近似去重（快，误差约2%）
SELECT uniq(user_id) FROM events;

-- 更精确的近似去重
SELECT uniqCombined(64)(user_id) FROM events;

-- 近似分位数
SELECT 
    quantile(0.5)(response_time) as median,        -- 近似中位数
    quantileExact(0.5)(response_time) as exact_median,  -- 精确中位数
    quantiles(0.5, 0.9, 0.99)(response_time) as quantiles  -- 多个分位数
FROM requests;

-- 近似 TopK
SELECT topK(10)(city) FROM users;  -- 近似高频值

避免 SELECT *

只查询需要的列可以大幅减少 I/O：

-- 差：读取所有列
SELECT * FROM events WHERE event_time >= '2024-01-01';

-- 好：只读取需要的列
SELECT event_time, user_id, event_type 
FROM events 
WHERE event_time >= '2024-01-01';

-- 查看查询读取的列
EXPLAIN PIPELINE 
SELECT * FROM events LIMIT 10;

表设计优化

-- 1. 选择合适的主键
-- 查询频繁的列、基数适中的列放前面
ORDER BY (event_time, user_id)

-- 2. 使用分区
PARTITION BY toYYYYMM(event_time)

-- 3. 使用压缩
CREATE TABLE compressed_table (
    ...
)
ENGINE = MergeTree()
ORDER BY ...
SETTINGS 
    compression_codec = 'ZSTD(3)';  -- 压缩算法

-- 4. 列级别压缩
CREATE TABLE column_compressed (
    id UInt64,
    data String CODEC(ZSTD(3)),      -- 高压缩比
    status LowCardinality(String),   -- 低基数优化
    time DateTime CODEC(Delta, ZSTD) -- 时间列专用压缩
)
ENGINE = MergeTree()
ORDER BY id;

-- 5. 使用 Projection（ClickHouse 21.6+）
-- Projection 允许同一张表按不同的排序方式存储数据
-- 类似于物化视图，但数据与原表保持同步

CREATE TABLE events_with_projection (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    amount Float64
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);

-- 添加 Projection：按用户维度优化
ALTER TABLE events_with_projection ADD PROJECTION user_projection (
    SELECT * ORDER BY user_id, event_time
);

-- 添加 Projection：按事件类型和时间优化聚合查询
ALTER TABLE events_with_projection ADD PROJECTION aggregation_projection (
    SELECT 
        user_id,
        event_type,
        sum(amount) as total_amount,
        count() as event_count
    GROUP BY user_id, event_type
);

-- 物化 Projection（首次添加后需要执行）
ALTER TABLE events_with_projection MATERIALIZE PROJECTION user_projection;
ALTER TABLE events_with_projection MATERIALIZE PROJECTION aggregation_projection;

-- 查询时自动选择最优的 Projection
-- ClickHouse 会根据查询条件自动选择使用哪个 Projection
SELECT 
    user_id,
    event_type,
    sum(amount)
FROM events_with_projection
WHERE user_id = 1001
GROUP BY user_id, event_type;
-- 此查询会自动使用 aggregation_projection 或 user_projection

高级特性

Projection 投影

Projection（投影）是 ClickHouse 21.6 引入的重要特性，它允许在同一张表中按不同的排序方式存储数据。与物化视图不同，Projection 的数据与原表保持同步，查询时自动选择最优的 Projection。

为什么需要 Projection？

在实际业务中，同一张表往往有多种查询模式。例如：

• 按时间范围查询最新数据
• 按用户维度聚合分析
• 按事件类型统计

传统解决方案是创建多个物化视图，但这带来了数据同步和维护的复杂性。Projection 在表内部解决了这个问题：

原表数据：按 (event_time, user_id) 排序
├── Projection 1：按 (user_id, event_time) 排序
├── Projection 2：按 (event_type, event_time) 排序
└── Projection 3：预聚合数据

创建 Projection

-- 创建基础表
CREATE TABLE events_with_projection (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    platform LowCardinality(String),
    amount Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

-- 添加 Projection：按用户维度查询优化
-- 场景：经常按用户查询其所有事件
ALTER TABLE events_with_projection ADD PROJECTION user_projection (
    SELECT * ORDER BY user_id, event_time
);

-- 添加 Projection：按事件类型聚合优化
-- 场景：统计各事件类型的数量和金额
ALTER TABLE events_with_projection ADD PROJECTION type_aggregation (
    SELECT 
        event_type,
        platform,
        sum(amount) as total_amount,
        count() as event_count,
        uniqState(user_id) as unique_users
    GROUP BY event_type, platform
);

-- 物化 Projection（首次添加后需要执行）
ALTER TABLE events_with_projection MATERIALIZE PROJECTION user_projection;
ALTER TABLE events_with_projection MATERIALIZE type_aggregation;

Projection 的工作原理

当查询执行时，ClickHouse 会自动选择最优的 Projection：

1. 查询分析：解析查询的 WHERE 条件、GROUP BY、ORDER BY
2. 候选筛选：找出能够满足查询需求的所有 Projection
3. 代价估算：估算每个候选 Projection 需要读取的数据量
4. 最优选择：选择数据量最小的 Projection 执行查询

查询：SELECT event_type, sum(amount) FROM events_with_projection GROUP BY event_type

选择过程：
- 原表：需要扫描全表，聚合大量数据
- user_projection：同样需要全表扫描
- type_aggregation：数据已预聚合，只需读取少量聚合结果

结果：选择 type_aggregation Projection

查询优化示例

-- 查询1：按用户查询
-- 自动使用 user_projection
SELECT event_time, event_type, amount
FROM events_with_projection
WHERE user_id = 1001
ORDER BY event_time DESC
LIMIT 100;

-- 查询2：按事件类型聚合
-- 自动使用 type_aggregation Projection
SELECT 
    event_type,
    platform,
    total_amount,
    event_count,
    uniqMerge(unique_users) as unique_users
FROM events_with_projection
GROUP BY event_type, platform, total_amount, event_count;

-- 查看是否使用了 Projection
EXPLAIN PIPELINE 
SELECT * FROM events_with_projection WHERE user_id = 1001;

Projection 管理

-- 查看表的 Projection
SELECT 
    name,
    type,
    partition_id,
    row_count
FROM system.projections
WHERE database = 'default' AND table = 'events_with_projection';

-- 删除 Projection
ALTER TABLE events_with_projection DROP PROJECTION user_projection;

-- 清除 Projection 数据（保留定义）
ALTER TABLE events_with_projection CLEAR PROJECTION user_projection;

-- 重新物化 Projection
ALTER TABLE events_with_projection MATERIALIZE PROJECTION user_projection;

Projection 使用建议

场景	建议
查询模式固定且多样	创建多个 Projection 优化不同查询
数据量大、更新频繁	谨慎使用，Projection 会增加写入开销
预聚合查询	使用聚合 Projection 减少查询计算量
存储空间有限	评估 Projection 的存储开销

注意事项：

• Projection 会增加存储空间和写入开销
• 查询中使用 FINAL 修饰符时不会使用 Projection
• 可以通过 force_optimize_projection 设置强制使用 Projection

异步插入

异步插入（Asynchronous Inserts）是 ClickHouse 22.0 引入的特性，专为高频小批量写入场景设计。

传统写入的问题

传统方式下，每次 INSERT 都会创建一个新的 Part：

INSERT VALUES (1, 'a') → Part 1
INSERT VALUES (2, 'b') → Part 2
INSERT VALUES (3, 'c') → Part 3
...

这会导致：

• Part 数量爆炸，影响查询性能
• 后台合并压力巨大
• 内存和磁盘开销增加

异步插入原理

异步插入将多个小 INSERT 合并为一个批次：

客户端 INSERT 1 → 缓冲区
客户端 INSERT 2 → 缓冲区
客户端 INSERT 3 → 缓冲区
    ↓ 等待条件满足
合并写入 → Part 1（包含 INSERT 1、2、3 的数据）

启用异步插入

-- 方式1：会话级别启用
SET async_insert = 1;
SET wait_for_async_insert = 1;
SET async_insert_max_data_size = 1000000;  -- 缓冲区最大 1MB
SET async_insert_busy_timeout_ms = 10000;  -- 最大等待 10 秒

-- 然后正常插入
INSERT INTO events VALUES ('2024-01-15 10:00:00', 1001, 'click', 'web');
INSERT INTO events VALUES ('2024-01-15 10:01:00', 1002, 'view', 'mobile');
-- 这些 INSERT 会被合并成一个批次写入

-- 方式2：配置文件（users.xml）
<profiles>
    <default>
        <async_insert>1</async_insert>
        <wait_for_async_insert>1</wait_for_async_insert>
        <async_insert_max_data_size>1000000</async_insert_max_data_size>
        <async_insert_busy_timeout_ms>10000</async_insert_busy_timeout_ms>
    </default>
</profiles>

参数详解

参数	默认值	说明
async_insert	0	是否启用异步插入
wait_for_async_insert	1	是否等待异步插入完成后再返回
async_insert_max_data_size	100000	缓冲区最大字节数（约 100KB）
async_insert_busy_timeout_ms	200	最大等待时间（毫秒）
async_insert_max_query_number	450	缓冲区最大 INSERT 数量
async_insert_stale_timeout_ms	0	数据过期时间，0 表示不限制

两种模式对比

模式1：等待模式（wait_for_async_insert = 1）

客户端 INSERT → 服务器缓冲 → 等待触发条件 → 写入磁盘 → 返回成功

• 客户端会等待数据真正写入磁盘
• 保证数据持久化
• 延迟较高但更安全

模式2：非等待模式（wait_for_async_insert = 0）

客户端 INSERT → 服务器缓冲 → 立即返回成功
                    ↓ 后台异步
              写入磁盘

• 客户端立即收到成功响应
• 不保证数据持久化
• 延迟极低但可能丢数据

异步插入监控

-- 查看异步插入统计
SELECT 
    name,
    value,
    description
FROM system.metrics
WHERE name LIKE '%async_insert%';

-- 查看异步插入队列
SELECT * FROM system.asynchronous_inserts;

-- 查看异步插入历史
SELECT 
    table,
    async_insert_time,
    async_insert_rows,
    async_insert_bytes
FROM system.asynchronous_insert_log
LIMIT 10;

异步插入适用场景

场景	是否推荐	说明
日志收集（高频小写入）	推荐	理想场景，大幅减少 Part 数量
实时数据流	推荐	结合等待模式保证可靠性
批量导入	不推荐	直接批量写入更高效
金融交易	不推荐	需要严格的事务保证

查询缓存

查询缓存（Query Cache）是 ClickHouse 23.0 引入的特性，可以缓存查询结果，避免重复计算。

启用查询缓存

-- 会话级别启用
SET use_query_cache = 1;

-- 设置缓存参数
SET query_cache_min_query_duration = 1000;  -- 只缓存执行超过 1 秒的查询
SET query_cache_min_query_runs = 3;          -- 查询执行 3 次后才缓存
SET query_cache_max_entries = 1024;          -- 最大缓存条目数
SET query_cache_max_size_in_bytes = 1073741824;  -- 最大缓存大小 1GB

-- 执行查询，结果会被缓存
SELECT 
    event_type,
    COUNT() as count
FROM events
WHERE event_time >= '2024-01-01'
GROUP BY event_type
SETTINGS use_query_cache = 1;

查询缓存工作原理

第一次查询：
SQL → 解析 → 执行 → 返回结果 → 存入缓存

第二次相同查询：
SQL → 检查缓存 → 命中 → 直接返回结果

查询是否相同判断：
- SQL 文本完全相同
- 查询设置相同
- 使用相同的数据库

缓存失效条件

以下情况会导致缓存失效：

-- 1. 表数据变更
INSERT INTO events VALUES ...;  -- 缓存失效

-- 2. 表结构变更
ALTER TABLE events ADD COLUMN ...;  -- 缓存失效

-- 3. 强制刷新
-- 使用 SETTINGS 参数控制
SELECT ... SETTINGS use_query_cache = 1, query_cache_nondeterministic_function_handling = 'save';

-- 4. 缓存时间过期
SET query_cache_ttl = 300;  -- 缓存 5 分钟后过期

查询缓存管理

-- 查看查询缓存状态
SELECT 
    name,
    value,
    description
FROM system.metrics
WHERE name LIKE '%query_cache%';

-- 查看缓存内容
SELECT 
    query,
    result_size,
    stale_reason,
    is_initial_query
FROM system.query_cache;

-- 清空查询缓存
SYSTEM DROP QUERY CACHE;

-- 禁用特定查询的缓存
SELECT ... SETTINGS use_query_cache = 0;

查询缓存最佳实践

适合缓存的场景：

• 报表查询（固定 SQL，数据更新频率低）
• 仪表板查询（频繁执行相同查询）
• 历史数据分析（数据不变）

不适合缓存的场景：

• 实时数据查询（数据频繁变更）
• 参数化查询（每次参数不同）
• 简单查询（执行本身很快）

-- 示例：报表查询缓存
-- 这是一个典型的适合缓存的场景：
-- 查询固定、数据按天更新、用户频繁刷新报表

SELECT 
    toDate(event_time) as date,
    event_type,
    COUNT() as event_count,
    uniq(user_id) as unique_users
FROM events
WHERE event_time >= '2024-01-01' AND event_time < '2024-02-01'
GROUP BY date, event_type
ORDER BY date, event_type
SETTINGS 
    use_query_cache = 1,
    query_cache_min_query_runs = 1;  -- 第一次执行就缓存

数据跳过索引详解

数据跳过索引（Data Skipping Indexes）是 ClickHouse 的重要优化特性，它可以在不读取数据的情况下判断某些数据块是否需要扫描。

索引类型详解

1. MinMax 索引

存储每个 Granule 的最小和最大值：

CREATE TABLE events_with_minmax (
    event_time DateTime,
    user_id UInt64,
    amount Float64,
    INDEX idx_amount amount TYPE minmax GRANULARITY 4
)
ENGINE = MergeTree()
ORDER BY event_time;

-- 查询时：如果查询条件是 amount > 1000
-- ClickHouse 会检查每个 Granule 的 min/max
-- 如果 max <= 1000，跳过该 Granule

适用场景：范围查询（大于、小于、区间）

2. Set 索引

存储每个 Granule 的唯一值：

CREATE TABLE events_with_set (
    event_time DateTime,
    event_type String,
    platform LowCardinality(String),
    INDEX idx_type event_type TYPE set(100) GRANULARITY 1
)
ENGINE = MergeTree()
ORDER BY event_time;

-- 参数 100 表示最多存储 100 个唯一值
-- 如果唯一值超过 100，索引变为无效状态

-- 查询时：WHERE event_type IN ('click', 'view')
-- 检查 Granule 的 Set 是否包含目标值
-- 如果不包含，跳过该 Granule

适用场景：低基数列的等值查询、IN 查询

3. Bloom Filter 索引

使用布隆过滤器判断值是否可能存在：

CREATE TABLE events_with_bloom (
    event_time DateTime,
    user_id UInt64,
    request_id String,
    INDEX idx_user user_id TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_request request_id TYPE bloom_filter(0.001) GRANULARITY 1
)
ENGINE = MergeTree()
ORDER BY event_time;

-- 参数 0.01 表示假阳性率 1%
-- 假阳性：索引判断值存在，但实际不存在（不会漏数据）
-- 假阴性：不可能发生（索引判断不存在，则一定不存在）

-- 查询时：WHERE user_id = 1001
-- 布隆过滤器判断该 Granule 是否可能包含 user_id = 1001
-- 如果判断不存在，跳过该 Granule

适用场景：高基数列的等值查询

4. Token Bloom Filter 索引

对文本进行分词后建立布隆过滤器：

CREATE TABLE logs_with_tokenbf (
    log_time DateTime,
    message String,
    INDEX idx_message message TYPE tokenbf_v1(512, 3, 0) GRANULARITY 1
)
ENGINE = MergeTree()
ORDER BY log_time;

-- 参数说明：
-- 512: 布隆过滤器大小（字节）
-- 3: 哈希函数数量
-- 0: 随机种子

-- 分词规则：按非字母数字字符分割
-- "Hello World 2024" → ["Hello", "World", "2024"]

-- 查询时：WHERE message LIKE '%error%'
-- 检查布隆过滤器是否可能包含 "error" token

适用场景：文本搜索、LIKE 查询

5. N-gram Bloom Filter 索引

按 N-gram 切分文本建立布隆过滤器：

CREATE TABLE logs_with_ngrambf (
    log_time DateTime,
    message String,
    INDEX idx_message message TYPE ngrambf_v1(4, 256, 2, 0) GRANULARITY 1
)
ENGINE = MergeTree()
ORDER BY log_time;

-- 参数说明：
-- 4: n-gram 大小
-- 256: 布隆过滤器大小（字节）
-- 2: 哈希函数数量
-- 0: 随机种子

-- "hello" → ["hell", "ello"]

-- 查询时：WHERE message LIKE '%error%'
-- 检查布隆过滤器是否可能包含 "erro" 或 "rror" n-gram

适用场景：子串搜索、更精确的文本搜索

索引选择指南

索引类型	适用列类型	适用查询	存储开销
MinMax	数值、日期	范围查询	极低
Set	低基数列	等值、IN	取决于基数
Bloom Filter	高基数列	等值	中等
Token Bloom Filter	文本	LIKE、全文	中等
N-gram Bloom Filter	文本	子串搜索	较高

GRANULARITY 参数

GRANULARITY 参数控制索引的粒度：

-- GRANULARITY = 1（默认）：每个 Granule 建一个索引项
-- GRANULARITY = 4：每 4 个 Granule 建一个索引项

INDEX idx_amount amount TYPE minmax GRANULARITY 4

-- Granule 大小由 index_granularity 控制（默认 8192 行）
-- GRANULARITY = 4 意味着索引覆盖 4 * 8192 = 32768 行

选择建议：

• 数据分布均匀：GRANULARITY = 1 或 2
• 数据分布不均匀：增大 GRANULARITY
• 索引存储空间有限：增大 GRANULARITY

索引使用示例

-- 创建带有多种索引的表
CREATE TABLE optimized_events (
    event_time DateTime,
    user_id UInt64,
    event_type LowCardinality(String),
    platform LowCardinality(String),
    amount Float64,
    request_id String,
    error_message String,
    
    -- 主键索引（排序键）
    PRIMARY KEY (event_time, user_id),
    
    -- 数据跳过索引
    INDEX idx_type event_type TYPE set(0) GRANULARITY 1,           -- 低基数等值
    INDEX idx_platform platform TYPE set(0) GRANULARITY 1,          -- 低基数等值
    INDEX idx_amount amount TYPE minmax GRANULARITY 4,              -- 数值范围
    INDEX idx_request request_id TYPE bloom_filter(0.01) GRANULARITY 1,  -- 高基数等值
    INDEX idx_error error_message TYPE tokenbf_v1(512, 3, 0) GRANULARITY 1  -- 文本搜索
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);

-- 查询示例：利用多种索引
SELECT event_time, user_id, amount
FROM optimized_events
WHERE event_time >= '2024-01-01'                -- 使用主键索引
  AND event_type = 'click'                      -- 使用 idx_type 索引
  AND platform IN ('web', 'mobile')             -- 使用 idx_platform 索引
  AND amount > 100                              -- 使用 idx_amount 索引
  AND error_message LIKE '%timeout%';           -- 使用 idx_error 索引

-- 查看索引使用情况
EXPLAIN indexes = 1
SELECT * FROM optimized_events WHERE amount > 100;

集群部署

集群架构

ClickHouse 集群采用无共享架构，每个节点独立运行：

┌─────────────────────────────────────────────────────────────────────┐
│                        ClickHouse Cluster                            │
│                                                                      │
│  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐      │
│  │    Node 1       │  │    Node 2       │  │    Node 3       │      │
│  │  ┌───────────┐  │  │  ┌───────────┐  │  │  ┌───────────┐  │      │
│  │  │ Shard 1   │  │  │  │ Shard 2   │  │  │  │ Shard 3   │  │      │
│  │  │ Replica 1 │  │  │  │ Replica 1 │  │  │  │ Replica 1 │  │      │
│  │  └───────────┘  │  │  └───────────┘  │  │  └───────────┘  │      │
│  └─────────────────┘  └─────────────────┘  └─────────────────┘      │
│                                                                      │
│  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐      │
│  │    Node 4       │  │    Node 5       │  │    Node 6       │      │
│  │  ┌───────────┐  │  │  ┌───────────┐  │  │  ┌───────────┐  │      │
│  │  │ Shard 1   │  │  │  │ Shard 2   │  │  │  │ Shard 3   │  │      │
│  │  │ Replica 2 │  │  │  │ Replica 2 │  │  │  │ Replica 2 │  │      │
│  │  └───────────┘  │  │  └───────────┘  │  │  └───────────┘  │      │
│  └─────────────────┘  └─────────────────┘  └─────────────────┘      │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘
                              │
                              ▼
                    ┌─────────────────┐
                    │   ZooKeeper     │
                    └─────────────────┘

核心概念：

• Shard（分片）：数据水平切分，每个分片存储部分数据
• Replica（副本）：数据冗余，保证高可用

配置集群

1. 配置 ZooKeeper

<!-- /etc/clickhouse-server/config.d/zookeeper.xml -->
<clickhouse>
    <zookeeper>
        <node index="1">
            <host>zk1.example.com</host>
            <port>2181</port>
        </node>
        <node index="2">
            <host>zk2.example.com</host>
            <port>2181</port>
        </node>
        <node index="3">
            <host>zk3.example.com</host>
            <port>2181</port>
        </node>
    </zookeeper>
</clickhouse>

2. 配置集群

<!-- /etc/clickhouse-server/config.d/clusters.xml -->
<clickhouse>
    <remote_servers>
        <my_cluster>
            <!-- 分片1 -->
            <shard>
                <replica>
                    <host>node1.example.com</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>node4.example.com</host>
                    <port>9000</port>
                </replica>
            </shard>
            <!-- 分片2 -->
            <shard>
                <replica>
                    <host>node2.example.com</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>node5.example.com</host>
                    <port>9000</port>
                </replica>
            </shard>
            <!-- 分片3 -->
            <shard>
                <replica>
                    <host>node3.example.com</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>node6.example.com</host>
                    <port>9000</port>
                </replica>
            </shard>
        </my_cluster>
    </remote_servers>
</clickhouse>

3. 创建分布式表

-- 在每个节点上创建本地表
CREATE TABLE events_local ON CLUSTER my_cluster (
    event_time DateTime,
    user_id UInt64,
    event_type String
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{database}/events_local/{shard}',
    '{replica}'
)
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

-- 创建分布式表
CREATE TABLE events_dist ON CLUSTER my_cluster (
    event_time DateTime,
    user_id UInt64,
    event_type String
)
ENGINE = Distributed(my_cluster, default, events_local, rand());

-- 通过分布式表写入
INSERT INTO events_dist VALUES ('2024-01-15 10:00:00', 1001, 'click');

-- 通过分布式表查询
SELECT COUNT() FROM events_dist;

分布式 DDL

使用 ON CLUSTER 子句在所有节点执行 DDL：

-- 在集群所有节点创建表
CREATE TABLE events_local ON CLUSTER my_cluster (
    event_time DateTime,
    user_id UInt64,
    event_type String
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{database}/events_local/{shard}',
    '{replica}'
)
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

-- 在集群所有节点修改表
ALTER TABLE events_local ON CLUSTER my_cluster 
ADD COLUMN session_id String AFTER user_id;

-- 在集群所有节点删除表
DROP TABLE events_local ON CLUSTER my_cluster;

数据复制

ReplicatedMergeTree 自动同步数据：

-- 写入任一副本，自动同步到其他副本
INSERT INTO events_local VALUES ('2024-01-15', 1001, 'click');

-- 查看副本状态
SELECT 
    database,
    table,
    engine,
    replica_name,
    replica_path,
    total_replicas,
    active_replicas
FROM system.replicas;

-- 手动触发同步
SYSTEM SYNC REPLICA events_local;

与其他系统集成

Kafka 集成

使用 ClickHouse 的 Kafka 表引擎或 Kafka Connect：

-- 使用 Kafka 表引擎
CREATE TABLE events_kafka (
    event_time DateTime,
    user_id UInt64,
    event_type String
)
ENGINE = Kafka()
SETTINGS 
    kafka_broker_list = 'kafka1:9092,kafka2:9092',
    kafka_topic_list = 'events',
    kafka_group_name = 'clickhouse_consumer',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 4;

-- 创建物化视图消费数据
CREATE MATERIALIZED VIEW events_consumer
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id)
AS SELECT * FROM events_kafka;

Spark 集成

使用 ClickHouse 的 JDBC 驱动：

import org.apache.spark.sql.SparkSession

val spark = SparkSession.builder()
    .appName("ClickHouse Integration")
    .getOrCreate()

// 读取 ClickHouse
val df = spark.read
    .format("jdbc")
    .option("url", "jdbc:clickhouse://clickhouse:8123/default")
    .option("dbtable", "events")
    .option("user", "default")
    .option("password", "")
    .load()

// 写入 ClickHouse
df.write
    .format("jdbc")
    .option("url", "jdbc:clickhouse://clickhouse:8123/default")
    .option("dbtable", "events_output")
    .option("user", "default")
    .option("password", "")
    .mode("append")
    .save()

Flink 集成

使用 Flink CDC 连接器：

-- Flink SQL
CREATE TABLE events_source (
    event_time TIMESTAMP(3),
    user_id BIGINT,
    event_type STRING
) WITH (
    'connector' = 'mysql-cdc',
    'hostname' = 'mysql',
    'port' = '3306',
    'username' = 'root',
    'password' = 'password',
    'database-name' = 'app_db',
    'table-name' = 'events'
);

CREATE TABLE events_sink (
    event_time TIMESTAMP(3),
    user_id BIGINT,
    event_type STRING
) WITH (
    'connector' = 'clickhouse',
    'url' = 'clickhouse://clickhouse:8123',
    'database-name' = 'default',
    'table-name' = 'events',
    'sink.batch-size' = '1000'
);

INSERT INTO events_sink SELECT * FROM events_source;

对象存储集成

ClickHouse 可以直接查询对象存储（S3、OSS 等）：

-- 查询 S3 数据
SELECT *
FROM s3(
    'https://bucket.s3.amazonaws.com/data/*.parquet',
    'access_key',
    'secret_key',
    'Parquet'
)
LIMIT 10;

-- 写入 S3
INSERT INTO FUNCTION s3(
    'https://bucket.s3.amazonaws.com/output/data.parquet',
    'access_key',
    'secret_key',
    'Parquet'
)
SELECT * FROM events;

-- 创建 S3 表
CREATE TABLE events_s3 (
    event_time DateTime,
    user_id UInt64,
    event_type String
)
ENGINE = S3(
    'https://bucket.s3.amazonaws.com/events/*.parquet',
    'access_key',
    'secret_key',
    'Parquet'
);

运维管理

监控指标

关键监控指标：

类别	指标	说明
性能	queries_per_second	每秒查询数
	inserted_rows	插入行数
	selected_rows	查询扫描行数
	query_duration	查询耗时
存储	parts_count	Part 数量
	parts_delayed	延迟合并的 Part 数
	disk_usage	磁盘使用量
内存	memory_usage	内存使用量
	memory_tracking	内存跟踪

-- 查询系统表
-- 查看当前查询
SELECT 
    query_id,
    user,
    query_duration_ms,
    memory_usage,
    read_rows,
    written_rows
FROM system.processes;

-- 查看查询历史
SELECT 
    query,
    query_duration_ms,
    read_rows,
    memory_usage,
    exception
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date = today()
ORDER BY query_duration_ms DESC
LIMIT 10;

-- 查看表状态
SELECT 
    database,
    table,
    rows,
    bytes_on_disk,
    parts_count
FROM system.tables
WHERE engine LIKE '%MergeTree%';

-- 查看 Part 合并状态
SELECT 
    table,
    count() as parts,
    sum(rows) as total_rows,
    sum(bytes_on_disk) as total_bytes
FROM system.parts
WHERE active = 1
GROUP BY table;

备份与恢复

# 使用 clickhouse-backup 工具
# 安装
curl -s https://packagecloud.io/install/repositories/Altinity/clickhouse-backup/script.deb.sh | sudo bash
sudo apt-get install -y clickhouse-backup

# 创建备份
clickhouse-backup create

# 列出备份
clickhouse-backup list

# 恢复备份
clickhouse-backup restore <backup_name>

# 上传到 S3
clickhouse-backup upload <backup_name>

常见问题排查

问题1：查询慢

-- 查看慢查询
SELECT 
    query,
    query_duration_ms / 1000 as duration_sec,
    read_rows,
    memory_usage / 1024 / 1024 as memory_mb
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 60000  -- 超过 60 秒
ORDER BY query_duration_ms DESC
LIMIT 10;

-- 分析查询执行计划
EXPLAIN PIPELINE
SELECT * FROM events WHERE user_id = 1001;

-- 查看查询 Profile
SET allow_introspection_functions = 1;
SELECT 
    query,
    ProfileEvents['OSCPUVirtualTimeMicroseconds'] as cpu_time_us,
    ProfileEvents['OSReadBytes'] as read_bytes,
    ProfileEvents['OSWriteBytes'] as write_bytes
FROM system.query_log
WHERE query_id = '<query_id>';

问题2：Part 合并慢

-- 查看 Part 数量
SELECT 
    table,
    count() as parts,
    sum(rows) as total_rows
FROM system.parts
WHERE active = 1
GROUP BY table
ORDER BY parts DESC;

-- 手动触发合并
OPTIMIZE TABLE events FINAL;

-- 查看合并任务
SELECT * FROM system.merges;

-- 调整合并线程数
SET background_pool_size = 16;

问题3：内存不足

-- 查看内存使用
SELECT 
    query_id,
    query,
    memory_usage / 1024 / 1024 as memory_mb
FROM system.processes
ORDER BY memory_usage DESC;

-- 杀掉查询
KILL QUERY WHERE query_id = '<query_id>';

-- 限制内存使用
SET max_memory_usage = 10000000000;  -- 10GB

最佳实践

主键设计原则

主键（ORDER BY）是 ClickHouse 性能优化的关键，设计时需要考虑：

1. 选择查询频繁的列

-- 场景：经常按时间范围查询，然后按用户聚合
-- 好：时间在前，用户在后
ORDER BY (event_time, user_id)

-- 差：用户在前，时间在后（无法利用时间范围查询的索引）
ORDER BY (user_id, event_time)

2. 考虑基数

-- 基数太高（如 UUID）：索引效率低，不建议作为主键第一列
-- 基数太低（如布尔值）：过滤效果差
-- 建议：中等基数的列作为主键第一列（如日期、地区）

-- 好
ORDER BY (date, user_id)

-- 差（UUID 基数太高）
ORDER BY (uuid, date)

-- 差（is_active 基数太低）
ORDER BY (is_active, date, user_id)

3. 使用降序排序键（ClickHouse 24.0+）

-- 场景：TopN 查询，需要最近的数据
ENGINE = MergeTree()
ORDER BY (event_time DESC, user_id);  -- 降序排列

-- 这样查询最新数据时可以减少扫描量
SELECT * FROM events ORDER BY event_time DESC LIMIT 100;

写入最佳实践

1. 批量写入

-- 推荐：每次写入 1000-100000 行
-- 太小：Part 数量过多，合并压力大
-- 太大：内存占用高，写入延迟增加

-- 好：批量插入
INSERT INTO events VALUES 
    ('2024-01-15 10:00:00', 1001, 'click', 'web'),
    ('2024-01-15 10:01:00', 1002, 'view', 'mobile'),
    -- ... 更多行
    ('2024-01-15 11:00:00', 1999, 'click', 'web');

-- 差：单行插入
INSERT INTO events VALUES ('2024-01-15 10:00:00', 1001, 'click', 'web');
INSERT INTO events VALUES ('2024-01-15 10:01:00', 1002, 'view', 'mobile');

2. 数据预排序

-- 写入前按主键排序，减少后台合并开销
INSERT INTO events 
SELECT * FROM staging_table 
ORDER BY (event_time, user_id);

3. 避免小分区

-- 差：分区粒度太细
PARTITION BY (toDate(event_time), event_type)  -- 每天每种类型一个分区

-- 好：分区粒度适中
PARTITION BY toYYYYMM(event_time)  -- 每月一个分区

查询最佳实践

1. 使用分区裁剪

-- 好：使用分区键过滤
SELECT * FROM events
WHERE event_time >= '2024-01-01' AND event_time < '2024-02-01';

-- 差：使用函数无法利用分区裁剪
SELECT * FROM events
WHERE toYYYYMM(event_time) = 202401;

2. 使用 PREWHERE

-- 对于过滤性强且涉及主键的条件，使用 PREWHERE
SELECT user_id, event_type
FROM events
PREWHERE event_time >= '2024-01-01'
WHERE event_type = 'click';

3. 避免FINAL

-- 差：FINAL 会强制合并所有 Part，性能极差
SELECT * FROM user_profiles FINAL;

-- 好：使用 argMax 获取最新值
SELECT 
    user_id,
    argMax(name, update_time) as name,
    argMax(email, update_time) as email
FROM user_profiles
GROUP BY user_id;

4. 使用物化视图预聚合

-- 创建物化视图进行预聚合
CREATE MATERIALIZED VIEW daily_stats_mv
ENGINE = SummingMergeTree()
ORDER BY (day, event_type)
AS SELECT 
    toDate(event_time) as day,
    event_type,
    count() as event_count,
    uniqState(user_id) as unique_users,
    sumState(revenue) as total_revenue
FROM events
GROUP BY day, event_type;

-- 查询物化视图（快速）
SELECT 
    day,
    event_type,
    event_count,
    uniqMerge(unique_users) as unique_users,
    sumMerge(total_revenue) as total_revenue
FROM daily_stats_mv
GROUP BY day, event_type, event_count;

内存管理

1. 设置内存限制

-- 查询级内存限制
SET max_memory_usage = 10000000000;  -- 10GB

-- 用户级内存限制（在 users.xml 中配置）
<profiles>
    <default>
        <max_memory_usage>10000000000</max_memory_usage>
    </default>
</profiles>

2. 使用外部排序

-- 当数据量超过内存时，使用磁盘排序
SET max_bytes_before_external_sort = 100000000;  -- 100MB

-- 外部聚合
SET max_bytes_before_external_group_by = 200000000;  -- 200MB

监控告警

关键指标监控：

指标	说明	告警阈值
CurrentMetric_Query	当前执行查询数	接近 max_concurrent_queries
CurrentMetric_Merge	当前合并任务数	持续高位
PartsActive	活跃 Part 数量	单表超过 300
ReplicasQueue	副本同步队列长度	持续增长
MemoryTracking	内存使用量	接近物理内存

慢查询监控：

-- 查看慢查询
SELECT 
    query,
    query_duration_ms / 1000 as duration_sec,
    read_rows,
    memory_usage / 1024 / 1024 as memory_mb,
    exception
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 60000
  AND event_date = today()
ORDER BY query_duration_ms DESC
LIMIT 20;

小结

本章详细介绍了 ClickHouse 列式 OLAP 数据库的核心概念和使用方法：

1. 架构设计：列式存储、向量化执行、稀疏索引、LSM-Tree 架构
2. 表引擎：MergeTree 家族引擎，包括 ReplacingMergeTree、SummingMergeTree 等
3. 数据类型：数值、字符串、日期时间、复合类型
4. SQL 语法：查询、聚合、窗口函数、常用函数
5. 索引与分区：主键索引、二级索引、分区策略、TTL
6. 性能优化：写入优化、查询优化、内存优化、表设计优化
7. 集群部署：分片、副本、分布式表、复制机制
8. 系统集成：Kafka、Spark、Flink、对象存储集成
9. 运维管理：监控指标、备份恢复、问题排查

ClickHouse 以其极致的查询性能，成为大数据分析领域的首选 OLAP 数据库。在实际项目中，ClickHouse 常与 Kafka、Spark、Flink 等组件配合使用，构建完整的实时数据分析平台。

参考资源

• ClickHouse 官方文档
• ClickHouse Architecture
• MergeTree Engine Family
• ClickHouse Performance Guide