ApsaraDB for SelectDB：倒排索引

索引原理

在雲資料庫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)