# 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 - 索引创建详见官方文档 - 建表后再创建索引 **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个以内。