Analysieren Sie, wie eine SQL-Abfrageanweisung in MySQL ausgeführt wird

1. Übersicht über die logische MySQL-Architektur

Das wichtigste und markanteste Merkmal von MySQL ist die steckbare Speicher-Engine-Architektur, die darauf ausgelegt ist, die Abfrageverarbeitung und andere Systemaufgaben von der Speicherung/dem Abruf von Daten zu trennen. Schauen wir uns die Erklärung auf der offiziellen Website an:

Die steckbare Speicher-Engine-Architektur von MySQL ermöglicht es einem Datenbankexperten, eine spezialisierte Speicher-Engine für einen bestimmten Anwendungsbedarf auszuwählen und gleichzeitig vollständig von der Notwendigkeit abgeschirmt zu sein, irgendwelche spezifischen Anwendungscodierungsanforderungen verwalten zu müssen.

Die steckbare Speicher-Engine-Architektur von MySQL ermöglicht Entwicklern grundsätzlich, eine spezialisierte Speicher-Engine für spezielle Anwendungsanforderungen auszuwählen, ohne sich um spezielle Anwendungscodierungsanforderungen kümmern zu müssen. Das heißt, obwohl unterschiedliche Speicher-Engines über unterschiedliche Fähigkeiten verfügen, sind Anwendungen von diesen Unterschieden nicht betroffen.

Wenn aufgrund von Anwendungsänderungen die zugrunde liegende Speicher-Engine geändert werden muss oder eine oder mehrere Speicher-Engines hinzugefügt werden müssen, um neue Anforderungen zu unterstützen, sind keine größeren Codierungs- oder Prozessänderungen erforderlich, damit alles funktioniert. Die MySQL-Serverarchitektur schützt Anwendungen vor der zugrunde liegenden Komplexität von Speicher-Engines, indem sie eine konsistente und einfach zu verwendende API für alle Speicher-Engines bereitstellt.

Das logische Architekturdiagramm von MySQL sieht wie folgt aus, siehe „High Performance MySQL – 3. Ausgabe“:

Wir können die logische Architektur von MySQL grob in die Serverschicht und die Speicher-Engine-Schicht unterteilen:

1) Die meisten Kerndienstfunktionen von MySQL befinden sich in der Serverschicht, einschließlich Verbindungen, Abfrageanalyse, Analyse, Optimierung, Zwischenspeicherung und aller integrierten Funktionen (z. B. Datums-, Zeit-, Mathematik- und Verschlüsselungsfunktionen). Alle speicherübergreifenden Engine-Funktionen sind in dieser Schicht implementiert: gespeicherte Prozeduren, Trigger, Ansichten usw.

Erwähnenswert ist, dass der Connector der wichtigste Dienst des Servers ist, der über die Funktionen zur Verwaltung von MySQL-Verbindungen und zur Berechtigungsüberprüfung verfügt. Dies ist offensichtlich nicht nur auf MySQL beschränkt; die meisten netzwerkbasierten Client/Server-Tools oder -Dienste haben eine ähnliche Architektur.

2) Die zweite Schicht ist die Speicher-Engine (unterstützt mehrere Speicher-Engines wie InnoDB, MyISAM, Memory usw.). Die Speicher-Engine ist für das Speichern und Abrufen von Daten in MySQL und das Beantworten von Anfragen von Servern der übergeordneten Ebene verantwortlich. Jede Speicher-Engine hat natürlich ihre Vor- und Nachteile. Verschiedene Speicher-Engines können nicht miteinander kommunizieren, daher müssen wir je nach Szenario die geeignete Speicher-Engine auswählen.

Der Server kommuniziert über eine API mit der Speicher-Engine. Diese Schnittstellen schirmen die Unterschiede zwischen verschiedenen Speicher-Engines ab und machen diese Unterschiede für den Abfrageprozess der höheren Ebene transparent. Die API der Speicher-Engine besteht aus Dutzenden von Low-Level-Funktionen zum Ausführen von Vorgängen wie „Eine Transaktion starten“ oder „Eine Datensatzzeile basierend auf dem Primärschlüssel abrufen“.

Es ist zu beachten, dass in MySQL 5.1 und früheren Versionen MyISAM die Standardspeicher-Engine ist und nach MySQL 5.5.5 InnoDB die Standardspeicher-Engine wird.

2. Anschluss

Die offizielle Dokumentation für MySQL 5.7 beschreibt den Connector wie folgt:

MySQL-Konnektoren stellen für Client-Programme die Verbindung zum MySQL-Server her.

Der MySQL Connector ermöglicht Client-Programmen die Verbindung zu einem MySQL-Server. Genauer gesagt erledigt der Connector eigentlich zwei Aufgaben: Zum einen die Verwaltung von MySQL-Verbindungen und zum anderen die Überprüfung der Berechtigungen. Lassen Sie uns jeden einzelnen Schritt einzeln erklären.

Um zunächst eine Verbindung mit dem MySQL-Server herzustellen, müssen wir normalerweise den MySQL-Benutzernamen und das MySQL-Passwort angeben. Wenn der Server auf einem anderen Computer ausgeführt wird als dem, von dem aus wir angemeldet sind, müssen wir auch einen Hostnamen wie „Host“ angeben. Der Verbindungsbefehl sieht also im Allgemeinen folgendermaßen aus:

Shell> mysql -h Host -u Benutzer -p

Passwort eingeben: ********

Wenn Sie auf derselben Maschine angemeldet sind, auf der MySQL läuft, können Sie den Hostnamen natürlich weglassen und einfach Folgendes verwenden:

Shell>mysql -u Benutzer -p

Sie sollten alle mit dem obigen Befehl vertraut sein.

OK, nachdem der klassische TCP-Drei-Wege-Handshake zum Herstellen einer Verbindung über den obigen Befehl abgeschlossen ist, authentifiziert der Connector Ihre Identität anhand des von Ihnen eingegebenen Benutzernamens und Kennworts:

1) Wenn der Benutzername oder das Passwort falsch sind, erhalten Sie die Fehlermeldung „Zugriff für Benutzer verweigert“ und das Client-Programm wird abgebrochen.

2) Wenn die Authentifizierung mit Benutzername und Passwort erfolgreich ist, wird die folgende Inhaltszeichenfolge angezeigt:

mysql> teilt Ihnen mit, dass MySQL bereit ist und Sie mit der Eingabe von SQL-Anweisungen beginnen können!

Natürlich vergleicht der Connector nicht nur Benutzername und Passwort; er überprüft auch, ob der Benutzer die Berechtigung hat, eine bestimmte Abfrage auszuführen (beispielsweise, ob der Benutzer eine SELECT-Anweisung für die Tabelle „Country“ in der Datenbank „World“ ausführen darf). Danach hängt die gesamte Berechtigungsbeurteilungslogik in diesem Zusammenhang von den zu diesem Zeitpunkt gelesenen Berechtigungen ab.

Dies bedeutet, dass, sobald ein Benutzer erfolgreich eine Verbindung hergestellt hat, selbst wenn Sie die Berechtigungen dieses Benutzers mit einem Administratorkonto auf einem anderen Terminal ändern, dies keine Auswirkungen auf die Berechtigungen der bestehenden Verbindung hat.

Das heißt, nachdem die Benutzerberechtigungen geändert wurden, verwenden nur neu erstellte Verbindungen die neuen Berechtigungseinstellungen.

Wenn eine Verbindung hergestellt ist und Sie keine weitere Aktion ausführen, befindet sich die Verbindung im Leerlaufzustand (Ruhezustand).

Tatsächlich gibt es für eine MySQL-Verbindung (oder einen Thread) jederzeit einen Status, der angibt, was MySQL derzeit tut. Es gibt mehrere Möglichkeiten, den aktuellen Status anzuzeigen. Am einfachsten ist es, den Befehl SHOW FULL PROCESSLIST zu verwenden (die Spalte „Befehl“ im vom Befehl zurückgegebenen Ergebnis zeigt den aktuellen Status an).

Während des Lebenszyklus einer Abfrage ändert sich der Status viele Male. Ich werde sie hier nicht im Detail auflisten. Der Sleep in der obigen Abbildung bedeutet, dass die aktuelle Verbindung darauf wartet, dass der Client eine neue Anfrage sendet, und Query Abfragezustand bedeutet, dass die aktuelle Verbindung eine Abfrage ausführt oder das Ergebnis an den Client sendet.

In den Standardeinstellungen von MySQL trennt der Server die Verbindung, wenn sich eine Verbindung 8 Stunden lang im Ruhezustand befindet (d. h. länger als 8 Stunden nicht verwendet wird), und alle nachfolgenden Vorgänge für die Verbindung schlagen fehl. Diese Zeit wird durch den Parameter wait_timeout gesteuert:

Abfrage-Cache

OK, nachdem die Verbindung hergestellt ist, können wir die Select-Anweisung für die Abfrage eingeben. Die Ausführungslogik kommt zum zweiten Schritt: Abfrage-Cache.

Die offizielle Dokumentation erklärt Query Cache wie folgt:

Der Abfragecache speichert den Text einer SELECT-Anweisung zusammen mit dem entsprechenden Ergebnis, das an den Client gesendet wurde. Wenn später eine identische Anweisung empfangen wird, ruft der Server die Ergebnisse aus dem Abfragecache ab, anstatt die Anweisung erneut zu analysieren und auszuführen. Der Abfragecache wird von mehreren Sitzungen gemeinsam genutzt, sodass ein von einem Client generierter Ergebnissatz als Antwort auf dieselbe Abfrage gesendet werden kann, die von einem anderen Client ausgegeben wurde.

Das heißt, der Abfrage-Cache speichert den Text der SELECT-Anweisung und die entsprechenden Ergebnisse, die dem Client zurückgegeben werden. Wenn der Server also später dieselbe SELECT-Anweisung empfängt, ruft er zuerst die Ergebnisse aus dem Abfragecache ab, anstatt die Anweisung erneut zu analysieren und auszuführen. Der Abfragecache wird zwischen Sitzungen gemeinsam genutzt, sodass ein von einem Client generierter Ergebnissatz als Antwort auf dieselbe Abfrage eines anderen Clients gesendet werden kann.

Wenn die aktuelle Abfrage den Abfragecache erreicht, überprüft MySQL die Benutzerberechtigungen einmal, bevor die Abfrageergebnisse zurückgegeben werden. Es besteht weiterhin keine Notwendigkeit, die SQL-Anweisung der Abfrage zu analysieren, da die Tabelleninformationen, auf die die aktuelle Abfrage zugreifen muss, bereits im Abfragecache gespeichert sind.

Da es sich um einen Cache handelt, lässt sich das Problem der Cache-Konsistenz nicht vermeiden. Glücklicherweise werden beim Abfragen des Caches keine veralteten Daten zurückgegeben, ohne dass wir dafür zusätzliche Arbeit benötigen!

Der Abfragecache gibt keine veralteten Daten zurück. Wenn Tabellen geändert werden, werden alle relevanten Einträge im Abfragecache gelöscht.

Wenn eine Tabelle geändert wird, werden alle zugehörigen Einträge im Abfragecache gelöscht. Beachten Sie, dass „löschen“ hier eher „löschen“ als „aktualisieren“ bedeutet.

Sieht ziemlich gut aus? Der Ungültigkeitscache kann ohne manuelles Eingreifen automatisch gelöscht werden.

Aufgrund dieser Funktion wird die Verwendung des Abfragecaches ab MySQL 5.7.20 offiziell leider nicht mehr empfohlen und der Abfragecache in MySQL 8.0 direkt gelöscht!

Der Abfragecache ist ab MySQL 5.7.20 veraltet und wird in MySQL 8.0 entfernt.

Tatsächlich ist es nicht schwer zu verstehen. Beispielsweise besteht bei einem Forumprojekt mit hohem Datenverkehr ständig die Notwendigkeit, die Post-Tabelle abzufragen, und die Posts nehmen fast im Sekundentakt zu. Sobald diese Tabelle aktualisiert wird, werden alle Abfrage-Caches in dieser Tabelle gelöscht. Sie können sich den enormen Druck auf die MySQL-Datenbank vorstellen. Ich habe mir große Mühe gegeben, die Abfrageergebnisse zu speichern, aber bevor ich sie verwenden konnte, wurden sie durch ein Update gelöscht.

Bei Versionen vor MySQL 8.0 können Sie den Parameter query_cache_type auf DEMAND setzen, sodass alle SQL-Anweisungen den Abfrage-Cache nicht mehr verwenden. Für Anweisungen, bei denen Sie sicher sind, dass sie die Abfrage-Zwischenspeicherung verwenden, können Sie dies explizit mit SQL_CACHE angeben, wie in der folgenden Anweisung:

mysql> wähle SQL_CACHE * von t1, wobei ID = 1;

4. Parser

Wenn kein Treffer vorliegt oder der Abfragecache nicht aktiviert ist, konvertiert der MySQL-Server als Nächstes eine SQL-Anweisung in einen Ausführungsplan und interagiert dann entsprechend diesem Ausführungsplan mit der Speicher-Engine. Dies umfasst mehrere Unterphasen: SQL-Parsing, Vorverarbeitung und Optimierung des SQL-Ausführungsplans. Jeder Fehler während dieses Vorgangs (z. B. ein Syntaxfehler) kann zum Abbruch der Abfrage führen.

Die Aufgabe des Parsers besteht im Parsen von SQL und in der Vorverarbeitung, die Aufgabe des Optimierers im Optimieren von SQL-Ausführungsplänen. Hier sprechen wir zuerst über den Parser.

Hier wird im Buch „High Performance MySQL – 3. Ausgabe“ eine genauere Unterteilung vorgenommen. Der Parser wird zum Parsen von SQL verwendet, und der Präprozessor wird zur Vorverarbeitung verwendet. Ich werde sie alle vorerst als Parser klassifizieren.

Beim Parsen von SQL analysiert MySQL SQL-Anweisungen anhand von Schlüsselwörtern und generiert einen entsprechenden „Analysebaum“, um zu überprüfen, ob die Anweisung den grammatikalischen Regeln entspricht. Dabei wird beispielsweise überprüft, ob die falschen Schlüsselwörter verwendet werden, ob die Schlüsselwörter in der richtigen Reihenfolge verwendet werden oder ob die Anführungszeichen richtig gesetzt sind.

Die Vorverarbeitung überprüft außerdem, ob der Analysebaum zulässig ist. Beispielsweise wird überprüft, ob die Datentabelle und die Datenspalten vorhanden sind, ob der Tabellenname und der Feldname korrekt sind usw.

5. Optimierer

Jetzt ist der Analysebaum gültig und MySQL weiß, was Sie versuchen. Eine Abfrage kann jedoch mehrere Ausführungspläne haben und alle liefern dieselben Ergebnisse. Welchen Ausführungsplan sollten wir also wählen?

Hier ist ein einfaches Beispiel:

mysql> select * from t1, wobei ID = 10 und Name = "gut";

Für die obige Anweisung können Sie zuerst nach Name = gut und dann nach ID = 10 suchen, oder Sie können zuerst nach ID = 10 und dann nach Name = gut suchen. Der Zeitaufwand dieser beiden Ausführungspläne kann unterschiedlich sein.

Die Rolle des Optimierers besteht darin, den besten Ausführungsplan darunter zu finden. Es ist zu beachten, dass der Ausführungsplan hier eine Datenstruktur ist und nicht wie bei vielen anderen relationalen Datenbanken entsprechende Bytecodes generiert.

Darüber hinaus ist es dem Optimierer egal, welche Speicher-Engine die Tabelle verwendet, aber die Speicher-Engine hat einen Einfluss auf die Optimierung der Abfragen. Der Optimierer fordert die Speicher-Engine auf, Kapazitäts- oder Kosteninformationen für einen bestimmten Vorgang sowie statistische Informationen zu Tabellendaten bereitzustellen.

Wenn die Optimiererphase abgeschlossen ist, ist der Ausführungsplan für die Anweisung fertiggestellt und die Executorphase kann beginnen.

6. Aktuator

Genau wie beim Aufrufen des Abfrage-Caches ermittelt der Executor vor der Ausführung der SQL-Anweisung zunächst, ob der aktuelle Benutzer die Berechtigung zum Ausführen der Abfrage für diese Tabelle hat. Wenn nicht, wird ein Fehler zurückgegeben, der angibt, dass der Benutzer keine Berechtigung hat.

Nachdem die Berechtigungsauthentifizierung abgeschlossen ist, wird MySQL Schritt für Schritt gemäß den Anweisungen im Ausführungsplan ausgeführt. Im Prozess der schrittweisen Ausführung gemäß dem Ausführungsplan müssen viele Vorgänge durch Aufrufen der von der Speicher-Engine implementierten Schnittstellen abgeschlossen werden. Diese Schnittstellen werden als „Handler-API“-Schnittstellen bezeichnet.

Jede Tabelle in der Abfrage wird durch eine Handlerinstanz dargestellt. Tatsächlich erstellt MySQL während der Optimierungsphase für jede Tabelle eine Handler-Instanz. Der Optimierer kann anhand der Schnittstellen dieser Instanzen relevante Informationen über die Tabelle abrufen, darunter alle Spaltennamen, Indexstatistiken usw.

Zum Beispiel:

mysql> wähle * von t1, wobei ID = 10;

Angenommen, wir verwenden die Standard-InnoDB-Engine, dann ist der Ausführungsfluss des Executors ungefähr wie folgt (beachten Sie, dass, wenn die ID kein Index ist, ein vollständiger Tabellenscan durchgeführt wird, bei dem Zeile für Zeile gesucht wird. Wenn es sich um einen Index handelt, wird die Abfrage in der Indexorganisationstabelle durchgeführt, was effizienter ist. Hier nehmen wir einen Nichtindex als Beispiel):

1) Rufen Sie die Schnittstelle der InnoDB-Engine auf, um die erste Datensatzzeile in dieser Tabelle abzurufen, und bestimmen Sie, ob der ID-Wert 10 ist. Wenn dies der Fall ist, speichern Sie diese Datensatzzeile in einem Satz. Wenn nicht, fahren Sie mit der Beurteilung der nächsten Zeile fort, bis die letzte Zeile dieser Tabelle abgerufen wird.

2) Der Executor gibt den Datensatz, der aus allen Zeilen besteht, die die Bedingungen im obigen Durchlaufprozess erfüllen, als Ergebnis an den Client zurück.

VII. Zusammenfassung

Fassen Sie den Ausführungsprozess der nächsten Abfrageanweisung zusammen:

1. Es wird eine Verbindung zwischen dem MySQL-Client und dem Server hergestellt, und der Client sendet eine Abfrage an den Server.

2. Der Server überprüft zunächst den Abfragecache. Wenn der Cache gefunden wird, wird das im Cache gespeicherte Ergebnis sofort zurückgegeben. Andernfalls wird mit der nächsten Stufe fortgefahren.

3. Der Server führt eine SQL-Analyse und Vorverarbeitung durch, um einen gültigen Analysebaum zu generieren.

4. Der Optimierer generiert dann den entsprechenden Ausführungsplan;

5. MySQL ruft die entsprechende Speicher-Engine-API zur Ausführung gemäß dem vom Optimierer generierten Ausführungsplan auf und gibt das Ausführungsergebnis an den Client zurück.

Oben finden Sie eine detaillierte Analyse der Ausführung einer SQL-Abfrageanweisung in MySQL. Weitere Informationen zur Ausführung von MySQL-Abfrageanweisungen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!