Der Lebenszyklus einer INSERT-Anweisung in MySQL

1. Hintergrund: Grundlegende MySQL-Architektur

Im Großen und Ganzen ist MySQL grob in zwei Teile unterteilt: die Server-Schicht und die Storage-Engine-Schicht.

Server-Schicht

Dazu gehören der Verbindungsmanager, der Query-Cache, der Parser, der Optimierer, der Executor usw. Funktionen wie gespeicherte Prozeduren, Trigger und Views werden ebenfalls auf dieser Schicht implementiert.

Verbindungsmanager: Verantwortlich für die Handhabung der Verbindung zwischen dem Client und dem Server. Er akzeptiert Client-Anfragen, führt Authentifizierungs- und Berechtigungsprüfungen durch und verwaltet den Verbindungslebenszyklus.
Query-Cache: In älteren Versionen von MySQL verfügbar, aber in neueren Versionen nicht mehr empfohlen. Er speichert Abfragen und ihre Ergebnisse zwischen, um die Abfrageleistung zu verbessern. In hochfrequentierten und großvolumigen Datenbanken kann er jedoch aufgrund von Sperren und Overhead-Problemen zu einem Leistungsengpass werden.
Parser: Verantwortlich für die Analyse von SQL-Abfragen, die Überprüfung ihrer Syntax und Semantik und die Sicherstellung der Korrektheit. Er konvertiert SQL-Anweisungen in interne Datenstrukturen, die vom Optimierer und Executor verwendet werden.
Optimierer: Empfängt Abfragen vom Parser und bestimmt die effizienteste Art und Weise, sie auszuführen. Der Optimierer zielt darauf ab, den optimalen Ausführungspfad zu finden, indem er geeignete Indizes, Join-Reihenfolgen und Zugriffsmethoden auswählt, um die Leistung zu verbessern.
Executor: Führt den vom Optimierer generierten Ausführungsplan aus, ruft Daten von der Storage-Engine ab und bearbeitet Client-Anfragen. Er interagiert mit der Storage-Engine, um die Abfrage auszuführen und Ergebnisse an den Benutzer zurückzugeben.
Storage-Engine-Schicht: Verantwortlich für die Datenspeicherung und -abfrage. MySQL unterstützt mehrere Storage-Engines wie InnoDB, MyISAM und Memory. In der täglichen Entwicklung wird typischerweise InnoDB verwendet. Seit MySQL Version 5.5 ist InnoDB die Standard-Storage-Engine.

Nachdem wir nun die grundlegende MySQL-Architektur vorgestellt haben, wollen wir uns ansehen, was die einzelnen Komponenten bei der Verarbeitung einer Write-SQL-Anweisung tun.

2. Verbindungsmanager

Um eine Write-SQL-Anweisung auszuführen, beginnen Sie in der Regel im MySQL-Client, indem Sie einen Befehl eingeben, um sich mit dem MySQL-Server zu verbinden. Auf der Serverseite ist es der Verbindungsmanager, der die Verbindung mit Ihrem Client herstellt, die Authentifizierung abwickelt und den Verbindungslebenszyklus verwaltet.

Verbindungsbefehl:

mysql -h(ip address) -P(port) -u(username) -p

Nachdem Sie den Verbindungsbefehl und das korrekte Passwort eingegeben und den klassischen TCP-Handshake abgeschlossen haben, wird die Verbindung zum MySQL-Server erfolgreich hergestellt.

Anschließend können Sie direkt einen Write-SQL-Befehl eingeben und das Ergebnis sehen:

mysql> insert into user_score_tab(user_id,score) values(888,10);
Query OK, 1 row affected (0.02 sec)

3. Query-Cache

In MySQL-Versionen 5.6 und früher stand nach einer erfolgreichen Verbindung der Query-Cache zur Verfügung, um SQL-Abfragen zu optimieren. Wenn die Tabelle, die Sie abfragen, aktualisiert oder in die sie eingefügt wird, wird der Cache geleert. Daher leert die Ausführung einer Write-SQL-Anweisung den Cache.

In neueren Versionen von MySQL (wie z. B. 8.0) ist der Query-Cache sogar vollständig veraltet. Dies liegt daran, dass die Abfragezwischenspeicherung in hochfrequentierten und großvolumigen Datenbankumgebungen zu Leistungsproblemen führen kann. Tests haben gezeigt, dass die Deaktivierung des Query-Cache die Gesamtleistung und Skalierbarkeit tatsächlich verbessern kann.

4. Parser

Wenn Sie einen Write-SQL-Befehl an den MySQL-Server übermitteln, muss dieser den Befehl parsen, um zu verstehen, was er tun soll.

Der Parser führt zunächst eine lexikalische Analyse durch. Das von Ihnen übermittelte SQL besteht aus Zeichenketten und Leerzeichen, und MySQL analysiert, was jede Zeichenkette bedeutet. Zum Beispiel in diesem Insert-SQL:

insert into user_score_tab(user_id,score) values(888,10);

Es parst das Schlüsselwort insert into, identifiziert user_score_tab als Tabelle und user_id, score als Spaltennamen. Nach der lexikalischen Analyse folgt die Syntaxanalyse.

Die Syntaxanalyse prüft, ob Ihre SQL-Anweisung den MySQL-Syntaxregeln entspricht. Wenn Ihr SQL beispielsweise falsch ist, wird ein Fehler wie dieser zurückgegeben:

mysql> inser into user_score_tab(user_id,score) value(888,10);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inser into user_score_tab(user_id,score) value(888,10)' at line 1

Nachdem sowohl die lexikalische als auch die Syntaxanalyse abgeschlossen sind, versteht das System, dass es sich um ein Insert-SQL handelt.

5. Optimierer

Für einfache INSERT-SQL-Anweisungen führt der Optimierer keine komplexe Abfrageplangenerierung durch.

Der Optimierer ist für die Indexauswahl und -wartung zuständig, führt aber in diesem Fall keine komplexe Abfrageoptimierung durch. Wenn die Tabelle einen Primärschlüssel oder Index hat, stellt der Optimierer sicher, dass der Index aktualisiert wird, um die Datenkonsistenz zu wahren, und prüft die Constraint-Bedingungen während der Dateneinfügung.

Die INSERT-Anweisung führt auch zur Generierung eines Ausführungsplans, der detailliert beschreibt, wie die Datenbank auf Daten zugreift, welche Indizes verwendet werden und in welcher Reihenfolge Datenoperationen durchgeführt werden.

6. Executor

Der Executor ist für die Durchführung der eigentlichen SQL-Operation verantwortlich und ist das zentrale Ausführungsmodul des Datenbanksystems. Für eine INSERT-Anweisung übernimmt der Executor den eigentlichen Dateneinfügungsprozess.

Bestimmung des Einfügepunkts: Basierend auf dem Ausführungsplan des Optimierers identifiziert der Executor die genaue Position in der Tabelle, an der die Daten eingefügt werden sollen - wie z. B. die Position, die durch einen Primär- oder eindeutigen Schlüssel vorgegeben ist.
Laden von Datenseiten: Wenn sich die Zieldatenseite bereits im Speicher (Buffer Pool) befindet, wird sie direkt verwendet. Andernfalls lädt sie die entsprechende Datenseite von der Festplatte in den Speicher.
Indexaktualisierung: Wenn die Tabelle Indizes hat (wie Primärschlüssel, eindeutige Indizes oder andere), aktualisiert der Executor diese entsprechend.

7. Buffer Pool

Der Buffer Pool ist ein Speicherbereich in der InnoDB-Storage-Engine von MySQL, der Datenseiten, Indexseiten und andere Inhalte aus Datenbanktabellen zwischenspeichert. Sein Hauptzweck ist die Verbesserung der Lese-/Schreibleistung und die Reduzierung von Festplatten-I/O-Operationen.

Der Executor fügt neue Daten in die entsprechende Datenseite innerhalb des Buffer Pools ein. Diese Operation findet im Speicher statt und verändert nicht sofort Dateien auf der Festplatte.

8. Undo Log

Bevor die Daten tatsächlich eingefügt werden, erzeugt InnoDB ein Undo Log. Für INSERT-Operationen zeichnet das Undo Log auf, wie der neu eingefügte Datensatz gelöscht werden kann (dies wird verwendet, um das Einfügen rückgängig zu machen, wenn die Transaktion später abgebrochen wird).

Warum wird ein Undo Log erzeugt?

Wenn eine Transaktion rückgängig gemacht wird, muss MySQL nicht-festgeschriebene Operationen rückgängig machen. Mit dem Undo Log kann MySQL Datensätze löschen, die eingefügt, aber nicht festgeschrieben wurden, wodurch die Atomarität von Transaktionen gewährleistet wird.

9. Redo Log

Nachdem der Executor die Daten eingefügt hat, wird die Operation sofort im Redo Log aufgezeichnet.

Um die Datenzuverlässigkeit zu gewährleisten, verwendet MySQL einen Write-Ahead Logging (WAL)-Mechanismus. Bevor die Daten physisch auf die Festplatte geschrieben werden, wird die Operation zuerst im Redo Log protokolliert.

Wie sieht der Prozess aus?

MySQL schreibt die Operation zuerst in das Redo Log und markiert sie als prepare (Pre-Commit)-Status. Das bedeutet, dass MySQL im Falle eines Absturzes das Redo Log abspielen kann, um die Daten wiederherzustellen.

10. Schreiben in Binlog

Während des Schreibens in das Redo Log schreibt MySQL die Operation auch in das Binlog für Replikations- und Disaster-Recovery-Zwecke.

Das Binlog ist das logische Log von MySQL, das die Details von SQL-Operationen wie INSERT INTO aufzeichnet. Es unterscheidet sich vom Redo Log, das ein physisches Log ist.

11. Transaktions-Commit (Two-Phase Commit)

Im Two-Phase-Commit-Prozess aktualisiert MySQL den Redo-Log-Status auf commit, wenn die Transaktion festgeschrieben wird.

Warum ist ein Two-Phase-Commit notwendig?

Um die Konsistenz zwischen dem Binlog und dem Redo Log zu gewährleisten. Wenn das System abstürzt, kann MySQL Operationen aus dem Redo Log wiedergeben und fehlende Daten mithilfe des Binlogs wiederherstellen.

12. Schreiben von Daten auf die Festplatte

Der Executor schreibt Dirty Pages im Speicher nicht sofort auf die Festplatte. Hintergrund-Threads schreiben Dirty Pages asynchron aus dem Buffer Pool gemäß bestimmten Strategien (z. B. periodisches Schreiben) in die Tablespace-Datei auf der Festplatte. Dies vermeidet häufige Festplatten-I/O-Operationen und verbessert die Leistung.