如何在ClickHouse中通过物化视图检测未近期出现的ID数据

问题背景 #

我有三张ClickHouse表：

• seen_ids：存储已出现过的ID（当前约5000万行）；
• source_ids：采用Null Engine的实时数据流入表；
• target_ids：仅存储流入时未出现在seen_ids中的新ID。

当前使用的物化视图方案在大表+高流量场景下存在性能瓶颈：

CREATE materialized view mv_fresh
TO target_ids
AS SELECT
  *
FROM source_ids where id not in (select id from seen_ids)

ID会被添加至seen_ids，后续重复流入时不会存入target_ids。尝试过LEFT JOIN，但大表场景下性能依然不理想，需要更高效的方案来检测“未出现过的ID”。

推荐优化方案 #

1. 用EXISTS替代NOT IN优化查询逻辑 #

NOT IN会先拉取seen_ids的全量ID结果集再做比对，大表场景下内存开销极大。而EXISTS是行级匹配校验，找到匹配项后立即终止查询，性能提升明显：

CREATE materialized view mv_fresh
TO target_ids
AS SELECT
  s.*
FROM source_ids s
WHERE NOT EXISTS (SELECT 1 FROM seen_ids t WHERE t.id = s.id)

2. 给seen_ids的id列创建高效索引 #

针对大表的精确匹配查询，索引是核心优化点：

• 整数ID：将id设为Int64类型并设置为主键（ClickHouse主键是有序索引，点查询性能最优）：

  ALTER TABLE seen_ids MODIFY PRIMARY KEY (id);
  

• 字符串ID：创建Hash索引（适合精确匹配）或Bloom Filter索引（适合快速排除已存在的ID，有极低误判率）：

  -- Hash索引
  ALTER TABLE seen_ids ADD INDEX idx_id_hash id TYPE hash() GRANULARITY 1;
  -- Bloom Filter索引
  ALTER TABLE seen_ids ADD INDEX idx_id_bloom id TYPE bloom_filter(0.01) GRANULARITY 1;
  

索引创建后执行OPTIMIZE TABLE seen_ids FINAL让索引生效。

3. 分层存储实现冷热分离 #

将seen_ids拆分为热数据层和冷数据层，平衡内存与查询效率：

• 热数据层：用Memory引擎存储最近几小时/天的ID，用于快速过滤近期重复的ID；
• 冷数据层：用MergeTree引擎存储全量历史ID。

物化视图先查询热数据层，未命中再查冷数据层：

CREATE materialized view mv_fresh
TO target_ids
AS SELECT
  s.*
FROM source_ids s
WHERE NOT EXISTS (SELECT 1 FROM seen_ids_hot t WHERE t.id = s.id)
  AND NOT EXISTS (SELECT 1 FROM seen_ids_cold t WHERE t.id = s.id)

定时将热数据层的ID合并到冷数据层并清空热数据层，避免内存溢出。

4. 用ReplacingMergeTree自动去重减少存储压力 #

如果不需要保留seen_ids的全量历史，仅需记录ID是否出现过，可将seen_ids改为ReplacingMergeTree引擎，利用其自动去重特性：

-- 创建自动去重的seen_ids表
CREATE TABLE seen_ids
(
    id String,
    updated_at DateTime DEFAULT now()
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY id;

-- 写入target_ids的物化视图
CREATE MATERIALIZED VIEW mv_fresh
TO target_ids
AS SELECT
    s.*
FROM source_ids s
WHERE NOT EXISTS (SELECT 1 FROM seen_ids t WHERE t.id = s.id);

-- 同步写入seen_ids的物化视图（自动去重）
CREATE MATERIALIZED VIEW mv_sync_seen
TO seen_ids
AS SELECT
    id,
    now() as updated_at
FROM source_ids;

seen_ids会自动合并相同ID的记录，始终只保留一条，大幅减少存储和查询开销。

5. 利用内存字典实现O(1)查询性能 #

将seen_ids的ID加载到ClickHouse内存字典中，字典查询为常数时间复杂度，适合高流量场景：

-- 创建内存字典
CREATE DICTIONARY seen_ids_dict
(
    id String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
    host 'localhost'
    port 8123
    user 'default'
    password ''
    db 'default'
    table 'seen_ids'
))
LAYOUT(FLAT())
LIFETIME(MIN 0 MAX 3600); -- 每小时自动刷新字典

-- 物化视图中用字典判断ID是否存在
CREATE materialized view mv_fresh
TO target_ids
AS SELECT
    s.*
FROM source_ids s
WHERE NOT existsDic('seen_ids_dict', s.id)

若ID更新频繁，可调整LIFETIME的刷新间隔，或执行INVALIDATE DICTIONARY seen_ids_dict手动刷新。

内容的提问来源于stack exchange，提问作者Егор Лебедев