Resumo : neste tutorial, você aprenderá como usar FORCE INDEX
a instrução MySQL para forçar o Query Optimizer a usar índices nomeados especificados.
Introdução à instrução MySQL FORCE INDEX
O otimizador de consulta é um componente do servidor de banco de dados MySQL que cria o plano de execução ideal para uma instrução SQL.
O otimizador de consulta usa as estatísticas disponíveis para chegar ao plano que tem o menor custo entre todos os planos candidatos.
Por exemplo, uma consulta pode solicitar produtos cujos preços estão entre 10 e 80. Se as estatísticas mostrarem que 80% dos produtos têm essas faixas de preço, então ela poderá decidir que uma varredura completa da tabela é a mais eficiente.
No entanto, se as estatísticas mostrarem que muito poucos produtos têm estas gamas de preços, então a leitura de um índice seguido de acesso à tabela poderá ser mais rápida e eficiente do que uma análise completa da tabela.
Caso o otimizador de consulta ignore o índice, você pode usar a FORCE INDEX
dica para instruí-lo a usar o índice.
O seguinte ilustra a FORCE INDEX
sintaxe da dica:
SELECT *
FROM table_name
FORCE INDEX (index_list)
WHERE condition;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Nesta sintaxe, você coloca a FORCE INDEX
cláusula após a cláusula FROM seguida por uma lista de índices nomeados que o otimizador de consulta deve usar.
Exemplo MySQL FORCE INDEX
Usaremos a products
tabela do banco de dados de exemplo para demonstração.
A instrução a seguir mostra os índices da products
tabela:
SHOW INDEXES FROM products;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para encontrar os produtos cujos preços estão entre 10 e 80, você usa a seguinte afirmação:
SELECT
productName,
buyPrice
FROM
products
WHERE
buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Como você pode imaginar, para retornar os produtos o otimizador de consulta teve que varrer toda a tabela porque nenhum índice estava disponível para a buyPrice
coluna:
EXPLAIN SELECT
productName,
buyPrice
FROM
products
WHERE
buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Vamos criar um índice para a buyPrice
coluna:
CREATE INDEX idx_buyprice ON products(buyPrice);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
E execute a consulta novamente:
EXPLAIN SELECT
productName,
buyPrice
FROM
products
WHERE
buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Surpreendentemente, o otimizador de consulta não usou o índice da buyPrice
coluna, embora o índice exista. O motivo é que a consulta retorna 94 linhas de 110 linhas da products
tabela, portanto, o otimizador de consulta decidiu realizar uma verificação completa da tabela.
Para forçar o otimizador de consulta a usar o idx_buyprice
índice, use a seguinte consulta:
SELECT
productName, buyPrice
FROM
products
FORCE INDEX (idx_buyPrice)
WHERE
buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Desta vez, o índice foi utilizado para localização dos produtos conforme demonstra o EXPLAIN
depoimento a seguir:
EXPLAIN SELECT
productName, buyPrice
FROM
products
FORCE INDEX (idx_buyprice)
WHERE
buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Aqui está a saída:
Neste tutorial, você aprendeu como usar a FORCE INDEX
dica do MySQL para forçar o otimizador de consulta a usar uma lista de índices nomeados.