在前一篇文章《ClickHouse MergeTree表引擎和建表语句》中，我们详细介绍了MergeTree的建表语句、存储结构和索引原理，本篇我们继续介绍MergeTree的另一个特性——二级索引，二级索引适用于所有MergeTree家族表引擎。在某些版本中，默认可能没有开启二级索引，可以通过下面的设置开启二级索引。


SET allow_experimental_data_skipping_indices=1;


在继续介绍之前我们先来回顾一下MergeTree的查询过程：

  1. 当我们通过主键进行查询时，会先通过primary.idx文件获取到对应的稀疏索引值，可能会有多个满足条件的稀疏索引值。例如查找 id = 1000的数据，primary.idx中刚好只有一个稀疏索引标记是 1000，那么就要把这个标记前后两个索引粒度的数据都解压出来扫描，因为满足条件的数据可能会有多个。假设这两个索引粒度区间分别是：10*8192~11*8192 - 1 和 11*8192~12*8192 - 1，简称为10和11号索引粒度。
  2. 根据mrk文件找到要查的索引粒度在各个bin文件中的偏移量。mrk第二列记录的就是每个稀疏索引行在bin文件压缩块中的偏移量，10和11号索引粒度可能在同一个压缩块中，也可能在多个压缩块中。
  3. 把第2步中查询到的压缩块全部从bin文件中取出解压，然后从第2步得到的偏移量处开始扫描判断，返回所有满足条件的数据。

大多数情况下我们都是按照主键查询的，但是如果我们查询一个非主键字段呢？或者通过一个虚拟列查询呢？例如表中有列a和b，经常需要根据 where a * b == value 来查询。此时稀疏索引就失效了，就需要全表扫描，查询效率无疑大大降低，ClickHouse MergeTree针对这种情况也给出了优化方案，就是二级索引，又叫跳数索引(Data Skipping Indexes)。

1. 创建跳数索引

跳数索引可在建表的时候指定，创建语句如下：

INDEX index_name expr TYPE type(...) GRANULARITY granularity_value

expr可以是元组，表示创建多个同类的跳数索引。一旦创建了跳数索引，在分区文件中就会出现skpi_idx_[Column].idx 和 skip_idx_[Column].mrk 文件。例如创建名称分别为a和b的minmax、set跳数索引：

CREATE TABLE table_name
(
    u64 UInt64,
    i32 Int32,
    s String,
    ...
    INDEX a (u64 * i32, s) TYPE minmax GRANULARITY 3,
    INDEX b (u64 * length(s)) TYPE set(1000) GRANULARITY 4
) ENGINE = MergeTree()
...

2. 跳数索引类型

针对不同的场景，ClickHouse提供了不同类型的跳数索引。每种跳数索引都有一个 GRANULARITY 参数，表示每隔 GRANULARITY 个索引粒度 (index_granularity ) 才会生成一次跳数索引。

※ 注意区分 GRANULARITY 和 index_granularity 的不同。

（1）minmax

minmax顾名思义就是和分区目录下的 minmax_{column_name}.idx 文件类似，只不过不再是只有一个min/max值，例如上面的minmax跳数索引 a，表示每隔 3 * index_granularity 的区间就会记录一次 u64 * i32 和 s 的最大最小值。当我们通过s查询数据时，可以先基于minmax值判断，从而跳过大多数不需要扫描的索引粒度。

（2）set(max_rows)

保存指定表达式的去重值，尤其是对于那些重复性很高的列，例如性别、年龄段等，max_rows 参数表示在一个索引粒度内，最多记录不超过 max_rows 行，即不多于 max_rows 个去重值，max_rows=0 表示不限制。

（3）ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

存储一个包含数据块中所有 n元短语（ngram） 的布隆过滤器（Bloom filter）。对String, FixedString 和 Map类型数据有效，可用于优化 EQUALS, LIKE 和 IN表达式。

ngram表示一句话中的连续n个单词，或者一个字符串中的n个连续字符。感兴趣的同学可阅读《Word2Vector详解》。

什么是布隆过滤器呢？

本质上布隆过滤器是一种数据结构，一种比较巧妙的概率型数据结构（probabilistic data structure），特点是高效地插入和查询，可以用来告诉你：某个数据一定不存在或者可能存在（注意：布隆过滤器是不能判断某条数据一定存在的，存在误报率 (false_positive) ）。相比于传统的 List、Set、Map 等数据结构，布隆过滤器更高效、占用空间更少，因为不需要存储原始值，但是缺点是其返回的结果是概率性的，而不是确切的。

布隆过滤器会定义一个初始值全为0，长度为m的 bit 向量或者说 bit 数组：

clickhouse创建唯一索引 clickhouse 建索引_大数据

然后再定义k个hash函数，使得原始数据可以映射到bit向量中（是不是和redis的bitmap很像，还记得那个怎么记录上亿用户在线状态的问题吗？）。例如，定义3个hash函数，

对于“python”得到3个hash值：1、5、6，就将bit向量中的1、5、6 bit位置1。

对于“java”得到3个hash值：2、3、9，就将bit向量中的2、3、9 bit位置1。

对于“rust”得到3个hash值：7、8、9，就将bit向量中的7、8、9 bit位置1。

可以发现，对于不同的数据，因为bit向量的长度是有限的，所以可能会出现重复的bit位被置1的情况，即不同数据之间有交集。当我们查数据的时候，例如：“go”，得到的三个hash值为：4、6、7，因为4号bit位是0，所以“go”一定不存在。如果查询数据“shell”得到的hash值是：2、5、9，虽然这个三个bit位都有数据，但是我们也不能确定“shell”一定存在。

布隆过滤器的好处是可以用较少的空间存储多条复杂数据的存在信息，但是不容易确定bit向量的长度m的值，因为m越小，bit向量就越容易被占满，越容易误报（false_positive越大），起不到过滤作用。hash函数的个数k也不容易确定，因为k越大，bit向量也越容易被占满，k越小，越容易误报，因为更容易撞车。而且，可以发现布隆过滤器是不支持删除的，因为被置1的bit位可能存储了多条数据信息，对于经常变动的数据是不合适的。

再来介绍一下ngrambf_v1的参数：

  • n：ngram短语长度。
  • size_of_bloom_filter_in_bytes：布隆过滤器的大小 m，以字节为单位(可以使用较大的值，例如256或512，因为它可以很好地被压缩，并且有更强的表达能力)。
  • number_of_hash_functions：布隆过滤器中使用的哈希函数的个数 k 。
  • random_seed：布隆过滤器哈希函数的种子。

（4）tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

跟 ngrambf_v1 类似，但是存储的是token而不是ngrams。Token是由非字母数字的符号分割的序列。不再需要ngram size参数，自动通过原始数据中的非字母数字字符切分原始数据。

（5）bloom_filter([false_positive])

为指定的列存储布隆过滤器，可用于优化 equals, notEquals, in, notIn, has, hasAny, hasAll 函数，false_positive 表示从过滤器接收到假阳性响应的概率（误报率），取值范围是 (0,1)，默认值：0.025。支持数据类型：Int*, UInt*, Float*, Enum, Date, DateTime, String, FixedString, Array, LowCardinality, Nullable, UUID, Map.

对于Map类型数据，可以使用mapKeys或mapValues函数指定是否为键或值创建索引。如：

INDEX map_key_index mapKeys(map_column) TYPE bloom_filter GRANULARITY 1
INDEX map_key_index mapValues(map_column) TYPE bloom_filter GRANULARITY 1

假设已知布隆过滤器误报率 p，插入元素个数为n，则hash函数个数 k 和布隆过滤器bit向量长度 m 为：

clickhouse创建唯一索引 clickhouse 建索引_布隆过滤器_02

3. 跳数索引函数支持

在使用where条件查询的时候，如果where条件表达式中包含跳数索引列，ClickHouse会在执行函数时尝试使用索引。不同的函数对索引的支持是不同的，其中 set 索引对所有函数都生效，其他跳数索引支持如下：

clickhouse创建唯一索引 clickhouse 建索引_布隆过滤器_03

※ 注意：参数小于ngram大小的常量函数不能被ngrambf_v1用于查询优化。例如，ngrambf_v1的参数n为5，即5元短语，则 where like 'ab%' 不能被优化。

因为布隆过滤器存在误报率，所以 ngrambf_v1, tokenbf_v1, 和 bloom_filter 不能用于优化结果逻辑为False的查询。例如，下面查询语句可以优化：

s LIKE '%test%'
NOT s NOT LIKE '%test%'
s = 1
NOT s != 1
startsWith(s, 'test')

下面的语句不能被优化：

NOT s LIKE '%test%'
s NOT LIKE '%test%'
NOT s = 1
s != 1
NOT startsWith(s, 'test')

因为布隆过滤器可以明确地判断该条语句是不存在，但是却不能明确地判断一定是存在的，所以对于返回存在like后数据的块还需要全部解压扫描。

所有跳数索引的原则都是“排除法”，即尽可能的排除那些一定不满足条件的索引粒度。在写查询where条件时也要基于这一原则考虑。