clickhouse索引设计

一级索引设计

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

● 根据业务场景合理设计稀疏索引粒度 ClickHouse的主键索引采用的是稀疏索引存储，稀疏索引的默认采样粒度是8192 行，即每8192行取一条记录在索引文件中，之前的文章中我也给出稀疏索引值得大小设计，参考clickHouse本地表设计-CSDN博客

二级跳数索引设计

创建索引样例：

CREATE TABLE TEST ON CLUSTER default_cluster ( ss String, lll String, codeing String, time Date, INDEX a ss TYPE minmax GRANULARITY 5, INDEX b (length(ss) * 8) TYPE set(10) GRANULARITY 5, INDEX c (ss, codeing) TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 5, INDEX d ss TYPE tokenbf_v1(256, 2, 0) GRANULARITY 5, INDEX e ss TYPE bloom_filter(0.025) GRANULARITY 5 ) ENGINE = MergeTree() ORDER BY ss ;

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

索引定义

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

– expr ：属性表达式，基于字段或者字段的表达式来创建索引；

– type（...）：支持的索引类型，minmax、set等；

– Granularity：创建索引的记录粒度。比如index_granularity = 8192， granularity配置为3，则使用8192*3条记录创建一条索引数据。

索引详解：

– minmax索引

记录了一段数据范围内的最小和最大极值，其索引的作用类似分区目录的 minmax索引，能够快速跳过无用的数据区间。 INDEX a ss TYPE minmax GRANULARITY 5 上述示例中minmax索引会记录这段数据区间内ss字段的极值。极值的计算涉 及每5个index_granularity区间中的数据。

– set索引

直接记录了声明字段或表达式的取值（唯一值，无重复），其完整形式为set （max_rows），其中max_rows是一个阈值，表示在一个index_granularity 内，索引最多记录的数据行数。如果max_rows=0，则表示无限制。 INDEX b (length(ss) * 8) TYPE set(10) GRANULARITY 5 上述示例中set索引会记录数据中ss的长度*8后的取值。其中，每个 index_granularity内最多记录10条。

– 布隆过滤器

▪ 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 ss TYPE tokenbf_v1(256,2,0) GRANULARITY 5

建表后再创建索引

ALTER TABLE my_table add INDEX minMax_index (time) TYPE minmax GRANULARITY 3;

● 删除索引

ALTER TABLE my_table DROP INDEX minMax_index ;