Aufgabe 23 - N+1 finden und beheben

Aufgabe 23 - N+1 finden und beheben

Worum geht es?

Sie schalten das Abfrageprotokoll ein, sehen zum ersten Mal, was Ihr ORM tatsächlich an die Datenbank schickt, und beheben den häufigsten Leistungsfehler in datenbankgestützten Anwendungen (siehe Kapitel Datenbank produktionsnah). Danach messen Sie mit EXPLAIN ANALYZE, ob ein Index etwas bringt, statt es zu vermuten.

Was Sie dafür brauchen

• Kapitel Datenbank produktionsnah, Abschnitt Abfragen, die skalieren.
• Ihr Projekt aus Aufgabe 21 mit dem vollen Seed. Ohne realistische Datenmenge ist der Fehler dieser Übung unsichtbar.
• Zugriff auf psql.

Welche Kompetenzen Sie erwerben und zeigen

• Sie weisen ein N+1-Problem im Abfrageprotokoll nach und beheben es.
• Sie belegen mit EXPLAIN ANALYZE, ob ein Index verwendet wird, und messen die Wirkung vorher und nachher.
• Sie entscheiden, wann ein ORM angemessen ist und wann eine handgeschriebene Abfrage die bessere Wahl ist.

Pädagogische Einordnung

• Reproduktion: das Abfrageprotokoll einschalten und Abfragen zählen (Teil A).
• Reorganisation und Transfer: ein N+1 beheben und die Wirkung messen (Teil B).
• Reflexion, Problemlösung und Urteilsbildung: Indizes beurteilen und die Werkzeugwahl begründen (Teil C).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt.

Teil A - Sichtbar machen

1. Schalten Sie das Abfrageprotokoll Ihres ORM ein. Bei Prisma genügt new PrismaClient({ log: ["query"] }). Notieren Sie die Änderung.

2. Laden Sie eine einfache Detailseite Ihres Projekts, die genau einen Datensatz zeigt. Zählen Sie die protokollierten Abfragen und notieren Sie die Zahl.

3. Laden Sie jetzt eine Übersichtsseite mit einer Liste. Zählen Sie die Abfragen erneut und notieren Sie die Zahl.

4. Sehen Sie sich das Protokoll an. Notieren Sie die Abfrage, die sich wiederholt, und wie oft.

5. Setzen Sie die Zahl der Wiederholungen in Beziehung zur Zahl der angezeigten Zeilen. Beschreiben Sie in zwei bis drei Sätzen, welchen Zusammenhang Sie sehen und warum das Problem N+1 heißt.

6. Beantworten Sie in zwei Sätzen, warum Ihnen dieser Fehler mit zehn Testdatensätzen nicht aufgefallen wäre.

Teil B - Beheben und messen

1. Messen Sie den Ausgangszustand. Notieren Sie drei Werte für die Übersichtsseite: die Anzahl der Abfragen, die Gesamtzeit der Datenbankabfragen aus dem Protokoll, und die Ladezeit im Browser.

2. Beheben Sie das N+1, indem Sie die Beziehung mitladen. Bei Prisma ist das include. Notieren Sie die geänderte Stelle im Repository.

3. Messen Sie erneut dieselben drei Werte und stellen Sie beide Messungen in einer Tabelle gegenüber.

4. Ergänzen Sie eine Obergrenze, bei Prisma take. Beschreiben Sie in zwei bis drei Sätzen, wovor diese Zeile schützt und was in Kapitel 7 daraus wird.

5. Sehen Sie im Protokoll nach, welche SQL-Abfrage jetzt tatsächlich entsteht. Kopieren Sie sie heraus.

6. Suchen Sie in Ihrem Projekt eine zweite Stelle mit demselben Muster. Beheben Sie sie ebenfalls und notieren Sie die Messwerte vorher und nachher.

7. Sehen Sie sich Ihre Listenabfragen an und prüfen Sie, ob Felder geladen werden, die in der Liste nie angezeigt werden, etwa lange Texte oder Bilder. Schränken Sie die Feldauswahl ein und messen Sie die Größe der Antwort vorher und nachher.

Teil C - Indizes und die Werkzeugwahl

1. Nehmen Sie die SQL-Abfrage aus Teil B Punkt 5 und führen Sie sie in psql mit EXPLAIN ANALYZE aus. Notieren Sie die vollständige Ausgabe.

2. Suchen Sie in der Ausgabe zwei Angaben und notieren Sie sie: steht dort Seq Scan oder Index Scan, und wie viel Zeit wurde tatsächlich verbraucht?

3. Legen Sie einen passenden Index als Migration an. Bei einer Abfrage nach Maschine und Startzeit ist das ein zusammengesetzter Index über beide Spalten.

4. Führen Sie EXPLAIN ANALYZE erneut aus und stellen Sie beide Ausgaben gegenüber. Notieren Sie die Zeit vorher und nachher.

5. Prüfen Sie die Reihenfolge der Spalten. Setzen Sie eine Abfrage ab, die nur nach der hinteren Spalte Ihres Index filtert, und sehen Sie mit EXPLAIN ANALYZE nach, ob der Index benutzt wird. Erklären Sie das Ergebnis in drei bis vier Sätzen.

6. Ein Index beschleunigt das Lesen und verlangsamt das Schreiben. Messen Sie die Zeit für das Einfügen von tausend Zeilen einmal mit und einmal ohne Ihren Index. Notieren Sie beide Zeiten und beurteilen Sie in zwei bis drei Sätzen, ob sich der Index lohnt.

7. Suchen Sie in Ihrem Projekt eine Auswertung mit Gruppierung oder Aggregat, etwa eine Anzahl je Maschine oder eine Summe je Monat. Schreiben Sie sie einmal mit dem ORM und einmal als handgeschriebene SQL-Abfrage. Vergleichen Sie beide nach drei Fragen: Welche ist kürzer, welche ist verständlicher, und welche erzeugt weniger Abfragen?

8. Entscheiden Sie begründet, welche der beiden Varianten in Ihr Projekt kommt, und halten Sie in zwei bis drei Sätzen fest, nach welcher Regel Sie diese Entscheidung künftig treffen.

9. Legen Sie die Messungen aus den Teilen B und C als Abschnitt in docs/betrieb.md ab, jeweils mit den Werten vorher und nachher.

Wissenscheck

1. Was ist ein N+1-Problem, und woran erkennen Sie es im Abfrageprotokoll?
2. Warum bleibt dieser Fehler mit wenigen Testdaten unsichtbar?
3. Wie beheben Sie ein N+1, und was passiert dabei mit der Zahl der Abfragen?
4. Wovor schützt eine Obergrenze in einer Listenabfrage?
5. Was bedeutet Seq Scan in der Ausgabe von EXPLAIN ANALYZE, und wann ist er unproblematisch?
6. Warum ist bei einem zusammengesetzten Index die Reihenfolge der Spalten entscheidend?
7. Warum ist ein Index auf jeder Spalte keine gute Idee?

Abgabe

Die behobenen Stellen liegen im Repository, ebenso die Migration mit dem Index. In docs/betrieb.md stehen die Messungen vorher und nachher für das N+1 und für den Index. In protokoll.md stehen die Abfragezählungen aus Teil A, die beiden EXPLAIN ANALYZE-Ausgaben, die Schreibzeitmessung und der Vergleich zwischen ORM und handgeschriebener Abfrage samt Ihrer Regel.