Início / dba / Perguntas / 15800
Accepted
codecool
Asked: 2012-03-30 06:34:36 +0800 CST

Group_by e tendo vs Joins quando a tabela é auto-juntada 5-6 vezes

  • 772

Agora a situação devido à qual esta pergunta veio a mim. esquema é

    Table User_Read_Book
       user_id | book_id

Agora, quero obter usuários que leram determinados livros. Diga, dê-me usuários que leram o livro número 1 e 2. O número de livros para testar pode ir até 10.

Primeira consulta que escrevi:

Select user_id from User_Read_Book Where book_id In (1,2) Group by user_id Having count(book_id) = 2

Segunda consulta:

Select  user_id from User_Read_Book as U join User_Read_Book as U1 On
 U.user_id = U1.user_id And U1.book_id = 1 where U.book_id = 2

Como dito nesta resposta https://stackoverflow.com/a/621891 que preferem joins em caso de group by e tendo feito a segunda consulta.

Mas minha pergunta é qual é a melhor consulta quando o número a ser correspondido é grande. Diga quando você precisa encontrar usuários que leram 7 livros

   Having Count(book_id) = 7
   or
   6 joins to the same table.

Eu sei que esta pergunta é melhor respondida quando testada em dados grandes e em tempo real. Ainda assim, qual é a opinião dos especialistas sobre isso?

database-design performance

2 respostas

  1. Best Answer

    Com 7 livros, meu palpite seria que 7 junções são mais rápidas que GROUP BY / HAVING.

    Mas depende do DBMS, da versão, das configurações do otimizador, das configurações do banco de dados, da RAM que você possui, do desempenho dos discos rígidos, da fragmentação dos índices, da pressão geral no servidor e possivelmente de vários outros parâmetros. Ainda mais, mesmo que todos os anteriores estejam definidos, depende dos seus dados (e sua distribuição) e dos parâmetros específicos da consulta. Por exemplo, se os 7 livros são os 7 livros de Harry Potter e todos os seus usuários são fãs de Harry Potter, então o GROUP BY/HAVINGpode ser mais rápido.

    Além disso, você não deve confiar nos outros, por mais especialistas que pareçam ser, quando você pode testar. Por que você não testa - com seus dados em seu servidor e suas configurações - o desempenho de ambas as formas, usando número variável de livros (e títulos)?

    Verifique também esta questão (com uma consulta semelhante) onde várias outras (mais de 10) maneiras são mostradas (e comparadas no PostgrSQL): Como filtrar resultados SQL em uma relação has-many-through

    ATUALIZAR

    explicação do "palpite" de que 7 Joins geralmente são melhores que GROUP BY / HAVING:

    Imagine que você tem um milhão de usuários e cerca de um milhão de livros. Agora, em média, um usuário já leu 100 livros (em seu banco de dados, dados totalmente fictícios e distribuição). Portanto, a tabela tem cerca de 100 milhões de linhas.

    Agora, a GROUP BYconsulta teria algo como WHERE book_id IN (1,2,3,4,5,6,7). Vamos assumir que book_id=1é o mais popular (a Bíblia) e tem cerca de 100 mil leitores e os outros 6 não são tão populares, tendo entre 100 a 1000 leitores cada. Isso limitaria as linhas a serem agrupadas a algo entre 100K e 106K. Isso se traduz (aproximadamente) no mecanismo SQL lendo dados de 106K do índice adequado e, em seguida, fazendo o arquivo GROUP BY user_id. Portanto, (ele provavelmente escolherá usar o (user_id, book_id)índice) e fará cerca de 100 mil cálculos de COUNT(book_id)- e rejeitará qualquer um que não seja 7.

    Na JOINconsulta 7 tem mais opções. O otimizador pode optar por usar o outro índice, o (book_id, user_id)um. Imagine "retirar" 7 partes menores desse grande índice, a (1, user_id)parte (lembre-se: 100K de dados (user_ids) nele), a (2, user_id)parte (menos de 1000 dados aqui), ..., até a (7, user_id)parte (menos de 1000 dados aqui também). Portanto, agora, ele precisa combinar de alguma forma essas 7 partes de índice (que são apenas 7 listas de IDs de usuário) e descobrir quais IDs de usuário estão em TODAS as 7 listas. Existem alguns algoritmos inteligentes que fazem exatamente isso, semter que fazer uma leitura completa (full scan) das 7 listas. Observe que mesmo um algoritmo burro que combina primeiro as 6 listas menores pode acabar com apenas um punhado de IDs de usuário (digamos, apenas 1). Para descobrir se este 1 user_id está na grande (primeira) lista, apenas uma pesquisa binária é necessária (lembre-se de que não é realmente uma lista, é um índice e isso é o que há de bom nos índices, você pode pesquisar rapidamente neles). Portanto, mesmo que haja apenas 100 user_ids, 100 pesquisas na grande lista/índice de 100K precisarão apenas de menos de 100*17 operações ( log(100K) ~= 17). Que são 1700 operações, muito menos que as GROUP BY100 mil operações. E não COUNT(*)é necessário.

    Portanto, com os Joins, se a maioria dos livros não for muito popular (ou apenas um livro e tivermos sorte), a consulta será bastante eficiente porque terá que olhar o índice em um número muito pequeno de lugares.
    (Outro pensamento é que com o método Group By, a consulta calculou - antes de rejeitá-los - quantos livros leram todos os usuários que leram 1 ou 2 ou ... ou 6 livros. Mas não nos importamos se eles leia 1 ou 6. Só precisamos saber se eles leram todos os 7!)

    A situação é diferente se todos os 7 livros escolhidos forem muito populares. Agora, as 7 partes do índice são todas grandes e combiná-las pode ser menos eficiente do que usar o GROUP BYmétodo que usa apenas uma passagem em um índice.
    (E o outro pensamento diz que Group By é eficiente agora porque quase todos os cálculos de contagem serão um 7, portanto, um número muito pequeno de cálculos é desperdiçado)

    • 4

  2. atuação

    Depende MUITO dos dados reais (existem muitas pessoas que leram alguns livros cada, ou algumas pessoas que leram muitos livros), distorção (existem alguns leitores avançados?) E os livros que você consulta - como o ypercube mencionado a Bíblia.

    E isso é antes de o otimizador realmente entrar em ação e decidir reescrever completamente sua consulta, porque entende que algo pode ser mais rápido....

    Em princípio, os Joins múltiplos provavelmente farão seleções múltiplas na Tabela, cada vez obtendo um conjunto de Usuários para cada livro e, em seguida, fazendo uma interseção entre esses conjuntos para encontrar os usuários presentes em todos os conjuntos. Ou ele primeiro selecionará todos os usuários para um livro e quando a lista resultante for pequena o suficiente e o índice correto estiver presente, a consulta usará o índice para verificar cada um dos usuários individuais da primeira lista para todos os livros que leram.

    A cláusula Group-By/Having provavelmente resultará em um grande número de acessos de índice para todas as linhas que contêm um dos livros e, em seguida, agrupará e contará. Ou para um grande número de livros, provavelmente resultará em uma varredura completa da tabela e apenas somará todas as linhas relevantes, resultando na lista de usuários.

    Então, meu palpite seria - se você espera um grande número de usuários na lista de resultados e está procurando por uma combinação de livros que muitos de seus usuários leram, uma varredura completa da tabela provavelmente será mais rápida em IO (o consumo de memória deve ser insignificante...) -- Se, por outro lado, você tiver livros bastante especiais e raramente lidos em sua lista e/ou o conjunto de acessos resultante for um número muito pequeno de usuários, o acesso múltiplo JOIN provavelmente será mais rápido se o otimizador puder reduzi-lo rapidamente e não precisar de tantos acessos IO...

    No geral, o fator predominante no desempenho provavelmente será o número de leituras de disco e os locais reais dessas leituras (não consecutivas) e um grande número de acessos de índice pode prejudicar seu desempenho, mesmo que o algoritmo com várias junções deva em teoria ser mais rápido.

    MAS , como ypercube disse, a única maneira correta de resolver isso é executar benchmarks em dados reais (não dados preparados, mas dados muito próximos dos dados reais/esperados do cliente)

    Usabilidade / Manutenibilidade

    Se você me perguntar o que eu usaria no código de produção real? Claramente, a única opção viável é a alternativa GROUP BY/HAVING, já que é flexível (você pode simplesmente vincular uma matriz/lista como a variável e pesquisar um número aleatório de livros) e também pode pesquisar 5 de 7 e assim sobre. Com a solução de junção, você teria 7 consultas diferentes em seu código, cada uma para vários livros... e altamente inflexíveis para outros casos de uso

    Além disso, quando escrita em código, a solução GROUP BY/HAVING é altamente idiomática. Com comentários e formatação um pouco mais claros, qualquer programador entenderá imediatamente a consulta. A monstruosidade SELF-JOIN será um pouco mais difícil de entender e manter...

    • 2

relate perguntas