ÍNDICE DE FORÇA MySQL – Acervo Lima Tutorials

Resumo : neste tutorial, você aprenderá como usar FORCE INDEXa 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 INDEXdica para instruí-lo a usar o índice.

O seguinte ilustra a FORCE INDEXsintaxe 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 INDEXclá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 productstabela do banco de dados de exemplo para demonstração.

A instrução a seguir mostra os índices da productstabela:

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 buyPricecoluna:

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 buyPricecoluna:

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 buyPricecoluna, embora o índice exista. O motivo é que a consulta retorna 94 linhas de 110 linhas da productstabela, 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 EXPLAINdepoimento 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 )