Índice JSON do MySQL – Acervo Lima Tutorials

Resumo : neste tutorial, você aprenderá como criar um índice para dados JSON no MySQL usando índices funcionais.

Configurando uma tabela de exemplo

Usaremos a productstabela do banco de dados de amostra como fonte de dados para criar dados JSON:

Primeiro, crie uma nova tabela chamada product_info:

CREATE TABLE product_results(
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A product_resultstabela possui duas colunas:

id: Esta é uma coluna de chave primária de incremento automático .
data: esta coluna possui o tipo de dados JSON que armazena os dados JSON.

A seguir, crie dados JSON a partir das colunas da productstabela e insira-os na product_resultstabela:

INSERT INTO product_results(data) 
SELECT 
  JSON_OBJECT(
    'buyPrice', buyPrice, 'MSRP', msrp, 
    'quantityInStock', quantityInStock, 
    'productName', productName
  ) 
FROM
  products;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A instrução insere 110 linhas na product_resultstabela.

Em seguida, consulte os dados da product_resultstabela:

SELECT * FROM product_results;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+-----+--------------------------------------------------------------------------------------------------------------------------+
| id  | data                                                                                                                     |
+-----+--------------------------------------------------------------------------------------------------------------------------+
|   1 | {"MSRP": 95.70, "buyPrice": 48.81, "productName": "1969 Harley Davidson Ultimate Chopper", "quantityInStock": 7933}      |
|   2 | {"MSRP": 214.30, "buyPrice": 98.58, "productName": "1952 Alpine Renault 1300", "quantityInStock": 7305}                  |
|   3 | {"MSRP": 118.94, "buyPrice": 68.99, "productName": "1996 Moto Guzzi 1100i", "quantityInStock": 6625}                     |
...Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Depois disso, encontre o produto com o nome 'P-51-D Mustang':

SELECT 
  data ->> '$.productName',
  data ->> '$.buyPrice'
FROM 
  product_results 
WHERE 
  data ->> '$.productName' = 'P-51-D Mustang';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Ele retorna o seguinte produto:

+--------------------------+-----------------------+
| data ->> '$.productName' | data ->> '$.buyPrice' |
+--------------------------+-----------------------+
| P-51-D Mustang           | 49.00                 |
+--------------------------+-----------------------+
1 row in set (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A consulta funciona conforme o esperado, mas é necessário verificar todas as linhas da product_resultstabela para encontrar o produto.

Por fim, mostre como o otimizador de consulta executa a consulta usando a EXPLAINinstrução:

EXPLAIN SELECT 
  data ->> '$.productName',
  data ->> '$.buyPrice'
FROM 
  product_results 
WHERE 
  data ->> '$.productName' = 'P-51-D Mustang' \G;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: product_results
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 110
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Para agilizar a consulta, você precisa indexar o JSON na coluna de dados. No entanto, o MySQL não oferece suporte à indexação direta de dados JSON.

Felizmente, você pode usar índices funcionais , disponíveis desde o MySQL 8.0.13, para indexar dados JSON.

Indexando dados JSON usando índices funcionais

Um índice funcional é um índice baseado em uma expressão e não em uma coluna. A instrução a seguir adiciona um índice à product_resultstabela:

ALTER TABLE 
  product_results 
ADD 
  INDEX idx_product_name(
    (data ->> '$.productName')
  );Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Mas retorna um erro:

ERROR 3757 (HY000): Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A razão é que o operador ->> retorna um valor do TEXTtipo que não é indexável.

A mensagem de erro também mostra que precisamos usar o CASToperador para converter o valor em um tipo indexável, por exemplo, VARCHARneste caso:

ALTER TABLE 
  product_results 
ADD 
  INDEX idx_product_name (
    (
      CAST(
        data ->> '$.productName' AS CHAR(255)
      ) COLLATE utf8mb4_bin
    )
  );Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O índice foi criado com sucesso.

Observe que a conversão usando CHAR instrui o MySQL a inferir um tipo VARCHAR.

Agora você pode consultar os produtos pelo preço de compra a partir dos dados JSON:

SELECT 
  data ->> '$.productName',
  data ->> '$.buyPrice'
FROM 
  product_results 
WHERE 
  data ->> '$.productName' = 'P-51-D Mustang';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Ele retorna o mesmo conjunto de resultados de antes. Mas desta vez, ele usa o índice funcional idx_product_name:

EXPLAIN SELECT 
  data ->> '$.productName',
  data ->> '$.buyPrice'
FROM 
  product_results 
WHERE 
  data ->> '$.productName' = 'P-51-D Mustang' \G;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: product_results
   partitions: NULL
         type: ref
possible_keys: idx_product_name
          key: idx_product_name
      key_len: 1023
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )