Des N+1-Query-Problem und wia ma's vameidet

#sql#databases#performance

A Seitn, de 50 Blogposts mit dem Nama vom jeweiligen Autor listet, fühlt si in da Entwicklung mit a poar Testzeilen sofort an, und fällt dann in Produktion zamm, sobois echte Daten kemman - ned weil de Query langsam is, sondern weil's ned oane Query is. Es sand 51: oane, um die Posts zu holn, und oane pro Post, um den Autor zu holn. Des is des N+1-Problem, und es is oans von de häufigsten Performance-Bugs in Anwendungen auf ORM-Basis, genau weil da ORM es unsichtbar mocht, bis ma danoch sucht.

Wia es auftaucht

const posts = await Post.findAll(); // 1 query

for (const post of posts) {
  post.author = await User.findByPk(post.authorId); // N queries, one per post
}

Nix do schaut falsch aus - es is lesbar, es is die offensichtliche Art, es zu schreiben, und mit 5 Test-Posts lauft's in Millisekunden. Mit 5.000 Posts in Produktion sand's 5.001 Roundtrips zur Datenbank, und die Roundtrip-Latenz (ned die Query-Ausführungszeit) dominiert. Selbst bei sehr schnellen 2ms pro Roundtrip sand des 10 Sekunden, de fost nur mit Warten aufs Netzwerk draufgehn, für Daten, de a einzige gut gformte Query in oam Roundtrip liefern kunnt.

Es erkennen

Das Kennzeichen is a Query-Anzoi, de mit da Größe vom Ergebnis mitwächst. De meisten ORMs und Frameworks hom a Möglichkeit, jede Query pro Request zum loggn - schalt's für a verdächtige Seitn ein und zähl. Wenn s'Laden ana Liste mit 10 Einträge ungefähr 11 Queries auslöst und a Liste mit 100 Einträge ungefähr 101, is des N+1.

// Sequelize example: log every query for the duration of one request
const posts = await Post.findAll({ logging: console.log });

Wenn dieselbe SELECT * FROM users WHERE id = ?-Form einmal pro Zeile wiederkehrt und sich nur da Parameter ändert, is des des Erkennungszeichen.

Die Lösung: Eager Loading

Lad de zugehörigen Daten in derselben Query oda in oaner gebündelten Folge-Query, statt oane Query pro Zeile.

Join-basiertes Eager Loading, mit dem eingbauten Relationen-Laden vom ORM:

// Sequelize
const posts = await Post.findAll({ include: User }); // 1 query, joined

// Prisma
const posts = await prisma.post.findMany({ include: { author: true } });

// Django ORM
posts = Post.objects.select_related('author')

Des löst oane einzige Query mit am JOIN aus, sodass die Autorendaten an jeder Post-Zeile im söbn Ergebnis dranhängn.

Gebündeltes Laden, wenn a Join ned passt (große zugehörige Collection pro Zeile, wo's Joinen de Zeilenanzoi vervielfachen würd):

// 2 queries total, not N+1
const posts = await Post.findAll();
const authorIds = [...new Set(posts.map((p) => p.authorId))];
const authors = await User.findAll({ where: { id: authorIds } });

const authorsById = new Map(authors.map((a) => [a.id, a]));
for (const post of posts) {
  post.author = authorsById.get(post.authorId);
}

Oane Query für die Posts, oane Query für olle Autoren, auf de die Posts verweisen (mit dem Set dedupliziert), dann im Anwendungsspeicher zammgfügt. Zwoa Roundtrips, egal ob's 10 oda 10.000 Posts gibt.

In reinem SQL, die söbe Idee

Wennst gor ned über an ORM gehst, is das Eager-Loading-Äquivalent einfach, den Join söba zum schreiben, statt zum loopen und abzufragen:

-- N+1: one query per post, issued from application code in a loop
SELECT * FROM posts;
SELECT * FROM users WHERE id = ?; -- repeated per post

-- one query
SELECT posts.*, users.name AS author_name
FROM posts
JOIN users ON users.id = posts.author_id;

Das N+1-Problem is eigentlich ka ORM-Problem - ORMs machen's nur leicht, des Loop-und-Query-Muster aus Versehn zum schreiben, weil post.author wia a normaler Property-Zugriff ausschaut und die Query versteckt, die er auslöst.

Der verschachtelte Foi: N+1 an Level tiefer

Es potenziert si, wenn's eine weitere Relation an Level weiter unten gibt - sagn ma Kommentare zu jedem Post:

const posts = await Post.findAll({ include: User }); // fixed the first N+1

for (const post of posts) {
  post.comments = await Comment.findAll({ where: { postId: post.id } }); // reintroduced it
}

Den Autor-Lookup zu reparieren hüft nix, wenn Kommentare no immer in ana Pro-Post-Schleifn gholt wern. Lad verschachtelte Relationen auf de söbe Art eager:

const posts = await Post.findAll({ include: [User, Comment] });

Wenn olles eager zu laden söba des Problem is

Den umgekehrten Fehler gibt's a: jede Relation bei jeder Query "für alle Foi" eager zu laden holt weit mehr Daten, ois de meisten Requests brauchn, und kann an billigen Ein-Tabellen-Lookup in a Query verwandeln, de jedes Moi über sechs Tabellen joint. Die Lösung is ned "immer eager laden" - es is, zu laden, wos a Codepfad tatsächlich nutzt, wos meist heißt, Includes pro Query explizit zu machen, statt an pauschalen Default zu setzen.

Fazit

N+1-Queries sand im Code-Review und in an klanen Entwicklungs-Datensatz unsichtbar, und werdn erst zum echten Problem, sobois Daten in Produktionsgröße die Query-Anzoi - ned die Komplexität ana einzelnen Query - zum Flaschenhals mochen. Schau dir echte Query-Logs gegn realistische Daten o, ned nur die Korrektheit von Queries gegn a poar Testzeilen, und greif zu Eager Loading (a Join oda a gebündelte Folge-Query), sobois a Schleifn pro Durchlauf a Query absetzt. Und hör auf, in Schleifn Queries abzufeuern, du Gfrast!