Qual é o problema da consulta N + 1 SELECT?

SELECT N + 1 é geralmente declarado como um problema nas discussões de Mapeamento Objeto-Relacional (ORM), e eu entendo que ele tem algo a ver com ter que fazer muitas consultas de database para algo que parece simples no mundo dos objects.

Alguém tem uma explicação mais detalhada do problema?

   

Digamos que você tenha uma coleção de objects Car (linhas do database) e cada Car tenha uma coleção de objects Wheel (também linhas). Em outras palavras, Car -> Wheel é um relacionamento de 1 para muitos.

Agora, digamos que você precisa percorrer todos os carros e, para cada um, imprimir uma lista das rodas. A implementação ingênua do O / R faria o seguinte:

 SELECT * FROM Cars; 

E então para cada Car :

 SELECT * FROM Wheel WHERE CarId = ? 

Em outras palavras, você tem um select para os carros, e depois N selects adicionais, onde N é o número total de carros.

Como alternativa, pode-se obter todas as rodas e realizar as pesquisas na memory:

 SELECT * FROM Wheel 

Isso reduz o número de viagens de ida e volta ao database de N + 1 para 2. A maioria das ferramentas ORM oferece várias maneiras de evitar as seleções de N + 1.

Referência: Java Persistence with Hibernate , capítulo 13.

 SELECT table1.* , table2.* INNER JOIN table2 ON table2.SomeFkId = table1.SomeId 

Isso obtém um conjunto de resultados em que as linhas filhas na table2 causam duplicação, retornando os resultados table1 para cada linha filha na tabela2. Os mapeadores de O / R devem diferenciar instâncias de table1 com base em um campo-chave exclusivo e, em seguida, usar todas as colunas table2 para preencher instâncias-filho.

 SELECT table1.* SELECT table2.* WHERE SomeFkId = # 

O N + 1 é onde a primeira consulta preenche o object principal e a segunda consulta preenche todos os objects filhos para cada um dos objects primários exclusivos retornados.

Considerar:

 class House { int Id { get; set; } string Address { get; set; } Person[] Inhabitants { get; set; } } class Person { string Name { get; set; } int HouseId { get; set; } } 

e tabelas com estrutura semelhante. Uma única consulta para o endereço “22 Valley St” pode retornar:

 Id Address Name HouseId 1 22 Valley St Dave 1 1 22 Valley St John 1 1 22 Valley St Mike 1 

O O / RM deve preencher uma instância de Home com ID = 1, Address = “22 Valley St” e preencher a matriz Inhabitants com instâncias People para Dave, John e Mike com apenas uma consulta.

Uma consulta N + 1 para o mesmo endereço usado acima resultaria em:

 Id Address 1 22 Valley St 

com uma consulta separada como

 SELECT * FROM Person WHERE HouseId = 1 

e resultando em um dataset separado como

 Name HouseId Dave 1 John 1 Mike 1 

e o resultado final é o mesmo que acima com a consulta única.

As vantagens para selecionar único é que você obtenha todos os dados na frente, que pode ser o que você deseja em última instância. As vantagens para o N + 1 é que a complexidade da consulta é reduzida e você pode usar o carregamento lento onde os conjuntos de resultados filhos são carregados somente na primeira solicitação.

Fornecedor com uma relação um-para-muitos com o Produto. Um fornecedor tem (fornece) muitos produtos.

 ***** Table: Supplier ***** +-----+-------------------+ | ID | NAME | +-----+-------------------+ | 1 | Supplier Name 1 | | 2 | Supplier Name 2 | | 3 | Supplier Name 3 | | 4 | Supplier Name 4 | +-----+-------------------+ ***** Table: Product ***** +-----+-----------+--------------------+-------+------------+ | ID | NAME | DESCRIPTION | PRICE | SUPPLIERID | +-----+-----------+--------------------+-------+------------+ |1 | Product 1 | Name for Product 1 | 2.0 | 1 | |2 | Product 2 | Name for Product 2 | 22.0 | 1 | |3 | Product 3 | Name for Product 3 | 30.0 | 2 | |4 | Product 4 | Name for Product 4 | 7.0 | 3 | +-----+-----------+--------------------+-------+------------+ 

Fatores:

  • Modo preguiçoso para o fornecedor definido como “true” (padrão)

  • O modo de busca usado para consulta no produto é selecionado

  • Modo de busca (padrão): as informações do fornecedor são acessadas

  • O cache não desempenha um papel pela primeira vez

  • O fornecedor é acessado

O modo de busca é Selecionar busca (padrão)

 // It takes Select fetch mode as a default Query query = session.createQuery( "from Product p"); List list = query.list(); // Supplier is being accessed displayProductsListWithSupplierName(results); select ... various field names ... from PRODUCT select ... various field names ... from SUPPLIER where SUPPLIER.id=? select ... various field names ... from SUPPLIER where SUPPLIER.id=? select ... various field names ... from SUPPLIER where SUPPLIER.id=? 

Resultado:

  • 1 declaração selecionada para o produto
  • N selecione as declarações para o fornecedor

Este é N + 1 problema selecione!

Não posso comentar diretamente sobre outras respostas, porque não tenho reputação suficiente. Mas vale a pena notar que o problema essencialmente surge apenas porque, historicamente, muitos dbms têm sido bastante ruins quando se trata de manipular joins (o MySQL é um exemplo particularmente notável). Então, n + 1 tem sido, muitas vezes, notavelmente mais rápido que uma junit. E há maneiras de melhorar n + 1, mas ainda sem precisar de uma junit, que é o problema original.

No entanto, o MySQL é agora muito melhor do que costumava ser quando se trata de junções. Quando eu aprendi o MySQL pela primeira vez, eu usei bastante. Então eu descobri o quão lento eles são, e mudei para n + 1 no código. Mas, recentemente, eu tenho me mudado de volta para joins, porque o MySQL agora é muito melhor em lidar com eles do que quando comecei a usá-lo.

Atualmente, uma junit simples em um conjunto de tabelas indexadas corretamente raramente é um problema, em termos de desempenho. E, se isso der certo, o uso de dicas de índice geralmente as resolve.

Isso é discutido aqui por uma das equipes de desenvolvimento do MySQL:

http://jorgenloland.blogspot.co.uk/2013/02/dbt-3-q3-6-x-performance-in-mysql-5610.html

Portanto, o resumo é: Se você está evitando joins no passado por causa do desempenho abismal do MySQL com eles, tente novamente nas versões mais recentes. Você provavelmente ficará agradavelmente surpreso.

Nós nos afastamos do ORM no Django por causa desse problema. Basicamente, se você tentar e

 for p in person: print p.car.colour 

O ORM terá todo o prazer em retornar todas as pessoas (normalmente como instâncias de um object Person), mas precisará consultar a tabela de carros para cada Pessoa.

Uma abordagem simples e muito eficaz para isso é algo que eu chamo de ” fanfolding “, que evita a idéia absurda de que os resultados de consulta de um database relacional devem mapear de volta para as tabelas originais a partir das quais a consulta é composta.

Etapa 1: seleção ampla

  select * from people_car_colour; # this is a view or sql function 

Isso retornará algo como

  p.id | p.name | p.telno | car.id | car.type | car.colour -----+--------+---------+--------+----------+----------- 2 | jones | 2145 | 77 | ford | red 2 | jones | 2145 | 1012 | toyota | blue 16 | ashby | 124 | 99 | bmw | yellow 

Etapa 2: objetivar

Suga os resultados em um criador de object genérico com um argumento para dividir após o terceiro item. Isso significa que o object “jones” não será criado mais de uma vez.

Etapa 3: renderizar

 for p in people: print p.car.colour # no more car queries 

Veja esta página web para uma implementação de fanfolding para python.

Suponha que você tenha EMPRESA e EMPREGADO. A EMPRESA tem muitos EMPREGADOS (ou seja, o EMPREGADO tem um campo COMPANY_ID).

Em algumas configurações de O / R, quando você tem um object Company mapeado e acessa seus objects Employee, a ferramenta O / R fará uma seleção para cada funcionário, em que se você estivesse apenas fazendo as coisas em SQL direto, poderia select * from employees where company_id = XX . Assim, N (número de funcionários) mais 1 (empresa)

É assim que as versões iniciais do EJB Entity Beans funcionavam. Eu acredito que coisas como o Hibernate acabaram com isso, mas eu não tenho muita certeza. A maioria das ferramentas geralmente inclui informações sobre sua estratégia de mapeamento.

Aqui está uma boa descrição do problema – http://www.realsolve.co.uk/site/tech/hib-tip-pitfall.php?name=why-lazy

Agora que você entende o problema, ele pode ser evitado normalmente fazendo uma busca de junit em sua consulta. Isso basicamente força a busca do object carregado com preguiça para que os dados sejam recuperados em uma consulta, em vez de n + 1 consultas. Espero que isto ajude.

Confira Ayende post sobre o tema: Combatendo o problema Select N + 1 no NHibernate

Basicamente, ao usar um ORM como NHibernate ou EntityFramework, se você tiver um relacionamento um-para-muitos (mestre-detalhe), e quiser listar todos os detalhes por cada registro mestre, você tem que fazer chamadas de consulta N + 1 para o database, “N” é o número de registros mestre: 1 consulta para obter todos os registros mestre e N consultas, uma por registro mestre, para obter todos os detalhes por registro mestre.

Mais chamadas de consulta do database -> mais tempo de latência -> desempenho reduzido do aplicativo / database.

No entanto, os ORMs têm opções para evitar esse problema, principalmente usando “joins”.

Na minha opinião, o artigo escrito em Hibernate Pitfall: Por que os relacionamentos devem ser preguiçosos é exatamente o oposto do problema real N + 1.

Se você precisa de uma explicação correta, por favor consulte o Hibernate – Capítulo 19: Melhorando o desempenho – Buscando estratégias

Selecionar buscar (o padrão) é extremamente vulnerável a N + 1, seleciona problemas, então podemos querer habilitar a busca de junit

O link fornecido tem um exemplo muito simples do problema n + 1. Se você aplicá-lo ao Hibernate é basicamente falando sobre a mesma coisa. Quando você consulta um object, a entidade é carregada, mas qualquer associação (a menos que seja configurada de outra forma) será carregada com preguiça. Daí uma consulta para os objects raiz e outra consulta para carregar as associações para cada um deles. 100 objects retornados significam uma consulta inicial e, em seguida, 100 consultas adicionais para obter a associação de cada, n + 1.

http://pramatr.com/2009/02/05/sql-n-1-selects-explained/

É muito mais rápido emitir uma consulta que retorne 100 resultados do que emitir 100 consultas, cada uma retornando 1 resultado.

O problema da consulta N + 1 acontece quando você esquece de buscar uma associação e precisa acessá-la:

 List comments = entityManager.createQuery( "select pc " + "from PostComment pc " + "where pc.review = :review", PostComment.class) .setParameter("review", review) .getResultList(); LOGGER.info("Loaded {} comments", comments.size()); for(PostComment comment : comments) { LOGGER.info("The post title is '{}'", comment.getPost().getTitle()); } 

Que gera as seguintes instruções SQL:

 SELECT pc.id AS id1_1_, pc.post_id AS post_id3_1_, pc.review AS review2_1_ FROM post_comment pc WHERE pc.review = 'Excellent!' INFO - Loaded 3 comments SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_ FROM post pc WHERE pc.id = 1 INFO - The post title is 'Post nr. 1' SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_ FROM post pc WHERE pc.id = 2 INFO - The post title is 'Post nr. 2' SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_ FROM post pc WHERE pc.id = 3 INFO - The post title is 'Post nr. 3' 

Primeiro, o Hibernate executa a consulta JPQL, e uma lista de entidades PostComment é buscada.

Em seguida, para cada PostComment , a propriedade de post associada é usada para gerar uma mensagem de log contendo o título da Post .

Como a associação de post não é inicializada, o Hibernate deve buscar a entidade Post com uma consulta secundária e, para N entidades PostComment , N mais consultas serão executadas (daí o problema de consulta N + 1).

Primeiro, você precisa de um registro e monitoramento de SQL adequados para identificar esse problema.

Em segundo lugar, esse tipo de problema é melhor ser pego por testes de integração. Você pode usar uma declaração automática de JUnit para validar a contagem esperada de instruções SQL geradas . O projeto de unidade de database já fornece essa funcionalidade e é de código aberto.

Quando você identificou o problema da consulta N + 1, é necessário usar um JOIN FETCH para que as associações filho sejam buscadas em uma consulta, em vez de N. Se você precisar buscar várias associações filho, é melhor buscar uma coleção na consulta inicial e a segunda com uma consulta SQL secundária.

Um milionário tem N carros. Você quer pegar todas as (4) rodas.

Uma (1) consulta carrega todos os carros, mas para cada (N) carro, uma consulta separada é enviada para o carregamento de rodas.

Custos:

Suponha que os índices se encaixem no carneiro.

Análise e análise de consultas 1 + N + pesquisa de índice E access de placa 1 + N + (N * 4) para carregamento de carga útil.

Suponha que os índices não caibam no carneiro.

Custos adicionais no pior caso 1 + N accesss da placa para o índice de carregamento.

Resumo

O gargalo da garrafa é o access à placa (cerca de 70 vezes por segundo de access random ao disco rígido). Uma seleção de participantes ansiosos também acessaria a placa 1 + N + (N * 4) vezes para carga útil. Então, se os índices se encheckboxm em ram – não há problema, é rápido o suficiente, porque apenas as operações de ram envolvidas.

A questão, como outros afirmaram de maneira mais elegante, é que você tem um produto cartesiano das colunas OneToMany ou está fazendo N + 1 Selects. Possível conjunto de resultados gigantesco ou chatty com o database, respectivamente.

Estou surpreso que isso não é mencionado, mas isso como eu tenho chegado em torno desta questão … Eu faço uma tabela de ids semi-temporários . Eu também faço isso quando você tem a limitação da cláusula IN () .

Isso não funciona para todos os casos (provavelmente nem mesmo para a maioria), mas funciona muito bem se você tiver muitos objects-filhos de tal forma que o produto cartesiano fique fora de controle (ou seja, muitas colunas OneToMany o número de resultados será uma multiplicação das colunas) e seu mais de um lote como trabalho.

Primeiro, insira seus IDs de object pai como lote em uma tabela de IDs. Este batch_id é algo que geramos em nosso aplicativo e nos mantemos.

 INSERT INTO temp_ids (product_id, batch_id) (SELECT p.product_id, ? FROM product p ORDER BY p.product_id LIMIT ? OFFSET ?); 

Agora, para cada coluna OneToMany , basta fazer um SELECT na tabela ids INNER JOIN na tabela filha com um WHERE batch_id= (ou vice-versa). Você só quer ter certeza de ordenar pela coluna de id, pois isso tornará as colunas de resultado de mesclagem mais fáceis (caso contrário, você precisará de um HashMap / Table para todo o conjunto de resultados, que pode não ser tão ruim assim).

Então você apenas limpa periodicamente a tabela ids.

Isso também funciona particularmente bem se o usuário selecionar 100 ou mais itens distintos para algum tipo de processamento em massa. Coloque os 100 IDs distintos na tabela temporária.

Agora, o número de consultas que você está fazendo é pelo número de colunas OneToMany.

N + 1 problema de seleção é uma dor, e faz sentido detectar esses casos em testes de unidade. Eu desenvolvi uma pequena biblioteca para verificar o número de consultas executadas por um determinado método de teste ou apenas um bloco arbitrário de código – JDBC Sniffer

Basta adicionar uma regra JUnit especial à sua class de teste e colocar uma anotação com o número esperado de consultas nos seus methods de teste:

 @Rule public final QueryCounter queryCounter = new QueryCounter(); @Expectation(atMost = 3) @Test public void testInvokingDatabase() { // your JDBC or JPA code } 

Tome exemplo Matt Solnit, imagine que você define uma associação entre Car e Wheels como LAZY e você precisa de alguns campos Wheels. Isso significa que, após o primeiro select, o modo de hibernação fará “Select * from Wheels, onde car_id =: id” FOR EACH Car.

Isto faz o primeiro select e mais 1 select por cada N carro, por isso é chamado n + 1 problema.

Para evitar isso, faça com que a associação seja tão ansiosa, de modo que a hibernação carregue dados com uma junit.

Mas atenção, se muitas vezes você não acessar as Wheels associadas, é melhor mantê-lo LAZY ou alterar o tipo de busca com Criteria.