ClickHouse_ClickHouse二级索引与实战

发表于 | 分类于

ClickHouse_ClickHouse二级索引与实战

1.背景

ClickHouse作为数据产品业务数据库，其中存储的明细数据表，通常要给多个看板或者多个不同维度组合提供数据，那么不可能实现每次查询都能命中主键索引粒度。例如交易用户款表，可能有从商品类目粒度的全行业交易数据查询，也有店铺粒度的交易数据查询。那么我们往往需要比较请求比例，对请求最多的维度路径设置主键索引，那么其他维度路径的查询就无法命中索引。

如果有一些维度路径的请求没有命中索引，而且参与计算的数据量非常大，那么这种请求一定很慢，那么一般需要通过预计算来解决。如果有一些维度路径的请求本身参与计算的数据量不大，但是由于没有命中索引导致请求sql扫描数据量极大，那么这种请求也很可能成为性能瓶颈。而且这类扫描量大计算量小的请求往往在单个请求时无性能问题，但是在压测或者大促等集群性能本身较差的情况下，这类请求会变得非常慢。

那么对于这种扫描量大计算量小的请求场景一般可以通过另建一张表、物化视图、projection等牺牲存储的方式解决，目的就是重建主键索引避免扫描量过大，但是这类方法往往需要存储double，使用之前可以先试试ClickHouse二级索引能不能解决这个问题。

2.MergeTree引擎的二级索引特性

2.1 二级索引表达式

ClickHouse的二级索引又叫跳数索引，可在建表的时候指定，也可在已有表上添加，创建语句如下：

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

expr可以是运算表达式，也可以是元组，表示对多个字段创建多个同类的跳数索引。GRANULARITY参数表示每隔多少个主键稀疏索引数据块 (index_granularity ) 生成一次跳数索引。一旦创建了跳数索引，在分区文件中就会出现skpi_idx_[Column].idx和skip_idx_[Column].mrk文件。如下sql为创建名称分别为a和b的minmax、set跳数索引：

1
2
3
4
5
6
7
8
9
CREATE TABLE table_name
(
    x UInt64,
    y Int32,
    z String,
    ...
    INDEX a (x * y, z) TYPE minmax GRANULARITY 3,
    INDEX b (x * length(z)) TYPE set(1000) GRANULARITY 4
) ENGINE = MergeTree()

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

2.2 二级索引类型

针对不同的场景，ClickHouse提供了不同类型的跳数索引

minmax

minmax的作用就是记录最大值和最小值。它与分区目录下的分区字段的minmax_{column_name}.idx文件作用类似，不过minmax二级索引不再是只能记录一个字段数据的min/max值，例如上面的minmax跳数索引a，表示每隔3 * index_granularity的区间就会记录一次x * y和s的最大最小值。当我们通过x * y表达式作为过滤条件查询数据时，就可以先基于minmax二级索引判断，从而跳过大多数不需要扫描的索引粒度。

set(max_rows)

保存指定表达式的去重值。主要用于低基维度字段，例如性别、年龄段等，max_rows参数表示在一个索引粒度内，最多记录不超过max_rows行，即不多于max_rows个去重值，max_rows=0表示不限制。

bloom_filter([false_positive])

为指定的列存储布隆过滤器。可用于优化 equals, notEquals, in, notIn, has, hasAny, hasAll 函数，false_positive 表示从过滤器接收到假阳性响应的概率（误报率），取值范围是 (0,1)，默认值：0.025。支持数据类型如下：

1
Int*, UInt*, Float*, Enum, Date, DateTime, String, FixedString, Array, LowCardinality, Nullable, UUID, Map

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

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

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

ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

可用于优化 EQUALS、LIKE和IN表达式。原理比较复杂，使用场景较特殊，不再赘述。

tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

原理比较复杂，使用场景较特殊，不再赘述。

3.布隆过滤器二级索引使用实战

3.1 使用背景

之前博文中提到的行业模块ClickHouse宽表，使用商品二级类目id、三级泪目id、skuId作为主键，在大部分场景下查询性能没问题。但是对于店铺榜单场景，很多日常单词请求性能达标(1s左右)，但是压测时请求并发高起来性能就非常差(10s+)，分析发现这种sql的主要特点就是由于没有命中主键索引扫描读取的数据行数特别大，但是店铺粒度数据计算量并不是很大。

3.2 二级索引实战

1
2
3
4
5
6
7
8
9
10
11
--删除二级索引
ALTER TABLE sz.app_jdr_traffic_sz_lead_ord_cart_mvp_i_d_d on cluster LF02_CK_Pub_202 DROP INDEX ind_itm_id;

--查看二级索引是否添加成功
show create table sz.app_jdr_traffic_sz_lead_ord_cart_mvp_i_d_d;

--每10跳添加一个布隆过滤器二级索引，分别比较了1、5、10跳，其中每5跳一个布隆过滤器二级索引效率最高。
ALTER TABLE sz.app_jdr_traffic_sz_lead_ord_cart_mvp_i_d_d on cluster LF02_CK_Pub_202 ADD INDEX ind_itm_id itm_id TYPE bloom_filter GRANULARITY 10;

--初始化二级索引，使其生效
ALTER TABLE sz.app_jdr_traffic_sz_lead_ord_cart_mvp_i_d_d on cluster LF02_CK_Pub_202 MATERIALIZE INDEX ind_itm_id;

此处采用布隆过滤器类型的二级索引，这也是最常用的二级索引类型，该种过滤器的参数GRANULARITY n表示每n个主键稀疏索引创建一个布隆过滤器。一般可以根据二级索引维度与主键索引维度的关系估算出该参数的最佳值，但是估算比较麻烦，不如直接用二分法试出最佳参数值。

其实如果主键索引与二级索引是级联维度，且主键维度小于二级索引维度，优化效果是非常好的，比如主键最细到了skuid粒度，而二级索引是shopid粒度，那么查询shopid粒度数据时，大部分数据基本就分布在某几个主键稀疏索引中。

参考文献

数据开发之ClickHouse(1)_MergeTree&ReplicatedMergeTree&Distributed原理解析

ClickHouse MergeTree二级索引/跳数索引