Consultas de JOIN vs várias consultas

As consultas de JOIN são mais rápidas que várias consultas? (Você executa sua consulta principal e executa muitos outros SELECTs com base nos resultados de sua consulta principal)

Estou perguntando porque JOINING eles complicariam MUITO o design do meu aplicativo

Se eles são mais rápidos, alguém pode aproximar-se muito de quanto? Se é 1,5x eu não me importo, mas se é 10x eu acho que sim.

Isso é muito vago para lhe dar uma resposta relevante para o seu caso específico. Isso depende de muitas coisas. Jeff Atwood (fundador deste site) escreveu sobre isso . Na maior parte, porém, se você tiver os índices corretos e fizer corretamente seus JOINs, normalmente será mais rápido fazer uma viagem do que vários.

Para junções internas, uma única consulta faz sentido, já que você só recebe linhas correspondentes. Para junções à esquerda, várias consultas são muito melhores … veja o seguinte benchmark que fiz:

  1. Consulta única com 5 junções

    consulta: 8.074508 segundos

    tamanho do resultado: 2268000

  2. 5 consultas consecutivas

    tempo de consulta combinado: 0,00262 segundos

    tamanho do resultado: 165 (6 + 50 + 7 + 12 + 90)

.

Observe que obtemos os mesmos resultados em ambos os casos (6 x 50 x 7 x 12 x 90 = 2268000)

junções esquerdas usam exponencialmente mais memory com dados redundantes.

O limite de memory pode não ser tão ruim se você fizer apenas uma junit de duas tabelas, mas geralmente três ou mais e valer consultas diferentes.

Como uma nota lateral, meu servidor MySQL está bem ao lado do meu servidor de aplicativos … então o tempo de conexão é insignificante. Se o seu tempo de conexão é nos segundos, então talvez haja um benefício

Frank

Eu realmente cheguei a essa pergunta procurando por uma resposta, e depois de ler as respostas dadas eu só posso concordar que a melhor maneira de comparar o desempenho das consultas do database é obter números do mundo real porque há apenas muitas variables ​​a serem consideradas MAS, também acho que comparar os números entre eles não é bom em quase todos os casos. O que quero dizer é que os números devem sempre ser comparados com um número aceitável e definitivamente não comparados uns com os outros.

Eu posso entender se uma maneira de consultar demora 0,02 segundos e a outra demora 20 segundos, isso é uma enorme diferença. Mas e se uma maneira de consultar demora 0,0000000002 segundos e a outra demora 0,0000002 segundos? Em ambos os casos, uma maneira é uma colossal 1000 vezes mais rápida que a outra, mas ela ainda é realmente “gritante” no segundo caso?

Bottom line, como eu pessoalmente vejo: se ele funciona bem, vá para a solução fácil.

Fiz um teste rápido selecionando uma linha de uma tabela de 50.000 linhas e juntando-se a uma linha de uma tabela de 100.000 linhas. Basicamente parecia:

$id = mt_rand(1, 50000); $row = $db->fetchOne("SELECT * FROM table1 WHERE id = " . $id); $row = $db->fetchOne("SELECT * FROM table2 WHERE other_id = " . $row['other_id']); 

vs

 $id = mt_rand(1, 50000); $db->fetchOne("SELECT table1.*, table2.* FROM table1 LEFT JOIN table1.other_id = table2.other_id WHERE table1.id = " . $id); 

O método two select levou 3,7 segundos para 50.000 leituras, enquanto o JOIN demorou 2,0 segundos no meu computador lento caseiro. O INNER JOIN e o LEFT JOIN não fizeram diferença. Buscar várias linhas (por exemplo, usando IN SET) produziu resultados semelhantes.

Construa consultas e junções separadas, depois calcule cada uma delas – nada ajuda mais que números do mundo real.

Então, melhor ainda – adicione “EXPLAIN” ao início de cada consulta. Isto lhe dirá quantas subconsultas o MySQL está usando para responder sua requisição de dados, e quantas linhas serão verificadas para cada consulta.

Dependendo da complexidade do database comparado à complexidade do desenvolvedor, pode ser mais simples fazer muitas chamadas SELECT.

Tente executar algumas statistics do database no JOIN e nos diversos SELECTS. Veja se no seu ambiente o JOIN é mais rápido / mais lento que o SELECT.

Então, novamente, se mudá-lo para um JOIN significaria um dia extra / semana / mês de trabalho dev, eu ficaria com vários SELECTs

Felicidades,

BLT

A verdadeira questão é: esses registros têm um relacionamento um-para-um ou um relacionamento um-para-muitos ?

Resposta do TLDR:

Se um-para-um, use uma instrução JOIN .

Se um-para-muitos, use uma (ou várias) instruções SELECT com a otimização de código do lado do servidor.

Por que e como usar SELECT for Optimization

SELECT ‘ing (com várias consultas em vez de junções) em um grande grupo de registros com base em um relacionamento um-para-muitos produz uma eficiência ideal, pois JOIN ‘ ing tem um problema de memory leaks exponencial. Pegue todos os dados e use uma linguagem de script do lado do servidor para resolver:

 SELECT * FROM Address WHERE Personid IN(1,2,3); 

Resultados:

 Address.id : 1 // First person and their address Address.Personid : 1 Address.City : "Boston" Address.id : 2 // First person's second address Address.Personid : 1 Address.City : "New York" Address.id : 3 // Second person's address Address.Personid : 2 Address.City : "Barcelona" 

Aqui, eu estou recebendo todos os registros, em uma declaração select. Isso é melhor do que JOIN , que estaria recebendo um pequeno grupo desses registros, um de cada vez, como um subcomponente de outra consulta. Então eu analiso com código do lado do servidor que parece algo como …

 Address[] = $address; } ?> 

Quando não usar o JOIN para otimização

JOIN um grande grupo de registros com base em um relacionamento um-para-um com um único registro produz uma eficiência ideal em comparação com várias instruções SELECT , uma após a outra, que simplesmente obtêm o próximo tipo de registro.

Mas o JOIN é ineficiente ao obter registros com um relacionamento um-para-muitos.

Exemplo: O database Blogs tem 3 tabelas de interesse, Blogpost, Tag e Comment.

 SELECT * from BlogPost LEFT JOIN Tag ON Tag.BlogPostid = BlogPost.id LEFT JOIN Comment ON Comment.BlogPostid = BlogPost.id; 

Se houver 1 postagem de blog, 2 tags e 2 comentários, você obterá resultados como:

 Row1: tag1, comment1, Row2: tag1, comment2, Row3: tag2, comment1, Row4: tag2, comment2, 

Observe como cada registro é duplicado. Ok, então, 2 comentários e 2 tags são 4 linhas. E se tivermos 4 comentários e 4 tags? Você não obtém 8 linhas – você obtém 16 linhas:

 Row1: tag1, comment1, Row2: tag1, comment2, Row3: tag1, comment3, Row4: tag1, comment4, Row5: tag2, comment1, Row6: tag2, comment2, Row7: tag2, comment3, Row8: tag2, comment4, Row9: tag3, comment1, Row10: tag3, comment2, Row11: tag3, comment3, Row12: tag3, comment4, Row13: tag4, comment1, Row14: tag4, comment2, Row15: tag4, comment3, Row16: tag4, comment4, 

Adicione mais tabelas, mais registros, etc., e o problema se inflará rapidamente em centenas de linhas, todas repletas de dados redundantes.

O que essas duplicatas custam a você? Memória (no servidor SQL e o código que tenta remover as duplicatas) e resources de rede (entre o servidor SQL e seu servidor de código).

Fonte: https://dev.mysql.com/doc/refman/8.0/en/nested-join-optimization.html ; https://dev.mysql.com/doc/workbench/en/wb-relationship-tools.html

Na minha experiência, descobri que normalmente é mais rápido executar várias consultas, especialmente ao recuperar grandes conjuntos de dados.

Ao interagir com o database a partir de outro aplicativo, como o PHP, há o argumento de uma viagem ao servidor sobre muitos.

Existem outras maneiras de limitar o número de viagens feitas ao servidor e ainda executar várias consultas que muitas vezes não são apenas mais rápidas, mas também facilitam a leitura do aplicativo – por exemplo, mysqli_multi_query.

Eu não sou novato quando se trata de SQL, eu acho que há uma tendência para os desenvolvedores, especialmente juniores para gastar muito tempo tentando escrever joins muito inteligentes, porque eles parecem inteligentes, enquanto que existem maneiras realmente inteligentes para extrair dados que parecem simples.

O último parágrafo foi uma opinião pessoal, mas espero que isso ajude. Eu concordo com os outros, embora digam que você deveria fazer benchmark. Nenhuma das abordagens é uma bala de prata.

Será mais rápido em termos de rendimento? Provavelmente. Mas também bloqueia potencialmente mais objects de database de cada vez (dependendo do database e do esquema) e, portanto, diminui a simultaneidade. Na minha experiência, muitas vezes as pessoas são enganadas pelo argumento “menos viagens de ida e volta ao database” quando, na realidade, na maioria dos sistemas OLTP em que o database está na mesma LAN, o verdadeiro gargalo raramente é a rede.

Aqui está um link com 100 consultas úteis, estas são testadas em database Oracle mas lembre-se SQL é um padrão, o que diferem entre Oracle, MS SQL Server, MySQL e outros bancos de dados são o dialeto SQL:

http://javaforlearn.com/100-sql-queries-learn/

Existem vários fatores, o que significa que não há resposta binária. A questão do que é melhor para desempenho depende do seu ambiente. A propósito, se a sua seleção única com um identificador não for sub-segundo, algo pode estar errado com sua configuração.

A verdadeira questão a ser feita é como você deseja acessar os dados. Único seleciona suporte de binding tardia. Por exemplo, se você deseja apenas informações sobre funcionários, pode selecionar na tabela Empregados. Os relacionamentos de chave estrangeira podem ser usados ​​para recuperar resources relacionados posteriormente e conforme necessário. Os selects já terão uma chave para apontar para que eles sejam extremamente rápidos, e você só precisa recuperar o que precisa. A latência da rede deve sempre ser levada em conta.

As junções recuperarão todos os dados de uma só vez. Se você estiver gerando um relatório ou preenchendo uma grade, isso pode ser exatamente o que você deseja. Associações compiladas e optomizadas serão simplesmente mais rápidas do que as seleções únicas nesse cenário. Lembre-se, junções Ad-hoc podem não ser tão rápidas – você deve compilá-las (em um procedimento armazenado). A resposta da velocidade depende do plano de execução, que detalha exatamente quais etapas o DBMS executa para recuperar os dados.

Sim, uma consulta usando JOINS seria mais rápida. Embora sem conhecer as relações das tabelas que você está consultando, o tamanho do seu dataset ou onde estão as chaves primárias, é quase impossível dizer quanto mais rápido.

Por que não testar ambos os cenários, então você saberá com certeza …

Se você deve usar uma associação é, antes de tudo, saber se uma associação faz sentido . Somente nesse ponto o desempenho é algo que deve ser considerado, já que quase todos os outros casos resultarão em um desempenho significativamente pior .

As diferenças de desempenho serão, em grande parte, vinculadas à forma como as informações que você está pesquisando estão relacionadas. As junções funcionam, e são rápidas quando os dados são relacionados e você indexa as coisas corretamente, mas geralmente resultam em alguma redundância e, às vezes, em mais resultados do que o necessário. E se os seus conjuntos de dados não estiverem diretamente relacionados, colá-los em uma única consulta resultará no que é chamado de um produto cartesiano (basicamente, todas as combinações possíveis de linhas), o que quase nunca é o que você deseja.

Isso geralmente é causado por relacionamentos muitos-para-um-para-muitos. Por exemplo, a resposta da HoldOffHunger mencionou uma única consulta para postagens, tags e comentários. Os comentários estão relacionados a uma postagem, assim como as tags … mas as tags não estão relacionadas a comentários.

 +------------+ +---------+ +---------+ | comment | | post | | tag | |------------|* 1|---------|1 *|---------| | post_id |-----| post_id |-----| post_id | | comment_id | | ... | | tag_id | | user_id | | | | ... | | ... | | | | ... | +------------+ +---------+ +---------+ 

Nesse caso, é inequivocamente melhor que isso seja pelo menos duas consultas separadas. Se você tentar juntar tags e comentários, porque não há relação direta entre os dois, você acaba com todas as combinações possíveis de tags e comentários. many * many == manymany . Além disso, como as postagens e as tags não são relacionadas, você pode fazer essas duas consultas em paralelo, gerando ganhos em potencial.

Vamos considerar um cenário diferente: Você quer os comentários anexados a um post e as informações de contato dos comentadores.

  +----------+ +------------+ +---------+ | user | | comment | | post | |----------|1 *|------------|* 1|---------| | user_id |-----| post_id |-----| post_id | | username | | user_id | | ... | | ... | | ... | +---------+ +----------+ +------------+ 

É aqui que você deve considerar uma união. Além de ser uma consulta muito mais natural, a maioria dos sistemas de database (incluindo o MySQL) tem muitas pessoas inteligentes que trabalham muito para otimizar as consultas como ele. Para consultas separadas, uma vez que cada consulta depende dos resultados da anterior, as consultas não podem ser feitas em paralelo e o tempo total torna-se não apenas o tempo real de execução das consultas, mas também o tempo gasto na busca de resultados, peneiramento através deles para IDs para a próxima consulta, ligando linhas juntas, etc.