Quem mexe com MySQL ja ouviu falar aquela famosa frase de clientes:
“Nossa, como esta consulta onera meu ambiente em todas as vezes que executo-a”
Nós DBA’s sabemos que na maioria destes cenários a query tá lenta e ninguém quer admitir que o problema é dela.
É aí que entra o EXPLAIN, aquela ferramenta que parece complicada, mas na real só quer te mostrar como o MySQL pretende executar a consulta a respeito.
Sendo, irei apresentar como interpretar melhor seu Explain.
- Se a query está usando índices.
- Se está fazendo Full Scan na tabela.
- Se existem filtros ruins.
- Se a junção entre as tabelas está eficiente.
E o melhor…. não executa a query, só mostrará o plano real.
|
1 |
Ele te entregará colunas como:
- Id –> Ordem do processamento da query.
- Select_type –> Tipo da consulta (simple, subquery, derived etc..).
- Table –> Tabela sendo lida.
- Type –> Qualidade do acesso (All, index, ref, const,eq_ref).
- Possible_keys –> Índices possíveis.
- Key –> Índice realmente usado.
- Rows –> Estimativa de linhas que serão lidas.
- Filtered –> Percentual de filtragem (%).
- Extra –> Detalhes importantes (Using index, filesort, temporary).
| Type | Significado |
|---|---|
| All | Full table scan (ruim na maioria dos casos) |
| Index | Scan do índice inteiro |
| Range | Busca por intervalo (bom) |
| Ref | Uso de índice com igualdade (muito bom) |
| Eq_ref | Melhor forma para JOINs |
| Const / System | O MySQL sabe exatamente o registro (excelente) |
Os conceitos resumidamente foram compartilhados acima, agora é hora de por a mão na massa…. ou melhor, mão nos dados!
- Criação de base + tabela para nossa DEMO.
- Após realizarmos a população de registros na tabela, vamos executar um explain e entendermos na sequência seus resultados.
Vemos que, a consulta está realizando um full_scan, seguindo a condição imposta no Where.
- Vemos que não temos chaves (indices) a serem utilizados, então vamos criar o indice correto para esta consulta.
Agora sim, após a criação do índice podemos notar a melhoria em questão.
As colunas type + key informando as condições sendo utilizadas.
- Outro cenário clássico, é a junção de 2 tabelas para o retorno de informações complementares.
No mesmo conceito dos “demos” anteriores, vemos 2 tabelas agora realizando um junção de informações.
- Assim, usaremos a mesma análise anteriormente.
Criando o indice adequado, vemos uma melhoria no tipo acesso para as tabelas, JOIN usando o índice correto e menos linhas para consultar.
Óbviamente que nesta demo, mostrei a todos conceitos simples dos planos de execuções e 2 formato diferentes.
| Extra | Significado |
|---|---|
| Using index | A query leu tudo pelo índice (ótimo) |
| Using where | Houve filtragem (ok) |
| Using temporary | MySQL criou tabela temporária (pode ser ruim) |
| Using filesort | Houve ordenação fora do índice (pode ser ruim) |
Se na maioria das vezes identificarmos Using temporary + Filesort, quase haverá melhorias a serem realizads.
- Foque em type, key, rows e Extra.
- Se o MySQL não escolhe seu índice, muitas vezes faltam estatísticas.
- Queries lentes SEMPRE têm um motivo –> relaxa, Explain mostrará o caminho.
- Não se assuste com o tamanho do plano: comece pela pior linha (maior rows + type ruim).
Referências.
https://dev.mysql.com/doc/refman/8.4/en/explain-output.html
https://dev.to/amitiwary999/get-useful-information-from-mysql-explain-2i97
