When creating special indexes in an AP (Analytical Processing) database, you can refer to the following practices:
JSON multi-valued index
Application scenarios
A JSON multi-valued index is a type of index specifically designed for array fields in JSON documents. It is suitable for scenarios where you need to query multiple values or attributes, significantly improving query efficiency. Multi-valued indexes are mainly applicable to the following scenarios:
Many-to-many association queries: When there is a many-to-many relationship between two entities, a multi-valued index can accelerate the query. For example, an actor can appear in multiple films, and a film may involve multiple actors. You can use a JSON array to store all actors involved in a film and leverage a JSON multi-valued index to optimize queries for finding films starring specific actors.
Tag and category queries: When an entity has multiple tags or categories, you can use a multi-valued index to speed up queries. For example, a product may contain multiple tag attributes. By storing a product's multiple tags in a JSON array, you can quickly query for products that contain one or more specific tags.
JSON multi-valued indexes are often used to accelerate queries based on JSON arrays where the WHERE condition contains any of the following three predicates:
For more detailed information about JSON multi-valued indexes, see Multi-valued indexes.
Operation example
The following example illustrates the application scenario of a JSON multi-valued index. Suppose we have a user information table that records user IDs, names, ages, and hobbies, where hobbies are stored as JSON arrays. A user may have multiple hobbies, which can be understood as the user's tags. The table structure for storing user information is as follows:
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"]');
In product advertising, precise targeting based on user hobbies is required. For example, before placing an ad for hiking outdoor equipment, you need to query which users have the "hiking" hobby. The corresponding query statement is as follows:
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 |
+---------+----------+
-- When this query is executed for the first time, a full table scan may be required, which can be inefficient.
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 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
From the above query plan, it can be seen that the entire query requires scanning the entire table and filtering and comparing JSON arrays row by row. The filtering cost of JSON itself is also significant. When the number of record rows to filter reaches a certain amount, it severely impacts query efficiency. In this case, creating a JSON multi-valued index on the hobbies column can significantly improve the efficiency of this query.
Currently, the post-create feature for JSON multi-valued indexes is disabled by default. You need to enable the switch for post-creating JSON multi-valued indexes under the sys tenant.
alter system set _enable_add_fulltext_index_to_existing_table = true;
-- As you can see, the query plan indicates that the entire table needs to be scanned. To optimize performance, it is recommended to create a JSON multi-valued index on the hobbies column:
CREATE INDEX idx1 ON user_info ( (CAST(hobbies->'$[*]' AS char(512) ARRAY)) );
-- After creating the index, re-executing the query yields a significant performance improvement.
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)
Note that a JSON multi-valued index occupies additional storage space and may affect write performance. When modifying a JSON field containing a multi-valued index (such as insert, update, or delete operations), the index is also updated, increasing the write overhead. Therefore, when using it, you need to weigh the pros and cons of a JSON multi-valued index and create it as needed.
Full-text index
Application scenarios
In scenarios involving large amounts of text data requiring fuzzy retrieval, if a full table scan is performed to perform a fuzzy query on each row of data, performance often fails to meet requirements when the text is large or the data volume is high. Other complex query scenarios, such as approximate matching and relevance sorting, are also difficult to support by rewriting SQL.
To better support these scenarios, full-text indexes emerged. By preprocessing text content and establishing keyword indexes, they effectively improve the efficiency of full-text searches. Full-text indexes are applicable to various scenarios. The following lists a few specific cases:
- Enterprise intranet knowledge bases: Many large enterprises build their own internal knowledge base systems to store project documents, meeting minutes, research reports, and other materials. Using full-text indexing helps employees find the information they need more quickly and accurately, improving work efficiency.
- Online libraries and e-book platforms: For services that provide a large number of books for users to read, full-text indexing is extremely important. Users can search by entering the book title, author name, or even a segment of text from the book as keywords, and the system quickly locates results that match the criteria based on the full-text index.
- News portals and social media websites: These platforms generate massive amounts of fresh content every day, including articles, posts, comments, etc. Utilizing full-text indexing allows users to filter the information stream by topics, events, or names of people they are interested in to get the most relevant content.
- Legal document retrieval systems: The legal industry involves a lot of document review work, such as contracts, court judgments, and legal regulations. An efficient full-text search engine can greatly simplify lawyers' workflows, enabling them to find precedents, cited clauses, and related legal bases more quickly.
- Healthcare information systems: In the medical field, doctors often need to consult patients' historical medical records, the latest medical research papers, and other reference materials. With full-text indexing, healthcare professionals can access relevant information more conveniently, thereby making more accurate diagnostic decisions.
Any application involving the management and querying of large volumes of unstructured text data can consider adopting full-text indexing to improve retrieval efficiency. For more detailed information about OceanBase's full-text search capabilities, see Full-text index.
Examples
We define a table to store document materials and set up a full-text index for the documents. Using the full-text index, we can quickly match documents containing the desired keyword and sort them in descending order of similarity.
Create a table and add a full-text index
CREATE TABLE Articles (
id INT AUTO_INCREMENT,
title VARCHAR(255),
content TEXT,
PRIMARY KEY (id),
FULLTEXT ft1 (content) WITH PARSER SPACE
);
Insert sample data
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. ');
Query all data in the table
SELECT * FROM Articles;
The execution result is as follows:
+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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
Execute a full-text search query
SELECT id, title, content, MATCH(content) AGAINST('OceanBase database') AS score
FROM Articles
WHERE MATCH(content) AGAINST('OceanBase database');
The execution result is as follows:
+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| 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
View the query execution plan
EXPLAIN SELECT id, title, content, MATCH(content) AGAINST('OceanBase database') AS score
FROM Articles
WHERE MATCH(content) AGAINST('OceanBase database');
The execution result is as follows:
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| 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