Apache Doris 学习笔记（二）：查询优化

查询分析方法

在拿到一个查询之后，要分析该查询的效率，Doris提供了如下几种方法：

Doris explain

与mysql中的explain形似，doris可以通过explain来对一条 SQL 具体的执行方式和执行过程的描述，通过对 Explain 输出的计划进行分析，可以帮助使用者快速定位计划层面的瓶颈，从而针对不同的情况进行计划层面的调优。

Doris profile

在控制台打开如下参数后，可以在控制台查看profile结果。

set enable_profile=true; 

# 执行测试的SQL
select * from log_table where log_time>'2024-11-02'

登录Doris的控制台，

索引

前缀索引

原理

Doris的底层存储是建立在类似SST（Sorted String Table）的数据结构中，会依次按照指定的列进行排序存储，可以理解数据插入doris后，会自动整理为按照字母顺序排序的字典，而前缀索引就是字典前几页的目录页，告诉某个字母是从哪页开始。借助这个特性，Doris使用前缀索引的方式来加速查询。

管理索引

根据官方文档说明，Doris会自动根据建表时的sort keys建立前缀索引，同时，doris的前缀索引有如下约束：

建表时自动取表的 Key 的前 36 字节作为前缀索引
当遇到 VARCHAR 类型时，前缀索引会直接截断
一个表只有一组前缀索引

使用索引

前缀索引支持等值查询和范围查询：

等值查询：
SELECT * FROM table WHERE user_id = 12345;
范围查询：
SELECT * FROM table WHERE user_id BETWEEN 10000 AND 20000;
前缀匹配查询：
SELECT * FROM table WHERE user_name LIKE 'John%';
多列组合查询，且查询列顺序与排序列顺序一致：
SELECT * FROM table WHERE user_id = 12345 AND age = 30;
排序查询：
SELECT * FROM table ORDER BY user_id;
分组查询
SELECT user_id, COUNT(*) FROM table GROUP BY user_id;

我在如下的表上，执行测试SQL：

CREATE TABLE IF NOT EXISTS log_table
(
    log_time        DATETIME       NOT NULL,
    log_type        INT            NOT NULL,
    error_code      INT,
    error_msg       VARCHAR(1024),
_id           BIGINT,
    op_time         DATETIME
)
DUPLICATE KEY(log_time, log_type, error_code)
DISTRIBUTED BY HASH(log_type) BUCKETS 10
PROPERTIES (
    "replication_num" = "1"
);


INSERT INTO log_table VALUES
('2024-11-01 00:00:00', 2, 2, 'timeout', 12, '2024-11-01 01:00:00'),
('2024-11-02 00:00:00', 1, 2, 'success', 13, '2024-11-02 01:00:00'),
('2024-11-03 00:00:00', 2, 2, 'unknown', 13, '2024-11-03 01:00:00'),
('2024-11-04 00:00:00', 2, 2, 'unknown', 12, '2024-11-04 01:00:00');

INSERT INTO log_table VALUES
('2024-11-01 00:00:00', 2, 2, 'timeout', 12, '2024-11-01 01:00:00'),
('2024-11-01 00:00:00', 2, 2, 'unknown', 13, '2024-11-01 01:00:00');


# 执行如下SQL
select * from log_table where log_time>'2024-11-02';

可以通过Doris Profile的日志信息中，搜索如下关键词，发现通过rowkey过滤掉了4条数据，测试预期一致。

 RowsKeyRangeFiltered:  4

倒排索引

从Doris2.0开始，引入了类似ES的倒排索引机制，可以用来进行文本类型的全文检索、日期类型的等值范围查询。

用简单示例解释倒排索引的原理：
假设有一张log_table,只有一列error_msg:

   Row 1: error_msg = "success"
   Row 2: error_msg = "failure"
   Row 3: error_msg = "success"

对齐进行倒排索引后，对应的索引信息如下：

   "success" -> [Row 1, Row 3]
   "failure" -> [Row 2]

假如想查找所有error的日志，只需要从索引中找到对应的Row 2，精准获取这条数据即可。

当然这里只做解释用，实际比这个复杂的多，还要考虑分词规则等因素。

管理索引

新建索引

倒排索引可以在建表时一同建立，例如我们建一张如下测试表：

CREATE TABLE hackernews_1m
(
    `id` BIGINT,
    `deleted` TINYINT,
    `type` String,
    `author` String,
    `timestamp` DateTimeV2,
    `comment` String,
    `dead` TINYI
    `parent` BIGINT,
    `poll` BIGINT,
    `children` Array<BIGINT>,
    `url` String,
    `score` INT,
    `title` String,
    `parts` Array<INT>,
    `descendants` INT,
    INDEX idx_comment (`comment`) USING INVERTED PROPERTIES("parser" = "english") COMMENT 'inverted index for comment'
)
DUPLICATE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES ("replication_num" = "1");

注意，这里显示定义了倒排索引：

INDEX idx_comment (comment) USING INVERTED PROPERTIES("parser" = "english") COMMENT 'inverted index for comment'

这段话含有如下几个含义：

为comment列，定义一个idx_comment的倒排索引；
该倒排索引使用英文的分词器；
定义索引的注释

除此，还可以通过create index 和 alter 命令建立索引：

建立索引

-- 语法 1
CREATE INDEX idx_comment ON hackernews_1m(comment) USING INVERTED PROPERTIES("parser" = "english") COMMENT 'inverted index for comment';

-- 语法 2
ALTER TABLE hackernews_1m ADD INDEX idx_comment(comment) USING INVERTED PROPERTIES("parser" = "english") COMMENT 'inverted index for 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";

取消构建索引

CANCEL BUILD INDEX ON table_name;
CANCEL BUILD INDEX ON table_name (job_id1,jobid_2,...);

删除索引

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

查看索引

-- 语法 1，表的 schema 中 INDEX 部分 USING INVERTED 是倒排索引
SHOW CREATE TABLE table_name;

-- 语法 2，IndexType 为 INVERTED 的是倒排索引
SHOW INDEX FROM idx_name;

在对索引的写操作（主要是构建和删除）过程中，BE会在后台通过异步方式进行构建，并行线程数可以通过设置参数alter_index_worker_count完成。

使用索引

Doris支持如下的方式使用倒排索引：

-- 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 的顺序反了
-- 使用 MATCH_PHRASE 需要再 PROPERTIES 中开启 "support_phrase" = "true"
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, keyword2 在原文分词后也是相邻的，不能中间有其他词
-- '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');

倒排索引有效性验证：
可以通过 Query Profile 中的下面几个指标分析倒排索引的加速效果：

RowsInvertedIndexFiltered：倒排过滤掉的行数，可以与其他几个 Rows 值对比分析索引过滤效果
InvertedIndexFilterTime 倒排索引消耗的时间
InvertedIndexSearcherOpenTime 倒排索引打开索引的时间
InvertedIndexSearcherSearchTime 倒排索引内部查询的时间

布隆过滤器索引

管理索引

使用索引

参考资料

Doris使用指南-表索引：https://doris.apache.org/zh-CN/docs/3.0/table-design/index/prefix-index
Doris使用指南-加速查询：https://doris.apache.org/zh-CN/docs/3.0/query-acceleration/performance-tuning-overview/tuning-overview
Doris的倒排索引功能，好使不？：https://zhuanlan.zhihu.com/p/684441085

目录CONTENT

Apache Doris 学习笔记（二）：查询优化

查询分析方法

Doris explain

Doris profile

索引

前缀索引

原理

管理索引

使用索引

倒排索引

管理索引

新建索引

使用索引

布隆过滤器索引

管理索引

使用索引

参考资料

评论区

Apache Doris 学习笔记（二）： 查询优化

查询分析方法

Doris explain

Doris profile

索引

前缀索引

原理

管理索引

使用索引

倒排索引

管理索引

新建索引

使用索引

布隆过滤器索引

管理索引

使用索引

参考资料

评论区

Apache Doris 学习笔记（二）：查询优化