Índice JSON para un columnstore index - PolarDB - Centro de documentación de Alibaba Cloud

Las consultas sobre una columna JSON que necesitan analizar sintácticamente todo el objeto JSON pueden ser lentas. Para acelerar estas consultas, In-Memory Column Index (IMCI) ofrece la función de índice JSON. Esta función utiliza un tokenizer JSON para descomponer los datos JSON y compilar un índice invertido. Este proceso evita analizar sintácticamente el objeto JSON completo y mejora significativamente el rendimiento de expresiones como json_overlaps, json_contains, json_extract, json_unquote, json_type, json_length, json_keys y json_depth.

Nota

El índice JSON para un columnstore index se encuentra actualmente en versión beta. Si desea usar esta función, envíe un ticket para solicitar acceso.

Crear un índice JSON

Un índice JSON utiliza un tokenizer JSON para descomponer un objeto JSON en elementos de arreglo o pares clave-valor, y los agrega a un índice invertido. Durante una consulta, el sistema usa el índice para encontrar directamente las filas coincidentes, lo que evita cargar y analizar sintácticamente el objeto JSON completo. Solo puede crear un índice JSON en columnas del tipo de datos JSON. Existen tres tipos de índices JSON disponibles:

Índice de arreglo JSON (mode=0 o mode=1): diseñado para columnas JSON que contienen arreglos. Solo admite arreglos con elementos escalares (como enteros, cadenas o valores de tiempo), pero no arreglos de objetos ni arreglos anidados. Se recomienda usar mode=1. Este tipo de índice admite json_overlaps y json_contains.
Índice de clave-valor JSON (mode=2): indicado para columnas JSON que contienen objetos donde los valores de los pares clave-valor son escalares (como enteros, cadenas o valores de tiempo) o arreglos de escalares. Utilice el parámetro json_paths para especificar las claves o los arreglos que se van a indexar, por ejemplo, {$.k1} o {$[*].k1,$[*].k2}. Este tipo de índice admite json_extract, json_overlaps y json_contains.
Índice híbrido JSON (mode=3): recomendado cuando se tienen columnas JSON de cualquier tipo, donde los valores de los pares clave-valor son escalares, objetos, arreglos de escalares o arreglos de objetos. Utilice el parámetro json_paths para especificar las claves o los arreglos que se van a indexar. Puede agregar @[extract|contains|overlaps|unquote|type|length|keys|depth|structure_path|structure_hash|structure_value] al final de una ruta para declarar qué expresiones admite esa ruta. El valor predeterminado es @[extract|contains|overlaps], que admite expresiones de escalares y arreglos de escalares como json_overlaps, json_contains y json_extract. @[length] admite json_length. @[contains|overlaps|structure_path] admite json_overlaps y json_contains en arreglos de objetos. @[extract|structure_hash] o @[extract|structure_value] admite json_extract en objetos. structure_hash almacena el valor hash del objeto en el índice, lo que permite filtrar con el menor costo posible. structure_value almacena el valor del objeto en el índice, lo que proporciona un filtrado preciso con un mayor costo de almacenamiento. Este tipo de índice admite json_overlaps, json_contains, json_extract, json_unquote, json_type, json_length, json_keys y json_depth.

Al crear un índice JSON se compila un índice invertido, y su uso es similar al de un índice de texto completo. Para obtener más información, consulte IMCI full-text index.

Procedimiento

Habilitar la función de índice JSON: para usar un índice JSON, primero debe habilitar el parámetro global imci_enable_fts_json.
Parámetro
Nivel
Descripción
imci_enable_fts_json
Global
Controla si se habilita la función de índice JSON.
ON (predeterminado): habilita la función.
OFF: deshabilita la función.
Sintaxis:
```
CREATE TABLE table_name (
    column_name JSON COMMENT "imci_fts(type=4 mode=MODE json_paths={$.PATH1,$.PATH2,...})"
) COMMENT 'columnar=1';
```
Parámetros:
- type=4: indica que se use el tokenizer JSON.
- mode: especifica el tipo de índice JSON. Establezca este valor en 0 o 1 para un índice de arreglo JSON, en 2 para un índice de clave-valor JSON, o en 3 para un índice híbrido JSON.
- json_paths: define las claves o los arreglos que se van a indexar. Este parámetro aplica cuando mode=2 o cuando mode=3. Por ejemplo, para el objeto JSON '{"k1":"v1","k2":2}', puede especificar json_paths={$.k1}, json_paths={$.k2} o json_paths={$.k1,$.k2}. Cuando mode=3, puede agregar @[...] al final de cada ruta para declarar qué expresiones admite esa ruta, con los valores descritos para el índice híbrido JSON.

Índice de arreglo JSON

Un índice de arreglo JSON acelera las consultas sobre columnas JSON que contienen arreglos. Admite las expresiones json_overlaps y json_contains.

Sintaxis

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

Acelerar consultas json_overlaps

Parámetro

Nivel

Descripción

imci_convert_json_overlap_to_match

Global/Session

Controla si se habilita la aceleración mediante índice JSON para las expresiones json_overlaps.

ON: habilita la aceleración.
OFF (predeterminado): deshabilita la aceleración.

Cuando este parámetro está habilitado, las expresiones json_overlaps se aceleran mediante un FtsTableScan. El tokenizer JSON descompone los valores objetivo, busca cada uno en el índice invertido y la consulta devuelve la unión de los resultados. Esto significa que una fila coincide si se encuentra cualquiera de los elementos objetivo.

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

Acelerar consultas json_contains

Parámetro

Nivel

Descripción

imci_convert_json_contains_to_match

Global/Session

Controla si se habilita la aceleración mediante índice JSON para las expresiones json_contains.

ON: habilita la aceleración.
OFF (predeterminado): deshabilita la aceleración.

Cuando este parámetro está habilitado, las expresiones json_contains se aceleran mediante un FtsTableScan. El tokenizer JSON descompone los valores objetivo, busca cada uno en el índice invertido y la consulta devuelve la intersección de los resultados. Esto significa que una fila coincide únicamente si se encuentran todos los elementos objetivo.

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

Índice de clave-valor JSON

Un índice de clave-valor JSON acelera las consultas de igualdad sobre claves específicas dentro de objetos JSON. Admite la expresión json_extract. Al crear el índice, utilice json_paths para especificar qué claves se van a indexar. Durante una consulta, el sistema puede usar el índice invertido para localizar rápidamente las filas coincidentes según el valor del par clave-valor.

Sintaxis

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

Acelerar consultas json_extract

Parámetro

Nivel

Descripción

imci_convert_json_extract_to_match

Global/Session

Controla si se habilita la aceleración mediante índice JSON para las expresiones json_extract.

ON: habilita la aceleración.
OFF (predeterminado): deshabilita la aceleración.

Cuando este parámetro está habilitado, las expresiones de igualdad que usan json_extract se aceleran mediante un FtsTableScan. A continuación se muestra una consulta que busca las filas donde el valor de $.k1 es '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)") |
+----+----------------------+------+------------------------------------------------------------------------------------------------+

También puede acelerar varias condiciones de clave-valor en la misma consulta:

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

Índice anidado JSON

Un índice anidado JSON acelera las consultas de arreglo sobre claves específicas en columnas JSON que contienen arreglos de objetos. Admite consultas anidadas que usan expresiones como json_overlaps, json_contains y json_extract. Al crear el índice, utilice json_paths para especificar las claves de los arreglos de objetos. Durante una consulta, el sistema localiza las filas coincidentes en el índice invertido según los valores de los pares clave-valor.

Sintaxis

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

Acelerar consultas anidadas JSON

Las consultas anidadas que usan json_overlaps, json_contains y json_extract se pueden acelerar mediante un FtsTableScan. Por ejemplo, suponga que una columna JSON contiene '[{"id": 1, "name": "Zhang San"}, {"id": 2, "name": "Li Si"}, {"id": 3, "name": "Chen Yi"}]'. Puede especificar json_paths={$[*].id, $[*].name} para analizar sintácticamente los pares clave-valor del arreglo de objetos y agregarlos al índice con el fin de acelerar las consultas.

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

Índice híbrido JSON

Un índice híbrido JSON acelera las consultas sobre claves específicas en columnas JSON de cualquier tipo, incluidos valores escalares, objetos, arreglos de escalares y arreglos de objetos. Admite json_overlaps, json_contains, json_extract, json_unquote, json_type, json_length, json_keys y json_depth.

Sintaxis

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

Acelerar diversos tipos de consultas JSON

Parámetro	Nivel	Descripción
`imci_convert_json_unquote_to_match`	Global/Session	Controla si se habilita la aceleración mediante índice JSON para las expresiones `json_unquote`. ON: habilita la aceleración. OFF (predeterminado): deshabilita la aceleración.
`imci_convert_json_type_to_match`	Global/Session	Controla si se habilita la aceleración mediante índice JSON para las expresiones `json_type`. ON: habilita la aceleración. OFF (predeterminado): deshabilita la aceleración.
`imci_convert_json_length_to_match`	Global/Session	Controla si se habilita la aceleración mediante índice JSON para las expresiones `json_length`. ON: habilita la aceleración. OFF (predeterminado): deshabilita la aceleración.
`imci_convert_json_keys_to_match`	Global/Session	Controla si se habilita la aceleración mediante índice JSON para las expresiones `json_keys`. ON: habilita la aceleración. OFF (predeterminado): deshabilita la aceleración.
`imci_convert_json_depth_to_match`	Global/Session	Controla si se habilita la aceleración mediante índice JSON para las expresiones `json_depth`. ON: habilita la aceleración. OFF (predeterminado): deshabilita la aceleración.
`imci_fts_json_structure_preference`	Global/Session	Controla si el optimizador usa el valor hash del objeto o el valor del objeto cuando tanto `structure_hash` como `structure_value` están habilitados en `@[structure_hash\|structure_value]`. Valores válidos: `"HASH"` (predeterminado) y `"VALUE"`.

Después de habilitar imci_convert_json_unquote_to_match, las expresiones json_unquote se aceleran mediante un 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") |
+----+------------------------+------+----------------------------------------------------------------------------------------------------------------------------------+

Después de habilitar imci_convert_json_length_to_match, las expresiones json_length se aceleran mediante un 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) |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------+

Cuando una ruta tiene habilitados tanto structure_hash como structure_value, establezca imci_fts_json_structure_preference en "VALUE" para que el optimizador use el valor del objeto en una consulta de igualdad de objetos:

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