ClickHouse详细教程
文章目录
- @[toc]
- 一、ClickHouse 概述
-
- 二、ClickHouse 查询引擎原理
-
- 三、存储引擎——MergeTree 家族
-
- 四、集群架构与分布式
-
- 五、ClickHouse 使用指南
-
- 六、建表优化（按重要程度排序）
-
- 七、查询调优实战
-
- 八、写入最佳实践
-
- 九、为什么不适合多表 JOIN
-
- 十、ClickHouse vs ElasticSearch
- 十一、服务器资源配置
-
- 十二、调优优先级总结
- 十三、ClickHouse 能处理的数据量级
- 十四、快速上手——本地安装
- 参考资料
文章目录
- @[toc]
- 一、ClickHouse 概述
- 二、ClickHouse 查询引擎原理
- 三、存储引擎——MergeTree 家族
- 四、集群架构与分布式
- 五、ClickHouse 使用指南
- 六、建表优化（按重要程度排序）
- 七、查询调优实战
- 八、写入最佳实践
- 九、为什么不适合多表 JOIN
- 十、ClickHouse vs ElasticSearch
- 十一、服务器资源配置
- 十二、调优优先级总结
- 十三、ClickHouse 能处理的数据量级
- 十四、快速上手——本地安装
- 参考资料
一、ClickHouse 概述
1.1 什么是 ClickHouse
ClickHouse 是俄罗斯 Yandex 于 2016 年开源的高性能列式存储 SQL 数据库，专为在线分析处理（OLAP）场景设计。官方定位是：最快且最节省资源的开源实时分析数据库。
ClickHouse 的核心定位是 OLAP 分析引擎，区别于传统的 OLTP 事务型数据库（如 MySQL、PostgreSQL）。理解这两者的区别是学习 ClickHouse 的起点：
- OLTP（On-Line Transaction Processing）：联机事务处理，注重事务的原子性和一致性，主要应用于事件的实时记录。典型实现：Oracle、PostgreSQL、MySQL。
- OLAP（On-Line Analytical Processing）：联机分析处理，注重海量数据聚合查询，主要应用于大数据统计分析。典型实现：Hive、ClickHouse、Doris。
1.2 行式存储 vs 列式存储
行式存储：数据库以行作为存储单位，一行内所有字段数据存在一起。查询引擎每次都会从存储介质中将一行内所有数据读取出来，之后在内存中过滤不需要的字段。当查询命中的数据量非常大而只需要少量列时，IO 资源会被大量无用数据读取所占用。
列式存储：数据库以列为存储单位，把所有数据的某一列串在一起存储。一张表某一列的所有数据在存储介质上顺序存放，理论上一次 IO 可以读取整列所有数据，且不包含其他列的数据。
行存（MySQL）：
[id=1, name=Alice, age=25, city=BJ] [id=2, name=Bob, age=30, city=SH] ...
列存（ClickHouse）：
id列: [1, 2, 3, 4, ...]
name列: [Alice, Bob, ...]
age列: [25, 30, ...]
city列: [BJ, SH, ...]
列存的优点：只读需要的列大幅减少 IO；每列可根据数据类型单独设置压缩算法，提高物理存储利用率；查询时可并发处理各列。
列存的缺点：插入更新慢，不适合含有频繁删除和更新的实时操作。
1.3 ClickHouse 核心特性
- 列式存储与列式计算：使用列式存储后计算也是列式计算（向量化计算），单条 SQL 可充分利用服务器多线程能力加速查询。但也会降低并发性能，官方建议单副本 QPS 为 100 以下。
- 单 SQL 查询极快：在一亿级数据量下，ClickHouse 比 Vertica 约快 5 倍，比 Hive 快 279 倍，比 MySQL 快 801 倍。
- 灵活的分布式架构：支持线性扩展、数据分片、多副本存储。
- 多种库表引擎：支持多种引擎实现不同的存储特性。需注意 UPDATE、DELETE、ALTER 操作只有 MergeTree 系列引擎才支持，且是非常重的操作。
- 高实时性：实时存储，实时查询。
- 典型 OLAP 特征：不支持事务，不擅长多表 JOIN，要求批量写入。
1.4 适用场景
ClickHouse 适用于以下条件的场景：
- 业务不需要事务（原子性、持久性、一致性、隔离性），可允许最终一致性
- 对于简单查询，允许大约 50 毫秒的延迟
- 数据需要以大批次（大于 1000 行）进行更新，而不是单行更新
- 表很"宽"，包含大量列
- 读取数据时，从数据库中提取大量行，但只用到少量列
- 查询频率相对较低（通常每台服务器每秒百次或更少）
- 每次查询只会查询一个大表，关联查询的其它表都是小表（维度表）
- 查询结果显著小于数据源（数据有过滤或聚合）
1.5 ClickHouse 不适合的场景
- 点查（
WHERE id = 123）：稀疏索引定位不精确，不如 MySQL - 频繁更新/删除：MergeTree 设计上是追加写，UPDATE/DELETE 代价很高
- 复杂 JOIN：右表必须能放进内存
- 需要完整事务：基本没有 ACID 保证
- 高并发小查询：单查询消耗资源大，并发能力有限
二、ClickHouse 查询引擎原理
2.1 查询的生命周期
SQL文本
↓
Parser（词法/语法解析）
↓
AST（抽象语法树）
↓
Analyzer（语义分析 + 类型推断）
↓
Query Plan（逻辑执行计划）
↓
Query Pipeline（物理执行计划）
↓
执行引擎（多线程 + SIMD）
↓
结果返回
2.2 为什么 ClickHouse 快——四个层次
ClickHouse 能做到"亿级数据秒级响应"，不仅仅是因为列存，而是四个层次共同发力：
第一层：列式存储 → IO 效率
假设一张表有 100 列，查询只用到 3 列。行存的有效 IO 占比只有 3%（97% 浪费），列存有效 IO 占比 100%。
第二层：压缩 → 减少 IO 量
列存的压缩率极高。同一列的数据类型相同、值域相近，压缩算法（字典编码、RLE、Delta 等）效果极好。压缩后数据更小，同样的磁盘 IO 能读到更多有效数据。
第三层：向量化执行 → CPU 效率
ClickHouse 使用向量化执行（Vectorized Execution），每次处理一个 Block（默认 65536 行），对整个 Block 做批量运算，配合 SIMD 指令（AVX2，256 位寄存器），一条指令同时处理 8 个 int32，实际吞吐量提升 4-8 倍。
传统火山模型：每次 next() 返回一行数据，函数调用开销大
向量化模型：每次处理一整个 Block，批量运算，CPU 利用率极高
第四层：多线程并行 → 充分利用多核
ClickHouse 会把一张表的多个 Part 分配给多个线程并行读取，每个线程独立执行 Filter/Aggregate，最后合并。单个查询可以把所有 CPU 核心跑满。
2.3 QueryPipeline 模型
ClickHouse 使用 Push 模型（Pipeline/流水线模型），数据从 Source 出发主动"推"向下游算子：
Source (读取 MergeTree Part) × N个并行
↓
FilterTransform (WHERE 条件过滤)
↓
AggregatingTransform (GROUP BY 聚合，每个线程本地聚合)
↓
MergingAggregatedTransform (合并局部结果)
↓
Sink (输出结果)
2.4 聚合执行的极致优化
GROUP BY 是 OLAP 查询的核心操作，ClickHouse 在这里做了大量优化。
两阶段聚合：每个线程先本地聚合，生成局部 HashTable，最后合并所有线程的结果，没有锁竞争。
HashTable 的 key 类型特化：根据 GROUP BY key 的类型选择不同实现——UInt8 用数组（O(1) 无碰撞）、UInt64 用特化的开放寻址 HashTable、String 用带 inline 优化的 HashTable。避免了泛型带来的虚函数开销。
2.5 列存如何关联各列数据
列存里没有显式的"行 ID"字段来关联各列，完全依赖物理位置（下标）隐式对应：所有列文件的第 N 个元素永远对应同一行数据。
过滤时用 Bitmap（Filter Column）记录哪些位置满足条件，多列同步用同一个 Bitmap 过滤，天然保持对应关系。
三、存储引擎——MergeTree 家族
3.1 MergeTree 核心设计
MergeTree 是 ClickHouse 最核心的存储引擎，其他引擎（ReplacingMergeTree、SummingMergeTree 等）都是它的变体。
核心设计哲学：
- 不可变性：已写入的 Part 不会被修改，只会被新 Part 替换
- 追加写：所有写入都是生成新文件，没有随机写
- 异步整理：数据的排序、去重、聚合都在后台 Merge 时完成
- 最终一致：查询时数据可能处于"未完全合并"状态
这个设计和 LSM-Tree 思想高度一致，用写入的简单性换取极高的写入吞吐。
3.2 物理存储结构
表目录结构：
/data/default/orders/
├── 20240101_1_1_0/ ← Part目录（分区_最小块_最大块_合并层级）
│ ├── data.bin ← 各列的压缩数据
│ ├── data.mrk3 ← 列的标记文件（记录granule偏移量）
│ ├── primary.idx ← 稀疏索引文件
│ ├── minmax_dt.idx ← 分区键的minmax索引
│ └── columns.txt ← 列名和类型
├── 20240101_2_2_0/ ← 另一个Part
├── 20240101_1_2_1/ ← 合并后的Part（层级变为1）
└── detached/ ← 损坏或手动分离的Part
三个关键文件的关系：
- primary.idx（稀疏索引）：记录每个 granule 的第一行的排序键值，很小，查询时全部加载进内存
- data.mrk3（标记文件）：记录每个 granule 在 data.bin 中的字节偏移量，是稀疏索引和数据文件之间的桥梁
- data.bin（数据文件）：实际的列数据，压缩存储，按 granule 分块，每块独立压缩
查询定位过程：
WHERE dt = '2024-01-01' AND city = 'BJ'
↓
primary.idx 二分查找 → 定位到 granule 0
↓
data.mrk3 查找 granule 0 的偏移量 → offset=0
↓
data.bin 从 offset=0 读取一个granule的数据（8192行）
↓
在内存中精确过滤
3.3 Part 的生命周期
写入阶段：每次 INSERT 生成新 Part（内部排序、压缩、建索引），Part 命名格式为 {分区}_{minBlock}_{maxBlock}_{level}，level=0 表示刚写入未经合并。
合并阶段（后台异步）：选择同一分区内的相邻小 Part，读取所有数据重新排序合并，生成新的大 Part（level+1），删除旧的小 Part。
触发条件：Part 数量超过阈值（默认每个分区超过 10 个 Part 触发）、定时触发、手动触发 OPTIMIZE TABLE orders FINAL。
3.4 稀疏索引（Primary Key Index）
这是 ClickHouse 和 MySQL 索引最大的区别：
MySQL B+Tree：每行都有索引项，精确定位到行
ClickHouse 稀疏索引：每 8192 行（一个 granule）记录一个索引项
稀疏索引只建在 PRIMARY KEY（默认等同于 ORDER BY）指定的列上。索引能发挥作用的前提是数据在这些列上是有序的。
为什么不给每列都建稀疏索引：一份数据只能有一种物理排列顺序，非排序列的数据是乱序的，对其建稀疏索引无意义。
3.5 数据分区（Partition）
PARTITION BY toYYYYMM(event_time)
查询时如果带上分区键过滤条件，直接跳过不相关分区，相当于物理级别的剪枝。
3.6 跳数索引（Skip Index）
对非排序键字段的过滤加速。不要求数据有序，而是记录每个 granule 范围内的统计信息：
- minmax：记录每个范围的最小值和最大值，适合范围查询
- set(N)：记录范围内不同值的集合（最多 N 个），适合低基数字段等值查询
- bloom_filter：布隆过滤器，适合高基数字段的等值查询
- ngrambf_v1：N-gram 布隆过滤器，用于 LIKE 查询
INDEX idx_age age TYPE minmax GRANULARITY 4
-- 每4个granule记录一次age的min/max
3.7 局部有序与后台 Merge
ClickHouse 不是"插入时保证全局有序"，而是每批写入内部有序，后台异步合并成全局有序。
写入时：追加新Part，保证局部有序，不破坏已有数据
查询时：多Part并行查，每个Part用自己的稀疏索引加速
后台时：异步Merge，逐渐把局部有序变成全局有序
这就是为什么列存不适合频繁插入删除的根本原因——列存的性能优势（有序性+压缩）和随机写入天然冲突，工程上用"追加写+后台合并"来绕开。
3.8 MergeTree 的变体引擎
ReplacingMergeTree（去重）：
ENGINE = ReplacingMergeTree(version)
ORDER BY (user_id, dt)
-- 相同ORDER BY key的行，Merge时只保留version最大的那行
-- 注意：去重是异步的，查询时可能有重复，需要用 FINAL 关键字
SummingMergeTree（自动累加）：
ENGINE = SummingMergeTree()
ORDER BY (dt, city)
-- 相同ORDER BY key的行，Merge时数值列自动累加
-- 专门为物化视图的目标表设计
AggregatingMergeTree（通用聚合）：
ENGINE = AggregatingMergeTree()
ORDER BY (dt, city)
-- 比SummingMergeTree更通用，支持任意聚合函数
-- 配合AggregateFunction类型使用
CollapsingMergeTree（折叠删除）：
ENGINE = CollapsingMergeTree(sign)
-- sign=1 表示插入，sign=-1 表示删除
-- Merge时 +1 和 -1 的行互相抵消
-- 用于模拟UPDATE/DELETE操作
VersionedCollapsingMergeTree：解决了 CollapsingMergeTree 存在乱序写入情况下无法正常删除的问题。
四、集群架构与分布式
4.1 整体架构
一张逻辑表由 Distributed 表和存储实际数据的 local 表组成：
- Distributed 表：在所有节点上均存在，功能相同，用户操作数据时连接到任意一台服务器上的 Distributed 表，由该表根据配置去操作 local 表中的数据。
- local 表：实际存储数据的表，可以进行分片。每个分片保存不同的数据，同分片的副本保存相同数据，起到分流查询和数据备份的作用。
<!-- 多分片多副本配置示例 -->
<clickhouse_remote_servers>
<clusterA>
<shard>
<replica><host>linux01</host><port>9000</port></replica>
<replica><host>linux02</host><port>9000</port></replica>
</shard>
<shard>
<replica><host>linux03</host><port>9000</port></replica>
<replica><host>linux04</host><port>9000</port></replica>
</shard>
</clusterA>
</clickhouse_remote_servers>
4.2 Distributed 表引擎
Distributed 表自身不存储任何数据，作为数据分片的透明代理，能够自动路由数据至集群各节点。
CREATE TABLE demo_all ON CLUSTER clusterA (
id Int8,
ctime DateTime,
cost Decimal(10,2)
) ENGINE = Distributed('clusterA', 'default', 'localTable', cityHash64(id));
-- Distributed(集群名, 库名, local表名, 分片方法)
分片方法：random（随机）、constant（固定）、column value（按列值 hash）、自定义表达式。
4.3 Distributed 表写入流程
- 客户端连接到节点 A，向 Distributed 表批量写入数据
- 对每行数据计算分片键，决定数据属于哪个分片
- 属于本机分片的数据直接写入本地 MergeTree
- 属于远端分片的数据写入节点 A 的临时目录
- 后台线程异步将临时目录中的数据推送给目标分片节点
- 目标节点收到数据写入本地 MergeTree
- 确认收到后删除临时文件
这个流程的问题：
- 可靠性差（节点宕机时临时目录数据丢失）
- 接收节点成为瓶颈
- 双写磁盘
- 网络放大（
internal_replication = false时）
4.4 生产环境最佳实践：写入走本地表，查询走 Distributed 表
写入：业务方自己计算分片路由，直接连接目标分片节点写入local表
查询：通过 Distributed 表广播子查询给所有分片，汇总结果返回
这是职责分离的合理设计：写入路径追求可靠性和性能，查询路径追求便利性和完整性。
多副本集群优化：在 shard 配置中设置 internal_replication = true，所有表使用 Replicated-MergeTree 系列引擎，通过 ZooKeeper 让其他副本拉取数据。
4.5 分布式查询执行流程
Client → 任意一个节点（Coordinator）
↓
解析 SQL，生成分布式执行计划
↓
向所有 Shard 发送子查询
↓
[Shard1] [Shard2] [Shard3]
本地聚合 本地聚合 本地聚合
↓
Coordinator 汇总结果
↓
返回 Client
每个 Shard 先本地 GROUP BY，返回局部结果给 Coordinator，Coordinator 做最终合并，网络传输量远小于原始数据量。
4.6 Replicated-MergeTree 引擎
Replicated-MergeTree 需要在集群内不同节点上创建一样的表作为副本，同步对表的修改。使用 ZooKeeper 作为协调服务。
数据插入流程：
- 数据写入内存缓冲区，再写入 tmp 临时目录
- 临时目录重命名为正式分区目录
- 修改 ZooKeeper 节点，通知其他副本
- 其他副本拉取最新数据完成复制
会同步的操作：INSERT、MERGE、MUTATION、ALTER。不会同步的操作：SELECT、CREATE、DROP、RENAME。
五、ClickHouse 使用指南
5.1 连接方式
# 命令行客户端
clickhouse-client --host 127.0.0.1 --port 9000 -u default --password xxx
# HTTP接口
curl 'http://localhost:8123/?query=SELECT+1'
# JDBC（Java项目）
# 驱动：ru.yandex.clickhouse:clickhouse-jdbc
5.2 基本建表
CREATE TABLE orders (
order_id UInt64,
user_id UInt64,
city LowCardinality(String),
amount Float64,
status UInt8,
created_at DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, user_id)
SETTINGS index_granularity = 8192;
5.3 基本查询
-- 普通聚合
SELECT city, count(), sum(amount)
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY city
ORDER BY sum(amount) DESC
LIMIT 10;
-- ClickHouse特有的函数
SELECT
toYYYYMMDD(created_at) AS dt,
uniqExact(user_id) AS uv, -- 精确去重
uniq(user_id) AS uv_approx -- 近似去重，更快
FROM orders
GROUP BY dt;
5.4 物化视图（Materialized View）
物化视图不是"查询时实时计算的视图"，而是一个真实存储数据的表 + 一个自动触发的写入钩子。写入源表时自动触发，把计算结果写入另一张表，查询时直接读预计算好的结果。
-- 源表
CREATE TABLE events (
user_id UInt64,
city LowCardinality(String),
dt Date,
amount Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(dt)
ORDER BY (dt, city);
-- 目标表
CREATE TABLE events_daily (
dt Date,
city LowCardinality(String),
total_amount Float64,
cnt UInt64
) ENGINE = SummingMergeTree()
ORDER BY (dt, city);
-- 物化视图：连接源表和目标表
CREATE MATERIALIZED VIEW events_daily_mv
TO events_daily
AS SELECT
dt,
city,
sum(amount) AS total_amount,
count() AS cnt
FROM events
GROUP BY dt, city;
查询时注意用 sum() 再聚合一次，防止 Merge 还没完成时读到未合并的数据：
SELECT city, sum(total_amount), sum(cnt)
FROM events_daily
GROUP BY city;
5.5 字典表（Dictionary）
把小的维度表加载进内存，用函数调用代替 JOIN：
CREATE DICTIONARY user_dict (
id UInt64,
city String,
age UInt8
) PRIMARY KEY id
SOURCE(CLICKHOUSE(TABLE 'users'))
LAYOUT(HASHED())
LIFETIME(3600);
-- 查询时用dictGet函数
SELECT
dictGet('user_dict', 'city', user_id) AS city,
sum(amount)
FROM orders
GROUP BY city;
5.6 GROUP BY 修饰符：WITH ROLLUP / WITH CUBE / WITH TOTALS
在 OLAP 分析中，一个极为常见的需求是：在查看明细分组的同时，还需要查看各层级小计和总计。传统做法是执行多条 SQL 然后在应用层拼接结果，ClickHouse 提供了三个 GROUP BY 修饰符来一次性解决这类需求。
WITH TOTALS——追加全局总计行
WITH TOTALS 是最简单的修饰符，它在正常的 GROUP BY 结果之后额外追加一行，该行包含所有数据的全局汇总值。
SELECT city, count() AS cnt, sum(amount) AS total
FROM orders
GROUP BY city
WITH TOTALS;
返回结果示例：
┌─city──┬──cnt─┬──total─┐
│ 北京 │ 500 │ 50000 │
│ 上海 │ 300 │ 30000 │
│ 广州 │ 200 │ 20000 │
└───────┴──────┴────────┘
Totals:
┌─city─┬──cnt──┬───total─┐
│ │ 1000 │ 100000 │
└──────┴───────┴─────────┘
Totals 行中分组字段显示为该类型的默认值（String 为空字符串，数值为 0），聚合列则为全部数据的汇总。
WITH ROLLUP——逐级上卷小计
WITH ROLLUP 按 GROUP BY 字段列表从右到左逐级"卷起"，每卷起一个字段就产生一个更高层级的小计。如果 GROUP BY 包含 N 个字段，最终会产生 N+1 个层级的结果（N 级明细 + 最终总计）。
SELECT city, status, count() AS cnt, sum(amount) AS total
FROM orders
GROUP BY city, status
WITH ROLLUP;
返回结果包含三个层级：
-- 层级1：city + status 明细
北京, 已付款, 300, 30000
北京, 待付款, 200, 15000
上海, 已付款, 150, 18000
上海, 待付款, 100, 8000
-- 层级2：只按 city（status 被卷起，显示为默认值）
北京, '', 500, 45000
上海, '', 250, 26000
-- 层级3：全局总计（city 也被卷起）
'', '', 750, 71000
上卷顺序严格按 GROUP BY 字段从右到左：先把最右边的 status 卷掉得到 city 小计，再把 city 也卷掉得到全局总计。如果有三个字段 GROUP BY a, b, c WITH ROLLUP，则产生四级结果：(a,b,c)、(a,b)、(a)、()。
ROLLUP 适合具有层次关系的维度分析，例如"年→月→日"或"国家→省份→城市"。
WITH CUBE——全维度组合
WITH CUBE 产生 GROUP BY 字段的所有可能子集的聚合结果。如果有 N 个分组字段，会产生 2^N 种组合。
SELECT city, status, count() AS cnt, sum(amount) AS total
FROM orders
GROUP BY city, status
WITH CUBE;
对于两个字段，产生 4 种组合（2^2 = 4）：
-- 组合1：city + status（完整明细）
北京, 已付款, 300, 30000
北京, 待付款, 200, 15000
上海, 已付款, 150, 18000
上海, 待付款, 100, 8000
-- 组合2：只按 city（status 被忽略）
北京, '', 500, 45000
上海, '', 250, 26000
-- 组合3：只按 status（city 被忽略）
'', 已付款, 450, 48000
'', 待付款, 300, 23000
-- 组合4：全局总计
'', '', 750, 71000
CUBE 与 ROLLUP 的核心区别在于：ROLLUP 只产生"从右到左逐级收缩"的线性层次，而 CUBE 产生所有字段子集的笛卡尔组合。三个字段时 ROLLUP 产生 4 级，CUBE 产生 8 种组合。
CUBE 适合维度之间无层次关系的多维交叉分析，例如同时需要"按城市看"、“按状态看”、"按城市+状态看"以及"整体看"的场景。
三者的适用场景对比
WITH TOTALS 适合仅需要一个全局合计行的简单场景，开销最小。WITH ROLLUP 适合维度之间存在包含关系的层级分析，如时间维度（年→月→日）或地理维度（国→省→市）。WITH CUBE 适合维度之间相互独立、需要所有组合视角的多维分析，但要注意当分组字段超过 3-4 个时结果集会急剧膨胀。
实际使用注意事项
被卷起的字段值显示为该数据类型的零值（String 为空串，整数为 0，日期为 ‘1970-01-01’）。在应用层可以通过判断零值来区分"这是小计行"还是"这是真实值恰好为零"的情况。如果字段使用了 Nullable 类型，则被卷起的字段值为 NULL，更容易区分。
这三个修饰符可以与 HAVING 子句配合使用，HAVING 过滤在所有小计和总计行生成之后执行。
六、建表优化（按重要程度排序）
6.1 分区设计（PARTITION BY）
按月分区是最常见的选择：
PARTITION BY toYYYYMM(event_date)
- 太细（按天分区）：分区数量过多，每个分区 Part 数量少，merge 效率低。官方建议单表分区数不超过 1000 个。
- 太粗（按年分区）：分区裁剪效果差。
- 分区字段必须和查询的时间过滤条件对齐，否则分区裁剪失效。
6.2 排序键设计（ORDER BY）——影响最大
字段顺序遵循两个原则：
原则一：基数低的字段放前面。低基数字段排在前面，相同值的数据聚集在一起，压缩率更高，索引过滤效果更好。这和 MySQL"高基数在前"的原则恰好相反，原因是两者索引结构不同。
原则二：查询频率高的过滤字段放前面。WHERE 条件里最常用的字段排在 ORDER BY 最前面。
原则三：时间字段通常放最后。时间字段基数极高，放前面会让同一城市的数据分散在各时间段，破坏聚集效果。
-- 好的设计
ORDER BY (city, user_id, event_time)
-- 差的设计：高基数字段放前面
ORDER BY (user_id, city, event_time)
-- 差的设计：时间放前面
ORDER BY (event_time, city, user_id)
压缩率差的真正原因：每列独立压缩，但 ORDER BY 决定了物理存储顺序。按 (city, ...) 排序时，city 列文件内容是"北京北京北京…上海上海上海…"，压缩率极高；按 (event_time, ...) 排序时，city 列内容变成"北京上海广州北京深圳…"完全没有连续性。
6.3 主键设计（PRIMARY KEY）
主键必须是 ORDER BY 的前缀，可以比 ORDER BY 少几个字段：
ORDER BY (city, user_id, event_time)
PRIMARY KEY (city, user_id) -- 不包含event_time，索引更精简
event_time 参与排序（压缩率好），但不进索引（索引文件更小）。
6.4 数据类型选择
-- 用 LowCardinality 包装低基数字符串（基数<10000）
status LowCardinality(String) -- 内部字典编码，存储和查询都快
-- 用最小够用的整数类型
city_id Int16 -- 城市数量不超过32767完全够用
-- 日期类型选 Date（2字节）而不是 DateTime（4字节）
-- 能用整数就不用字符串
6.5 避免 Nullable
Nullable(T) 在底层是两列存储：一列存数据，一列存 null mask。
为什么要避免：
- 每次读取都要额外读取 null mask 文件做位运算，性能损耗
- 无法用于 ORDER BY 和主键
- 额外存储开销，压缩率下降
替代方案：用有业务含义的默认值代替 NULL
age UInt32 DEFAULT 0
remark String DEFAULT ''
expire_date Date DEFAULT '1970-01-01'
何时可以用 Nullable：只有当 0 或空字符串有独立的业务含义、无法作为"未知"占位值时（如评分字段，0 分是真实评分，NULL 表示未评分）。
6.6 压缩编解码器（CODEC）
CREATE TABLE events (
event_date Date,
user_id UInt64 CODEC(Delta, ZSTD(1)), -- 单调递增用Delta
amount Float64 CODEC(Gorilla, ZSTD(1)), -- 浮点数用Gorilla
log_data String CODEC(ZSTD(3)) -- 大文本用高压缩比
)
- Delta：适合单调递增的整数（时间戳、自增 ID）
- Gorilla：适合变化平缓的浮点数（监控指标、金额）
- ZSTD(level)：通用压缩，level 越高压缩率越好但 CPU 开销越大
- LZ4：默认压缩算法，速度最快，压缩率一般
6.7 跳数索引
CREATE TABLE events (
event_date Date,
user_id UInt64,
action String,
amount Float64,
INDEX idx_action action TYPE bloom_filter(0.01) GRANULARITY 4,
INDEX idx_amount amount TYPE minmax GRANULARITY 4
)
ORDER BY (event_date, user_id);
跳数索引不是越多越好，它增加写入开销和存储空间，只给真正高频的过滤字段加。
6.8 TTL 设计
TTL 字段和 PARTITION BY 字段对齐，让过期数据整个 Part 直接删除：
PARTITION BY toYYYYMM(event_date)
TTL event_date + INTERVAL 1 YEAR
6.9 索引字段选择的判断框架
这个字段是否高频出现在 WHERE 条件里？
→ 否：不需要索引
→ 是：继续判断
这个字段是否适合放进排序键？
（标准：基数相对低、是最核心的过滤维度）
→ 是：放进 ORDER BY，调整好字段顺序
→ 否：考虑跳数索引
跳数索引选哪种？
→ 等值查询 + 低基数：set
→ 等值查询 + 高基数：bloom_filter
→ 范围查询：minmax
七、查询调优实战
7.1 EXPLAIN 使用
ClickHouse 的 EXPLAIN 有多个子命令：
EXPLAIN PLAN（最常用）：
EXPLAIN PLAN indexes=1
SELECT user_id, sum(amount)
FROM events
WHERE event_date >= '2024-01-01' AND event_date < '2024-02-01'
GROUP BY user_id
ORDER BY sum(amount) DESC LIMIT 10;
重点看 Parts: X/Y 和 Granules: X/Y，X 越接近 Y 说明索引越没用上。
EXPLAIN PIPELINE（看并行度）：
EXPLAIN PIPELINE SELECT user_id, sum(amount) FROM events GROUP BY user_id;
× 8 表示用了 8 个并行线程，确认查询是否充分利用多核。
EXPLAIN ESTIMATE（快速估算）：
EXPLAIN ESTIMATE SELECT user_id, sum(amount) FROM events WHERE event_date >= '2024-01-01';
不执行查询，只估算扫描多少 Part、行数、mark 数。
7.2 系统表诊断
-- 查最近的慢查询
SELECT query, query_duration_ms, read_rows, read_bytes, memory_usage
FROM system.query_log
WHERE type = 'QueryFinish' AND query_duration_ms > 1000
AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC LIMIT 20;
-- 查当前正在执行的查询
SELECT query_id, query, elapsed, read_rows, memory_usage
FROM system.processes ORDER BY elapsed DESC;
-- 查Part状态
SELECT table, count() AS part_count, sum(rows) AS total_rows
FROM system.parts WHERE active = 1
GROUP BY table ORDER BY part_count DESC;
7.3 调优实战顺序
遇到慢查询时，按这个顺序排查：
第一步，看扫描量是否合理：EXPLAIN ESTIMATE SELECT ...
第二步，看索引是否命中：EXPLAIN PLAN indexes=1 SELECT ...
第三步，看并行度是否充分：EXPLAIN PIPELINE SELECT ...
7.4 SQL 语法优化详解
ClickHouse 的查询优化可以从两个层面理解：一是用户编写 SQL 时主动采用的优化手法（手动优化），二是 ClickHouse 优化器基于规则自动执行的查询重写（自动优化）。本节分两大部分分别讲解，帮助读者既能写出高效 SQL，也能理解底层优化器在背后做了哪些工作。
7.4.A 手动 SQL 优化
本部分按优化收益从高到低的顺序，系统讲解编写 ClickHouse SQL 时最重要的优化手法。这些优化需要开发者在写查询时主动应用。
7.4.1 PREWHERE——列存最核心的过滤优化
PREWHERE 是 ClickHouse 特有的语法，也是列存场景下最重要的查询优化之一。它将过滤条件的执行提前到列读取之前，实现"先读过滤列，再读其余列"的两阶段 IO 模式。
-- 显式 PREWHERE 写法
SELECT user_id, city, amount
FROM orders
PREWHERE status = 1
WHERE amount > 100;
执行过程分为两个阶段：第一阶段只读取 status 这一列，在内存中对整个 granule 进行过滤，得到满足条件的行位置集合；第二阶段只对满足条件的行，再去读取 user_id、city、amount 等其他列数据。如果 status=1 的行只占 5%，那么 user_id、city、amount 三列只需要读取 5% 的数据量，IO 节省高达 95%。
在实际使用中，ClickHouse 的优化器会自动将 WHERE 条件转换为 PREWHERE（通过 optimize_move_to_prewhere 设置控制，默认开启）。优化器会选择"过滤率最高且列体积最小"的条件提升为 PREWHERE。但当自动选择不理想时，可以手动指定。
适合 PREWHERE 的条件特征是：过滤性强（能过滤掉大部分行）、涉及的列存储体积小。不适合 PREWHERE 的情况是：条件本身涉及大量列或计算复杂的表达式，此时提前读取过滤列的成本可能超过收益。
7.4.2 列裁剪——杜绝 SELECT *
列式存储的核心优势是"只读取查询所需的列"。使用 SELECT * 会强制读取所有列的数据文件，完全抵消列存优势。在宽表场景下（几十甚至上百列），一个 SELECT * 和只查 3-5 列的查询，IO 量可能相差 10-30 倍。
-- 反模式：读取全部列
SELECT * FROM events WHERE dt = '2024-01-01' LIMIT 100;
-- 正确做法：只选需要的列
SELECT user_id, action, amount FROM events WHERE dt = '2024-01-01' LIMIT 100;
即使在调试阶段，也建议养成显式列出字段的习惯。
7.4.3 条件聚合——单次扫描替代多次查询
当需要对同一张表按不同条件分别统计时，传统做法是写多条 SQL 或用子查询，每条都触发一次全表扫描。ClickHouse 提供了 countIf、sumIf、avgIf 等带条件的聚合函数，可以在单次扫描中完成所有计算。
-- 反模式：同一张表扫描三次
SELECT
(SELECT count() FROM orders WHERE dt = '2024-01-01') AS today,
(SELECT count() FROM orders WHERE dt = '2023-12-31') AS yesterday,
(SELECT sum(amount) FROM orders WHERE dt = '2024-01-01') AS today_amount;
-- 优化：一次扫描，三个结果
SELECT
countIf(dt = '2024-01-01') AS today,
countIf(dt = '2023-12-31') AS yesterday,
sumIf(amount, dt = '2024-01-01') AS today_amount
FROM orders
WHERE dt IN ('2024-01-01', '2023-12-31');
注意外层的 WHERE 子句用于分区裁剪（只扫描这两天所在的分区），内层的条件聚合函数在已扫描的数据上做细分统计。这样 IO 量仅为单个分区的数据量，而非两次全量扫描。
7.4.4 IN 代替 JOIN——小表关联的高效替代
ClickHouse 的 JOIN 实现代价较高（右表必须全量加载进内存构建 HashTable），当关联目的仅仅是过滤或判断存在性时，使用 IN 子查询是更优的选择。
-- 较重：JOIN
SELECT a.user_id, a.amount
FROM orders a
INNER JOIN vip_users b ON a.user_id = b.user_id
WHERE a.dt = '2024-01-01';
-- 较轻：IN 子查询
SELECT user_id, amount
FROM orders
WHERE dt = '2024-01-01'
AND user_id IN (SELECT user_id FROM vip_users);
IN 的底层实现是将子查询结果构建为一个 Set（HashSet），主查询逐行判断 key 是否存在于 Set 中。相比 JOIN 需要为右表所有列构建 HashTable，Set 只存储 key 列，内存占用更小，构建速度更快。
使用 IN 的适用条件是：只需要"是否存在"的判断（过滤），不需要取右表的字段值。如果需要从右表获取字段，仍然需要使用 JOIN。
7.4.5 GLOBAL IN / GLOBAL JOIN——分布式查询的去重优化
在分布式集群中，普通的 IN 子查询会在每个 Shard 上独立执行子查询。如果子查询本身也是分布式表，就会产生 N×N 的扇出问题（N 个 Shard 各自向所有 Shard 发起子查询）。
-- 问题写法：每个 Shard 都会独立执行子查询，产生 N² 次查询
SELECT user_id, amount
FROM distributed_orders
WHERE user_id IN (SELECT user_id FROM distributed_vip_users);
-- 优化写法：Coordinator 先执行一次子查询，将结果集广播到所有 Shard
SELECT user_id, amount
FROM distributed_orders
WHERE user_id GLOBAL IN (SELECT user_id FROM distributed_vip_users);
GLOBAL IN 的执行流程是：Coordinator 节点先执行括号内的子查询，得到完整结果集，然后将该结果集序列化后广播到所有 Shard，各 Shard 使用收到的结果集做本地过滤。子查询只执行一次，网络传输从 N² 降为 N。
适用场景是子查询结果集不太大（能放进内存并在网络上传输）。如果子查询本身返回数亿行数据，GLOBAL IN 的广播开销也会很大，此时需要考虑其他方案（如提前写入本地表）。
7.4.6 近似算法——以误差换性能
ClickHouse 内置了多种近似计算函数，在可接受小幅误差的场景下，性能提升可达数倍到数十倍。
近似去重（HyperLogLog）：
-- 精确去重：内存消耗大，速度慢
SELECT uniqExact(user_id) FROM events;
-- 近似去重：标准误差约 1.5%，速度快 10 倍以上
SELECT uniq(user_id) FROM events;
-- 可合并的 HLL（用于物化视图增量聚合）
SELECT uniqState(user_id) FROM events;
uniq 基于 HyperLogLog++ 算法，用固定大小的内存（约 12KB）即可估算任意规模的去重基数。对于 UV 统计、DAU 计算等场景，1-2% 的误差通常完全可以接受。
近似分位数（T-Digest / DDSketch）：
-- 近似 P99（T-Digest 算法，误差通常 <1%）
SELECT quantile(0.99)(response_time) FROM requests;
-- 多分位数一次计算
SELECT quantiles(0.5, 0.9, 0.95, 0.99)(response_time) FROM requests;
-- 精确分位数（需要全量排序，慢）
SELECT quantileExact(0.99)(response_time) FROM requests;
近似中位数：
SELECT median(amount) FROM orders; -- 等价于 quantile(0.5)
选择原则：报表展示、趋势监控等场景使用近似函数；涉及计费、对账等需要精确值的场景使用 Exact 版本。
7.4.7 LIMIT 优化与短路评估
ClickHouse 对带 LIMIT 的查询有特殊优化：当读取到足够的行数后会提前终止扫描。但这个优化有前提条件。
-- 能触发提前终止（无聚合、无排序）
SELECT user_id, amount FROM events WHERE dt = '2024-01-01' LIMIT 100;
-- 不能提前终止（有 GROUP BY，必须扫描全部数据才能聚合）
SELECT city, count() FROM events GROUP BY city LIMIT 10;
-- 不能提前终止（ORDER BY 要求全局排序）
SELECT user_id, amount FROM events ORDER BY amount DESC LIMIT 10;
对于 ORDER BY ... LIMIT N 模式，ClickHouse 内部使用堆排序（Top-N 优化），只维护 N 个元素的堆而非全量排序，内存开销为 O(N) 而非 O(全量)。但仍然需要扫描所有满足 WHERE 条件的数据。
7.4.8 Nullable 的性能影响
Nullable 类型在查询层面也有额外开销。每个 Nullable 列在底层存储为两个子列：数据列和 null-mask 列。查询时需要额外读取 null-mask 并对每行做位运算判断，所有涉及该列的运算都需要先检查是否为 NULL。
-- 需要额外处理 NULL 的情况
SELECT sum(amount) FROM orders;
-- 如果 amount 是 Nullable(Float64)，sum 需要跳过 NULL 值
-- 底层：每行检查 null_mask[i]，为 1 则跳过
-- 非 Nullable 版本直接对数据列做向量化累加，无分支判断
在 GROUP BY 中，Nullable 字段的影响更加明显：HashTable 的 key 比较逻辑需要额外处理 NULL（NULL 被视为一个独立的分组），增加了比较开销。
7.4.9 argMax / argMin——避免子查询取关联值
一个常见需求是"取每个分组中某个指标最大时对应的另一个字段值"。传统 SQL 需要使用窗口函数或子查询，ClickHouse 提供了 argMax 和 argMin 函数直接完成。
-- 需求：每个用户最近一笔订单的金额
-- 传统写法（子查询 + JOIN）
SELECT a.user_id, a.amount
FROM orders a
INNER JOIN (
SELECT user_id, max(created_at) AS max_time
FROM orders GROUP BY user_id
) b ON a.user_id = b.user_id AND a.created_at = b.max_time;
-- ClickHouse 优化写法：一次聚合搞定
SELECT
user_id,
argMax(amount, created_at) AS latest_amount
FROM orders
GROUP BY user_id;
argMax(amount, created_at) 的语义是"返回 created_at 取最大值时对应的 amount 值"。整个计算在一次 GROUP BY 聚合中完成，无需子查询或 JOIN，执行效率远高于传统写法。
类似地，argMin(amount, created_at) 返回 created_at 最小时对应的 amount。
7.4.10 any() 函数——GROUP BY 取任意值
当确定某个字段在分组内的值唯一（或不关心取哪个值）时，使用 any() 比 max() 或 min() 更高效，因为 any() 遇到第一个值即可返回，无需遍历整个分组。
-- 假设 user_id 和 username 是一对一关系
SELECT
user_id,
any(username) AS username, -- 取任意一个即可，无需 max/min
sum(amount) AS total
FROM orders
GROUP BY user_id;
7.4.11 谓词下推与分区裁剪
ClickHouse 优化器会自动进行谓词下推：将 WHERE 条件尽可能推到数据读取的最底层执行。但有些写法会阻止优化器的自动下推。
-- 优化器能下推分区裁剪条件
SELECT * FROM orders WHERE toYYYYMM(created_at) = 202401;
-- 如果分区键是 toYYYYMM(created_at)，以下写法也能触发裁剪
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';
-- 反模式：对分区字段做函数变换后再比较，可能无法裁剪
SELECT * FROM orders WHERE toString(created_at) LIKE '2024-01%';
编写 WHERE 条件时，应尽量让过滤表达式与分区键表达式直接对应，或使用范围条件让优化器能推断出分区范围。
7.4.12 GROUP BY 内存溢出处理
当 GROUP BY 的基数非常高（如按用户 ID 分组，可能有几千万个不同值）时，内存中的 HashTable 可能超出限制。ClickHouse 支持将聚合结果溢出到磁盘：
SET max_bytes_before_external_group_by = 10000000000; -- 10GB
-- 超过 10GB 后自动将部分数据刷到磁盘，用外部排序完成聚合
这是一个兜底机制，会显著降低查询性能。如果经常触发溢出，应从业务层面重新考虑聚合粒度或使用物化视图预聚合。
7.4.B ClickHouse 优化器自动优化（RBO）
ClickHouse 内置了一套基于规则的优化器（Rule Based Optimization, RBO），在查询执行前会自动对 AST（抽象语法树）进行语义等价的重写。这些优化无需用户干预，会在满足条件时自动触发。可以通过 EXPLAIN SYNTAX 命令查看优化器重写后的 SQL，从而了解实际执行的语句。
7.4.13 COUNT 优化
在调用 count 函数时，如果使用的是 count() 或 count(*)，且没有 WHERE 条件，优化器会直接使用 system.tables 中存储的 total_rows 元数据，完全跳过数据扫描。
-- 触发优化：直接读取元数据，执行计划出现 "Optimized trivial count"
EXPLAIN SELECT count() FROM orders;
-- ┌─explain──────────────────────────────────────────────┐
-- │ Expression ((Projection + Before ORDER BY)) │
-- │ MergingAggregated │
-- │ ReadFromPreparedSource (Optimized trivial count) │
-- └──────────────────────────────────────────────────────┘
-- 不触发优化：count 具体字段时需要实际扫描数据
SELECT count(user_id) FROM orders;
-- 因为需要排除该列中的 NULL 值，必须逐行判断
注意：count() 和 count(*) 等价且都能触发优化；count(column) 不会触发，因为语义上需要排除 NULL 值。这也是为什么应该始终使用 count() 而非 count(具体字段) 的原因之一。
7.4.14 消除子查询重复字段
当子查询中出现重复的列名时，优化器会自动去重；同时，外层不需要的列也会被裁剪掉（投影下推）。
-- 原始 SQL：子查询中 id 重复出现，且 time 字段外层未使用
EXPLAIN SYNTAX
SELECT a.id, b.name, a.price
FROM id_join_tb1 AS a
LEFT JOIN (
SELECT id, id, name, time FROM join_tb1
) AS b USING (id);
-- 优化器重写后：
-- SELECT id, name, price
-- FROM id_join_tb1 AS a
-- ALL LEFT JOIN (
-- SELECT id, name FROM join_tb1 ← 重复 id 去重，未用的 time 被裁剪
-- ) AS b USING (id)
这项优化减少了子查询中不必要的列读取，在宽表 JOIN 场景下尤其有效，可以显著降低 IO 和内存开销。
7.4.15 谓词下推（优化器自动执行）
优化器会自动将过滤条件下推到更底层执行，减少中间数据量。主要体现在以下几个场景：
HAVING 下推到 WHERE：当 GROUP BY 没有 WITH CUBE、WITH ROLLUP 或 WITH TOTALS 修饰时，如果 HAVING 中的过滤字段不依赖聚合结果，优化器会将其下推到 WHERE 阶段，在分组前就过滤掉不相关数据。
-- 原始 SQL
EXPLAIN SYNTAX SELECT name FROM join_tb1 GROUP BY name HAVING name = 'test';
-- 优化器重写后：HAVING 变为 WHERE，在 GROUP BY 之前过滤
-- SELECT name FROM join_tb1 WHERE name = 'test' GROUP BY name
子查询谓词下推：外层查询的 WHERE 条件会被自动下推到子查询内部。
-- 原始 SQL
EXPLAIN SYNTAX SELECT * FROM (SELECT id FROM id_join_tb1) WHERE id = 10;
-- 优化器重写后：WHERE id = 10 被下推到子查询内部
-- SELECT id FROM (SELECT id FROM id_join_tb1 WHERE id = 10) WHERE id = 10
对于 UNION ALL 的多分支子查询，谓词会被下推到每一个分支：
-- 原始 SQL
SELECT * FROM (
SELECT id FROM table_a
UNION ALL
SELECT id FROM table_b
) WHERE id = 10;
-- 优化器重写后：每个分支都有 WHERE id = 10
-- SELECT id FROM (
-- SELECT id FROM table_a WHERE id = 10
-- UNION ALL
-- SELECT id FROM table_b WHERE id = 10
-- ) WHERE id = 10
7.4.16 聚合计算外推
当聚合函数内部包含与常量的算术运算时，优化器会将常量计算提到聚合函数外部，减少聚合过程中每行的计算量。
-- 原始 SQL：每行都要先算 id * 2，再累加（百万行 = 百万次乘法）
EXPLAIN SYNTAX SELECT sum(id * 2) FROM join_tb1;
-- 优化器重写后：先聚合再乘常量（只做一次乘法）
-- SELECT sum(id) * 2 FROM join_tb1
当数据量为百万级时，这项优化将乘法运算次数从百万次降为 1 次，在聚合计算密集的场景下收益明显。
7.4.17 聚合函数消除
如果对 GROUP BY 的 key 字段使用 min、max、any 等聚合函数，由于分组内该字段值必定唯一，优化器会直接消除这些冗余的聚合函数调用，用字段本身替代。
-- 原始 SQL
EXPLAIN SYNTAX SELECT sum(id * 2), max(name), max(id) FROM join_tb1 GROUP BY id;
-- 优化器重写后：max(id) 被替换为 id 本身（GROUP BY key 值唯一）
-- SELECT sum(id) * 2, max(name), id FROM join_tb1 GROUP BY id
7.4.18 删除重复的 ORDER BY / LIMIT BY / USING Key
优化器会自动去除 ORDER BY、LIMIT BY 和 JOIN USING 子句中重复声明的字段，避免冗余的排序和比较操作。
-- ORDER BY 重复字段去重
EXPLAIN SYNTAX SELECT * FROM join_tb1 ORDER BY id ASC, id ASC, name ASC, name ASC;
-- 重写后：ORDER BY id ASC, name ASC
-- LIMIT BY 重复字段去重
EXPLAIN SYNTAX SELECT * FROM join_tb1 LIMIT 3 BY name, name LIMIT 10;
-- 重写后：LIMIT 3 BY name LIMIT 10
-- USING 重复字段去重
EXPLAIN SYNTAX SELECT a.id, b.name FROM t1 AS a LEFT JOIN t2 AS b USING (id, id);
-- 重写后：USING (id)
7.4.19 标量子查询替换
如果 WITH 子句中的子查询只返回一行一列的标量值，优化器会在编译期将其替换为常量（用 identity() 函数包裹），避免运行时重复执行子查询。
-- 原始 SQL
EXPLAIN SYNTAX
WITH (SELECT sum(bytes) FROM system.parts WHERE active) AS total_disk_usage
SELECT (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage, table
FROM system.parts
GROUP BY table ORDER BY table_disk_usage DESC LIMIT 10;
-- 优化器重写后：子查询被替换为常量
-- WITH identity(CAST(0, 'UInt64')) AS total_disk_usage
-- SELECT (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage, table
-- FROM system.parts GROUP BY table ORDER BY table_disk_usage DESC LIMIT 10
7.4.20 三元运算优化
当开启 optimize_if_chain_to_multiif 参数时，嵌套的三元运算符（?:）会被自动替换为 multiIf 函数，减少表达式求值的分支开销。
-- 原始 SQL
EXPLAIN SYNTAX
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz')
FROM numbers(10)
SETTINGS optimize_if_chain_to_multiif = 1;
-- 优化器重写后：
-- SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'xyz') FROM numbers(10)
multiIf 是 ClickHouse 优化过的多分支条件函数，比嵌套三元运算符有更好的向量化执行效率。
7.4.21 WHERE 自动转 PREWHERE
优化器默认开启 optimize_move_to_prewhere 设置，会自动选择过滤性强且列体积小的 WHERE 条件提升为 PREWHERE 执行。即使用户没有手动编写 PREWHERE，优化器也会自动进行两阶段 IO 优化。选择依据是：条件列的压缩后体积占查询总列体积的比例越小、过滤率越高，被选中的优先级越高。
7.4.22 如何验证优化器行为
可以使用 EXPLAIN SYNTAX 和 EXPLAIN PLAN 来观察优化器的实际行为：
-- 查看优化器重写后的 SQL（语法层面的 RBO 规则）
EXPLAIN SYNTAX SELECT sum(id * 2) FROM join_tb1;
-- 查看完整执行计划（含索引使用、Part 过滤情况）
EXPLAIN PLAN indexes=1 SELECT count() FROM orders;
-- 查看执行 pipeline（含并行度信息）
EXPLAIN PIPELINE SELECT * FROM orders WHERE dt = '2024-01-01' LIMIT 100;
通过对比重写前后的 SQL，可以验证上述优化规则是否生效。理解优化器的行为也有助于判断为什么某些看似低效的写法实际执行效率并不差——因为优化器已经自动完成了等价重写。但优化器的能力毕竟有限，7.4.A 部分介绍的手动优化（如选择 IN 代替 JOIN、使用近似算法、条件聚合等）是优化器无法自动完成的，需要开发者主动应用。
八、写入最佳实践
8.1 必须批量写入
-- 极其错误的做法（每条单独insert）
INSERT INTO orders VALUES (1, 100, 'BJ', 99.9, 1, now());
-- 每次insert生成一个新Part，Part数量爆炸
-- 正确做法：批量插入，建议每批至少1000行，最好10000行以上
INSERT INTO orders VALUES
(1, 100, 'BJ', 99.9, 1, now()),
(2, 101, 'SH', 199.9, 1, now()),
...
(10000, 200, 'GZ', 59.9, 1, now());
ClickHouse 官方建议每秒写入不超过 1 次，每次至少几千行。
8.2 生产标准做法
业务服务 → Kafka → Flink/消费者攒批 → ClickHouse
（攒够1万条或等待5秒，取先到者）
8.3 Java 集成示例
ClickHouseDataSource dataSource = new ClickHouseDataSource(
"jdbc:clickhouse://localhost:8123/default"
);
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(
"INSERT INTO orders VALUES (?, ?, ?, ?, ?, ?)")) {
for (Order order : orders) {
ps.setLong(1, order.getId());
ps.setString(2, order.getCity());
// ...
ps.addBatch();
}
ps.executeBatch(); // 一次性提交
}
九、为什么不适合多表 JOIN
9.1 根本原因
ClickHouse 的 JOIN 默认使用 Hash Join：右表全量加载进内存构建 HashTable，左表流式扫描逐行 probe。
限制一：右表必须能放进内存。OLAP 场景的表动辄几十亿行，根本放不下。
限制二：分布式 JOIN 的数据传输问题。同一个 JOIN key 的数据可能在不同节点，需要右表广播到所有节点，网络开销巨大。
9.2 工程解决方案
方案一：提前做宽表（最常用）
ETL 阶段提前把多表关联字段写进一张大宽表，查询时只查一张表。
方案二：字典表（Dictionary）
把小维度表常驻内存，用 dictGet() 函数代替 JOIN。
方案三：物化视图预聚合
用物化视图提前把多表数据聚合成结果表。
十、ClickHouse vs ElasticSearch
两者经常被混淆，但本质定位不同：ClickHouse 是计算引擎，ES 是搜索引擎。
| 维度 | ClickHouse | ElasticSearch |
|---|---|---|
| 核心索引结构 | 稀疏索引 + 跳数索引 | 倒排索引 |
| 最擅长的查询 | 聚合分析（GROUP BY + 聚合函数） | 全文搜索 + 关键词匹配 |
| 数据模型 | 结构化表（强 Schema） | 文档（弱 Schema，JSON） |
| 写入方式 | 批量追加（建议每次>1000行） | 近实时单条写入 |
| 相关性排序 | 不支持 | 核心能力（BM25） |
| JOIN 能力 | 有限 | 极弱 |
| 资源消耗 | CPU 密集 | 内存密集 |
| 典型数据量 | 百亿行级别 | 十亿文档级别 |
日志系统中的分工：
- 需要全文检索（搜索某个 traceId）→ 用 ES
- 需要大范围聚合计算（各接口的 P99 延迟趋势）→ 用 ClickHouse
- 现实中很多公司两者都用：Kafka → ES（实时检索）+ ClickHouse（离线分析）
一句话总结：ES 解决"找到什么"的问题，ClickHouse 解决"算出什么"的问题。
十一、服务器资源配置
11.1 CPU 配置
ClickHouse 是 CPU 密集型系统，核心参数：
<max_threads>16</max_threads> <!-- 单查询最多线程数 -->
<background_pool_size>8</background_pool_size> <!-- 后台merge线程数 -->
生产建议：专机部署不混部，32 核以上。
11.2 内存配置
<!-- 服务器总内存上限，建议物理内存的80% -->
<max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio>
<!-- 单查询内存上限 -->
<max_memory_usage>10000000000</max_memory_usage> <!-- 10GB -->
<!-- Mark Cache，建议5GB -->
<mark_cache_size>5368709120</mark_cache_size>
11.3 存储配置（冷热分层）
<storage_configuration>
<disks>
<hot_disk><type>local</type><path>/data/clickhouse/hot/</path></hot_disk>
<cold_disk><type>local</type><path>/data2/clickhouse/cold/</path></cold_disk>
</disks>
<policies>
<hot_to_cold>
<volumes>
<hot><disk>hot_disk</disk></hot>
<cold><disk>cold_disk</disk></cold>
</volumes>
<move_factor>0.2</move_factor>
</hot_to_cold>
</policies>
</storage_configuration>
配合 TTL 自动冷热迁移：
TTL event_date + INTERVAL 30 DAY TO VOLUME 'cold'
11.4 生产环境推荐配置
| 资源 | 推荐配置 | 说明 |
|---|---|---|
| CPU | 32核+ | 专机部署，不混部 |
| 内存 | 128GB+ | ClickHouse 用 80%，留 20% 给 OS |
| 热存储 | NVMe SSD | 存近期数据，高 IOPS |
| 冷存储 | HDD 或对象存储 | 存历史数据，低成本 |
| 网络 | 万兆网卡 | 副本同步依赖网络带宽 |
| 磁盘数量 | 多块独立磁盘 | JBOD 模式，不用 RAID |
ClickHouse 官方不推荐用 RAID，因为 ReplicatedMergeTree 自己做了数据冗余，RAID 是多余的且写放大会降低写入性能。
十二、调优优先级总结
收益最高（优先做）：
1. 排序键设计合理（索引命中与否差距10-100倍）
2. 分区键设计合理（分区裁剪）
3. 批量写入（避免Part爆炸）
收益中等（次优先）：
4. 物化视图预聚合（固定查询模式）
5. LowCardinality 和合适的数据类型
6. 跳数索引（非排序键列的过滤）
收益较小（最后做）：
7. 压缩算法调整
8. 内存参数调整
9. 并行度调整
核心原则：调优永远从减少数据扫描量开始，而不是从调参数开始。 索引设计好了，扫描量减少 99%，任何参数调优都比不上这个效果。
十三、ClickHouse 能处理的数据量级
单表行数：百亿 ~ 千亿行
单集群数据量：几十TB ~ 几百TB
查询延迟：
简单聚合（带分区裁剪）：< 1秒
复杂多维分析（扫描几十亿行）：几秒到十几秒
全表扫描（不带索引）：可能几分钟
写入吞吐：单节点可达 500MB/s ~ 1GB/s（批量写入）
公开生产规模参考：Cloudflare 单集群 36PB，每秒写入 600 万行；字节跳动内部最大集群数千节点。
十四、快速上手——本地安装
Docker 一行命令即可启动：
docker run -d --name clickhouse \
-p 8123:8123 -p 9000:9000 \
clickhouse/clickhouse-server
连接并测试：
docker exec -it clickhouse clickhouse-client
参考资料
更多推荐
11
0- 0
已为社区贡献3条内容
所有评论(0)