文档首页/ MapReduce服务 MRS/ 组件开发规范/ ClickHouse应用开发规范/ ClickHouse宽表设计/ ClickHouse索引设计
更新时间：2024-12-11 GMT+08:00
查看PDF
分享

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个以内。

父主题： ClickHouse宽表设计