倒排索引

索引原理

在云数据库SelectDB版的倒排索引实现中，表的一行对应一个文档，一列对应文档中的一个字段。倒排索引可以根据关键词快速定位包含它的行，从而提高了WHERE子查询的效率。

倒排索引与普通索引的不同之处在于，它的存储层采用独立的倒排文件，与主数据文件Segment只存在逻辑对应关系，而非与主数据文件集成在一起。这使得对索引的更新和删除操作不需要重写主数据文件，从而大幅降低了处理开销。

使用场景

• 加速字符串类型的全文检索。

• 加速字符串、数值、日期时间类型的=、!=、>、>=、<、<=快速过滤。

功能优势

• 支持完善的逻辑组合。

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

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

• 灵活、快速的索引管理。

  • 支持在创建表时创建倒排索引。

  • 支持在已有的表中增加倒排索引。

  • 支持删除表中已有的倒排索引。

使用限制

• 存在精度问题的浮点数类型FLOAT和DOUBLE不支持倒排索引，原因是浮点数精度不准确。您可以使用精度准确的定点数类型DECIMAL，DECIMAL支持倒排索引。

• 部分复杂数据类型还不支持倒排索引。包括：MAP、STRUCT、JSON、HLL、BITMAP、QUANTILE_STATE、AGG_STATE。JSON类型可通过换成VARIANT类型获得支持。

• 数值类型的字段支持创建倒排索引，但不支持指定parser分词属性，包括：english、chinese、unicode。

• DUPLICATE和开启Merge-on-Write的UNIQUE表模型支持任意列建倒排索引。但是AGGREGATE和未开启Merge-on-Write的UNIQU模型仅支持Key列建倒排索引，非Key列不能建倒排索引，这是因为这两个模型需要读取所有数据后做合并，因此不能利用索引做提前过滤。

建表时创建索引

此操作为同步过程，建表成功后索引即完成创建。

语法

CREATE TABLE  [IF NOT EXISTS] [db_name.]<table_name>
(
  <column_definition_list>,
  [<index_definition_list>] 
)
table_properties;

参数说明

建表参数说明

index_definition_list说明

在创建表时定义索引，可以定义多个索引。其格式为index_definition[, index_definition][, index_definition]...。

index_definition定义

INDEX <index_name>(<column_name>) <index_type> [PROPERTIES("<key>" = "<value>")] [COMMENT '<comment>']

index_definition参数说明

建表并创建索引示例

--   创建表的同时创建了comment的倒排索引idx_comment
--   USING INVERTED 指定索引类型是倒排索引
--   PROPERTIES("parser" = "english") 指定采用english分词，还支持"chinese"中文分词和"unicode"中英文多语言混合分词，如果不指定"parser"参数表示不分词
CREATE TABLE hackernews_1m
(
    `id` BIGINT,
    `deleted` TINYINT,
    `type` String,
    `author` String,
    `timestamp` DateTimeV2,
    `comment` String,
    `dead` TINYINT,
    `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;

已有表增加索引

此操作为异步操作，可通过SHOW ALTER TABLE COLUMN;查看索引增加进度。

语法

ALTER TABLE <table_name> ADD INDEX <index_name>(<column_name>) <index_type> [PROPERTIES("<key>" = "<value>")];

参数说明

此处参数与建表时的参数相同。

示例

添加一个不进行分词的索引。

ALTER TABLE user_tb ADD INDEX index_userId(user_id) USING INVERTED ;

添加一个按照english方式进行分词的索引。

ALTER TABLE user_tb ADD INDEX index_city(city) USING INVERTED PROPERTIES("parser" = "english");

查看索引

查看索引变更进度

通过ALTER和DROP变更索引，是异步过程，可通过以下语句查看变更进度。

SHOW ALTER TABLE COLUMN;

查看表的所有索引

语法

SHOW INDEXES FROM <table_name>;

示例

SHOW INDEXES FROM user_tb;

删除索引

删除索引是异步过程，查看删除进度，请参见查看索引。

语法

-- 语法1
DROP INDEX <index_name> ON <table_name>;
-- 语法2
ALTER TABLE <table_name> DROP INDEX <index_name>;

示例

DROP INDEX index_userId ON user_tb;
ALTER TABLE user_tb DROP INDEX index_city;

全文检索

语法

SELECT * FROM <table_name> WHERE <column_name> <conditional_logic> '<keywords>';

参数说明

示例

-- 检索log_tb表中，logmsg字段包含keyword1的所有行。
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1';

-- 检索log_tb表中，logmsg字段包含keyword1或者keyword2的所有行。
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1 keyword2';

-- 检索log_tb表中，logmsg字段同时包含keyword1和keyword2的所有行。
SELECT * FROM log_tb WHERE logmsg MATCH_ALL 'keyword1 keyword2';

-- 检索log_tb表中,logmsg字段同时包含keyword1和keyword2的所有行，并且按照keyword1在前，keyword2在后的顺序。
SELECT * FROM log_tb WHERE logmsg MATCH_PHRASE 'keyword1 keyword2';

普通数值和日期类型的等值或范围查询

此种场景下，查询语法与标准查询SQL语法并无差异。

示例

-- 普通等值、范围、IN、NOT IN
SELECT * FROM user_tb WHERE id = 123;
SELECT * FROM user_tb WHERE ts > '2023-01-01 00:00:00';
SELECT * FROM user_tb WHERE op_type IN ('add', 'delete');

查询性能对比

使用hackernews100万条数据，验证使用倒排索引与无倒排索引的查询的性能对比。

分词函数

分词函数可以将一段连续的文本拆分成一个个独立的词语或短语。它是构建和使用倒排索引的核心，二者是密切相关的，分词质量和方法的选择会直接影响倒排索引的质量和性能。

如果您不了解一段连续文本的分词结果，可以使用函数TOKENIZE查看文本分词结果。TOKENIZE函数中主要包含两个参数：parser和parser_mode，两个参数的详细说明如下。

示例如下。

--英文分词效果。
SELECT TOKENIZE('I love CHINA','"parser"="english"');
+------------------------------------------------+
| tokenize('I love CHINA', '"parser"="english"') |
+------------------------------------------------+
| ["i", "love", "china"]                         |
+------------------------------------------------+
1 row in set (0.02 sec)

--中文分词器实现的细粒度分词效果。
SELECT TOKENIZE('武汉长江大桥','"parser"="chinese","parser_mode"="fine_grained"');
+-----------------------------------------------------------------------------------+
| tokenize('武汉长江大桥', '"parser"="chinese","parser_mode"="fine_grained"')       |
+-----------------------------------------------------------------------------------+
| ["武汉", "武汉长江大桥", "长江", "长江大桥", "大桥"]                              |
+-----------------------------------------------------------------------------------+
1 row in set (0.02 sec)

--中文分词器实现的粗粒度分词效果。
SELECT TOKENIZE('武汉市长江大桥','"parser"="chinese","parser_mode"="coarse_grained"');
+----------------------------------------------------------------------------------------+
| tokenize('武汉市长江大桥', '"parser"="chinese","parser_mode"="coarse_grained"')        |
+----------------------------------------------------------------------------------------+
| ["武汉市", "长江大桥"]                                                                 |
+----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

--多语言混合分词效果。
SELECT TOKENIZE('I love CHINA 我爱我的祖国','"parser"="unicode"');
+-------------------------------------------------------------------+
| tokenize('I love CHINA 我爱我的祖国', '"parser"="unicode"')       |
+-------------------------------------------------------------------+
| ["i", "love", "china", "我", "爱", "我", "的", "祖", "国"]        |
+-------------------------------------------------------------------+
1 row in set (0.02 sec)