Início / dba / Perguntas / 308885
Accepted
Zak
Asked: 2022-03-18 09:54:03 +0800 CST

JSON_EXRACT vs consulta SQL nativa

  • 772

Eu ia originalmente perguntar isso no SO, mas pensei melhor, então estou tentando minha mão aqui.

Eu pesquisei isso e não consigo encontrar nenhum dado definitivo. Eu tenho uma tabela MySQL que se parece com:

| id |  type  |  value  |

O valuecampo contém uma string JSON e o tipo de coluna é JSON. Meu JSON contém informações de identificação que eu gostaria de poder chamar no IE

WHERE json_extract(value, '$.contractor_id')='12345'

Quais são as ramificações de desempenho fazendo isso dessa maneira, em vez de apenas criar uma contractor_idcoluna separada? Esta tabela específica tem aproximadamente 500.000 linhas. O contractor_idnão é um campo capaz de chave ou indexável ... Então, é realmente 6 de uma maneira meia dúzia de outra? Ou há uma razão específica pela qual preciso criar uma coluna separada para fins de desempenho?

mysql json

1 respostas

  1. Best Answer

    Depende da sua versão do MySQL.

    No MySQL 5.7, era possível criar uma coluna virtual baseada na expressão json_extract() e um índice nessa coluna virtual. Mas você deve pesquisar usando essa coluna virtual para usar o índice.

    mysql> create table mytable (id serial primary key, type text, value json);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into mytable set value = '{"contractor_id": "12345"}';
Query OK, 1 row affected (0.00 sec)

mysql> alter table mytable add column contractor_id int as (json_extract(value, '$.contactor_id')), add index (contractor_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
    
mysql> explain select * from mytable where json_extract(value, '$.contractor_id')='12345';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from mytable where contractor_id='12345';
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | mytable | NULL       | ref  | contractor_id | contractor_id | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

    No MySQL 8.0, você pode criar índices virtuais em expressões, sem precisar criar a coluna virtual primeiro. Mas há restrições na indexação de expressões json.

    mysql> alter table mytable add index ((json_unquote(json_extract(value, '$.contractor_id'))));
ERROR 3757 (HY000): Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.

    Então eu tenho que convertê-lo para um inteiro:

    mysql> alter table mytable add index ((cast(json_unquote(json_extract(value, '$.contractor_id')) as signed)));
Query OK, 0 rows affected (0.01 sec)

    O otimizador parece ser capaz de descobrir isso, e eu posso usar o índice mesmo com base em parte da expressão.

    mysql> explain select * from mytable where json_extract(`value`,'$.contractor_id')=12345;
+----+-------------+---------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | mytable | NULL       | ref  | functional_index | functional_index | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+------------------+------------------+---------+-------+------+----------+-------+

    Mas todas essas idiossincrasias em relação ao JSON em um RDBMS são um incômodo. Você acaba sendo forçado a soluções complexas e que exigem profundo conhecimento de recursos avançados.

    Por que não apenas criar contractor_idou qualquer outro atributo que você deseja que seja indexado como uma coluna normal? Isso é muito mais simples.

    Quanto mais vejo pessoas usando JSON no MySQL, mais sinto que é um dos piores e mais desnecessários recursos a serem adicionados ao produto.

    Há casos em que você pode realmente precisar ter uma coluna "semi-estruturada", quando você precisa armazenar dados que possuem campos variáveis. JSON é bom para isso, ou XML, ou YAML, ou protobufs, etc. Mas fazê-los suportar operações SQL como se fossem colunas normais não é uma boa estratégia.

    Use colunas normais para atributos que você deseja pesquisar ou classificar. Se necessário, use JSON apenas como uma coluna "payload" para armazenar os dados variáveis.

    Você também pode gostar da minha apresentação How to Use JSON in MySQL Wrong . Eu desenvolvi essa apresentação antes do MySQL 8.0, então ela não cobre índices de expressão, mas outros pontos ainda são verdadeiros, como JSON exigindo muito mais espaço para armazenar os mesmos dados.

    • 1

relate perguntas