query - orm select n 1 problem




Qual é o problema da consulta N+1 SELECT? (12)

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

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

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


A questão, como outros afirmaram com mais elegância, é 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 banco de dados, 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ário . 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 objetos filhos, de modo 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 objeto 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.


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 desejar listar todos os detalhes para cada registro mestre, você precisará fazer chamadas de consulta N + 1 para o banco de dados, "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 banco de dados -> mais tempo de latência -> desempenho reduzido do aplicativo / banco de dados.

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


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 só surge essencialmente 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 junção. E há maneiras de melhorar n + 1, mas ainda sem precisar de uma junção, 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 junção 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 objeto 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 ideia absurda de que os resultados de consulta de um banco de dados 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 objetos genéricos com um argumento para dividir após o terceiro item. Isso significa que o objeto "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.


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 objeto, 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 objetos raiz e outra consulta para carregar as associações para cada um deles. 100 objetos 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/


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

List<PostComment> 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 banco de dados 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.


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 objeto Company mapeado e acessa seus objetos 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.


Tome o exemplo de Matt Solnit, imagine que você defina 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 junção.

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.


Digamos que você tenha uma coleção de objetos Car (linhas do banco de dados) e cada Car tenha uma coleção de objetos 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 deles, 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 memória:

SELECT * FROM Wheel

Isso reduz o número de viagens de ida e volta ao banco de dados 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 objeto principal e a segunda consulta preenche todos os objetos filhos para cada um dos objetos 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 conjunto de dados 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.





select-n-plus-1