SQL-Handbuch: Besseres Schreiben von Abfragen (Teil 2)

Fortsetzung Artikel SQL-Handbuch: Besseres Schreiben von Abfragen (Teil 1)

Von der Anfrage bis zu den Ausführungsplänen

Zu wissen, dass Antimuster nicht statisch sind und sich im Zuge Ihres Wachstums als SQL-Entwickler weiterentwickeln, und die Tatsache, dass beim Überlegen von Alternativen viele Dinge zu beachten sind, bedeutet auch, dass es ziemlich schwierig sein kann, Antimuster zu vermeiden und Abfragen neu zu schreiben Aufgabe. Jede Hilfe kann nützlich sein, weshalb ein strukturierterer Ansatz zur Abfrageoptimierung mit einigen Tools am effektivsten sein kann.

Es sollte auch beachtet werden, dass einige der im letzten Abschnitt erwähnten Antimuster auf Leistungsproblemen beruhen, wie z. B. den Operatoren AND , OR und NOT und deren Abwesenheit bei der Verwendung von Indizes. Das Nachdenken über Leistung erfordert nicht nur einen strukturierteren, sondern auch einen tieferen Ansatz.

Dieser strukturierte und detaillierte Ansatz basiert jedoch hauptsächlich auf dem Abfrageplan, der, wie Sie sich erinnern, das Ergebnis einer Abfrage ist, die zuerst in einen „Analysebaum“ oder einen „Analysebaum“ analysiert wurde und genau bestimmt, welcher Algorithmus wird für jede Operation verwendet und wie ihre Ausführung koordiniert wird.

Abfrageoptimierung

Wie Sie in der Einführung lesen, müssen Sie möglicherweise Pläne überprüfen und einrichten, die vom Optimierer manuell kompiliert werden. In solchen Fällen müssen Sie Ihre Anfrage erneut analysieren, indem Sie sich den Anfrageplan ansehen.

Um auf diesen Plan zugreifen zu können, müssen Sie die vom Datenbankverwaltungssystem bereitgestellten Tools verwenden. Die folgenden Tools stehen Ihnen möglicherweise zur Verfügung:

• Einige Pakete enthalten Tools, die eine grafische Darstellung des Abfrageplans generieren. Betrachten Sie das folgende Beispiel:
  
  
  
• Andere Tools bieten eine Textbeschreibung des Abfrageplans. Ein Beispiel ist die EXPLAIN PLAN Anweisung in Oracle, aber der Name der Anweisung hängt von dem DBMS ab, mit dem Sie arbeiten. An anderer Stelle finden Sie EXPLAIN (MySQL, PostgreSQL) oder EXPLAIN QUERY PLAN (SQLite).

Bitte beachten Sie, dass Sie bei der Arbeit mit PostgreSQL zwischen EXPLAIN unterscheiden können. Hier erhalten Sie einfach eine Beschreibung, wie der Planer die Abfrage ausführen EXPLAIN ANALYZE ohne sie auszuführen, während EXPLAIN ANALYZE die Abfrage tatsächlich ausführt und Ihnen die Analyse zurückgibt erwartete und tatsächliche Anfragepläne. Im Allgemeinen ist ein realer Ausführungsplan ein Plan, in dem eine Anforderung tatsächlich ausgeführt wird, während ein Evaluierungsausführungsplan bestimmt, was er tun wird, ohne die Anforderung zu erfüllen. Obwohl dies logisch äquivalent ist, ist der tatsächliche Ausführungsplan viel nützlicher, da er zusätzliche Informationen und Statistiken darüber enthält, was wirklich passiert ist, als die Anforderung ausgeführt wurde.

Im Rest dieses Abschnitts erfahren Sie mehr über EXPLAIN und ANALYZE sowie deren Verwendung, um weitere Informationen zum Abfrageplan und seiner möglichen Leistung zu erhalten. Beginnen Sie dazu mit einigen Beispielen, in denen Sie mit zwei Tabellen arbeiten: one_million und half_million .

Sie können die aktuellen Informationen mit EXPLAIN aus der Tabelle EXPLAIN . Stellen Sie sicher, dass Sie es direkt über der Anforderung platzieren. Nach der Ausführung wird der Abfrageplan an Sie zurückgegeben:

 EXPLAIN SELECT * FROM one_million; QUERY PLAN ____________________________________________________ Seq Scan on one_million (cost=0.00..18584.82 rows=1025082 width=36) (1 row) 

In diesem Fall sehen Sie, dass die Kosten für die Anforderung 0.00..18584.82 und die Anzahl der Zeilen 1025082 beträgt. Die Breite der Spaltenanzahl beträgt 36 .

Darüber hinaus können Sie Statistiken mit ANALYZE .

 ANALYZE one_million; EXPLAIN SELECT * FROM one_million; QUERY PLAN ____________________________________________________ Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (1 row) 

Neben EXPLAIN und ANALYZE können Sie mit EXPLAIN ANALYZE auch die tatsächliche Laufzeit EXPLAIN ANALYZE :

 EXPLAIN ANALYZE SELECT * FROM one_million; QUERY PLAN ___________________________________________________________ Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.015..1207.019 rows=1000000 loops=1) Total runtime: 2320.146 ms (2 rows) 

Der Nachteil der Verwendung von EXPLAIN ANALYZE besteht darin, dass die Abfrage tatsächlich ausgeführt wird. EXPLAIN ANALYZE also vorsichtig damit!

Bisher sind alle Algorithmen, die Sie gesehen haben, Seq Scan (Sequential Scan) oder Full Table Scan: Dies ist ein Scan, der in einer Datenbank durchgeführt wird, in der jede Zeile der gescannten Tabelle in serieller Reihenfolge gelesen und die gefundenen Spalten überprüft werden Einhaltung der Bedingung oder nicht. In Bezug auf die Leistung sind sequentielle Scans definitiv nicht der beste Ausführungsplan, da Sie immer noch einen vollständigen Tabellenscan durchführen. Dies ist jedoch nicht so schlimm, wenn die Tabelle nicht in den Speicher passt: Sequentielle Lesevorgänge sind selbst auf langsamen Festplatten ziemlich schnell.

Sie werden später mehr darüber erfahren, wenn wir über das Scannen von Indizes sprechen.

Es gibt jedoch andere Algorithmen. Nehmen Sie zum Beispiel diesen Abfrageplan für eine Verbindung:

 EXPLAIN ANALYZE SELECT * FROM one_million JOIN half_million ON (one_million.counter=half_million.counter); QUERY PLAN _________________________________________________________________ Hash Join (cost=15417.00..68831.00 rows=500000 width=42) (actual time=1241.471..5912.553 rows=500000 loops=1) Hash Cond: (one_million.counter = half_million.counter) -> Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.007..1254.027 rows=1000000 loops=1) -> Hash (cost=7213.00..7213.00 rows=500000 width=5) (actual time=1241.251..1241.251 rows=500000 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 770kB -> Seq Scan on half_million (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.008..601.128 rows=500000 loops=1) Total runtime: 6468.337 ms 

Sie sehen, dass der Abfrageoptimierer hier Hash Join hat! Denken Sie an diesen Vorgang, da Sie ihn benötigen, um die zeitliche Komplexität Ihrer Anfrage zu bewerten. Beachten Sie half_million.counter , dass in half_million.counter kein Index half_million.counter , den wir im folgenden Beispiel hinzufügen:

 CREATE INDEX ON half_million(counter); EXPLAIN ANALYZE SELECT * FROM one_million JOIN half_million ON (one_million.counter=half_million.counter); QUERY PLAN ________________________________________________________________ Merge Join (cost=4.12..37650.65 rows=500000 width=42) (actual time=0.033..3272.940 rows=500000 loops=1) Merge Cond: (one_million.counter = half_million.counter) -> Index Scan using one_million_counter_idx on one_million (cost=0.00..32129.34 rows=1000000 width=37) (actual time=0.011..694.466 rows=500001 loops=1) -> Index Scan using half_million_counter_idx on half_million (cost=0.00..14120.29 rows=500000 width=5) (actual time=0.010..683.674 rows=500000 loops=1) Total runtime: 3833.310 ms (5 rows) 

Sie sehen, dass das Abfrageoptimierungsprogramm beim Erstellen des Index jetzt entschieden hat, beim Scannen des Index Scan Index den Merge join zu verwenden.

Beachten Sie den Unterschied zwischen Index-Scannen und vollständigem Tabellen-Scannen oder sequentiellem Scannen: Das erste, auch als "Tabellen-Scannen" bezeichnet, durchsucht die Daten oder Seiten des Index nach den entsprechenden Datensätzen, während das zweite jede Zeile der Tabelle durchsucht.

Sie sehen, dass die Gesamtlaufzeit abgenommen hat und die Leistung besser sein sollte, aber es gibt zwei Index-Scans, wodurch der Speicher hier wichtiger wird, insbesondere wenn die Tabelle nicht in sie passt. In solchen Fällen müssen Sie zuerst einen vollständigen Index-Scan durchführen, der mit schnellen sequentiellen Lesevorgängen durchgeführt wird und kein Problem darstellt. Anschließend müssen Sie jedoch viele zufällige Lesevorgänge ausführen, um Zeilen nach Indexwert auszuwählen. Dies sind zufällige Leseoperationen, die normalerweise mehrere Größenordnungen langsamer sind als sequentielle. In diesen Fällen erfolgt ein vollständiger Tabellenscan tatsächlich schneller als ein vollständiger Indexscan.

Tipp: Wenn Sie mehr über EXPLAIN erfahren oder Beispiele genauer betrachten möchten, lesen Sie Guillaume Lelarge 's Understanding Explain .

Zeitkomplexität und Big O.

Nachdem Sie den Abfrageplan kurz durchgesehen haben, können Sie sich eingehender mit der Theorie der Rechenkomplexität befassen und formeller über die Leistung nachdenken. Dies ist ein Bereich der theoretischen Informatik, der sich unter anderem auf die Klassifizierung von Rechenproblemen in Abhängigkeit von ihrer Komplexität konzentriert; Diese Rechenprobleme können Algorithmen sein, aber auch Abfragen.

Bei Abfragen werden sie jedoch nicht unbedingt nach ihrer Komplexität klassifiziert, sondern in Abhängigkeit von der Zeit, die erforderlich ist, um sie abzuschließen und Ergebnisse zu erzielen. Dies wird als Zeitkomplexität bezeichnet, und Sie können die große O-Notation verwenden, um diese Art von Komplexität zu formulieren oder zu messen.

Mit der Bezeichnung big O drücken Sie die Laufzeit in Bezug darauf aus, wie schnell sie relativ zur Eingabe wächst, wenn die Eingabe beliebig groß wird. Die große O-Notation schließt Koeffizienten und Elemente niedrigerer Ordnung aus, sodass Sie sich auf den wichtigen Teil der Ausführungszeit Ihrer Abfrage konzentrieren können: die Wachstumsrate. Wenn sie auf diese Weise ausgedrückt werden und die Koeffizienten und Terme niedrigerer Ordnung verwerfen, sagen sie, dass die Zeitkomplexität asymptotisch beschrieben wird. Dies bedeutet, dass die Eingabegröße unendlich ist.

In einer Datenbanksprache bestimmt die Komplexität, wie lange es dauert, eine Abfrage abzuschließen, wenn die Größe der Datentabellen und damit die Datenbank wächst.

Bitte beachten Sie, dass die Größe Ihrer Datenbank nicht nur durch die Zunahme der Datenmenge in den Tabellen zunimmt, sondern auch durch die Tatsache, dass Indizes vorhanden sind.

Schätzen der zeitlichen Komplexität Ihres Abfrageplans

Wie Sie bereits gesehen haben, bestimmt der Ausführungsplan unter anderem, welcher Algorithmus für jede Operation verwendet wird. Auf diese Weise können Sie jede Ausführungszeit für Abfragen als Funktion der Größe der im Abfrageplan enthaltenen Tabelle, die als Komplexitätsfunktion bezeichnet wird, logisch ausdrücken. Mit anderen Worten, Sie können die große O-Notation und den Ausführungsplan verwenden, um die Komplexität und Leistung der Abfrage zu bewerten.

In den folgenden Abschnitten erhalten Sie einen Überblick über die vier Arten der Zeitkomplexität und einige Beispiele dafür, wie die Zeitkomplexität von Abfragen je nach Kontext, in dem sie ausgeführt werden, variieren kann.

Hinweis: Indizes sind Teil dieser Geschichte!

Es ist jedoch zu beachten, dass es unterschiedliche Arten von Indizes, unterschiedliche Ausführungspläne und unterschiedliche Implementierungen für unterschiedliche Datenbanken gibt. Daher sind die unten aufgeführten vorübergehenden Schwierigkeiten sehr allgemein und können je nach bestimmten Einstellungen variieren.

O (1): Konstante Zeit

Sie sagen, dass ein Algorithmus in konstanter Zeit arbeitet, wenn er unabhängig von der Größe der Eingabedaten dieselbe Zeit benötigt. Wenn es um eine Abfrage geht, wird sie in konstanter Zeit ausgeführt, wenn unabhängig von der Größe der Tabelle dieselbe Zeit benötigt wird.

Diese Art der Abfrage ist nicht wirklich üblich, aber hier ist ein solches Beispiel:

 SELECT TOP 1 t.* FROM t 

Die zeitliche Komplexität ist konstant, da eine beliebige Zeile aus der Tabelle ausgewählt wird. Daher sollte die Zeitdauer nicht von der Größe der Tabelle abhängen.

Lineare Zeit: O (n)

Sie sagen, dass der Algorithmus in linearer Zeit arbeitet, wenn seine Ausführungszeit direkt proportional zur Größe der Eingabedaten ist, dh die Zeit linear mit der Größe der Eingabedaten zunimmt. Für Datenbanken bedeutet dies, dass die Ausführungszeit direkt proportional zur Größe der Tabelle ist: Wenn die Anzahl der Zeilen in der Tabelle zunimmt, erhöht sich die Ausführungszeit der Abfrage.

Ein Beispiel ist eine Abfrage mit einer WHERE für eine nicht indizierte Spalte: Ein vollständiger Tabellenscan oder Seq Scan ist erforderlich, was zu einer Komplexität der O (n) -Zeit führt. Dies bedeutet, dass jede Zeile gelesen werden muss, um die Zeile mit der gewünschten Kennung (ID) zu finden. Sie haben überhaupt keine Einschränkungen, daher müssen Sie jede Zeile zählen, auch wenn die erste Zeile der Bedingung entspricht.

Betrachten Sie auch das folgende Abfragebeispiel, das eine O (n) i_id wenn für das Feld i_id kein Index i_id ist:

 SELECT i_id FROM item; 

• Das Vorstehende bedeutet auch, dass andere Abfragen, wie z. B. Abfragen zur Berechnung der Anzahl der Zeilen, COUNT (*) FROM TABLE; hat eine zeitliche Komplexität von O (n) , da ein vollständiger Tabellenscan erforderlich ist, da die Gesamtzahl der Zeilen nicht für die Tabelle gespeichert wurde. Andernfalls wäre die zeitliche Komplexität ähnlich wie bei O (1) .
  

Die lineare Laufzeit hängt eng mit der Laufzeit von Plänen mit Tabellenverknüpfungen zusammen. Hier einige Beispiele:

• Der Hash-Join hat die erwartete Komplexität von O (M + N). Der klassische Hash-Join-Algorithmus zum internen Verbinden von zwei Tabellen bereitet zuerst die Hash-Tabelle der kleineren Tabelle vor. Hash-Tabelleneinträge bestehen aus einem Verbindungsattribut und seiner Zeichenfolge. Auf die Hash-Tabelle wird zugegriffen, indem die Hash-Funktion auf das Verbindungsattribut angewendet wird. Sobald die Hash-Tabelle erstellt ist, wird eine große Tabelle gescannt und die entsprechenden Zeilen aus der kleineren Tabelle werden durch Durchsuchen der Hash-Tabelle gefunden.
• Zusammenführungsverknüpfungen haben normalerweise eine O (M + N) -Komplexität, hängen jedoch stark von den Verknüpfungsspaltenindizes ab und, falls kein Index vorhanden ist, davon, ob die Zeilen nach den im Verknüpfungen verwendeten Schlüsseln sortiert sind:
  • Wenn beide Tabellen nach den im Join verwendeten Schlüsseln sortiert sind, hat die Abfrage eine zeitliche Komplexität von O (M + N).
  • Wenn beide Tabellen einen Index für verknüpfte Spalten haben, unterstützt der Index diese Spalten bereits in der angegebenen Reihenfolge, und eine Sortierung ist nicht erforderlich. Die Schwierigkeit wird O (M + N) sein.
  • Wenn keine der Tabellen einen Index für verbundene Spalten hat, müssen Sie zuerst beide Tabellen sortieren, damit die Komplexität wie O aussieht (M log M + N log N).
  • Wenn nur eine der Tabellen einen Index für die verbundenen Spalten hat, muss nur die Tabelle ohne Index sortiert werden, bevor der Verknüpfungsschritt ausgeführt wird, damit die Komplexität wie O aussieht (M + N log N).
• Bei verschachtelten Verknüpfungen beträgt die Komplexität normalerweise O (MN). Dieser Join ist effektiv, wenn eine oder beide Tabellen extrem klein sind (z. B. weniger als 10 Datensätze). Dies ist eine sehr häufige Situation bei der Auswertung von Abfragen, da einige Unterabfragen so geschrieben werden, dass sie nur eine Zeile zurückgeben.

Denken Sie daran: Ein verschachtelter Join ist ein Join, der jeden Datensatz in einer Tabelle mit jedem Datensatz in einer anderen vergleicht.

Logarithmische Zeit: O (log (n))

Es wird gesagt, dass ein Algorithmus in logarithmischer Zeit arbeitet, wenn seine Ausführungszeit proportional zum Logarithmus der Eingabegröße ist; Für Abfragen bedeutet dies, dass sie ausgeführt werden, wenn die Ausführungszeit proportional zum Logarithmus der Datenbankgröße ist.

Diese logarithmische Zeitkomplexität gilt für Abfragepläne, bei denen ein Index Scan oder ein Clustered-Index gescannt wird. Ein Clustered-Index ist ein Index, bei dem die endgültige Indexebene die tatsächlichen Zeilen der Tabelle enthält. Ein Clustered-Index ähnelt jedem anderen Index: Er wird in einer oder mehreren Spalten definiert. Sie bilden einen Indexschlüssel. Der Clustering-Schlüssel sind die Schlüsselspalten eines Clustered-Index. Das Scannen eines Clustered-Index ist im Grunde das Lesen Ihres DBMS für eine Zeile oder Zeilen von oben nach unten in einem Clustered-Index.

Betrachten Sie das folgende i_id , in dem ein Index für i_id und der normalerweise zu einer Komplexität von O (log (n)) führt:

 SELECT i_stock FROM item WHERE i_id = N; 

Beachten Sie, dass ohne Index die zeitliche Komplexität O (n) wäre.

Quadratische Zeit: O (n ^ 2)

Es wird angenommen, dass der Algorithmus in quadratischer Zeit ausgeführt wird, wenn seine Ausführungszeit proportional zum Quadrat der Eingabegröße ist. Für Datenbanken bedeutet dies wiederum, dass die Ausführungszeit der Abfrage proportional zum Quadrat der Datenbankgröße ist.

Ein mögliches Beispiel für eine quadratische Zeitkomplexitätsabfrage ist das Folgende:

 SELECT * FROM item, author WHERE item.i_a_id=author.a_id 

Die minimale Komplexität kann O (n log (n)) sein, aber die maximale Komplexität kann O (n ^ 2) sein, basierend auf den Indexinformationen der Verbindungsattribute.

Zusammenfassend können Sie sich auch das folgende Spickzettel ansehen, um die Abfrageleistung anhand ihrer zeitlichen Komplexität und ihrer Effektivität zu bewerten:

SQL-Optimierung

Aufgrund des Abfrageausführungsplans und der zeitlichen Komplexität können Sie Ihre SQL-Abfrage weiter anpassen. Sie können beginnen, indem Sie sich auf die folgenden Punkte konzentrieren:

• Ersetzen Sie unnötige vollständige Tabellenscans durch Indexscans.
• Stellen Sie sicher, dass die optimale Verknüpfungsreihenfolge angewendet wird.
• Stellen Sie sicher, dass die Indizes optimal genutzt werden. Und
• Das Zwischenspeichern von Volltext-Scans kleiner Tabellen (Cache von Volltabellen-Scans kleiner Tabellen) wird verwendet.

Weitere Verwendung von SQL

Glückwunsch! Sie sind am Ende dieses Artikels angelangt, in dem Sie nur einen kleinen Einblick in die Leistung von SQL-Abfragen erhalten haben. Ich hoffe, Sie haben weitere Informationen zu Antipatterns, dem Abfrageoptimierer und den Tools, mit denen Sie die Komplexität Ihres Abfrageplans analysieren, bewerten und interpretieren können. Sie haben jedoch noch so viel zu entdecken! Wenn Sie mehr wissen möchten, lesen Sie das Buch „Database Management Systems“ von R. Ramakrishnan und J. Gehrke.

Schließlich möchte ich Ihnen in diesem Zitat StackOverflow nicht verweigern:

Mein Lieblings-Antimuster überprüft Ihre Anfragen nicht.

Es gilt jedoch, wenn:

• Ihre Abfrage enthält mehr als eine Tabelle.
• Sie denken, dass Sie das optimale Design für die Anforderung haben, versuchen jedoch nicht, Ihre Annahmen zu überprüfen.
• Sie akzeptieren die erste Arbeitsanforderung, ohne zu wissen, wie nahe sie am Optimum liegt.