clickhouse group by添加索引 clickhouse创建索引

从问题出发

二级索引的含义是什么?
什么情况下会走二级索引？
如何知道SQL走没走索引？

二级索引相关的技术原理

MergeTree二级索引又叫跳数索引，是由数据按索引粒度分割后的每部分在指定表达式上的汇总信息组成，这些汇总信息有助于用where条件过滤时跳过不满足的数据，从而减少select查询从磁盘读取的数据量以及数据扫描的范围。
二级索引生成规则按照index_granularity粒度间隔将数据划分为n段，总共有[0, n-1]个区间(n = total_rows / index_granularity，向上取整)。根据索引定义声明，从0区间开始，一次按照index_granularity粒度从数据中获取聚合信息，每次向前移动1步(n + 1)，聚合信息逐步累加。这些区间每隔granularity生成一行跳数索引数据。

举个例子说明：
以minmax索引为例，它聚合了一个index_granularity区间内的最大和最小数据，假设index_granularity=8192且granularity=3，则数据按照index_granularity划分为n等份，MergeTree从第0段分区开始，依次获取聚合信息，当获取到第三个分区(granularity=3)，则汇总并生成第一行minmax索引(前3段minmax汇总后取值为[1, 9])，如下图

二级索引支持的类型

目前包括如下类型的索引：

minmax：minmax索引记录了一段数据内的最小值和最大值，用于快速跳过无用的数据区间。

INDEX a ID TYPE minmax GRANULARITY 5 

表示minmax索引会记录每5个index_granularity区间数据中的最大值和最小值。
set：  存储指定字段或表达式的唯一值，完整形式为 set(max_rows) ，max_rows表示在一个index_granularity内，索引最多纪录的数据行数，如果max_rows=0，则表示无限制。

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

 表示该索引会记录数据中ID长度*8之后的取值，并且每个index_granularity最多纪录100条

ngrambf_v1： ngrambf_v1 记录了数据块中n元短语的布隆表过滤器(简单来讲，布隆表过滤器本质是由仅包含0和1位值的列表组成，默认均为0，利用哈希函数对数据值进行处理，并将结果位置上对应位的值改为1，由于存在哈希冲突，所以只能判断不在列表中和可能在列表中)，只支持String和FixedString数据类型，可用于优化like、in、equals、notIn、notEquals的查询性能，完整形式为 ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed) ，各参数含义为：

1. n: token长度，依据n长度将数据切割为token短语
2. size_of_bloom_filter_in_bytes: 布隆过滤器的大小
3. number_of_hash_functions: 布隆过滤器中使用Hash函数的个数
4. random_seed: Hash函数的随机种子

INDEX c(ID, Code) TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 5 

表示依照3的粒度将数据切割成短语token，token经过两个Hash函数映射后再被写入，布隆过滤器大小伟256字节。

tokenbf_v1：tokenbf_v1和ngrambf_v1类似，但它会自动按照非字符、数字的字符串切割token。形式如：tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)，参数含义：

1. size_of_bloom_filter_in_bytes: 布隆过滤器的大小
2. number_of_hash_functions: 布隆过滤器中使用Hash函数的个数
3. random_seed: Hash函数的随机种子

bloom_filter：bloom_filter([false_positive]) 存储指定列的布隆过滤器。
可选 false_positive 参数是从过滤器接收到误报响应的概率。可能的值：(0, 1)。默认值：0.025。

以上的bloom_filter、ngrambf_v1、tokenbf_v1是通过把完整的字符串列或者字符串列分词后的token用bloom_filter生成高压缩比的签名来进行排除Block，在长字符串的场景下有一定加速空间。

另外，Clickhouse最新版本也发布了，invert倒排索引的实验性特性供大家使用，具体参考如下PR： Inverted Indices Implementation by larryluogit · Pull Request #38667 · ClickHouse/ClickHouse · GitHub 大家可以尝先使用。可以使用二级索引的场景，下面显示各类操作符支持的情况表：

Function (operator) / Index	primary key	minmax	ngrambf_v1	tokenbf_v1	bloom_filter
equals (=, ==)	✔️	✔️	✔️	✔️	✔️
notEquals(!=, <>)	✔️	✔️	✔️	✔️	✔️
less (<)	✔️	✔️	❌	❌	❌
greater (>)	✔️	✔️	❌	❌	❌
lessOrEquals (<=)	✔️	✔️	❌	❌	❌
greaterOrEquals (>=)	✔️	✔️	❌	❌	❌
like	✔️	✔️	✔️	✔️	❌
notLike	✔️	✔️	✔️	✔️	❌
startsWith	✔️	✔️	✔️	✔️	❌
endsWith	❌	❌	✔️	✔️	❌
multiSearchAny	❌	❌	✔️	❌	❌
in	✔️	✔️	✔️	✔️	✔️
notIn	✔️	✔️	✔️	✔️	✔️
empty	✔️	✔️	❌	❌	❌
notEmpty	✔️	✔️	❌	❌	❌
hasToken	❌	❌	❌	✔️	❌

and 和 or 支持的情况表

索引列	A and B	B and A	A or B	B or A	A and B or B
A	✔️	✔️	❌	❌	❌
B	✔️	✔️	❌	❌	✔️
(A,B)	✔️	✔️	✔️	✔️	✔️

这里需要注意下，当使用组合索引的时候(A,B), 单独使用组合索引里面某一列的时候也能走索引。例如(A = 1 也是可以走组合索引的(A,B))。
or运算比较特殊，通过测试可以推断出or运算的左右表达式中的列必须是索引列才能够走索引。

测试SQL如下:

-- A字段索引,B字段索引,(A、B)字段组合索引
CREATE TABLE index_test
(
    key Int32,
    i_a_column Int32,
    i_b_column Int32,
    INDEX idx_a (i_a_column) TYPE minmax GRANULARITY 1,
    INDEX idx_b (i_b_column) TYPE minmax GRANULARITY 1,
    INDEX idx_a_b (i_a_column, i_b_column) TYPE minmax GRANULARITY 1
)
ENGINE = MergeTree()
ORDER BY key
settings index_granularity = 1;
insert into index_test values(1, 1, 1);
insert into index_test values(2, 2, 2);
insert into index_test values(3, 3, 3);
select * from index_test where i_a_column = 1 and i_b_column = 1;
select * from index_test where i_b_column = 1 and i_a_column = 1;
select * from index_test where i_a_column = 1 or i_b_column = 1;
select * from index_test where i_b_column = 1 or i_a_column = 1;
select * from index_test where i_a_column = 1 and i_b_column = 1 or i_b_column = 1;

如何知道SQL走没走索引

目前Clickhouse有两种方式确认是否走索引执行任务：

第一通过Explain的方式看到走没走索引的信息：

EXPLAIN indexes = 1 select * from index_test where i_a_column = 1;

第二通过日志查看。比如下面的例子，比如当我们执行下面的查询时

select * from index_test where i_a_column = 1;

clickhouse再做insert语句的时候，底层会落成1个part，因为我们index_granularity=1,所以会生成3个GRANULARITY, 那么上面的查询语句只需要i_a_column = 1的数据，那么如果走索引的情况下应该只读1个GRANULARITY才对。2和3的数据应该被丢弃掉。通过设置debug日志(set send_logs_level='debug'),是不是这样我们看下日志。

通过这个日志，我们可以知道，查询SQL使用了idx_a索引， 并且使用idx_a索引丢掉了多少个2/3 granules，2是丢掉的GRANULARITY的个数，3的是总的GRANULARITY数量。 如果不走索引的情况下，是没有这样的打印的。 可以通过日志进行分析，是否走了索引。