倒排索引

索引原理

倒排索引，是信息检索领域常用的索引技术，将文本分成一个个词，构建 词 -> 文档编号 的索引，可以快速查找一个词在哪些文档出现。

从 2.0.0 版本开始，PALO 支持倒排索引，可以用来进行文本类型的全文检索、普通数值日期类型的等值范围查询，快速从海量数据中过滤出满足条件的行。

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

与 PALO 中其他索引不同的是，在存储层倒排索引使用独立的文件，跟数据文件一一对应、但物理存储上文件相互独立。这样的好处是可以做到创建、删除索引不用重写数据文件，大幅降低处理开销。

使用场景

倒排索引的使用范围很广泛，可以加速等值、范围、全文检索（关键词匹配、短语系列匹配等）。一个表可以有多个倒排索引，查询时多个倒排索引的条件可以任意组合。

倒排索引的功能简要介绍如下：

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

• 支持关键词检索，包括同时匹配多个关键字 MATCH_ALL、匹配任意一个关键字 MATCH_ANY

• 支持短语查询 MATCH_PHRASE

  • 支持指定词距 slop
  • 支持短语 + 前缀 MATCH_PHRASE_PREFIX
• 支持分词正则查询 MATCH_REGEXP
• 支持英文、中文以及 Unicode 多种分词

2. 加速普通等值、范围查询，覆盖原来 BITMAP 索引的功能，代替 BITMAP 索引

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

3. 支持完善的逻辑组合

• 不仅支持 AND 条件加速，还支持 OR NOT 条件加速
• 支持多个条件的任意 AND OR NOT 逻辑组合

4. 灵活高效的索引管理

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

提示：

倒排索引的使用有下面一些限制：

1. 存在精度问题的浮点数类型 FLOAT 和 DOUBLE 不支持倒排索引，原因是浮点数精度不准确。解决方案是使用精度准确的定点数类型 DECIMAL，DECIMAL 支持倒排索引。
2. 部分复杂数据类型还不支持倒排索引，包括：MAP、STRUCT、JSON、HLL、BITMAP、QUANTILE_STATE、AGG_STATE。其中 MAP、STRUCT 会逐步支持，JSON 类型可以换成 VARIANT 类型获得支持。其他几个类型因为其特殊用途暂不需要支持倒排索引。
3. DUPLICATE 和 开启 Merge-on-Write 的 UNIQUE 表模型支持任意列建倒排索引。但是 AGGREGATE 和 未开启 Merge-on-Write 的 UNIQUE 模型仅支持 Key 列建倒排索引，非 Key 列不能建倒排索引，这是因为这两个模型需要读取所有数据后做合并，因此不能利用索引做提前过滤。

管理索引

建表时定义倒排索引

在建表语句中 COLUMN 的定义之后是索引定义：

1CREATE TABLE table_name
2(
3  column_name1 TYPE1,
4  column_name2 TYPE2,
5  column_name3 TYPE3,
6  INDEX idx_name1(column_name1) USING INVERTED [PROPERTIES(...)] [COMMENT 'your comment'],
7  INDEX idx_name2(column_name2) USING INVERTED [PROPERTIES(...)] [COMMENT 'your comment']
8)
9table_properties;

语法说明如下：

1. idxcolumn_name(column_name) 是必须的，column_name 是建索引的列名，必须是前面列定义中出现过的，idx_column_name 是索引名字，必须表级别唯一，建议命名规范：列名前面加前缀 idx
2. USING INVERTED 是必须的，用于指定索引类型是倒排索引
3. PROPERTIES 是可选的，用于指定倒排索引的额外属性，目前支持的属性如下：

1   INDEX idx_name(column_name) USING INVERTED PROPERTIES("parser" = "chinese", "parser_mode" = "coarse_grained", "support_phrase" = "true")

1   INDEX idx_name(column_name) USING INVERTED PROPERTIES("parser" = "unicode", "char_filter_type" = "char_replace", "char_filter_pattern" = "._", "char_filter_replacement" = " ")

4. COMMENT 是可选的，用于指定索引注释

已有表增加倒排索引

1. ADD INDEX

支持CREATE INDEX 和 ALTER TABLE ADD INDEX 两种语法，参数跟建表时索引定义相同

1-- 语法 1
2CREATE INDEX idx_name ON table_name(column_name) USING INVERTED [PROPERTIES(...)] [COMMENT 'your comment'];
3-- 语法 2
4ALTER TABLE table_name ADD INDEX idx_name(column_name) USING INVERTED [PROPERTIES(...)] [COMMENT 'your comment'];

2. BUILD INDEX

CREATE / ADD INDEX 操作只是新增了索引定义，这个操作之后的新写入数据会生成倒排索引，而存量数据需要使用 BUILD INDEX 触发：

1-- 语法 1，默认给全表的所有分区 BUILD INDEX
2BUILD INDEX index_name ON table_name;
3-- 语法 2，可指定 Partition，可指定一个或多个
4BUILD INDEX index_name ON table_name PARTITIONS(partition_name1, partition_name2);

通过 SHOW BUILD INDEX 查看 BUILD INDEX 进度：

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

通过 CANCEL BUILD INDEX 取消 BUILD INDEX：

1CANCEL BUILD INDEX ON table_name;
2CANCEL BUILD INDEX ON table_name (job_id1,jobid_2,...);

提示：

BUILD INDEX 会生成一个异步任务执行，在每个 BE 上有多个线程执行索引构建任务，通过 BE 参数 alter_index_worker_count 可以设置，默认值是 3。

2.0.12 和 2.1.4 之前的版本 BUILD INDEX 会一直重试直到成功，从这两个版本开始通过失败和超时机制避免一直重试。3.0 存算分离模式暂不支持此命令。

1. 一个 tablet 的多数副本 BUILD INDEX 失败后，整个 BUILD INDEX 失败结束
2. 时间超过 alter_table_timeout_second ()，BUILD INDEX 超时结束
3. 用户可以多次触发 BUILD INDEX，已经 BUILD 成功的索引不会重复 BUILD

已有表删除倒排索引

1-- 语法 1
2DROP INDEX idx_name ON table_name;
3-- 语法 2
4ALTER TABLE table_name DROP INDEX idx_name;

提示：

DROP INDEX 会删除索引定义，新写入数据不会再写索引，同时会生成一个异步任务执行索引删除操作，在每个 BE 上有多个线程执行索引删除任务，通过 BE 参数 alter_index_worker_count 可以设置，默认值是 3。

查看倒排索引

1-- 语法 1，表的 schema 中 INDEX 部分 USING INVERTED 是倒排索引
2SHOW CREATE TABLE table_name;
3
4-- 语法 2，IndexType 为 INVERTED 的是倒排索引
5SHOW INDEX FROM idx_name;

使用索引

利用倒排索引加速查询

1-- 1. 全文检索关键词匹配，通过 MATCH_ANY MATCH_ALL 完成
2SELECT * FROM table_name WHERE column_name MATCH_ANY | MATCH_ALL 'keyword1 ...';
3
4-- 1.1 content 列中包含 keyword1 的行
5SELECT * FROM table_name WHERE content MATCH_ANY 'keyword1';
6
7-- 1.2 content 列中包含 keyword1 或者 keyword2 的行，后面还可以添加多个 keyword
8SELECT * FROM table_name WHERE content MATCH_ANY 'keyword1 keyword2';
9
10-- 1.3 content 列中同时包含 keyword1 和 keyword2 的行，后面还可以添加多个 keyword
11SELECT * FROM table_name WHERE content MATCH_ALL 'keyword1 keyword2';
12
13
14-- 2. 全文检索短语匹配，通过 MATCH_PHRASE 完成
15-- 2.1 content 列中同时包含 keyword1 和 keyword2 的行，而且 keyword2 必须紧跟在 keyword1 后面
16-- 'keyword1 keyword2'，'wordx keyword1 keyword2'，'wordx keyword1 keyword2 wordy' 能匹配，因为他们都包含 keyword1 keyword2，而且 keyword2 紧跟在 keyword1 后面
17-- 'keyword1 wordx keyword2' 不能匹配，因为 keyword1 keyword2 之间隔了一个词 wordx
18-- 'keyword2 keyword1'，因为 keyword1 keyword2 的顺序反了
19-- 使用 MATCH_PHRASE 需要再 PROPERTIES 中开启 "support_phrase" = "true"
20SELECT * FROM table_name WHERE content MATCH_PHRASE 'keyword1 keyword2';
21
22-- 2.2 content 列中同时包含 keyword1 和 keyword2 的行，而且 keyword1 keyword2 的 `词距`（slop）不超过 3
23-- 'keyword1 keyword2', 'keyword1 a keyword2', 'keyword1 a b c keyword2' 都能匹配，因为 keyword1 keyword2 中间隔的词分别是 0 1 3 都不超过 3
24-- 'keyword1 a b c d keyword2' 不能能匹配，因为 keyword1 keyword2 中间隔的词有 4 个，超过 3
25-- 'keyword2 keyword1', 'keyword2 a keyword1', 'keyword2 a b c keyword1' 也能匹配，因为指定 slop > 0 时不再要求 keyword1 keyword2 的顺序。这个行为参考了 ES，与直觉的预期不一样，因此 PALO 提供了在 slop 后面指定正数符号（+）表示需要保持 keyword1 keyword2 的先后顺序
26SELECT * FROM table_name WHERE content MATCH_PHRASE 'keyword1 keyword2 ~3';
27-- slop 指定正号，'keyword1 a b c keyword2' 能匹配，而 'keyword2 a b c keyword1' 不能匹配
28SELECT * FROM table_name WHERE content MATCH_PHRASE 'keyword1 keyword2 ~3+';
29
30-- 2.3 在保持词顺序的前提下，对最后一个词 keyword2 做前缀匹配，默认找 50 个前缀词（session 变量 inverted_index_max_expansions 控制）
31-- 需要保证 keyword1, keyword2 在原文分词后也是相邻的，不能中间有其他词
32-- 'keyword1 keyword2abc' 能匹配，因为 keyword1 完全一样，最后一个 keyword2abc 是 keyword2 的前缀
33-- 'keyword1 keyword2' 也能匹配，因为 keyword2 也是 keyword2 的前缀
34-- 'keyword1 keyword3' 不能匹配，因为 keyword3 不是 keyword2 的前缀
35-- 'keyword1 keyword3abc' 也不能匹配，因为 keyword3abc 也不是 keyword2 的前缀
36SELECT * FROM table_name WHERE content MATCH_PHRASE_PREFIX 'keyword1 keyword2';
37
38-- 2.4 如果只填一个词会退化为前缀查询，默认找 50 个前缀词（session 变量 inverted_index_max_expansions 控制）
39SELECT * FROM table_name WHERE content MATCH_PHRASE_PREFIX 'keyword1';
40
41-- 2.5 对分词后的词进行正则匹配，默认匹配 50 个（session 变量 inverted_index_max_expansions 控制）
42-- 类似 MATCH_PHRASE_PREFIX 的匹配规则，只是前缀变成了正则
43SELECT * FROM table_name WHERE content MATCH_REGEXP 'key.*';
44
45-- 3. 普通等值、范围、IN、NOT IN，正常的 SQL 语句即可，例如
46SELECT * FROM table_name WHERE id = 123;
47SELECT * FROM table_name WHERE ts > '2023-01-01 00:00:00';
48SELECT * FROM table_name WHERE op_type IN ('add', 'delete');
49
50-- 4. 多列全文检索匹配，通过 multi_match 函数完成
51-- 参数说明：
52--   前N个参数是要匹配的列名
53--   倒数第二个参数指定匹配模式：'any'/'all'/'phrase'/'phrase_prefix'
54--   最后一个参数是要搜索的关键词或短语
55
56-- 4.1 在col1,col2,col3任意一列中包含'keyword1'的行（OR逻辑）
57SELECT * FROM table_name WHERE multi_match(col1, col2, col3, 'any', 'keyword1');
58
59-- 4.2 在col1,col2,col3所有列中都包含'keyword1'的行（AND逻辑）
60SELECT * FROM table_name WHERE multi_match(col1, col2, col3, 'all', 'keyword1');
61
62-- 4.3 在col1,col2,col3任意一列中包含完整短语'keyword1'的行（精确短语匹配）
63SELECT * FROM table_name WHERE multi_match(col1, col2, col3, 'phrase', 'keyword1');
64
65-- 4.4 在col1,col2,col3任意一列中包含以'keyword1'开头的短语的行（短语前缀匹配）
66-- 例如会匹配"keyword123"这样的内容
67SELECT * FROM table_name WHERE multi_match(col1, col2, col3, 'phrase_prefix', 'keyword1');

通过 profile 分析索引加速效果

倒排查询加速可以通过 session 变量 enable_inverted_index_query 开关，默认是 true 打开，有时为了验证索引加速效果可以设置为 false 关闭。

可以通过 Query Profile 中的下面几个指标分析倒排索引的加速效果。

• RowsInvertedIndexFiltered 倒排过滤掉的行数，可以与其他几个 Rows 值对比分析索引过滤效果

• InvertedIndexFilterTime 倒排索引消耗的时间

  • InvertedIndexSearcherOpenTime 倒排索引打开索引的时间
  • InvertedIndexSearcherSearchTime 倒排索引内部查询的时间

用分词函数验证分词效果

如果想检查分词实际效果或者对一段文本进行分词行为，可以使用 TOKENIZE 函数进行验证。

TOKENIZE 函数的第一个参数是待分词的文本，第二个参数是创建索引指定的分词参数。

1SELECT TOKENIZE('武汉长江大桥','"parser"="chinese","parser_mode"="fine_grained"');
2+-----------------------------------------------------------------------------------+
3| tokenize('武汉长江大桥', '"parser"="chinese","parser_mode"="fine_grained"')       |
4+-----------------------------------------------------------------------------------+
5| ["武汉", "武汉长江大桥", "长江", "长江大桥", "大桥"]                              |
6+-----------------------------------------------------------------------------------+
7
8
9SELECT TOKENIZE('武汉市长江大桥','"parser"="chinese","parser_mode"="fine_grained"');
10+--------------------------------------------------------------------------------------+
11| tokenize('武汉市长江大桥', '"parser"="chinese","parser_mode"="fine_grained"')        |
12+--------------------------------------------------------------------------------------+
13| ["武汉", "武汉市", "市长", "长江", "长江大桥", "大桥"]                               |
14+--------------------------------------------------------------------------------------+
15
16
17SELECT TOKENIZE('武汉市长江大桥','"parser"="chinese","parser_mode"="coarse_grained"');
18+----------------------------------------------------------------------------------------+
19| tokenize('武汉市长江大桥', '"parser"="chinese","parser_mode"="coarse_grained"')        |
20+----------------------------------------------------------------------------------------+
21| ["武汉市", "长江大桥"]                                                                 |
22+----------------------------------------------------------------------------------------+
23
24
25SELECT TOKENIZE('I love PALO','"parser"="english"');
26+------------------------------------------------+
27| tokenize('I love PALO', '"parser"="english"') |
28+------------------------------------------------+
29| ["i", "love", "PALO"]                         |
30+------------------------------------------------+
31
32
33SELECT TOKENIZE('I love CHINA 我爱我的祖国','"parser"="unicode"');
34+-------------------------------------------------------------------+
35| tokenize('I love CHINA 我爱我的祖国', '"parser"="unicode"')       |
36+-------------------------------------------------------------------+
37| ["i", "love", "china", "我", "爱", "我", "的", "祖", "国"]        |
38+-------------------------------------------------------------------+

使用示例

用 HackerNews 100 万条数据展示倒排索引的创建、全文检索、普通查询，包括跟无索引的查询性能进行简单对比。

建表

1CREATE DATABASE test_inverted_index;
2
3USE test_inverted_index;
4
5-- 创建表的同时创建了 comment 的倒排索引 idx_comment
6--   USING INVERTED 指定索引类型是倒排索引
7--   PROPERTIES("parser" = "english") 指定采用 "english" 分词，还支持 "chinese" 中文分词和 "unicode" 中英文多语言混合分词，如果不指定 "parser" 参数表示不分词
8
9CREATE TABLE hackernews_1m
10(
11    `id` BIGINT,
12    `deleted` TINYINT,
13    `type` String,
14    `author` String,
15    `timestamp` DateTimeV2,
16    `comment` String,
17    `dead` TINYINT,
18    `parent` BIGINT,
19    `poll` BIGINT,
20    `children` Array<BIGINT>,
21    `url` String,
22    `score` INT,
23    `title` String,
24    `parts` Array<INT>,
25    `descendants` INT,
26    INDEX idx_comment (`comment`) USING INVERTED PROPERTIES("parser" = "english") COMMENT 'inverted index for comment'
27)
28DUPLICATE KEY(`id`)
29DISTRIBUTED BY HASH(`id`) BUCKETS 10
30PROPERTIES ("replication_num" = "1");

导入数据

通过 Stream Load 导入数据

1wget https://qa-build.oss-cn-beijing.aliyuncs.com/regression/index/hacknernews_1m.csv.gz
2
3curl --location-trusted -u root: -H "compress_type:gz" -T hacknernews_1m.csv.gz  http://127.0.0.1:8030/api/test_inverted_index/hackernews_1m/_stream_load
4{
5    "TxnId": 2,
6    "Label": "a8a3e802-2329-49e8-912b-04c800a461a6",
7    "TwoPhaseCommit": "false",
8    "Status": "Success",
9    "Message": "OK",
10    "NumberTotalRows": 1000000,
11    "NumberLoadedRows": 1000000,
12    "NumberFilteredRows": 0,
13    "NumberUnselectedRows": 0,
14    "LoadBytes": 130618406,
15    "LoadTimeMs": 8988,
16    "BeginTxnTimeMs": 23,
17    "StreamLoadPutTimeMs": 113,
18    "ReadDataTimeMs": 4788,
19    "WriteDataTimeMs": 8811,
20    "CommitAndPublishTimeMs": 38
21}

SQL 运行 count() 确认导入数据成功

1SELECT count() FROM hackernews_1m;
2+---------+
3| count() |
4+---------+
5| 1000000 |
6+---------+

查询

01 全文检索

• 用 LIKE 匹配计算 comment 中含有 'OLAP' 的行数，耗时 0.18s

  SQL 复制

  1SELECT count() FROM hackernews_1m WHERE comment LIKE '%OLAP%';
  2+---------+
  3| count() |
  4+---------+
  5|      34 |
  6+---------+

• 用基于倒排索引的全文检索 MATCH_ANY 计算 comment 中含有'OLAP'的行数，耗时 0.02s，加速 9 倍，在更大的数据集上效果会更加明显

  这里结果条数的差异，是因为倒排索引对 comment 分词后，还会对词进行进行统一成小写等归一化处理，因此 MATCH_ANY 比 LIKE 的结果多一些

  SQL 复制

  1SELECT count() FROM hackernews_1m WHERE comment MATCH_ANY 'OLAP';
  2+---------+
  3| count() |
  4+---------+
  5|      35 |
  6+---------+

• 同样的对比统计 'OLTP' 出现次数的性能，0.07s vs 0.01s，由于缓存的原因 LIKE 和 MATCH_ANY 都有提升，倒排索引仍然有 7 倍加速

  SQL 复制

  1SELECT count() FROM hackernews_1m WHERE comment LIKE '%OLTP%';
  2+---------+
  3| count() |
  4+---------+
  5|      48 |
  6+---------+

  SELECT count() FROM hackernews_1m WHERE comment MATCH_ANY 'OLTP'; +---------+ | count() | +---------+ | 51 | +---------+

• 同时出现 'OLAP' 和 'OLTP' 两个词，0.13s vs 0.01s，13 倍加速

  要求多个词同时出现时（AND 关系）使用 MATCH_ALL 'keyword1 keyword2 ...'

  SQL 复制

  1SELECT count() FROM hackernews_1m WHERE comment LIKE '%OLAP%' AND comment LIKE '%OLTP%';
  2+---------+
  3| count() |
  4+---------+
  5|      14 |
  6+---------+
  7
  8
  9SELECT count() FROM hackernews_1m WHERE comment MATCH_ALL 'OLAP OLTP';
  10+---------+
  11| count() |
  12+---------+
  13|      15 |
  14+---------+

• 任意出现 'OLAP' 和 'OLTP' 其中一个词，0.12s vs 0.01s，12 倍加速

  只要求多个词任意一个或多个出现时（OR 关系）使用 MATCH_ANY 'keyword1 keyword2 ...'

  SQL 复制

  1SELECT count() FROM hackernews_1m WHERE comment LIKE '%OLAP%' OR comment LIKE '%OLTP%';
  2+---------+
  3| count() |
  4+---------+
  5|      68 |
  6+---------+
  7
  8
  9SELECT count() FROM hackernews_1m WHERE comment MATCH_ANY 'OLAP OLTP';
  10+---------+
  11| count() |
  12+---------+
  13|      71 |
  14+---------+

02 普通等值、范围查询

• DataTime 类型的列范围查询

  SQL 复制

  1SELECT count() FROM hackernews_1m WHERE timestamp > '2007-08-23 04:17:00';
  2+---------+
  3| count() |
  4+---------+
  5|  999081 |
  6+---------+

• 为 timestamp 列增加一个倒排索引

  SQL 复制

  1-- 对于日期时间类型 USING INVERTED，不用指定分词
  2-- CREATE INDEX 是第一种建索引的语法，另外一种在后面展示
  3CREATE INDEX idx_timestamp ON hackernews_1m(timestamp) USING INVERTED;

  SQL 复制

  1BUILD INDEX idx_timestamp ON hackernews_1m;

• 查看索引创建进度，通过 FinishTime 和 CreateTime 的差值，可以看到 100 万条数据对 timestamp 列建倒排索引只用了 1s

  SQL 复制

  1SHOW ALTER TABLE COLUMN;
  2+-------+---------------+-------------------------+-------------------------+---------------+---------+---------------+---------------+---------------+----------+------+----------+---------+
  3| JobId | TableName     | CreateTime              | FinishTime              | IndexName     | IndexId | OriginIndexId | SchemaVersion | TransactionId | State    | Msg  | Progress | Timeout |
  4+-------+---------------+-------------------------+-------------------------+---------------+---------+---------------+---------------+---------------+----------+------+----------+---------+
  5| 10030 | hackernews_1m | 2023-02-10 19:44:12.929 | 2023-02-10 19:44:13.938 | hackernews_1m | 10031   | 10008         | 1:1994690496  | 3             | FINISHED |      | NULL     | 2592000 |
  6+-------+---------------+-------------------------+-------------------------+---------------+---------+---------------+---------------+---------------+----------+------+----------+---------+

  SQL 复制

  1-- 若 table 没有分区，PartitionName 默认就是 TableName
  2SHOW BUILD INDEX;
  3+-------+---------------+---------------+----------------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+
  4| JobId | TableName     | PartitionName | AlterInvertedIndexes                                     | CreateTime              | FinishTime              | TransactionId | State    | Msg  | Progress |
  5+-------+---------------+---------------+----------------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+
  6| 10191 | hackernews_1m | hackernews_1m | [ADD INDEX idx_timestamp (`timestamp`) USING INVERTED],  | 2023-06-26 15:32:33.894 | 2023-06-26 15:32:34.847 | 3             | FINISHED |      | NULL     |
  7+-------+---------------+---------------+----------------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+

• 索引创建后，范围查询用同样的查询方式，PALO 会自动识别索引进行优化，但是这里由于数据量小性能差别不大

  SQL 复制

  1SELECT count() FROM hackernews_1m WHERE timestamp > '2007-08-23 04:17:00';
  2+---------+
  3| count() |
  4+---------+
  5|  999081 |
  6+---------+

• 在数值类型的列 Parent 进行类似 timestamp 的操作，这里查询使用等值匹配

  SQL 复制

  1SELECT count() FROM hackernews_1m WHERE parent = 11189;
  2+---------+
  3| count() |
  4+---------+
  5|       2 |
  6+---------+

  -- 对于数值类型 USING INVERTED，不用指定分词 -- ALTER TABLE t ADD INDEX 是第二种建索引的语法 ALTER TABLE hackernews_1m ADD INDEX idx_parent(parent) USING INVERTED;

  -- 执行 BUILD INDEX 给存量数据构建倒排索引 BUILD INDEX idx_parent ON hackernews_1m;

  SHOW ALTER TABLE COLUMN; +-------+---------------+-------------------------+-------------------------+---------------+---------+---------------+---------------+---------------+----------+------+----------+---------+ | JobId | TableName | CreateTime | FinishTime | IndexName | IndexId | OriginIndexId | SchemaVersion | TransactionId | State | Msg | Progress | Timeout | +-------+---------------+-------------------------+-------------------------+---------------+---------+---------------+---------------+---------------+----------+------+----------+---------+ | 10030 | hackernews_1m | 2023-02-10 19:44:12.929 | 2023-02-10 19:44:13.938 | hackernews_1m | 10031 | 10008 | 1:1994690496 | 3 | FINISHED | | NULL | 2592000 | | 10053 | hackernews_1m | 2023-02-10 19:49:32.893 | 2023-02-10 19:49:33.982 | hackernews_1m | 10054 | 10008 | 1:378856428 | 4 | FINISHED | | NULL | 2592000 | +-------+---------------+-------------------------+-------------------------+---------------+---------+---------------+---------------+---------------+----------+------+----------+---------+

  SHOW BUILD INDEX; +-------+---------------+---------------+----------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+ | JobId | TableName | PartitionName | AlterInvertedIndexes | CreateTime | FinishTime | TransactionId | State | Msg | Progress | +-------+---------------+---------------+----------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+ | 11005 | hackernews_1m | hackernews_1m | [ADD INDEX idx_parent (parent) USING INVERTED], | 2023-06-26 16:25:10.167 | 2023-06-26 16:25:10.838 | 1002 | FINISHED | | NULL | +-------+---------------+---------------+----------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+

  SELECT count() FROM hackernews_1m WHERE parent = 11189; +---------+ | count() | +---------+ | 2 | +---------+

• 对字符串类型的 author 建立不分词的倒排索引，等值查询也可以利用索引加速

  SQL 复制

  1SELECT count() FROM hackernews_1m WHERE author = 'faster';
  2+---------+
  3| count() |
  4+---------+
  5|      20 |
  6+---------+
  7
  8
  9-- 这里只用了 USING INVERTED，不对 author 分词，整个当做一个词处理
  10ALTER TABLE hackernews_1m ADD INDEX idx_author(author) USING INVERTED;
  11
  12
  13-- 执行 BUILD INDEX 给存量数据加上倒排索引：
  14BUILD INDEX idx_author ON hackernews_1m;
  15
  16
  17-- 100 万条 author 数据增量建索引仅消耗 1.5s
  18SHOW ALTER TABLE COLUMN;
  19+-------+---------------+-------------------------+-------------------------+---------------+---------+---------------+---------------+---------------+----------+------+----------+---------+
  20| JobId | TableName     | CreateTime              | FinishTime              | IndexName     | IndexId | OriginIndexId | SchemaVersion | TransactionId | State    | Msg  | Progress | Timeout |
  21+-------+---------------+-------------------------+-------------------------+---------------+---------+---------------+---------------+---------------+----------+------+----------+---------+
  22| 10030 | hackernews_1m | 2023-02-10 19:44:12.929 | 2023-02-10 19:44:13.938 | hackernews_1m | 10031   | 10008         | 1:1994690496  | 3             | FINISHED |      | NULL     | 2592000 |
  23| 10053 | hackernews_1m | 2023-02-10 19:49:32.893 | 2023-02-10 19:49:33.982 | hackernews_1m | 10054   | 10008         | 1:378856428   | 4             | FINISHED |      | NULL     | 2592000 |
  24| 10076 | hackernews_1m | 2023-02-10 19:54:20.046 | 2023-02-10 19:54:21.521 | hackernews_1m | 10077   | 10008         | 1:1335127701  | 5             | FINISHED |      | NULL     | 2592000 |
  25+-------+---------------+-------------------------+-------------------------+---------------+---------+---------------+---------------+---------------+----------+------+----------+---------+
  26
  27SHOW BUILD INDEX order by CreateTime desc limit 1;
  28+-------+---------------+---------------+----------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+
  29| JobId | TableName     | PartitionName | AlterInvertedIndexes                               | CreateTime              | FinishTime              | TransactionId | State    | Msg  | Progress |
  30+-------+---------------+---------------+----------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+
  31| 13006 | hackernews_1m | hackernews_1m | [ADD INDEX idx_author (`author`) USING INVERTED],  | 2023-06-26 17:23:02.610 | 2023-06-26 17:23:03.755 | 3004          | FINISHED |      | NULL     |
  32+-------+---------------+---------------+----------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+
  33
  34
  35-- 创建索引后，字符串等值匹配也有明显加速
  36SELECT count() FROM hackernews_1m WHERE author = 'faster';
  37+---------+
  38| count() |
  39+---------+
  40|      20 |
  41+---------+