Clickhouse中Map或键值数组的数据跳索引方案咨询

背景 #

我正将PostgreSQL中的表迁移至ClickHouse，其中一列是包含自定义属性的jsonb列，这些属性因租户而异，目前PostgreSQL中存储了10万个不同的自定义属性键。

我对比了ClickHouse的两种半结构化存储方案：Map(String, String)或拆分的keys/values双Array(String)列，测试后两者基础性能相近，无法判断哪种更优。

测试环境 #

我创建了测试表并插入2亿条数据：

CREATE TABLE maptest
 (
     `k` Int64,
     `keys` Array(String),
     `values` Array(String),
     `map` Map(String, String)
  )
 ENGINE = MergeTree
 ORDER BY k
 SETTINGS index_granularity = 8192;

insert into maptest 
select 
    number, 
    mapKeys(map(concat('custom', toString(number%87000)), toString(number%87000))), 
    mapValues(map(concat('custom', toString(number%87000)), toString(number%87000))), 
    map(concat('custom', toString(number%87000)), toString(number%87000)) 
from numbers(200000000);

单条数据示例：

┌─k─┬─keys────────┬─values─┬─map─────────────┐
│ 0 │ ['custom0'] │ ['0']  │ {'custom0':'0'} │
└───┴─────────────┴────────┴─────────────────┘

查询性能测试 #

目前查询特定键值对都会触发全表扫描，三种查询方式的耗时如下：

1. 双Array+arrayFilter查询：

SELECT count()
FROM maptest
WHERE length(arrayFilter((v, k) -> ((k = 'custom2') AND (v = '2')), values, keys)) > 0

结果：2299，耗时10.541秒，处理2亿行，9.95GB

2. Map直接索引查询：

SELECT count()
FROM maptest
WHERE (map['custom2']) = '2'

结果：2299，耗时11.142秒，处理2亿行，8.35GB

3. 双Array+indexOf查询：

SELECT count()
FROM maptest
WHERE (values[indexOf(keys, 'custom2')]) = '2'

结果：2299，耗时3.458秒，处理2亿行，9.95GB

跳索引优化建议 #

针对Map和双Array两种存储方式，分别给出可行的跳索引方案：

一、针对Map(String, String)列的优化 #

• 使用bloom_filter跳索引
  为Map列创建针对键值对的布隆过滤器索引，ClickHouse支持直接对Map类型创建bloom_filter索引，能快速排除不包含目标键的索引粒度块：

  ALTER TABLE maptest ADD INDEX map_bloom map TYPE bloom_filter(0.01) GRANULARITY 8192;
  

  10万级别的键基数刚好匹配bloom_filter的适用场景，能有效减少扫描的数据块。

• 预提取高频键为单独列+索引
  如果存在部分高频访问的自定义键，可将这些键的值从Map中提取为单独列，搭配单列索引实现极致性能：

  ALTER TABLE maptest ADD COLUMN custom2 String DEFAULT map['custom2'];
  ALTER TABLE maptest ADD INDEX custom2_idx custom2 TYPE minmax GRANULARITY 8192;
  

二、针对keys/values双Array列的优化 #

• 为keys列创建bloom_filter跳索引
  查询核心是先定位包含目标键的行，对keys列创建布隆过滤器索引，能快速过滤掉不包含目标键的索引块，配合indexOf查询方式可大幅减少扫描行数：

  ALTER TABLE maptest ADD INDEX keys_bloom keys TYPE bloom_filter(0.01) GRANULARITY 8192;
  

• 使用tokenbf_v1跳索引
  如果keys数组中的字符串是类似customXXX的结构化格式，tokenbf_v1索引会对数组中每个元素做分词处理，能高效过滤不包含目标键的块：

  ALTER TABLE maptest ADD INDEX keys_token keys TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 8192;
  

三、通用优化方案 #

• 调整索引粒度
  当前index_granularity=8192，如果单条数据的Map/Array字段较大，可适当调小粒度（如4096），让跳索引更精准地过滤数据块。

• 用Set类型替代Array存储keys
  如果keys数组无重复值，将keys列改为Set(String)类型，ClickHouse对Set的contains查询性能更优，配合bloom索引能进一步提升过滤效率：

  ALTER TABLE maptest MODIFY COLUMN keys Set(String);
  

  查询可改为：

  SELECT count()
  FROM maptest
  WHERE has(keys, 'custom2') AND (values[indexOf(keys, 'custom2')] = '2')
  

内容的提问来源于stack exchange，提问作者George