Question Quel est le problème de requête N + 1 SELECT?


SELECT N + 1 est généralement indiqué comme un problème dans les discussions ORM (Object-Relational Mapping), et je comprends qu'il a quelque chose à faire avec avoir à faire beaucoup de requêtes de base de données pour quelque chose qui semble simple dans le monde objet.

Quelqu'un a-t-il une explication plus détaillée du problème?


1297
2017-09-18 21:30


origine


Réponses:


Disons que vous avez une collection de Car objets (lignes de base de données), et chaque Car a une collection de Wheel objets (aussi des lignes). En d'autres termes, Car -> Wheel est une relation 1-à-plusieurs.

Maintenant, disons que vous devez parcourir toutes les voitures, et pour chacune, imprimer une liste des roues. L'implémentation O / R naïve ferait ce qui suit:

SELECT * FROM Cars;

Et alors pour chaque Car:

SELECT * FROM Wheel WHERE CarId = ?

En d'autres termes, vous avez un choix pour les voitures, puis N sélections supplémentaires, où N est le nombre total de voitures.

Alternativement, on pourrait obtenir toutes les roues et effectuer les recherches en mémoire:

SELECT * FROM Wheel

Cela réduit le nombre d'allers-retours à la base de données de N + 1 à 2. La plupart des outils ORM vous offrent plusieurs moyens d'empêcher les sélections N + 1.

Référence: Java Persistence avec Hibernate, chapitre 13.


767
2017-09-18 21:36



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

Cela vous obtient un jeu de résultats où les lignes enfant dans table2 provoquent la duplication en retournant les résultats table1 pour chaque ligne enfant dans table2. Les mappeurs O / R doivent différencier les instances table1 en fonction d'un champ clé unique, puis utiliser toutes les colonnes table2 pour remplir les instances enfants.

SELECT table1.*

SELECT table2.* WHERE SomeFkId = #

Le N + 1 est où la première requête remplit l'objet principal et la deuxième requête remplit tous les objets enfants pour chacun des objets principaux uniques renvoyés.

Considérer:

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

class Person
{
    string Name { get; set; }
    int HouseId { get; set; }
}

et des tables avec une structure similaire. Une seule requête pour l'adresse "22 Valley St" peut renvoyer:

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

L'O / RM doit remplir une instance de Home avec ID = 1, Address = "22 Valley St", puis remplir le tableau Habitants avec des instances People pour Dave, John et Mike avec une seule requête.

Une requête N + 1 pour la même adresse utilisée ci-dessus entraînerait:

Id Address
1  22 Valley St

avec une requête séparée comme

SELECT * FROM Person WHERE HouseId = 1

et résultant en un ensemble de données distinct comme

Name    HouseId
Dave    1
John    1
Mike    1

et le résultat final étant le même que ci-dessus avec la requête unique.

Les avantages de sélectionner seul est que vous obtenez toutes les données à l'avance, ce qui peut être ce que vous désirez. Les avantages de N + 1 sont la complexité de la requête est réduite et vous pouvez utiliser le chargement paresseux où les ensembles de résultats enfant ne sont chargés qu'à la première demande.


98
2017-09-18 21:43



Fournisseur avec une relation un-à-plusieurs avec le produit. Un fournisseur a (fournit) de nombreux produits.

***** 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      |
+-----+-----------+--------------------+-------+------------+

Facteurs:

  • Mode paresseux pour le fournisseur défini sur "true" (par défaut)

  • Le mode d'extraction utilisé pour l'interrogation sur le produit est sélectionné

  • Mode de récupération (par défaut): les informations sur le fournisseur sont accessibles

  • La mise en cache ne joue pas de rôle pour la première fois

  • Le fournisseur est consulté

Le mode Fetch est Sélectionner Fetch (par défaut)

// 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=?

Résultat:

  • 1 instruction select pour le produit
  • N sélectionner les déclarations pour le fournisseur

C'est N + 1 problème de sélection!


58
2017-12-01 13:35



Je ne peux pas commenter directement d'autres réponses, car je n'ai pas assez de réputation. Mais il est intéressant de noter que le problème ne se pose essentiellement que parce que, historiquement, beaucoup de dbms ont été très médiocres lorsqu'il s'agit de gérer des jointures (MySQL étant un exemple particulièrement remarquable). Donc, n + 1 a souvent été nettement plus rapide qu'une jointure. Et puis il y a des façons de s'améliorer sur n + 1 mais toujours sans avoir besoin d'une jointure, ce à quoi correspond le problème original.

Cependant, MySQL est maintenant beaucoup mieux que ce qu'il était quand il s'agit de jointures. Quand j'ai appris MySQL pour la première fois, j'ai beaucoup utilisé les jointures. Puis j'ai découvert à quel point ils étaient lents, et j'ai changé à n + 1 dans le code à la place. Mais, récemment, je suis retourné à des jointures, parce que MySQL est maintenant un diable de beaucoup mieux à les manipuler que c'était quand j'ai commencé à l'utiliser.

De nos jours, une simple jointure sur un ensemble de tables correctement indexées est rarement un problème, en termes de performances. Et si cela donne un coup de performance, alors l'utilisation de conseils d'index les résout souvent.

Ceci est discuté ici par l'un des membres de l'équipe de développement de MySQL:

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

Donc, le résumé est: Si vous avez évité les jointures dans le passé en raison de la performance abyssale de MySQL avec eux, alors essayez à nouveau sur les dernières versions. Vous serez probablement agréablement surpris.


33
2018-01-08 12:49



Nous nous sommes éloignés de l'ORM de Django à cause de ce problème. Fondamentalement, si vous essayez et faites

for p in person:
    print p.car.colour

L'ORM retournera volontiers toutes les personnes (généralement en tant qu'objets d'un objet Person), mais il devra ensuite interroger la table de voiture pour chaque personne.

Une approche simple et très efficace est quelque chose que j'appelle "fanfolding», ce qui évite l'idée absurde que les résultats d'une requête provenant d'une base de données relationnelle doivent correspondre aux tables d'origine à partir desquelles la requête est composée.

Étape 1: sélection large

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

Cela retournera quelque chose comme

  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

Étape 2: Objectify

Suce les résultats dans un créateur d'objet générique avec un argument à diviser après le troisième élément. Cela signifie que l'objet "jones" ne sera pas créé plus d'une fois.

Étape 3: Rendu

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

Voir cette page web pour une mise en œuvre de fanfolding pour python.


25
2018-06-09 21:18



Supposons que vous avez COMPANY et EMPLOYEE. L'ENTREPRISE compte de nombreux EMPLOYÉS (par exemple, EMPLOYÉ a un champ COMPANY_ID).

Dans certaines configurations O / R, lorsque vous avez un objet Company mappé et que vous accédez à ses objets Employee, l'outil O / R effectue une sélection pour chaque employé, alors que si vous étiez en train de faire des opérations SQL, vous pouviez select * from employees where company_id = XX. Donc N (nombre d'employés) plus 1 (compagnie)

C'est ainsi que fonctionnaient les versions initiales d'EJB Entity Beans. Je crois que des choses comme Hibernate ont fait disparaître cela, mais je ne suis pas trop sûr. La plupart des outils incluent généralement des informations sur leur stratégie de cartographie.


16
2017-09-18 21:33



Voici une bonne description du problème - http://www.realsolve.co.uk/site/tech/hib-tip-pitfall.php?name=why-lazy

Maintenant que vous comprenez le problème, vous pouvez généralement l'éviter en effectuant une recherche de jointure dans votre requête. Cela force fondamentalement l'extraction de l'objet chargé paresseux afin que les données soient récupérées dans une requête au lieu de n + 1 requêtes. J'espère que cela t'aides.


14
2017-09-18 21:43



Vérifiez Ayende post sur le sujet: Combattre le problème Select N + 1 dans NHibernate

Fondamentalement, lorsque vous utilisez un ORM comme NHibernate ou EntityFramework, si vous avez une relation un-à-plusieurs (maître-détail) et que vous voulez répertorier tous les détails pour chaque enregistrement, vous devez effectuer N + 1 appels de requête au base de données, "N" étant le nombre de fiches principales: 1 requête pour obtenir toutes les fiches, et N requêtes, une par fiche, pour obtenir tous les détails par fiche.

Plus d'appels de requête de base de données -> plus de temps de latence -> diminution des performances de l'application / de la base de données.

Cependant, les ORM ont des options pour éviter ce problème, en utilisant principalement des "jointures".


12
2018-06-05 22:21



À mon avis, l'article écrit en Hibernate Pitfall: Pourquoi les relations devraient être paresseuses est exactement à l'opposé de la vraie question N + 1 est.

Si vous avez besoin d'une explication correcte, veuillez vous référer Hibernate - Chapitre 19: Amélioration des performances - Récupération des stratégies

Sélectionner l'extraction (la valeur par défaut) est   extrêmement vulnérable aux sélections N + 1   problèmes, nous pourrions donc vouloir activer   rejoindre aller chercher


11
2017-07-21 11:55



Le lien fourni a un exemple très simple du problème n + 1. Si vous l'appliquez à Hibernate, cela revient à parler de la même chose. Lorsque vous interrogez un objet, l'entité est chargée, mais toutes les associations (sauf configuration contraire) seront chargées paresseusement. D'où une requête pour les objets racine et une autre requête pour charger les associations pour chacun d'eux. 100 objets retournés signifie une requête initiale et ensuite 100 requêtes supplémentaires pour obtenir l'association pour chacun, n + 1.

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


9
2018-02-20 08:33



Il est beaucoup plus rapide d'émettre une requête qui renvoie 100 résultats que d'émettre 100 requêtes qui retournent chacune 1 résultat.


7
2017-11-07 10:30