ClickHouse跳过索引：bloom、set、minmax

本文解释了ClickHouse中二级（跳过）索引的机制，该机制允许在搜索ORDER BY之外的列时跳过数据块。考虑了索引类型：用于范围的minmax、用于低基数的set、用于高基数字符串的bloom_filter、用于LIKE搜索的ngrambf_v1、用于按令牌全文搜索的tokenbf_v1。展示了如何通过EXPLAIN indexes=1检查索引使用情况，描述了索引无用的场景及其成本（磁盘空间、INSERT速度降低）。提供了通过IP搜索多账户以进行欺诈检测的真实示例。

By Sudonull Editorial Team • Published 2026-06-11 • Updated 2026-06-11

ClickHouse中的二级（跳跃）索引：当ORDER BY索引不够用时

1. 跳跃索引的工作原理——跳过数据块

在传统数据库（PostgreSQL、MySQL）中，索引是一种精确指向满足条件的行的结构。B树会说：“值 user_id = 123 在第45678行”。

在ClickHouse中，主索引（通过ORDER BY的稀疏索引）工作方式不同。它仅存储每8192行（颗粒）的值，并能高效地剪枝那些在ORDER BY中靠前列的整个数据块。

但如果你需要搜索一个不在ORDER BY中的列呢？例如，你想查找具有特定IP地址的所有投注，但你的ORDER BY是 (user_id, created_at)。ClickHouse将不得不读取所有颗粒，并在读取后过滤IP。这称为全表扫描。

二级（跳跃）索引解决了这个问题。它们不指向特定行，而是说：“在这个N个颗粒的块中，肯定没有这个值——你可以跳过它。”如果索引说“可能”——ClickHouse仍然会读取该块。

现实类比： 想象你在图书馆里找一本绿色封面的书。主索引（按作者姓氏的目录）帮不上忙。但你走过书架，快速扫一眼：“这个书架上的书全是蓝色的——跳过。这个书架上有绿色的——我来检查。”跳跃索引就像颜色编码的书架，而不是精确的指针。

为什么叫跳跃？ 因为索引的主要工作是跳过肯定不需要的块。跳过的块越多，查询越快。

重要限制： 跳跃索引仅在颗粒级别工作。它们无法找到颗粒内的确切行位置。因此，当所需值很少见（低选择性）时，索引才有用。如果80%的行匹配条件——你仍然需要读取所有内容。

2. INDEX ... TYPE minmax —— 用于范围查询

最简单的跳跃索引是 minmax。它存储每个颗粒组中某列的最小值和最大值。

CREATE TABLE player_events
(
    user_id     UInt64,
    event_time  DateTime,
    amount      Decimal(18,2),
    outcome     String      -- 'win', 'loss', 'push'
)
ENGINE = MergeTree()
ORDER BY (user_id, event_time)              -- 主排序
INDEX idx_outcome_minmax outcome TYPE minmax GRANULARITY 4;

参数解析：

INDEX idx_outcome_minmax —— 索引名称（可自定义，但要有意义）。
outcome —— 建立索引的列。
TYPE minmax —— 索引类型：存储一组颗粒中的最小值和最大值。
GRANULARITY 4 —— 多少个颗粒（每个8192行）组合成一个索引组。这里4 × 8192 = 32768行每个索引条目。

查询中的工作方式：

-- 查找特定结果的记录
SELECT * FROM player_events 
WHERE outcome = 'win' AND event_time >= '2025-06-01';

ClickHouse读取 idx_outcome_minmax 索引：

组1：min='loss', max='push' → 没有'win' → 跳过32768行。
组2：min='loss', max='win' → 包含'win' → 读取该组。
组3：min='win', max='win' → 只有'win' → 读取。

minmax何时有效：

单调变化的列（时间、ID、温度）。
唯一值少但分布不均匀的列。
范围查询（BETWEEN、>=、<=）。

何时无用：

随机值（如哈希、UUID）。最小值和最大值将覆盖整个范围，索引不会跳过任何内容。

3. INDEX ... TYPE set —— 用于低基数列上的等值查询

set 索引存储一组颗粒中的唯一值。如果搜索的值不在这个集合中——跳过该组。

CREATE TABLE bets
(
    user_id     UInt64,
    sport_id    UInt8,      -- 只有20种运动
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, user_id)
INDEX idx_sport sport_id TYPE set(10) GRANULARITY 2;

参数：

set(10) —— 索引为每个组存储的最大唯一值数量。如果一个组包含超过10个唯一的sport_id值，索引只记住10个（可能导致误报）。选择一个略大于预期列基数的数字。

工作方式：

-- 查询特定运动
SELECT sum(amount) FROM bets WHERE sport_id = 1;

idx_sport 索引知道每个颗粒组中出现了哪些sport_id值。如果一个组不包含sport_id=1——跳过整个组。如果包含——读取它。

set何时有效：

列基数低（最多几百个值）。
等值查询（=、IN）。
数据在颗粒内分组良好（例如，一小时内所有足球投注紧凑存储）。

博彩示例： 一个投注表，ORDER BY (created_at, user_id)。列 sport_id（20个值）不在ORDER BY中。在 sport_id 上建立set索引可以快速找到所有冰球投注，而无需扫描所有内容。

4. bloom_filter 索引 —— 用于高基数字符串列

布隆过滤器是一种概率数据结构。它可以判断“该值肯定不在组中”或“该值可能存在”。它从不说“肯定存在”——它只能出现误报。

CREATE TABLE player_events
(
    user_id     UInt64,
    ip_address  String,          -- 数百万个唯一IP
    event_type  String,
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
INDEX idx_ip ip_address TYPE bloom_filter(0.01) GRANULARITY 3;

参数：

bloom_filter(0.01) —— 误报率为1%。数字越小，索引越精确，但占用更多空间。通常使用0.01（1%）或0.001（0.1%）。
GRANULARITY 3 —— 每个索引条目3个颗粒（3 × 8192 = 24576行）。

工作方式：

-- 查找来自可疑IP的所有事件
SELECT * FROM player_events WHERE ip_address = '192.168.1.100';

每个颗粒组的索引通过布隆过滤器检查：“这个组可能包含IP=192.168.1.100吗？”如果“否”——跳过该组。如果“是”（包括误报）——读取该组。

布隆过滤器何时有效：

高基数列（IP地址、电子邮件、user_agent）。
精确匹配查询。
搜索的值很少见（例如，1000万个IP中的特定一个）。

为什么minmax不适合IP： 由于随机分布，组中的最小和最大IP几乎覆盖整个范围，因此剪枝无效。

实际示例——多账户检测（一个IP，多个user_id）：

-- 查找来自给定IP的所有用户
SELECT DISTINCT user_id FROM player_events 
WHERE ip_address = '192.168.1.100';

没有索引——全表扫描。使用 ip_address 上的布隆过滤器——快速，即使该IP出现在0.1%的行中。

5. ngrambf_v1 —— 用于字符串的LIKE/ILIKE搜索

有时你需要按子串搜索：WHERE player_name LIKE '%John%'。常规索引不起作用，因为开头的 % 阻止了B树的使用。

ngrambf_v1 将字符串拆分为n-gram——长度为N的子串。例如，对于N=3，'Johny' → 'Joh'、'ohn'、'hny'。索引在这些n-gram上构建布隆过滤器。

CREATE TABLE players
(
    player_id   UInt64,
    player_name String,
    country     String
)
ENGINE = MergeTree()
ORDER BY player_id
INDEX idx_name player_name TYPE ngrambf_v1(3, 500000, 2, 0.01) GRANULARITY 4;

ngrambf_v1的参数：

3 —— n-gram长度（通常2-4）。越大越精确，但占用更多内存。
500000 —— 每个索引条目的布隆过滤器大小（字节）。
2 —— 哈希函数数量（通常2-4）。
0.01 —— 误报概率。

如何在查询中使用：

-- 查找名字包含'Alex'的玩家
SELECT * FROM players WHERE player_name LIKE '%Alex%';

索引将 'Alex' 拆分为n-gram（'Ale'、'lex'），并检查这些n-gram是否存在于组中。如果某个组没有这些n-gram——跳过该组。

限制：

仅适用于 LIKE 和 ILIKE（不区分大小写）。
要求搜索字符串长于n-gram（至少3个字符）。
不适用于短字符串（例如 'a'）。

何时使用： 按玩家昵称、部分电子邮件、地址搜索。在博彩中——通过玩家名字的一部分查找玩家以进行客户支持。

6. tokenbf_v1 —— 用于令牌（单词）搜索

tokenbf_v1 类似于 ngrambf_v1，但将字符串拆分为令牌——由空格、标点、数字分隔的单词。

CREATE TABLE logs
(
    log_time    DateTime,
    message     String,
    user_agent  String
)
ENGINE = MergeTree()
ORDER BY log_time
INDEX idx_msg message TYPE tokenbf_v1(500000, 2, 0.01) GRANULARITY 2;

tokenbf_v1的参数：

500000 —— 布隆过滤器大小（字节）。
2 —— 哈希函数数量。
0.01 —— 误报概率。

工作方式：

对于字符串 "User 123 logged in from Ukraine"，令牌：'User'、'123'、'logged'、'in'、'from'、'Ukraine'。

-- 查找所有提及错误的日志
SELECT * FROM logs WHERE message LIKE '%error%';

索引将 'error' 拆分为令牌（仅 'error'），并检查该令牌在组中是否存在。

tokenbf_v1何时优于ngrambf_v1：

搜索整个单词（而非部分）。
英文文本、日志、user_agent。
误报比ngrambf_v1少。

博彩示例： 搜索包含 'fraud' 或 'suspicious' 消息的投注日志。

7. 如何检查索引是否被使用 —— EXPLAIN indexes=1

你创建了索引，但它工作吗？ClickHouse提供了 EXPLAIN indexes = 1 命令。

-- 启用索引使用分析
EXPLAIN indexes = 1
SELECT user_id, amount FROM bets 
WHERE sport_id = 1 AND created_at >= '2025-06-01';

示例输出：

Expression
  ...
  ReadFromMergeTree
    Indexes:
      PrimaryKey
        Condition: (created_at >= '2025-06-01')
        Used keys: (created_at)
        Granules: 150 / 12000
      Skip
        Name: idx_sport
        Type: set
        Condition: sport_id = 1
        Granules: 80 / 12000

数字含义：

Granules: 150 / 12000 —— 主键剪枝了11850个颗粒，剩余150个。
Skip ... Granules: 80 / 150 —— 跳跃索引进一步剪枝了70个颗粒，剩余80个。
最终收益：12000 → 80个颗粒被读取。

如果索引未被使用：

未显示在 Skip 部分 → 要么未创建，要么查询与索引类型不匹配。
Granules: 12000 / 12000 —— 读取所有内容，索引未起作用。

索引可能未被使用的原因：

索引类型与操作符不匹配（minmax 对 = 无效）。
颗粒度太大（粗粒度索引）。
搜索的值几乎无处不在（索引无法跳过块）。

8. 跳跃索引何时无帮助

场景1：高基数 + 随机分布

如果列 user_id（数百万个值）且ORDER BY不以 user_id 开头，跳跃索引（即使是布隆过滤器）剪枝效果差。因为值 user_id=123 可能分散在整个表中。

场景2：查询未过滤“好”列

如果WHERE只有 amount > 1000 且 amount 上没有索引，则 sport_id 上的索引无帮助。

场景3：GRANULARITY 太大

如果 GRANULARITY = 64（每组524k行），且表有1000万行，则只有约20个组。你只能跳过20个块，微不足道。

场景4：搜索的值出现在50%+的行中

跳跃索引适用于稀有值。如果一半的行匹配条件，索引几乎对所有块都说“可能”，你将读取所有内容。

场景5：索引太小

-- 不好：布隆过滤器太小（10000字节）
INDEX idx_ip ip_address TYPE bloom_filter(0.01) GRANULARITY 4;

小的布隆过滤器会产生许多误报（经常在不是时也说“可能”）。索引停止跳过块。

9. 跳跃索引的成本 —— 内存和插入速度

每个索引都有成本。不要“以防万一”创建索引。

成本1：额外的磁盘空间

minmax —— 非常便宜（每个组每列8字节）。
set(100) —— 更贵，但每个组几千字节。
bloom_filter —— 昂贵：500k字节且GRANULARITY=1时，对于有10k个组的表，仅索引就需要5 GB。

成本2：插入变慢

每次插入时，ClickHouse为每个颗粒更新所有索引。表上的5个索引可能使插入速度减慢2-3倍。

经验法则：

大型表（数十亿行）上不超过2-3个跳跃索引。
仅对经常过滤的列建立索引。
对于测试工作负载——实验。对于生产——测量。

如何估算索引成本：

-- 检查表中的索引大小
SELECT 
    table,
    index_name,
    formatReadableSize(index_size) AS size
FROM system.indexes
WHERE table = 'bets';

如果索引大小接近数据大小——你可能过度了。

10. 实际示例：通过IP地址进行欺诈检测

想象在你的赌场中，一群玩家使用一个IP地址进行多账户操作（违反规则）。你需要找到所有从可疑IP登录的人。

事件表：

5亿行。
ORDER BY = (user_id, event_time) —— 按用户快速查询。
频繁查询：SELECT user_id FROM events WHERE ip_address = 'x.x.x.x'。

解决方案 —— bloom_filter 索引：

CREATE TABLE player_events
(
    user_id     UInt64,
    event_time  DateTime,
    ip_address  String,
    event_type  String,   -- 'login', 'bet', 'withdraw'
    amount      Decimal(18,2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, event_time)
INDEX idx_ip ip_address TYPE bloom_filter(0.01) GRANULARITY 4;

性能对比：

场景	无索引	使用bloom_filter (0.01)
稀有IP查询时间（0.001%行）	60秒（全表扫描5亿）	0.3秒
频繁IP查询时间（5%行）	60秒	45秒（索引帮助不大）
表大小（压缩后）	100 GB	108 GB（+8%）
插入时间（1万行/秒）	每批0.5毫秒	每批0.7毫秒（+40%）

如何编写反欺诈查询：

-- 查找所有使用过可疑IP的用户
SELECT DISTINCT user_id 
FROM player_events 
WHERE ip_address = '192.168.1.100'   -- bloom_filter帮助
AND event_time >= today() - 30;      -- 分区剪枝旧数据

-- 然后检查有多少不同账户使用这个IP
SELECT count(DISTINCT user_id) AS suspicious_accounts
FROM player_events 
WHERE ip_address = '192.168.1.100';

为什么用bloom_filter而不是minmax：

IP地址随机分布；组中的最小/最大几乎总是覆盖整个范围。
布隆过滤器非常适合集合成员检查。

下一步

现在你已经了解了ClickHouse所有类型的二级索引。接下来的主题：

组合索引 —— 多个跳跃索引如何协同工作。
调整颗粒度 —— 如何为不同数据类型选择最佳颗粒大小。
分布式表中的索引 —— 跳跃索引在集群中如何工作。

总结： ClickHouse中的跳跃索引不是银弹。它们不像PostgreSQL中的B树那样工作。但对于正确的场景（稀有值、布隆过滤器、n-gram），它们能将全表扫描变成闪电般的查询。关键规则：

在发现问题（全表扫描）之前不要创建索引。
从高基数列的bloom_filter开始，低基数列用set。
始终使用 EXPLAIN indexes = 1 检查。
记住成本：磁盘空间 + 插入变慢。

← 上一篇: ClickHouse中的物化视图：增量处理的强大力量

— Editorial Team

社区

暂无评论。

继续阅读

主题

数据库 ClickHouse

常见问题

跳过索引与常规B树有何不同？

跳过索引不指向特定行，而是存储关于颗粒块的聚合信息（最小值/最大值、值集合、布隆过滤器）。它们只能判断块肯定不包含搜索值，从而跳过该块。

何时bloom_filter索引比minmax更高效？

对于高基数和随机分布的列（IP地址、电子邮件、哈希值）。minmax对此类列无效，因为组内的最小值和最大值几乎覆盖整个范围。

如何检查查询中是否使用了跳过索引？

使用EXPLAIN indexes = 1。在Skip部分，将列出索引及其修剪的颗粒数（例如，Granules: 80 / 12000）。

ngrambf_v1和tokenbf_v1有什么区别？

ngrambf_v1将字符串拆分为重叠的n-gram（适用于LIKE '%substring%'），而tokenbf_v1按分隔符拆分为单词（适用于在日志中搜索完整单词）。

跳过索引的成本是多少？

额外的磁盘空间（bloom_filter可能使表大小增加5-10%）和INSERT速度降低（使用3-5个索引时降低20-40%）。

ClickHouse跳过索引：bloom、set、minmax

ClickHouse中的二级（跳跃）索引：当ORDER BY索引不够用时

1. 跳跃索引的工作原理——跳过数据块

2. INDEX ... TYPE minmax —— 用于范围查询

3. INDEX ... TYPE set —— 用于低基数列上的等值查询

4. bloom_filter 索引 —— 用于高基数字符串列

5. ngrambf_v1 —— 用于字符串的LIKE/ILIKE搜索

6. tokenbf_v1 —— 用于令牌（单词）搜索

7. 如何检查索引是否被使用 —— EXPLAIN indexes=1

8. 跳跃索引何时无帮助

9. 跳跃索引的成本 —— 内存和插入速度

10. 实际示例：通过IP地址进行欺诈检测

下一步

社区

继续阅读

最近浏览

标签

主题

常见问题

ClickHouse中的二级（跳跃）索引：当ORDER BY索引不够用时

1. 跳跃索引的工作原理——跳过数据块

2. INDEX ... TYPE minmax —— 用于范围查询

3. INDEX ... TYPE set —— 用于低基数列上的等值查询

4. bloom_filter 索引 —— 用于高基数字符串列

5. ngrambf_v1 —— 用于字符串的LIKE/ILIKE搜索

6. tokenbf_v1 —— 用于令牌（单词）搜索

7. 如何检查索引是否被使用 —— EXPLAIN indexes=1

8. 跳跃索引何时无帮助

9. 跳跃索引的成本 —— 内存和插入速度

10. 实际示例：通过IP地址进行欺诈检测

下一步

社区

继续阅读

ClickHouse 物化视图：INSERT 触发器

特殊ClickHouse引擎：何时不需要MergeTree

ClickHouse中的字典：无需JOIN的快速查找

ClickHouse中的TTL：数据生命周期管理

ClickHouse中的ORDER BY和PRIMARY KEY：索引选择

ClickHouse中的分区：策略与操作

ClickHouse中的CollapsingMergeTree：无需UPDATE即可更新

ClickHouse中的SummingMergeTree和AggregatingMergeTree

ClickHouse中的ReplacingMergeTree：完整指南

Oracle在耶路撒冷开设了一个地下云计算区域——以色列首个主权云

最近浏览

标签

主题

常见问题

分享本文