ClickHouse索引设计

一级索引设计

• 在建表设计时指定主键字段的建议：按查询时最常使用且过滤性最高的字段作为主键。依次按照访问频度从高到低、维度基数从小到大来排列。数据是按照主键排序存储的，查询的时候，通过主键可以快速筛选数据，合理的主键设计，能够大大减少读取的数据量，提升查询性能。例如所有的分析，都需要指定业务的id，则可以将业务id字段作为主键的第一个字段顺序。
• 根据业务场景合理设计稀疏索引粒度

  ClickHouse的主键索引采用的是稀疏索引存储，稀疏索引的默认采样粒度是8192行，即每8192行取一条记录在索引文件中，实践建议：

  • 索引粒度越小，对于小范围的查询更有效，避免查询资源的浪费；
  • 索引粒度越大，则索引文件越小，索引文件的处理会更快；
  • 超过10亿的表索引粒度可设为16384，其他设为8192或者更小值。

二级跳数索引设计

跳数索引使用参考：

• 使用说明

  对于*MergeTree引擎，支持配置跳数索引，即一种数据局部聚合的粗糙索引，对数据块创建索引，选择性的保留一部分原始数据（minmax、set）， 或者是保留计算后的中间数据（bloomfilter）。在查询时，选择忽略加载不会包含结果的数据块，从而达到加速查询的效果。

• 索引定义

  INDEX index_name expr TYPE type（...） GRANULARITY granularity_value

  • Expr：属性表达式，基于字段或者字段的表达式来创建索引；
  • type（...）：支持的索引类型，minmax、set等；
  • Granularity：创建索引的记录粒度。比如index_granularity = 8192，granularity配置为3，则使用8192*3条记录创建一条索引数据。
• 创建索引样例

  CREATE TABLE skip_index_test ON CLUSTER default_cluster
  (
  ID     String,
  URL    String,
  Code   String,
  EventTime Date,
  INDEX a ID TYPE minmax GRANULARITY 5,
  INDEX b (length(ID) * 8) TYPE set(100) GRANULARITY 5,
  INDEX c (ID, Code) TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 5,
  INDEX d ID TYPE tokenbf_v1(256, 2, 0) GRANULARITY 5,
  INDEX e ID TYPE bloom_filter(0.025) GRANULARITY 5
  ) ENGINE = MergeTree()
  ORDER BY ID ;

  • minmax索引

    记录了一段数据范围内的最小和最大极值，其索引的作用类似分区目录的minmax索引，能够快速跳过无用的数据区间。

    INDEX a ID TYPE minmax GRANULARITY 5

    上述示例中minmax索引会记录这段数据区间内ID字段的极值。极值的计算涉及每5个index_granularity区间中的数据。

  • set索引

    直接记录了声明字段或表达式的取值（唯一值，无重复），其完整形式为set（max_rows），其中max_rows是一个阈值，表示在一个index_granularity内，索引最多记录的数据行数。如果max_rows=0，则表示无限制。

    INDEX b (length(ID) * 8) TYPE set(100) GRANULARITY 5

    上述示例中set索引会记录数据中ID的长度*8后的取值。其中，每个index_granularity内最多记录100条。

  • 布隆过滤器
    • bloom_filter索引

      为指定的列存储布隆过滤器。

      可选的参数false_positive用来指定从布隆过滤器收到错误响应的几率。取值范围是 (0,1)，默认值：0.025。

      支持的数据类型：Int*，UInt*，Float*，Enum，Date，DateTime，String，FixedString，Array，LowCardinality，Nullable。

    • ngrambf_v1索引

      记录的是数据短语的布隆表过滤器，只支持String和FixedString数据类型。只能够提升in、notIn、like、equals和notEquals查询的性能，其完整形式为：

      ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

      这些参数是一个布隆过滤器的标准输入，如果接触过布隆过滤器，应该会对此十分熟悉。

      具体的含义如下：

      • n：token长度，依据n的长度将数据切割为token短语。
      • size_of_bloom_filter_in_bytes：布隆过滤器的大小。
      • number_of_hash_functions：布隆过滤器中使用Hash函数的个数。
      • random_seed：Hash函数的随机种子。
    • tokenbf_v1索引

      是ngrambf_v1的变种，同样也是一种布隆过滤器索引。tokenbf_v1除了短语token的处理方法外，其他与ngrambf_v1是完全一样的。tokenbf_v1会自动按照非字符的、数字的字符串分割token。

      INDEX d ID TYPE tokenbf_v1(256,2,0) GRANULARITY 5

• 索引创建详见官方文档

  https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetree-data_skipping-indexes

• 建表后再创建索引

  ALTER TABLE table_name add INDEX min_max_index (etl_time) TYPE minmax GRANULARITY 3;

• 删除索引

  ALTER TABLE table_name DROP INDEX min_max_index;

• 单表跳数索引数量

  由于索引的创建对数据导入性能有影响，建议单表跳数索引的总数量控制在5个以内。