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 INDEX
instruçã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 INDEX
instrução, você pode usar a ALTER TABLE
instruçã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 orders
tabela 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 EXPLAIN
para 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 orderDate
coluna:
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 orderDate
coluna 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 )
Resumo
- Use um índice funcional para otimizar consultas indexando os valores computados de colunas específicas, agilizando a recuperação de dados.