Der ultimative Leitfaden – Wie schreibe ich bessere SQL-Abfragen?

Abfragen basierend auf Sammlungs- und Programmmethoden

Im umgekehrten Modell ist die Tatsache impliziert, dass es einen Unterschied zwischen sammlungsbasierten und prozeduralen Ansätzen zur Abfrageerstellung gibt.

• Der prozedurale Ansatz beim Abfragen ist dem Programmieren sehr ähnlich: Sie teilen dem System mit, was zu tun ist und wie es zu tun ist. Fragen Sie beispielsweise wie im Beispiel im vorherigen Artikel die Datenbank ab, indem Sie eine Funktion ausführen und dann eine andere Funktion aufrufen, oder verwenden Sie einen logischen Ansatz mit Schleifen, Bedingungen und benutzerdefinierten Funktionen (UDF), um das endgültige Abfrageergebnis zu erhalten. Sie werden feststellen, dass Sie auf diese Weise immer eine Teilmenge der Daten in jeder Ebene anfordern. Dieser Ansatz wird oft auch als schrittweise oder zeilenweise Abfrage bezeichnet.
• Der andere ist ein sammlungsbasierter Ansatz, bei dem Sie nur die Vorgänge angeben müssen, die ausgeführt werden müssen. Bei dieser Methode müssen Sie lediglich die Bedingungen und Anforderungen für die Ergebnisse angeben, die Sie durch die Abfrage erhalten möchten. Beim Abrufen von Daten müssen Sie nicht auf die internen Mechanismen achten, die die Abfrage implementieren: Die Datenbank-Engine ermittelt den besten Algorithmus und die beste Logik zum Ausführen der Abfrage.

Da SQL satzbasiert ist, ist dieser Ansatz effizienter als der prozedurale Ansatz, was erklärt, warum SQL in manchen Fällen schneller arbeiten kann als Code.

Die satzbasierte Abfragemethode ist ebenfalls eine Fähigkeit, die Sie in der Data-Mining-Analysebranche beherrschen müssen! Denn Sie müssen in der Lage sein, zwischen diesen beiden Methoden zu wechseln. Wenn Sie feststellen, dass Ihre Abfragen prozedurale Fragen enthalten, sollten Sie überlegen, ob dieser Teil neu geschrieben werden muss.

Von der Anfrage bis zum Ausführungsplan

Der Rückwärtsmodus ist nicht statisch. Auf dem Weg zum SQL-Entwickler kann es eine entmutigende Aufgabe sein, Abfrage-Reverse-Modelle zu vermeiden und Abfragen neu zu schreiben. Daher müssen Sie häufig Tools verwenden, um Ihre Abfragen strukturierter zu optimieren.

Das Nachdenken über Leistung erfordert nicht nur einen strukturierteren Ansatz, sondern auch einen tieferen Ansatz.

Diese strukturierte und tiefgehende Vorgehensweise basiert jedoch in erster Linie auf Abfrageplänen. Der Abfrageplan wird zunächst in einen „Analysebaum“ geparst und definiert genau, welcher Algorithmus für jede Operation verwendet wird und wie die Operationen koordiniert werden.

Abfrageoptimierung

Beim Optimieren einer Abfrage müssen Sie höchstwahrscheinlich den vom Optimierer generierten Plan manuell überprüfen. In diesem Fall müssen Sie Ihre Abfrage erneut analysieren, indem Sie sich den Abfrageplan ansehen.

Um einen solchen Abfrageplan zu beherrschen, müssen Sie einige vom Datenbankverwaltungssystem bereitgestellte Tools verwenden. Hier sind einige Tools, die Sie verwenden können:

• Einige Softwarepakete verfügen über Tools, mit denen grafische Darstellungen von Abfrageplänen erstellt werden können.
• Andere Tools können Ihnen Textbeschreibungen von Abfrageplänen liefern.

Beachten Sie, dass Sie bei Verwendung von PostgreSQL zwischen verschiedenen EXPLAINs unterscheiden können. Sie erhalten lediglich eine Beschreibung, wie der Planer die Abfrage ausführt, ohne den Plan auszuführen. Gleichzeitig führt EXPLAIN ANALYZE die Abfrage aus und sendet Ihnen einen Analysebericht zurück, der den Abfrageplan und den tatsächlichen Abfrageplan bewertet. Im Allgemeinen führt der tatsächliche Ausführungsplan den Plan tatsächlich aus, während der ausgewertete Ausführungsplan dieses Problem lösen kann, ohne die Abfrage auszuführen. Logischerweise ist der tatsächliche Ausführungsplan nützlicher, da er zusätzliche Details und Statistiken darüber enthält, was tatsächlich passiert ist, als die Abfrage ausgeführt wurde.

Als nächstes erfahren Sie mehr über XPLAIN und ANALYZE und wie Sie diese beiden Befehle verwenden, um Ihre Abfragepläne und die Abfrageleistung besser zu verstehen. Dazu müssen Sie zunächst einige Beispiele mit zwei Tabellen erstellen: one_million und half_million.

Sie können die aktuellen Informationen der Tabelle „one_million“ mit Hilfe von EXPLAIN abrufen: Stellen Sie sicher, dass Sie sie beim Ausführen der Abfrage an erster Stelle platzieren. Nach Abschluss der Ausführung werden sie an den Abfrageplan zurückgegeben:

EXPLAIN
SELECT *
FROM one_million;
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-emphasis">___</span>_
Seq Scan on one_million
(cost=0.00..18584.82 rows=1025082 width=36)
(1 row)

Im obigen Beispiel sehen wir, dass die Kosten der Abfrage 0,00..18584,82 betragen, die Anzahl der Zeilen 1025082 beträgt und die Spaltenbreite 36 beträgt.

Gleichzeitig können Sie mit ANALYZE auch statistische Informationen aktualisieren.

ANALYZE one_million;
EXPLAIN
SELECT *
FROM one_million;
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-emphasis">___</span>_
Seq Scan on one_million
(cost=0.00..18334.00 rows=1000000 width=37)
(1 row)

Zusätzlich zu EXPLAIN und ANALYZE können Sie die tatsächliche Ausführungszeit auch mit Hilfe von EXPLAIN ANALYZE abrufen:

EXPLAIN ANALYZE
SELECT *
FROM one_million;
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span>_
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 Sie die Abfrage tatsächlich ausführen müssen, was erwähnenswert ist!

Alle Algorithmen, die wir bisher gesehen haben, sind sequentielle Scans oder vollständige Tabellenscans: Dies ist eine Methode zum Scannen einer Datenbank, bei der jede Zeile der gescannten Tabelle in sequentieller (serieller) Reihenfolge gelesen wird und jede Spalte überprüft wird Sehen Sie, ob es die Kriterien erfüllt. In Bezug auf die Leistung ist ein sequenzieller Scan nicht der beste Ausführungsplan, da die gesamte Tabelle gescannt werden muss. Wenn Sie jedoch eine langsame Festplatte verwenden, sind auch sequentielle Lesevorgänge schnell.

Es gibt einige Beispiele für andere Algorithmen:

EXPLAIN ANALYZE
SELECT *
FROM one<span class="hljs-emphasis">_million JOIN half_</span>million
ON (one<span class="hljs-emphasis">_million.counter=half_</span>million.counter);
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span>_
Hash Join (cost=15417.00..68831.00 rows=500000 width=42)
(actual time=1241.471..5912.553 rows=500000 loops=1)
Hash Cond: (one<span class="hljs-emphasis">_million.counter = half_</span>million.counter)
<span class="hljs-code">    -> Seq Scan on one_million</span>
<span class="hljs-code">    (cost=0.00..18334.00 rows=1000000 width=37)</span>
<span class="hljs-code">    (actual time=0.007..1254.027 rows=1000000 loops=1)</span>
<span class="hljs-code">    -> Hash (cost=7213.00..7213.00 rows=500000 width=5)</span>
<span class="hljs-code">    (actual time=1241.251..1241.251 rows=500000 loops=1)</span>
<span class="hljs-code">    Buckets: 4096 Batches: 16 Memory Usage: 770kB</span>
<span class="hljs-code">    -> Seq Scan on half_million</span>
<span class="hljs-code">    (cost=0.00..7213.00 rows=500000 width=5)</span>
(actual time=0.008..601.128 rows=500000 loops=1)
Total runtime: 6468.337 ms

Wir können sehen, dass der Abfrageoptimierer Hash Join ausgewählt hat. Denken Sie an diesen Vorgang, da wir ihn verwenden müssen, um die zeitliche Komplexität der Abfrage zu bewerten. Uns ist aufgefallen, dass es im obigen Beispiel keinen half_million.counter-Index gibt. Wir können den Index im folgenden Beispiel hinzufügen:

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> <span class="hljs-keyword">ON</span> half_million(counter);
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">ANALYZE</span>
<span class="hljs-keyword">SELECT</span> *
<span class="hljs-keyword">FROM</span> one_million <span class="hljs-keyword">JOIN</span> half_million
<span class="hljs-keyword">ON</span> (one_million.counter=half_million.counter);
QUERY PLAN
______________________________________________________________
<span class="hljs-keyword">Merge</span> <span class="hljs-keyword">Join</span> (<span class="hljs-keyword">cost</span>=<span class="hljs-number">4.12</span>.<span class="hljs-number">.37650</span><span class="hljs-number">.65</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> width=<span class="hljs-number">42</span>)
(actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.033</span>.<span class="hljs-number">.3272</span><span class="hljs-number">.940</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> loops=<span class="hljs-number">1</span>)
<span class="hljs-keyword">Merge</span> Cond: (one_million.counter = half_million.counter)
    -> <span class="hljs-keyword">Index</span> <span class="hljs-keyword">Scan</span> <span class="hljs-keyword">using</span> one_million_counter_idx <span class="hljs-keyword">on</span> one_million
    (<span class="hljs-keyword">cost</span>=<span class="hljs-number">0.00</span>.<span class="hljs-number">.32129</span><span class="hljs-number">.34</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">1000000</span> width=<span class="hljs-number">37</span>)
    (actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.011</span>.<span class="hljs-number">.694</span><span class="hljs-number">.466</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500001</span> loops=<span class="hljs-number">1</span>)
    -> <span class="hljs-keyword">Index</span> <span class="hljs-keyword">Scan</span> <span class="hljs-keyword">using</span> half_million_counter_idx <span class="hljs-keyword">on</span> half_million
    (<span class="hljs-keyword">cost</span>=<span class="hljs-number">0.00</span>.<span class="hljs-number">.14120</span><span class="hljs-number">.29</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> width=<span class="hljs-number">5</span>)
(actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.010</span>.<span class="hljs-number">.683</span><span class="hljs-number">.674</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> loops=<span class="hljs-number">1</span>)
Total runtime: <span class="hljs-number">3833.310</span> ms
(<span class="hljs-number">5</span> <span class="hljs-keyword">rows</span>)

Durch die Erstellung des Index hat der Abfrageoptimierer entschieden, wie der Merge-Join beim Scannen des Index gefunden wird.

Bitte beachten Sie den Unterschied zwischen einem Index-Scan und einem vollständigen Tabellen-Scan (sequentieller Scan): Letzterer (auch „Tabellen-Scan“ genannt) findet geeignete Ergebnisse, indem er alle Daten scannt oder alle Seiten indiziert, während ersterer nur jede einzelne Zeile scannt Der Tisch.

Der zweite Teil des Tutorials wird hier vorgestellt. Der letzte Artikel der Reihe „So schreiben Sie bessere SQL-Abfragen“ folgt, bleiben Sie also auf dem Laufenden.

Bitte geben Sie die Quelle des Nachdrucks an: Grape City Control

Das obige ist der detaillierte Inhalt vonDer ultimative Leitfaden – Wie schreibe ich bessere SQL-Abfragen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!