Doris索引的相关使用--留存

最新推荐文章于 2024-06-16 09:46:44 发布

不吃的土豆雷

最新推荐文章于 2024-06-16 09:46:44 发布

阅读量569

点赞数 13

分类专栏： mysql 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/wan_duzi/article/details/139068743

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

前缀索引

含义：根据表字段顺序的类型进行查询，作为条件时要符合表字段顺序

ColumnName	Type
user_id	BIGINT
age	INT
message	VARCHAR(100)
max_dwell_time	DATETIME
min_dwell_time	DATETIME

注意事项：数据块一行数据的前 36 个字节作为这行数据的前缀索引。当遇到 VARCHAR 类型时，前缀索引会直接截断。如果第一列即为 VARCHAR，那么即使没有达到 36 字节，也会直接截断。

布隆过滤器索引

含义：BloomFilter 索引可以通过建表的时候指定，或者通过表的 ALTER 操作来完成。Bloom Filter 本质上是一种位图结构，用于快速的判断一个给定的值是否在一个集合中。BloomFilter 索引也是以 Block 为粒度创建的。每个 Block 中，指定列的值作为一个集合生成一个 BloomFilter 索引条目，用于在查询时快速过滤不满足条件的数据。

BloomFilter 使用场景

首先 BloomFilter 适用于非前缀过滤。

查询会根据该列高频过滤，而且查询条件大多是 in 和 = 过滤。

不同于 Bitmap, BloomFilter 适用于高基数列。比如 UserID。因为如果创建在低基数的列上，比如“性别”列，则每个 Block 几乎都会包含所有取值，导致 BloomFilter 索引失去意义。

BloomFilter 的注意事项

不支持对 Tinyint、Float、Double 类型的列建 Bloom Filter 索引。

Bloom Filter 索引只对 in 和 = 过滤查询有加速效果。

如果要查看某个查询是否命中了 Bloom Filter 索引，可以通过查询的 Profile 信息查看。

索引的使用

建表时创建索引

CREATE TABLE IF NOT EXISTS sale_detail_bloom  (
    sale_date date NOT NULL COMMENT "销售时间",
    customer_id int NOT NULL COMMENT "客户编号",
    saler_id int NOT NULL COMMENT "销售员",
    sku_id int NOT NULL COMMENT "商品编号",
    category_id int NOT NULL COMMENT "商品分类",
    sale_count int NOT NULL COMMENT "销售数量",
    sale_price DECIMAL(12,2) NOT NULL COMMENT "单价",
    sale_amt DECIMAL(20,2)  COMMENT "销售总金额"
)
Duplicate  KEY(sale_date, customer_id,saler_id,sku_id,category_id)
DISTRIBUTED BY HASH(saler_id) BUCKETS 10
PROPERTIES (
"replication_num" = "1",
"bloom_filter_columns"="saler_id,category_id"
);

查看 BloomFilter 索引

SHOW CREATE TABLE table_name;

删除 BloomFilter 索引

ALTER TABLE table_name SET ("bloom_filter_columns" = "");

修改 BloomFilter 索引

ALTER TABLE table_name SET ("bloom_filter_columns" = "k1,k3");

N-Gram 索引

含义：为了提高 like 查询的效率，实现了多元组布隆过滤器索引(N-Gram BloomFilter 索引从 Doris 2.0 版本开始支持)

N-Gram 索引的使用场景

NGram BloomFilter 只支持字符串列

NGram BloomFilter 索引和 BloomFilter 索引为互斥关系，即同一个列只能设置两者中的一个

NGram 大小和 BloomFilter 的字节数，可以根据实际情况调优，如果 NGram 比较小，可以适当增加 BloomFilter 大小

如果要查看某个查询是否命中了 NGram BloomFilter 索引，可以通过查询的 Profile 信息查看

索引的使用

建表时创建索引

CREATE TABLE table_ngrambf (
  `siteid` int(11) NULL DEFAULT "10" COMMENT "",
  `citycode` smallint(6) NULL COMMENT "",
  `username` varchar(32) NULL DEFAULT "" COMMENT "",
  INDEX idx_ngrambf (`username`) USING NGRAM_BF PROPERTIES("gram_size"="3", "bf_size"="256") COMMENT 'username ngram_bf index'
) ENGINE=OLAP
AGGREGATE KEY(`siteid`, `citycode`, `username`) COMMENT "OLAP"
DISTRIBUTED BY HASH(`siteid`) BUCKETS 10
PROPERTIES (
"replication_num" = "1"
);

查看 NGram BloomFilter 索引

show index from table_ngrambf;

删除 NGram BloomFilter 索引

alter table table_ngrambf drop index idx_ngrambf;

修改 NGram BloomFilter 索引

alter table table_ngrambf add index idx_ngrambf(username) using NGRAM_BF PROPERTIES("gram_size"="2", "bf_size"="512")comment 'username ngram_bf index'

位图索引

含义：用位图表示的索引，对列的每个键值建立一个位图，相对于其它索引，占用的存储空间非常小，创建和使用非常快，缺点是修改操作锁粒度大，不适合频繁更新。

官方位图（看不懂一点儿）：

位图索引的使用场景

建在值重复度高的列上，建议在 100 到 100,000 之间，如：职业、地市等。重复度过高则对比其他类型索引没有明显优势；重复度过低，则空间效率和性能会大大降低。

特定类型的查询例如 count、or、and 等逻辑操作只需要进行位运算。如：通过多个条件组合查询，select count(*) from table where city = ’南京市’and job = ’医生’ and phonetype = ‘iphone’and gender =’男’。类似这种场景，如果在每个查询条件列上都建立了 Bitmap 索引，则数据库可以进行高效的 bit 运算，精确定位到需要的数据，减少磁盘 IO，并且筛选出的结果集越小，Bitmap 索引的优势越明显。

适用于即席查询、多维分析等分析场景。如果有一张表有 100 列，用户会使用其中的 20 个列作为查询条件（任意使用这 20 个列上的 N 的列），在这些列上创建 20 个 Bitmap 索引，那么所有的查询都可以应用到索引。

Bitmap 索引仅在单列上创建。

Bitmap 索引能够应用在 Duplicate、Uniq 数据模型的所有列和 Aggregate模型的 Key 列上。

Bitmap 索引支持的数据类型如下：

TINYINT
SMALLINT
INT
BIGINT
CHAR
VARCHAR
DATE
DATETIME
LARGEINT
DECIMAL
BOOL

位图索引的注意事项

值重复度低的列，如：身份证号、手机号码等。

重复度过高的列，如：性别，可以建立 Bitmap 索引，但不建议单独作为查询条件使用，建议与其他条件共同过滤。

经常需要更新修改的列。

索引的使用

创建位图索引

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

查看位图索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX [IF EXISTS] index_name ON table_name;

倒排索引

含义： Doris 的倒排索引实现中，Table 的一行对应一个文档、一列对应文档中的一个字段，因此利用倒排索引可以根据关键词快速定位包含它的行，达到 WHERE 子句加速的目的。

功能介绍

1. 增加了字符串类型的全文检索

支持字符串全文检索，包括同时匹配多个关键字 MATCH_ALL、匹配任意一个关键字 MATCH_ANY、匹配短语词组 MATCH_PHRASE

支持短语查询 MATCH_PHRASE

支持短语+前缀 MATCH_PHRASE_PREFIX
支持正则查询 MATCH_REGEXP

支持字符串数组类型的全文检索

支持英文、中文以及 Unicode 多语言分词

2. 加速普通等值、范围查询，覆盖 bitmap 索引的功能，可代替 Bitmap 索引

支持字符串、数值、日期时间类型的 =, !=, >, >=, <, <= 快速过滤

支持字符串、数字、日期时间数组类型的 =, !=, >, >=, <, <=

3. 支持完善的逻辑组合

新增索引对 OR NOT 逻辑的下推

支持多个条件的任意 AND OR NOT 组合

4. 灵活、快速的索引管理

支持在创建表上定义倒排索引

支持在已有的表上增加倒排索引，而且支持增量构建倒排索引，无需重写表中的已有数据

支持删除已有表上的倒排索引，无需重写表中的已有数据

注意事项

倒排索引在不同数据模型中有不同的使用限制：

Aggregate 模型：只能为 Key 列建立倒排索引。

Unique 模型：需要开启 Merge-on-Write 特性，开启后，可以为任意列建立倒排索引。

Duplicate 模型：可以为任意列建立倒排索引。

索引的使用

建表时定义倒排索引

CREATE TABLE table_name
(
  columns_difinition,
  INDEX idx_name1(column_name1) USING INVERTED [PROPERTIES("parser" = "english|unicode|chinese")] [COMMENT 'your comment']
  INDEX idx_name2(column_name2) USING INVERTED [PROPERTIES("parser" = "english|unicode|chinese")] [COMMENT 'your comment']
  INDEX idx_name3(column_name3) USING INVERTED [PROPERTIES("parser" = "chinese", "parser_mode" = "fine_grained|coarse_grained")] [COMMENT 'your comment']
  INDEX idx_name4(column_name4) USING INVERTED [PROPERTIES("parser" = "english|unicode|chinese", "support_phrase" = "true|false")] [COMMENT 'your comment']
  INDEX idx_name5(column_name4) USING INVERTED [PROPERTIES("char_filter_type" = "char_replace", "char_filter_pattern" = "._"), "char_filter_replacement" = " "] [COMMENT 'your comment']
  INDEX idx_name5(column_name4) USING INVERTED [PROPERTIES("char_filter_type" = "char_replace", "char_filter_pattern" = "._")] [COMMENT 'your comment']
)
table_properties;

说明：

1. USING INVERTED 是必须的，用于指定索引类型是倒排索引

2. COMMENT 是可选的，用于指定注释

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

char_filter:

        功能主要在分词前对字符串提前处理

- char_filter_type：指定使用不同功能的 char_filter（目前仅支持 char_replace）

- char_replace 将 pattern 中每个 char 替换为一个 replacement 中的 char

        - char_filter_pattern：需要被替换掉的字符数

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

ignore_above:

        控制字符串是否建索引

        - 长度超过 ignore_above 设置的字符串不会被索引。对于字符串数组，ignore_above 将分别应用于每个数组元素，长度超过 ignore_above 的字符串元素将不被索引。

        - 默认为 256 字节

lower_case:

        是否将分词进行小写转换，从而在匹配的时候实现忽略大小写

        - true: 转换小写

        - false：不转换小写

已有表增加倒排索引

2.0 版本之前：

-- 语法 1
CREATE INDEX idx_name ON table_name(column_name) USING INVERTED [PROPERTIES("parser" = "english|unicode|chinese")] [COMMENT 'your comment'];
-- 语法 2
ALTER TABLE table_name ADD INDEX idx_name(column_name) USING INVERTED [PROPERTIES("parser" = "english|unicode|chinese")] [COMMENT 'your comment'];

2.0 版本（含 2.0）之后：

create/add index操作只对增量数据生成倒排索引，增加了 BUILD INDEX 的语法用于对存量数据加倒排索引（(在执行 BUILD INDEX 之前需要已经执行了以上create/add index的操作)）。

-- 语法 1，默认给全表的存量数据加上倒排索引
BUILD INDEX index_name ON table_name;
-- 语法 2，可指定 Partition，可指定一个或多个
BUILD INDEX index_name ON table_name PARTITIONS(partition_name1, partition_name2);

查看`BUILD INDEX`进展

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

取消 `BUILD INDEX`

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

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

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

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

-- 1.4 不指定slop时短语查询slop为0，keyword1 keyword2位置相邻，可以通过~指定短语查询的slop
SELECT * FROM table_name WHERE logmsg MATCH_PHRASE 'keyword1 keyword2';
SELECT * FROM table_name WHERE logmsg MATCH_PHRASE 'keyword1 keyword2 ~3';

-- 1.5 在保持词顺序的前提下，对最后一个词keyword2做前缀匹配，默认找50个前缀词（session变量inverted_index_max_expansions控制）
SELECT * FROM table_name WHERE logmsg MATCH_PHRASE_PREFIX 'keyword1 keyword2';

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

-- 1.7 对分词后的词进行正则匹配，默认匹配50个（session变量inverted_index_max_expansions控制）
SELECT * FROM table_name WHERE logmsg MATCH_REGEXP 'key*';

-- 2. 普通等值、范围、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');

分词函数

含义：如果想检查分词实际效果或者对一段文本进行分词的话，可以使用 tokenize 函数.

mysql> SELECT TOKENIZE('武汉长江大桥','"parser"="chinese","parser_mode"="fine_grained"');
+-----------------------------------------------------------------------------------+
| tokenize('武汉长江大桥', '"parser"="chinese","parser_mode"="fine_grained"')       |
+-----------------------------------------------------------------------------------+
| ["武汉", "武汉长江大桥", "长江", "长江大桥", "大桥"]                              |
+-----------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT TOKENIZE('武汉市长江大桥','"parser"="chinese","parser_mode"="fine_grained"');
+--------------------------------------------------------------------------------------+
| tokenize('武汉市长江大桥', '"parser"="chinese","parser_mode"="fine_grained"')        |
+--------------------------------------------------------------------------------------+
| ["武汉", "武汉市", "市长", "长江", "长江大桥", "大桥"]                               |
+--------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT TOKENIZE('武汉市长江大桥','"parser"="chinese","parser_mode"="coarse_grained"');
+----------------------------------------------------------------------------------------+
| tokenize('武汉市长江大桥', '"parser"="chinese","parser_mode"="coarse_grained"')        |
+----------------------------------------------------------------------------------------+
| ["武汉市", "长江大桥"]                                                                 |
+----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT TOKENIZE('I love CHINA','"parser"="english"');
+------------------------------------------------+
| tokenize('I love CHINA', '"parser"="english"') |
+------------------------------------------------+
| ["i", "love", "china"]                         |
+------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT TOKENIZE('I love CHINA 我爱我的祖国','"parser"="unicode"');
+-------------------------------------------------------------------+
| tokenize('I love CHINA 我爱我的祖国', '"parser"="unicode"')       |
+-------------------------------------------------------------------+
| ["i", "love", "china", "我", "爱", "我", "的", "祖", "国"]        |
+-------------------------------------------------------------------+
1 row in set (0.02 sec)