当您查询海量数据时，性能可能会因为需要进行全列扫描来匹配 WHERE 子句而下降。为解决此问题，ByteHouse 提供了跳数索引（Data Skipping Index）功能。它通过跳过不包含匹配值的数据块，来大幅提升查询效率。本文将介绍跳数索引的基本概念和使用方法。

创建跳数索引 #

您只能在 CnchMergeTree 表系列上应用数据跳数索引。 这些索引涉及四个主要参数：

1. 索引名称：用作创建索引文件的标识符。它是删除或具体化 materializing 索引等操作的必要参数。
2. 索引表达式：索引表达式用于计算和确定索引中存储的值范围的公式。它可以包含列、基本运算符和索引类型指定的函数的指定子集的组合。
3. 类型：索引类型定义了用于跳过读取和评估每个索引块的计算方法。
4. 粒度：每个索引块都由定义数量的颗粒组成，由粒度参数决定。 例如，如果主表索引的粒度为 8192 行，并且索引粒度设置为 4，则每个索引“块”将包含 32,768 行（8192 行 x 4 粒度）。

跳数索引语法示例 #

/*
Index Name: key_i_idx
Index expression: key_i
Type: minmax
Granularity: 1
*/
INDEX key_i_idx key_i TYPE minmax GRANULARITY 1, 

支持的数据类型 #

最大最小值 minmax

这种高效的索引方法无需任何特定参数即可运行。 它保留每个块的索引表达式的最小值和最大值。 对于元组表达式，它单独保留元组中每个元素的末端。 对于倾向于对值进行松散排序的列来说，它是最有效的。 在各种索引类型中，这种索引类型通常是查询执行期间最具成本效益的。 但是，需要注意的是，该索引仅适用于标量或元组表达式，不能与生成数组或映射数据类型的表达式一起使用。

数据集 Set

这种轻量级索引类型允许使用单个参数 max_size，即每个块设置的值集。 当设置为 0 时，它在块内可容纳数量不受限制的不同值。 该集合包含块中的所有值（或者如果值的数量超过 max_size 则为空）。 尽管整体基数较高，但此索引类型对于每组颗粒中值聚集在一起的列特别有效。
该索引的成本、性能和有效性取决于这些块内的基数。 如果每个块都有大量唯一值，则针对大型索引集评估查询条件的成本将变得很高。 或者，如果集合由于超过指定的 max_size 而为空，则索引不会被应用。

布隆过滤器类型 Bloom filter types

布隆过滤器是一种紧凑的数据结构，设计用于在集合成员是否有效进行判断，可能存在极小可能性的误报。 在跳数索引的使用情况下，这并不是主要的问题，因为它们只会导致去读取一些额外的、不必要的块。 然而，值得注意的是，潜在的误报意味着索引表达式应该为真。 否则，可能会无意中跳过有效数据。 应注意设计索引表达式，以尽量减少误报的风险，并确保准确跳过不相关的块。
布隆过滤器对于高效测试大量离散值非常有效，适用于产生大量条件表达式判断的场景。 具体来说，布隆过滤器索引可以应用于数组，数组中的每个值都会被测试。 这种调整允许对数组或映射中的成员有效性进行判断，从而增强此类数据结构的索引功能。

• 专用的 tokenbf_v1 索引是为增强的布隆过滤器功能而定制的，并且需要调整三个相关的参数：
  • 过滤器大小（以字节为单位）：此参数控制布隆过滤器的大小（以字节为单位）。 更大的过滤器可以减少误报的可能性，其代价是增加存储空间。
  • 哈希函数的数量：应用于布隆过滤器的哈希函数的数量。使用更多哈希函数有助于减少误报。
  • 哈希函数的种子：用于生成布隆过滤器哈希函数的种子。
  • 此索引仅适用于 String、FixedString 类型的数据。输入表达式被分割为由非字母数字字符分隔的字符序列。例如，列值 This is a candidate for a "full text" search 将被分割为This is a candidate for full text search。
  • 此索引用于 LIKE、EQUALS、in、hasToken() 和类似的长字符串中单词和其他值的搜索。例如，一种可能的用途是在非结构的应用程序日志行列中搜索少量的类名或行号。
• 更专业的 ngrambf_v1。该索引的功能与 tokenbf_v1 相同。在布隆过滤器设置之前需要一个额外的参数，即要索引的 ngram 的大小。一个 ngram 是长度为 n 的任何字符串，比如如果 n 是 4，A short string会被分割为A sh`` sho, shor, hort, ort s, or st, r str, stri, trin, ring。这个索引对于文本搜索也很有用，特别是没有单词间断的语言，比如中文。
• 可以选择使用 false_positive 参数来指定从该过滤器得到假阳性响应的概率，其值范围在 0 到 1 之间。默认值为 0.025。支持的数据类型包括：Int*、UInt*、Float*、Enum、Date、DateTime、String、FixedString、Array、LowCardinality、Nullable、UUID。
  语法：

  bloom_filter([false_positive])
  

使用示例 #

载入数据集到 ByteHouse #

DROP DATABASE IF EXISTS test_si;
CREATE DATABASE test_si;

--创建表，用于加载 numbers_source.csv 数据集
CREATE TABLE test_si.numbers
(
    `number` UInt64
)
ENGINE = CnchMergeTree
ORDER BY number;

下载下面的文件，并通过数据加载功能将数据集加载到 test_si.numbers 表中。

numbers_source.csv

未知大小

创建不带索引的数据表 #

USE test_si;

--建表
CREATE TABLE test_skipindex
(
    `id` UInt64,
    `key_i` UInt64,
    `p_date` Date
)
ENGINE = CnchMergeTree
PARTITION BY p_date
ORDER BY id;

--将测试数据集插入表
INSERT INTO test_skipindex SELECT
    number,
    number,
    today()
FROM test_si.numbers
LIMIT 1000000;

--查询数据是否已成功插入
SELECT sum(id) FROM test_skipindex WHERE key_i = 1;

如果我们运行下面的命令：

-- 简单查询：从 test_skipindex 表中，筛选出 key_i 列值为 200 或 700 的所有行
SELECT * FROM `test_skipindex` WHERE key_i IN (200, 700);

结果显示，在没有索引的情况下，处理了 100 万行数据。

增加索引 #

给新表增加索引

EXPLAIN ANALYZE indexes=1; --执行计划分析，analyze真实执行查询并统计耗时 / 扫描量，indexes=1专门展示索引使用详情，用于验证跳过索引是否生效；
DROP TABLE IF EXISTS test_si.test_skipindex;

USE test_si;

--建表时增加索引
CREATE TABLE test_skipindex
(
    `id` UInt64,
    `key_i` UInt64,
    `p_date` Date,
    INDEX key_i_idx key_i TYPE minmax GRANULARITY 1
)
ENGINE = CnchMergeTree
PARTITION BY p_date
ORDER BY id
--index_granularity 默认值为 8192
SETTINGS index_granularity = 8192;

--将测试数据集插入表
INSERT INTO test_skipindex SELECT
    number,
    number,
    today()
FROM test_si.numbers
LIMIT 1000000;

--查询数据是否已成功插入
SELECT sum(id) FROM test_skipindex WHERE key_i = 1;

运行与上面相同的命令，

-- 简单查询：从 test_skipindex 表中，筛选出 key_i 列值为 200 或 700 的所有行
SELECT * FROM `test_skipindex` WHERE key_i IN (200, 700); 

结果表明，使用该索引的情况下仅处理了 8192 行数据，且执行速度更快。

在现有表为后续数据添加索引

EXPLAIN ANALYZE indexes=1; --执行计划分析，analyze真实执行查询并统计耗时 / 扫描量，indexes=1专门展示索引使用详情，用于验证索引是否生效；
DROP TABLE if EXISTS test_si.test_skipindex;
USE test_si;

--建表
CREATE TABLE test_skipindex
(
    `id` UInt64,
    `key_i` UInt64,
    `p_date` Date
)
ENGINE = CnchMergeTree
PARTITION BY p_date
ORDER BY id;

--将测试数据集插入表
INSERT INTO test_skipindex SELECT
    number,
    number,
    today()
FROM test_si.numbers
LIMIT 1000000;

--建表后添加索引
ALTER TABLE test_si.test_skipindex ADD index key_i_idx key_i TYPE minmax GRANULARITY 1;

--查询数据是否已成功插入
SELECT sum(id) FROM test_skipindex WHERE key_i = 1;

如果我们运行相同的命令，我们的结果将是处理 100 万行。

这是因为在创建索引之前添加的数据被认为是历史数据，对于历史数据，需参考下文，执行以下步骤来建立索引。

为历史数据增加索引

要为历史数据构建索引，您需要在命令中使用 MATERIALIZE INDEX。 如果仅使用 ALTER TABLE...ADD 命令而不执行MATERIALIZE INDEX，则只会为新写入的数据生成索引。

--执行以下额外代码块
ALTER TABLE test_si.test_skipindex MATERIALIZE INDEX key_i_idx;

查询结果表明历史数据索引添加成功。仅查询处理 8192 行，且速度更快。

为历史数据分区增加索引

默认情况下，MATERIALIZE INDEX 会为所有历史数据生成索引。如果您只想为某个特定的历史数据分区生成索引，请将 IN PARTITION 子句与 MATERIALIZE INDEX 命令结合使用，这样索引就只会针对该指定分区生成。

DROP TABLE if EXISTS test_si.test_skipindex;
USE test_si; 
--建表
CREATE TABLE test_skipindex
(
    `id` UInt64,
    `key_i` UInt64,
    `p_date` Date
)
ENGINE = CnchMergeTree
PARTITION BY p_date
ORDER BY id;

--将测试数据集插入表
INSERT INTO test_skipindex SELECT
    number,
    number,
    today()
FROM test_si.numbers
LIMIT 1000000;

--将测试数据集插入表
INSERT INTO test_skipindex SELECT
    number,
    number,
    today()-1
FROM test_si.numbers
LIMIT 1000000;

ALTER TABLE test_si.test_skipindex ADD index key_i_idx key_i TYPE minmax GRANULARITY 1;

-- 假设前一天的日期为 20230927，则仅为 20230927 日期分区生成索引
ALTER TABLE test_si.test_skipindex MATERIALIZE INDEX key_i_idx IN PARTITION '20230927';

--查询数据是否已成功插入
SELECT sum(id) FROM test_skipindex WHERE key_i = 1 AND p_date = today()-1;

--查询数据是否已成功插入
SELECT sum(id) FROM test_skipindex WHERE key_i = 1 AND p_date = today();

-- 开启执行计划查看
SET send_logs_level = 'trace';

-- 执行带索引字段的过滤查询
EXPLAIN SELECT * FROM test_si.test_skipindex WHERE key_i = 100;

如果我们运行下图中的简单查询，我们可以看到分区 20230927 [today() - 1] 中的历史数据生成了索引。

对于未指定分区的历史数据，不生成索引。

删除索引 #

ALTER TABLE test_si.test_skipindex DROP INDEX key_i_idx;

SHOW CREATE TABLE test_skipindex;

SELECT sum(id) FROM test_skipindex WHERE key_i = 1;

使用建议 #

一般来说，当数据量较小时，建立索引的成本不会很高。 但是，如果您希望为全量历史数据建立索引，并且数据量非常大，直接使用 MATERIALIZE INDEX 为所有历史数据添加索引可能会对集群负载造成一定的压力。 在这种情况下，我们推荐使用 IN PARTITION 按分区建立索引。