Indeks JSON untuk indeks penyimpanan kolom - PolarDB - Alibaba Cloud Documentation Center

Kueri pada kolom JSON yang mengharuskan penguraian seluruh objek JSON dapat berjalan lambat. Untuk mempercepat kueri tersebut, In-Memory Column Index (IMCI) menyediakan fitur indeks JSON yang menggunakan Pemisah kata JSON untuk memecah data JSON dan membangun indeks terbalik. Pendekatan ini menghindari penguraian lengkap objek JSON sehingga secara signifikan meningkatkan performa ekspresi seperti json_overlaps, json_contains, json_extract, json_unquote, json_type, json_length, json_keys, dan json_depth.

Catatan

Indeks JSON untuk indeks penyimpanan kolom saat ini berada dalam tahap beta. Jika Anda ingin menggunakan fitur ini, submit a ticket untuk meminta akses.

Buat indeks JSON

Indeks JSON menggunakan Pemisah kata JSON untuk memecah objek JSON menjadi elemen array atau pasangan kunci-nilai, lalu menambahkannya ke indeks terbalik. Saat menjalankan kueri, sistem menggunakan indeks tersebut untuk langsung menemukan baris yang sesuai tanpa perlu memuat dan mengurai seluruh objek JSON. Indeks JSON hanya dapat dibuat pada kolom bertipe data JSON. Terdapat tiga jenis indeks JSON:

Indeks array JSON (mode=0 atau mode=1): Untuk kolom JSON yang berisi array. Indeks ini hanya mendukung array dengan elemen skalar (seperti integer, string, atau nilai waktu), bukan array objek atau array bersarang. Disarankan menggunakan mode=1. Jenis indeks ini mendukung json_overlaps dan json_contains.
Indeks kunci-nilai JSON (mode=2): Untuk kolom JSON yang berisi objek di mana nilai dalam pasangan kunci-nilai berupa skalar (seperti integer, string, atau nilai waktu) atau array skalar. Gunakan parameter json_paths untuk menentukan kunci atau array yang akan diindeks, misalnya {$.k1} atau {$[*].k1,$[*].k2}. Jenis indeks ini mendukung json_extract, json_overlaps, dan json_contains.
Indeks hibrida JSON (mode=3): Untuk kolom JSON bertipe apa pun, di mana nilai dalam pasangan kunci-nilai berupa skalar, objek, array skalar, atau array objek. Gunakan parameter json_paths untuk menentukan kunci atau array yang akan diindeks. Anda dapat menambahkan sufiks @[extract|contains|overlaps|unquote|type|length|keys|depth|structure_path|structure_hash|structure_value] pada suatu path untuk mendeklarasikan ekspresi yang didukung oleh path tersebut. Nilai default-nya adalah @[extract|contains|overlaps], yang mendukung ekspresi skalar dan array skalar seperti json_overlaps, json_contains, dan json_extract. Sufiks @[length] mendukung fungsi json_length. Sufiks @[contains|overlaps|structure_path] mendukung json_overlaps dan json_contains pada array objek. Sufiks @[extract|structure_hash] atau @[extract|structure_value] mendukung json_extract pada objek. structure_hash menyimpan nilai hash objek dalam indeks sehingga memungkinkan penyaringan dengan biaya paling rendah, sedangkan structure_value menyimpan nilai objek dalam indeks sehingga memberikan penyaringan lebih akurat dengan biaya penyimpanan lebih tinggi. Jenis indeks ini mendukung json_overlaps, json_contains, json_extract, json_unquote, json_type, json_length, json_keys, dan json_depth.

Pembuatan indeks JSON akan membangun indeks terbalik, dan penggunaannya mirip dengan indeks teks penuh. Untuk informasi lebih lanjut, lihat IMCI full-text index.

Prosedur

Aktifkan fitur indeks JSON: Untuk menggunakan indeks JSON, Anda harus terlebih dahulu mengaktifkan parameter global imci_enable_fts_json.
Parameter
Level
Deskripsi
imci_enable_fts_json
Global
Mengontrol apakah fitur indeks JSON diaktifkan.
ON (default): Mengaktifkan fitur.
OFF: Menonaktifkan fitur.
Sintaksis:
```
CREATE TABLE table_name (
    column_name JSON COMMENT "imci_fts(type=4 mode=MODE json_paths={$.PATH1,$.PATH2,...})"
) COMMENT 'columnar=1';
```
Parameter:
- type=4: Menentukan penggunaan Pemisah kata JSON.
- mode: Menentukan jenis indeks JSON. Atur ke 0 atau 1 untuk indeks array JSON, 2 untuk indeks kunci-nilai JSON, atau 3 untuk indeks hibrida JSON.
- json_paths: Menentukan kunci atau array yang akan diindeks. Parameter ini berlaku ketika mode=2 atau mode=3. Misalnya, untuk objek JSON '{"k1":"v1","k2":2}', Anda dapat menentukan json_paths={$.k1}, json_paths={$.k2}, atau json_paths={$.k1,$.k2}. Ketika mode=3, Anda dapat menambahkan sufiks @[...] pada setiap path untuk mendeklarasikan ekspresi yang didukung oleh path tersebut, sesuai dengan nilai yang dijelaskan untuk indeks hibrida JSON.

Indeks array JSON

Indeks array JSON mempercepat kueri pada kolom JSON yang berisi array dan mendukung ekspresi json_overlaps serta json_contains.

Sintaksis

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

Percepat kueri json_overlaps

Parameter

Level

Deskripsi

imci_convert_json_overlap_to_match

Global/Session

Mengontrol apakah percepatan indeks JSON diaktifkan untuk ekspresi json_overlaps.

ON: Mengaktifkan percepatan.
OFF (default): Menonaktifkan percepatan.

Ketika parameter ini diaktifkan, ekspresi json_overlaps dipercepat dengan menggunakan FtsTableScan. Pemisah kata JSON memecah nilai target, mencari masing-masing nilai tersebut dalam indeks terbalik, dan kueri mengembalikan gabungan hasilnya. Artinya, suatu baris cocok jika salah satu elemen target ditemukan.

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) |
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------------+

Percepat kueri json_contains

Parameter

Level

Deskripsi

imci_convert_json_contains_to_match

Global/Session

Mengontrol apakah percepatan indeks JSON diaktifkan untuk ekspresi json_contains.

ON: Mengaktifkan percepatan.
OFF (default): Menonaktifkan percepatan.

Ketika parameter ini diaktifkan, ekspresi json_contains dipercepat dengan menggunakan FtsTableScan. Pemisah kata JSON memecah nilai target, mencari masing-masing nilai tersebut dalam indeks terbalik, dan kueri mengembalikan irisan hasilnya. Artinya, suatu baris cocok hanya jika semua elemen target ditemukan.

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) |
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------+

Indeks kunci-nilai JSON

Indeks kunci-nilai JSON mempercepat kueri kesamaan pada kunci tertentu dalam objek JSON dan mendukung ekspresi json_extract. Saat membuat indeks, gunakan json_paths untuk menentukan kunci yang akan diindeks. Saat menjalankan kueri, sistem dapat menggunakan indeks terbalik untuk dengan cepat menemukan baris yang cocok berdasarkan nilai pasangan kunci-nilai tersebut.

Sintaksis

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

Percepat kueri json_extract

Parameter

Level

Deskripsi

imci_convert_json_extract_to_match

Global/Session

Mengontrol apakah percepatan indeks JSON diaktifkan untuk ekspresi json_extract.

ON: Mengaktifkan percepatan.
OFF (default): Menonaktifkan percepatan.

Ketika parameter ini diaktifkan, ekspresi kesamaan yang menggunakan json_extract dipercepat oleh FtsTableScan. Contoh berikut menunjukkan kueri yang menemukan baris di mana nilai $.k1 adalah '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)") |
+----+----------------------+------+------------------------------------------------------------------------------------------------+

Anda dapat mempercepat beberapa kondisi kunci-nilai dalam satu kueri yang sama:

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)")) |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Indeks bersarang JSON

Indeks bersarang JSON mempercepat kueri array pada kunci tertentu dalam kolom JSON yang berisi array objek. Indeks ini mendukung kueri bersarang yang menggunakan ekspresi seperti json_overlaps, json_contains, dan json_extract. Saat membuat indeks, gunakan json_paths untuk menentukan kunci dalam array objek tersebut. Saat menjalankan kueri, sistem menemukan baris yang cocok dalam indeks terbalik berdasarkan nilai pasangan kunci-nilai tersebut.

Sintaksis

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]})';

Percepat kueri JSON bersarang

Kueri bersarang yang menggunakan json_overlaps, json_contains, dan json_extract dapat dipercepat oleh FtsTableScan. Sebagai contoh, asumsikan kolom JSON berisi '[{"id": 1, "name": "Zhang San"}, {"id": 2, "name": "Li Si"}, {"id": 3, "name": "Chen Yi"}]'. Anda dapat menentukan json_paths={$[*].id, $[*].name} untuk mengurai pasangan kunci-nilai dalam array objek dan menambahkannya ke indeks guna mempercepat kueri.

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)         |
+----+----------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Indeks hibrida JSON

Indeks hibrida JSON mempercepat kueri pada kunci tertentu dalam kolom JSON bertipe apa pun, termasuk nilai skalar, objek, array skalar, dan array objek. Indeks ini mendukung json_overlaps, json_contains, json_extract, json_unquote, json_type, json_length, json_keys, dan json_depth.

Sintaksis

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]})';

Percepat berbagai kueri JSON

Parameter	Level	Deskripsi
`imci_convert_json_unquote_to_match`	Global/Session	Mengontrol apakah percepatan indeks JSON diaktifkan untuk ekspresi `json_unquote`. ON: Mengaktifkan percepatan. OFF (default): Menonaktifkan percepatan.
`imci_convert_json_type_to_match`	Global/Session	Mengontrol apakah percepatan indeks JSON diaktifkan untuk ekspresi `json_type`. ON: Mengaktifkan percepatan. OFF (default): Menonaktifkan percepatan.
`imci_convert_json_length_to_match`	Global/Session	Mengontrol apakah percepatan indeks JSON diaktifkan untuk ekspresi `json_length`. ON: Mengaktifkan percepatan. OFF (default): Menonaktifkan percepatan.
`imci_convert_json_keys_to_match`	Global/Session	Mengontrol apakah percepatan indeks JSON diaktifkan untuk ekspresi `json_keys`. ON: Mengaktifkan percepatan. OFF (default): Menonaktifkan percepatan.
`imci_convert_json_depth_to_match`	Global/Session	Mengontrol apakah percepatan indeks JSON diaktifkan untuk ekspresi `json_depth`. ON: Mengaktifkan percepatan. OFF (default): Menonaktifkan percepatan.
`imci_fts_json_structure_preference`	Global/Session	Mengontrol apakah pengoptimal menggunakan nilai hash objek atau nilai objek itu sendiri ketika kedua opsi `structure_hash` dan `structure_value` diaktifkan dalam `@[structure_hash\|structure_value]`. Nilai yang valid: `"HASH"` (default) dan `"VALUE"`.

Setelah Anda mengaktifkan imci_convert_json_unquote_to_match, ekspresi json_unquote dipercepat oleh 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") |
+----+------------------------+------+----------------------------------------------------------------------------------------------------------------------------------+

Setelah Anda mengaktifkan imci_convert_json_length_to_match, ekspresi json_length dipercepat oleh 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) |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------+

Ketika suatu path mengaktifkan kedua opsi structure_hash dan structure_value, atur imci_fts_json_structure_preference ke "VALUE" agar pengoptimal menggunakan nilai objek untuk kueri kesamaan objek:

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)") |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+