Analise o desempenho das consultas no Banco de Dados do Azure para MySQL – Servidor flexível usando o comando EXPLAIN
EXPLAIN é uma ferramenta útil para ajudá-lo ajuda otimizar consultas. A instrução EXPLAIN pode ser utilizada para obter informações sobre como as instruções SQL são executadas. A seguir, é mostrado um exemplo de saída da execução de uma instrução EXPLAIN.
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 de key é NULL, o que significa que o servidor flexível do Banco de Dados do Azure para MySQL não conseguiu 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 servidor flexível do Banco de Dados do Azure para MySQL está usando um índice para limitar o número de linhas a 1, o que reduz drasticamente o tempo de busca.
Índice de cobertura
Um índice de cobertura inclui todas as colunas de uma consulta, o que reduz a recuperação de valor a partir de tabelas de dados. A instrução GROUP BY e a saída relacionada a seguir 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 servidor flexível do Banco de Dados do Azure para MySQL não está usando nenhum índice, porque os índices apropriados não estão disponíveis. A saída também mostra Using temporary; Using filesort, o que indica que o servidor flexível do Banco de Dados do Azure para MySQL está criando uma tabela temporária para atender à 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
Neste caso, você pode criar um índice coberto em ambos c1 e c2 adicionando o valor de c2" diretamente no índice, o que irá eliminar pesquisa de dados adicionais.
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 mostrado na saída do EXPLAIN acima, o servidor flexível do Banco de Dados do Azure para MySQL agora usa o índice de cobertura e evita a necessidade de 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 ordenadas por concatenação de 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 realiza uma operação de ordenação em arquivo que é bastante lenta, especialmente quando precisa ordenar 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 do comando EXPLAIN agora mostra que o servidor flexível do Banco de Dados do Azure para MySQL usa um índice combinado para evitar ordenações adicionais, já que o índice está previamente ordenado.
Conclusão
O desempenho pode aumentar significativamente com o uso de EXPLAIN em conjunto com diferentes tipos de índices. Ter um índice em uma tabela não necessariamente significa que o servidor flexível do Banco de Dados do Azure para MySQL pode usá-lo para suas consultas. Sempre valide suas pressuposições usando a EXPLAIN e otimize as consultas usando índices.