VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
As tabelas usadas neste guia
Indexe a coluna de junção
Sem um índice na coluna de junção, o servidor constrói uma tabela hash sobre um dos lados e varre o outro.EXPLAIN FORMAT=TREE mostra isso pelo nome:
join_buffer_size o servidor o despeja em disco em lotes.
Adicionar o índice substitui tudo isso por uma busca:
EXPLAIN, os mesmos 2 planos aparecem como Extra: Using join buffer (hash join) e type: ref. Essa tabela também mostra a ordem em que o servidor
escolheu fazer a junção, o que importa assim que uma consulta tem 3 ou mais tabelas: o
servidor as lê de cima para baixo, e a estimativa de linhas da primeira
se multiplica por tudo o que vem abaixo.
Lendo a saída do EXPLAIN cobre o resto do plano.
Uma condição de igualdade é o que um hash join consegue buscar. Faça a junção por um intervalo ou por uma
desigualdade e o servidor ainda constrói o hash, mas não tem com o que
buscar: o plano mostra Inner hash join (no condition) com o teste em um
Filter acima dele, então cada par de linhas é formado e depois descartado. Esse é
o custo que um join de igualdade indexado evita.
Uma cláusula WHERE transforma um LEFT JOIN de volta em um INNER JOIN
O bug de contagem de linhas errada mais comum, e nada avisa você.o.amount NULL. O WHERE
então testa cada linha, NULL > 50 é NULL em vez de verdadeiro, e essas linhas são
descartadas. Mover a condição para o ON faz dela parte do que conta como
correspondência.
A regra: em um outer join, uma condição sobre a tabela interna pertence ao ON, e
uma condição sobre a tabela preservada pertence ao WHERE. A exceção é
WHERE <chave da tabela interna> IS NULL, em que descartar as linhas correspondidas é o
objetivo.
Um NULL na coluna de junção não corresponde a nada
orders.customer_id aceita NULL, e o pedido 4 não tem cliente. Um inner join
o descarta, porque NULL não é igual a nada, nem mesmo a outro NULL.
customer_id é NULL, que é
a metade que surpreende as pessoas: a linha existe, seu valor é real, e nenhuma condição
de junção consegue alcançá-la. Um total de receita construído sobre esse join fica 15.00 abaixo e
nada avisa.
Quando uma coluna de junção aceita NULL, decida o que uma linha órfã deve fazer antes de
escrever o join. Um LEFT JOIN a partir de orders a mantém com um nome de cliente NULL.
NULL no MySQL cobre a regra de comparação por trás disso.
Um join um-para-muitos multiplica suas linhas
Um join retorna 1 linha por correspondência, então fazer a junção com uma tabela que contém várias linhas correspondentes repete a linha da esquerda uma vez por correspondência. Os totais calculados sobre esse resultado ficam então errados, normalmente altos demais, e a consulta continua parecendo certa.GROUP BY c.id com SUM ou COUNT sobre a coluna juntada. Consulte
Desempenho de GROUP BY no MySQL.
A mesma aritmética dá errado mais rápido com 2 joins um-para-muitos em uma consulta,
em que os 2 conjuntos de correspondências se multiplicam entre si. Agregue cada um em sua
própria subconsulta em vez de juntar os dois de uma vez.
Um ON ausente dá a você todas as combinações
O MySQL aceitaINNER JOIN sem cláusula ON e o interpreta como um cross join, então
um erro de digitação produz o produto das duas tabelas em vez de um erro. 2 tabelas de
10.000 linhas dão 100 milhões de linhas.
Quando um join retorna um número suspeitosamente redondo e suspeitosamente grande, verifique
se há um ON ausente antes de qualquer outra coisa. Uma vírgula entre as tabelas no FROM é
a mesma coisa na sintaxe antiga, com a condição de correspondência normalmente mais
abaixo, no WHERE.
O MySQL não tem FULL OUTER JOIN
Não existeFULL OUTER JOIN no MySQL. Combine as 2 metades com UNION:
UNION remove as linhas duplicadas, que é o que faz as 2 metades se encaixarem
de forma limpa. O UNION ALL não emula um full outer join de jeito nenhum: ele retorna cada
linha correspondida duas vezes, uma de cada metade. Quando seus dados têm linhas duplicadas que você
precisa manter, desduplique por uma coluna de chave em vez de recorrer ao UNION ALL.
JOIN ou subconsulta
Um join e uma subconsultaIN frequentemente expressam o mesmo filtro, e qual deles o
otimizador prefere depende dos dados. Quando você só precisa saber se uma
linha relacionada existe e não quer nenhuma de suas colunas, EXISTS normalmente fica mais legível
e deixa o servidor parar na primeira correspondência.
Subconsultas vs JOINs no MySQL os compara com
planos.
Solução de Problemas
Veja também
- Lendo a saída do EXPLAIN — o plano que mostra qual índice um join usou
- Chaves estrangeiras no MySQL — a restrição que decide qual coluna indexar
- Desempenho de GROUP BY no MySQL — agregando um join um-para-muitos de volta a 1 linha
- Como os joins funcionam — o tutorial, se você quiser o básico primeiro