Desempenho da consulta de perfil no Banco de Dados do Azure para MySQL - Servidor flexível usando EXPLAIN
EXPLAIN é uma ferramenta útil que pode ajudá-lo a otimizar consultas. Você pode usar uma instrução EXPLAIN para obter informações sobre como as instruções SQL são executadas. A seguir mostra o exemplo de saída da execução de uma instrução EXPLICA.
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 é NULL, o que significa que o Banco de Dados do Azure para servidor flexível MySQL não pode localizar nenhum índice otimizado para a consulta. Como resultado, ele executa uma verificação de tabela completa. Vamos otimizar essa consulta adicionando um índice na coluna ID e, em seguida, execute a instrução EXPLAIN novamente.
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 Banco de Dados do Azure para servidor flexível 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, o que reduz a recuperação de valor de tabelas de dados. A seguinte instrução GROUP BY e a saída relacionada ilustram isso.
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 Banco de Dados do Azure para servidor flexível MySQL não usa nenhum índice, porque os índices adequados não estão disponíveis. A saída também mostra Usando temporário; Usando filesort, que indica que o Banco de Dados do Azure para servidor flexível 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 Servidor flexível do Banco de Dados do Azure para MySQL ainda precisa 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
Nesse caso, você pode criar um índice coberto em c1 e c2 adicionando o valor de c2" diretamente no índice, o que eliminará mais pesquisas 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 servidor flexível do Banco de Dados do Azure para MySQL agora usa o índice coberto e evita ter que criar uma tabela temporária.
Índice combinado
Um índice combinado consiste em valores de várias colunas e pode ser considerado uma matriz de linhas que são classificadas concatenando valores das colunas indexadas. Esse método pode ser útil em uma instrução GROUP 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 servidor flexível do Banco de Dados do Azure para MySQL executa uma operação de classificação de arquivos que é bastante lenta, especialmente quando ele precisa classificar muitas linhas. Para otimizar essa 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 saída da instrução EXPLAIN agora mostra que o Banco de Dados do Azure para servidor flexível MySQL usa um índice combinado para evitar classificação adicional, pois o índice já está classificado.
Conclusão
Você pode aumentar significativamente o desempenho usando EXPLAIN juntamente com diferentes tipos de índices. Ter um índice em uma tabela não significa necessariamente que o Banco de Dados do Azure para servidor flexível MySQL possa usá-lo para suas consultas. Sempre valide suas suposições usando EXPLAIN e otimize suas consultas usando índices.