在适当情况下使用数据跳跃索引

在遵循了之前的最佳实践之后，应考虑使用数据跳跃索引，例如优化了类型、选择了良好的主键并利用了物化视图。如果您不熟悉跳跃索引，本指南是一个好的起点。

如果谨慎使用并了解其工作原理，这些类型的索引可用于加速查询性能。

ClickHouse 提供了一种强大的机制，称为数据跳跃索引，可以显著减少查询执行期间扫描的数据量——尤其是在主键对特定筛选条件没有帮助时。与依赖于基于行的辅助索引（如 B 树）的传统数据库不同，ClickHouse 是一个列式存储，它不以支持此类结构的方式存储行位置。相反，它使用跳跃索引，帮助它避免读取保证不匹配查询筛选条件的的数据块。

跳跃索引通过存储有关数据块的元数据（例如最小值/最大值、值集或 Bloom 过滤器表示）并在查询执行期间使用此元数据来确定可以完全跳过的哪些数据块来工作。它们仅适用于 MergeTree 系列的表引擎，并使用表达式、索引类型、名称和定义每个索引块大小的粒度来定义。这些索引与表数据一起存储，并在查询过滤器匹配索引表达式时进行咨询。

有几种类型的数据跳跃索引，每种索引都适用于不同类型的查询和数据分布

minmax：跟踪每个块中表达式的最小值和最大值。适用于对松散排序的数据进行范围查询。
set(N)：跟踪每个块中最多 N 个值的值集。对每个块的基数较低的列有效。
bloom_filter：概率性地确定一个值是否存在于一个块中，从而为集合成员资格提供快速近似筛选。有效于优化查找“大海捞针”的查询，其中需要正匹配项。
tokenbf_v1 / ngrambf_v1：专门的 Bloom 过滤器变体，专为在字符串中搜索标记或字符序列而设计——尤其适用于日志数据或文本搜索用例。

虽然功能强大，但必须谨慎使用跳跃索引。只有在它们消除有意义数量的数据块时，它们才能提供好处，并且如果查询或数据结构不一致，实际上可能会引入开销。即使一个块中存在匹配值，仍然必须读取整个块。

有效的跳跃索引使用通常取决于索引列与表的键之间的强相关性，或者以将相似值分组在一起的方式插入数据。

通常，在确保正确的主键设计和类型优化之后，最好应用数据跳跃索引。它们特别适用于

具有高总体基数但每个块内的基数较低的列。
对于搜索至关重要的稀有值（例如错误代码、特定 ID）。
在非主键列上进行筛选且分布集中的情况。

始终

使用真实数据和真实的查询测试跳跃索引。尝试不同的索引类型和粒度值。
使用诸如 send_logs_level='trace' 和 EXPLAIN indexes=1 之类的工具评估它们的影响，以查看索引的有效性。
始终评估索引的大小以及粒度对其的影响。降低粒度大小通常会在某个点提高性能，从而过滤更多的颗粒并需要扫描。但是，随着索引大小随着较低的粒度而增加，性能也可能会下降。测量各种粒度数据点的性能和索引大小。这对于 Bloom 过滤器索引尤其重要。

如果使用得当，跳跃索引可以提供显着的性能提升——如果使用不当，它们可能会增加不必要的成本。

有关数据跳跃索引的更详细指南，请参阅此处。

示例

考虑以下优化的表。这包含 Stack Overflow 数据，每篇帖子一行。

CREATE TABLE stackoverflow.posts
(
  `Id` Int32 CODEC(Delta(4), ZSTD(1)),
  `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
  `AcceptedAnswerId` UInt32,
  `CreationDate` DateTime64(3, 'UTC'),
  `Score` Int32,
  `ViewCount` UInt32 CODEC(Delta(4), ZSTD(1)),
  `Body` String,
  `OwnerUserId` Int32,
  `OwnerDisplayName` String,
  `LastEditorUserId` Int32,
  `LastEditorDisplayName` String,
  `LastEditDate` DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)),
  `LastActivityDate` DateTime64(3, 'UTC'),
  `Title` String,
  `Tags` String,
  `AnswerCount` UInt16 CODEC(Delta(2), ZSTD(1)),
  `CommentCount` UInt8,
  `FavoriteCount` UInt8,
  `ContentLicense` LowCardinality(String),
  `ParentId` String,
  `CommunityOwnedDate` DateTime64(3, 'UTC'),
  `ClosedDate` DateTime64(3, 'UTC')
)
ENGINE = MergeTree
PARTITION BY toYear(CreationDate)
ORDER BY (PostTypeId, toDate(CreationDate))

此表针对按帖子类型和日期进行筛选和聚合的查询进行了优化。假设我们希望计算发布于 2009 年之后且浏览次数超过 10,000,000 的帖子数量。

SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)

┌─count()─┐
│     5   │
└─────────┘

1 row in set. Elapsed: 0.720 sec. Processed 59.55 million rows, 230.23 MB (82.66 million rows/s., 319.56 MB/s.)

此查询可以使用主索引排除一些行（和颗粒）。但是，如上述响应和以下 EXPLAIN indexes = 1 所示，仍然需要读取大部分行

EXPLAIN indexes = 1
SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)
LIMIT 1

┌─explain──────────────────────────────────────────────────────────┐
│ Expression ((Project names + Projection))                        │
│   Limit (preliminary LIMIT (without OFFSET))                     │
│     Aggregating                                                  │
│       Expression (Before GROUP BY)                               │
│         Expression                                               │
│           ReadFromMergeTree (stackoverflow.posts)                │
│           Indexes:                                               │
│             MinMax                                               │
│               Keys:                                              │
│                 CreationDate                                     │
│               Condition: (CreationDate in ('1230768000', +Inf))  │
│               Parts: 123/128                                     │
│               Granules: 8513/8545                                │
│             Partition                                            │
│               Keys:                                              │
│                 toYear(CreationDate)                             │
│               Condition: (toYear(CreationDate) in [2009, +Inf))  │
│               Parts: 123/123                                     │
│               Granules: 8513/8513                                │
│             PrimaryKey                                           │
│               Keys:                                              │
│                 toDate(CreationDate)                             │
│               Condition: (toDate(CreationDate) in [14245, +Inf)) │
│               Parts: 123/123                                     │
│               Granules: 8513/8513                                │
└──────────────────────────────────────────────────────────────────┘

25 rows in set. Elapsed: 0.070 sec.

简单的分析表明，ViewCount 与 CreationDate（主键）相关，正如人们所期望的那样——帖子存在的时间越长，被浏览的时间就越多。

SELECT toDate(CreationDate) AS day, avg(ViewCount) AS view_count FROM stackoverflow.posts WHERE day > '2009-01-01'  GROUP BY day

因此，这对于数据跳跃索引来说是一个合乎逻辑的选择。鉴于数值类型，minmax 索引是合理的。我们使用以下 ALTER TABLE 命令添加索引——首先添加它，然后“使其生效”。

ALTER TABLE stackoverflow.posts
  (ADD INDEX view_count_idx ViewCount TYPE minmax GRANULARITY 1);

ALTER TABLE stackoverflow.posts MATERIALIZE INDEX view_count_idx;

该索引也可以在初始表创建期间添加。 DDL 中定义 minmax 索引的模式

CREATE TABLE stackoverflow.posts
(
  `Id` Int32 CODEC(Delta(4), ZSTD(1)),
  `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
  `AcceptedAnswerId` UInt32,
  `CreationDate` DateTime64(3, 'UTC'),
  `Score` Int32,
  `ViewCount` UInt32 CODEC(Delta(4), ZSTD(1)),
  `Body` String,
  `OwnerUserId` Int32,
  `OwnerDisplayName` String,
  `LastEditorUserId` Int32,
  `LastEditorDisplayName` String,
  `LastEditDate` DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)),
  `LastActivityDate` DateTime64(3, 'UTC'),
  `Title` String,
  `Tags` String,
  `AnswerCount` UInt16 CODEC(Delta(2), ZSTD(1)),
  `CommentCount` UInt8,
  `FavoriteCount` UInt8,
  `ContentLicense` LowCardinality(String),
  `ParentId` String,
  `CommunityOwnedDate` DateTime64(3, 'UTC'),
  `ClosedDate` DateTime64(3, 'UTC'),
  INDEX view_count_idx ViewCount TYPE minmax GRANULARITY 1 --index here
)
ENGINE = MergeTree
PARTITION BY toYear(CreationDate)
ORDER BY (PostTypeId, toDate(CreationDate))

以下动画说明了我们的 minmax 跳跃索引如何为示例表构建，跟踪表中每个数据块（颗粒）的最小和最大 ViewCount 值

重复我们之前的查询显示了显著的性能改进。请注意扫描的行数减少

SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)

┌─count()─┐
│     5   │
└─────────┘

1 row in set. Elapsed: 0.012 sec. Processed 39.11 thousand rows, 321.39 KB (3.40 million rows/s., 27.93 MB/s.)

EXPLAIN indexes = 1 确认了索引的使用。

EXPLAIN indexes = 1
SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)

┌─explain────────────────────────────────────────────────────────────┐
│ Expression ((Project names + Projection))                          │
│   Aggregating                                                      │
│     Expression (Before GROUP BY)                                   │
│       Expression                                                   │
│         ReadFromMergeTree (stackoverflow.posts)                    │
│         Indexes:                                                   │
│           MinMax                                                   │
│             Keys:                                                  │
│               CreationDate                                         │
│             Condition: (CreationDate in ('1230768000', +Inf))      │
│             Parts: 123/128                                         │
│             Granules: 8513/8545                                    │
│           Partition                                                │
│             Keys:                                                  │
│               toYear(CreationDate)                                 │
│             Condition: (toYear(CreationDate) in [2009, +Inf))      │
│             Parts: 123/123                                         │
│             Granules: 8513/8513                                    │
│           PrimaryKey                                               │
│             Keys:                                                  │
│               toDate(CreationDate)                                 │
│             Condition: (toDate(CreationDate) in [14245, +Inf))     │
│             Parts: 123/123                                         │
│             Granules: 8513/8513                                    │
│           Skip                                                     │
│             Name: view_count_idx                                   │
│             Description: minmax GRANULARITY 1                      │
│             Parts: 5/123                                           │
│             Granules: 23/8513                                      │
└────────────────────────────────────────────────────────────────────┘

29 rows in set. Elapsed: 0.211 sec.

我们还展示了一个动画，说明 minmax 跳跃索引如何剪除所有无法包含我们的示例查询中 ViewCount > 10,000,000 谓词的匹配项的行块

示例​

相关文档​

示例

相关文档