Was ist das "N + 1-Auswahlproblem" in ORM (Object-Relational Mapping)?
Das "N + 1-Auswahlproblem" wird im Allgemeinen als Problem in den ORM-Diskussionen (Object-Relational Mapping) angegeben, und ich verstehe, dass es etwas damit zu tun hat, viele Datenbankabfragen für etwas durchzuführen, das im Objekt einfach zu sein scheint Welt.
Hat jemand eine genauere Erklärung des Problems?
Angenommen, Sie haben eine Sammlung von Car Objekten (Datenbankzeilen) und jede Car hat eine Sammlung von Wheel Objekten (auch Zeilen). Mit anderen Worten, Car -> Wheel ist eine 1-zu-viele-Beziehung.
Angenommen, Sie müssen alle Autos durchlaufen und für jedes eine Liste der Räder ausdrucken. Die naive O/R-Implementierung würde Folgendes bewirken:
SELECT * FROM Cars;
Und dann für jedes Car:
SELECT * FROM Wheel WHERE CarId = ?
Mit anderen Worten, Sie haben eine Auswahlmöglichkeit für die Autos und dann N zusätzliche Auswahlmöglichkeiten, wobei N die Gesamtzahl der Autos ist.
Alternativ könnte man alle Räder abrufen und die Suche im Speicher durchführen:
SELECT * FROM Wheel
Dies reduziert die Anzahl der Roundtrips zur Datenbank von N + 1 auf 2. Die meisten ORM-Tools bieten verschiedene Möglichkeiten, um N + 1-Auswahlen zu verhindern.
Referenz: Java-Persistenz mit Ruhezustand, Kapitel 13.
SELECT
table1.*
, table2.*
INNER JOIN table2 ON table2.SomeFkId = table1.SomeId
Auf diese Weise erhalten Sie eine Ergebnismenge, bei der untergeordnete Zeilen in Tabelle2 Duplikationen verursachen, indem die Ergebnisse von Tabelle1 für jede untergeordnete Zeile in Tabelle2 zurückgegeben werden. O/R-Zuordnungen sollten table1-Instanzen anhand eines eindeutigen Schlüsselfelds unterscheiden und dann alle table2-Spalten verwenden, um untergeordnete Instanzen aufzufüllen.
SELECT table1.*
SELECT table2.* WHERE SomeFkId = #
Mit N + 1 werden bei der ersten Abfrage das primäre Objekt und bei der zweiten Abfrage alle untergeordneten Objekte für jedes der zurückgegebenen eindeutigen primären Objekte ausgefüllt.
Erwägen:
class House
{
int Id { get; set; }
string Address { get; set; }
Person[] Inhabitants { get; set; }
}
class Person
{
string Name { get; set; }
int HouseId { get; set; }
}
und Tabellen mit einer ähnlichen Struktur. Eine einzelne Abfrage für die Adresse "22 Valley St" kann zurückgeben:
Id Address Name HouseId
1 22 Valley St Dave 1
1 22 Valley St John 1
1 22 Valley St Mike 1
Der O/RM sollte eine Instanz von Home mit ID = 1, Address = "22 Valley St" füllen und dann das Array Inhabitants mit People-Instanzen für Dave, John und Mike mit nur einer Abfrage füllen.
Eine N + 1-Abfrage für dieselbe Adresse, die oben verwendet wurde, würde zu Folgendem führen:
Id Address
1 22 Valley St
mit einer separaten Abfrage wie
SELECT * FROM Person WHERE HouseId = 1
und was zu einem separaten Datensatz wie
Name HouseId
Dave 1
John 1
Mike 1
und das Endergebnis ist dasselbe wie oben mit der einzelnen Abfrage.
Der Vorteil von Single Select besteht darin, dass Sie alle Daten im Voraus erhalten, die Sie sich letztendlich wünschen. Der Vorteil von N + 1 besteht darin, dass die Komplexität von Abfragen verringert wird und Sie das verzögerte Laden verwenden können, bei dem die untergeordneten Ergebnismengen nur bei der ersten Anforderung geladen werden.
Lieferant mit einer Eins-zu-Viele-Beziehung zum Produkt. Ein Lieferant hat (liefert) viele Produkte.
***** 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 |
+-----+-----------+--------------------+-------+------------+
Faktoren:
Lazy-Modus für Supplier auf "true" gesetzt (Standard)
Der für die Abfrage des Produkts verwendete Abrufmodus lautet "Auswählen"
Abrufmodus (Standard): Auf Lieferanteninformationen wird zugegriffen
Caching spielt beim ersten Mal keine Rolle
Auf den Lieferanten wird zugegriffen
Der Abrufmodus ist "Abruf auswählen" (Standard).
// 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=?
Ergebnis:
- 1 select-Anweisung für Produkt
- N ausgewählte Aussagen für Lieferanten
Dies ist ein N + 1-Auswahlproblem!
Ich kann andere Antworten nicht direkt kommentieren, da ich nicht genug Ruf habe. Es ist jedoch erwähnenswert, dass das Problem im Wesentlichen nur auftritt, weil in der Vergangenheit viele DBMS im Umgang mit Joins ziemlich schlecht waren (MySQL ist ein besonders bemerkenswertes Beispiel). Daher war n + 1 oftmals deutlich schneller als ein Join. Und dann gibt es Möglichkeiten, n + 1 zu verbessern, ohne dass ein Join erforderlich ist, worauf sich das ursprüngliche Problem bezieht.
Allerdings ist MySQL jetzt viel besser als früher, wenn es um Joins geht. Als ich zum ersten Mal MySQL lernte, habe ich viele Joins verwendet. Dann entdeckte ich, wie langsam sie sind und wechselte stattdessen im Code zu n + 1. Aber in letzter Zeit bin ich wieder zu Joins übergegangen, weil MySQL jetzt viel besser mit ihnen umgehen kann als zu der Zeit, als ich es zum ersten Mal benutzte.
Heutzutage ist ein einfacher Join für einen ordnungsgemäß indizierten Tabellensatz in Bezug auf die Leistung selten ein Problem. Und wenn es dennoch zu Leistungseinbußen kommt, werden diese durch die Verwendung von Index-Hinweisen häufig behoben.
Dies wird hier von einem der MySQL-Entwicklerteams erörtert:
http://jorgenloland.blogspot.co.uk/2013/02/dbt-3-q3-6-x-performance-in-mysql-5610.html
Die Zusammenfassung lautet also: Wenn Sie in der Vergangenheit Joins aufgrund der schlechten Leistung von MySQL vermieden haben, versuchen Sie es mit den neuesten Versionen erneut. Sie werden wahrscheinlich angenehm überrascht sein.
Wir sind wegen dieses Problems vom ORM in Django weggezogen. Grundsätzlich, wenn Sie es versuchen und tun
for p in person:
print p.car.colour
Das ORM gibt gerne alle Personen zurück (normalerweise als Instanzen eines Personenobjekts), muss dann jedoch die Fahrzeugtabelle für jede Person abfragen.
Ein einfacher und sehr effektiver Ansatz hierfür ist das sogenannte " fanfolding ", das die unsinnige Vorstellung vermeidet, dass Abfrageergebnisse aus einer relationalen Datenbank wieder zugeordnet werden sollten Die Originaltabellen, aus denen die Abfrage besteht.
Schritt 1: Breite Auswahl
select * from people_car_colour; # this is a view or sql function
Dies wird so etwas zurückgeben
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
Schritt 2: Objektivieren
Saugen Sie die Ergebnisse in einen generischen Objektersteller mit einem Argument ein, das nach dem dritten Element aufgeteilt werden soll. Dies bedeutet, dass "jones" -Objekt nicht mehr als einmal erstellt wird.
Schritt 3: Rendern
for p in people:
print p.car.colour # no more car queries
Siehe diese Webseite für eine Implementierung von Fanfolding für Python.
Angenommen, Sie haben UNTERNEHMEN und MITARBEITER. UNTERNEHMEN hat viele MITARBEITER (d. H. MITARBEITER hat ein Feld COMPANY_ID).
In einigen O/R-Konfigurationen führt das O/R-Tool, wenn Sie ein zugeordnetes Unternehmensobjekt haben und auf dessen Employee-Objekte zugreifen, eine Auswahl für jeden Mitarbeiter durch. Wenn Sie nur in direktem SQL arbeiten, können Sie select * from employees where company_id = XX. Also N (Anzahl der Mitarbeiter) plus 1 (Firma)
So funktionierten die ersten Versionen von EJB Entity Beans. Ich glaube Dinge wie Hibernate haben das beseitigt, aber ich bin mir nicht sicher. Die meisten Tools enthalten normalerweise Informationen zu ihrer Strategie für die Zuordnung.
Hier ist eine gute Beschreibung des Problems - https://web.archive.org/web/20160310145416/http://www.realsolve.co.uk/site/tech/hib-tip-pitfall.php? name = warum-faul
Nachdem Sie das Problem verstanden haben, können Sie es normalerweise vermeiden, indem Sie einen Join-Abruf in Ihrer Abfrage ausführen. Dies erzwingt im Grunde das Abrufen des verzögert geladenen Objekts, sodass die Daten in einer Abfrage statt in n + 1 Abfragen abgerufen werden. Hoffe das hilft.
Überprüfen Sie den Ayende-Beitrag zum Thema: Bekämpfung des Select N + 1-Problems in NHibernate
Grundsätzlich müssen Sie bei Verwendung eines ORM wie NHibernate oder EntityFramework, wenn Sie eine Eins-zu-Viele-Beziehung (Master-Detail-Beziehung) haben und alle Details für jeden Stammsatz auflisten möchten, N + 1 Abfrageaufrufe an den durchführen Datenbank, wobei "N" die Anzahl der Stammsätze ist: 1 Abfrage, um alle Stammsätze abzurufen, und N Abfragen, eine pro Stammsatz, um alle Details pro Stammsatz abzurufen.
Mehr Datenbankabfragen -> längere Wartezeiten -> geringere Anwendungs-/Datenbankleistung.
ORMs haben jedoch Optionen, um dieses Problem zu vermeiden, und verwenden hauptsächlich "Joins".
Meiner Meinung nach ist der Artikel in Hibernate Pitfall: Warum Beziehungen faul sein sollten genau das Gegenteil von echtem N + 1-Problem.
Wenn Sie eine korrekte Erklärung benötigen, lesen Sie bitte Ruhezustand - Kapitel 19: Verbessern der Leistung - Abrufen von Strategien
Select-Abruf (Standardeinstellung) ist extrem anfällig für N + 1-Auswahlprobleme. Daher möchten wir möglicherweise den Join-Abruf aktivieren
Das N + 1-Abfrageproblem tritt auf, wenn Sie vergessen haben, eine Zuordnung abzurufen, und dann darauf zugreifen müssen:
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());
}
Wodurch die folgenden SQL-Anweisungen generiert werden:
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'
Zunächst führt Hibernate die JPQL-Abfrage aus, und eine Liste der Entitäten PostComment wird abgerufen.
Anschließend wird für jedes PostComment die zugehörige post -Eigenschaft verwendet, um eine Protokollnachricht mit dem Titel Post zu generieren.
Da die post Zuordnung nicht initialisiert ist, muss Hibernate die Post Entität mit einer sekundären Abfrage abrufen, und für N PostComment Entitäten werden N weitere Abfragen ausgeführt (daher die N + 1 Abfrageproblem).
Zunächst benötigen Sie ordnungsgemäße SQL-Protokollierung und -Überwachung , damit Sie dieses Problem erkennen können.
Zweitens ist es besser, solche Probleme bei Integrationstests zu erkennen. Sie können ein automatische JUnit-Bestätigung, um die erwartete Anzahl der generierten SQL-Anweisungen zu überprüfen verwenden. Das DB-Unit-Projekt bietet diese Funktionalität bereits und ist Open Source.
Wenn Sie das N + 1-Abfrageproblem identifiziert haben, Sie müssen JOIN FETCH verwenden, damit untergeordnete Zuordnungen in einer Abfrage anstelle von N abgerufen werden . Wenn Sie mehrere untergeordnete Zuordnungen abrufen müssen, ist es besser, eine Auflistung in der ersten Abfrage und die zweite mit einer sekundären SQL-Abfrage abzurufen.
Der mitgelieferte Link enthält ein sehr einfaches Beispiel für das n + 1-Problem. Wenn Sie es auf Hibernate anwenden, handelt es sich im Grunde genommen um dasselbe. Wenn Sie nach einem Objekt fragen, wird die Entität geladen, aber alle Zuordnungen (sofern nicht anders konfiguriert) werden verzögert geladen. Daher eine Abfrage nach den Stammobjekten und eine weitere Abfrage, um die Zuordnungen für jedes dieser Objekte zu laden. 100 zurückgegebene Objekte bedeuten eine Anfangsabfrage und dann 100 zusätzliche Abfragen, um die Zuordnung für jedes n + 1 zu erhalten.
Ein Millionär hat N Autos. Sie möchten alle (4) Räder bekommen.
Eine (1) Abfrage lädt alle Autos, aber für jedes (N) Auto wird eine separate Abfrage zum Laden von Rädern gesendet.
Kosten:
Angenommen, die Indizes passen in den RAM.
Parsing und Planing von 1 + N Abfragen + Indexsuche UND 1 + N + (N * 4) Plattenzugriff zum Laden von Nutzdaten.
Angenommen, die Indizes passen nicht in den RAM.
Zusätzliche Kosten im schlimmsten Fall 1 + N Plattenzugriffe für den Ladeindex.
Zusammenfassung
Der Flaschenhals ist ein Plattenzugriff (ca. 70-mal pro Sekunde, zufälliger Zugriff auf die Festplatte). Eine eifrige Auswahl von Verknüpfungen würde auch 1 + N + (N * 4) -mal auf die Platte für die Nutzlast zugreifen. Wenn die Indizes also in den RAM passen - kein Problem, ist es schnell genug, da nur RAM-Operationen erforderlich sind.
Es ist viel schneller, eine Abfrage auszustellen, die 100 Ergebnisse zurückgibt, als 100 Abfragen, die jeweils 1 Ergebnis zurückgeben.
N + 1-Auswahlproblem ist ein Schmerz, und es ist sinnvoll, solche Fälle in Komponententests zu erkennen. Ich habe eine kleine Bibliothek entwickelt, um die Anzahl der Abfragen zu überprüfen, die von einer bestimmten Testmethode oder einem beliebigen Codeblock ausgeführt werden - JDBC Sniffer
Fügen Sie Ihrer Testklasse einfach eine spezielle JUnit-Regel hinzu und fügen Sie eine Anmerkung mit der erwarteten Anzahl von Abfragen zu Ihren Testmethoden hinzu:
@Rule
public final QueryCounter queryCounter = new QueryCounter();
@Expectation(atMost = 3)
@Test
public void testInvokingDatabase() {
// your JDBC or JPA code
}
Das Problem, das andere eleganter ausgedrückt haben, ist, dass Sie entweder ein kartesisches Produkt der OneToMany-Spalten haben oder N + 1 Selects ausführen. Entweder ein möglicher gigantischer Resultset oder ein Gespräch mit der Datenbank.
Ich bin überrascht, dass dies nicht erwähnt wird, aber wie ich dieses Problem umgehe ... Ich erstelle eine semi-temporäre ID-Tabelle . Ich mache das auch, wenn Sie die IN () -Klausel-Einschränkung haben .
Dies funktioniert nicht in allen Fällen (wahrscheinlich nicht einmal in der Mehrheit), aber es funktioniert besonders gut, wenn Sie viele untergeordnete Objekte haben, so dass das kartesische Produkt außer Kontrolle gerät (dh viele Spalten OneToMany enthalten die Nummer von Ergebnissen wird eine Multiplikation der Spalten sein) und es ist eher ein Stapel-ähnlicher Job.
Zuerst fügen Sie Ihre übergeordneten Objekt-IDs als Stapel in eine ID-Tabelle ein. Diese batch_id erzeugen wir in unserer App und behalten sie bei.
INSERT INTO temp_ids
(product_id, batch_id)
(SELECT p.product_id, ?
FROM product p ORDER BY p.product_id
LIMIT ? OFFSET ?);
Nun führen Sie für jede Spalte OneToMany einfach ein SELECT für die Tabelle ids INNER JOIN aus, wobei Sie die untergeordnete Tabelle mit einem WHERE batch_id= versehen (oder umgekehrt). Sie sollten nur sicherstellen, dass Sie nach der ID-Spalte sortieren, da dies das Zusammenführen der Ergebnisspalten erleichtert (andernfalls benötigen Sie eine HashMap/Tabelle für die gesamte Ergebnismenge, die möglicherweise nicht so schlecht ist).
Dann säubern Sie einfach regelmäßig die ID-Tabelle.
Dies funktioniert auch besonders gut, wenn der Benutzer etwa 100 verschiedene Artikel für eine Art Massenverarbeitung auswählt. Fügen Sie die 100 eindeutigen IDs in die temporäre Tabelle ein.
Die Anzahl der Abfragen, die Sie ausführen, entspricht der Anzahl der OneToMany-Spalten.
Stellen Sie sich zum Beispiel Matt Solnit vor, Sie definieren eine Assoziation zwischen Auto und Rädern als LAZY und benötigen einige Felder für Räder. Dies bedeutet, dass der Ruhezustand nach der ersten Auswahl "Select * from Wheels where car_id =: id" für jedes Auto ausführen wird.
Dies macht die erste Auswahl und mehr 1 Auswahl von jedem N Auto, deshalb heißt es n + 1 Problem.
Um dies zu vermeiden, sollten Sie den Assoziationsabruf so eifrig wie möglich gestalten, damit der Ruhezustand Daten mit einem Join lädt.
Aber Achtung, wenn Sie oft nicht auf die zugehörigen Wheels zugreifen, ist es besser, sie LAZY zu halten oder den Abruftyp mit Criteria zu ändern.