Doris索引小总结

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

内置智能索引

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

用户创建的二级索引

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

---

添加索引

BloomFilter索引

（全版本适用）

• 创建表时添加索引

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

• 修改表时添加索引

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

• 查看索引

SHOW CREATE TABLE example_db.example_bloom_index_tbl;

• 删除索引

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信息来确认。

1.

Bitmap索引

（该索引适用于1.2版本）

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

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

• 查看索引：

SHOW INDEX FROM example_db.table_name;

• 删除索引：

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

倒排索引

（2.0版本以上适用）

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

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;

1. idx_column_name(column_name) 是必须的，column_name 是建索引的列名，必须是前面列定义中出现过的，idx_column_name 是索引名字，必须表级别唯一，建议命名规范：列名前面加前缀 idx_
2. USING INVERTED 是必须的，用于指定索引类型是倒排索引
3. 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

+ 添加倒排索引

-- 语法 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'];

• 构建倒排索引

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

• 查看构建速度

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

• 删除倒排索引

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

• 加速查询的例子

-- 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');

注意事项

• 在创建索引时，需要根据实际的查询需求和数据特点来选择合适的索引类型。
• 索引虽然可以提升查询性能，但也会增加写入和更新的开销。因此，需要在性能和写入效率之间做出权衡。
• 定期监控索引的使用情况和性能表现，根据需要进行调整和优化。

总之，在Doris中添加索引是一个复杂但重要的过程，需要根据具体的场景和需求来选择合适的索引类型和创建方式。通过合理的索引设计，可以显著提升查询性能并优化数据库的整体表现。