能否用Bloom Filter对ClickHouse表执行COUNT(*)？查询慢求助

问题根源 #

1. tokenbf_v1索引的局限性：它仅能跳过完全不含目标token的数据颗粒，但如果目标token在大量颗粒中存在，过滤效果会大打折扣，仍需扫描大部分数据。
2. 颗粒粒度设置错误：你将GRANULARITY设为1，意味着每个颗粒仅存储1行数据。布隆过滤器按颗粒生效，过小的颗粒会导致过滤器存储开销暴增，不仅无法高效过滤，反而拖慢查询。
3. 主键排序不匹配查询场景：表的ORDER BY字段为created_at，未关联event字段，即便过滤了部分颗粒，剩余颗粒仍需全量扫描来统计行数。

优化方案 #

1. 修正布隆过滤器的颗粒粒度 #

将GRANULARITY调整为默认的8192（也可根据数据量选4096/16384），减少过滤器数量，提升过滤效率：

ALTER TABLE logs MODIFY INDEX event_index TYPE tokenbf_v1(9815, 7, 0) GRANULARITY 8192;

修改后必须重新物化索引：

ALTER TABLE logs MATERIALIZE INDEX event_index;

2. 用预聚合物化视图替代实时COUNT #

若业务允许，创建按token预统计数量的物化视图，直接从视图取数避免全表扫描：

CREATE MATERIALIZED VIEW logs_event_cnt
ENGINE = SummingMergeTree
PARTITION BY category
ORDER BY (category, token)
AS
SELECT
    category,
    token,
    COUNT(*) AS cnt
FROM logs
-- 按空格拆分event字段为token，可根据实际分隔符调整
ARRAY JOIN splitByWhitespace(event) AS token
GROUP BY category, token;

查询时直接从视图获取结果：

SELECT cnt FROM logs_event_cnt WHERE token = 'Proin';
-- 需按分类过滤时添加条件
SELECT cnt FROM logs_event_cnt WHERE category = '目标分类' AND token = 'Proin';

3. 调整表的主键排序（可选，需重建表） #

若频繁按event的token查询，可将event的哈希值加入ORDER BY，让相同token的数据尽量落在同一颗粒，提升过滤后的扫描效率：

-- 注意：修改ORDER BY需重建表，建议先备份数据
ALTER TABLE logs MODIFY ORDER BY (category, created_at, cityHash64(event));

4. 优化布隆过滤器参数 #

当前设置的size为9815比特（约1.2KB），对于大表可能过小，导致误判率高。可调大size并匹配哈希函数数量，降低误判：

ALTER TABLE logs MODIFY INDEX event_index TYPE tokenbf_v1(16384, 8, 0) GRANULARITY 8192;

参数说明：

• 16384：每个过滤器的比特数（2KB）
• 8：哈希函数数量
• 0：随机种子

5. 强制使用索引（若未自动触发） #

先查看执行计划，确认索引是否被使用：

EXPLAIN SELECT COUNT(*) FROM logs WHERE hasToken(event, 'Proin');

若计划未显示event_index，强制开启索引：

SELECT COUNT(*) FROM logs WHERE hasToken(event, 'Proin') SETTINGS force_data_skipping_indices='event_index';

验证优化效果 #

用EXPLAIN ANALYZE查看优化后的执行细节，对比扫描行数和耗时：

EXPLAIN ANALYZE SELECT COUNT(*) FROM logs WHERE hasToken(event, 'Proin');

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