Subconsultas vs junções

Refatorei uma seção lenta de um aplicativo que herdamos de outra empresa para usar uma junit interna em vez de uma subconsulta como

where id in (select id from ... ) 

A consulta refatorada é executada aproximadamente 100x mais rápido. (~ 50 segundos a ~ 0,3) Eu esperava uma melhora, mas alguém pode explicar por que foi tão drástico? As colunas usadas na cláusula where foram todas indexadas. O SQL executa a consulta na cláusula where uma vez por linha ou algo assim?

Atualização – Explique os resultados:

A diferença está na segunda parte da consulta “where id in ()” –

 2 DEPENDENT SUBQUERY submission_tags ref st_tag_id st_tag_id 4 const 2966 Using where 

vs 1 linha indexada com a junit:

  SIMPLE s eq_ref PRIMARY PRIMARY 4 newsladder_production.st.submission_id 1 Using index 

Uma “subconsulta correlacionada” (ou seja, aquela em que a condição where depende dos valores obtidos das linhas da consulta contida) será executada uma vez para cada linha. Uma subconsulta não correlacionada (uma na qual a condição where é independente da consulta que contém) será executada uma vez no início. O mecanismo SQL faz essa distinção automaticamente.

Mas sim, o plano explicativo lhe dará os detalhes sujos.

Você está executando a subconsulta uma vez para cada linha, enquanto a junit acontece nos índices.

Aqui está um exemplo de como as subconsultas são avaliadas no MySQL 6.0 .

O novo otimizador converterá esse tipo de subconsultas em junções.

Execute o plano de explicação em cada versão, ele dirá o porquê.

antes que as consultas sejam executadas no dataset, elas são colocadas por meio de um otimizador de consulta, e o otimizador tenta organizar a consulta de maneira que ela possa remover quantas tuplas (linhas) do conjunto de resultados, o mais rápido possível. Muitas vezes, quando você usa subconsultas (especialmente as ruins), as tuplas não podem ser removidas do conjunto de resultados até que a consulta externa comece a ser executada.

Sem ver a consulta, é difícil dizer o que era tão ruim em relação ao original, mas meu palpite seria que o otimizador não poderia melhorar muito. Executar ‘explain’ mostrará o método dos otimizadores para recuperar os dados.

Geralmente, é o resultado de o otimizador não conseguir descobrir que a subconsulta pode ser executada como uma junit. Nesse caso, ela executa a subconsulta de cada registro na tabela, em vez de unir a tabela na subconsulta à tabela que você está consultando. Alguns dos bancos de dados mais “enterprisey” são melhores nisso, mas eles ainda sentem falta disso às vezes.

Esta questão é um pouco geral, então aqui está uma resposta geral:

Basicamente, as consultas levam mais tempo quando o MySQL tem toneladas de linhas para classificar.

Faça isso:

Execute um EXPLAIN em cada uma das consultas (a que foi unida, depois a subconsultada), e poste os resultados aqui.

Acho que ver a diferença na interpretação do MySQL dessas consultas seria uma experiência de aprendizado para todos.

A subconsulta where precisa executar 1 consulta para cada linha retornada. A junit interna só precisa executar uma consulta.

Veja o plano de consulta para cada consulta.

Onde in e Join normalmente podem ser implementados usando o mesmo plano de execução, então tipicamente não há aceleração zero na mudança entre eles.

Otimizador não fez um bom trabalho. Geralmente eles podem ser transformados sem qualquer diferença e o otimizador pode fazer isso.

A subconsulta provavelmente estava executando uma “verificação completa da tabela”. Em outras palavras, não usando o índice e retornando muitas linhas que o Where da consulta principal estava precisando filtrar.

Apenas um palpite sem detalhes, claro, mas essa é a situação comum.

Com uma subconsulta, você precisa executar novamente o segundo SELECT para cada resultado, e cada execução normalmente retorna 1 linha.

Com uma junit, o segundo SELECT retorna muito mais linhas, mas você só precisa executá-lo uma vez. A vantagem é que agora você pode juntar-se aos resultados, e juntar relações é o que um database deve ser bom. Por exemplo, talvez o otimizador possa identificar como aproveitar melhor um índice agora.

Não é tanto a subconsulta quanto a cláusula IN, embora as junções estejam na base do mecanismo de SQL do Oracle e sejam executadas com extrema rapidez.

Extraído do Manual de Referência ( 14.2.10.11 Reescrevendo Subconsultas como Junções ):

Um LEFT [OUTER] JOIN pode ser mais rápido que uma subconsulta equivalente, porque o servidor pode otimizá-lo melhor – um fato que não é específico do MySQL Server sozinho.

Portanto, as subconsultas podem ser mais lentas que as LEFT [OUTER] JOINS.