OLAP数据库之ClickHouse

ClickHouse

​ ClickHouse是面向联机分析处理OLAP的列式数据库管理系统，支持SQL查询，且查询性能好，特别是基于大宽表的聚合分析查询性能非常优异，比其他分析型数据库速度快一个数量级。主要特性如下。

  • 数据压缩比高。
  • 多核并行计算。
  • 向量化计算引擎。
  • 支持嵌套数据结构。
  • 支持稀疏索引。

优势及短板

clickhouse 在以下场景非常有优势：

  • 单表实时分析
  • 海量数据聚合（10 亿行数据）
  • 高吞吐写入（批量）
  • 列存压缩需求

短板

尽管 ClickHouse 在单表聚合、全表扫描等场景中性能领先，但在以下方面可能不如竞品：

  1. 频繁更新数据效率低
  2. 复杂查询优化不足
  3. 高并发查询能力有限
  4. 事务支持不完善

数据更新与删除的低效性

问题 ：ClickHouse 对 UPDATE 和 DELETE 操作支持有限，尤其是高频的实时更新。

原因 ：MergeTree 引擎通过异步合并（Merge）实现数据修改，会产生：

  • 写放大 ：更新一行可能触发整个数据块（Part）的重写。
  • 延迟可见 ：修改操作需等待后台合并完成才能生效。

适用场景 ：
适合批量写入（如日志、指标数据），不适合频繁更新的事务场景（如订单状态更新）。

复杂查询优化不足

问题 ：
对多表关联（JOIN）、子查询、复杂表达式等场景优化较弱。

具体表现 ：

  • JOIN 性能差 ：仅支持 ANY JOIN 和 GLOBAL JOIN，大表 JOIN 易引发内存溢出。
  • 子查询限制 ：嵌套子查询可能导致全表扫描，需手动改写为临时表或物化视图。
  • CASE WHEN 性能开销 ：复杂条件判断可能无法有效利用索引。

解决方案 ：

  • 预处理数据为宽表（Denormalization）。
  • 使用外部计算引擎（如 Spark）预处理复杂逻辑。

高并发查询能力有限

问题 ：ClickHouse 单节点的并发查询能力较弱（通常建议 < 100 QPS ），高并发场景下容易因资源竞争导致性能下降。

原因 ：向量化执行引擎和列存设计更适合批量处理，而非高频率的小查询。

缓解方案 ：

  • 使用分布式集群分摊负载。
  • 通过缓存（如 Redis）预聚合热点数据。
  • 避免直接暴露给终端用户高并发查询（如通过中间层服务聚合结果）。

缺乏完整的事务支持

问题 ：不支持 ACID 事务，无法保证跨行或跨表操作的原子性和一致性。

影响场景 ：金融、电商等对数据一致性要求严格的业务需谨慎使用。

总结：

ClickHouse 的局限性主要源于其为 OLAP 场景极致优化的设计哲学 ，牺牲了部分通用性以换取性能。在选择时需权衡：

  • 优势 ：实时分析、列存压缩、海量数据聚合。
  • 劣势 ：高并发、事务、复杂查询、运维复杂度。

OLAP场景

OLAP场景的关键特征

  • 绝大多数是读请求
  • 数据以相当大的批次(> 1000行)更新，而不是频繁单行更新，或者根本没有更新。
  • 已添加到数据库的数据基本不会修改。
  • 对于读取，从数据库中提取相当多的行，但只提取少部分列。
  • 宽表，即每个表包含着大量的列（成百上千）
  • 事务不是必须的

​ 很容易可以看出，OLAP场景与其他通常业务场景(例如,OLTP或K/V)有很大的不同， 因此想要使用OLTP或Key-Value数据库去高效的处理分析查询场景，并不是非常完美的适用方案。例如，使用OLAP数据库去处理分析请求通常要优于使用MongoDB或Redis去处理分析请求。

发版历史

从2016年开源至今，ClickHouse经历三个版本阶段：初始版本命名混乱，

2018年起采用年份为主版本号，2020年后稳定版每月一更，LTS版本提供长期支持。

基本使用

SQL

ClickHouse 支持基于 SQL 的声明式查询语言，在许多情况下与 ANSI SQL 标准相同。

支持的查询包括 GROUP BY、ORDER BY、FROM 中的子查询、JOIN 子句、IN 操作符、窗口函数和标量子查询。

用户通过 SQL 语句与 ClickHouse 进行交互。ClickHouse 支持常见的 SQL 语句，如 SELECT 和 CREATE，但它还提供了特定的语句，如 KILL 和 OPTIMIZE。

表连接

支持的 JOIN 类型

所有标准的 SQL JOIN 类型均被支持：

  • INNER JOIN，仅返回匹配的行。
  • LEFT OUTER JOIN，除了匹配的行外还返回左表中的不匹配行。
  • RIGHT OUTER JOIN，除了匹配的行外还返回右表中的不匹配行。
  • FULL OUTER JOIN，返回两个表中不匹配的行，外加匹配的行。
  • CROSS JOIN，生成整个表的笛卡尔积，未指定“连接键”。

未指定类型的 JOIN 将隐含为 INNER。关键字 OUTER 可以安全省略。在 FROM 子句 中用逗号分隔多个表是 CROSS JOIN 的替代语法。

性能

​ 执行 JOIN 时，不会针对查询的其他阶段优化执行顺序。连接（在右表中的搜索）是在 WHERE 过滤之前和聚合之前执行的。

​ 每次以相同的 JOIN 运行查询时，子查询都会再次运行，因为结果不会被缓存。为避免这种情况，使用特殊的 Join 表引擎，这是一个始终在内存中的连接准备数组。

​ 在某些情况下，使用 IN 可能比 JOIN 更有效。

​ 如果需要使用维度表进行连接（这些是相对较小的表，包含维度属性，例如广告活动的名称），由于每个查询都要重新访问右表，因此 JOIN 可能不是很方便。在这种情况下，建议使用字典功能，而不是 JOIN。

数据类型

参考：https://clickhouse.com/docs/zh/sql-reference

Clickhouse 支持 100 多种数据类型。

基本数据类型

Bool：布尔类型，在内部存储为 Uint8，true 为 1，false 为 0；

UInt8, UInt16, UInt32, UInt64：无符号整数类型，分别占用 1 字节、2 字节、4 字节、8 字节；

Int8, Int16, Int32, Int64：有符号整数类型，分别占用 1 字节、2 字节、4 字节、8 字节；

Float32, Float64：浮点数类型，分别单精度和双精度，分别占用 4 字节、8字节；

Decimal32，Decimal64，Decimal128：高精度类型，原生方式为 Decimal(P, S)；

String：字符串类型，可变长度；

FixedString(N)：固定长度字符串类型，指定长度为 N，插入的字符串长度少于 N，则补空，大于 N，则报异常；

日期类型

Date：日期类型，以 YYYY-MM-DD 格式存储；

DateTime：日期时间类型，以 YYYY-MM-DD HH:MM:SS 格式存储；

DateTime64(N)：带有精度 N 的日期时间类型，N 为从 1 到 9 的精度值；

复杂类型

Array(T)：数组类型，包含元素类型 T 的数组；

Nested：嵌套类型，支持嵌套结构；

Tuple(T1, T2, …)：元组类型，包含多个字段，字段类型可以是不同的数据类型；

Map(K ,V)：键值类型，映射不唯一，即一个映射可以包含两个具有相同键的元素；

Enum：枚举类型，用来定义常量；

聚合类型

AggregateFunction：聚合函数类型，具有实现定义的中间状态，可以将其序列化为 AggregateFunction(…) 数据类型并存储在表中，通常使用物化视图来存储。产生聚合函数状态的常用方法是调用带后缀的聚合函数 -State。将来要获得聚合的最终结果，必须使用带 -Merge 后缀的相同聚合函数；

SimpleAggregateFunction：产生聚合函数值的常见方法是调用带有 -SimpleState 后缀的聚合函数，如 min、max、sum、groupArrayArray 等。SimpleAggregateFunction 比使用相同的聚合函数具有更好的性能 AggregateFunction；

其他数据类型

UUID：UUID类型，用于存储全局唯一标识符；

IPv4、IPv6 类型：IP类型，IPv4 基于 UInt32 封装，IPv6 基于 FixedString(16) 封装，包含格式检查；

Nullable(T)：可空类型，包装类型 T，允许存储 NULL 值； 

-- 示例：创建一个包含数组列的表
CREATE TABLE example_array_table
(
    id UInt32,
    tags Array(String),        -- 字符串数组
    scores Array(Int32),       -- 整数数组
    matrix Array(Array(Float64))  -- 二维浮点数数组
)
ENGINE = MergeTree()
ORDER BY id;

-- 插入单行数据
INSERT INTO example_array_table VALUES
(1, ['clickhouse', 'database'], [90, 85], [[1.1, 2.2], [3.3, 4.4]]);

-- 插入多行数据
INSERT INTO example_array_table VALUES
(2, ['analytics', 'OLAP'], [88, 92], [[5.5], [6.6]]),
(3, ['sql', 'array'], [75, 80], []);

表设计

-- 创建表
create table if not exists article_view_events (
	postId Nullable(Float64),
	viewTime DateTime DEFAULT now(),
	clientId String DEFAULT 'unknown',
) engine = MergeTree 
order by ();

-- 
create table if not exists hacker_news_articles (
	id Nullable(Float64),
	deleted Nullable(Float64),
	type Nullable(String),
	by Nullable(String),
	time Nullable(String),
	text Nullable(String),
	dead Nullable(Float64),
	parent Nullable(Float64),
	poll Nullable(Float64),
	kids Array(Nullable(Float64)),
	url Nullable(String),
	score Nullable(Float64),
	title Nullable(String),
	parts Array(Nullable(Float64)),
	descendants Nullable(Float64)
) engine = MergeTree 
order by ();
-- 删除表结构
drop table if exists hacker_news_articles;

函数

和大多数数据库一样，clickhouse内置了很多系统函数，这些函数基本上满足所有业务场景，但用户也可以自定义函数。

内置函数

  • 常规函数
  • 聚合函数
  • 表函数
  • 窗口函数

自定义函数

更新数据

​ 无论是修改数据，还是删除数据，亦或是新增单行数据，都是ClickHouse 不推荐频繁使用的功能。因为这会影响clickhouse的性能。clickhouse所擅长的领域是批量新增数据，宽表和预聚合，以支持高效的查询数据、聚合统计。

新增

CREATE TABLE insert_select_testtable
(
    `a` Int8,
    `b` String,
    `c` Int8
)
ENGINE = MergeTree()
ORDER BY a;


INSERT INTO insert_select_testtable (*) VALUES (1, 'a', 1);

修改

​ ClickHouse 支持 UPDATE 语句，但与传统关系型数据库（如 MySQL）的实时行级更新不同。它的 UPDATE 操作本质上是异步的、非阻塞的，并且受限于存储引擎和数据模型的特性。

轻量级删除

​ 轻量级 DELETE 实际上是逻辑删除，clickhouse 设计了一个隐藏的系统列 _row_exists，当执行 delete from 进行逻辑删除时，它标记该行为已删除，标记为逻辑删除的行并不会在后续的 select 中查询出来。

​ 逻辑删除并不会立即物理删除这些行，这只会在下次合并时发生。因此，在未指定的时间内，数据实际上可能并未从存储中删除，而只是标记为删除。

​ 之所以称为“轻量级 DELETE”，是为了与 ALTER TABLE ... DELETE 命令进行对比，后者是一种重量级过程。

-- 删除 `hits` 表中 `Title` 列包含文本 `hello` 的所有行
DELETE FROM hits WHERE Title LIKE '%hello%';

​ 默认情况下，DELETE 语句会在标记行已删除完成后才返回。如果数据量很大，这可能需要很长时间。或者，您可以使用设置 lightweight_deletes_sync 在后台异步运行它。如果禁用，则 DELETE 语句将立即返回，但在后台变更完成之前，查询仍然可以看到数据。

小结：

​ 使用掩码技术的轻量级 DELETE 在性能上优于传统的 ALTER TABLE ... DELETE，因为它并不重新写入所有受影响部分的所有列文件。

数据分区与合并

​ Data part是指存储在硬盘上的一个数据片段，它是ClickHouse表数据存储的基本单位。每次向ClickHouse表写入数据时，都会生成一个新的数据分片。每个数据分片都是自包含的，包括了该部分数据的所有列和索引，且保持着数据的有序性。数据分片的设计支持了高效的合并和压缩操作，这对于ClickHouse的高性能查询处理至关重要。

数据分区

每当一组行被插入到表中时，就会创建一个数据分区。以下图示简要说明了这一过程：

当ClickHouse服务器处理上面的示例插入（例如，通过INSERT INTO 语句）时，它会执行几个步骤：

① 排序：这些行根据表的排序键(town, street)进行排序，并为排序后的行生成一个稀疏主键索引。

② 拆分：排序后的数据被拆分成列。

③ 压缩：每一列都被压缩。

④ 写入磁盘：被压缩后的列作为二进制列文件保存在一个新的目录中，该目录表示插入的数据分区。稀疏主键索引也被压缩并保存在同一目录中。

image-20250411222341349

分区合并

​ 为了管理每个表的分区数量，后台合并作业会定期将较小的分区合并为较大的分区，直到它们达到可配置的压缩大小（通常约150 GB）。合并后的分区被标记为非活动状态，并在可配置的时间间隔后被删除。随着时间的推移，这一过程创建了一个合并分区的层次结构，这就是为什么它被称为MergeTree表的原因。

性能调优

推荐批量插入或异步插入

​ 为了减少初始分区数量和合并的开销，数据库客户端被鼓励批量插入元组，例如一次插入20,000行，或使用异步插入模式，在这种模式下，ClickHouse将来自多个插入的行缓冲到同一个表中，仅在缓冲区大小超过可配置阈值或超时到期后才创建新的分区。

监控分区

您可以通过使用虚拟列 _part 来查询我们示例表当前所有活动分区的列表：

SELECT _part
FROM uk.uk_price_paid_simple
GROUP BY _part
ORDER BY _part ASC;

   ┌─_part───────┐
1. │ all_0_5_1   │
2. │ all_12_17_1 │
3. │ all_18_23_1 │
4. │ all_6_11_1  │
   └─────────────┘

表分区

​ 在 创建表 时，可以在 PARTITION BY expr 子句中指定分区。分区键可以是来自表列的任何表达式。例如，要按月进行分区，可以使用表达式 toYYYYMM(date_column)：

CREATE TABLE visits
(
    VisitDate Date,
    Hour UInt8,
    ClientID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(VisitDate)
ORDER BY Hour;

向表中插入新数据时，这些数据作为按主键排序的单独部分（块）存储。在插入后 10-15 分钟内，相同分区的部分会合并为整个部分。

注：不应创建过于精细的分区（如一个表超过1000个分区）。一个表的分区过多会造成SELECT 查询的性能很差，因为文件系统中的文件数量和打开的文件描述符过多。

查看分区状态

​ 使用 system.parts 表查看表的部分和分区。例如，假设我们有一个按月分区的 visits 表。让我们执行 system.parts 表的 SELECT 查询：

SELECT
    partition,
    name,
    active
FROM system.parts
WHERE table = 'visits'

查询结果：

┌─partition─┬─name──────────────┬─active─┐
│ 201901    │ 201901_1_3_1      │      0 │
│ 201901    │ 201901_1_9_2_11   │      1 │
│ 201901    │ 201901_8_8_0      │      0 │
│ 201901    │ 201901_9_9_0      │      0 │
│ 201902    │ 201902_4_6_1_11   │      1 │
│ 201902    │ 201902_10_10_0_11 │      1 │
│ 201902    │ 201902_11_11_0_11 │      1 │
└───────────┴───────────────────┴────────┘

active 列显示部分的状态。1 是活动的；0 是非活动的。例如，合并后留下的部分就是非活动的。损坏的数据部分也被标记为非活动。

ClickHouse 会定期合并插入的数据部分，大约在插入后 15 分钟。

非活动部分将在合并后大约 10 分钟后被删除。

在 ClickHouse 的 MergeTree 引擎中，分区键的声明直接影响数据存储结构和合并行为。以下是详细对比分析：

未声明分区键

如果建表时没有指定 PARTITION BY 子句，ClickHouse 会默认将所有数据存储在一个单一的分区 中。这种情况下：

  • 数据存储 ：所有数据文件（Data Part）会集中存储在同一个分区目录下（例如 all_1_1_0）。
  • 查询性能 ：查询时无法利用分区裁剪（Partition Pruning），必须扫描全表数据，可能影响性能。
  • 合并行为 ：后台合并（Merge）会针对整个表的数据进行，可能导致较大的合并开销（尤其是数据量大时）。
  • 数据管理 ：无法按分区粒度进行数据删除（如 ALTER TABLE ... DROP PARTITION）或 TTL 管理。
-- 示例：未声明分区键的表
CREATE TABLE example_no_partition (
    event_date Date,
    user_id UInt32,
    value String
) ENGINE = MergeTree()
ORDER BY (user_id, event_date);

当通过 PARTITION BY 明确指定分区键后，数据会按分区键的值分散存储到多个分区中。例如：

-- 示例：按月份分区
CREATE TABLE example_with_partition (
    event_date Date,
    user_id UInt32,
    value String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)  -- 按年月分区
ORDER BY (user_id, event_date);

声明分区键

当通过 PARTITION BY 明确指定分区键后，数据会按分区键的值分散存储到多个分区中。例如：

-- 示例：按月份分区
CREATE TABLE example_with_partition (
    event_date Date,
    user_id UInt32,
    value String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)  -- 按年月分区
ORDER BY (user_id, event_date);

此时的行为差异如下：

数据存储

  • 数据按分区键的值分散到多个分区目录中（例如 202310_1_1_0, 202311_1_1_0）。
  • 每个分区独立存储，物理上隔离不同分区的数据。

查询性能

  • 分区裁剪 ：查询条件包含分区键时（如 WHERE event_date BETWEEN '2023-10-01' AND '2023-10-31'），ClickHouse 会跳过无关分区，减少扫描数据量。
  • 并行处理 ：分布式查询中，不同分区的数据可以并行读取，提升吞吐。

合并行为

  • 合并操作以分区为单位进行，不同分区的数据不会合并在一起。
  • 每个分区内的数据按 ORDER BY 键排序和合并，减少写放大。

数据管理

  • 支持按分区粒度操作：
    • 删除分区：ALTER TABLE example_with_partition DROP PARTITION '202310';
    • 移动分区：ALTER TABLE ... MOVE PARTITION ...
    • TTL 按分区清理过期数据。

最佳实践

  • 分区粒度 ：避免分区过细（如按天分区导致大量小分区）或过粗（如按年分区导致单个分区过大）。通常按月或按天分区较为合理。
  • 与 ORDER BY 的关系 ：分区键通常是 ORDER BY 键的前缀，确保数据在分区内有序。
  • 避免过度分区 ：分区数量应控制在合理范围（通常建议不超过 1000 个分区/表），否则会增加元数据管理和文件系统开销。

总结对比

特性 未声明分区键 声明分区键
数据存储 单一分区 多个分区（按分区键值划分）
查询性能 无法分区裁剪，全表扫描 支持分区裁剪，减少扫描数据量
合并效率 全局合并，开销大 分区级合并，效率更高
数据管理 无法按分区操作 支持按分区删除、移动、TTL 等
适用场景 小数据量或临时表 大数据量，需高效查询与管理

合理设计分区键可以显著提升 ClickHouse 的查询性能和数据管理能力，但需根据实际业务场景权衡分区策略。

分区键也分为以下几种策略：

  • 单一键分区
  • 组合键分区
  • 动态分区策略

​ 无论采用哪种策略，最终的目的是减少数据扫描的范围，但随着分区数量的增多，也会带来相应的负面影响，比如存储变大，查询性能降低。

  • 单一键分区策略可能出现的问题是分区粒度太粗，扫描的数据还是太多，性能优化不明显；
  • 组合键分区可能出现的问题是分区粒度太细，在全表统计的场景下，性能降低。并且增加存储成本。
  • 动态分区策略可以灵活的控制哪些数据进行分区，但会增加运维成本。

动态分区策略

-- 按年分区为主，但对高频问题（如TOP20）单独分区
ALTER TABLE survey_answer_data 
ATTACH PARTITION '2024_高频问题ID' 
FROM SELECT ... WHERE questionId IN (高频列表);

决策建议

因素 按年分区 按问题ID分区 混合分区
单问题查询性能 ★★☆ ★★★★☆ ★★★★☆
全问卷分析性能 ★★★★☆ ★☆☆☆☆ ★★★☆☆
运维复杂度 ★★☆ ★☆☆☆☆ ★★★★☆
数据冷热分离 ★★☆ ★★★★☆ ★★★★☆

推荐选择：

优先保留按年分区，通过优化ORDER BY和添加索引提升单问题查询性能。

仅在以下情况采用问题ID分区：

  1. 超过80%的查询是单问题分析
  2. 接受全表分析性能下降
  3. 有专职团队维护分区策略

主索引

有两种方式声明主键索引：

1.不显示声明：如果我们只指定了排序键，则主键将被隐式定义为等于排序键。

CREATE TABLE hits_UserID_URL
(
    `UserID` UInt32,
    `URL` String,
    `EventTime` DateTime
)
ENGINE = MergeTree
ORDER BY (UserID, URL, EventTime);

2.显示声明：如果我们只指定了排序键，则主键将被隐式定义为等于排序键。

CREATE TABLE hits_UserID_URL
(
    `UserID` UInt32,
    `URL` String,
    `EventTime` DateTime
)
ENGINE = MergeTree
PRIMARY KEY (UserID, URL)
ORDER BY (UserID, URL, EventTime);

ClickHouse 允许插入具有相同主键列值的多行。在此情况下，最终顺序由指定的排序键决定，因此取决于 EventTime 列的值。

我们创建的表具有

  • 复合 主键 (UserID, URL) 和
  • 复合 排序键 (UserID, URL, EventTime)。

备注：

​ 数据在磁盘上的存储是按主键列排序的，然后稀疏索引是存储在内存中的，一个稀疏索引就能对应8192行数据。

在磁盘表示上，每个表列都有一个单独的数据文件（*.bin），其中该列的所有值以 压缩 格式存储，并且

8.87 百万行按主键列（和附加排序键列）的字典升序存储在磁盘上，即在这个例子中

  • 首先按 UserID，
  • 然后按 URL，
  • 最后按 EventTime
UserID.bin、URL.bin 和 EventTime.bin 是存储 UserID、URL 和 EventTime 列值的磁盘数据文件。

由于主键定义了磁盘上行的字典顺序，因此一个表只能有一个主键。

颗粒 granules

​ 数据被组织为颗粒以进行并行数据处理。出于数据处理的目的，表的列值逻辑上被划分为颗粒。 颗粒是流入 ClickHouse 进行数据处理的最小不可分割数据集。 这意味着 ClickHouse 总是以流的方式（并行）读取整组（颗粒）行，而不是单行读取。

​ 列值不在物理上存储在颗粒中：颗粒只是列值的逻辑组织形式，以便于查询处理。

示例：

​ 表的 8.87 百万行（列值）是如何被组织成 1083 个颗粒的，这是由于表的 DDL 语句包含了设置 index_granularity（设置为其默认值 8192）。

​ 8870000 ÷ 8192 = 1082.76 ，所以一共 1083 个颗粒，基于磁盘的物理顺序，前 8192 行（它们的列值）逻辑上属于颗粒 0，接下来的 8192 行（它们的列值）属于颗粒 1，以此类推。

视图

视图可以是 普通视图和物化视图。

普通视图

​ 普通视图不存储任何数据。它们在每次访问时仅从另一个表中读取数据。换句话说，普通视图只是一个保存的查询。当从视图中读取时，这个保存的查询被用作 FROM 子查询。

-- 假设您创建了一个视图：
CREATE VIEW view AS SELECT ...
-- 并编写了一个查询：
SELECT a, b, c FROM view
-- 这个查询与使用子查询完全等价：
SELECT a, b, c FROM (SELECT ...)

物化视图

在 ClickHouse 中，物化视图（Materialized View） 是一种预先计算并存储查询结果的数据库对象，它能显著加速复杂查询的执行。

物化视图又分两种：

  • 增量物化视图
  • 可刷新物化视图

功能特性

预计算与存储

  • 物化视图会预先执行查询逻辑，将结果集存储为物理表。
  • 查询时直接读取预存结果，避免重复计算。

自动或手动更新

  • 自动更新：当底层基表数据变更时，物化视图可通过 ATTACH 或 REFRESH 自动同步。
  • 手动更新：通过 ALTER MATERIALIZED VIEW ... REFRESH 触发更新。

独立存储引擎

  • 物化视图可指定独立的存储引擎（如 MergeTree、AggregatingMergeTree），支持数据压缩和分区。

语法

CREATE MATERIALIZED VIEW [IF NOT EXISTS] view_name
ENGINE = EngineType
[PARTITION BY partition_expr]
[ORDER BY order_expr]
AS
SELECT ... -- 查询逻辑

示例：

-- 创建物化视图（按天统计问卷提交数）
CREATE MATERIALIZED VIEW daily_survey_count
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMMDD(submit_time)
ORDER BY submit_time
AS
SELECT 
    toYYYYMMDD(submit_time) AS day,
    count() AS total_submissions
FROM survey_responses
GROUP BY day;

-- 直接查询预存结果
SELECT * FROM daily_survey_count 
WHERE day >= '2024-10-01';
CREATE MATERIALIZED VIEW daily_survey_mv TO survey_responses AS
SELECT 
    toStartOfDay(submit_time day,
    company, 
    post, 
    question_id, 
    any(question_type) as question_type,
    groupArray(option_ids) as options
FROM survey_responses
GROUP BY day, company, post, question_id;

可刷新的物化视图

与 ClickHouse 的 增量物化视图 不同，这需要定期在完整数据集上执行查询——其结果存储在目标表中以供查询。理论上，这个结果集应该比原始数据集小，从而使后续查询能够更快地执行。

性能优化

引擎选择

  • 高频查询：MergeTree + 分区键
  • 聚合统计：AggregatingMergeTree

索引优化

-- 为物化视图添加二级索引
ALTER TABLE daily_survey_count ADD INDEX idx_day day TYPE minmax GRANULARITY 1;

数据生命周期管理

-- 设置 TTL 自动清理旧数据（7天）
ALTER TABLE daily_survey_count 
MODIFY TTL day + INTERVAL 7 DAY;

​ ClickHouse 的物化视图是 高性能 OLAP 的核心工具，通过预计算和存储优化，可将复杂查询的响应时间从分钟级降低到毫秒级。合理设计物化视图的 分区策略 和 存储引擎，能显著提升大数据量场景下的分析效率。

占用存储空间过大

解决方案：

  • 启用数据压缩（SETTINGS index_granularity = 8192, compression = lz4）
  • 按时间分区并定期删除旧分区。

字典

有两种方式创建字典：

  • 使用DDL语句
  • 使用配置文件

语法：

CREATE DICTIONARY dict_name
(
    ... -- attributes
)
PRIMARY KEY ... -- 复杂或单一密钥配置
SOURCE(...) -- 来源配置
LAYOUT(...) -- 内存布局配置
LIFETIME(...) -- 字典在内存中的生命周期

示例：

-- 声明字典
CREATE DICTIONARY users_dict
(
  `Id` Int32,
  `Location` String
)
PRIMARY KEY Id
SOURCE(CLICKHOUSE(QUERY 'SELECT Id, Location FROM users'))
LIFETIME(MIN 600 MAX 900)
LAYOUT(HASHED());

-- 在查询中使用字典
SELECT
	Id,
	Title,
	dictGet('users_dict', 'Location', CAST(OwnerUserId, 'UInt64')) AS location
FROM posts
WHERE Title ILIKE '%clickhouse%'
LIMIT 5;

来源

字典的源可以是：

  • 数据库（Mysql、Redis、ClickHouse…）
  • 通过 HTTP(S) 可用的文件
  • 本地文件

从当前 ClickHouse 服务中的表创建字典：（假如现在有一个输入表：source_table）

SOURCE(CLICKHOUSE(
    host 'example01-01-1'
    port 9000
    user 'default'
    password ''
    db 'default'
    table 'source_table'
    where 'id=10'
    secure 1
	query 'SELECT id, value_1, value_2 FROM default.ids'
));

设置字段：

  • host – ClickHouse 主机。如果是本地主机，则查询在没有网络活动的情况下处理。为了提高容错性，您可以创建一个分布式表，并在后续配置中输入它。
  • port – ClickHouse 服务器的端口。
  • user – ClickHouse 用户的名称。
  • password – ClickHouse 用户的密码。
  • db – 数据库的名称。
  • table – 表的名称。
  • where – 选择标准。可以省略。
  • invalidate_query – 检查字典状态的查询。可选参数。更多信息请查看使用生存时间刷新字典数据部分。
  • secure - 在连接时使用 ssl。
  • query – 自定义查询。可选参数。

注：table 或 where 字段不能与 query 字段一起使用。并且必须声明 table 或 query 字段之一。

创建原始表

-- 用户表
CREATE TABLE users (
    user_id UInt32,
    country_name String
) ENGINE = MergeTree()
ORDER BY user_id;

INSERT INTO users VALUES (1, 'USA'), (2, 'Canada'), (3, 'UK');
-- 订单表
CREATE TABLE orders (
    order_id UInt32,
    user_id UInt32,
    amount Float64
) ENGINE = MergeTree()
ORDER BY order_id;

INSERT INTO orders VALUES (101, 1, 100.0), (102, 2, 200.0), (103, 1, 150.0);

传统方法：JOIN 

SELECT 
    o.order_id, 
    o.amount, 
    u.country_name
FROM orders o
JOIN users u ON o.user_id = u.user_id;

创建一个字典：

CREATE DICTIONARY user_country (
    user_id UInt32,
    country_name String
)
PRIMARY KEY user_id
SOURCE(CLICKHOUSE(TABLE 'users'))
LAYOUT(FLAT())
LIFETIME(MIN 0 MAX 3600); -- 每小时自动更新字典

查看字典的DDL

SHOW CREATE DICTIONARY id_value_dictionary;

内存布局 Layout

有多种方法可以在内存中存储字典。我们推荐flat、hashed 和 complex_key_hashed，这些方法提供最佳的处理速度。

不推荐使用磁盘缓存，因为可能会造成性能不佳及在选择最佳参数时的困难。

在ClickHouse中，字典的布局（Layout）决定了数据在内存中的存储方式和查询时的访问机制。不同布局适用于不同场景，以下是常见布局的对比及选择建议：

  • flat 扁平布局
  • hashed 哈希布局
  • complex_key_hashed
  • cache 缓存

flat 扁平布局

字典完全以平面数组的形式存储在内存中。字典使用多少内存？该量与最大key的大小成正比例。

字典键为UInt64类型，值被限制为 max_array_size（默认为 500,000）。如果在创建字典时发现更大的密钥，ClickHouse 会抛出异常并且不会创建字典。字典平面数组的初始大小由 initial_array_size 设置控制（默认为1024）。

LAYOUT(FLAT(INITIAL_ARRAY_SIZE 50000 MAX_ARRAY_SIZE 5000000))

hashed 哈希布局

字典完全以哈希表的形式存储在内存中。字典可以包含任意数量的元素和任意标识符。在实际应用中，键的数量可以达到数千万。

字典的键为UInt64类型。

LAYOUT(HASHED())

complex_key_hashed

这种存储类型用于复合键。类似于 hashed。

定期刷新数据

使用生存时间刷新字典数据

ClickHouse 会根据 LIFETIME 标签定期更新字典（以秒为单位定义）。

CREATE DICTIONARY (...)
...
LIFETIME(300)
...

设置 <lifetime>0</lifetime> (LIFETIME(0)) 将阻止字典更新。

可以为更新设置一个时间间隔，ClickHouse 将在此范围内选择一个均匀随机的时间。这是为了在多个服务器上更新时分散字典源的负载。

LIFETIME(MIN 300 MAX 360)

在更新字典时，ClickHouse 服务器根据源类型应用不同的逻辑：

  • 默认情况下，来自其他源的字典每次都更新。
  • 对于文本文件，它会检查修改时间。如果时间与先前记录的时间不同，则更新字典。
  • 对于其他源（ODBC、PostgreSQL、ClickHouse 等），您可以设置一个查询，仅在字典确实发生变化时才会更新，而不是每次都更新。

最佳实践

表引擎

表引擎（表的类型）决定了：

  • 数据如何存储，写入到哪里，以及从哪里读取。
  • 支持哪些查询，以及如何支持。
  • 并发数据访问。
  • 索引的使用（如果存在的话）
  • 是否可以多线程请求执行。
  • 数据复制参数

MergeTree 引擎

​ 针对高负载任务最通用和功能强大的表引擎。这些引擎的共同特征是快速的数据插入，以及随后的后台数据处理。 MergeTree 家族的引擎支持数据复制（带有 Replicated* 版本的引擎）、分区、二级数据跳过索引以及其他在其他引擎中不支持的功能。

MergeTree 引擎
MergeTree
ReplacingMergeTree
AggregatingMergeTree
SummingMergeTree
CollapsingMergeTree
VersionedCollapsingMergeTree
GraphiteMergeTree

MergeTree

​ MergeTree 引擎和 MergeTree 家族的其他引擎（例如 ReplacingMergeTree、AggregatingMergeTree）是 ClickHouse 中最常用和最强大的表引擎。

​ MergeTree 家族的表引擎旨在满足高数据摄取速率和大量数据的需求。 插入操作会创建表的分片，这些分片会与其他分片通过后台进程进行合并。

MergeTree 家族表引擎的主要特性包括：

  • 表的主键决定了每个表分片内的排序顺序（聚集索引）。主键还不引用单独的行，而是引用8192行的块，称为粒度。这使得大量数据集的主键足够小，可以保持在主内存中，同时仍提供对磁盘数据的快速访问。
  • 表可以使用任意分区表达式进行分区。当查询允许时，分区修剪确保省略对读取的分区。
  • 数据可以在多个集群节点之间复制，以实现高可用性、故障转移和零停机升级。
  • MergeTree 表引擎支持各种统计类型和抽样方法，以帮助查询优化。

Log 引擎

具有最小功能的轻量级 引擎。当您需要快速写入许多小表（最多约 100 万行）并随后整体读取时，它们是最有效的。

家族中的引擎：

Log 引擎
TinyLog
StripeLog
Log

集成引擎

用于与其他数据存储和处理系统进行通信的引擎。

集成引擎
ODBC
JDBC
MySQL
MongoDB
Redis
HDFS
S3
Kafka
EmbeddedRocksDB
RabbitMQ
PostgreSQL
S3Queue
TimeSeries

数据压缩

ClickHouse查询性能的秘密之一就是压缩。

磁盘上的数据越少，I/O就越少，查询和插入的速度也越快。在大多数情况下，任何压缩算法在CPU方面的开销都将被I/O减少所抵消。因此，确保ClickHouse查询快速时，首先应关注数据压缩的改进。

关于ClickHouse为何能如此有效地压缩数据，我们推荐这篇文章。总之，作为一款面向列的数据库，值将按列顺序写入。如果这些值是有序的，相同的值将相邻存放。压缩算法利用数据的连续模式。在此基础上，ClickHouse具有编解码器和粒度数据类型，允许用户进一步调整压缩技术。

ClickHouse中的压缩将受到三个主要因素的影响：

  • 排序键
  • 数据类型
  • 使用的编解码器

所有这些都通过模式进行配置。

压缩算法

ClickHouse支持大量编解码器和压缩算法。以下是按重要性排序的一些建议：

推荐 原因
ZSTD一直是最佳选择 ZSTD压缩提供最佳的压缩率。ZSTD(1)应是大多数常见类型的默认设置。通过修改数字值可以尝试更高的压缩率。我们很少看到在压缩成本（插入速度较慢）上高于3的值能带来足够的好处。
如果可能，优先选择LZ4而非ZSTD 如果你在LZ4和ZSTD之间得到可比的压缩，倾向于选择前者，因为它提供更快的解压和更少的CPU需求。然而，在大多数情况下，ZSTD的性能将比LZ4卓越。这些编解码器中的某些在与LZ4结合使用时可能工作得更快，同时与没有编解码器的ZSTD相比提供类似的压缩。不过，这将取决于具体数据，因此需要进行测试。

最佳实践

ClickHouse性能优化指南

性能调优

  • 批量插入
  • 异步插入
  • 避免变更
  • 避免Nullable列
  • 分区键

批量插入

​ 默认情况下，发送到 ClickHouse 的每个插入操作都会导致 ClickHouse 立即在存储中创建一个包含插入数据及其他需要存储的元数据的部分。因此，与发送大量每个数据量较少的插入相比，发送较少的每个包含更多数据的插入将减少所需的写入次数。通常，我们建议一次插入至少 1,000 行数据，理想情况下为 10,000 到 100,000 行。

异步插入

​ 默认情况下，ClickHouse 是以同步方式写入数据的。 每个发送到 ClickHouse 的插入都会导致 ClickHouse 立即创建一个包含插入数据的分片。 这是当 async_insert 设置为其默认值 0 时的默认行为。

有三种方式使用异步插入：

  • 为特定用户设置
  • 为插入语句设置
  • 在客户端的连接信息上设置

可以为特定用户或特定查询启用异步插入：

在用户级别启用异步插入。此示例使用用户 default，如果您创建了不同的用户，请替换该用户名：

ALTER USER default SETTINGS async_insert = 1

通过在插入查询中使用 SETTINGS 子句，可以指定异步插入设置：

INSERT INTO YourTable SETTINGS async_insert=1, wait_for_async_insert=1 VALUES (...)

当使用 ClickHouse 编程语言客户端时，也可以将异步插入设置作为连接参数指定。

例如，当您使用 ClickHouse Java JDBC 驱动程序连接到 ClickHouse Cloud 时，可以通过 JDBC 连接字符串这样做：

"jdbc:ch://HOST.clickhouse.cloud:8443/?user=default&password=PASSWORD&ssl=true&custom_http_params=async_insert=1,wait_for_async_insert=1"

​ 我们的强烈建议是在使用异步插入时使用 async_insert=1,wait_for_async_insert=1。使用 wait_for_async_insert=0 非常危险，因为您的 INSERT 客户端可能无法了解是否存在错误，并且如果您的客户端在 ClickHouse 服务器需要减缓写入速度并创建一些反压以确保服务的可靠性时继续快速写入，可能会导致潜在的过载。

使用异步插入时，自动去重默认情况下是禁用的。

​ 手动批量插入的优势在于，如果（恰好）对 ClickHouse 发送了相同的插入语句多次，例如，由于客户端软件因某些临时网络连接问题而进行的自动重试，则支持内置的自动去重。

Java客户端

​ Java 客户端是一个库，实施了自己的 API，抽象了与 ClickHouse 服务器的网络通信细节。目前仅支持 HTTP 接口。该库提供了处理不同 ClickHouse 格式和其他相关功能的工具。

​ Java 客户端早在 2015 年就已开发。其代码库变得非常难以维护，API 令人困惑，进一步优化也很困难。因此，我们在 2024 年对其进行了重构，分为新的组件 client-v2。它具有清晰的 API、更轻的代码库和更多的性能改进，更好的 ClickHouse 格式支持（主要是 RowBinary 和 Native）。JDBC 将在不久的将来使用此客户端。

官方提供的Java客户端文档

使用案例 

<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.8.3</version>
</dependency>
<dependency>
    <groupId>org.apache.httpcomponents.client5</groupId>
    <artifactId>httpclient5</artifactId>
    <version>5.4.3</version>
</dependency>

业务场景

​ 我在clickhouse中设计调查问卷的数据存储，现在有个题型是评分题，每个选项都要对应一个评分，我的表结构是下面这样的，
请写一个查询，查询某一道排序题的各个选项的平均分值、最大分值和最小分值，要求多维度分析，年份、公司、岗位作为分析维度；假设已经知道这道排序题的问题ID，
​ 如果一个用户所填写的调查问卷共计200个选项，整个集团每年都会收集12万份问卷，则共计2400万个选项数据，基于这样的数据规模进行统计，现有的效率如何，怎么优化？

CREATE TABLE survey_answer_data
(
    surveyId FixedString(36), -- 问卷ID
    year int8,      -- 年份
    submit_time DateTime,    -- 提交时间
    company FixedString(36),    -- 公司
	post FixedString(36),    -- 岗位
	questionId FixedString(36),    -- 问题id
	optionId FixedString(36),    -- 选项id
	score int8,    -- 选项分值
ENGINE = MergeTree()
PARTITION BY (year,surveyId)
ORDER BY (questionId,company,post, optionId);

INSERT INTO survey_answer_data (*) VALUES ("11a9a47f-9711-49db-9969-5119aab960f2",2024, '2024-01-01 12:00:00', "公司1","岗位1","91a9a47f-9711-49db-9969-5119aab960f1","91a9a47f-9711-49db-9969-5119aab960f2",3);

运维

安装部署

docker 拉取镜像

docker pull clickhouse:lts

docker run -d \
-p 18123:8123 -p19000:9000 \
-e CLICKHOUSE_PASSWORD=changeme \
--name clickhouse-server \
-v /opt/clickhouse/data:/var/lib/clickhouse/ \
-v /opt/clickhouse/logs:/var/log/clickhouse-server/ \
--ulimit nofile=262144:262144 \
clickhouse:lts

配置文件 /etc/clickhouse-server/config.xml

使用容器中的默认客户端进行连接

docker exec -it clickhouse-server clickhouse-client

ClickHouse 的默认连接信息如下：

默认端口号：

  • HTTP 接口端口：8123（通过 HTTP 请求访问，如浏览器或 curl）
  • TCP 原生协议端口：9000（客户端通过此端口直接连接，如 clickhouse-client）

默认用户名：default

  • 初始安装时无密码，直接使用 default 用户即可连接（不安全，生产环境建议修改密码）

桌面客户端工具

推荐开源工具DBeaver

用户管理

https://clickhouse.com/docs/zh/sql-reference/statements/create/user

角色管理

https://clickhouse.com/docs/zh/sql-reference/statements/create/role

README

参考：

​ clickhouse 基本用法

​ clickhouse 性能优化【官方】

数据库 > ClickHouse
#数据库 #ClickHouse #OLAP
OLAP数据库之ClickHouse
http://jackpot-lang.online/2025/01/08/数据库/OLAP数据库之ClickHouse/
作者
Jackpot
发布于
2025年1月8日
许可协议