ApsaraDB for SelectDB:Inverted indexes

Implementation

In the implementation of inverted indexes in ApsaraDB for SelectDB, each row in a table corresponds to a document, and each column corresponds to a field in the document. Therefore, you can use an inverted index to quickly find rows that contain specific terms. This accelerates queries that contain the WHERE clause.

Inverted indexes are different from other indexes in ApsaraDB for SelectDB. ApsaraDB for SelectDB uses a separate file to store inverted indexes. The separate file has a logical mapping with the segment file, but the two files are independent of each other. This eliminates the need to rewrite the segment file when you update and delete inverted indexes, which significantly reduces processing overhead.

Scenarios

• You want to accelerate full-text searches for data of the STRING type.

• You want to accelerate searches for data of the STRING, NUMERIC, or DATETIME type by using the following operators: =, !=, >, >=, <, and <=.

Benefits

• Support logic combination.

  • Support index filter pushdown for the OR and NOT operators.

  • Support a combination of the AND, OR, and NOT operators.

• Support flexible and quick management of indexes.

  • Allow you to create inverted indexes when you create a table.

  • Allow you to create inverted indexes for an existing table.

  • Allow you to delete existing inverted indexes from a table.

Limits

• The FLOAT and DOUBLE data types that do not have high precision do not support inverted indexes. The DECIMAL data type has high precision and supports inverted indexes. You can use the DECIMAL type.

• Some complex data types do not support inverted indexes. Complex data types include MAP, STRUCT, JSON, HLL, BITMAP, QUANTILE_STATE, and AGG_STATE. The JSON data type can support inverted indexes after it is converted into the VARIANT data type.

• Fields of the NUMERIC type support inverted indexes, but you are not allowed to specify parser for the fields. Valid values of parser: english, chinese, and unicode.

• You can create inverted indexes for any columns in the DUPLICATE model and the UNIQUE model for which the Merge on Write (MoW) is enabled. You can create inverted indexes only for the Key column in the AGGREGATE model and the UNIQUE model for which MoW is disabled. You can create inverted indexes for other columns in the models. The models need to read all data and merge it. Therefore, you cannot use indexes to filter data in advance.

Create inverted indexes when you create a table

This operation is synchronous. If you create inverted indexes when you create a table, the table and inverted indexes are synchronously created.

Syntax

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

Parameters

Parameters that are used to create a table

index_definition_list

You can define multiple indexes in the following format when you create a table: index_definition[, index_definition][, index_definition]....

Syntax of index_definition

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

Parameters of index_definition

Example

-- Create a table and an inverted index named idx_comment on the comment column.
-- USING INVERTED specifies that the index type is inverted index.
-- PROPERTIES("parser" = "english") specifies that the English tokenizer is used to tokenize text into terms. You can also set the parser parameter to chinese or unicode. If you leave the parser parameter empty, text is not tokenized into terms.
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;

Create inverted indexes for an existing table

This operation is asynchronous. You can execute the SHOW ALTER TABLE COLUMN; statement to query the index creation progress.

Syntax

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

Parameters

The parameters are the same as those used to create inverted indexes when you create a table.

Examples

Create an inverted index that is not used to split text into terms.

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

Create an inverted index that is used to split text into terms by using the English tokenizer.

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

Query the information about inverted indexes

Query the index change progress

You can execute the ALTER or DROP statement to change an inverted index. This operation is asynchronous. You can execute the following statement to query the change progress:

SHOW ALTER TABLE COLUMN;

Query all indexes that are created for a table

Syntax

SHOW INDEXES FROM <table_name>;

Example

SHOW INDEXES FROM user_tb;

Delete inverted indexes

You can delete indexes in asynchronous mode. For more information about how to query the index deletion progress, see Query the information about inverted indexes.

Syntax

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

Example

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

Full-text searches

Syntax

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

Parameters

Examples

-- Query all rows that contain keyword1 in the logmsg column from the log_tb table. 
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1';

-- Query all rows that contain keyword1 or keyword2 in the logmsg column from the log_tb table. 
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1 keyword2';

-- Query all rows that contain keyword1 and keyword2 in the logmsg column from the log_tb table. 
SELECT * FROM log_tb WHERE logmsg MATCH_ALL 'keyword1 keyword2';

-- Query all rows that contain keyword1 and keyword2 in the logmsg column from the log_tb table and in which keyword2 follows keyword1. 
SELECT * FROM log_tb WHERE logmsg MATCH_PHRASE 'keyword1 keyword2';

Equality or range queries on data of the NUMERIC and DATE types

In these scenarios, the query syntax is the same as the standard SQL syntax.

Example

-- Perform an equality query, a range query, and a query that contains the IN or NOT IN condition.
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');

Compare query performance

In this example, the hackernews table that contains 1 million data entries is used to compare the query performance before and after inverted indexes are used.

TOKENIZE function

The TOKENIZE function can tokenize continuous text into separate terms or phrases. The TOKENIZE function is important for creating and using inverted indexes and is closely related to inverted indexes. The tokenization quality and method directly affect the quality and performance of inverted indexes.

You can also call the TOKENIZE function to query the tokenization results of continuous text. The TOKENIZE function contains the parser and parser_mode parameters. The following table describes the parameters.

Example

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

-- Fine-grained word tokenization result implemented by using the Chinese tokenizer. 
SELECT TOKENIZE('武汉长江大桥','"parser"="chinese","parser_mode"="fine_grained"');
+-----------------------------------------------------------------------------------+
| tokenize('武汉长江大桥', '"parser"="chinese","parser_mode"="fine_grained"')       |
+-----------------------------------------------------------------------------------+
| ["武汉", "武汉长江大桥", "长江", "长江大桥", "大桥"]                              |
+-----------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- Coarse-grained word tokenization result implemented by using the Chinese tokenizer. 
SELECT TOKENIZE('武汉市长江大桥','"parser"="chinese","parser_mode"="coarse_grained"');
+----------------------------------------------------------------------------------------+
| tokenize('武汉市长江大桥', '"parser"="chinese","parser_mode"="coarse_grained"')        |
+----------------------------------------------------------------------------------------+
| ["武汉市", "长江大桥"]                                                                 |
+----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- Word tokenization result of multi-language text 
SELECT TOKENIZE('I love CHINA 我爱我的祖国','"parser"="unicode"');
+-------------------------------------------------------------------+
| tokenize('I love CHINA 我爱我的祖国', '"parser"="unicode"')       |
+-------------------------------------------------------------------+
| ["i", "love", "china", "我", "爱", "我", "的", "祖", "国"]        |
+-------------------------------------------------------------------+
1 row in set (0.02 sec)