AP（分析処理）型データベースで特殊インデックスの作成を検討する際、以下の実践を参考にしてください：
JSON複数値インデックス
適用シナリオ
JSON複数値インデックスは、JSONドキュメント内の配列フィールド専用に設計されたインデックスタイプであり、複数の値や属性に対するクエリが必要なシナリオに適しており、クエリの効率を大幅に向上させることができます。複数値インデックスは主に以下のシナリオに適用されます：
多対多関連クエリ：2つのエンティティ間に多対多関係が存在する場合、複数値インデックスはクエリを高速化できます。例えば、俳優は複数の映画に出演することができ、1本の映画には複数の俳優が参加する可能性があります。映画に関わるすべての俳優をJSON配列で保存し、JSON複数値インデックスを利用して特定の俳優が出演する映画をクエリすることができます。
タグと分類クエリ：エンティティが複数のタグや分類を持つ場合、複数値インデックスを使用してクエリを高速化できます。例えば、商品には複数のタグ属性が含まれる可能性があります。商品の複数のタグをJSON配列で保存することで、特定の1つまたは複数のタグを含む商品を迅速にクエリできます。
JSON複数値インデックスは、通常、JSON配列に基づいており、WHERE句に以下の3つの述語が含まれるクエリの高速化に使用されます：
JSON複数値インデックスの詳細については、複数値インデックスを参照してください。
操作例
以下の例を通じて、JSON複数値インデックスの適用シナリオを説明します。ユーザー情報テーブルがあると仮定します。このテーブルには、ユーザーID、氏名、年齢、趣味が記録されており、趣味はJSON配列形式で保存されています。1人のユーザーは複数の趣味を持つことができ、これらの趣味はユーザーのタグと理解できます。ユーザー情報を格納するテーブル構造とデータは以下のとおりです：
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