Detaillierte Erläuterung der Ausführung von SQL-Anweisungen (Übersicht über die MySQL-Architektur -> Abfrageausführungsprozess -> SQL-Analysereihenfolge)

Vorwort:

Ich wollte schon immer wissen, wie eine SQL-Anweisung ausgeführt wird und in welcher Reihenfolge sie ausgeführt wird. Dann habe ich die Informationen von verschiedenen Seiten geprüft und zusammengefasst und bin zu folgendem Blogbeitrag gekommen.

In diesem Artikel werden die Erkenntnisse aus der Perspektive der MySQL-Gesamtarchitektur untersucht ---> Abfrageausführungsprozess ---> Reihenfolge der Anweisungsausführung.

1. Übersicht über die MySQL-Architektur:

Die Architektur lässt sich am besten in Bildern mit den erforderlichen erklärenden Texten darstellen.

Das folgende Bild basiert auf einem Bild aus einem Nachschlagewerk und wurde um mein eigenes Verständnis ergänzt.

Aus der obigen Abbildung können wir erkennen, dass die gesamte Architektur in zwei Schichten unterteilt ist. Die obere Schicht besteht aus MySQLD, die als „SQL-Schicht“ bezeichnet wird, und die untere Schicht besteht aus verschiedenen Speicher-Engines, die Schnittstellen zur oberen Schicht bereitstellen, die als „Speicher-Engine-Schicht“ bezeichnet wird. Die Funktionen anderer Module und Komponenten lassen sich leicht anhand ihrer Namen erschließen, daher werde ich hier nicht näher darauf eingehen.

2. Abfrageausführungsprozess

Lassen Sie mich etwas weiter gehen und den Abfrageausführungsprozess basierend auf meinem eigenen Verständnis erklären:

1. Verbinden

1.1 Der Client initiiert eine Abfrageanforderung und wartet auf das „Verbindungsverwaltungsmodul“ des Clients, um die Anforderung zu empfangen

1.2 Leiten Sie die Anfrage an „Connection/Thread Module“ weiter.

1.3 Rufen Sie das „Benutzermodul“ auf, um eine Berechtigungsprüfung durchzuführen

1.4 Nach bestandener Prüfung holt das „Verbindungs-/Threadmodul“ den im Leerlauf befindlichen zwischengespeicherten Verbindungsthread aus dem „Threadverbindungspool“ und verbindet ihn mit der Clientanforderung. Wenn dies fehlschlägt, wird eine neue Verbindungsanforderung erstellt.

2. Verarbeitung

2.1 Fragen Sie zuerst den Cache ab, um zu prüfen, ob die Abfrageanweisung vollständig übereinstimmt, und prüfen Sie dann, ob Sie über die Berechtigung verfügen. Wenn beide erfolgreich sind, rufen Sie die Daten direkt ab und geben Sie sie zurück

2.2 Wenn der vorherige Schritt fehlschlägt, wird er an den „Befehlsparser“ übertragen, der nach lexikalischer Analyse und Syntaxanalyse einen Parsebaum generiert

2.3 Als nächstes folgt die Vorverarbeitungsphase, die die Semantik behandelt, die der Parser nicht auflösen kann, Berechtigungen überprüft usw. und einen neuen Analysebaum generiert

2.4 Übertragen Sie es zur Verarbeitung an das entsprechende Modul

2.5 Wenn es sich um eine SELECT-Abfrage handelt, führt der Abfrageoptimierer zahlreiche Optimierungen durch und generiert einen Ausführungsplan.

2.6 Nach dem Empfang der Anfrage prüft das Modul, ob der verbundene Benutzer die Berechtigung hat, über das „Zugriffskontrollmodul“ auf die Zieltabelle und das Zielfeld zuzugreifen.

2.7 Wenn ja, rufen Sie das „Tabellenverwaltungsmodul“ auf, um zu prüfen, ob der Tabellencache vorhanden ist. Wenn ja, direkt die entsprechende Tabelle aufrufen und eine Sperre erwerben, andernfalls die Tabellendatei erneut öffnen

2.8 Ermitteln Sie anhand der Metadaten der Tabelle den Speicher-Engine-Typ und andere Informationen der Tabelle und rufen Sie die entsprechende Speicher-Engine-Verarbeitung über die Schnittstelle auf

2.9 Wenn während des obigen Vorgangs Datenänderungen auftreten und die Protokollierungsfunktion aktiviert ist, werden diese in der entsprechenden Binärprotokolldatei aufgezeichnet

3. Ergebnisse

3.1Nachdem die Abfrageanforderung abgeschlossen ist, wird der Ergebnissatz an das „Verbindungs-/Threadmodul“ zurückgegeben.

3.2 Der zurückgegebene Wert kann auch ein entsprechender Statusindikator sein, beispielsweise Erfolg oder Fehler.

3.3 „Connect to/Thread Module“ um anschließende Bereinigungsarbeiten durchzuführen und weiter auf Anfragen zu warten oder die Verbindung zum Client zu trennen

Eine kurze Zusammenfassung in einem Bild

3. SQL-Analysereihenfolge

Gehen wir als Nächstes einen Schritt weiter und betrachten die Vergangenheit und Gegenwart einer SQL-Anweisung.

Schauen wir uns zunächst den Beispielsatz an

AUSWÄHLEN
 <Auswahlliste>
AUS
 <linke_Tabelle> <join_typ>
JOIN <rechte_Tabelle > ON <Joinbedingung >
WO
 <wo_bedingung>
GRUPPELN NACH
 < Gruppieren nach Liste >
HABEN
 <Bedingung habend >
BESTELLEN BIS
 < Bestell_nach_Bedingung >
LIMIT <Grenzwert>

Die Ausführungsreihenfolge ist jedoch wie folgt

VON <linke_Tabelle>
ON <Beitrittsbedingung>
<join_type> JOIN <rechte_Tabelle>
WHERE <Wo_Bedingung>
GROUP BY <Gruppenliste>
HAVING <Haben_Bedingung>
WÄHLEN 
DISTINCT <Auswahlliste>
ORDER BY <Bestellbedingung>
LIMIT <Grenzwert_Nummer>

Obwohl ich nicht damit gerechnet habe, dass es so ist, wirkt es auf den ersten Blick dennoch sehr natürlich und harmonisch. Woher bekommt man das? Filtert ständig die Bedingungen, wählt gleiche oder unterschiedliche aus und sortiert sie. Dann wisst ihr, was ihr als Erstes nehmen müsst.

Lassen Sie uns daher die Einzelheiten Schritt für Schritt durchgehen.

Vorbereitung

1. Erstellen Sie eine Testdatenbank

Datenbanktestabfrage erstellen

2. Erstellen Sie eine Testtabelle

Tabelle erstellen Tabelle1
(
 uid VARCHAR(10) NICHT NULL,
 Name VARCHAR(10) NOT NULL,
 PRIMÄRSCHLÜSSEL (UID)
)ENGINE=INNODB STANDARD-ZEICHENSATZ=UTF8;

Tabelle erstellen Tabelle2
(
 oid INT NICHT NULL auto_increment,
 uid VARCHAR(10),
 PRIMÄRSCHLÜSSEL(oid)
)ENGINE=INNODB STANDARD-ZEICHENSATZ=UTF8;

3. Daten einfügen

INSERT INTO Tabelle1 (UID, Name) VALUES (,aaa‘,,mike‘), (,bbb‘,,jack‘), (,ccc‘,,mike‘), (,ddd‘,,mike‘);

INSERT INTO Tabelle2(UID) VALUES('aaa'),('aaa'),('bbb'),('bbb'),('bbb'),('ccc'),(NULL);

4. Endgültiges gewünschtes Ergebnis

WÄHLEN
 eine Flüssigkeit,
 Anzahl(b.oid) AS gesamt
AUS
 Tabelle1 AS a
LEFT JOIN Tabelle2 AS b ON a.uid = b.uid
WO
 ein. NAME = 'Mike'
GRUPPELN NACH
 eine.uid
HABEN
 Anzahl(b.oid) < 2
BESTELLEN BIS
 Gesamtsumme DESC
GRENZE 1;

! Beginnen wir jetzt unsere SQL-Parsing-Reise!

1. VON

Wenn mehrere Tabellen beteiligt sind, wird die Ausgabe der linken Tabelle als Eingabe der rechten Tabelle verwendet und dann eine virtuelle Tabelle VT1 generiert.

(1-J1) Kartesisches Produkt

Berechnen Sie das kartesische Produkt (CROSS JOIN) zweier verknüpfter Tabellen und generieren Sie die virtuelle Tabelle VT1-J1.

mysql> wähle * aus Tabelle1, Tabelle2;
+-----+------+-----+------+
| Benutzer-ID | Name | OID | Benutzer-ID |
+-----+------+-----+------+
| aaa | Mike | 1 | aaa |
| bbb | Buchse | 1 | aaa |
| ccc | mike | 1 | aaa |
| ddd | Mike | 1 | aaa |
| aaa | Mike | 2 | aaa |
| bbb | Buchse | 2 | aaa |
| ccc | mike | 2 | aaa |
| ddd | Mike | 2 | aaa |
| aaa | Mike | 3 | bbb |
| bbb | Buchse | 3 | bbb |
| ccc | mike | 3 | bbb |
| ddd | Mike | 3 | bbb |
| aaa | Mike | 4 | bbb |
| bbb | Buchse | 4 | bbb |
| ccc | mike | 4 | bbb |
| ddd | Mike | 4 | bbb |
| aaa | Mike | 5 | bbb |
| bbb | Buchse | 5 | bbb |
| ccc | mike | 5 | bbb |
| ddd | Mike | 5 | bbb |
| aaa | Mike | 6 | ccc |
| bbb | Buchse | 6 | ccc |
| ccc | Mike | 6 | ccc |
| ddd | Mike | 6 | ccc |
| aaa | Mike | 7 | NULL |
| bbb | jack | 7 | NULL |
| ccc | mike | 7 | NULL |
| ddd | mike | 7 | NULL |
+-----+------+-----+------+
Zeilen im Set (0,00 Sek.)

(1-J2)ON Filter

Basierend auf der virtuellen Tabelle VT1-J1 werden alle Spalten herausgefiltert, die die ON-Prädikatbedingung erfüllen, um die virtuelle Tabelle VT1-J2 zu generieren.

Hinweis: Aufgrund grammatikalischer Einschränkungen wird hier stattdessen „WHERE“ verwendet, wodurch die Leser auch die subtile Beziehung zwischen den beiden erkennen können.

mysql> AUSWÄHLEN
 -> *
 -> VON
 -> Tabelle1,
 -> Tabelle2
 -> WO
 -> Tabelle1.uid = Tabelle2.uid
 -> ;
+-----+------+-----+------+
| Benutzer-ID | Name | OID | Benutzer-ID |
+-----+------+-----+------+
| aaa | Mike | 1 | aaa |
| aaa | Mike | 2 | aaa |
| bbb | Buchse | 3 | bbb |
| bbb | Buchse | 4 | bbb |
| bbb | Buchse | 5 | bbb |
| ccc | Mike | 6 | ccc |
+-----+------+-----+------+
Zeilen im Set (0,00 Sek.)

(1-J3) Externe Spalten hinzufügen

Wenn ein äußerer Join (LEFT, RIGHT, FULL) verwendet wird, werden die Spalten in der Haupttabelle (beibehaltene Tabelle), die die ON-Bedingung nicht erfüllen, auch als externe Zeilen zu VT1-J2 hinzugefügt, um eine virtuelle Tabelle VT1-J3 zu generieren.

mysql> AUSWÄHLEN
 -> *
 -> VON
 -> Tabelle1 AS a
 -> LINKER ÄUSSERER JOIN Tabelle2 AS b ON a.uid = b.uid;
+-----+------+------+------+
| Benutzer-ID | Name | OID | Benutzer-ID |
+-----+------+------+------+
| aaa | Mike | 1 | aaa |
| aaa | Mike | 2 | aaa |
| bbb | Buchse | 3 | bbb |
| bbb | Buchse | 4 | bbb |
| bbb | Buchse | 5 | bbb |
| ccc | Mike | 6 | ccc |
| ddd | mike | NULL | NULL |
+-----+------+------+------+
Zeilen im Set (0,00 Sek.)

Unten finden Sie ein sehr anschauliches Erklärungsdiagramm zu „SQL JOINS“, das im Internet zu finden ist. Wenn es Ihre Rechte verletzt, informieren Sie uns bitte und löschen Sie es. Vielen Dank.

2. WO

Die im VT1-Prozess generierte temporäre Tabelle wird gefiltert und die Spalten, die die WHERE-Klausel erfüllen, werden in die VT2-Tabelle eingefügt.

Beachten:

Aufgrund der Gruppierung können derzeit weder Aggregatvorgänge verwendet werden, noch kann der in SELECT erstellte Alias ​​verwendet werden.

Unterschied zu ON:

Wenn externe Spalten vorhanden sind, filtert ON die zugehörige Tabelle und die Haupttabelle (beibehaltene Tabelle) gibt alle Spalten zurück.

Wenn keine externen Spalten hinzugefügt werden, sind die Effekte dieselben;

Anwendung:

Die Filterung der Haupttabelle sollte in WHERE erfolgen;

Verwenden Sie für verknüpfte Tabellen ON, wenn Sie zuerst die Bedingungen abfragen und dann eine Verbindung herstellen, und verwenden Sie WHERE, wenn Sie zuerst eine Verbindung herstellen und dann die Bedingungen abfragen.

mysql> AUSWÄHLEN
 -> *
 -> VON
 -> Tabelle1 AS a
 -> LINKER OUTER JOIN Tabelle2 AS b ON a.uid = b.uid
 -> WO
 -> a.NAME = "Mike";
+-----+------+------+------+
| Benutzer-ID | Name | OID | Benutzer-ID |
+-----+------+------+------+
| aaa | Mike | 1 | aaa |
| aaa | Mike | 2 | aaa |
| ccc | Mike | 6 | ccc |
| ddd | mike | NULL | NULL |
+-----+------+------+------+
Zeilen im Set (0,00 Sek.)

3. GRUPPE NACH

Diese Klausel gruppiert die in VT2 generierte Tabelle entsprechend den Spalten in GROUP BY. Erstellen Sie eine VT3-Tabelle.

Beachten:

Die in den nachfolgenden Verarbeitungsanweisungen verwendeten Spalten, wie z. B. SELECT und HAVING, müssen in GROUP BY enthalten sein. Für die Spalten, die nicht vorkommen, müssen Aggregatfunktionen verwendet werden.

Grund:

GROUP BY ändert den Verweis auf die Tabelle, konvertiert ihn in einen neuen Verweismodus und die Anzahl der Spalten, die für die nächste Ebene logischer Operationen verwendet werden können, wird reduziert.

Mein Verständnis ist:

Gemäß dem Gruppierungsfeld werden Datensätze mit demselben Gruppierungsfeld zu einem Datensatz zusammengeführt, da jede Gruppe nur einen Datensatz zurückgeben kann, sofern sie nicht herausgefiltert wird, und die Felder, die nicht im Gruppierungsfeld enthalten sind, mehrere Werte haben können und mehrere Werte nicht in einen Datensatz eingefügt werden können. Daher müssen diese mehrwertigen Spalten durch Aggregatfunktionen in einzelne Werte umgewandelt werden.

mysql> AUSWÄHLEN
 -> *
 -> VON
 -> Tabelle1 AS a
 -> LINKER OUTER JOIN Tabelle2 AS b ON a.uid = b.uid
 -> WO
 -> a.NAME = "Mike"
 -> GRUPPE NACH
 -> eine.uid;
+-----+------+------+------+
| Benutzer-ID | Name | OID | Benutzer-ID |
+-----+------+------+------+
| aaa | Mike | 1 | aaa |
| ccc | Mike | 6 | ccc |
| ddd | mike | NULL | NULL |
+-----+------+------+------+
Zeilen im Set (0,00 Sek.)

4. HABEN

Diese Klausel filtert die verschiedenen Gruppen in der VT3-Tabelle und wirkt nur auf die gruppierten Daten. Die Klauseln, die die HAVING-Bedingung erfüllen, werden der VT4-Tabelle hinzugefügt.

mysql> AUSWÄHLEN
 -> *
 -> VON
 -> Tabelle1 AS a
 -> LINKER OUTER JOIN Tabelle2 AS b ON a.uid = b.uid
 -> WO
 -> a.NAME = "Mike"
 -> GRUPPE NACH
 -> eine.uid
 -> HABEN
 -> Anzahl(b.oid) < 2;
+-----+------+------+------+
| Benutzer-ID | Name | OID | Benutzer-ID |
+-----+------+------+------+
| ccc | Mike | 6 | ccc |
| ddd | mike | NULL | NULL |
+-----+------+------+------+
Zeilen im Set (0,00 Sek.)

5. AUSWÄHLEN

Diese Klausel verarbeitet die Elemente in der SELECT-Klausel und generiert eine VT5-Tabelle.

(5-J1) Berechnen Sie den Ausdruck in der SELECT-Klausel und generieren Sie VT5-J1

(5-J2)UNTERSCHIEDLICH

Suchen Sie die doppelten Spalten in VT5-1 und löschen Sie sie, um VT5-J2 zu generieren

Wenn in der Abfrage die DISTINCT-Klausel angegeben ist, wird eine temporäre In-Memory-Tabelle erstellt (wenn sie nicht in den Speicher passt, muss sie auf der Festplatte gespeichert werden). Die Tabellenstruktur dieser temporären Tabelle ist identisch mit der im vorherigen Schritt generierten virtuellen Tabelle VT5. Der Unterschied besteht darin, dass der Spalte für die DISTINCT-Operation ein eindeutiger Index hinzugefügt wird, um doppelte Daten zu vermeiden.

mysql> AUSWÄHLEN
 -> eine.uid,
 -> Anzahl(b.oid) AS Gesamt
 -> VON
 -> Tabelle1 AS a
 -> LINKER OUTER JOIN Tabelle2 AS b ON a.uid = b.uid
 -> WO
 -> a.NAME = "Mike"
 -> GRUPPE NACH
 -> eine.uid
 -> HABEN
 -> Anzahl(b.oid) < 2;
+-----+--------+
| Flüssigkeit | gesamt |
+-----+--------+
| 1 |
| ddd | 0 |
+-----+--------+
Zeilen im Set (0,00 Sek.)

6. BESTELLUNG BIS

Sortieren Sie die Ergebnisse aus der Tabelle in VT5-J2 gemäß den Bedingungen der ORDER BY-Klausel, um die VT6-Tabelle zu generieren.

Beachten:

Die einzige Stelle, an der ein Alias ​​verwendet werden kann, ist in SELECT.

mysql> AUSWÄHLEN
 -> eine.uid,
 -> Anzahl(b.oid) AS Gesamt
 -> VON
 -> Tabelle1 AS a
 -> LINKER OUTER JOIN Tabelle2 AS b ON a.uid = b.uid
 -> WO
 -> a.NAME = "Mike"
 -> GRUPPE NACH
 -> eine.uid
 -> HABEN
 -> Anzahl(b.oid) < 2
 -> BESTELLEN NACH
 -> Gesamtsumme DESC;
+-----+--------+
| Flüssigkeit | gesamt |
+-----+--------+
| 1 |
| ddd | 0 |
+-----+--------+
Zeilen im Set (0,00 Sek.)

7.GRENZE

Die LIMIT-Klausel wählt die angegebenen Zeilendaten beginnend an der angegebenen Position aus der im vorherigen Schritt erhaltenen virtuellen VT6-Tabelle aus.

Beachten:

Der Einfluss der positiven und negativen Werte von Offset und Zeilen;

Wenn der Offset groß ist, ist die Effizienz sehr gering. Sie können Folgendes tun:

Verwenden Sie zur Optimierung eine Unterabfrage. Holen Sie sich in der Unterabfrage zuerst die maximale ID aus dem Index, sortieren Sie dann in umgekehrter Reihenfolge und holen Sie sich dann N Zeilen mit Ergebnismengen.

Bei Verwendung der INNER JOIN-Optimierung räumt die JOIN-Klausel dem Abrufen der ID-Liste aus dem Index ebenfalls Priorität ein und verknüpft die Abfrage dann direkt, um das Endergebnis zu erhalten.

mysql> AUSWÄHLEN
 -> eine.uid,
 -> Anzahl(b.oid) AS Gesamt
 -> VON
 -> Tabelle1 AS a
 -> LEFT JOIN Tabelle2 AS b ON a.uid = b.uid
 -> WO
 -> a.NAME = "Mike"
 -> GRUPPE NACH
 -> eine.uid
 -> HABEN
 -> Anzahl(b.oid) < 2
 -> BESTELLEN NACH
 -> insgesamt DESC
 -> GRENZE 1;
+-----+--------+
| Flüssigkeit | gesamt |
+-----+--------+
| 1 |
+-----+--------+
Zeile im Satz (0,00 Sek.)

Dies ist das Ende der SQL-Analyse. Die obige Abbildung fasst es zusammen:

Nachschlagewerke:

• MySQL-Leistungsoptimierung und Architekturpraxis
• „MySQL-Technologie-Insider: SQL-Programmierung“

Ende:

Nun, diese Reise des tiefen Verständnisses ist hier fast vorbei. Obwohl sie nicht sehr tiefgründig ist, sind es nur einige Dinge, die zusammengestückelt wurden, und ich habe auf einige Bücher verwiesen, die ich zuvor gelesen habe. Die Schrift des Meisters ist in der Tat anders. Und ich habe dabei auch viele Dinge bekommen. Das Wichtigste ist, die Erhabenheit der Computersoftwarewelt weiter zu erkennen ~

Darüber hinaus kommt es aufgrund meiner begrenzten Kenntnisse zwangsläufig zu Auslassungen und Fehlern. Wenn Sie welche finden, lassen Sie es mich bitte wissen und korrigieren Sie sie. Vielen Dank~

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.