列存索引JSON索引 - PolarDB

當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類型的列，包含三種索引類型：

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 參數指定需要索引的鍵或數組，path 後面可帶 @[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 在索引中儲存物件的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
Global
控制是否開啟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索引類型。0 與 1 為JSON數組索引（建議使用 1），2 為JSON索引值索引，3 為JSON混合索引。
- json_paths：指定需要索引的鍵或數組資訊，在mode=2與mode=3時使用。例如，對於'{"k1":"v1","k2":2}'，可以指定json_paths={$.k1}、json_paths={$.k2}或json_paths={$.k1,$.k2}。mode=3 時，還可以在每個 path 後面追加 @[...] 尾碼聲明該鍵需要加速的運算式，可選項為 extract、contains、overlaps、unquote、type、length、keys、depth、structure_path、structure_hash 與 structure_value，不指定時預設為 @[extract|contains|overlaps]，例如 json_paths={$[*].k1@[extract|contains|overlaps|length] ,$[*].k2@[contains|overlaps]}。

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

Global/Session

控制是否開啟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

Global/Session

控制是否開啟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

Global/Session

控制是否開啟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加速。以[{"id": 1, "name": "張三"}, {"id": 2, "name": "李四"}, {"id": 3, "name": "陳一"}] 為例，可以通過json_paths={$[*].id, $[*].name}將JSON對象數組中對應索引值解析出來添加到索引來加速查詢。

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', '["張三", "陳一"]');
+----+----------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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(張三(json_index(1),json_type(5)), 陳一(json_index(1),json_type(5)))) Fallback: (JSON_CONTAINS(JSON_EXTRACT(t1.title, "$[*].name"), "["張三", "陳一"](json)") <> 0)            |
+----+----------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

JSON混合索引

JSON混合索引用於加速任意類型JSON列中特定鍵的標量、對象、標量數組與對象數組等查詢，支援 json_overlaps、json_contains、json_extract、json_unquote、json_type、json_length、json_keys 與 json_depth 等運算式。索引建立時通過 json_paths 指定需要索引的鍵或數組，並在每個 path 後用 @[...] 尾碼聲明該鍵需要加速的運算式。

建立文法

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`	Global/Session	控制是否開啟 `json_unquote` 使用JSON索引加速功能。 ON：開啟。 OFF（預設）：關閉。
`imci_convert_json_type_to_match`	Global/Session	控制是否開啟 `json_type` 使用JSON索引加速功能。 ON：開啟。 OFF（預設）：關閉。
`imci_convert_json_length_to_match`	Global/Session	控制是否開啟 `json_length` 使用JSON索引加速功能。 ON：開啟。 OFF（預設）：關閉。
`imci_convert_json_keys_to_match`	Global/Session	控制是否開啟 `json_keys` 使用JSON索引加速功能。 ON：開啟。 OFF（預設）：關閉。
`imci_convert_json_depth_to_match`	Global/Session	控制是否開啟 `json_depth` 使用JSON索引加速功能。 ON：開啟。 OFF（預設）：關閉。
`imci_fts_json_structure_preference`	Global/Session	控制在 `@[structure_hash\|structure_value]` 均開啟的情況下，查詢最佳化選擇對象的hash值還是對象值。 `"HASH"`（預設） `"VALUE"`。

開啟後，對應運算式利用 FtsTableScan 加速。以下樣本查詢 $.id 值等於 '21' 的行：

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

以下樣本查詢JSON元素個數等於 1 的行：

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 指定使用對象值精準過濾：

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