列ストアインデックスの JSON インデックス - PolarDB - Alibaba Cloud ドキュメントセンター

JSON オブジェクト全体を解析する必要がある JSON 列に対するクエリは、処理速度が遅くなる可能性があります。これらのクエリを高速化するために、インメモリ列指向インデックス (IMCI) は JSON インデックス機能を提供します。この機能は JSON トークナイザーを使用して JSON データを分解し、転置インデックスを構築します。このプロセスにより、JSON オブジェクト全体の解析を回避し、json_overlaps、json_contains、json_extract、json_unquote、json_type、json_length、json_keys、json_depth などの式のパフォーマンスを大幅に向上させます。

説明

列ストアインデックスの JSON インデックスは現在ベータ版です。この機能を使用する場合は、[チケットを送信] してアクセスをリクエストしてください。

JSON インデックスの作成

JSON インデックスは、JSON トークナイザーを使用して JSON オブジェクトを配列アイテムまたはキーと値のペアに分解し、転置インデックスに追加します。クエリ実行時、システムはインデックスを使用して一致する行を直接検索し、JSON オブジェクト全体の読み込みと解析を回避します。JSON インデックスは JSON データ型の列にのみ作成できます。3 種類の JSON インデックスが利用できます。

JSON 配列インデックス (mode=0 または mode=1) ：配列を含む JSON 列を対象とします。スカラー要素 (整数、文字列、時刻値など) のみを含む配列をサポートし、オブジェクト配列やネストされた配列はサポートしません。mode=1 の使用を推奨します。このインデックスタイプは json_overlaps と json_contains をサポートします。
JSON キーバリューインデックス (mode=2) ：キーと値のペアの値がスカラー (整数、文字列、時刻値など) またはスカラー配列であるオブジェクトを含む JSON 列を対象とします。json_paths パラメータを使用して、インデックスを作成するキーまたは配列を指定します (例：{$.k1} または {$[*].k1,$[*].k2})。このインデックスタイプは json_extract、json_overlaps、json_contains をサポートします。
JSON ハイブリッドインデックス (mode=3) ：キーと値のペアの値がスカラー、オブジェクト、スカラー配列、またはオブジェクト配列である、任意のタイプの JSON 列を対象とします。json_paths パラメータを使用して、インデックスを作成するキーまたは配列を指定します。パスに @[extract|contains|overlaps|unquote|type|length|keys|depth|structure_path|structure_hash|structure_value] を追加して、そのパスがサポートする式を宣言できます。デフォルトは @[extract|contains|overlaps] で、json_overlaps、json_contains、json_extract などのスカラーおよびスカラー配列式をサポートします。@[length] は json_length をサポートします。@[contains|overlaps|structure_path] は、オブジェクト配列に対する json_overlaps と json_contains をサポートします。@[extract|structure_hash] または @[extract|structure_value] は、オブジェクトに対する json_extract をサポートします。structure_hash はオブジェクトのハッシュ値をインデックスに格納するため、最小のコストでフィルタリングが可能です。structure_value はオブジェクト値をインデックスに格納するため、より高いストレージコストで正確なフィルタリングが可能です。このインデックスタイプは json_overlaps、json_contains、json_extract、json_unquote、json_type、json_length、json_keys、json_depth をサポートします。

JSON インデックスの作成により転置インデックスが構築され、その使用方法は全文インデックスと同様です。詳細については、「IMCI 全文インデックス」をご参照ください。

操作手順

JSON インデックス機能の有効化 ：JSON インデックスを使用するには、まずグローバルパラメータ imci_enable_fts_json を有効にする必要があります。

パラメータ

レベル

説明

imci_enable_fts_json

グローバル

JSON インデックス機能を有効にするかどうかを制御します。

ON (デフォルト) ：機能を有効にします。
OFF ：機能を無効にします。

構文：
```
CREATE TABLE table_name (
    column_name JSON COMMENT "imci_fts(type=4 mode=MODE json_paths={$.PATH1,$.PATH2,...})"
) COMMENT 'columnar=1';
```
パラメータ：
- type=4 ：JSON トークナイザーの使用を指定します。
- mode ：JSON インデックスタイプを指定します。JSON 配列インデックスの場合は 0 または 1、JSON キーバリューインデックスの場合は 2、JSON ハイブリッドインデックスの場合は 3 に設定します。
- json_paths ：インデックスを作成するキーまたは配列を指定します。このパラメータは mode=2 または mode=3 の場合に有効です。たとえば、JSON オブジェクト '{"k1":"v1","k2":2}' の場合、json_paths={$.k1}、json_paths={$.k2}、または json_paths={$.k1,$.k2} を指定できます。mode=3 の場合、各パスに @[...] を追加して、そのパスがサポートする式を宣言できます。JSON ハイブリッドインデックスで説明されている値を使用します。

JSON 配列インデックス

JSON 配列インデックスは、配列を含む JSON 列に対するクエリを高速化します。json_overlaps と json_contains 式をサポートします。

構文

ALTER TABLE table_name MODIFY COLUMN column_name JSON DEFAULT NULL COMMENT 'imci_fts(type=4 mode=1)';

json_overlaps クエリの高速化

パラメータ

レベル

説明

imci_convert_json_overlap_to_match

グローバル/セッション

json_overlaps 式の JSON インデックス高速化を有効にするかどうかを制御します。

ON ：高速化を有効にします。
OFF (デフォルト) ：高速化を無効にします。

このパラメータを有効にすると、json_overlaps 式は FtsTableScan を使用して高速化されます。JSON トークナイザーはターゲット値を分解し、転置インデックスで各値を検索し、クエリは結果の和集合を返します。つまり、ターゲット要素のいずれかが見つかった場合に行が一致します。

mysql> EXPLAIN SELECT * FROM t1 WHERE json_overlaps(title, '[300, "301"]');
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------------+
| ID | Operator             | Name | Extra Info                                                                                                      |
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------------+
|  1 | Select Statement     |      | IMCI Execution Plan (max_dop = 1, max_query_mem = 1073741824)                                                   |
|  2 | └─Compute Scalar     |      |                                                                                                                 |
|  3 |   └─FtsTableScan     | t1   | Term: ("300(json_type(2))", "301(json_type(5))") Fallback: (JSON_OVERLAPS(t1.title, "[300, "301"](json)") <> 0) |
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------------+

json_contains クエリの高速化

パラメータ

レベル

説明

imci_convert_json_contains_to_match

グローバル/セッション

json_contains 式の JSON インデックス高速化を有効にするかどうかを制御します。

ON ：高速化を有効にします。
OFF (デフォルト) ：高速化を無効にします。

このパラメータを有効にすると、json_contains 式は FtsTableScan を使用して高速化されます。JSON トークナイザーはターゲット値を分解し、転置インデックスで各値を検索し、クエリは結果の積集合を返します。つまり、すべてのターゲット要素が見つかった場合にのみ行が一致します。

mysql> EXPLAIN SELECT * FROM t1 WHERE json_contains(title, '[300, "301"]');
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------+
| ID | Operator             | Name | Extra Info                                                                                                |
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------+
|  1 | Select Statement     |      | IMCI Execution Plan (max_dop = 1, max_query_mem = 1073741824)                                             |
|  2 | └─Compute Scalar     |      |                                                                                                           |
|  3 |   └─FtsTableScan     | t1   | Term: ("300(json_type(2))", "301(json_type(5))") Fallback: (JSON_CONTAINS(t1.title, "[300, "301"]") <> 0) |
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------+

JSON キーバリューインデックス

JSON キーバリューインデックスは、JSON オブジェクト内の特定のキーに対する等価クエリを高速化します。json_extract 式をサポートします。インデックスを作成する際に、json_paths を使用してインデックスを作成するキーを指定します。クエリ実行時、システムは転置インデックスを使用して、キーと値のペアの値に基づいて一致する行を迅速に検索できます。

構文

ALTER TABLE table_name MODIFY COLUMN column_name JSON DEFAULT NULL COMMENT 'imci_fts(type=4 mode=2 json_paths={$.k1,$.k2})';

json_extract クエリの高速化

パラメータ

レベル

説明

imci_convert_json_extract_to_match

グローバル/セッション

json_extract 式の JSON インデックス高速化を有効にするかどうかを制御します。

ON ：高速化を有効にします。
OFF (デフォルト) ：高速化を無効にします。

このパラメータを有効にすると、json_extract を使用する等価式は FtsTableScan によって高速化されます。次の例は、$.k1 の値が '1' である行を検索するクエリを示しています。

mysql> EXPLAIN SELECT * FROM t1 WHERE json_extract(title, "$.k1") = '1';
+----+----------------------+------+------------------------------------------------------------------------------------------------+
| ID | Operator             | Name | Extra Info                                                                                     |
+----+----------------------+------+------------------------------------------------------------------------------------------------+
|  1 | Select Statement     |      | IMCI Execution Plan (max_dop = 1, max_query_mem = 1073741824)                                  |
|  2 | └─Compute Scalar     |      |                                                                                                |
|  3 |   └─FtsTableScan     | t1   | Term: ("1(json_index(0),json_type(5))") Fallback: (JSON_EXTRACT(t1.title, "$.k1") = "1(json)") |
+----+----------------------+------+------------------------------------------------------------------------------------------------+

同じクエリで複数のキーと値の条件を高速化できます。

mysql> EXPLAIN SELECT * FROM t1 WHERE json_extract(title, "$.k1") = '1' AND json_extract(title, "$.k2") = 2;
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | Operator             | Name | Extra Info                                                                                                                                                                                |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | Select Statement     |      | IMCI Execution Plan (max_dop = 1, max_query_mem = 1073741824)                                                                                                                             |
|  2 | └─Compute Scalar     |      |                                                                                                                                                                                           |
|  3 |   └─FtsTableScan     | t1   | Term: ("1(json_index(0),json_type(5))") Term: ("2(json_index(1),json_type(2))") Fallback: ((JSON_EXTRACT(t1.title, "$.k1") = "1(json)") AND (JSON_EXTRACT(t1.title, "$.k2") = "2(json)")) |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

JSON ネスト化インデックス

JSON ネスト化インデックスは、オブジェクト配列を含む JSON 列内の特定のキーに対する配列クエリを高速化します。json_overlaps、json_contains、json_extract などの式を使用するネストされたクエリをサポートします。インデックスを作成する際に、json_paths を使用してオブジェクト配列内のキーを指定します。クエリ実行時、システムはキーと値のペアの値に基づいて、転置インデックスで一致する行を検索します。

構文

ALTER TABLE table_name MODIFY COLUMN column_name JSON DEFAULT NULL COMMENT 'imci_fts(type=4 mode=2 json_paths={$[*].k1,$[*].k2})';

ALTER TABLE table_name MODIFY COLUMN column_name JSON DEFAULT NULL COMMENT 'imci_fts(type=4 mode=3 json_paths={$[*].k1@[extract|contains|overlaps|unquote|type|length|keys|depth|structure_path|structure_hash|structure_value] ,$[*].k2@[contains|overlaps]})';

JSON ネストクエリの高速化

json_overlaps、json_contains、json_extract を使用するネストされたクエリは、FtsTableScan によって高速化できます。たとえば、JSON 列に '[{"id": 1, "name": "Zhang San"}, {"id": 2, "name": "Li Si"}, {"id": 3, "name": "Chen Yi"}]' が含まれているとします。json_paths={$[*].id, $[*].name} を指定して、オブジェクト配列内のキーと値のペアを解析し、インデックスに追加してクエリを高速化できます。

mysql> EXPLAIN SELECT * FROM t1 WHERE json_overlaps(title->'$[*].id', '[1, 2]');
+----+----------------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | Operator             | Name | Extra Info                                                                                                                                                 |
+----+----------------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | Select Statement     |      | IMCI Execution Plan (max_dop = 32, max_query_mem = 137438953472)                                                                                           |
|  2 | └─Compute Scalar     |      |                                                                                                                                                            |
|  3 |   └─FtsTableScan     | t1   | Term: (OR(1(json_index(0),json_type(2)), 2(json_index(0),json_type(2)))) Fallback: (JSON_OVERLAPS(JSON_EXTRACT(t1.title, "$[*].id"), "[1, 2](json)") <> 0) |
+----+----------------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

mysql> EXPLAIN SELECT * FROM t1 WHERE json_contains(title->'$[*].name', '["Zhang San", "Chen Yi"]');
+----+----------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | Operator             | Name | Extra Info                                                                                                                                                                            |
+----+----------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | Select Statement     |      | IMCI Execution Plan (max_dop = 32, max_query_mem = 137438953472)                                                                                                                      |
|  2 | └─Compute Scalar     |      |                                                                                                                                                                                       |
|  3 |   └─FtsTableScan     | t1   | Term: (AND("Zhang San"(json_index(1),json_type(5)), "Chen Yi"(json_index(1),json_type(5)))) Fallback: (JSON_CONTAINS(JSON_EXTRACT(t1.title, "$[*].name"), "["Zhang San", "Chen Yi"](json)") <> 0)         |
+----+----------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

JSON ハイブリッドインデックス

JSON ハイブリッドインデックスは、スカラー値、オブジェクト、スカラー配列、オブジェクト配列を含む、任意のタイプの JSON 列内の特定のキーに対するクエリを高速化します。json_overlaps、json_contains、json_extract、json_unquote、json_type、json_length、json_keys、json_depth をサポートします。

構文

ALTER TABLE table_name MODIFY COLUMN column_name JSON DEFAULT NULL COMMENT 'imci_fts(type=4 mode=3 json_paths={$[*].k1@[extract|contains|overlaps|unquote|type|length|keys|depth|structure_path|structure_hash|structure_value] ,$[*].k2@[contains|overlaps]})';

各種 JSON クエリの高速化

パラメータ	レベル	説明
`imci_convert_json_unquote_to_match`	グローバル/セッション	`json_unquote` 式の JSON インデックス高速化を有効にするかどうかを制御します。 ON ：高速化を有効にします。 OFF (デフォルト) ：高速化を無効にします。
`imci_convert_json_type_to_match`	グローバル/セッション	`json_type` 式の JSON インデックス高速化を有効にするかどうかを制御します。 ON ：高速化を有効にします。 OFF (デフォルト) ：高速化を無効にします。
`imci_convert_json_length_to_match`	グローバル/セッション	`json_length` 式の JSON インデックス高速化を有効にするかどうかを制御します。 ON ：高速化を有効にします。 OFF (デフォルト) ：高速化を無効にします。
`imci_convert_json_keys_to_match`	グローバル/セッション	`json_keys` 式の JSON インデックス高速化を有効にするかどうかを制御します。 ON ：高速化を有効にします。 OFF (デフォルト) ：高速化を無効にします。
`imci_convert_json_depth_to_match`	グローバル/セッション	`json_depth` 式の JSON インデックス高速化を有効にするかどうかを制御します。 ON ：高速化を有効にします。 OFF (デフォルト) ：高速化を無効にします。
`imci_fts_json_structure_preference`	グローバル/セッション	`structure_hash` と `structure_value` の両方が `@[structure_hash\|structure_value]` で有効になっている場合、オプティマイザーがオブジェクトのハッシュ値とオブジェクト値のどちらを使用するかを制御します。有効な値：`"HASH"` (デフォルト) および `"VALUE"`。

imci_convert_json_unquote_to_match を有効にすると、json_unquote 式は FtsTableScan によって高速化されます。

mysql> explain select id, title from t1 where json_unquote(title->'$.id') = '21';
+----+------------------------+------+----------------------------------------------------------------------------------------------------------------------------------+
| ID | Operator               | Name | Extra Info                                                                                                                       |
+----+------------------------+------+----------------------------------------------------------------------------------------------------------------------------------+
|  1 | Select Statement       |      | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited)                                                                    |
|  2 | └─Compute Scalar       |      |                                                                                                                                  |
|  3 |   └─FILTER             |      | Cond: ((TRUE PRED) AND (JSON_UNQUOTE(JSON_EXTRACT(t1.title, "$.id")) = "21"))                                                    |
|  4 |     └─FtsTableScan     | t1   | Term: (AND("21"(json:index(0),type(STRING),src(UnquotedValue)))) Fallback: (JSON_UNQUOTE(JSON_EXTRACT(t1.title, "$.id")) = "21") |
+----+------------------------+------+----------------------------------------------------------------------------------------------------------------------------------+

imci_convert_json_length_to_match を有効にすると、json_length 式は FtsTableScan によって高速化されます。

mysql> explain select id, json_length(title), title from t1 where json_length(title) = 1;
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------+
| ID | Operator             | Name | Extra Info                                                                                                  |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------+
|  1 | Select Statement     |      | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited)                                               |
|  2 | └─Compute Scalar     |      |                                                                                                             |
|  3 |   └─FtsTableScan     | t1   | Term: (AND(1(json:index(0),type(UNSIGNED_INTEGER),src(LengthValue)))) Fallback: (JSON_LENGTH(t1.title) = 1) |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------+

パスで structure_hash と structure_value の両方を有効にした場合、imci_fts_json_structure_preference を "VALUE" に設定すると、オプティマイザーはオブジェクトの等価クエリにオブジェクト値を使用します。

mysql> set imci_fts_json_structure_preference = "VALUE";
mysql> explain select id, title from t1 where json_extract(title, '$.info') = cast('{"id": 10, "name": "name_10"}' as json);
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | Operator             | Name | Extra Info                                                                                                                                                                                |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | Select Statement     |      | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited)                                                                                                                             |
|  2 | └─Compute Scalar     |      |                                                                                                                                                                                           |
|  3 |   └─FtsTableScan     | t1   | Term: (AND(json_value({"id":10,"name":"name_10"})(json:index(0),type(OBJECT),src(JsonValue)))) Fallback: (JSON_EXTRACT(t1.title, "$.info") = "{"id": 10, "name": "name_10"}(json)") |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+