云数据库ClickHouse：ClickHouse二级索引

背景信息

ClickHouse官方开源版目前没有二级索引的功能设计，以下二级索引相关功能是云数据库ClickHouse的增强功能，并且只适配于20.3和20.8的内核版本。这里的二级索引和官方开源版的索引并不是一个原理，解决的也不是同一类问题，二级索引最常见的使用场景是对根据非排序键的等值条件进行点查加速。

二级索引语法

二级索引在创建表时的定义语句示例如下：

CREATE TABLE index_test
(  
id UInt64,  
d DateTime,  
x UInt64,  
y UInt64,  
tag String,  
KEY tag_idx tag TYPE range,  
KEY d_idx toStartOfHour(d) TYPE range,  
KEY combo_idx (toStartOfHour(d), x, y) TYPE range
) ENGINE = MergeTree() ORDER BY id;

二级索引相关的修改DDL如下：

--删除索引定义
Alter table index_test DROP KEY tag_idx;
--增加索引定义
Alter table index_test ADD KEY tag_idx tag TYPE range;
--清除数据分区下的索引文件
Alter table index_test CLEAR KEY tag_idx in partition partition_expr;
--重新构建数据分区下的索引文件
Alter table index_test MATERIALIZE KEY tag_idx in partition partition_expr;

二级索引特性

ClickHouse的二级索引支持多索引列条件交并差检索。总体特点概括如下：

• 多列联合索引 & 表达式索引
• 函数下推
• In Set Clause下推
• 高压缩比 （索引文件大小接近Lucene 8.7）
• 向量化构建 （构建速度对比Lucene 8.7提升4倍）

多列联合索引的目的是减少特定查询pattern下的索引结果归并，针对QPS要求特别高的查询用户可以创建针对性的多列联合索引达到极致的检索性能。而表达式索引主要是方便用户进行自由的检索粒度变换，考虑以下两个典型场景：

• 二级索引中的时间列在搜索条件中，只会以小时粒度进行过滤，这种情况下用户可以对toStartOfHour(time)表达式创建索引，可以一定程度加速索引构建，同时对time列的时间过滤条件都可以自动转换下推索引。
• 二级索引中的ID列是由UUID构成，UUID几乎是可以保证永久distinct的字符串序列，直接对ID构建索引会导致索引文件太大。这时用户可以使用前缀函数截取UUID来构建索引，如prefix8(id)是截取8个byte的前缀，对应的还有prefix4和prefix16。prefixUTF4、prefixUTF8、prefixUTF16则是用来截取UTF编码的。

用户对表达式构建索引后，原列上的查询条件也可以正常下推索引，不需要特意改写查询。同样用户对原列构建索引，过滤条件上对原列加了表达式的情况下，优化器也都可以正常下推索引。

In Set Clause下推则是一个关联搜索的典型场景，经常有用户碰到此类场景：user的属性是一张单独的大宽表，user的行为记录又是另一张单独的表，对user的搜索需要先从user行为记录表中聚合过滤出满足条件的user id，再用user ids从属性表中取出明细记录。这种in subquery的场景下，ClickHouse也可以自动下推二级索引进行加速。

二级索引构建性能

以下介绍的是ClickHouse二级索引构建性能和索引压缩率的测试结果，主要对比的是Lucene 8.7的倒排索引和BKD索引。索引构建时间都是基于单线程作业统计的。

场景一：UUID字符串

CREATE TABLE string_index_test (
 `C_KEY` String,  
 KEY C_KEY_IDX C_KEY Type range
) ...
INSERT INTO string_index_test 
select substringUTF8(cast (generateUUIDv4() as String), 1, 16) 
from system.numbers limit 100000000;

测试结果如下：

测试对象	索引构建耗时	索引文件大小	数据文件大小
ClickHouse	97.2s	1.3G	1.5G
Lucene	487.255s	1.3G	N/A

场景二：枚举字符串

CREATE TABLE string_index_test (
 `C_KEY` LowCardinality(String),  
 KEY C_KEY_IDX C_KEY Type range
) ...
INSERT INTO string_index_test 
select cast((10000000 + rand(number) % 4000) as String) 
from system.numbers limit 100000000;

测试对象	索引构建耗时	索引文件大小	数据文件大小
ClickHouse	25.5s	187M	193M
Lucene	45.513s	187M	N/A

场景三：随机数值

CREATE TABLE long_index_test (
  `C_KEY` UInt64,  
  KEY C_KEY_IDX C_KEY Type range
) ...
INSERT INTO long_index_test 
select rand(number) % 100000000 
from system.numbers limit 100000000;

测试对象	索引构建耗时	索引文件大小	数据文件大小
ClickHouse	34.2s	615M	519M
Lucene	81.971s	482M	N/A

场景四：枚举数值

CREATE TABLE int_index_test (
  `C_KEY` UInt32,  
  KEY C_KEY_IDX C_KEY Type range
) ...
INSERT INTO int_index_test 
select rand(number) % 1000 
from system.numbers limit 100000000;

测试对象	索引构建耗时	索引文件大小	数据文件大小
ClickHouse	12.2s	163M	275M
Lucene	77.999s	184M	N/A

二级索引等值查询性能

测试环境：32core 128G，内存 ecs，1T PL1 ESSD

测试数据：总数据行数13E，表结构如下：

CREATE TABLE point_search_test (
 `PRI_KEY` String,  
 `SED_KEY` String,  
 `INT_0` UInt32, 
 `INT_1` UInt32, 
 `INT_2` UInt32, 
 `INT_3` UInt32, 
 `INT_4` UInt32, 
 `LONG_0` UInt64, 
 `LONG_1` UInt64, 
 `LONG_2` UInt64, 
 `LONG_3` UInt64, 
 `LONG_4` UInt64, 
 `STR_0` String, 
 `STR_1` String, 
 `STR_2` String, 
 `STR_3` String, 
 `STR_4` String, 
 `FIXSTR_0` FixedString(16), 
 `FIXSTR_1` FixedString(16), 
 `FIXSTR_2` FixedString(16), 
 `FIXSTR_3` FixedString(16), 
 `FIXSTR_4` FixedString(16), 
 KEY SED_KEY_IDX SED_KEY Type range
) ...

二级索引等值查询QPS如下：

二级索引极致性能推荐配置

对二级索引性能有极致要求的用户推荐购买32core 128G内存的ECS规格实例，同时挂载PL2级别的ESSD硬盘。

实例全局参数设置：min_compress_block_size = 4096, max_compress_block_size = 8192。

MergeTree表级别的参数设置：调整min_bytes_for_compact_part参数，优先使用Compact Format。