TL;DR

• 场景：线上表在 MergeTree 下读放大严重、查询“扫全列”、TTL 不生效。

• 结论：用 分区裁剪 + 稀疏主键索引 + mark 粒度 组合，配合压缩与 OPTIMIZE，可把同等查询的 I/O 降到 10–30%。

• 产出：一套可复制 DDL/查询与诊断 附 marks/压缩比/TTL 验证方法。

版本矩阵

MergeTree

续接上节部分

数据存储

ClickHouse 是一个 列式存储 数据库，这意味着每一列的数据是单独存储的，而不是像行式数据库那样将每一行作为一个整体来存储。列式存储的优势在于，它可以针对特定的查询只读取相关的列，大大减少了 I/O 操作，尤其在进行聚合或过滤操作时表现出色。每一列的数据通常会被划分成若干块（block），这些块被组织在存储引擎的元数据和数据文件中。ClickHouse 的存储引擎有多个，常用的包括 MergeTree 引擎及其变种。

表由按主键排序的数据 片段组成。当数据被插入到表中时，会分成数据片段并按主键的字典序排序。

例如：主键是（CounterID, Date）时，片段中的数据按 CounterID 排序，具有相同的 CounterID 的部分按 Date 排序。不同的分区的数据会被划分成不同的片段，ClickHouse 在后台合并数据片段以便高效的存储，不会合并来自不同分区的数据片段，这个合并机制并不保证相同的主键的所有行都会合并到同一个数据片段中。

ClickHouse 会为每个数据片段创建一个索引文件，索引文件包括每个索引行的主键值，索引行号定义为 n * index_granularity。最大的 N 等于总行数除以 index_granularity 的值的整数部分。对于每列，跟主键相同的索引行也会写入标记，这些标记可以让你直接找到数据所在的列。你可以只用单一达标不断的一块块往里加入数据，MergeTree 引擎就是为了这样的场景。

按列存储

在 MergeTree 中数据按列存储，具体到每个字段列，都拥有一个 bin 数据文件，最终存储数据的文件。按列存储的好处：

• 更好的压缩

• 最小化数据扫描范围

MergeTree 往 bin 里存数据的步骤：

• 对数据进行压缩

• 根据 ORDER BY 排序

• 数据以压缩块的形式写入 bin 文件

压缩数据块

CompressionMethod_CompressedSize_UnccompressedSize 一个压缩数据块由两部分组成：

• 头信息

• 压缩数据

头信息固定使用 9 位字节表示，1 个 UInt8（1 字节） + 2 个 UInt32(4 字节)，分别表示压缩算法，压缩后的数据大小，压缩前的数据大小。如：0x821200065536

• 0x82 是压缩方法

• 12000 是压缩后数据大小

• 65536 是压缩前数据大小

可以使用如下的语句，来查看压缩的情况

# 这里路径可能不一样 根据你的来cd /var/lib/clickhouse/data/default/mt_table/202407_1_1_0# 查看clickhouse-compressor --stat data.bin out.log

运行截图如下所示：

如果按照默认 8192 的索引粒度把数据分成批次，每批次读入数据的规则：

• 设 x 为批次数据的大小

• 如果单批次数据 x < 64k，则继续读下一个批次，找到 size > 64k 则生成下一个数据块

• 如果单批次数据 64k < x < 1M 则直接生成下一个数据块

• 如果 x > 1M，则按照 1M 切分数据，剩下的数据继续按照上述规则执行。

数据标记

在 ClickHouse 中，mark 是索引的一部分，用于标记数据文件中数据块的开始位置。标记可以看作是一个辅助数据结构，帮助快速定位需要查询的数据块。在进行查询时，ClickHouse 不会读取整个数据文件，而是根据标记来跳过无关的数据块。

标记通常包含以下信息：

• 块开始的位置

• 块中每列的最小值和最大值

• 其他元数据信息

标记的粒度（即每多少行生成一个标记）可以通过配置 index_granularity 来控制。标记粒度越小，标记文件占用的空间越大，但查询性能也会越好，因为可以更精确地定位到具体的数据块。

.mrk 文件：将索引 primary.idx 和数据文件.bin 建立映射关系。通用 hits_v1 表说明：

• 数据标记和索引区间是对齐的，根据索引区间的下标编号，就能找到数据标记-索引编号和数据标记数值相同

• 每一个[Column].bin 都有一个[Column].mrk 与之对应 .mrk 文件记录数据在 bin 文件中的偏移量

数据标记和区间是对齐的，均按照 index_grangularity 粒度间隔，可以通过索引区间的下标编号找到对应的数据标记。每一个列字段的 bin 文件都有一个 mrk 数据标记文件，用于记录数据在 bin 文件的偏移量信息。标记数据采用 LRU 缓存策略加快其取用速度

分区、索引、标记和压缩协同

分区（Partition）

ClickHouse 分区机制详解

分区概念

ClickHouse 的分区机制是一种将大型表数据分割成独立逻辑段的存储策略。每个分区相当于表的一个独立子集，包含特定范围的数据。这些分区在物理存储上是相互独立的，可以单独进行读写操作，同时又能被统一管理。

分区键定义

分区可以基于一个或多个列来定义：

• 单列分区：最常见的是按日期字段分区，例如 PARTITION BY toYYYYMM(date_column)
  

• 多列分区：可以组合多个字段，如 PARTITION BY (toYYYYMM(date_column), city)
  

• 表达式分区：支持使用函数表达式，例如 PARTITION BY sipHash64(user_id) % 4
  

主要优势

1. 查询效率提升

• 分区裁剪：WHERE 子句中包含分区键时，查询引擎会自动跳过不相关的分区

• 减少 IO：只需读取目标分区的数据文件，大幅减少磁盘扫描量

• 并行处理：不同分区可以并行处理，提高查询吞吐量

示例场景：

-- 按月分区的表SELECT count() FROM logs WHERE event_date BETWEEN '2023-10-01' AND '2023-10-31'-- 只会扫描2023年10月的分区

2. 数据管理便捷

• 生命周期管理：可设置分区 TTL 自动过期数据

• 灵活操作：支持单独对分区执行：

• ALTER TABLE DROP PARTITION 删除分区

• ALTER TABLE DETACH PARTITION 归档数据

• ALTER TABLE MOVE PARTITION 迁移分区

• 维护优化：可针对单个分区执行 OPTIMIZE 操作

3. 时间序列处理

典型的分区策略示例：

• 按天分区：PARTITION BY toDate(timestamp)
  

• 按月分区：PARTITION BY toYYYYMM(timestamp)
  

• 按小时分区：PARTITION BY toStartOfHour(timestamp)
  

适用于：

• IoT 设备数据

• 应用日志分析

• 金融交易记录

• 网络监控数据

实现建议

1. 分区粒度选择：

2. 单个分区建议保持在 1GB 以上

3. 避免创建过多小分区(超过 10,000 个)

4. 常用分区策略：

5. 日志数据：按日分区

6. 用户行为数据：按月分区+用户 ID 哈希

7. 监控指标：按小时分区+设备 ID

8. 监控维护：

   -- 查看分区信息   SELECT partition, rows, disk_size    FROM system.parts    WHERE table = 'your_table'

通过合理设计分区策略，可以显著提升 ClickHouse 在大数据场景下的查询性能和管理效率。

索引（Index）

ClickHouse 的索引与传统数据库中的索引有所不同。其主要依赖于主键索引和稀疏索引来加速数据查询。

• 主键索引：在 MergeTree 表引擎中，主键用于决定数据的排序方式，并辅助数据查询。ClickHouse 的主键索引是一种稀疏索引，不会为每一行都创建索引项，而是为数据块创建索引项。

• 稀疏索引（Sparse Index）：这种索引仅针对某些行进行标记，而非为每一行建立索引，从而减少了存储开销。稀疏索引与标记配合使用，使得查询时可以快速跳过无关的数据块。

此外，ClickHouse 还支持 Skip Indexes（跳过索引），用于优化复杂查询时跳过不相关的数据。这些索引类型包括 minmax、set、bloom_filter 等，适用于不同的查询场景。

标记（Marks）

标记（Marks）是 ClickHouse 中的一个重要概念，它是稀疏索引的实现基础。在每列数据的存储文件中，ClickHouse 会定期在某些行记录一个标记，记录下该行在文件中的位置。查询时，ClickHouse 会利用这些标记跳过不需要的块，从而加速查询过程。

压缩协同（Compression）

ClickHouse 的列式存储结构非常适合数据压缩。它通过对每一列的数据进行独立压缩，从而大幅减少存储空间。ClickHouse 提供了多种压缩算法，包括：

• LZ4（默认）：一种快速、轻量级的压缩算法，适合需要快速解压的数据。

• ZSTD：一种高压缩率的算法，适合用于磁盘存储空间有限但允许较长查询时间的场景。

• Delta、DoubleDelta：这些算法专为时间序列数据设计，利用相邻数值之间的差异来实现高效压缩。

写入过程

• 生成分区目录

• 合并分区目录

• 生成 primary.idx 索引文件，每一列的 bin 和 mrk 文件

查询过程

• 根据分区缩小查询范围

• 根据数据标记、缩小查询范围

• 解压数据块

MergeTree 的 TTL

TTL：time to live 数据存活时间，TTL 既可以设置在表上，也可以设置在列上，TTL 指定的时间到期后删除相应的表或者列，如果同时设置了 TTL，则根据先过期时间删除相应数据。

用法：TTL time_col + INTERVAL 3 DAY 表示数据存活时间是 time_col 时间的 3 天后 INTERVAL 可以设定的时间：

• SECOND

• MINUTE

• HOUR

• DAY

• WEEK

• MONTH

• QUARTER

• YEAR

TTL 设置列

新建表格：

CREATE TABLE ttl_table_v1 (  id String,  create_time DateTime,  code String TTL create_time + INTERVAL 10 SECOND,  type UInt8 TTL create_time + INTERVAL 10 SECOND) ENGINE = MergeTreePARTITION BY toYYYYMM(create_time)ORDER BY id;

执行结果如下图所示：

插入数据

INSERT INTO ttl_table_v1 VALUES('A0000', now(), 'c1', 1), ('A0000', now() + INTERVAL 10 MINUTE, 'c1', 1);

执行结果如下图所示：

查询结果：

SELECT * FROM ttl_table_v1;

执行结果如下图所示：

手动触发数据的合并和压缩：

OPTIMIZE TABLE ttl_table_v1 FINAL;SELECT * FROM ttl_table_v1;

看起来好像没有什么区别...执行结果如下图：

TTL 设置表

CREATE TABLE tt1_table_v2 (  id String,  create_time DateTime,  code String TTL create_time + INTERVAL 10 SECOND,  type UInt8) ENGINE = MergeTreePARTITION BY toYYYYMM(create_time)ORDER BY create_timeTTL create_time + INTERVAL 1 DAY;

使用 ALTER 编辑表：

ALTER TABLE tt1_table_v1 MODIFY TTL create_time + INTERVAL + 3 DAY;

运行截图如下所示：

运行结果如下所示：

可复现简易脚本

-- 1) 建表：月分区 + 典型主键CREATE TABLE mt_demo (  counter_id UInt32,  dt DateTime,  city LowCardinality(String),  v Float64)ENGINE = MergeTreePARTITION BY toYYYYMM(dt)ORDER BY (counter_id, dt)SETTINGS index_granularity = 8192;
-- 2) 造数：1000 万行（可按机器缩小）INSERT INTO mt_demoSELECT  randUniform(1, 50000) AS counter_id,  now() - randUniform(0, 86400*90) AS dt,  concat('c', toString(randUniform(1, 5000))) AS city,  randCanonical() AS vFROM numbers(10000000);
-- 3) 典型查询：裁剪 + 跳读SELECT avg(v)FROM mt_demoWHERE counter_id = 42 AND dt BETWEEN now()-86400 AND now();
-- 4) 观察系统表：分区/part/marks/压缩SELECT partition, name, rows, bytes_on_diskFROM system.parts WHERE table = 'mt_demo' AND active;
SELECT partition, name, column, marks, data_compressed_bytesFROM system.parts_columns WHERE table = 'mt_demo' AND activeORDER BY partition, name, column;

分区策略与维护

-- 查看分区/行数/空间SELECT partition, rows, disk_size FROM system.parts WHERE table='mt_demo' AND active;
-- 单分区优化OPTIMIZE TABLE mt_demo PARTITION 202510 FINAL;
-- 移动/删除/分离ALTER TABLE mt_demo DROP PARTITION 202409;ALTER TABLE mt_demo DETACH PARTITION 202409;

错误速查

其他系列

🚀 AI 篇持续更新中（长期更新）

AI 炼丹日志-29 - 字节跳动 DeerFlow 深度研究框斜体样式架 私有部署 测试上手 架构研究，持续打造实用 AI 工具指南！AI-调查研究-108-具身智能 机器人模型训练全流程详解：从预训练到强化学习与人类反馈🔗 AI模块直达链接

💻 Java 篇持续更新中（长期更新）

Java-154 深入浅出 MongoDB 用 Java 访问 MongoDB 数据库 从环境搭建到 CRUD 完整示例 MyBatis 已完结，Spring 已完结，Nginx 已完结，Tomcat 已完结，分布式服务正在更新！深入浅出助你打牢基础！🔗 Java模块直达链接

📊 大数据板块已完成多项干货更新（300 篇）：

包括 Hadoop、Hive、Kafka、Flink、ClickHouse、Elasticsearch 等二十余项核心组件，覆盖离线+实时数仓全栈！大数据-278 Spark MLib - 基础介绍 机器学习算法 梯度提升树 GBDT 案例 详解🔗 大数据模块直达链接