Effizientere SQL Abfragen durch Planung und Optimierung

Nun, da du viele Möglichkeiten zum Selektieren von Daten inklusive SELECTs über mehrere Tabellen kennengelernt hast, ist es ein guter Zeitpunkt, die Effizienz von SQL-Abfragen anzusprechen. Wie schnell werden diese ausgeführt? Wie können sie schneller ausgeführt werden?

SQL ist eine beschreibende (deklarative) Sprache. Jede Abfrage beschreibt für die SQL Engine, was für Daten wir wollen, aber sie sagt nicht, wie dies zu tun ist. Nun ist es aber so, dass das wie -- der "Plan" -- für die Effizienz der Abfragen sehr wichtig ist.

Weshalb sollte man SQL-Abfragen planen?

Schauen wir uns zum Beispiel die folgende, einfache Abfrage an:

SELECT * FROM books WHERE author = "J K Rowling";

Bei dieser Abfrage gibt es 2 verschiedene Techniken, wie SQL die Ergebnisse ermitteln kann:

Vollständigen "Tabellenscan": Jede einzelne Zeile der Tabelle anschauen und die übereinstimmenden Zeilen zurückgeben.
Einen "Index" erstellen: Eine Kopie der nach Autor sortierten Tabelle erstellen, dann darauf eine binäre Suche durchführen, um die Zeile, bei welcher der Autor "J K Rowling" ist, zu finden, die übereinstimmenden IDs ermitteln und dann eine Binärsuche auf der ursprünglichen Tabelle durchführen, um die Zeilen mit der entsprechenden ID zurückzugeben.

Was ist schneller? Dies kommt darauf an, wie groß die Datenmenge ist und wie oft die Abfrage ausgeführt wird. Wenn die Tabelle 10 Zeilen hat, dann müssen für einen Tabellenscan nur 10 Zeilen angeschaut werden, und die erste Möglichkeit ist wohl die bessere.

Wenn die Tabelle 10 Millionen Zeilen hat, würde dieser vollständige Tabellenscan das Prüfen aller 10 Millionen Zeilen erfordern. Es wäre viel schneller, eine binäre Suche in einer sortierten Tabelle zu machen - dann müssen nur 23 Lookup-Operationen gemacht werden, um einen Wert in 10 Millionen Zeilen zu finden. Aber es würde lange dauern, um die Tabelle zu sortieren (je nach Algorithmus ~230 Millionen Operationen). Wenn wir diese Abfrage ganz oft durchführen (mehr als 23 Mal) oder wenn die Tabelle schon besteht, dann wäre die zweite Technik schneller.

Wie entscheidet die SQL-Engine, welche Technik sie verwendet? Dies ist ein wichtiger Schritt, den wir noch nicht besprochen haben, da wir uns auf die Syntax unserer Abfragen und nicht auf ihre Ausführung fokussiert haben. Wenn du aber mehr Erfahrung mit großen Datenbanken gewinnst, wird die Planung der Abfragen immer wichtiger.

Der Lebenszyklus einer SQL-Abfrage

Wir können uns vorstellen, dass eine SQL-Engine für jede SQL-Abfrage diese Schritte durchläuft:

Der Parser der Abfrage überprüft, ob die Abfrage syntaktisch (z. B. Kommas am falschen Platz) und semantisch (z. B., ob die Tabelle existiert) korrekt ist und gibt ansonsten einen Fehler zurück. Wenn sie korrekt ist, wird die Abfrage in einen algebraischen Ausdruck umgewandelt und dem nächsten Schritt übergeben.
Der Planer und Optimizer macht nun die harte Arbeit. Zuerst führt er einfache Optimierungen durch (Optimierungen, die die Performanz immer verbessern, wie z. B. 5*10 zu 50 vereinfachen). Dann betrachtet er verschiedene "Abfragepläne", welche unterschiedliche Optimierungen haben, schätzt, basierend auf der Anzahl Zeilen in den relevanten Tabellen, die Aufwände (CPU und Zeit) jedes Planes, wählt den den optimalen Abfrageplan aus und übergibt alles dem nächsten Schritt.
Der Query Executor nimmt den Abfrageplan, wandelt diesen in Operationen für die Datenbank um und gibt die Ergebnisse, falls vorhanden, zurück.

Wo kommen die Menschen ins Spiel?

Die Planung und Optimierung der Abfrage geschieht für jede Abfrage automatisch. Du könntest dein ganzes Leben lang SQL-Abfragen schreiben und dies nie bemerken. Sobald du aber beginnst, mit größeren Datenmengen zu arbeiten, wirst du dich um die Geschwindigkeit deiner Abfragen kümmern. Du wirst dich wohl fragen, wie du es schaffst, die Performance deiner Abfragen zu verbessern.

Für komplexe Abfragen gibt es tatsächlich Wege, wie man Abfragen optimieren kann. Dies wird auch "Query-Tuning" genannt.

Der erste Schritt ist dabei, die zu optimierenden Abfragen zu ermitteln. Dies kannst du machen, indem du ermittelst, welche Abfragen auf deiner Datenbank am längsten brauchen und am meisten Ressourcen verbrauchen. Oft findest du eine langsame Abfrage, nachdem diese so lange gedauert hat, dass gleich die ganze Datenbank abgestürzt ist. Hoffentlich entdeckst du dies aber schon vorher!

Im nächsten Schritt solltest du versuchen, zu verstehen, wie deine SQL-Engine eine bestimmte Abfrage ausführt. Alle SQL-Systeme haben einen Weg, wie man diesen Ablauf ermitteln kann. In SQLite kannst du vor jede Abfrage einfach EXPLAIN QUERY PLAN schreiben, um zu sehen, was im Hintergrund genau abläuft. Wenn du dies machst, dann musst du aber die Referenz zu EXPLAIN QUERY PLAN ganz genau lesen, denn diese "Anleitung" ist sehr detailliert und implementationspezifisch. Wenn du eine andere SQL-Engine benutzt, kannst du im Internet nach "how do I get an execution plan in X" suchen.

Aber erst jetzt kommt der wirklich schwierige Teil: die manuelle Optimierung, um den Ausführungsplan zu verbessern. Dieser Teil hängt auch sehr stark von den speziellen Eigenschaften deiner SQL-Engine und deiner Daten ab.

Erinnerst du dich noch an die Abfrage, welche wir oben besprochen haben? Wenn wir gewusst hätten, das wir hunderte von Abfragen mit einer WHERE-Bedingung auf der Spalte author durchführen werden, dann hätten wir von Anfang an einen Index mit CREATE INDEX erstellt. Denn so könnte die SQL-Engine diesen Index verwenden, um die passenden Zeilen ganz effizient zu finden. Du kannst in der folgenden Anleitung zur Planung von Abfragen mit SQLite eine detaillierte Erklärung, wann Indizes hilfreich sind, finden.

Mit Indizes können sich oft wiederholende Abfragen effizienter werden. Aber es gibt auch viele andere Ansätze. Zu SQLite kannst du mehr in der Übersicht zur Planung von Abfragen finden. Beachte auch die Abschnitte mit "manual" im Titel.

Leider können wir hier nicht alle Spezialfälle der Optimierung von Abfragen behandeln. Tauche tiefer in diese Materie ein, sobald du es brauchst.

(Hier ein paar interessante Links mit detaillierten Informationen zur Optimierung von Abfragen: SQL Server Query Optimizer, Oracle SQL Tuning, MSSQL Execution Plan Basics)

Willst du an der Diskussion teilnehmen?

Anmelden

Sortiere nach:

Noch keine Beiträge.

Verstehst du Englisch? Klick hier, um weitere Diskussionen auf der englischen Khan Academy Seite zu sehen.