Antwort.

Historie der Frage:
SQL-Abfragen wurden von Anfang an als deklarativ konzipiert: „Was zu erhalten“, nicht „Wie zu erhalten“. Der Optimierer des DBMS erstellt einen Ausführungsplan, der die Reihenfolge von Joins, Filterungen, Scans und die Nutzung von Indizes bestimmt.

Problem:
Ohne Verständnis des Ausführungsplans ist es unmöglich zu erklären, warum eine scheinbar einfache Abfrage zu langsam ist, während eine komplexe schnell läuft. Ein falscher Plan kann den Server wegen unnötiger Operationen oder falscher Nutzung von Indizes stundenlang blockieren.

Lösung:
Analysetools sind Anweisungen wie EXPLAIN, EXPLAIN ANALYZE (PostgreSQL), SHOW EXPLAIN (MySQL), Management Studio Execution Plan (SQL Server). Mit diesen kann man Schritt für Schritt sehen, wie die Abfrage tatsächlich ausgeführt wurde, welches Datenvolumen gelesen wurde, welcher Index verwendet wurde, wo vollständige Tabellen-Scans stattfanden und wo Verzögerungen auftraten.

Beispielcode:

EXPLAIN ANALYZE SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'shipped';

Schlüsselmerkmale:

Der Plan gibt ein detailliertes Schema, welche Operation in welcher Reihenfolge vom DBMS ausgeführt wurde.
Man kann sehen, wo ein Index-Scan verwendet wurde und wo ein Tabellen-Scan.
Ermöglicht das Erkennen von Engpässen für Optimierungen und die Überprüfung der Hypothese über die Auswirkungen der Umformulierung der Abfrage auf die Leistung.

Fangfragen.

Wenn ich einen Index hinzufüge, wird die Abfrage immer schneller?

Nein! Ein Index hilft nur, wenn die Filterung nach seinem Feld die Anzahl der zurückgegebenen Zeilen erheblich einschränkt. Wenn die meisten Datensätze dem Kriterium entsprechen, kann der Optimierer den Index ignorieren.

Beispiel:

-- Das Feld 'gender' hat nur zwei Werte – der Index hilft nicht
CREATE INDEX idx_gender ON people(gender);
SELECT * FROM people WHERE gender = 'M';

Hängt die Reihenfolge der Tabellen in JOIN von dem Ergebnis der Ausführung ab?

Nein, die endgültigen Daten sind identisch, jedoch kann der Optimierer die Reihenfolge der Ausführung von Joins zur Leistungssteigerung ändern. Aber wenn ein spezifischer JOIN oder Hinweise wie „JOIN HINT“ geschrieben sind, kann die Reihenfolge die Effizienz der Ausführung beeinflussen.

Warum sollte man "Estimated rows" und "Actual rows" im Ausführungsplan analysieren?

Die Unterschiede zwischen ihnen können zeigen, dass die Statistiken zu den Tabellen veraltet oder nicht realistisch sind, und der gewählte Plan ist nicht optimal – man sollte die Statistiken aktualisieren oder die Struktur der Abfrage überdenken.

-- PostgreSQL
ANALYZE table_name; -- Statistiken aktualisieren

Typische Fehler und Anti-Patterns

Der Ausführungsplan wird bei Problemen nicht betrachtet: Man versucht, „blind“ zu optimieren.
Zusätzliche/nicht zielgerichtete Indizes werden zur „Beschleunigung“ hinzugefügt, was nur die Datenänderung verlangsamt.
Statistiken zu Tabellen werden nicht aktualisiert.
Abfragen werden ohne Berücksichtigung des Verhaltens des Optimierers entworfen.

Beispiel aus der Praxis

Negativer Fall

Im Projekt beschwerten sich Analysten lange über das „Einfrieren“ von Berichten. Eine Abfrage mit fünf JOINs dauerte 25 Minuten. Es stellte sich heraus, dass ein Plan mit vollständigem Scannen einer riesigen Tabelle anstelle eines Index-Scans gewählt wurde, die Indizes waren nicht für die richtigen Felder und die Statistiken wurden ein Jahr lang nicht aktualisiert.

Vorteile:

Einfache Wartung, Minimierung der Indizes

Nachteile:

Die Abfrage war langsam, es wurden viele Stunden menschlicher Arbeit aufgewendet.
Unzufriedenheit der Benutzer.
Last auf dem Server.

Positiver Fall

Wir haben den Ausführungsplan analysiert, einen Index für das tatsächlich filternde Feld hinzugefügt und die Statistiken aktualisiert. Die Abfragezeit reduzierte sich auf 20 Sekunden. Die Last auf dem Server wurde um den Faktor 10 gesenkt.