Índice Funcional MySQL – Acervo Lima Tutorials

Resumo : neste tutorial você aprenderá como usar o índice funcional do MySQL para aumentar a velocidade de consulta que envolve funções e expressões.

Introdução ao Índice Funcional MySQL

Um índice funcional é criado com base no resultado de uma expressão ou função aplicada a uma ou mais colunas de uma tabela.

Um índice funcional permite otimizar consultas indexando valores computados de colunas específicas. Pode ser útil quando você tem consultas que envolvem funções aplicadas aos seus dados.

Para criar um índice funcional, você usa a CREATE INDEXinstrução:

CREATE INDEX index_name
ON table_name ((fn));Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe:

index_name: Este é o nome do índice.
fn: Esta é uma função ou expressão que envolve as colunas da tabela.

Observe os parênteses duplos. A sintaxe está correta, pois os parênteses duplos são usados para delimitar a expressão, distinguindo-a de colunas ou prefixos de coluna

Além da CREATE INDEXinstrução, você pode usar a ALTER TABLEinstrução para adicionar um índice funcional a uma tabela:

ALTER TABLE table_name
ADD INDEX index_name((fn));Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Exemplo de índice funcional MySQL

Usaremos a orderstabela do banco de dados de exemplo :

A seguir encontra-se o total de pedidos com a data do pedido em 2004:

SELECT 
  COUNT(*)
FROM 
  orders 
WHERE 
  YEAR(orderDate) = 2004;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+----------+
| COUNT(*) |
+----------+
|      151 |
+----------+
1 row in set (0.02 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Ele retorna 151 pedidos.

A instrução a seguir usa EXPLAINpara mostrar como o otimizador de consulta executa a consulta:

EXPLAIN 
SELECT 
  COUNT(*) 
FROM 
  orders 
WHERE 
  YEAR(orderDate) = 2004;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  326 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A saída mostra que a consulta não usa um índice.

Para resolver isso, você pode fazer duas coisas:

Primeiro, crie um índice para a orderDatecoluna:

CREATE INDEX idx_order_date 
ON orders(orderDate);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Segundo, altere a consulta para não usar a YEAR()função:

SELECT 
  COUNT(*) 
FROM 
  orders 
WHERE 
  orderDate BETWEEN '2004-01-01' 
  AND '2004-12-31';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+----------+
| COUNT(*) |
+----------+
|      151 |
+----------+
1 row in set (0.01 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A nova consulta usa o índice idx_order_date:

+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | orders | NULL       | range | idx_order_date | idx_order_date | 3       | NULL |  151 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A consulta agora usa o índice. No entanto, se você usar a YEAR()função, a consulta ainda não poderá utilizar o índice.

Para corrigir isso permanentemente, você pode criar um índice funcional na orderDatecoluna que usa a YEAR()função como esta:

CREATE INDEX idx_year 
ON orders((YEAR(orderDate)));Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Após ter o índice funcional idx_year, a consulta que utiliza a YEAR()função utilizará o índice conforme mostrado na consulta a seguir:

EXPLAIN 
SELECT 
  COUNT(*) 
FROM 
  orders 
WHERE 
  YEAR(orderDate) = 2004;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_year      | idx_year | 5       | const |  151 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )