--- title: 'Datenbank-Abfrageoptimierung: Master Class | DevSense' description: 'Lernen Sie, wie Sie EXPLAIN und EXPLAIN ANALYZE lesen, Joins und CTEs optimieren, Keyset-Pagination implementieren und das Lastverhalten von MVCC in Datenbank-Engines verstehen.' faq: - { question: 'Was ist der Unterschied zwischen einem Sequential Scan und einem Index Scan?', answer: 'Ein Sequential Scan liest jede Seite in der Tabelle von Anfang bis Ende, um passende Zeilen zu finden. Ein Index Scan durchläuft den Indexbaum, um passende Einträge zu lokalisieren, und ruft dann nur die entsprechenden Seiten aus dem Heap ab. Bei kleinen Tabellen oder Abfragen, die einen großen Prozentsatz der Zeilen abrufen, ist ein Seq Scan oft schneller als die zufälligen I/O-Zugriffe eines Index Scans.' } - { question: 'Was sind Nested Loops, Hash Joins und Merge Joins?', answer: 'Nested Loops verbinden Tabellen, indem sie die äußere Tabelle scannen und passende Zeilen in der inneren Tabelle nachschlagen (optimal für kleine Datenmengen). Hash Joins erstellen im Arbeitsspeicher eine Hashtabelle aus der kleineren Relation und scannen die größere Relation, um Übereinstimmungen zu finden (optimal für große, unsortierte Mengen). Merge Joins sortieren beide Relationen nach dem Join-Schlüssel und scannen sie parallel (optimal für große, bereits sortierte Mengen oder wenn Index Scans eine Sortierung vermeiden können).' } - { question: 'Warum ist Keyset-Pagination schneller als LIMIT/OFFSET?', answer: 'LIMIT/OFFSET-Abfragen müssen alle Zeilen bis zum Offset-Wert lesen und verwerfen, was zu einer O(N)-Leistungsdegradation bei tieferen Seiten führt. Die Keyset-Pagination verwendet einen WHERE-Filter auf einer sortierten, eindeutigen Spalte (wie `WHERE id > ?`), um mithilfe eines Index direkt zur Zielzeile zu springen, wodurch ein O(log N) Lookup in konstanter Zeit erreicht wird.' } - { question: 'Was ist eine CTE-Optimierungsbarriere (Optimization Fence)?', answer: 'In älteren Postgres-Versionen (vor Version 12) und optional in neueren Versionen unter Verwendung von `MATERIALIZED` fungieren Common Table Expressions (CTEs) als Optimierungsbarriere. Die Datenbank führt das CTE zuerst aus, speichert das Ergebnis in einem temporären Bereich und verknüpft es erst danach. Dies verhindert, dass der Optimizer Prädikate der äußeren Abfrage (wie WHERE-Klauseln) in das CTE hinabprojiziert (Push-Down), was zu langsamer Ausführung führen kann.' } published: '2026-05-31' --- # Datenbank-Abfrageoptimierung: Master Class Eine einzige langsame Abfrage kann eine gesamte Unternehmensanwendung lahmlegen. In der Produktion geht es bei der Datenbankleistung selten um CPU-Leistung; entscheidend ist vielmehr, wie effizient der Query-Planner Festplattenseiten durchläuft, Join-Tabellen im Speicher aufbaut und Nebenläufigkeit handhabt. Wenn Sie eine SQL-Abfrage schreiben, beschreiben Sie, *welche* Daten Sie wollen, nicht *wie* diese abgerufen werden sollen. Der Query-Optimizer der Datenbank-Engine ist für die Planung des Ausführungsplans verantwortlich. Um hochperformante Anwendungen zu schreiben, müssen Sie lernen, wie der Optimizer zu denken. In dieser Master Class werden wir Ausführungspläne untersuchen, Join-Algorithmen sezieren, die Skalierung von Paginierung erforschen und die MVCC-Mechanik von PostgreSQL und MySQL analysieren. --- ## 1. Dekodierung von EXPLAIN und EXPLAIN ANALYZE Um eine Abfrage zu optimieren, müssen Sie zuerst deren Ausführungsplan mithilfe von `EXPLAIN` inspizieren. Ein standardmäßiges `EXPLAIN` zeigt jedoch nur die *Schätzung* des Optimizers bezüglich der Abfragekosten. Um zu sehen, was tatsächlich während der Ausführung passiert ist, müssen Sie `EXPLAIN ANALYZE` verwenden (wodurch die Abfrage tatsächlich ausgeführt wird). ### Die Kostenindikatoren verstehen (Postgres-Format) Ein Ausführungsplan zeigt Kosten im Format: `cost=0.00..431.25 rows=10500 width=244` an. * **Startup-Kosten (`0.00`):** Die Kosten, die anfallen, bevor die erste Zeile zurückgegeben werden kann (z. B. das Erstellen einer Hashtabelle oder das Sortieren von Indexknoten). * **Gesamtkosten (`431.25`):** Die geschätzten Kosten für die Rückgabe aller Zeilen. Dies wird in willkürlichen Einheiten von Seitenzugriffen gemessen (typischerweise `1.0` für sequenzielles Lesen von Seiten, `4.0` für zufälliges Lesen von Seiten). * **Zeilen (`10500`):** Die geschätzte Anzahl der zurückgegebenen Zeilen (Rows). * **Breite (`244`):** Die durchschnittliche Größe der zurückgegebenen Zeilen in Byte (Width). ### Knotenscan-Typen 1. **Sequential Scan (Seq Scan):** Die Engine liest die gesamte Tabelle von Anfang bis Ende. Dies ist optimal für kleine Tabellen oder wenn $> 20\%$ der Tabellenzeilen abgerufen werden. 2. **Index Scan:** Die Engine durchläuft den Index-B-Baum, ruft passende TIDs/PKs ab und holt dann die entsprechenden Seiten aus dem Heap bzw. der Tabelle. Dies erfordert zufälligen Festplattenzugriff. 3. **Index Only Scan:** Wenn alle ausgewählten Spalten im Index selbst enthalten sind, umgeht die Datenbank das Lesen der Tabelle komplett. 4. **Bitmap Index Scan & Bitmap Heap Scan (Postgres):** Beim Abrufen mehrerer passender Zeilen über einen Index erstellt Postgres zuerst eine Bitmap der passenden Seiten im Speicher (Bitmap Index Scan) und liest diese Seiten dann in sequenzieller physischer Reihenfolge (Bitmap Heap Scan). Dadurch wird langsamer, zufälliger I/O in schnelleren, sequenziellen I/O umgewandelt. ### Join-Algorithmen * **Nested Loop:** Die Datenbank scannt die äußere Tabelle und sucht für jede Zeile nach passenden Zeilen in der inneren Tabelle. Dies ist extrem schnell für kleine Datensätze, insbesondere wenn die Join-Spalte der inneren Tabelle indiziert ist. * **Hash Join:** Die Datenbank scannt die kleinere Tabelle, erstellt im Speicher eine Hashtabelle aus den Join-Schlüsseln und scannt dann die größere Tabelle, wobei sie deren Schlüssel hasht, um sofortige Übereinstimmungen zu finden. Dies ist optimal für große, unsortierte Datensätze. * **Merge Join:** Beide Tabellen werden nach ihren Join-Schlüsseln sortiert, und die Datenbank scannt sie parallel, um passende Zeilen zusammenzuführen. Dies ist hochgradig effizient für sehr große Datensätze, insbesondere wenn sie bereits durch Indizes vorsortiert sind. --- ## 2. Fortgeschrittene Join-Optimierung & CTEs Nicht alle Joins sind gleich aufgebaut. Die Art und Weise, wie Sie Subqueries und CTEs schreiben, beeinflusst stark die Fähigkeit des Optimizers, Pfade zu kürzen. ### Inner vs. Outer Joins und Anti-Joins Ein Anti-Join findet Datensätze in einer Tabelle, die in einer anderen *nicht* existieren. Entwickler schreiben dies oft mit `NOT IN`, was eine sehr schlechte Leistung aufweist und fehlschlägt, wenn die Unterabfrage `NULL` zurückgibt. Ein hochgradig optimierter Ansatz ist die Verwendung von `NOT EXISTS`. ```sql -- database/queries/anti_join_bad.sql -- Schlecht: NOT IN scannt die Unterabfrage und verhält sich unerwartet, wenn NULLs vorhanden sind SELECT id, name FROM users WHERE id NOT IN (SELECT user_id FROM orders); -- database/queries/anti_join_good.sql -- Gut: NOT EXISTS übersetzt sich in einen hochoptimierten Hash Anti Join oder Merge Anti Join SELECT u.id, u.name FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id ); ``` ### LATERAL Joins (Korrelierte Unterabfragen) Ein `LATERAL` Join verhält sich wie eine SQL-`foreach`-Schleife. Er ermöglicht es einer Unterabfrage, auf Spalten aus vorhergehenden Tabellen in der `FROM`-Klausel zu verweisen. Dies ist unglaublich nützlich, um die „Top N“-Datensätze pro Gruppe abzurufen. ```sql -- database/queries/lateral_join.sql -- Ruft die neuesten 3 Bestellungen für jeden Benutzer ab (Postgres & MySQL 8.0.14+) SELECT u.id, u.name, recent_orders.id AS order_id, recent_orders.amount, recent_orders.created_at FROM users u CROSS JOIN LATERAL ( SELECT o.id, o.amount, o.created_at FROM orders o WHERE o.user_id = u.id ORDER BY o.created_at DESC LIMIT 3 ) AS recent_orders; ``` ### CTE-Optimierungsbarrieren (Fences) Common Table Expressions (CTEs) machen Abfragecode übersichtlich, fungierten jedoch historisch als **Optimierungsbarrieren**. * **Postgres (Vor Version 12):** CTEs wurden vor der Ausführung der äußeren Abfrage immer materialisiert (berechnet und in temporären Speicher geschrieben). Dies verhinderte die Nutzung von Indizes aus äußeren WHERE-Klauseln. * **Postgres (Ab Version 12):** CTEs werden jetzt standardmäßig inline ausgeführt, es sei denn, sie sind rekursiv oder haben Seiteneffekte. Sie können die Materialisierung mit `MATERIALIZED` oder `NOT MATERIALIZED` explizit erzwingen oder verhindern. ```sql -- database/queries/cte_fence.sql -- Verhindert die Materialisierung, damit der Query-Planner das user_id-Prädikat nach unten weitergeben kann WITH user_stats AS NOT MATERIALIZED ( SELECT user_id, COUNT(*) as total_orders, SUM(amount) as total_spent FROM orders GROUP BY user_id ) SELECT u.name, s.total_orders, s.total_spent FROM users u JOIN user_stats s ON u.id = s.user_id WHERE u.id = 42; ``` --- ## 3. Skalierung von Abfragepfaden: Keyset-Pagination & Partitionierung Wenn Ihre Datenbanktabellen auf Milliarden von Zeilen anwachsen, werden einfache Abfragen langsamer, es sei denn, Sie ändern die Art und Weise, wie Sie Datenpfade scannen. ### Keyset-Pagination (Cursor-basiert) vs. LIMIT / OFFSET Die Offset-Paginierung (`LIMIT 10 OFFSET 100000`) zwingt die Datenbank, 100.000 Zeilen zu lesen und zu verwerfen, nur um 10 zurückzugeben. Die Leistung nimmt linear ab ($O(N)$) und verschlechtert sich bei tiefen Seiten drastisch. Die Keyset-Paginierung filtert bereits gesehene Zeilen mithilfe einer WHERE-Klausel auf einem sortierten Index heraus und skaliert mit $O(\log N)$ konstanter Suchzeit. ```sql -- database/queries/offset_pagination_bad.sql -- Schlecht: Scannt und verwirft 100.000 Datensätze, bevor 10 zurückgegeben werden SELECT id, amount, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT 10 OFFSET 100000; -- database/queries/keyset_pagination_good.sql -- Gut: Springt direkt zum zuletzt gesehenen Keyset mittels eines zusammengesetzten Index-Scans SELECT id, amount, created_at FROM orders WHERE (created_at, id) < ('2026-05-30 15:30:00', 987654) ORDER BY created_at DESC, id DESC LIMIT 10; ``` > [!NOTE] > Die Keyset-Paginierung erfordert eine deterministische Sortierung. Wenn Sie nach einer nicht eindeutigen Spalte wie `created_at` sortieren, müssen Sie eine eindeutige Spalte wie `id` als Entscheidungsmerkmal anhängen, um fehlende oder doppelte Zeilen zu verhindern. ### Tabellenpartitionierung Die Tabellenpartitionierung unterteilt eine große Tabelle in kleinere physische Tabellen (Partitionen) basierend auf einem Schlüssel (z. B. Datumsbereiche), während eine einzige logische Schnittstelle beibehalten wird. * **Query Pruning (Abfrage-Beschneidung):** Wenn eine Abfrage nach dem Partitionsschlüssel filtert, ignoriert der Planner irrelevante Partitionen komplett und scannt nur die passende Partition. ```sql -- database/migrations/2026_05_31_partitioned_orders.sql -- Deklarative Bereichspartitionierung in PostgreSQL CREATE TABLE orders_partitioned ( id BIGINT NOT NULL, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at TIMESTAMP NOT NULL, PRIMARY KEY (created_at, id) ) PARTITION BY RANGE (created_at); -- Einzelne Partitionstabellen erstellen CREATE TABLE orders_2026_q1 PARTITION OF orders_partitioned FOR VALUES FROM ('2026-01-01 00:00:00') TO ('2026-04-01 00:00:00'); ``` --- ## 4. MVCC, Vacuuming und Purge Lag Die Nebenläufigkeit in modernen Datenbanken wird durch **Multi-Version Concurrency Control (MVCC)** erreicht. Anstatt Zeilen für Lesevorgänge zu sperren, halten Datenbanken mehrere Versionen einer Zeile gleichzeitig vor. ### PostgreSQL MVCC & Autovacuum In Postgres wird jede Zeile (Tuple) auf der Festplatte mit Metadaten-Headern gespeichert, einschließlich `xmin` (die Transaktions-ID, die die Zeile erstellt hat) und `xmax` (die Transaktions-ID, die die Zeile gelöscht bzw. ablaufen lassen hat). * **Updates:** Ein `UPDATE` überschreibt die Zeile nicht. Es schreibt ein komplett neues Tuple in den Heap und setzt `xmax` des alten Tuples auf die aktualisierende Transaktion. * **Bloat (Aufblähung):** Die alte Version der Zeile wird zu einem „Dead Tuple“ (toten Tuple), sobald keine aktive Transaktion sie mehr sehen kann. * **Vacuuming:** Postgres benötigt `VACUUM` (verwaltet vom Autovacuum-Daemon), um Seiten zu scannen, den von toten Tuples belegten Speicherplatz zurückzufordern und die Sichtbarkeitskarte (Visibility Map) zu aktualisieren. Wenn Autovacuum bei schreibintensiven Workloads nicht hinterherkommt, beeinträchtigt Tabellen- und Index-Bloat die Abfrageleistung. ### MySQL InnoDB MVCC & Undo Logs Die InnoDB-Engine von MySQL handhabt MVCC anders. * **Updates im Clustered Index:** InnoDB führt Updates direkt an Ort und Stelle innerhalb des Blattknotens des Clustered Index durch. * **Undo Logs & Rollback Segments:** Die alte Version der Zeile wird nicht in der Haupttabelle gespeichert. Stattdessen schreibt InnoDB den historischen Zustand der Zeile in das **Undo Log** und speichert einen Rollback-Pointer in der aktualisierten Zeile. Wenn eine lesende Transaktion eine ältere Version benötigt, liest sie die aktuelle Zeile und rekonstruiert den alten Zustand mithilfe der Undo-Logs. * **Purge-Threads:** Wenn alte Transaktionen abgeschlossen sind, bereinigt ein Hintergrundprozess namens **Purge Thread** die Undo-Logs und löscht Datensätze, die zum Löschen markiert wurden. Bei hohen Schreiblasten kann ein **Purge Lag** auftreten, was zu einem enormen Anwachsen der Undo-Log-Dateien und zu Leistungseinbußen führt. --- ## 5. Häufige Fehler ### Fehler 1: Tiefe Paginierung mit OFFSET **Schlechte Praxis:** ```sql -- database/queries/bad_pagination.sql SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 50000; ``` *Warum es schlecht ist:* Die Datenbank muss den Index/Heap für 50.000 Datensätze scannen, diese in den Speicher laden und verwerfen, was CPU- und Festplatten-I/O verbraucht. **Gute Praxis:** ```sql -- database/queries/good_pagination.sql -- Keyset-Pagination unter Verwendung der zuletzt gesehenen ID (z.B. 50000) SELECT * FROM users WHERE id > 50000 ORDER BY id LIMIT 20; ``` *Warum es gut ist:* Die Datenbank führt ein Index-Seek durch, um in $O(\log N)$ Zeit direkt zu `id > 50000` zu springen, wodurch unnötige Zeilenscans vermieden werden. ### Fehler 2: Fehlender Index auf Fremdschlüsseln (Foreign Keys) **Schlechte Praxis:** ```sql -- database/migrations/bad_foreign_key.sql CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT, FOREIGN KEY (user_id) REFERENCES users(id) ); ``` *Warum es schlecht ist:* PostgreSQL und MySQL erstellen nicht automatisch Indizes auf Fremdschlüsselspalten. Wenn Sie Tabellen häufig über `user_id` verknüpfen oder Benutzer löschen (was Kaskadenprüfungen auslöst), muss die Engine einen sequenziellen Scan auf der Kindtabelle durchführen. --- ## 6. Selbsttest-Quiz ### Frage 1 Während eines `EXPLAIN ANALYZE`-Laufs in Postgres bemerken Sie einen Knoten mit der Bezeichnung `Bitmap Heap Scan` nach einem `Bitmap Index Scan`. Was bedeutet das?
Antworten anzeigen **Antwort:** Es zeigt an, dass Postgres entschieden hat, dass das direkte Lesen von Zeilen über einzelne Index-Seeks zu langsamen, zufälligen I/O-Zugriffen führen würde. Stattdessen passiert Folgendes: 1. Der `Bitmap Index Scan` scannt den Index und baut im Speicher eine Bitmap der Tabellenseitenadressen auf, die passende Zeilen enthalten. 2. Der `Bitmap Heap Scan` liest dann diese Tabellenseiten in sequenzieller physischer Reihenfolge und greift auf die passenden Zeilen dieser Seiten zu. Dies wandelt zufälligen I/O in schnellere sequenzielle Festplattenlesevorgänge um.
--- ### Frage 2 Warum schwillt die Größe des Undo-Log-Tablespace unter hoher Schreiblast in MySQL InnoDB an und wie wirkt sich dies auf Leseabfragen aus, die ältere Daten benötigen?
Antworten anzeigen **Antwort:** Wenn Schreibvorgänge sehr intensiv sind, kann der Purge-Thread bei der Bereinigung historischer Rollback-Segmente in Rückstand geraten (Purge Lag). Folglich wächst der Undo-Log-Speicherplatz schnell, um den Überblick über ältere Zeilenversionen zu behalten. Ältere lesende Transaktionen erfahren eine schlechtere Leistung, da sie eine lange Kette von Undo-Logs durchlaufen müssen, um den Zustand der Daten zum Startzeitpunkt ihrer Transaktion zu rekonstruieren.
--- ### Frage 3 Warum kann ein CTE, das mit einer standardmäßigen `WITH`-Klausel in Postgres 10 definiert ist, zu einem Leistungsengpass führen, wenn die äußere Abfrage nach einer bestimmten ID filtert?
Antworten anzeigen **Antwort:** In Postgres 10 (und älteren Versionen) wirken CTEs als Optimierungsbarriere. Die Engine wertet das CTE unabhängig aus und materialisiert seine vollständigen Ergebnisse im Speicher oder auf der Festplatte. Erst danach führt sie die äußere Abfrage aus und wendet den Filter `WHERE id = 42` an. In Postgres 12+ (oder durch Schreiben von `WITH ... AS NOT MATERIALIZED`) kann der Optimizer das CTE „inlinen“, was es ihm ermöglicht, den Filter `WHERE id = 42` in die Unterabfrage hinabzuprojizieren und so indexgestützte Scans zu ermöglichen.