Dica MySQL USE INDEX

Resumo : neste tutorial, você aprenderá como usar a USE INDEXdica do MySQL para instruir o otimizador de consulta a usar apenas uma lista de índices nomeados para uma consulta.

Introdução à dica USE INDEX do MySQL

No MySQL, quando você envia uma consulta SQL ao servidor de banco de dados, o otimizador de consulta tenta criar um plano de execução de consulta ideal.

Para determinar o melhor plano possível, o otimizador de consulta depende de vários parâmetros. Um dos parâmetros mais cruciais para selecionar o índice apropriado é a distribuição de chaves armazenadas , também conhecida como cardinalidade .

A cardinalidade, entretanto, pode não ser precisa. Por exemplo, se a tabela tiver sido muito modificada com muitas inserções ou exclusões, a cardinalidade não será atualizada em tempo hábil.

Para resolver esse problema, você deve executar a ANALYZE TABLEinstrução periodicamente para atualizar a cardinalidade.

Além disso, o MySQL permite recomendar os índices que o otimizador de consulta deve considerar usando uma dica de índice.

Aqui está a sintaxe básica para usar a USE INDEXdica:

SELECT select_list
FROM table_name USE INDEX(index_list)
WHERE condition;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe, USE INDEXinstrui o otimizador de consulta a usar um dos índices nomeados para localizar linhas na tabela.

Observe que quando você recomenda os índices a serem usados, o otimizador de consulta pode decidir usá-los ou não, dependendo do plano de consulta apresentado.

Exemplo de USE INDEX do MySQL

Usaremos a tabela de clientes  do banco de dados de exemplo para demonstração:

Primeiro, use a SHOW INDEXESinstrução para exibir todos os índices da   customerstabela:

SHOW INDEXES FROM customers;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Em segundo lugar, crie quatro índices da seguinte forma:

CREATE INDEX idx_c_ln  ON customers(contactLastName);
CREATE INDEX idx_c_fn ON customers(contactFirstName);
CREATE INDEX idx_name_fl  ON customers(contactFirstName,contactLastName);
CREATE INDEX idx_name_lf  ON customers(contactLastName,contactFirstName);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Terceiro, encontre clientes cujo nome ou sobrenome do contato comece com a letra A. Use a EXPLAINinstrução para verificar quais índices são usados:

EXPLAIN SELECT *
FROM
    customers
WHERE
    contactFirstName LIKE 'A%'
        OR contactLastName LIKE 'A%'\GLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O seguinte mostra a saída da instrução:

           id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: index_merge
possible_keys: idx_c_ln,idx_c_fn,idx_name_fl,idx_name_lf
          key: idx_c_fn,idx_c_ln
      key_len: 52,52
          ref: NULL
         rows: 16
     filtered: 100.00
        Extra: Using sort_union(idx_c_fn,idx_c_ln); Using where
1 row in set, 1 warning (0.00 sec)
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A saída indica que o otimizador de consulta usou os índices idx_c_fne idx_c_ln.

Quarto, se você acha que é melhor usar os índices idx_c_fle idx_c_lf, use a USE INDEXcláusula da seguinte maneira:

EXPLAIN SELECT *
FROM
    customers
USE INDEX (idx_name_fl, idx_name_lf)
WHERE
    contactFirstName LIKE 'A%'
        OR contactLastName LIKE 'A%'\GLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Observe que isso é apenas para fins de demonstração, mas não é a melhor escolha.

O seguinte ilustra a saída:

           id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: index_merge
possible_keys: idx_name_fl,idx_name_lf
          key: idx_name_fl,idx_name_lf
      key_len: 52,52
          ref: NULL
         rows: 16
     filtered: 100.00
        Extra: Using sort_union(idx_name_fl,idx_name_lf); Using where
1 row in set, 1 warning (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Estas são as mudanças:

• A possible_keyscoluna lista apenas os índices especificados na USE INDEXcláusula.
• A coluna chave tem ambos idx_name_fle idx_name_lf. Isso significa que o otimizador de consulta usou os índices recomendados.

Pode USE INDEXser útil se o otimizador de consulta usar o índice errado da lista de índices possíveis.

Resumo

• Use a USE INDEXdica do MySQL para instruir o otimizador de consulta a usar a única lista de índices especificados.