Durante minha trajetória sempre utilizei o EXPLAIN como a principal ferramenta para analisar minhas operações SQL.
Quando uma Query apresentava certa lentidão, o processo era padrão para analisar:
|
1 |
EXPLAIN SELECT... |
Se o índice aparecia no plano de execução, a impressão era de que o problema estava resolvido.
Mas ao longo do tempo, comecei a perceber alguns fatores curiosos:
- Consultas mesmo indexadas, continuava lenta.
- Alguns indices eram ignorados pelo otimizador.
- O número de linhas retornado no Explain, nem sempre era a realizada daquela tabela.
Foi ai que passei a perceber que:
“O explain não mostra a realidade. O Explain mostra o que o otmizador acredita ser a realidade”.
Neste post vamos utilizar uma tabela “Clientes” para demonstrar situações onde o EXPLAIN pode induzir conclusões equivocadas e como validar o comportamento real
da consulta.
Cenário 1 -> Indice existe, mas o MySQL não quer usá-lo.
Imagine a seguinte operação:
|
1 |
EXPLAIN SELECT *FROM clientes WHERE cidade = 'ARARAQUARA'; |
Vemos um resultado tranquilo.
|
1 2 |
Type: ref Key: idx_cidade |
Agora, como uma pequena alteração:
|
1 |
EXPLAIN SELECT *FROM clientes WHERE UPPER(cidade) = 'ARARAQUARA'; |
Resultado:
|
1 2 |
Type: ALL Key: NULL |
Full table Scan.
Nossa Henrique, mas o que aconteceu?
O índice continua existindo.
Mas o problema é que o MySQL precisará executar:
|
1 |
UPPER(cidade) |
para cada linha antes de comparar o valor, tendo como consequência
a não utilização do índice.
Primeira lição!
“O índice não desapareceu.
Nós criamos uma consulta que impede sua utilização!!!”
Cenário 2 -> O índice está sendo usado e mesmo assim a
consulta continua ruim?
Imagine agora, a seguinte distribuição:
|
1 2 3 4 5 |
cidade -------------- SAO PAULO 95% RIO DE JANEIRO 4% ARARAQUARA 1% |
Então, executamos:
|
1 |
EXPLAIN SELECT *FROM clientes WHERE cidade = 'SAO PAULO'; |
E podemos observar acima:
|
1 |
key: idx_cidade |
Parece ótimo, usando nossa operação utiliza do indice.
Mas existe um detalhe compartilhado no início:
“95% dos registros possuem o valor SAO PAULO”.
Sendo assim, o indice precisará acessar a tabela inteira.
Segunda lição!
“Índice utilizado não significa consulta rápida.”
Cenário 3 -> O Explain e a realidade podem ser diferentes!
Vamos analisar agora:
|
1 |
EXPLAIN SELECT *FROM clientes WHERE status = 'A'; |
Resultado obtido:
|
1 2 |
key: idx_status rows: 48583 |
Até aqui, vemos o uso do indice.
O otimizador estima que aproximadamente 48583 registros serão retornados.
Então, vamos validar esta informação:
|
1 |
SELECT COUNT(*) FROM clientes WHERE status = 'A'; |
Eita…. agora temos uma situação interessante!
| Informação | Valor |
|---|---|
| Estimativa do EXPLAIN | 48.583 |
| Registros reais | 99.000 |
Uma diferença superior a 100%.
Então, o Explain esta mentindo?
Não exatamente, e esta é uma das maiores armadilhas para quem analisa performance apenas utilizando o EXPLAIN.
O Explain não executa a consulta, ele apenas utiliza informações estatísticas armazenadas pelo mecanismo para estimar:
- Cardinalidade.
- Seletividade.
- Custo de acesso.
- Quantidade de linhas.
Em outras palavras:
“O explain mostra o que o otimizador acredita que vai acontecer.”
Então Henrique, como descobrir a realidade?
No MySQL 8 em diante, temos a ferramenta extremamente útil:
|
1 |
EXPLAIN ANALYZE SELECT... |
Diferentemente do Explain tradicional, o comando realmente executa a consulta e traz informações como:
- Tempo real de execução.
- Linhas processadas.
- Loops.
- Operadores executados.
Agora, vamos observar na prática:
Terceira lição!
“O Explain mostra uma previsão, o Explain Analyze mostra a realidade”.
Cenário 4 -> Descobrindo o raciocínio do otimizador.
Uma funcionalidade pouco utilizada, mas extremamente poderosa é o:
|
1 |
OPTIMIZER_TRACE |
Na prática, habilitando a funcionalidade:
|
1 |
SET optimizer_trace='enabled=on'; |
Em seguinda, executamos a operação:
|
1 |
SELECT *FROM clientes WHERE cidade = 'SAO PAULO'; |
Acima, apenas um resumo inicial do que você irá encontrar.
Como:
- Planos considerados.
- Custos calculados.
- Índices avaliados.
- Índices descartados.
- Plano escolhido.
É literalmente o processo de tomada de decisão do otimizador.
Quarta lição!
“Nem sempre o MySQL escolhe o plano que nós gostaríamos.
Mas quase sempre existe um motivo para a escolha.”
Conclusão.
Depois de analisar alguns cenários, deixo abaixo 3 pontos importantes:
- O Explain é uma ferramenta ESSENCIAL.
- O Explain não está mentindo!
- Mas ele também não está contando toda a história.
O otimizador trabalha com estimativas, e estas estimativas podem ser influenciadas por:
- Cardinalidade.
- Seletividade.
- Distribuição de dados.
- Estatísticas desatualizadas.
- Funções aplicadas sobre colunas.
Por isto, sempre que possível complemente sua análise utilizando:
|
1 |
EXPLAIN ANALYZE |
E também:
|
1 |
OPTIMIZER_TRACE |
Porque existe uma grande diferença entre:
“O que o MySQL acredita que vai acontecer e…..
O que realmente acontece!”
Referências:
MySQL 8.4 -> Explain Statement.
