Início / dba / Perguntas / 36391
Accepted
BellevueBob
Asked: 2013-03-12 06:20:05 +0800 CST

Existe uma diferença de execução entre uma condição JOIN e uma condição WHERE?

  • 772

Existe uma diferença de desempenho entre essas duas consultas de exemplo?

Consulta 1:

select count(*)
from   table1 a
join   table2 b
on     b.key_col=a.key_col
where  b.tag = 'Y'

Consulta 2;

select count(*)
from   table1 a
join   table2 b
on     b.key_col=a.key_col
   and b.tag = 'Y'

Observe que a única diferença é a colocação da condição suplementar; o primeiro usa uma WHEREcláusula e o segundo adiciona a condição à ONcláusula.

Quando executo essas consultas em meu sistema Teradata, os planos de explicação são idênticos e a etapa JOIN mostra a condição adicional em cada caso. No entanto, nesta questão do SO em relação ao MySQL, uma das respostas sugeriu que o segundo estilo é preferido porque WHEREo processamento ocorre após as junções serem feitas.

Existe uma regra geral a seguir ao codificar consultas como esta? Acho que deve depender da plataforma, pois obviamente não faz diferença no meu banco de dados, mas talvez seja apenas um recurso do Teradata. E se for dependente da plataforma, gostaria muito de obter algumas referências de documentação; Eu realmente não sei o que procurar.

mysql oracle

3 respostas

  1. Best Answer

    De acordo com o capítulo 9 (analisador e otimizador), página 172 do livro Understanding MySQL Internals de Sasha Pachev

    Compreendendo os Internos do MySQL

    aqui está o detalhamento da avaliação de uma consulta como as seguintes tarefas:

    • Determine quais chaves podem ser usadas para recuperar os registros das tabelas e escolha a melhor para cada tabela.
    • Para cada tabela, decida se uma varredura de tabela é melhor do que a leitura de uma chave. Se houver muitos registros que correspondem ao valor da chave, as vantagens da chave são reduzidas e a verificação da tabela torna-se mais rápida.
    • Determine a ordem na qual as tabelas devem ser unidas quando mais de uma tabela estiver presente na consulta.
    • Reescreva as cláusulas WHERE para eliminar o código morto, reduzindo os cálculos desnecessários e alterando as restrições sempre que possível para abrir o caminho para o uso de chaves.
    • Elimine tabelas não utilizadas da junção.
    • Determine se as chaves podem ser usadas para ORDER BYe GROUP BY.
    • Tente simplificar as subconsultas, bem como determinar até que ponto seus resultados podem ser armazenados em cache.
    • Mesclar exibições (expandir a referência de exibição como uma macro)

    Na mesma página, diz o seguinte:

    Na terminologia do otimizador MySQL, cada consulta é um conjunto de junções. O termo junção é usado aqui de forma mais ampla do que em comandos SQL. Uma consulta em apenas uma tabela é uma junção degenerada. Embora normalmente não pensemos na leitura de registros de uma tabela como uma junção, as mesmas estruturas e algoritmos usados ​​com junções convencionais funcionam perfeitamente para resolver a consulta com apenas uma tabela.

    EPÍLOGO

    Por causa das chaves presentes, a quantidade de dados e a expressão da consulta, o MySQL Joins às vezes pode fazer coisas para nosso próprio bem (ou para se vingar de nós) e obter resultados que não esperávamos e não podemos explicar rapidamente.

    Eu escrevi sobre essa estranheza antes

    porque o MySQL Query Optimizer pode descartar certas chaves durante a avaliação da consulta.

    O comentário de @Phil me ajuda a ver como postar esta resposta (+1 para o comentário de @Phil)

    O comentário de @ypercube (+1 para este também) é uma versão compacta do meu post porque o Query Optimizer do MySQL é primitivo. Infelizmente, tem que ser, pois lida com mecanismos de armazenamento externos.

    CONCLUSÃO

    Quanto à sua pergunta real, o MySQL Query Optimizer determinaria as métricas de desempenho de cada consulta quando ela for concluída

    • contando linhas
    • selecionando chaves
    • massageando conjuntos de resultados intermitentes
    • Ah, sim, fazendo o JOIN real

    Você provavelmente teria que coagir a ordem de execução reescrevendo (refatorando) a consulta

    Aqui está a primeira consulta que você deu

    select count(*)
from   table1 a
join   table2 b
on     b.key_col=a.key_col
where  b.tag = 'Y';

    Tente reescrevê-lo para avaliar o WHERE primeiro

    select count(*)
from   table1 a
join   (select key_col from table2 where tag='Y') b
on     b.key_col=a.key_col;

    Isso definitivamente alteraria o plano EXPLAIN. Pode produzir resultados melhores ou piores.

    Certa vez, respondi a uma pergunta no StackOverflow onde apliquei essa técnica. O EXPLAIN foi horrível, mas o desempenho foi dinamite. Só funcionou por ter os índices corretos presentes e pelo uso de LIMIT em uma subconsulta .

    Tal como acontece com os preços das ações, quando se trata de consultas e tentando expressá-las, as restrições se aplicam, os resultados podem variar e o desempenho passado não é indicativo de resultados futuros.

    • 14

  2. Para o Oracle, como o mySQL tinha uma descrição longa, temos 2 maneiras de alto nível de alavancar o otimizador.

    A primeira é a Otimização Baseada em Regras (ou RBO). A Oracle tem 15 regras imutáveis ​​que cada consulta que analisa tenta seguir em uma ordem definida. Se não puder gerar uma consulta otimizada a partir da regra 1, ele avançará para a regra 2 e assim por diante até atingir a regra 15.

    para mais informações: https://docs.oracle.com/cd/B10500_01/server.920/a96533/rbo.htm

    Isso afeta os kernels Oracle RDBMS de 11.1 e anteriores que não foram convertidos para o Cost Based Optimizer (também conhecido como CBO). O Oracle 11.2 e superior exigem o otimizador CBO, mas podem forçar IDs Sql específicos a serem otimizados no antigo método RBO, se o usuário desejar.

    Em vez disso, o CBO para Oracle 11.1+ faz vários planos de execução para o mesmo ID de SQL e executa aquele com o menor custo total antecipado. Ele aproveita muito da lógica do RBO, mas analisa as estatísticas da tabela para criar custos de plano de execução dinâmicos para cada operação que o banco de dados precisa fazer para fornecer seus dados ao usuário final. A execução de varreduras completas em tabelas muito grandes é realmente cara; executar verificações de tabela completa em uma tabela com 10 linhas é barato. Na RBO foram consideradas operações iguais.

    para mais informações: https://oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics

    Para seu exemplo de consulta específico: a Oracle provavelmente analisaria as informações para fazer diferentes planos de execução e, portanto, um seria tecnicamente melhor que o outro. No entanto, isso pode ser uma diferença mínima. Observando-o, tanto o Oracle RBO quanto o CBO gostariam de consultar mais 1 porque está executando em uma junção em menos condições e, em seguida, filtrando uma coluna específica da tabela temporária que ela criou a partir da junção.

    • 1

  3. Se você tiver duas consultas e achar que são equivalentes, pode acontecer o seguinte:

    1. Ambas as consultas têm o mesmo plano de execução. Isso é bom e é isso que esperamos. Esperemos que seja o plano de execução ideal para a consulta.

    2. existem diferentes planos de execução. Temos dois subcasos aqui.

      2.1 As consultas têm planos de execução diferentes, mas ambos os planos funcionam igualmente bem. Isso também é bom. Não há necessidade de que para consultas equivalentes seja gerado o mesmo plano. Mas o desempenho deve ser igual. E novamente esperamos que seja o melhor possível.

      2.2 As consultas possuem diferentes planos de execução e um plano é melhor que o outro. Novamente temos subcasos:

      2.2.1 Os planos são diferentes porque as consultas não são equivalentes. Portanto, verifique cuidadosamente se eles são realmente equivalentes. No seu caso, eles realmente são equivalentes.

      2.2.2 Os planos são diferentes mas as consultas são equivalentes. Isso significa que o otimizador não está maduro o suficiente. Em um mundo perfeito com otimizadores perfeitos, isso não deveria acontecer. Portanto, sim, depende da plataforma e você deve estudar os documentos específicos da plataforma para descobrir por que isso acontece.

      2.2.3 Os planos são diferentes, as consultas são equivalentes, o software de banco de dados tem um bug.

    • 1

relate perguntas