ClickHouse‌

ClickHouse‌ 是一款高性能、开源的‌列式联机分析处理（OLAP）数据库管理系统‌，专为实时分析海量数据而设计，查询速度极快，尤其适合处理大规模数据分析场景。

核心特点
‌列式存储‌：数据按列存储，相同类型的数据连续存放，大幅减少 I/O 开销，并提升压缩效率（通常比行式数据库高 5–10 倍）。
‌极致查询性能‌：采用向量化执行引擎，一次处理一批数据，充分利用 CPU 缓存和并行计算能力，支持多线程、分布式查询，复杂分析可实现秒级响应。
‌高吞吐写入‌：基于类 LSM 树结构，支持批量与异步写入，单节点每秒可处理数百万条记录。
‌SQL 兼容性‌：支持标准 SQL 语法，同时扩展了适用于 OLAP 的函数（如近似计算、TopK、窗口函数等），降低学习成本。
‌分布式架构‌：支持分片与副本机制，具备横向扩展能力，保障高可用性和数据可靠性。

 

 

为什么 ClickHouse 如此之快？

 从架构角度来看，数据库至少由存储层和查询处理层组成。存储层负责保存、加载和维护表数据，而查询处理层则负责执行用户查询。与其他数据库相比，ClickHouse 在这两个层面都进行了创新，从而实现了极快的插入和 SELECT 查询。

1.存储层：并发插入彼此之间是相互隔离的

在 ClickHouse 中，每个表由多个 “table part” 组成。每当用户向表中插入数据 (INSERT 语句) 时，就会创建一个新的 part。查询始终会在查询开始时已存在的所有 table part 上执行。

为避免累积过多的 part，ClickHouse 会在后台运行 merge 操作，持续地将多个较小的 part 合并为一个更大的 part。

这种设计有几个优点：所有数据处理都可以卸载到后台的 part merge，从而保持数据写入轻量且高效。单次插入在某种意义上是“局部”的，因为它们不需要更新全局 (即表级别) 的数据结构。因此，多个并发插入之间，以及与已有表数据之间都不需要相互同步，插入操作几乎可以接近磁盘 I/O 的速度完成。

所有数据处理都可以卸载到后台的 part merge

在 ClickHouse 中：

• 数据按 Part（分区片段）​ 存储

• 每次 INSERT​ 会：

  • 快速生成一个 新小 part

  • 直接落盘（排序 + 轻量压缩）

• 不会立刻：

  • 做全表去重

  • 重新聚合

  • 大规模重组数据

这些“数据整理工作”被延迟到：

👉 后台 Merge（合并）过程

Merge 时会：

• 合并多个小 part

• 重新排序

• 按 ORDER BY归并

• 执行 ReplacingMergeTree / SummingMergeTree的：

  • 去重

  • 预聚合

  • 保留最后版本

• 清理过期数据（TTL）

✅ 这些都是异步的、后台做的

---

2️⃣ 保持数据写入轻量且高效

如果每次 INSERT 都：

• 立即全局排序

• 立即去重

• 立即重排整张表

➡️ 写入延迟会飙升，吞吐会下降

ClickHouse 的做法是：

 

阶段	做了什么	特点
写入时​	生成独立小 part	🚀 极快、追加写
后台​	Merge 多个 part	🐢 重，但不影响写入

结果：

• 高吞吐写入

• 写入链路短

• CPU/IO 峰值可控

存储层：并发 INSERT 和 SELECT 相互隔离

INSERT 操作与 SELECT 查询完全隔离，已插入数据片段的合并在后台进行，不会影响并发查询的执行。

存储层：合并阶段计算

ClickHouse 通过在后台的 merge 合并过程中执行所有额外的数据转换，来保持数据写入轻量且高效。例如：

替换合并 (replacing merges) ：只保留输入分片中某行的最新版本，并丢弃该行的所有其他版本。可以将替换合并视为在合并阶段执行的清理操作。

聚合合并 (aggregating merges) ：将输入分片中的中间聚合状态合并为一个新的聚合状态。虽然这看起来有些难以理解，但本质上只是实现了增量聚合。

TTL (time-to-live，生存时间) 合并：根据某些基于时间的规则对行进行压缩、移动或删除。

这些转换的目的是将工作量 (计算) 从用户查询执行的时间点转移到合并阶段。这在两个方面很重要：

一方面，如果查询能够利用“已转换”的数据 (例如预聚合数据) ，用户查询的速度可能会显著提升，有时甚至可以快 1000 倍或更多。

另一方面，合并运行时间的主要开销来自加载输入分片和保存输出分片。在合并过程中对数据进行额外转换通常不会对合并的总运行时间产生太大影响。所有这些机制对用户完全透明，并不会改变查询结果 (除了性能方面的改进) 。

存储层：数据裁剪

在实践中，许多查询是重复的，即在固定时间间隔内周期性地运行，且查询本身保持不变或只做轻微修改 (例如使用不同的参数值) 。反复运行相同或相似的查询，使我们可以添加索引或重新组织数据，从而让高频查询能够更快地访问数据。这种方法也被称为“数据裁剪 (data pruning) ”，ClickHouse 为此提供了三种技术：

1. 主键索引，用于定义表数据的排序顺序。精心选择的主键可以让过滤条件 (例如上述查询中的 WHERE 子句) 通过快速的二分查找来评估，而无需对整列进行扫描。从更技术的角度来说，扫描的运行时间相对于数据规模会从线性变为对数级别。

2. 表投影，作为表的另一种内部版本，存储相同的数据，但按照不同的主键排序。当存在多种高频过滤条件时，投影会非常有用。

3. 跳过索引，将额外的数据统计信息嵌入到列中，例如最小值和最大值、唯一值集合等。跳过索引与主键和表投影相互独立，并且根据列中的数据分布情况，它们可以极大地加速过滤条件的评估。

这三种技术的共同目标，都是在整列读取时尽可能多地跳过行，因为读取数据最快的方式就是尽可能不去读取它。

存储层：数据压缩

ClickHouse 的存储层还可以 (可选地) 使用不同的编解码器对原始表数据进行压缩。

列式存储尤其适合此类压缩，因为相同类型且具有相似数据分布的值会被存放在一起。

用户可以指定使用各种通用压缩算法 (如 ZSTD) 或专用编解码器来压缩列，例如针对浮点值的 Gorilla 和 FPC，针对整数值的 Delta 和 GCD，甚至可以使用 AES 作为加密编解码器。

数据压缩不仅可以减少数据库表的存储空间，在很多情况下还可以提升查询性能，因为本地磁盘和网络 I/O 往往受限于较低的吞吐量。

最先进的查询处理层

ClickHouse 使用向量化查询处理层，在最大程度上并行化查询执行，以利用全部资源，实现最高速度和效率。

“Vectorization” 指的是查询计划算子以批次而非单行的方式传递中间结果行。这样可以更好地利用 CPU 缓存，并允许算子应用 SIMD 指令一次处理多个值。事实上，许多算子都提供多个版本——每一代 SIMD 指令集对应一个版本。ClickHouse 会根据其运行所在硬件的能力，自动选择最新、最快的版本。

现代系统通常具有数十个 CPU 核心。为了用满所有核心，ClickHouse 会将查询计划展开为多条执行通道，通常每个核心对应一条通道。每条通道处理表数据中互不重叠的一个范围。通过这种方式，数据库性能会随着可用核心数量实现“纵向”扩展。

如果单个节点过小，无法容纳表数据，可以添加更多节点组成集群。表可以被拆分 (“分片”) 并分布到各个节点上。ClickHouse 将在所有存储表数据的节点上运行查询，从而随着可用节点数量实现“横向”扩展。

 

典型应用场景

‌实时数据仓库‌：如广告平台、电商平台的实时 BI 分析。‌用户行为分析‌：追踪 App 或网页的点击流、事件日志。
‌日志存储与分析‌：微信、Cloudflare 等公司用其存储 PB 级日志数据。
‌AI 基础设施‌：被用于 Anthropic Claude 4 和 OpenAI GPT-4o 的数据分析后端。

合理选择ClickHouse表引擎

1. MergeTree系列（核心首选）

ClickHouse表引擎分为四个核心系列，90%的业务场景优先选择‌MergeTree系列‌，其他系列适配特殊场景：

‌1.MergeTree‌

核心特性:支持分区、排序主键索引、稀疏索引、数据TTL，不提供主键去重 

 适用场景:通用海量数据分析、日志存储、用户行为分析、物联网时序数据

2.ReplacingMergeTree‌

核心特性:继承MergeTree特性，合并时删除排序键相同的重复数据（仅保留最新版本）||需要去重/支持行级更新的场景

 适用场景:需要去重的场景，比如设备重复上报、业务数据全量同步（保留最新值）

3‌.SummingMergeTree‌

核心特性：需要预聚合汇总（如计数、统计类指标）

适用场景:合并时按排序键对数值列自动求和，适合简单计数器场景，需要提前聚合的报表、统计类场景，可减少存储空间提升查询速度

4.ReplicatedMergeTree‌

核心特性： 在MergeTree基础上增加数据复制能力，保证高可用

适用场景:  分布式集群场景，需要数据冗余保证高可用性

5.AggregatingMergeTree‌

复杂预聚合（结合物化视图做实时聚合）

支持任意聚合函数状态存储，配合物化视图实现高性能实时聚合

6.CollapsingMergeTree‌

需要处理行级变更（如取消已插入订单）

通过sign标记（+1插入/-1删除）在合并时折叠无效行

2. Log系列（轻量小表场景）
功能简单，适合小批量一次性写入、多次读取，不支持修改/索引：

‌TinyLog‌：最简单性能最低，适合100万行以内小表、临时数据存储
‌StripeLog‌：支持并发查询，比TinyLog性能更高
‌Log‌：Log系列性能最高，支持并发，适合小批量数据的中间表场景

3. 特殊/集成引擎（适配特殊需求）
‌Memory‌：数据全存在内存，查询极快但重启丢失，适合临时表/小维度表
‌File‌：直接将本地文件作为存储，适合格式转换/数据导出场景，不需要导入数据，快速预览外部数据
‌Kafka‌：直接消费Kafka消息，配合物化视图实现实时数据接入
‌Distributed‌：分布式查询代理，实现跨节点数据查询，不存储实际数据
‌Null‌：写入数据直接丢弃，用于测试和占位

✅ ‌推荐选择逻辑‌：

绝大多数OLAP场景 → 直接选MergeTree系列，根据是否需要去重选择具体变体
小批量临时数据 → 选Log系列或Memory
对接外部数据源 → 选择对应集成引擎（Kafka/MySQL/JDBC等）
分布式集群 → 搭配Distributed引擎做查询代理

选型总结口诀
生产分析选MergeTree，分布式集群加Replicated前缀
需要预聚合看场景：简单计数选Summing，复杂聚合选Aggregating
需要去重用Replacing，行级变更用Collapsing
小数据临时表选Log，测试中间结果选Memory
对接外部系统直接选对应集成引擎即可

ClickHouse提供大量集成引擎，直连外部系统不用导入数据即可查询：

消息队列：Kafka、NATS、RabbitMQ，适合流式数据接入
关系数据库：MySQL、PostgreSQL、MongoDB、ODBC/JDBC，直连外部数据库查询
对象存储/大数据：S3、HDFS、Hive、Iceberg、Delta Lake，对接大数据生态的存量数据

 

 

这是ClickHouse的版本查询执行结果，具体信息如下：

@@version返回值为5.7.30：这是MySQL兼容层的版本号，说明你通过ClickHouse的MySQL兼容接口（默认端口9004）连接了服务，该接口模拟了MySQL 5.7.30的协议版本。
version()返回值为26.4.3.37：这是ClickHouse自身的实际版本号，代表你使用的ClickHouse服务端版本为26.4.3.37。

 

 

建表语句

CREATE TABLE [IF NOT EXISTS] [db.]table_name
[ON CLUSTER cluster_name]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [COMMENT 'comment'],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [COMMENT 'comment'],
...
INDEX index_name expr TYPE type(...) GRANULARITY value
)
ENGINE = engine_name()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...];

‌[IF NOT EXISTS]‌：如果表已存在，不报错也不执行任何操作。
‌[ON CLUSTER cluster_name]‌：在集群模式下，该语句会在所有节点上广播执行，确保集群元数据一致。
‌ENGINE‌：‌必填项‌。决定数据存储方式、索引支持和并发特性（如 MergeTree, ReplacingMergeTree, Distributed 等）。
‌PARTITION BY‌：分区键。用于数据管理（如按月删除过期数据），建议按时间低基数字段分区。
‌ORDER BY‌：排序键。‌MergeTree系列引擎必填‌。决定数据在磁盘的物理存储顺序，也是稀疏索引的基础。
‌PRIMARY KEY‌：主键。默认与 ORDER BY 相同。ClickHouse主键不保证唯一性，仅用于加速查询。
‌TTL‌：数据生存时间。用于自动清理过期数据或移动数据到冷存储。

[SAMPLE BY expr]：采样键定义
该子句用于定义表的‌采样表达式（Sampling Key）‌，主要配合查询语句中的 SAMPLE 子句使用，旨在对海量数据进行‌近似查询‌以大幅提升性能。

[SETTINGS name=value, ...]：表级参数配置 是‌运维调优手段‌，用于根据表的读写特征定制底层存储引擎的行为，如索引密度、合并策略和资源限制。

应用行为日志表

CREATE TABLE IF NOT EXISTS app_behavior_logs
ON CLUSTER default_cluster -- 在集群所有节点执行
(
-- 1. 基础字段
event_time DateTime CODEC(Delta, ZSTD(1)) COMMENT '事件发生时间',
user_id UInt64 COMMENT '用户ID',
app_id String COMMENT '应用ID',
action_type LowCardinality(String) COMMENT '行为类型: click, view, purchase',
device_model String COMMENT '设备型号',

-- 2. 物化列 (自动计算，不依赖插入数据，节省存储且查询快)
event_date Date MATERIALIZED toDate(event_time) COMMENT '事件日期，用于分区',
hour UInt8 MATERIALIZED toHour(event_time) COMMENT '小时字段，用于高频聚合',

-- 3. 别名列 (不存储，查询时实时计算，适合衍生指标)
is_vip_user UInt8 ALIAS if(user_id % 100 = 0, 1, 0) COMMENT '模拟VIP标识',

-- 4. 二级索引 (Skip Index)，加速非主键字段的过滤
INDEX idx_device_model device_model TYPE minmax GRANULARITY 4,
INDEX idx_action_type action_type TYPE set(0) GRANULARITY 4

)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/app_behavior_logs', '{replica}')
PARTITION BY toYYYYMM(event_date) -- 按月分区，便于管理过期数据
ORDER BY (app_id, user_id, event_time) -- 排序键：先按应用聚簇，再按用户和时间
PRIMARY KEY (app_id, user_id) -- 主键：稀疏索引的基础
SAMPLE BY intHash32(user_id) -- 采样键：必须包含在主键中，用于近似查询
TTL event_time + INTERVAL 90 DAY -- 数据保留90后自动删除
SETTINGS
index_granularity = 8192, -- 默认索引粒度
min_merge_bytes_to_use_direct_io = 1073741824; -- 合并超过1GB时使用直接IO

 

 

 

ClickHouse的分区是数据管理手段，核心目标是减少扫描数据，优先遵循以下原则：

1. 核心设计原则
✅ ‌选择低基数、易于管理的字段作为分区键‌，推荐分区数控制在‌100~1000‌以内：

优先选择‌时间字段‌做分区（按天/按月，避免按小时/分钟这种过细粒度分区
禁止选择高基数字段（如user_id、设备号、订单号）作为分区键，会导致分区爆炸，影响性能
分区键尽量用原始字段或简单表达式，避免复杂函数，方便分区裁剪

 

常见分区方案
场景                              推荐分区粒度                                                                 优势
日志/监控/时序数据      按月（toYYYYMM(ts)）或按天（toYYYYMMDD(ts)） 方便按时间过期（配合TTL自动清理过期数据，批量删除/归档不需要扫描全表，查询时自动裁剪无关分区提升性能
离线批量数据               按业务批次/日期                                                             便于按批次管理数据

 

 
| 主键排序 | 按查询频率从高到低排列，低基数列放前面 |
| 分区键 | 用时间字段，粒度控制在天/月，避免过细 |
| TTL | 必须设置数据过期，避免数据无限增长 |
| 写入 | 每批1~10万行，避免逐行写入，同表每秒写入不超过1次 |

 

在ClickHouse中，‌TTL（Time To Live，数据生存时间）‌是用于自动管理数据生命周期的核心机制，支持自动删除过期数据、迁移冷热数据。

‌列级TTL‌    过期后该列值自动重置为对应类型默认值（数值型→0、字符串→空、时间→1970-01-01）    敏感数据自动脱敏、历史高精度数据降采样、释放存储空间
‌表级TTL‌    过期后整行数据自动删除                                                                                                           日志/监控数据过期清理、自动淘汰历史数据

 

-- 列级TTL示例：90天后脱敏敏感信息、180天后金额自动清空
CREATE TABLE example_column_ttl(
id UInt64,
created_at DateTime,
sensitive_data String TTL created_at + INTERVAL 90 DAY,
amount Float64 TTL created_at + INTERVAL 180 DAY
) ENGINE = MergeTree() ORDER BY id;

-- 表级TTL示例：保留7天查询日志，过期自动删除
CREATE TABLE system.query_log(
...
) ENGINE = MergeTree() ORDER BY event_date
TTL event_date + INTERVAL 7 DAY;

 

修改TTL规则使用ALTER TABLE MODIFY TTL语句，支持表级和列级修改：

-- 修改表级TTL：从保留7天改为保留30天
ALTER TABLE system.query_log MODIFY TTL event_date + INTERVAL 30 DAY;

-- 给已有列新增/修改TTL
ALTER TABLE example_column_ttl MODIFY COLUMN sensitive_data
TTL created_at + INTERVAL 30 DAY;

-- 强制立即生效TTL规则
ALTER TABLE example_column_ttl MATERIALIZE TTL;

3. 移除TTL规则
如果需要取消已设置的TTL，可以使用REMOVE TTL语句移除：‌

ALTER TABLE example_table REMOVE TTL;

TTL结合冷热分层存储
除了删除数据，TTL还可以配合存储策略，实现‌热→温→冷数据自动分层迁移‌：将近期热数据放SSD保证查询速度，过期数据自动迁移到低成本HDD归档。

-- 示例：当前数据放热存储，2年后移温存储，4年后移冷存储
ALTER TABLE my_table MODIFY TTL
trade_date TO VOLUME 'hot_volume',
trade_date + INTERVAL 2 YEAR TO VOLUME 'warm_volume',
trade_date + INTERVAL 4 YEAR TO VOLUME 'cold_volume';

 

TTL 表达式的基本格式为：

sql
TTL <时间列> + INTERVAL <数值> <单位> [动作]
‌时间列‌：必须是 Date 或 DateTime 类型的字段。
‌单位‌：SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR。
‌动作‌（可选）：
DELETE：删除整行数据（表级 TTL 默认动作）。
TO DISK '磁盘名'：将数据片段移动到指定磁盘。
TO VOLUME '卷名'：将数据片段移动到指定存储卷。

 

混合 TTL（分层存储 + 最终删除）
适用于冷热分离场景：热数据在 SSD，冷数据迁移到 HDD/S3，最后删除。

CREATE TABLE metrics (
metric_time DateTime,
value Float64
) ENGINE = MergeTree()
ORDER BY metric_time
-- 7天后迁移到名为 'hdd_volume' 的存储卷
-- 90天后彻底删除
TTL
metric_time + INTERVAL 7 DAY TO VOLUME 'hdd_volume',
metric_time + INTERVAL 90 DAY DELETE;

 

索引

设计 ClickHouse 索引的核心逻辑与传统关系型数据库（如 MySQL）截然不同。ClickHouse 没有 B+ 树索引，而是基于‌稀疏主键索引（Primary Index）‌和‌数据跳过索引（Data Skipping Indexes）‌的组合。

一、 核心原则：理解“排序即索引”
在 ClickHouse 中，ORDER BY 子句定义的列就是‌主键索引‌。

‌数据物理有序‌：数据在磁盘上严格按照 ORDER BY 指定的列顺序存储。
‌稀疏索引‌：ClickHouse 每隔固定行数（默认 8192 行，称为一个 Granule/颗粒）记录一次主键值。
‌查询加速原理‌：查询时，通过稀疏索引快速定位到可能包含数据的 Granule，然后只读取这些 Granule 的数据块，跳过无关数据。
‌关键结论‌：设计好 ORDER BY 就完成了 80% 的索引优化工作。