Was ist das "N+1 Selects-Problem" bei ORM (Object-Relational Mapping)?

Question

Das "N+1-Selects-Problem" wird im Allgemeinen als Problem in Diskussionen über objektrelationale Zuordnungen (ORM) genannt, und ich verstehe, dass es etwas damit zu tun hat, dass man viele Datenbankabfragen für etwas machen muss, das in der Objektwelt einfach erscheint.

Hat jemand eine genauere Erklärung für dieses Problem?

Answer 1

Angenommen, Sie haben eine Sammlung von Car Objekte (Datenbankzeilen), und jedes Car hat eine Sammlung von Wheel Objekte (auch Zeilen). Mit anderen Worten, Car Wheel ist eine 1-zu-viele-Beziehung.

Nehmen wir nun an, Sie müssen alle Autos durchlaufen und für jedes eine Liste der Räder ausgeben. Die naive O/R-Implementierung würde das Folgende tun:

SELECT * FROM Cars;

Und dann für jede Car :

SELECT * FROM Wheel WHERE CarId = ?

Mit anderen Worten: Sie haben eine Auswahl für die Autos und dann N weitere Auswahlen, wobei N die Gesamtzahl der Autos ist.

Alternativ könnte man auch alle Räder holen und die Suchvorgänge im Speicher durchführen:

SELECT * FROM Wheel

Dadurch verringert sich die Anzahl der Fahrten zur Datenbank von N+1 auf 2. Die meisten ORM-Tools bieten Ihnen mehrere Möglichkeiten, N+1 Selects zu verhindern.

Referenz: Java-Persistenz mit Hibernate , Kapitel 13.

Answer 2

Was ist das N+1-Abfrageproblem

Das N+1-Abfrageproblem tritt auf, wenn das Datenzugriffssystem N zusätzliche SQL-Anweisungen ausführt, um dieselben Daten abzurufen, die bei der Ausführung der primären SQL-Abfrage hätten abgerufen werden können.

Je größer der Wert von N ist, desto mehr Abfragen werden ausgeführt und desto größer sind die Auswirkungen auf die Leistung. Und im Gegensatz zum Protokoll für langsame Abfragen, das Ihnen helfen kann, langsam laufende Abfragen zu finden, wird das N+1-Problem nicht erkannt, da jede einzelne zusätzliche Abfrage ausreichend schnell läuft, um das Protokoll für langsame Abfragen nicht auszulösen.

Das Problem ist die Ausführung einer großen Anzahl zusätzlicher Abfragen, die insgesamt so viel Zeit in Anspruch nehmen, dass sich die Antwortzeit verlangsamt.

Nehmen wir an, wir haben die folgenden Datenbanktabellen post und post_comments, die eine Eins-zu-Viel-Tabellenbeziehung bilden:

Wir werden die folgenden 4 erstellen post Reihen:

INSERT INTO post (title, id)
VALUES ('High-Performance Java Persistence - Part 1', 1)

INSERT INTO post (title, id)
VALUES ('High-Performance Java Persistence - Part 2', 2)

INSERT INTO post (title, id)
VALUES ('High-Performance Java Persistence - Part 3', 3)

INSERT INTO post (title, id)
VALUES ('High-Performance Java Persistence - Part 4', 4)

Und wir werden auch 4 post_comment Kinderaufzeichnungen:

INSERT INTO post_comment (post_id, review, id)
VALUES (1, 'Excellent book to understand Java Persistence', 1)

INSERT INTO post_comment (post_id, review, id)
VALUES (2, 'Must-read for Java developers', 2)

INSERT INTO post_comment (post_id, review, id)
VALUES (3, 'Five Stars', 3)

INSERT INTO post_comment (post_id, review, id)
VALUES (4, 'A great reference book', 4)

N+1-Abfrageproblem mit einfachem SQL

Wenn Sie die Option post_comments mit dieser SQL-Abfrage:

List<Tuple> comments = entityManager.createNativeQuery("""
    SELECT
        pc.id AS id,
        pc.review AS review,
        pc.post_id AS postId
    FROM post_comment pc
    """, Tuple.class)
.getResultList();

Und später beschließen Sie, die zugehörigen Daten abzurufen. post title für jede post_comment :

for (Tuple comment : comments) {
    String review = (String) comment.get("review");
    Long postId = ((Number) comment.get("postId")).longValue();

    String postTitle = (String) entityManager.createNativeQuery("""
        SELECT
            p.title
        FROM post p
        WHERE p.id = :postId
        """)
    .setParameter("postId", postId)
    .getSingleResult();

    LOGGER.info(
        "The Post '{}' got this review '{}'",
        postTitle,
        review
    );
}

Sie werden das Problem der N+1-Abfrage auslösen, weil Sie statt einer SQL-Abfrage 5 (1 + 4) ausgeführt haben:

SELECT
    pc.id AS id,
    pc.review AS review,
    pc.post_id AS postId
FROM post_comment pc

SELECT p.title FROM post p WHERE p.id = 1
-- The Post 'High-Performance Java Persistence - Part 1' got this review
-- 'Excellent book to understand Java Persistence'

SELECT p.title FROM post p WHERE p.id = 2
-- The Post 'High-Performance Java Persistence - Part 2' got this review
-- 'Must-read for Java developers'

SELECT p.title FROM post p WHERE p.id = 3
-- The Post 'High-Performance Java Persistence - Part 3' got this review
-- 'Five Stars'

SELECT p.title FROM post p WHERE p.id = 4
-- The Post 'High-Performance Java Persistence - Part 4' got this review
-- 'A great reference book'

Die Behebung des Problems der N+1-Abfrage ist sehr einfach. Sie müssen nur alle Daten extrahieren, die Sie in der ursprünglichen SQL-Abfrage benötigen, etwa so:

List<Tuple> comments = entityManager.createNativeQuery("""
    SELECT
        pc.id AS id,
        pc.review AS review,
        p.title AS postTitle
    FROM post_comment pc
    JOIN post p ON pc.post_id = p.id
    """, Tuple.class)
.getResultList();

for (Tuple comment : comments) {
    String review = (String) comment.get("review");
    String postTitle = (String) comment.get("postTitle");

    LOGGER.info(
        "The Post '{}' got this review '{}'",
        postTitle,
        review
    );
}

Diesmal wird nur eine SQL-Abfrage ausgeführt, um alle Daten abzurufen, an denen wir weiter interessiert sind.

N+1-Abfrageproblem mit JPA und Hibernate

Bei der Verwendung von JPA und Hibernate gibt es mehrere Möglichkeiten, das Problem der N+1-Abfrage auszulösen. Daher ist es sehr wichtig zu wissen, wie Sie diese Situationen vermeiden können.

Für die nächsten Beispiele betrachten wir die Abbildung der post y post_comments Tabellen zu den folgenden Einrichtungen:

Die JPA-Zuordnungen sehen wie folgt aus:

@Entity(name = "Post")
@Table(name = "post")
public class Post {

    @Id
    private Long id;

    private String title;

    //Getters and setters omitted for brevity
}

@Entity(name = "PostComment")
@Table(name = "post_comment")
public class PostComment {

    @Id
    private Long id;

    @ManyToOne
    private Post post;

    private String review;

    //Getters and setters omitted for brevity
}

FetchType.EAGER

Verwendung von FetchType.EAGER entweder implizit oder explizit für Ihre JPA-Verknüpfungen zu verwenden, ist eine schlechte Idee, da Sie viel mehr Daten abrufen werden, als Sie benötigen. Außerdem ist die FetchType.EAGER Strategie ist auch anfällig für N+1-Abfrageprobleme.

Leider ist die @ManyToOne y @OneToOne Verbände nutzen FetchType.EAGER standardmäßig, wenn Ihre Zuordnungen also wie folgt aussehen:

@ManyToOne
private Post post;

Sie verwenden die FetchType.EAGER Strategie, und jedes Mal, wenn Sie vergessen, die JOIN FETCH beim Laden einiger PostComment Entitäten mit einer JPQL- oder Criteria-API-Abfrage:

List<PostComment> comments = entityManager
.createQuery("""
    select pc
    from PostComment pc
    """, PostComment.class)
.getResultList();

Sie werden das Problem der N+1-Abfrage auslösen:

SELECT 
    pc.id AS id1_1_, 
    pc.post_id AS post_id3_1_, 
    pc.review AS review2_1_ 
FROM 
    post_comment pc

SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 1
SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 2
SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 3
SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 4

Beachten Sie die zusätzlichen SELECT-Anweisungen, die ausgeführt werden, weil die post Assoziation abgeholt werden muss, bevor die List von PostComment Einheiten.

Im Gegensatz zum Standard-Fetchplan, den Sie beim Aufruf der find Methode der EntityManager Eine JPQL- oder Criteria-API-Abfrage definiert einen expliziten Plan, den Hibernate nicht durch automatisches Einfügen eines JOIN FETCH ändern kann. Sie müssen dies also manuell tun.

Wenn Sie das nicht bräuchten post Assoziation überhaupt, haben Sie Pech, wenn Sie FetchType.EAGER denn es lässt sich nicht vermeiden, dass es abgeholt wird. Deshalb ist es besser, die FetchType.LAZY standardmäßig.

Wenn Sie jedoch Folgendes verwenden möchten post Assoziation, dann können Sie JOIN FETCH um das N+1-Abfrageproblem zu vermeiden:

List<PostComment> comments = entityManager.createQuery("""
    select pc
    from PostComment pc
    join fetch pc.post p
    """, PostComment.class)
.getResultList();

for(PostComment comment : comments) {
    LOGGER.info(
        "The Post '{}' got this review '{}'", 
        comment.getPost().getTitle(), 
        comment.getReview()
    );
}

Dieses Mal führt Hibernate eine einzige SQL-Anweisung aus:

SELECT 
    pc.id as id1_1_0_, 
    pc.post_id as post_id3_1_0_, 
    pc.review as review2_1_0_, 
    p.id as id1_0_1_, 
    p.title as title2_0_1_ 
FROM 
    post_comment pc 
INNER JOIN 
    post p ON pc.post_id = p.id

-- The Post 'High-Performance Java Persistence - Part 1' got this review 
-- 'Excellent book to understand Java Persistence'

-- The Post 'High-Performance Java Persistence - Part 2' got this review 
-- 'Must-read for Java developers'

-- The Post 'High-Performance Java Persistence - Part 3' got this review 
-- 'Five Stars'

-- The Post 'High-Performance Java Persistence - Part 4' got this review 
-- 'A great reference book'

FetchType.LAZY

Selbst wenn Sie auf die Verwendung von FetchType.LAZY explizit für alle Verbände, können Sie immer noch auf das N+1 Problem stoßen.

Dieses Mal ist die post Assoziation wird wie folgt abgebildet:

@ManyToOne(fetch = FetchType.LAZY)
private Post post;

Wenn Sie nun die PostComment Einheiten:

List<PostComment> comments = entityManager
.createQuery("""
    select pc
    from PostComment pc
    """, PostComment.class)
.getResultList();

Hibernate wird eine einzelne SQL-Anweisung ausführen:

SELECT 
    pc.id AS id1_1_, 
    pc.post_id AS post_id3_1_, 
    pc.review AS review2_1_ 
FROM 
    post_comment pc

Wenn Sie aber danach auf die "lazy-loaded post Verein:

for(PostComment comment : comments) {
    LOGGER.info(
        "The Post '{}' got this review '{}'", 
        comment.getPost().getTitle(), 
        comment.getReview()
    );
}

Sie werden das Problem der N+1-Abfrage bekommen:

SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 1
-- The Post 'High-Performance Java Persistence - Part 1' got this review 
-- 'Excellent book to understand Java Persistence'

SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 2
-- The Post 'High-Performance Java Persistence - Part 2' got this review 
-- 'Must-read for Java developers'

SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 3
-- The Post 'High-Performance Java Persistence - Part 3' got this review 
-- 'Five Stars'

SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 4
-- The Post 'High-Performance Java Persistence - Part 4' got this review 
-- 'A great reference book'

Weil die post Assoziation "lazy" abgerufen wird, wird beim Zugriff auf die "lazy" Assoziation eine sekundäre SQL-Anweisung ausgeführt, um die Protokollmeldung zu erstellen.

Auch hier besteht die Lösung im Hinzufügen einer JOIN FETCH Klausel in der JPQL-Abfrage:

List<PostComment> comments = entityManager.createQuery("""
    select pc
    from PostComment pc
    join fetch pc.post p
    """, PostComment.class)
.getResultList();

for(PostComment comment : comments) {
    LOGGER.info(
        "The Post '{}' got this review '{}'", 
        comment.getPost().getTitle(), 
        comment.getReview()
    );
}

Und, genau wie in der FetchType.EAGER Beispiel wird diese JPQL-Abfrage eine einzige SQL-Anweisung erzeugen.

Auch wenn Sie die FetchType.LAZY und verweisen nicht auf die untergeordnete Assoziation einer bidirektionalen @OneToOne JPA-Beziehung, können Sie immer noch das Problem der N+1-Abfrage auslösen.

Wie wird das Problem der N+1-Abfrage automatisch erkannt?

Wenn Sie N+1-Abfrageprobleme in Ihrer Datenzugriffsschicht automatisch erkennen möchten, können Sie die db-util Open-Source-Projekt.

Zunächst müssen Sie die folgende Maven-Abhängigkeit hinzufügen:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>db-util</artifactId>
    <version>${db-util.version}</version>
</dependency>

Danach müssen Sie nur noch SQLStatementCountValidator um die zugrunde liegenden SQL-Anweisungen, die generiert werden, zu bestätigen:

SQLStatementCountValidator.reset();

List<PostComment> comments = entityManager.createQuery("""
    select pc
    from PostComment pc
    """, PostComment.class)
.getResultList();

SQLStatementCountValidator.assertSelectCount(1);

Falls Sie Folgendes verwenden FetchType.EAGER und führen Sie den obigen Testfall aus, erhalten Sie den folgenden Fehler:

SELECT 
    pc.id as id1_1_, 
    pc.post_id as post_id3_1_, 
    pc.review as review2_1_ 
FROM 
    post_comment pc

SELECT p.id as id1_0_0_, p.title as title2_0_0_ FROM post p WHERE p.id = 1

SELECT p.id as id1_0_0_, p.title as title2_0_0_ FROM post p WHERE p.id = 2

-- SQLStatementCountMismatchException: Expected 1 statement(s) but recorded 3 instead!

Answer 3

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

Dadurch erhalten Sie eine Ergebnismenge, bei der untergeordnete Zeilen in Tabelle2 eine Duplizierung verursachen, indem die Ergebnisse von Tabelle1 für jede untergeordnete Zeile in Tabelle2 zurückgegeben werden. O/R-Mapper sollten Instanzen von Tabelle1 anhand eines eindeutigen Schlüsselfeldes unterscheiden und dann alle Spalten von Tabelle2 verwenden, um untergeordnete Instanzen aufzufüllen.

SELECT table1.*

SELECT table2.* WHERE SomeFkId = #

N+1 bedeutet, dass die erste Abfrage das primäre Objekt auffüllt und die zweite Abfrage alle untergeordneten Objekte für jedes der zurückgegebenen eindeutigen primären Objekte auffüllt.

Bedenken Sie:

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 Inhabitants-Array mit People-Instanzen für Dave, John und Mike mit nur einer Abfrage auffüllen.

Eine N+1-Abfrage für dieselbe oben verwendete Adresse würde Folgendes ergeben:

Id Address
1  22 Valley St

mit einer separaten Abfrage wie

SELECT * FROM Person WHERE HouseId = 1

und führt zu einem separaten Datensatz wie

Name    HouseId
Dave    1
John    1
Mike    1

und das Endergebnis ist dasselbe wie oben bei der einfachen Abfrage.

Der Vorteil der Einzelauswahl besteht darin, dass Sie alle Daten im Voraus erhalten, was Sie letztendlich auch wünschen. Die Vorteile von N+1 sind eine geringere Abfragekomplexität und die Möglichkeit des "Lazy Loading", bei dem die untergeordneten Ergebnissätze nur bei der ersten Anfrage geladen werden.

Answer 4

Lieferant mit einer Eins-zu-Viel-Beziehung zu 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 Lieferanten auf "true" gesetzt (Standard)

• Der Abfragemodus für die Abfrage von Produkten ist Select.

• Abrufmodus (Standard): Es wird auf Lieferanteninformationen zugegriffen

• Die Zwischenspeicherung spielt beim ersten Mal keine Rolle, wenn die

• Lieferant wird angesprochen

Fetch-Modus ist Select Fetch (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 Product
• N select-Anweisungen für Lieferanten

Dies ist das Problem der N+1-Auswahl!

Answer 5

Zu anderen Antworten kann ich mich nicht direkt äußern, weil ich nicht genug Ansehen habe. Aber es ist erwähnenswert, dass das Problem im Grunde nur auftritt, weil viele Datenbanken in der Vergangenheit ziemlich schlecht mit Joins umgehen konnten (MySQL ist ein besonders bemerkenswertes Beispiel). Daher war n+1 oft 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 MySQL zum ersten Mal erlernte, habe ich Joins sehr oft benutzt. Dann habe ich entdeckt, wie langsam sie sind, und bin stattdessen zu n+1 im Code übergegangen. Aber in letzter Zeit bin ich wieder zu Joins zurückgekehrt, weil MySQL jetzt viel besser mit ihnen umgehen kann als damals, als ich anfing, es zu benutzen.

Heutzutage ist eine einfache Verknüpfung mit einem ordnungsgemäß indizierten Satz von Tabellen in Bezug auf die Leistung selten ein Problem. Und wenn es doch zu Leistungseinbußen kommt, dann werden diese oft durch die Verwendung von Indexhinweisen gelöst.

Dies wird hier von einem Mitglied des MySQL-Entwicklungsteams diskutiert:

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 wegen der miserablen Leistung von MySQL vermieden haben, sollten Sie es mit den neuesten Versionen noch einmal versuchen. Sie werden wahrscheinlich angenehm überrascht sein.