JSON 多值索引与全文索引实践

在 AP（分析处理）类型数据库中，选择创建特殊索引时，可以参考以下实践：

JSON 多值索引

适用场景

JSON 多值索引是专门为 JSON 文档中数组字段设计的索引类型，适用于需要对多个值或属性进行查询的场景，能够显著提高查询的效率。多值索引主要适用于如下场景：

多对多关联查询：当两个实体之间存在多对多关系时，多值索引可以加速查询。例如，演员可以出演多部影片，而一部影片可能由多个演员参与。可以使用JSON数组保存影片涉及的所有演员，并利用 JSON 多值索引优化查询特定演员出演的影片。
标签和分类查询：当实体具有多个标签或分类时，可以使用多值索引加速查询。例如，一个商品可能包含多个标签属性，通过JSON数组存储商品的多个标签，可以快速查询包含某个或多个标签的商品。

JSON 多值索引则常用于加速基于 JSON 数组且 where 条件中带有以下三种谓词的查询：
更多关于 JSON 多值索引的详细信息，参见多值索引。

操作示例

下面通过一个示例说明 JSON 多值索引的应用场景。假设我们有一张用户信息表，记录用户 ID、姓名、年龄和爱好，其中爱好以 JSON 数组形式保存。一个用户可能会有多个爱好，这些爱好可以理解为用户的标签。用于存储用户信息的表结构即数据如下：

create table user_info(user_id bigint, name varchar(1024), age bigint, hobbies json);
insert into user_info values(1, "LiLei", 18, '["reading", "knitting", "hiking"]');
insert into user_info values(2, "HanMeimei", 17, '["reading", "Painting", "Swimming"]');
insert into user_info values(3, "XiaoMing", 19, '["hiking", "Camping", "Swimming"]');

在商品广告投放中，需要根据用户的爱好进行精准定位。例如，在投放登山户外设备广告之前，需要查询哪些用户有“hiking”的爱好。对应的查询语句如下：

OceanBase(root@test)>select user_id, name from user_info where JSON_CONTAINS(hobbies->'$[*]', CAST('["hiking"]' AS JSON));
+---------+----------+
| user_id | name     |
+---------+----------+
|       1 | LiLei    |
|       3 | XiaoMing |
+---------+----------+

-- 初次执行该查询时，可能需要全表扫描，效率较低
OceanBase(root@test)>explain select user_id, name from user_info where JSON_CONTAINS(hobbies->'$[*]', CAST('["hiking"]' AS JSON));
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================================                                                                                                                    |
| |ID|OPERATOR       |NAME     |EST.ROWS|EST.TIME(us)|                                                                                                                    |
| ----------------------------------------------------                                                                                                                    |
| |0 |TABLE FULL SCAN|user_info|2       |3           |                                                                                                                    |
| ====================================================                                                                                                                    |
| Outputs & filters:                                                                                                                                                      |
| -------------------------------------                                                                                                                                   |
|   0 - output([user_info.user_id], [user_info.name]), filter([JSON_CONTAINS(JSON_EXTRACT(user_info.hobbies, '$[*]'), cast('[\"hiking\"]', JSON(536870911)))]), rowset=16 |
|       access([user_info.hobbies], [user_info.user_id], [user_info.name]), partitions(p0)                                                                                |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                       |
|       range_key([user_info.__pk_increment]), range(MIN ; MAX)always true                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

从上面的查询计划可以看出，整个查询需要对全表进行扫描，且逐行对 JSON 数组进行过滤比对。JSON 本身的过滤开销也不小，当需要过滤的记录行数达到一定数量的时候，会严重影响查询的效率。此时，通过在hobbies列上创建JSON多值索引，可显著提升该查询的效率。

当前 JSON 多值索引的后建功能默认关闭，需要在sys租户下打开后建 JSON 多值索引的开关。

alter system set _enable_add_fulltext_index_to_existing_table = true;

-- 可以看到，查询计划显示需要对整个表进行扫描。为优化性能，建议在 hobbies 列上创建 JSON 多值索引：
CREATE INDEX idx1 ON user_info ( (CAST(hobbies->'$[*]' AS char(512) ARRAY)) );

-- 创建索引后，再次执行查询，性能显著提升
OceanBase(root@test)>explain select user_id, name from user_info where JSON_CONTAINS(hobbies->'$[*]', CAST('["hiking"]' AS JSON));
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ==========================================================                                                                                                   |
| |ID|OPERATOR       |NAME           |EST.ROWS|EST.TIME(us)|                                                                                                   |
| ----------------------------------------------------------                                                                                                   |
| |0 |TABLE FULL SCAN|user_info(idx1)|1       |10          |                                                                                                   |
| ==========================================================                                                                                                   |
| Outputs & filters:                                                                                                                                           |
| -------------------------------------                                                                                                                        |
|   0 - output([user_info.user_id], [user_info.name]), filter([JSON_CONTAINS(JSON_EXTRACT(user_info.hobbies, '$[*]'), cast('[\"hiking\"]', JSON(536870911)))]) |
|       access([user_info.__pk_increment], [user_info.hobbies], [user_info.user_id], [user_info.name]), partitions(p0)                                         |
|       is_index_back=true, is_global_index=false, filter_before_indexback[false],                                                                             |
|       range_key([user_info.SYS_NC_mvi_21], [user_info.__pk_increment], [user_info.__doc_id_1733716274684183]), range(hiking,MIN,MIN ; hiking,MAX,MAX)        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set (0.005 sec)

需要注意的是，JSON 多值索引会占用额外存储空间，并可能对写入性能造成影响，当对包含多值索引的 JSON 字段进行修改（如插入、更新、删除操作）时，索引也会随之更新，写入的开销会变大。因此在使用过程中需要权衡JSON 多值索引的优缺点，按需创建。

全文索引

适用场景

在涉及大量文本数据需要进行模糊检索的场景，如果通过全表扫描来对每一行数据进行模糊查询，文本较大、数据量较多的情况下性能往往不能满足要求。另外一些复杂的查询场景，如近似匹配、相关性排序等，也难以通过改写 SQL 支撑。

为了更好的支撑这些场景，全文索引应运而生，它通过预先处理文本内容，建立关键词索引，有效提升全文搜索效率。全文索引适用于多种场景，下面列举几个具体的案例：

企业内部知识库：许多大型企业都会构建自己的内部知识库系统，用来存储项目文档、会议记录、研究报告等资料。使用全文索引可以帮助员工更加快速准确地找到所需信息，提高工作效率。
在线图书馆与电子书平台：对于提供大量书籍资源供用户阅读的服务来说，全文索引是极其重要的。用户可以输入书名、作者名字甚至是书中某段文字作为关键字来进行搜索，系统基于全文索引迅速定位到符合条件的结果。
新闻门户和社交媒体网站：这类平台上每天都会产生海量的新鲜内容，包括文章、帖子、评论等。利用全文索引可以让用户按照自己关心的话题、事件或是人物名称来过滤信息流，获取最相关的内容。
法律文书检索系统：法律行业涉及到大量的文件审阅工作，如合同、判决书、法律法规条文等。一个高效的全文搜索引擎能够极大地简化律师的工作流程，让他们能够更快地找到先例、引用条款以及相关的法律依据。
医疗健康信息系统：在医疗领域，医生经常需要查阅病人的历史病例、最新的医学研究论文以及其他参考资料。借助全文索引，医护人员可以更加便捷地访问相关信息，从而做出更为准确的诊断决策。

任何涉及到大量非结构化文本数据管理和查询的应用都可以考虑采用全文索引来提升检索效率。更多关于 OceanBase 全文搜索能力的详细信息，参见全文索引。

操作示例

我们定义一个表以保存文档资料，并为文档设置全文索引。利用全文索引可快速匹配包含期望关键字的文档，并按相似性从高到低排序。

创建表并添加全文索引

CREATE TABLE Articles (
    id INT AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT,
    PRIMARY KEY (id),
    FULLTEXT ft1 (content) WITH PARSER SPACE
);

插入示例数据

INSERT INTO Articles (title, content) VALUES
('Introduction to OceanBase', 'OceanBase is an open-source relational database management system.'),
('Full-Text Search in Databases', 'Full-text search allows for searching within the text of documents stored in a database. It is particularly useful for finding specific information quickly.'),
('Advantages of Using OceanBase', 'OceanBase offers several advantages such as high performance, reliability, and ease of use. ');

查询表中所有数据

SELECT * FROM Articles;

执行结果如下：

+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | title                         | content                                                                                                                                                      |
+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | Introduction to OceanBase     | OceanBase is an open-source relational database management system.                                                                                           |
|  2 | Full-Text Search in Databases | Full-text search allows for searching within the text of documents stored in a database. It is particularly useful for finding specific information quickly. |
|  3 | Advantages of Using OceanBase | OceanBase offers several advantages such as high performance, reliability, and ease of use.                                                                  |
+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set

执行全文检索查询

SELECT id, title, content, MATCH(content) AGAINST('OceanBase database') AS score
FROM Articles
WHERE MATCH(content) AGAINST('OceanBase database');

执行结果如下：

+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| id | title                         | content                                                                                                                                                      | score               |
+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
|  1 | Introduction to OceanBase     | OceanBase is an open-source relational database management system.                                                                                           |  0.5699481865284975 |
|  3 | Advantages of Using OceanBase | OceanBase offers several advantages such as high performance, reliability, and ease of use.                                                                  |   0.240174672489083 |
|  2 | Full-Text Search in Databases | Full-text search allows for searching within the text of documents stored in a database. It is particularly useful for finding specific information quickly. | 0.20072992700729927 |
+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
3 rows in set

查看查询执行计划

EXPLAIN SELECT id, title, content, MATCH(content) AGAINST('OceanBase database') AS score
FROM Articles
WHERE MATCH(content) AGAINST('OceanBase database');

执行结果如下：

+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| ==============================================================                                                                                      |
| |ID|OPERATOR             |NAME         |EST.ROWS|EST.TIME(us)|                                                                                      |
| --------------------------------------------------------------                                                                                      |
| |0 |SORT                 |             |17      |145         |                                                                                      |
| |1 |└─TEXT RETRIEVAL SCAN|articles(ft1)|17      |138         |                                                                                      |
| ==============================================================                                                                                      |
| Outputs & filters:                                                                                                                                  |
| -------------------------------------                                                                                                               |
|   0 - output([articles.id], [articles.title], [articles.content], [MATCH(articles.content) AGAINST('OceanBase database')]), filter(nil), rowset=256 |
|       sort_keys([MATCH(articles.content) AGAINST('OceanBase database'), DESC])                                                                      |
|   1 - output([articles.id], [articles.content], [articles.title], [MATCH(articles.content) AGAINST('OceanBase database')]), filter(nil), rowset=256 |
|       access([articles.id], [articles.content], [articles.title]), partitions(p0)                                                                   |
|       is_index_back=true, is_global_index=false,                                                                                                    |
|       calc_relevance=true, match_expr(MATCH(articles.content) AGAINST('OceanBase database')),                                                       |
|       pushdown_match_filter(MATCH(articles.content) AGAINST('OceanBase database'))                                                                  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set

JSON 多值索引

适用场景

操作示例

全文索引

适用场景

操作示例

创建表并添加全文索引

插入示例数据

查询表中所有数据

执行全文检索查询

查看查询执行计划

相关文档