Desempenho das consultas do perfil na Base de Dados do Azure para MySQL com o comando EXPLAIN

APLICA-SE A: Base de Dados do Azure para MySQL - Base de Dados do Azure para MySQL de servidor único - Servidor Flexível

Importante

Base de Dados do Azure para MySQL - O Servidor Único está no caminho da reforma. Recomendamos vivamente que faça upgrade para Base de Dados do Azure para MySQL - Servidor Flexível. Para obter mais informações sobre migração para Base de Dados do Azure para MySQL - Servidor Flexível, veja o que está a acontecer com Base de Dados do Azure para MySQL Servidor Único?

O EXPLAIN é uma ferramenta útil que pode ajudá-lo a otimizar consultas. Pode utilizar uma declaração explicada para obter informações sobre como as declarações sql são executadas. O seguinte mostra a saída exemplo da execução de uma declaração EXPLICAda.

mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 10.00
        Extra: Using where

Neste exemplo, o valor da chave é NU, o que significa que o MySQL não consegue localizar quaisquer índices otimizados para a consulta. Como resultado, executa uma tomografia completa da mesa. Vamos otimizar esta consulta adicionando um índice na coluna de ID e, em seguida, executar novamente a declaração EXPLAIN.

mysql> ALTER TABLE tb1 ADD KEY (id);
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ref
possible_keys: id
          key: id
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

Agora, a saída mostra que o MySQL usa um índice para limitar o número de linhas a 1, o que reduz drasticamente o tempo de pesquisa.

Índice de cobertura

Um índice de cobertura inclui todas as colunas de uma consulta, que reduz a recuperação de valor das tabelas de dados. A seguinte declaração DO GRUPO POR E a sua produção conexa ilustra-o.

mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using temporary; Using filesort

A saída mostra que o MySQL não utiliza nenhum índice, porque os índices adequados não estão disponíveis. A saída também mostra a utilização temporária; Utilizando o ficheiro, o que indica que o MySQL cria uma tabela temporária para satisfazer a cláusula GROUP BY .

Criar um índice apenas na coluna c2 não faz diferença, e o MySQL ainda precisa de criar uma tabela temporária:

mysql> ALTER TABLE tb1 ADD KEY (c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using temporary; Using filesort

Neste caso, pode criar um índice coberto tanto em c1 como em c2 , adicionando o valor de c2" diretamente no índice, o que eliminará mais a procura de dados.

mysql> ALTER TABLE tb1 ADD KEY covered(c1,c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: index
possible_keys: covered
          key: covered
      key_len: 108
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using index

Como mostra a saída do EXPLAIN acima, o MySQL utiliza agora o índice coberto e evita ter de criar uma tabela temporária.

Índice combinado

Um índice combinado consiste em valores de várias colunas e pode ser considerado um conjunto de linhas que são classificadas por valores concatenantes das colunas indexadas. Este método pode ser útil numa declaração do GRUPO BY .

mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using filesort

O MySQL executa uma operação de classificação de ficheiros bastante lenta, especialmente quando tem que separar muitas linhas. Para otimizar esta consulta, crie um índice combinado em ambas as colunas que estão sendo classificadas.

mysql> ALTER TABLE tb1 ADD KEY my_sort2 (c1, c2);
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: index
possible_keys: NULL
          key: my_sort2
      key_len: 108
          ref: NULL
         rows: 10
     filtered: 11.11
        Extra: Using where; Using index

A produção da declaração explicada mostra agora que o MySQL utiliza um índice combinado para evitar uma triagem adicional, uma vez que o índice já está classificado.

Conclusão

Pode aumentar significativamente o desempenho utilizando o EXPLAIN juntamente com diferentes tipos de índices. Ter um índice numa tabela não significa necessariamente que o MySQL possa usá-lo para as suas consultas. Valide sempre os seus pressupostos utilizando o EXPLAIN e otimize as suas consultas utilizando índices.

Passos seguintes

  • Para encontrar respostas de pares às suas perguntas mais importantes ou para publicar ou responder a uma pergunta, visite Stack Overflow.