ClickHouse多数组列检索：按索引匹配多条件过滤数据

针对你的多标签组合查询需求，结合高插入量、无timestamp过滤的场景，提供以下几种适配方案：

方案1：基于原数组结构的直接查询（无需修改表） #

利用ClickHouse的数组函数组合，避免ARRAY JOIN带来的数据膨胀，直接匹配多组(key, value)条件：

SELECT
    timestamp,
    message,
    source_type,
    labels_key,
    labels_value
FROM db.logs
WHERE
    hasAll(
        zip(labels_key, labels_value),
        [('color', 'red'), ('make', 'audi'), ('year', '2021')]
    )
ORDER BY timestamp DESC, message
LIMIT 100;

优势： #

• 无需修改现有表结构和插入逻辑，快速适配多条件查询
• 避免ARRAY JOIN展开数组导致的数据行数翻倍，查询性能更优
• 扩展条件只需在[]中添加新的(key, value)元组即可

注意： #

• 要求labels_key中的元素唯一（否则同一key对应多个value时，hasAll会匹配到任意一个符合的value）

方案2：重构表为Map类型（推荐长期使用） #

将labels_key和labels_value合并为Map(String, String)类型，简化查询逻辑并提升性能：

1. 创建新表 #

CREATE TABLE db.logs_map
(
    `timestamp` DateTime CODEC(Delta(4), ZSTD(1)),
    `message` String,    
    `source_type` LowCardinality(String),
    `labels` Map(String, String) CODEC(ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY timestamp
PRIMARY KEY (timestamp)
ORDER BY (timestamp)
SETTINGS index_granularity = 8192;

2. 插入数据（适配Map类型） #

INSERT into db.logs_map(message, source_type, labels) 
VALUES 
('red car', 'government', map(['color', 'make', 'year', 'type', 'interior'], ['red', 'audi', '2021', 'sedan', 'black'])),
('red car', 'private', map(['color', 'make', 'year', 'type', 'interior'], ['red', 'toyota', '2020', 'crossover', 'black']));

3. 多条件查询 #

SELECT *
FROM db.logs_map
WHERE 
    labels['color'] = 'red' 
    AND labels['make'] = 'audi' 
    AND labels['year'] = '2021'
ORDER BY timestamp DESC, message
LIMIT 100;

优势： #

• 查询语句更直观，可读性强
• Map类型存储更紧凑，相比两个数组占用更少空间
• 后续新增标签无需修改表结构，扩展性更好

注意： #

• Map的键必须唯一，如果原数据中labels_key存在重复值，转换时会保留最后一个对应的value

方案3：物化视图适配（不影响原表插入） #

如果无法停止原表的插入或修改插入逻辑，可以创建物化视图将原数组结构同步为Map类型：

1. 创建物化视图目标表（同方案2的logs_map表） #

2. 创建物化视图 #

CREATE MATERIALIZED VIEW db.logs_map_mv TO db.logs_map
AS
SELECT
    timestamp,
    message,
    source_type,
    map(labels_key, labels_value) AS labels
FROM db.logs;

3. 查询时直接使用物化视图 #

同方案2的查询语句即可。

优势： #

• 原表的插入逻辑完全不受影响，物化视图自动同步数据
• 享受Map类型的查询性能优势

性能优化建议 #

由于无法使用timestamp过滤，查询可能触发全表扫描，可通过以下方式优化：

• 添加二级索引：针对常用查询的标签键添加Bloom Filter索引（以Map表为例）：

  ALTER TABLE db.logs_map ADD INDEX idx_color labels['color'] TYPE bloom_filter(0.01) GRANULARITY 8192;
  ALTER TABLE db.logs_map ADD INDEX idx_make labels['make'] TYPE bloom_filter(0.01) GRANULARITY 8192;
  

• 调整主键/排序键：如果source_type基数较低，可将其加入主键或排序键，减少扫描范围：

  CREATE TABLE db.logs_map
  (
      `timestamp` DateTime CODEC(Delta(4), ZSTD(1)),
      `message` String,    
      `source_type` LowCardinality(String),
      `labels` Map(String, String) CODEC(ZSTD(1))
  )
  ENGINE = MergeTree
  PARTITION BY timestamp
  PRIMARY KEY (source_type, timestamp)
  ORDER BY (source_type, timestamp)
  SETTINGS index_granularity = 8192;
  

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