AP（分析処理）型データベースで特殊インデックスを作成する場合、以下のベストプラクティスに従うことができます：
JSON複数値インデックス
適用シナリオ
JSON複数値インデックスは、JSONドキュメント内の配列フィールド専用に設計されたインデックスタイプであり、複数の値や属性を対象とするクエリが必要なシナリオに適しており、クエリの効率を大幅に向上させることができます。複数値インデックスは主に以下のシナリオに適用されます：
多対多関連クエリ：2つのエンティティ間に多対多関係が存在する場合、複数値インデックスはクエリを高速化できます。例えば、1人の俳優が複数の映画に出演することができ、1本の映画には複数の俳優が参加することがあります。映画に出演しているすべての俳優をJSON配列で保存し、JSON複数値インデックスを利用して特定の俳優が出演した映画を検索するクエリを最適化できます。
タグと分類のクエリ：エンティティが複数のタグや分類を持つ場合、複数値インデックスを使用してクエリを高速化できます。例えば、1つの商品には複数のタグ属性が含まれる可能性があります。商品の複数のタグを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複数値インデックスの後付け機能はデフォルトで無効になっています。後付けJSON複数値インデックスのスイッチを有効にするには、sysテナントで設定する必要があります。
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
上記のAPシナリオにおけるデータベース内の特殊インデックス作成に関するベストプラクティスガイドを参照してください。これらのインデックスを適切に適用することで、データベースクエリの効率とパフォーマンスを向上させることができます。