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の全文検索機能の詳細については、全文インデックスをご参照ください。
ベストプラクティスの操作例
ドキュメント資料を保存するテーブルを定義し、ドキュメントに全文インデックスを設定します。全文インデックスを利用することで、期待するキーワードを含むドキュメントを迅速にマッチングし、類似度が高い順に並べ替えることができます。
obclient> CREATE TABLE Articles (
-> id INT AUTO_INCREMENT,
-> title VARCHAR(255) ,
-> content TEXT ,
-> PRIMARY KEY (id),
-> FULLTEXT ft1 (content) WITH PARSER SPACE
-> );
Query OK, 0 rows affected (0.67 sec)
-- データの挿入
obclient> 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 OK, 3 rows affected (0.10 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient> 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 (0.08 sec)
-- 一致するドキュメントを照会
obclient> select id,title, content,match(content) against('OceanBase database') 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 (0.08 sec)
-- EXPLAINコマンドを使用すると、クエリ計画を確認し、そのパフォーマンスを分析できます。
obclient> explain select id,title, content,match(content) against('OceanBase database') 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 (0.08 sec)
上記のAPシナリオにおけるデータベース内の特殊インデックス作成のベストプラクティスガイドを参考にしてください。これらのインデックスを適切に適用することで、データベースクエリの効率とパフォーマンスを向上させるのに役立ちます。