简介

  • 支持主键索引、数据分区、数据副本、数据采样、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 排序