Doris索引小总结

在Doris中，添加索引是提升查询性能的重要手段。Doris支持两种类型的索引：内置智能索引和用户创建的二级索引。以下是关于如何在Doris中添加索引的详细步骤和注意事项：

内置智能索引

前缀索引：
- 基于排序键以有序的方式存储数据，为每1024行数据创建一个前缀索引。
- 索引中的键是当前1024行组的第一行中已排序列的值。
- 查询时，通过前缀索引可以快速定位到相关的1024行组，并从那里开始扫描。
- 特别注意：Doris只有前36个字节能走前缀索引。因此，在创建表时，需要手动指定key，并且索引字段的类型和长度要尽可能精确，以增大索引范围。
ZoneMap索引：
- 在列存格式上，对每一列自动维护的索引信息，包括Min/Max值、null值个数等。
- 查询时，会根据范围条件过滤的字段按照ZoneMap统计信息选取扫描的数据范围。
- ZoneMap索引对用户是透明的，无需额外操作。

用户创建的二级索引

倒排索引：
- 用于文本类型的全文检索和普通数值日期类型的等值范围查询。
- 可以从海量数据中快速过滤出满足条件的行。
- 不同数据模型下的创建规则：
  - Aggregate KEY表模型：只能为Key列建立倒排索引。
  - Unique KEY表模型：需要开启merge on write特性后，可以为任意列建立倒排索引。
  - Duplicate KEY表模型：可以为任意列建立倒排索引。
BloomFilter索引：
- 一种高空间效率的概率数据结构，用于检查元素是否在集合中。
- 适用于高基数（5000以上）列上的等值查询场景。
- 创建方式：在表创建语句的PROPERTIES中添加”bloom_filter_columns”=”k1,k2,k3”，其中k1,k2,k3为要创建BloomFilter索引的关键列名称。
NGram BloomFilter索引：
- 为了提升LIKE的查询性能。
- 适用于亿级别以上数据，且只有模糊匹配需求时使用。
- 只支持字符串列，且和BloomFilter索引为互斥关系，即同一个列只能设置两者中的一个。
Bitmap索引：
- 能够应用在Duplicate、Unique数据模型的所有列和Aggregate模型的key列上。
- 仅在Segment V2下生效，创建索引时表的存储格式将默认转换为V2格式。
- 适用于低基数的列上（建议在100到100,000之间），如职业、地市等。
- 特定类型的查询（如COUNT、OR、AND等逻辑操作）因为只需要进行位运算，所以更适合使用Bitmap索引。

添加索引

BloomFilter索引

（全版本适用）

创建表时添加索引

bash

1	PROPERTIES ("bloom_filter_columns"="saler_id,category_id")

修改表时添加索引

bash

1	ALTER TABLE ods_test_bloom_delta SET ("bloom_filter_columns" = "city_org_code");

查看索引

bash

1	SHOW CREATE TABLE example_db.example_bloom_index_tbl;

删除索引

bash

1	ALTER TABLE ods_test_bloom_delta SET ("bloom_filter_columns" = "");

BloomFilter索引的注意事项
1. 适用场景：
  BloomFilter索引适用于高基数列的等值查询场景，如用户ID、商品ID等。对于低基数列（如性别、状态等），由于每个数据块几乎都会包含所有取值，因此BloomFilter索引的加速效果有限。
2. 数据类型限制：
  目前Doris不支持对Tinyint、Float、Double类型的列创建BloomFilter索引。
3. 性能权衡：
  虽然BloomFilter索引能够提升查询性能，但也会增加写入和更新的开销。因此，在创建索引时需要权衡性能提升和存储开销之间的平衡。
4. 查询优化：
  在查询时，Doris会自动选择是否使用BloomFilter索引。如果希望查看某个查询是否命中了BloomFilter索引，可以通过查询的Profile信息来确认。

Bitmap索引

（该索引适用于1.2版本）

bitmap index：位图索引，是一种快速数据结构，能够加快查询速度
创建索引：

bash

1	CREATE INDEX [IF NOT EXISTS] index_name ON table1 (siteid) USING BITMAP COMMENT 'balabala';

查看索引：

bash

1	SHOW INDEX FROM example_db.table_name;

删除索引：

bash

1	DROP INDEX [IF EXISTS] index_name ON [db_name.]table_name;

倒排索引

（2.0版本以上适用）

可用于进行文本类型的全文索引，以及普通数值日期类型的等值范围查询
建表的时候建索引：

bash

CREATE TABLE table_name
(
  column_name1 TYPE1,
  column_name2 TYPE2,
  column_name3 TYPE3,
  INDEX idx_name1(column_name1) USING INVERTED [PROPERTIES(...)] [COMMENT 'your comment'],
  INDEX idx_name2(column_name2) USING INVERTED [PROPERTIES(...)] [COMMENT 'your comment']
)
table_properties;

idx_column_name(column_name) 是必须的，column_name 是建索引的列名，必须是前面列定义中出现过的，idx_column_name 是索引名字，必须表级别唯一，建议命名规范：列名前面加前缀 idx_
USING INVERTED 是必须的，用于指定索引类型是倒排索引
PROPERTIES 是可选的，用于指定倒排索引的额外属性，目前支持的属性如下：

parser 指定分词器

默认不指定代表不分词
english 是英文分词，适合被索引列是英文的情况，用空格和标点符号分词，性能高
chinese 是中文分词，适合被索引列主要是中文的情况，性能比 English 分词低
unicode 是多语言混合类型分词，适用于中英文混合、多语言混合的情况。它能够对邮箱前缀和后缀、IP 地址以及字符数字混合进行分词，并且可以对中文按字符分词。

parser_mode

用于指定分词的模式，目前 parser = chinese 时支持如下几种模式：
fine_grained：细粒度模式，倾向于分出比较短、较多的词，比如 '武汉市长江大桥' 会分成 '武汉', '武汉市', '市长', '长江', '长江大桥', '大桥' 6 个词
coarse_grained：粗粒度模式，倾向于分出比较长、较少的词，，比如 '武汉市长江大桥' 会分成 '武汉市' '长江大桥' 2 个词
默认 coarse_grained

support_phrase

用于指定索引是否支持 MATCH_PHRASE 短语查询加速
true 为支持，但是索引需要更多的存储空间
false 为不支持，更省存储空间，可以用 MATCH_ALL 查询多个关键字
默认 false

例如下面的例子指定中文分词，粗粒度模式，支持短语查询加速

INDEX idx_name(column_name) USING INVERTED PROPERTIES("parser" = "chinese", "parser_mode" = "coarse_grained", "support_phrase" = "true")

char_filter

用于指定在分词前对文本进行预处理，通常用于影响分词行为
char_filter_type：指定使用不同功能的 char_filter（目前仅支持 char_replace）
char_replace 将 pattern 中每个 char 替换为一个 replacement 中的 char

char_filter_pattern：需要被替换掉的字符数
char_filter_replacement：替换后的字符数组，可以不用配置，默认为一个空格字符

例如下面的例子将点和下划线替换成空格，达到将点和下划线作为单词分隔符的目的，影响分词行为。

INDEX idx_name(column_name) USING INVERTED PROPERTIES("parser" = "unicode", "char_filter_type" = "char_replace", "char_filter_pattern" = "._", "char_filter_replacement" = " ")

ignore_above

用于指定不分词字符串索引（没有指定parser）的长度限制
长度超过 ignore_above 设置的字符串不会被索引。对于字符串数组，ignore_above 将分别应用于每个数组元素，长度超过 ignore_above 的字符串元素将不被索引。
默认为 256，单位是字节

lower_case

是否将分词进行小写转换，从而在匹配的时候实现忽略大小写
true: 转换小写
false：不转换小写
从 2.1.2 版本开始默认为 true，自动转小写，之前的版本默认为 false

+ 添加倒排索引

sql

-- 语法 1
CREATE INDEX idx_name ON table_name(column_name) USING INVERTED [PROPERTIES(...)] [COMMENT 'your comment'];
-- 语法 2
ALTER TABLE table_name ADD INDEX idx_name(column_name) USING INVERTED [PROPERTIES(...)] [COMMENT 'your comment'];

构建倒排索引

sql

-- 语法 1，默认给全表的所有分区 BUILD INDEX
BUILD INDEX index_name ON table_name;
-- 语法 2，可指定 Partition，可指定一个或多个
BUILD INDEX index_name ON table_name PARTITIONS(partition_name1, partition_name2);

查看构建速度

sql

SHOW BUILD INDEX [FROM db_name];
-- 示例 1，查看所有的 BUILD INDEX 任务进展
SHOW BUILD INDEX;
-- 示例 2，查看指定 table 的 BUILD INDEX 任务进展
SHOW BUILD INDEX where TableName = "table1";

删除倒排索引

sql

-- 语法 1
DROP INDEX idx_name ON table_name;
-- 语法 2
ALTER TABLE table_name DROP INDEX idx_name;

加速查询的例子

sql

-- 1. 全文检索关键词匹配，通过 MATCH_ANY MATCH_ALL 完成
SELECT * FROM table_name WHERE column_name MATCH_ANY | MATCH_ALL 'keyword1 ...';

-- 1.1 content 列中包含 keyword1 的行
SELECT * FROM table_name WHERE content MATCH_ANY 'keyword1';

-- 1.2 content 列中包含 keyword1 或者 keyword2 的行，后面还可以添加多个 keyword
SELECT * FROM table_name WHERE content MATCH_ANY 'keyword1 keyword2';

-- 1.3 content 列中同时包含 keyword1 和 keyword2 的行，后面还可以添加多个 keyword
SELECT * FROM table_name WHERE content MATCH_ALL 'keyword1 keyword2';


-- 2. 全文检索短语匹配，通过 MATCH_PHRASE 完成
-- 2.1 content 列中同时包含 keyword1 和 keyword2 的行，而且 keyword2 必须紧跟在 keyword1 后面
-- 'keyword1 keyword2'，'wordx keyword1 keyword2'，'wordx keyword1 keyword2 wordy' 能匹配，因为他们都包含keyword1 keyword2，而且keyword2 紧跟在 keyword1 后面
-- 'keyword1 wordx keyword2' 不能匹配，因为 keyword1 keyword2 之间隔了一个词 wordx
-- 'keyword2 keyword1'，因为 keyword1 keyword2 的顺序反了
SELECT * FROM table_name WHERE content MATCH_PHRASE 'keyword1 keyword2';

-- 2.2 content 列中同时包含 keyword1 和 keyword2 的行，而且 keyword1 keyword2 的 `词距`（slop） 不超过3
-- 'keyword1 keyword2', 'keyword1 a keyword2', 'keyword1 a b c keyword2' 都能匹配，因为keyword1 keyword2中间隔的词分别是0 1 3 都不超过3
-- 'keyword1 a b c d keyword2' 不能能匹配，因为keyword1 keyword2中间隔的词有4个，超过3
-- 'keyword2 keyword1', 'keyword2 a keyword1', 'keyword2 a b c keyword1' 也能匹配，因为指定 slop > 0 时不再要求keyword1 keyword2 的顺序。这个行为参考了 ES，与直觉的预期不一样，因此 Doris 提供了在 slop 后面指定正数符号（+）表示需要保持 keyword1 keyword2 的先后顺序
SELECT * FROM table_name WHERE content MATCH_PHRASE 'keyword1 keyword2 ~3';
-- slop 指定正号，'keyword1 a b c keyword2' 能匹配，而 'keyword2 a b c keyword1' 不能匹配
SELECT * FROM table_name WHERE content MATCH_PHRASE 'keyword1 keyword2 ~3+';

-- 2.3 在保持词顺序的前提下，对最后一个词keyword2做前缀匹配，默认找50个前缀词（session变量inverted_index_max_expansions控制）
-- 'keyword1 keyword2abc' 能匹配，因为keyword1完全一样，最后一个 keyword2abc 是 keyword2 的前缀
-- 'keyword1 keyword2' 也能匹配，因为 keyword2 也是 keyword2 的前缀
-- 'keyword1 keyword3' 不能匹配，因为 keyword3 不是 keyword2 的前缀
-- 'keyword1 keyword3abc' 也不能匹配，因为 keyword3abc 也不是 keyword2 的前缀
SELECT * FROM table_name WHERE content MATCH_PHRASE_PREFIX 'keyword1 keyword2';

-- 2.4 如果只填一个词会退化为前缀查询，默认找50个前缀词（session变量inverted_index_max_expansions控制）
SELECT * FROM table_name WHERE content MATCH_PHRASE_PREFIX 'keyword1';

-- 2.5 对分词后的词进行正则匹配，默认匹配50个（session变量inverted_index_max_expansions控制）
-- 类似 MATCH_PHRASE_PREFIX 的匹配规则，只是前缀变成了正则
SELECT * FROM table_name WHERE content MATCH_REGEXP 'key*';

-- 3. 普通等值、范围、IN、NOT IN，正常的 SQL 语句即可，例如
SELECT * FROM table_name WHERE id = 123;
SELECT * FROM table_name WHERE ts > '2023-01-01 00:00:00';
SELECT * FROM table_name WHERE op_type IN ('add', 'delete');