Pular para o conteúdo principal

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
Os JOINs funcionam na primeira vez e dão errado depois. A consulta é SQL correto, o servidor não reclama de nada, e a resposta é uma contagem de linhas que você não esperava ou um relatório que leva 40 segundos. Este guia cobre esses casos: o que torna um join lento, e as 4 formas pelas quais um join retorna silenciosamente as linhas erradas. Se você ainda está decidindo que tipo de join escrever, o tutorial ensina cada um deles com um banco de dados de exemplo: como os joins funcionam, INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN e self join.

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:
Um hash join lê cada tabela uma vez. Em 7 linhas isso não é nada. Em tabelas reais são 2 varreduras completas de tabela mais a memória para manter o hash, e quando o lado de construção não cabe em join_buffer_size o servidor o despeja em disco em lotes. Adicionar o índice substitui tudo isso por uma busca:
Peça o mesmo plano de novo e o hash desaparece:
Indexe a coluna de chave estrangeira no lado “muitos” de uma relação um-para-muitos. A chave primária no lado “um” já está indexada, então essa metade não precisa de nada. Na saída padrão do 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 join é executado primeiro e dá aos clientes sem correspondência um 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.
3 linhas de 4 pedidos. Carol está ausente porque não tem pedidos, que é a metade esperada. O pedido 4 está ausente porque seu 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.
Antes de tratar as linhas duplicadas como um bug, confirme que a relação é mesmo um-para-um. Quando ela é um-para-muitos e você quer 1 linha por cliente, agregue: 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 aceita INNER 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 existe FULL OUTER JOIN no MySQL. Combine as 2 metades com UNION:
O 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 subconsulta IN 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