Tutorial zur MySQL-Infrastruktur: Detaillierte Erläuterung des Ausführungsprozesses von Abfrageanweisungen

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 Artikel gekommen.

Dieser Hinweis beschreibt hauptsächlich die grundlegende Architektur von MySQL und wie eine Abfrageanweisung ausgeführt wird.

Werfen wir einen Blick auf die ausführliche Einführung.

Wenn wir beispielsweise eine ID=2 aus der Studententabelle abfragen

Wählen Sie * vom Studenten, wobei ID=2 ist;

Bevor wir den Ausführungsfluss dieser Anweisung erläutern, werfen wir einen Blick auf die grundlegende Architektur von MySQL.

Das Bild stammt aus der MySQL-Praxis von Geek Time. Das Bild beschreibt die logische Architektur von MySQL.

• Die Serverschicht umfasst Konnektoren, Abfrage-Cache, Analysator, Optimierer und Executor und deckt die meisten Kerndienstfunktionen von MySQL sowie alle integrierten Funktionen ab. Alle speicherübergreifenden Engine-Funktionen werden in dieser Schicht implementiert, z. B. gespeicherte Prozeduren, Trigger, Ansichten usw.
• Die Speicher-Engine-Schicht ist für das Speichern und Abrufen von Daten verantwortlich. Sein Architekturmodus ist Plug-in-basiert und unterstützt mehrere Speicher-Engines wie InnoDB, MyISAM, Memory usw. Normalerweise wird die InnoDB-Engine verwendet.

Anschlüsse

Bevor wir die Datenbank verwenden, müssen wir eine Verbindung zur Datenbank herstellen. Die Verbindungsanweisung lautet

mysql -h $ip -u $benutzername -p $passwort

Unser Connector übernimmt diesen Vorgang. Die Hauptfunktion des Connectors besteht darin, eine Verbindung mit dem Client herzustellen, Berechtigungen abzurufen und die Verbindung aufrechtzuerhalten und zu verwalten. Wenn sich die Berechtigungen des Benutzers während der Verwendung des Connectors ändern, wird dies nicht sofort wirksam, da die Benutzerberechtigungen beim Herstellen der Verbindung gelesen werden und die Berechtigungen nur durch erneutes Herstellen der Verbindung aktualisiert werden können.

Das Protokoll für die Kommunikation des Connectors mit dem Client ist TCP. Nachdem die Verbindung hergestellt wurde, können Sie mit show processlist die Anzahl der ausgeführten Verbindungen anzeigen.

Gleichzeitig wird die Verbindung automatisch getrennt, wenn die Verbindungszeit 8 Stunden überschreitet, und der Ruhezustand wird aktiviert. Dies ist die Standardeinstellung von MySQL. Wenn die Verbindung nicht getrennt wird, kann dieser Vorgang als lange Verbindung bezeichnet werden.

Dem entspricht die Kurzverbindung, also das Trennen der Verbindung nach der Ausführung einer oder mehrerer Aktionen.

Bei kontinuierlicher Verwendung langer Verbindungen werden viele Speicherressourcen belegt. Nach MySQL 5.7 können Sie die Anweisung mysql_reset_connection verwenden, um Ressourcen neu zu initialisieren.

Abfrage-Cache

Nach dem Verbinden sind Sie mit der Datenbank verbunden und können nun Anweisungen ausführen.

Beim Ausführen einer Anweisung fragt MySQL zunächst den Cache ab, um festzustellen, ob eine solche Anweisung zuvor ausgeführt wurde. MySQL speichert die zuvor ausgeführten Anweisungen und Ergebnisse in Form von Schlüsselwerten (natürlich gibt es eine bestimmte Speicher- und Gültigkeitsdauer). Wenn der Cache vorhanden ist, wird das zwischengespeicherte Ergebnis direkt zurückgegeben.

Der Caching-Workflow ist

• Der Server empfängt das SQL und durchsucht die Cache-Tabelle mit dem SQL und einigen anderen Bedingungen als Schlüssel
• Wenn der Cache gefunden wird, wird der Cache direkt zurückgegeben
• Wenn der Cache nicht gefunden wird, wird die SQL-Abfrage einschließlich der ursprünglichen SQL-Analyse, Optimierung usw. ausgeführt.
• Nach der Ausführung der SQL-Abfrageergebnisse werden die SQL-Abfrageergebnisse in der Cache-Tabelle zwischengespeichert.

Wenn diese Tabelle geändert wird, sind natürlich alle Caches, die diese Tabelle verwenden, nicht mehr gültig und die Abfrage-Cache-Einträge werden gelöscht. Daher ist es nicht sinnvoll, Anweisungen in einer Tabelle zwischenzuspeichern, die wiederholt geändert wird, da der Cache jederzeit wirksam wird und dadurch die Trefferquote des Abfragecaches erheblich reduziert wird, was nicht sehr kosteneffizient ist.

Wenn Daten in diese Tabelle geschrieben werden, ist der Cache dieser Tabelle (Treffer-Cache, Cache-Schreibzugriff usw.) ungültig. Wenn in Innodb eine Transaktion diese Tabelle ändert, ist der Cache dieser Tabelle ungültig, bevor die Transaktion festgeschrieben wird. Bevor diese Transaktion festgeschrieben wird, können verwandte Abfragen dieser Tabelle nicht zwischengespeichert werden.

Im Allgemeinen gilt: Wenn es sich um eine statische Tabelle oder eine Tabelle handelt, die sich selten ändert, kann sie zwischengespeichert werden und die Trefferquote ist sehr hoch.

Lassen Sie uns darüber sprechen, wann der Cache verwendet werden soll. Es ist schwierig zu beurteilen, ob das Einschalten des Cache die Systemleistung verbessert.

• Gemessen an der Cache-Trefferquote: Cache-Trefferquote = Anzahl der Cache-Treffer (Qcache_hits) / Anzahl der Abfragen (Com_select)
• Nach Cache-Schreibrate: Schreibrate = Cache-Schreibzeiten (Qcache_inserts) / Abfragezeiten (Qcache_inserts)
• Gemessen am Treffer-Schreib-Verhältnis (Verhältnis = Trefferanzahl (Qcache_hits) / Schreibanzahl (Qcache_inserts)) wird es in MySQL mit hoher Leistung als Index bezeichnet, der die Leistungsverbesserung besser widerspiegeln kann. Im Allgemeinen wird ein Verhältnis von 3:1 für den Abfragecache als effektiv angesehen, und 10:1 ist das Beste.

Analysator

Wenn der Abfragecache nicht effektiv ist oder kein Cache vorhanden ist, verwendet der MySQL-Server einen Analysator, um die Anweisung zu analysieren. Der Analysator wird auch Parser genannt.

Der MySQL-Analysator besteht aus zwei Teilen. Der erste Teil dient der lexikalischen Analyse, um den Zeichenstrom zu scannen und einzelne Wörter gemäß den Wortbildungsregeln zu identifizieren. MySQL verwendet Flex, um einen lexikalischen Scanner zu generieren. MySQL-Schlüsselwörter und Funktionsschlüsselwörter werden in sql/lex.h definiert und in zwei Arrays gespeichert. Die Funktion des zweiten Teils ist die Syntaxanalyse. Auf der Grundlage der lexikalischen Analyse werden Wortfolgen zu grammatikalischen Phrasen kombiniert und schließlich ein Syntaxbaum generiert und an den Optimierer übermittelt. Der Syntaxanalysator verwendet Bison und definiert die Syntaxregeln in sql/sql_yacc.yy. Generieren Sie dann den Syntaxbaum gemäß der Theorie der relationalen Algebra.

Die obige Erklärung des Analysators ist zu offiziell und kompliziert. Tatsächlich wird der Analysator hauptsächlich verwendet, um eine "lexikalische Analyse" durchzuführen und dann zu verstehen, was die Datenbankanweisung tut und was sie bedeutet.

Wenn der Analysator zu diesem Zeitpunkt feststellt, dass ein Problem mit dieser Anweisung vorliegt, wird ein Fehler gemeldet, z. B. ERROR 1064 (42000): Sie haben einen Fehler in Ihrer SQL-Syntax

Optimierer

Nachdem der Analysator die Analyse abgeschlossen hat und weiß, was die Anweisung bewirkt, besteht der nächste Schritt darin, einen dedizierten Optimierer zur Optimierung der Anweisung zu verwenden. Die Aufgabe des Optimierers besteht darin, die beste Lösung zur Ausführung der SQL-Abfrage zu finden. Die meisten Abfrageoptimierer, einschließlich des MySQL-Abfrageoptimierers, suchen in allen möglichen Szenarien zur Abfrageauswertung auf einem mehr oder weniger optimalen Niveau.

Der Optimierer wählt hauptsächlich einen optimalen Ausführungsplan aus, der den Overhead reduzieren und die Ausführungseffizienz verbessern soll.

Der MySQL-Optimierer ist eine sehr komplexe Komponente, die viele Optimierungsstrategien verwendet, um einen optimalen Ausführungsplan zu generieren:

• Definieren Sie die Zuordnungsreihenfolge der Tabellen neu (wenn mehrere Tabellen verknüpft und abgefragt werden, müssen sie nicht unbedingt die in SQL angegebene Reihenfolge aufweisen, es gibt jedoch einige Techniken zum Festlegen der Zuordnungsreihenfolge).
• Optimieren Sie die Funktionen MIN() und MAX() (um den Minimalwert einer Spalte zu finden, müssen Sie, wenn die Spalte einen Index hat, nur das äußerste linke Ende des B+Tree-Index finden, andernfalls können Sie den Maximalwert finden. Das spezifische Prinzip finden Sie unten.)
• Beenden Sie die Abfrage frühzeitig (wenn Sie beispielsweise „Limit“ verwenden, wird die Abfrage sofort beendet, nachdem ein Ergebnissatz gefunden wurde, der die Menge erfüllt).
• Sortierung optimieren (In der alten Version von MySQL wurde eine Sortierung mit zwei Übertragungen verwendet, d. h. zuerst wurden der Zeilenzeiger und das zu sortierende Feld im Speicher gelesen, um sie zu sortieren, und dann wurden die Datenzeilen entsprechend dem Sortierergebnis gelesen. Die neue Version verwendet eine Sortierung mit einer Übertragung, d. h. alle Datenzeilen wurden auf einmal gelesen und dann entsprechend der angegebenen Spalte sortiert. Bei E/A-intensiven Anwendungen ist die Effizienz viel höher.)

Da MySQL sich ständig weiterentwickelt, entwickeln sich auch die vom Optimierer verwendeten Optimierungsstrategien ständig weiter. Hier stellen wir nur einige sehr gängige und leicht verständliche Optimierungsstrategien vor.

Stellantrieb

Nachdem der Analysator weiß, was die Anweisung tun soll, und der Optimierer weiß, wie er es tun soll, ist der nächste Schritt die Ausführung, die an den Executor übergeben wird.

Bei der Ausführung ermittelt der Executor zunächst, ob der Benutzer über Ausführungsberechtigung für die Tabelle verfügt. Wenn nicht, wird eine Fehlermeldung wie „denied“ zurückgegeben.

Wenn Sie über die Berechtigung verfügen, wird die Tabelle geöffnet und die Ausführung fortgesetzt. Beim Öffnen einer Tabelle verwendet der Executor die Schnittstelle der Engine basierend auf der in der Tabelle definierten Engine.

Abschließend wird die Anweisung ausgeführt, um Daten abzurufen und an den Client zurückzugeben.

Zusammenfassen

Nachdem MySQL die SQL-Anweisung erhalten hat, läuft der allgemeine Ablauf wie folgt ab:

0. Der Connector ist für die Kommunikation mit dem Client verantwortlich

1. Cache abfragen: Fragen Sie zuerst den Cache ab, um festzustellen, ob ein KV-Cache vorhanden ist

2. Parser: verantwortlich für das Parsen und Weiterleiten von SQL

3. Präprozessor: Überprüfen Sie den analysierten SQL-Baum

4. Optimierer: Holen Sie sich einen Ausführungsplan

5. Abfrageausführungs-Engine: Der Executor führt die Anweisung aus, um den Datenergebnissatz zu erhalten

6. Senden Sie die Daten an den Anrufer zurück.

Nun, 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.