Quel est le problème n+1 SELECT query?

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

qui vous donne un ensemble de résultats où les rangées d'enfants dans le tableau2 causent la duplication en retournant les résultats du Tableau1 pour chaque rangée d'enfants dans le tableau2. O / R mappers devrait différencier les instances du Tableau1 en fonction d'un champ clé unique, puis utiliser toutes les colonnes du tableau2 pour remplir les instances des enfants.

SELECT table1.*

SELECT table2.* WHERE SomeFkId = #

le N+1 est où la première requête popule l'objet primaire et la deuxième requête popule tous les objets enfants pour chacun des primaires uniques les objets retournés.

prendre en considération:

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 tableaux de structure similaire. Une seule requête à l'adresse "22 de la Vallée de Saint" 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 devrait remplir une instance de Home Avec ID=1, Address=" 22 Valley St " et ensuite peupler le tableau des habitants avec des instances de personnes pour Dave, John, et Mike avec juste une requête.

Un N+1 de la requête de la même adresse ci-dessus résultat:

Id Address
1  22 Valley St

avec une requête séparée comme

SELECT * FROM Person WHERE HouseId = 1

et donnant 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élection unique est que vous obtenez toutes les données à l'avant qui peut être ce que vous désirez. Les avantages de N+1 est la complexité de requête est réduite et vous pouvez utiliser le chargement paresseux lorsque les jeux de résultats enfants ne sont chargés qu'à la première demande.

fournisseur avec une relation de 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 réglé à "true" (par défaut)

• mode Fetch utilisé pour interroger sur le produit est Sélectionner

• mode Fetch (par défaut): accès aux informations du fournisseur

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

• accès au fournisseur

Fetch mode Sélectionnez Extraire (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 Sélectionner la déclaration pour le produit
• N choisir les énoncés pour le fournisseur

ici N+1 select problème!

Je ne peux pas commenter directement les autres réponses, parce que 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 SGBD ont été assez pauvres quand il s'agit de gérer les jointures (MySQL étant un exemple particulièrement remarquable). Ainsi, n+1 a souvent été nettement plus rapide qu'une jointure. Et puis il y a des façons d'améliorer sur n+1 mais toujours sans avoir besoin d'une jointure, ce qui est ce que le problème original se rapporte.

cependant, MySQL est maintenant beaucoup mieux qu'il a l'habitude d'être quand il s'agit de jointures. Quand J'ai appris MySQL, j'ai beaucoup utilisé jointures. Puis j'ai découvert à quel point ils sont lents, et je suis passé à n+1 dans le code à la place. Mais, récemment, j'ai déménagé de nouveau à joins, parce que MySQL est maintenant un enfer de beaucoup mieux à les manipuler que ce qu'il était quand je ai commencé à l'utiliser.

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

ceci est discuté ici par L'une des équipes 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, puis essayer à nouveau sur les dernières versions. Vous aurez probablement être agréablement surpris.

nous nous sommes éloignés de L'ORM à Django à cause de ce problème. En gros, si vous essayez de faire

for p in person:
    print p.car.colour

l'ORM retournera volontiers toutes les personnes (typiquement comme des instances D'un objet de personne), mais alors il aura besoin d'interroger la table de voiture pour chaque personne.

une approche simple et très efficace à ce sujet est quelque chose que j'appelle " fanfolding ", qui évite l'idée absurde que la requête résulte d'une relationnelle la base de données doit revenir aux tables originales à partir desquelles la requête est composée.

Étape 1: large sélectionner

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

ça va retourner 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: Objectififier

aspirent les résultats dans un créateur d'objet générique avec un argument pour se diviser après le troisième élément. Cela signifie que "durand" l'objet ne sera faite plus d'une fois.

Etape 3: Render

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

voir cette page web pour une implémentation de fanfolding pour python.

supposons que vous ayez de la compagnie et un employé. L'entreprise compte de nombreux employés (C.-à-d. que L'employé a un ID D'entreprise sur le terrain).

dans certaines configurations O/R, Lorsque vous avez un objet mappé de la société et que vous allez accéder à ses objets employés, l'outil O/R fera une sélection pour chaque employé, alors que si vous faisiez simplement des choses en SQL droit, vous pourriez select * from employees where company_id = XX . Ainsi N (nombre d'employés) plus 1 (entreprise)

C'est ainsi que les versions initiales de L'entité EJB Haricots travaillé. Je crois que des choses comme hibernation ont disparu avec ça, mais je ne suis pas trop sûr. La plupart des outils contiennent généralement des informations sur leur stratégie de cartographie.

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 il peut typiquement être évité en faisant un fetch join dans votre requête. Cela force fondamentalement le fetch de l'objet chargé paresseux de sorte que les données sont récupérées dans une requête au lieu de n+1 requêtes. Espérons que cette aide.

Vérifier Ayende post sur le sujet: la Lutte contre le Sélectionner N + 1 Problème Dans NHibernate

essentiellement, lorsque vous utilisez un ORM comme NHibernate ou Entitefram Framework, si vous avez une relation un-à-plusieurs (master-detail), et que vous voulez énumérer tous les détails par enregistrement maître, vous devez faire N + 1 appels de requête à la base de données, "N" étant le nombre d'enregistrements maître: 1 requête pour obtenir tous les enregistrements maître, et N requêtes, Un Par enregistrement maître, à obtenez tous les détails de chaque fiche.

plus d'appels d'interrogation de base de données --> plus de temps de latence --> moins de performance de l'application/base de données.

cependant, les ORMS ont des options pour éviter ce problème, principalement en utilisant"joins".

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

Si vous avez besoin d'explication correcte veuillez vous reporter mise en veille prolongée - Chapitre 19: l'Amélioration du Rendement d'Extraction de Stratégies

chargement par Select (par défaut) est extrêmement vulnérable aux sélections N+1 les problèmes, donc nous pourrions vouloir permettre rejoindre fetching

le lien fourni a un exemple très simple du problème n + 1. Si vous L'appliquez à L'hibernation, c'est en gros la même chose. Lorsque vous interrogez un objet, l'entity est chargée mais toutes les associations (sauf si elles sont configurées autrement) seront chargées paresseusement. D'où une requête pour les objets racine et une autre pour charger les associations pour chacun de ceux-ci. 100 objets retournés signifie une requête initiale puis 100 requêtes supplémentaires pour obtenir l'association pour chacun, n + 1.

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

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

la question de requête N+1 se produit lorsque vous oubliez de récupérer une association et que vous devez y accéder:

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());
}

qui génère les instructions SQL suivantes:

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'

tout d'abord, Hibernate exécute la requête JPQL, et une liste d'entités PostComment est récupérée.

ensuite, pour chaque PostComment , la propriété associée post est utilisée pour générer un message log contenant le Post intitulé.

parce que l'association post n'est pas initialisée, Hibernate doit récupérer l'entité Post avec une requête secondaire, et pour les entités n PostComment , N Plus de requêtes vont être exécutées (d'où le problème de requête N+1).

tout d'abord, vous avez besoin de logging SQL approprié et la surveillance afin que vous puissiez repérer ce problème.

Deuxièmement, ce genre de question Est préférable d'être pris par les tests d'intégration. Vous pouvez utiliser un JUnit assert automatique pour valider le nombre attendu d'énoncés SQL générés . Le db-unit project fournit déjà cette fonctionnalité, et c'est open source.

lorsque vous avez identifié la question de requête N+1, vous devez utiliser un fetch JOIN pour que les associations d'enfants soient récupérées dans une requête, au lieu de N . Si vous devez aller chercher plusieurs associations d'enfants, il est préférable de récupérer une collection dans la requête initiale et la seconde avec une requête SQL secondaire.

un millionnaire a N voitures. Vous voulez obtenir tous les (4) roues.

une (1) requête charge tous les wagons, mais pour chaque (N) Wagon, une requête distincte est soumise pour les roues de chargement.

coûts:

supposons que les indices s'ajustent à la mémoire vive.

1 + N requête d'analyse et de rabotage + index de recherche ET d'1 + N + (N * 4) de la plaque d'accès pour le chargement de la charge utile.

suppose que les index ne correspondent pas à la mémoire vive.

des coûts Supplémentaires dans le pire des cas 1 + N de la plaque d'accès pour le chargement de l'index.

résumé

Col de bouteille est l'accès à plaque(ca. 70 fois par seconde accès aléatoire sur hdd) Un select join eager accéderait également à la plaque 1 + N + (N * 4) fois pour la charge utile. Donc si les index s'insèrent dans la ram-Pas de problème, c'est assez rapide parce que seules les opérations ram impliquées.

la question comme d'autres l'ont dit plus élégamment est que vous avez soit un produit cartésien des colonnes OneToMany ou vous faites des sélections N+1. Soit des résultats gigantesques possibles ou chatty avec la base de données, respectivement.

je suis surpris que cela ne soit pas mentionné mais c'est comme ça que j'ai contourné ce problème... je fais une table semi-temporaire ids . je le fais aussi lorsque vous avez la limite de la clause IN () .

cela ne fonctionne pas pour tous les cas (probablement même pas une majorité) mais il fonctionne particulièrement bien si vous avez beaucoup d'objets enfants tels que le produit cartésien va sortir de main (c'est-à-dire des lots de OneToMany colonnes le nombre de résultats sera une multiplication des colonnes) et son plus d'un lot comme travail.

vous insérez D'abord vos ID d'objet parent comme lot dans une table ids. Ce batch_id est quelque chose que nous générons dans notre app et s'accrocher.

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

maintenant pour chaque colonne OneToMany vous faites juste un SELECT sur la table d'ids INNER JOIN ing la table d'enfant avec un WHERE batch_id= (ou vice versa). Vous voulez juste vous assurer que vous commandez par la colonne id car elle rendra la fusion des colonnes de résultat plus facile (sinon vous aurez besoin D'un HashMap/Table pour l'ensemble des résultats qui peuvent ne pas être si mauvais).

puis vous nettoyez périodiquement la table ids.

cela fonctionne aussi particulièrement bien si l'utilisateur choisit disons 100 articles distincts pour une sorte de traitement en vrac. Mettez les 100 pièces d'identité distinctes dans la table temporaire.

maintenant le nombre de requêtes que vous faites est le nombre de colonnes OneToMany.

N+1 select est une question de douleur, et il fait sens pour détecter de tels cas dans les tests unitaires. J'ai développé une petite bibliothèque pour vérifier le nombre de requêtes exécutées par une méthode de test donnée ou juste un bloc arbitraire de code - JDBC Sniffer

il suffit d'ajouter une règle JUnit spéciale à votre classe de test et de placer l'annotation avec le nombre prévu de requêtes sur vos méthodes de test:

@Rule
public final QueryCounter queryCounter = new QueryCounter();

@Expectation(atMost = 3)
@Test
public void testInvokingDatabase() {
    // your JDBC or JPA code
}

prenez L'exemple de Matt Solnit, imaginez que vous définissiez une association entre Voiture et roues comme paresseux et vous avez besoin de quelques champs de roues. Cela signifie qu'après la première sélection, hibernate va faire "Select * from Wheels où car_id=: id" pour chaque voiture.

cela rend le premier select et plus 1 select par chaque voiture N, c'est pourquoi il est appelé n+1 problème.

pour éviter cela, faire l'association fetch aussi impatient, de sorte que les données des charges d'hibernation avec une jointure.

mais attention, si plusieurs fois vous n'ACCÉDEZ PAS aux roues associées, il est préférable de le garder paresseux ou de changer de type fetch avec des critères.