Índice JSON para índice columnstore - PolarDB: Central de Documentação do Alibaba Cloud

Consultas em uma coluna JSON que exigem a análise de todo o objeto JSON podem apresentar desempenho baixo. Para acelerar essas operações, o In-Memory Column Index (IMCI) oferece o recurso de índice JSON. Esse mecanismo utiliza um tokenizador JSON para decompor os dados e construir um índice invertido. Assim, evita-se a análise completa do objeto JSON, o que melhora significativamente o desempenho de expressões como json_overlaps, json_contains, json_extract, json_unquote, json_type, json_length, json_keys e json_depth.

Nota

O índice JSON para índice columnstore está atualmente em fase beta. Para usar este recurso, envie um ticket e solicite acesso.

Crie um índice JSON

Um índice JSON utiliza um tokenizador para decompor um objeto JSON em itens de array ou pares chave-valor e adicioná-los a um índice invertido. Durante uma consulta, o sistema usa o índice para localizar diretamente as linhas correspondentes, eliminando a necessidade de carregar e analisar o objeto JSON inteiro. É possível criar um índice JSON apenas em colunas do tipo de dados JSON. Há três tipos de índices JSON disponíveis:

Índice de array JSON (mode=0 ou mode=1): Destinado a colunas JSON que contêm arrays. Suporta apenas arrays com elementos escalares (como inteiros, strings ou valores de tempo), mas não é compatível com arrays de objetos ou arrays aninhados. Recomendamos o uso de mode=1. Este tipo de índice oferece suporte a json_overlaps e json_contains.
Índice de chave-valor JSON (mode=2): Indicado para colunas JSON que contêm objetos nos quais os valores dos pares chave-valor são escalares (como inteiros, strings ou valores de tempo) ou arrays escalares. Use o parâmetro json_paths para especifique as chaves ou arrays a serem indexados, como {$.k1} ou {$[*].k1,$[*].k2}. Este tipo de índice é compatível com json_extract, json_overlaps e json_contains.
Índice híbrido JSON (mode=3): Aplicável a colunas JSON de qualquer tipo, onde os valores nos pares chave-valor podem ser escalares, objetos, arrays escalares ou arrays de objetos. Use o parâmetro json_paths para definir as chaves ou arrays a indexar. Anexe @[extract|contains|overlaps|unquote|type|length|keys|depth|structure_path|structure_hash|structure_value] a um caminho para declarar quais expressões esse caminho suporta. O padrão é @[extract|contains|overlaps], que atende a expressões escalares e de arrays escalares como json_overlaps, json_contains e json_extract. O sufixo @[length] ative o suporte a json_length. Já @[contains|overlaps|structure_path] permite o uso de json_overlaps e json_contains em arrays de objetos. Para operações com objetos via json_extract, utilize @[extract|structure_hash] ou @[extract|structure_value]. A opção structure_hash armazena o valor de hash do objeto no índice, permitindo filtragem com menor custo computacional. Por outro lado, structure_value guarda o próprio valor do objeto, oferecendo filtragem precisa, porém com maior custo de armazenamento. Este tipo de índice abrange json_overlaps, json_contains, json_extract, json_unquote, json_type, json_length, json_keys e json_depth.

A criação de um índice JSON gera um índice invertido, cujo funcionamento assemelha-se ao de um índice de texto completo. Para mais detalhes, consulte IMCI full-text index.

Procedimento

Ative o recurso de índice JSON: Para usar um índice JSON, ative primeiro o parâmetro global imci_enable_fts_json.

Parâmetro

Nível

Descrição

imci_enable_fts_json

Global

Controla a ativação do recurso de índice JSON.

ON (padrão): Ativa o recurso.
OFF: Desativa o recurso.

Sintaxe:
```
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: Define o uso do tokenizador JSON.
- mode: Especifica o tipo de índice JSON. Defina como 0 ou 1 para índice de array JSON, 2 para índice de chave-valor JSON ou 3 para índice híbrido JSON.
- json_paths: Indica as chaves ou arrays a serem indexados. Este parâmetro aplica-se quando mode=2 ou quando mode=3. Por exemplo, para o objeto JSON '{"k1":"v1","k2":2}', especifique json_paths={$.k1}, json_paths={$.k2} ou json_paths={$.k1,$.k2}. Quando mode=3, anexe @[...] a cada caminho para declarar quais expressões ele suporta, utilizando os valores descritos para o índice híbrido JSON.

Índice de array JSON

O índice de array JSON acelera consultas em colunas JSON que contêm arrays. Ele oferece suporte às expressões json_overlaps e json_contains.

Sintaxe

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

Acelerar consultas json_overlaps

Parâmetro

Nível

Descrição

imci_convert_json_overlap_to_match

Global/Sessão

Determina se a aceleração por índice JSON deve ser ativada para expressões json_overlaps.

ON: Ativa a aceleração.
OFF (padrão): Desativa a aceleração.

Ao ativar este parâmetro, as expressões json_overlaps passam a ser aceleradas por meio de um FtsTableScan. O tokenizador JSON decompõe os valores-alvo, busca cada um deles no índice invertido e a consulta retorna a união dos resultados. Isso significa que uma linha será considerada correspondente se qualquer um dos elementos-alvo for encontrado.

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

Nível

Descrição

imci_convert_json_contains_to_match

Global/Sessão

Define se a aceleração por índice JSON será habilitada para expressões json_contains.

ON: Habilita a aceleração.
OFF (padrão): Desabilita a aceleração.

Com este parâmetro ativado, as expressões json_contains são otimizadas usando um FtsTableScan. O tokenizador JSON fragmenta os valores-alvo, pesquisa cada um no índice invertido e a consulta retorna a interseção dos resultados. Portanto, uma linha só corresponde se todos os elementos-alvo forem encontrados.

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 chave-valor JSON

O índice de chave-valor JSON otimiza consultas de igualdade em chaves específicas dentro de objetos JSON. Ele suporta a expressão json_extract. Ao criar o índice, use json_paths para indicar quais chaves devem ser indexadas. Durante a execução da consulta, o sistema recorre ao índice invertido para localizar rapidamente as linhas correspondentes com base no valor do par chave-valor.

Sintaxe

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

Nível

Descrição

imci_convert_json_extract_to_match

Global/Sessão

Especifica se a aceleração via índice JSON deve ser ativada para expressões json_extract.

ON: Ativa a aceleração.
OFF (padrão): Desativa a aceleração.

Quando este parâmetro está ativo, expressões de igualdade que usam json_extract ganham desempenho por meio de um FtsTableScan. O exemplo abaixo ilustra uma consulta que busca linhas onde o valor de $.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)") |
+----+----------------------+------+------------------------------------------------------------------------------------------------+

Também é possível acelerar múltiplas condições de chave-valor na mesma 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 aninhado JSON

O índice aninhado JSON otimiza consultas de array em chaves específicas de colunas JSON que contêm arrays de objetos. Ele viabiliza consultas aninhadas utilizando expressões como json_overlaps, json_contains e json_extract. No momento da criação do índice, use json_paths para definir as chaves presentes nos arrays de objetos. Durante a consulta, o sistema localiza as linhas correspondentes no índice invertido com base nos valores dos pares chave-valor.

Sintaxe

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 aninhadas JSON

Consultas aninhadas que empregam json_overlaps, json_contains e json_extract podem ter seu desempenho melhorado por um FtsTableScan. Suponha, por exemplo, que uma coluna JSON contenha '[{"id": 1, "name": "Zhang San"}, {"id": 2, "name": "Li Si"}, {"id": 3, "name": "Chen Yi"}]'. Nesse caso, especifique json_paths={$[*].id, $[*].name} para processar os pares chave-valor do array de objetos e adicioná-los ao índice, acelerando assim as 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

O índice híbrido JSON acelera consultas em chaves específicas de colunas JSON de qualquer tipo, abrangendo valores escalares, objetos, arrays escalares e arrays de objetos. São suportadas as funções json_overlaps, json_contains, json_extract, json_unquote, json_type, json_length, json_keys e json_depth.

Sintaxe

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 diversas consultas JSON

Parâmetro	Nível	Descrição
`imci_convert_json_unquote_to_match`	Global/Sessão	Controla a ativação da aceleração por índice JSON para expressões `json_unquote`. ON: Ativa a aceleração. OFF (padrão): Desativa a aceleração.
`imci_convert_json_type_to_match`	Global/Sessão	Controla a ativação da aceleração por índice JSON para expressões `json_type`. ON: Ativa a aceleração. OFF (padrão): Desativa a aceleração.
`imci_convert_json_length_to_match`	Global/Sessão	Controla a ativação da aceleração por índice JSON para expressões `json_length`. ON: Ativa a aceleração. OFF (padrão): Desativa a aceleração.
`imci_convert_json_keys_to_match`	Global/Sessão	Controla a ativação da aceleração por índice JSON para expressões `json_keys`. ON: Ativa a aceleração. OFF (padrão): Desativa a aceleração.
`imci_convert_json_depth_to_match`	Global/Sessão	Controla a ativação da aceleração por índice JSON para expressões `json_depth`. ON: Ativa a aceleração. OFF (padrão): Desativa a aceleração.
`imci_fts_json_structure_preference`	Global/Sessão	Determina se o otimizador deve usar o valor de hash do objeto ou o próprio valor do objeto quando tanto `structure_hash` quanto `structure_value` estiverem habilitados em `@[structure_hash\|structure_value]`. Valores válidos: `"HASH"` (padrão) e `"VALUE"`.

Após ativar imci_convert_json_unquote_to_match, as expressões json_unquote passam a ser aceleradas por um 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") |
+----+------------------------+------+----------------------------------------------------------------------------------------------------------------------------------+

Ao habilitar imci_convert_json_length_to_match, o sistema acelera as expressões json_length utilizando um 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) |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------+

Caso um caminho tenha tanto structure_hash quanto structure_value ativados, defina imci_fts_json_structure_preference como "VALUE" para que o otimizador utilize o valor do objeto em consultas de igualdade 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)") |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+