前缀索引
含义:根据表字段顺序的类型进行查询,作为条件时要符合表字段顺序
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)