AP（分析処理）型のデータベースで特殊インデックスを作成する際には、以下のベストプラクティスに基づいて選択できます。
JSON複数値インデックス
適用シナリオ
JSON複数値インデックスは、JSONドキュメント内の配列フィールドに特化したインデックスタイプであり、複数の値やプロパティに対するクエリが必要なシナリオに適しており、クエリ効率を大幅に向上させます。主に以下のシナリオに適用されます：
多対多関連クエリ：2つのエンティティ間で多対多の関係が存在する場合、複数値インデックスはクエリの高速化を実現します。例えば、俳優が複数の映画に出演し、一方で1本の映画に複数の俳優が参加する場合があります。この場合、JSON配列を使用して映画に関与するすべての俳優を保存し、JSON複数値インデックスを活用して特定の俳優が出演する映画をクエリを最適化できます。
タグやカテゴリによる検索：エンティティが複数のタグまたは分類を持つ場合、複数値インデックスを使用してクエリを高速化できます。例えば、商品が複数のタグプロパティを持つ場合、JSON配列を使用して商品の複数のタグを保存し、特定のタグまたは複数のタグを持つ商品を迅速にクエリできます。
JSON複数値インデックスは、JSON配列に基づくクエリで、where条件に以下の3つの述語が含まれる場合に特に効果的です：
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配列を1行ずつフィルタリング比較する必要があることがわかります。JSON自体のフィルタリングコストもそれなりに高いため、フィルタリング対象となるレコード行数が一定以上になると、クエリの効率に大幅に影響を及ぼします。この場合、hobbies列に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シナリオにおけるデータベース内の特殊なインデックス作成のベストプラクティスガイドを参考にしてください。これらのインデックスを適切に適用することで、データベースのクエリ効率とパフォーマンスが向上します。