简介

  • 支持主键索引、数据分区、数据副本、数据采样、ALTER 操作
  • 扩展表引擎丰富，生产环境中大多使用该表引擎
  • 数据以片段形式写入磁盘，后台定期合并片段到各分区相应片段

数据表

  • 建表语句

    1
2
3
4
5
6
7
8

    CREATE TABLE [IF NOT EXISTS] [db_name.]table_name(
    ...
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...];

  • PARTITION BY: 分区键，选填，支持单字段、多字段和表达式，默认生成一个 all 分区

  • ORDER BY: 排序键，必填，支持单列和元组(包含多列)

  • PRIMARY KEY: 主键，选填，默认与排序键相同，允许重复数据

  • SAMPLE BY: 抽样，选填，该配置需在主键中同时声明

  • SETTINGS: 其他参数，选填，示例如下

    • index_granularity: 索引粒度，默认 8192，通常不需要修改
    • index_granularity_bytes: 每批次写入的数据大小，用于自适应索引间隔，默认 10MB，0 表示无视数据大小
    • enable_mixed_granularity_parts: 自适应索引间隔，默认开启
    • merge_with_ttl_timeout: TTL 合并间隔时间，默认 86400(1天)
    • storage_policy: 数据在硬盘上的存储策略

数据文件

  • 目录和文件 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

    table_name # 表名目录
|___ partition_1 # 分区目录
     |___ checksums.txt # 校验文件，二进制，记录该分区目录中其他文件的大小和哈希值
     |___ columns.txt # 列信息文件，明文，记录该分区下的列字段信息
     |___ count.txt # 计数文件，明文，记录该分区总行数
     |___ primary.txt # 一级索引文件，二进制，存放稀疏索引
     |___ {column_name}.bin # 列数据文件，默认 LZ4 压缩
     |___ {column_name}.mrk # 列标记文件，二进制，记录对应数据文件(.bin)中的数据偏移量
     |___ {column_name}.mrk2 # 如果表使用了自适应索引间隔，那么对应的列字段标记文件以 .mrk2 命令
     |___ partition.dat # 保存当前分区表达式的值，二进制
     |___ minmax_{column_name}.idx # 保存当前分区字段对应原始数据的最小和最大值，二进制
     |___ skp_idx_{column_name}.idx # 二级索引(跳数索引)文件
     |___ skp_idx_{column_name}.mrk # 二级索引(跳数索引)列的标记文件

数据分区

分区 ID

  • 单字段分区 ID 生成规则
类型 样例数据 分区表达式 分区 ID
无分区键 - 无 all
整型 18,19,20 PARTITION BY Age 分区1: 18，分区2: 19，分区3: 20
整型 ‘A0’, ‘A1’, ‘A2’ PARTITION BY length(Code) 分区1: 2
日期 2020-10-05, 2020-10-06 PARTITION BY EventTime 分区1: 20201005，分区2: 20201006
日期 2020-09-25, 2020-10-06 PARTITION BY toYYYYMM(EventTime) 分区1: 202009，分区2: 202010
其他 ‘www.colben.cn’ PARTITION BY URL 分区1: {128 位 Hash 算法}
  • 多字段(元组)分区时， 先按单字段生成对应 ID，再用 “-” 拼接

分区目录

  • 分区目录命名: PartitionID_MinBlockNum_MaxBlockNum_Level，例如 202010_1_1_0

    • PartitionID: 分区 ID
    • MinBlockNum: 最小数据块编号，表内全局累加，从 1 开始
    • MaxBlockNum: 最大数据块编号，表内全局累加，从 1 开始
    • Level: 分区合并次数，从 0 开始

  • 不同批次写入的数据，即使分区相同，也会存储在不同目录中

  • 后台在默认 10-15 分钟后自动合并分区相同的多个目录，也可以手动执行 optimize 语句

  • 合并成功后，旧分区目录被置为非激活状态，在默认 8 分钟后被后台删除

  • 合并后新目录的命名规则:

    • MinBlockNum: 所有合并目录中的最小 MinBlockNum
    • MaxBlockNum: 所有合并目录中的最大 MaxBlockNum
    • Level: 所有合并目录中的最大 Level 值并加 1

数据索引

  • 常驻内存

  • 一级索引是稀疏索引，间隔 index_granularity (默认 8192) 行数据生成一条索引记录

  • 二级索引又称跳数索引，有数据的聚合信息构建而成，在 CREATE 语句中定义如下:

    1
2

    INDEX index_name expr TYPE index_type(...) GRANULARITY granularity
-- GRANULARITY 指定一行跳数索引聚合的数据段(index_granularity 区间)的个数

  • 跳数索引类型

    • minmax: 记录一段数据内的最小值和最大值

      1

      INDEX index_name ID TYPE minmax GRANULARITY 5

    • set: 记录字段或表达式的无重复取值

      1
2

      INDEX index_name (length(ID)) TYPE set(100) GRANULARITY 5
-- 每个数据段(index_granularity 区间)内最多记录 100 条 set 索引记录

    • ngrambf_v1: 只支持 String 和 FixedString，只能提升 in、notIn、like、equals 和 notEquals 性能

      1
2
3
4
5

      INDEX index_name (ID, Code) TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 5;
-- 3: token 长度，把数据切割成长度为 3 的短语
-- 256: 布隆过滤器大小
-- 2: 哈希函数个数
-- 0: 哈希函数随机种子

    • tokenbf_v1: ngrambf_v1 变种，按照非 字母和数字 自动分割

      1
2

      INDEX index_name ID TYPE tokenbf_v1(256, 2, 0) GRANULARITY 5;
-- 注意传参时不需要指定 token 长度

数据存储

  • 按列独立存储
  • 默认 LZ4 压缩
  • 按照 order by 排序
  • 以数据压缩块形式写入 .bin 文件，规则如下:
    • 单批次数据 < 64KB，继续获取下一批数据
    • 64KB <= 单批次数据 <= 1MB，直接生成压缩数据块
    • 单批次数据 > 1MB，按照 1MB 大小截断并生成数据块，剩余数据继续按前面规则执行

数据标记

  • 使用 LRU 策略缓存
  • 每一行标记数据记录的是一个数据片段在 .bin 文件中的读取位置

数据写入

  • 生成分区目录，合并分区相同的目录
  • 按照 index_granularity 索引粒度，生成一级索引、二级索引、数据标记文件和数据压缩文件

数据查询

  • 借助分区、索引、数据标记来缩小扫描范围
  • 如果未指定查询条件，或条件未匹配到索引，MergeTree 仍可借助数据标记多线程读取压缩数据块

数据 TTL

TTL 机制

  • TTL 信息保存在分区目录中的 ttl.txt 中

  • 支持的时间单位: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

  • 触发 TTL 删除过期数据

    • 后台分区合并
    • merge_with_ttl_timeout 合并频率，默认 86400 秒
    • 手动执行 OPTIMIZE 语句

  • 合并分区时，TTL 全部到期的数据分区不会参与合并

  • 控制全局 TTL 合并任务

    1
2
3
4

    -- 启动
SYSTEM START TTL MERGES;
-- 停止
SYSTEM STOP TTL MERGES;

列级别 TTL

  • 到达时间时，列数据被还原为对应数据类型的默认值

  • 主键字段不能被声明 TTL

  • 声明列级别 TTL

    1
2
3
4
5
6
7
8

    CREATE TABLE table_name(
    id String,
    create_time DateTime,
    code String TTL create_time + INTERVAL 10 SECOND,
    type UInt8 TTL create_time + INTERVAL 16 SECOND
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY id;

  • 修改列级别 TTL

    1

    ALTER TABLE table_name MODIFY COLUMN code String TTL create_time + INTERVAL 1 DAY;

表级别 TTL

  • 到达时间时，删除过期的数据行

  • 声明表级别 TTL

    1
2
3
4
5
6
7
8
9

    CREATE TABLE table_name(
    id String,
    create_time DateTime,
    code String TTL create_time _ INTERVAL 1 MINUTE,
    type UInt8
) ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY create_time
TTL create_time + INTERVAL 1 DAY;

  • 修改表级别 TTL

    1

    ALTER TABLE table_name MODIFY TTL create_time + INTERVAL 3 DAY;

存储策略

  • 最小移动单元是数据分区
  • 三大策略: 默认、JBOD、HOT/COLD

默认策略

  • 无需配置，所有分区自动保存至 config.xml 中的 path 目录下

JOB 策略

  • 适用于多磁盘无 RAID 场景
  • INSERT 或 MERGE 产生的新分区轮询写入各磁盘，类似 RAID0
  • 磁盘故障时，丢掉相应数据，需要副本机制保障数据可靠性

HOT/COLD 策略

  • 适用于已挂载不同类型磁盘的场景
  • 把磁盘划分到 HOT 和 COLD 两个区域，HOT 使用 SSD，注重性能，CODE 使用 HDD，注重经济
  • 单个区域内可应用 JBOD 策略

配置策略

  • 配置示例

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

    <storage_configuration>
  <disks>
    <!-- 磁盘名称，全局唯一 -->
    <disk_hot_0>
      <!-- 存储目录，注意 clickhouse 用户有权限读写该目录 -->
      <path>/ch/ssd0</path>
      <!-- 磁盘预留空间，选填 -->
      <keep_free_space_bytes>1073741824</keep_free_space_bytes>
    </disk_hot_0>
    <disk_hot_1>
      <path>/ch/ssd1<path>
    </disk_hot_1>
    <disk_cold_0>
      <path>/ch/hdd0<path>
      <keep_free_space_bytes>2147483648</keep_free_space_bytes>
    </disk_cold_0>
    <disk_cold_1>
      <path>/ch/hdd1<path>
    </disk_cold_1>
    <disk_cold_2>
      <path>/ch/hdd2<path>
    </disk_cold_2>
  </disks>
  <policies>
    <!-- 策略名称，全局唯一 -->
    <policy_jbod_0>
      <volumes>
        <!-- 卷名称，全局唯一 -->
        <volume_jbod_0>
          <!-- 指定该卷内使用的磁盘 -->
          <disk>disk_hot_0</disk>
          <disk>disk_hot_1</disk>
          <!-- 单个 disk 中一个分区的最大存储阈值，选填 -->
          <!-- 超出阈值后，该分区的其他数据会写入下一个该卷内下一个 disk -->
          <max_data_part_size_bytes>1073741824</max_data_part_size_bytes>
        </volume_jbod_0>
      </volumes>
    </policy_jbod_0>
    <policy_hot_cold_0>
      <volumes>
        <volume_hot_0>
          <disk>disk_hot_0</disk>
          <disk>disk_hot_1</disk>
        </volume_hot_0>
        <volume_cold_0>
          <disk>disk_cold_0</disk>
          <disk>disk_cold_1</disk>
          <disk>disk_cold_2</disk>
        </volume_cold_0>
      </volumes>
      <!-- 卷可用空间因子，默认 0.1，选填 -->
      <!-- 如果当前卷可用空间小于 20%，则数据会自动写入下一个卷 -->
      <move_factor>0.2</move_factor>
    </policy_hot_cold_0>
  </policies>
</storage_configuration>

  • clickhouse 用户需要有权限读写各存储目录

  • 存储配置不支持动态更新

  • 存储磁盘系统表: system.disks

  • 存储策略系统表: system.storage_policies

  • 移动分区到其他 disk

    1

    ALTER TABLE table_name MOVE PART 'part_name' TO DISK 'disk_name';

  • 移动分区到其他 volume

    1

    ALTER TABLE table_name MOVE PART 'part_name' TO VOLUME 'volume_name';

ReplacingMergeTree

  • 依据 ORDER BY 字段去重

  • 合并分区时，以分区为单位删除重复数据

  • 声明

    1

    ENGINE = ReplacingMergeTree(version_column)

  • version_column 选填，指定一个 UInt*、Date 或 DateTime 字段作为版本号

  • 未指定 version_column 时，保留同一组重复数据中的最后一行

  • 指定 version_column 时，保留同一组重复数据中该字段取值最大的一行

SummingMergeTree

  • 场景: 用户只需要汇总结果，不关心明细

  • 依据 ORDER BY 字段聚合

  • 合并分区时，触发条件聚合，以分区为单位把同一分组下的多行数据汇总成一行

  • 声明:

    1

    ENGINE = SummingMergeTree((col1,col2, ...))

  • col1、col2 选填，不可指定主键，指定被 SUM 汇总的数值类型字段

  • 未指定任何汇总字段时，默认汇总所有非主键的数值类型字段

  • 非汇总字段保留同组内的第一行数据

  • 汇总嵌套字段时，字段名需以 Map 为后缀，默认嵌套字段中第一列作为聚合 Key，其他以 *Key、*Id、*Type 未后缀名的列会和第一列组成复合 Key

AggregatingMergeTree

  • 预先计算聚合数据，二进制格式存入表中，空间换时间，可看成是 SummingMergeTree 的升级版
  • 依据 ORDER BY 字段聚合
  • 使用 AggregationFunction 字段类型定义聚合函数和字段
  • 分区合并时，触发以分区为单位的合并计算
  • 非汇总字段保留同组内的第一行数据
  • 写数据时调用 *State 函数，查询时调用 *Merge 函数
  • 一般用作物化视图的表引擎，与普通 MergeTree 搭配使用，示例如下

    • 创建明细数据表，俗称底表

      1
2
3
4
5
6
7
8

      CREATE TABLE table_name(
    id String,
    city String,
    code String,
    value Uint32
) ENGINE = MergeTree()
PARTITION BY city
ORDER BY (id, city);

    • 创建物化视图

       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

      CREATE MATERIALIZED VIEW view_name
ENGINE = AggregatingMergeTree()
PARTITION BY city
ORDER BY (id, city)
AS SELECT
    id,
    city,
    uniqState(code) AS code,
    sumState(value) AS value
FROM table_name
GROUP BY id, city;

    • 使用常规 SQL 面向底表增加数据

    • 面向物化视图查询

      1

      SELECT id, sumMerge(value), uniqMerge(code) FROM agg_view GROUP BY id,city;

CollapsingMergeTree

  • 以增代删

  • 声明

    1

    ENGINE = CollapsingMergeTree(sign)

  • 定义 sign 标记字段，Int8 类型，1 代表有效，-1 代表无效

  • 依据 ORDER BY 字段作为数据唯一性依据

  • 规则

    • 如果 sign=1 比 sign=-1 多一行，则保留最后一行 sign=1 的数据
    • 如果 sign=-1 比 sign=1 多一行，则保留第一行 sign=-1 的数据
    • 如果 sign=-1 和 sign=1 一样多，且最后一行是 sign=1，则保留第一行 sign=-1 和最后一行 sign=1 的数据
    • 如果 sign=-1 和 sign=1 一样多，且最后一行是 sign=-1，则不保留任何数据
    • 其他情况打印告警日志

  • 合并分区时，触发以分区为单位的数据折叠

  • 严格要求数据写入顺序，只有先写入 sign=1，再写入 sign=-1，才能正常折叠

VersionedCollapsingMergeTree

  • 与 CollapsingMergeTree 类似，但对数据写入顺序没有要求

  • 声明

    1

    ENGINE = VersionedCollapsingMergeTree(sign, ver)

  • ver 是 UInt8 类型的版本号字段

  • 每个分区内的数据都以 ORDER BY column_name, ver DESC 排序