Einführung in die MySQL-Gesamtarchitektur

Die Gesamtarchitektur von MySQL ist in die Serverschicht und die Speicher-Engine-Schicht unterteilt. Die Serverebene umfasst Konnektoren, Abfragecache, Analysator, Optimierer, Executor usw. Gespeicherte Prozeduren, Trigger, Ansichten und integrierte Funktionen werden alle in dieser Ebene implementiert. Die Daten-Engine-Schicht ist für die Datenspeicherung und den Datenabruf verantwortlich, wie z. B. InnoDB, MyISAM, Memory und andere Engines. Nachdem der Client eine Verbindung zur Serverebene hergestellt hat, ruft der Server die von der Daten-Engine bereitgestellte Schnittstelle auf, um Datenänderungen vorzunehmen.

Anschlüsse

Verantwortlich für das Herstellen von Verbindungen mit Clients, das Einholen von Benutzerberechtigungen sowie die Aufrechterhaltung und Verwaltung von Verbindungen.

Verwenden Sie show processlist ;“, um den Verbindungsstatus abzufragen. Nachdem ein Benutzer eine Verbindung hergestellt hat, hat es keine Auswirkungen auf die verbundenen Benutzer, selbst wenn der Administrator die Berechtigungen des verbundenen Benutzers ändert. Die Standardverbindungszeit beträgt 8 Stunden, danach wird die Verbindung getrennt.

Beschreiben Sie kurz die lange Verbindung:

Vorteil: Während der Verbindungszeit nutzt der Client immer die gleiche Verbindung und vermeidet so den Ressourcenverbrauch mehrerer Verbindungen.

Nachteile: Wenn MySQL ausgeführt wird, wird der verwendete Speicher vom Verbindungsobjekt verwaltet. Da es längere Zeit nicht freigegeben wird, führt dies zu einem Überlauf des Systemspeichers und wird vom System beendet. Daher ist es erforderlich, lange Verbindungen regelmäßig zu trennen oder die Verbindung nach der Ausführung großer Abfragen zu trennen. Nach MySQL 5.7 können Sie mysql_rest_connection verwenden, um Verbindungsressourcen zu initialisieren, ohne die Verbindung erneut herzustellen oder eine Berechtigungsüberprüfung durchzuführen.

Abfrage-Cache

Wenn eine Abfrageanforderung eingeht, wird der Abfragecache (Schlüssel-/Wertspeicher) abgefragt, um festzustellen, ob sie ausgeführt wurde. Wenn nicht, folgen Sie dem normalen Ausführungsprozess.

In tatsächlichen Situationen ist es jedoch im Allgemeinen nicht erforderlich, einen Abfragecache einzurichten. Denn der Cache wird geleert, wenn die in der Abfrage enthaltene Tabelle aktualisiert wird. Es ist daher für statische Tabellen geeignet. Nach MySQL 8.0 wird der Abfragecache abgeschafft.

Analysator

Lexikalische Analyse:

Beispielsweise das Identifizieren von Auswahl, Tabellenname, Spaltenname und Bestimmen, ob sie vorhanden sind.

Syntaxanalyse:

Bestimmen Sie, ob die Anweisung der MySQL-Syntax entspricht.

Optimierer

Bestimmen Sie die Verwendung von Indizes, die Reihenfolge der Verknüpfungstabellen usw. und wählen Sie die optimale Lösung aus.

Stellantrieb

Vor der Ausführung einer Anweisung wird zunächst eine Berechtigungsprüfung durchgeführt. Wenn die Berechtigung erteilt wird, wird die Abfrage über die von der Daten-Engine bereitgestellte Schnittstelle ausgeführt. Wenn eine langsame Abfrage festgelegt ist, wird im entsprechenden Protokoll rows_examined angezeigt, um die Anzahl der gescannten Zeilen anzugeben. In einigen Szenarien (Indizes) wird der Executor einmal aufgerufen, in der Daten-Engine werden jedoch mehrere Zeilen gescannt. Daher entspricht die Anzahl der von der Engine gescannten Zeilen nicht exakt der Anzahl der untersuchten Zeilen .

Grund für die nicht im Voraus erfolgte Überprüfung der Berechtigungen: In Fällen wie Triggern müssen die Berechtigungen beispielsweise in der Executor-Phase festgelegt werden und können in der Optimizer-Phase nicht überprüft werden.

Verwenden Sie die Profilerstellung, um den SQL-Ausführungsprozess anzuzeigen

Aktivieren Sie die Profilerstellung, um den Anweisungsausführungsprozess zu analysieren:

mysql> wählen Sie @@profiling;
+-------------+
| @@Profilierung |
+-------------+
| 0 |
+-------------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

mysql> Profilierung festlegen=1;
Abfrage OK, 0 Zeilen betroffen, 1 Warnung (0,00 Sek.)

Führen Sie die Abfrageanweisung aus:

mysql> AUSWÄHLEN * FROM s-Limit 10;
+------+--------+-----+-----+
| s_id | s_name | Alter | Geschlecht |
+------+--------+-----+-----+
| 1 | z | 12 | 1 |
| 2 | s | 14 | 0 |
| 3 | c | 14 | 1 |
+------+--------+-----+-----+
3 Zeilen im Satz (0,00 Sek.)

Profile abrufen;

mysql> Profile anzeigen;
+----------+------------+--------------------------+
| Abfrage-ID | Dauer | Abfrage |
+----------+------------+--------------------------+
| 1 | 0,00046600 | AUSWÄHLEN * FROM s-Limit 10 |
+----------+------------+--------------------------+

mysql> Profil anzeigen;
+----------------------+----------+
| Status | Dauer |
+----------------------+----------+
| beginnt | 0,000069 |
| Berechtigungen prüfen | 0,000008 | Tabellen öffnen | 0,000018 | init | 0,000019 | Systemsperre | 0,000010 | Optimieren | 0,000004 | Statistik | 0,000013 |
| wird vorbereitet | 0,000094 | wird vorbereitet | wird ausgeführt | 0,000016 | werden Daten gesendet | 0,000120 |
| Ende | 0,000010 |
| Abfrageende | 0,000015 |
| Tabellen schließen | 0,000014 |
| Elemente freigeben | 0,000032 |
| aufräumen | 0,000026 |
+----------------------+----------+
15 Zeilen im Satz, 1 Warnung (0,00 Sek.)

Abfragespezifische Anweisungen:

mysql> Profil für Abfrage 1 anzeigen;
+----------------------+----------+
| Status | Dauer |
+----------------------+----------+
| beginnt | 0,000069 |
| Berechtigungen werden geprüft | 0,000008 |
| Eröffnungstabellen | 0,000018 |
| init | 0,000019 |
| Systemsperre | 0,000010 |
| optimieren | 0,000004 |
| Statistik | 0,000013 |
| Vorbereitung | 0,000094 |
| wird ausgeführt | 0,000016 |
| Daten werden gesendet | 0,000120 |
| Ende | 0,000010 |
| Abfrageende | 0,000015 |
| Tabellen schließen | 0,000014 |
| Elemente freigeben | 0,000032 |
| aufräumen | 0,000026 |
+----------------------+----------+
15 Zeilen im Satz, 1 Warnung (0,00 Sek.)

MySQL-Protokollierungsmodul

Wie bereits erwähnt, ist MySQL in die Serverebene und die Daten-Engine-Ebene unterteilt, und jede Ebene entspricht auch ihrer eigenen Protokolldatei. Wenn die InnoDB-Engine ausgewählt ist, ist die entsprechende Datei die Redo-Log-Datei. Die Serverebene entspricht der Binlog-Datei. Schauen wir uns weiter unten an, warum es zwei Protokollierungssysteme gibt.

Redo-Protokoll

Redo-Log ist ein InnoDB-spezifisches Protokoll. Warum müssen wir Redo-Log einführen? Stellen Sie sich ein Szenario vor, in dem MySQL Daten in Festplattendateien schreiben muss, um die Persistenz sicherzustellen. Wir wissen, dass beim Schreiben auf die Festplatte Datei-E/A und Suchvorgänge ausgeführt werden. Wenn dies bei jedem Aktualisierungsvorgang der Fall ist, ist die Gesamteffizienz extrem gering und es ist unmöglich, sie zu verwenden.

Da das direkte Schreiben auf die Festplatte nicht möglich ist, besteht die Lösung darin, die Daten zunächst in den Speicher zu schreiben und sie dann auf der Festplatte zu aktualisieren, wenn das System im Leerlauf ist. Das bloße Aktualisieren des Speichers reicht jedoch nicht aus. Wenn das System abstürzt oder abnormal neu gestartet wird, gehen die Daten im Speicher, die nicht auf die Festplatte geschrieben wurden, verloren und die Datenkonsistenz wird zum Problem. Hier kommt das Redo-Log ins Spiel. Wenn ein Aktualisierungsvorgang stattfindet, schreibt InnoDb zuerst das Redo-Log (zeichnet auf, wie sich die Daten geändert haben), aktualisiert dann den Speicher und schreibt es schließlich zu einem geeigneten Zeitpunkt auf die Festplatte, normalerweise, wenn das System im Leerlauf ist. Der Vorgang, zuerst Protokolle zu schreiben und sie dann auf die Festplatte zu schreiben, ist die häufig erwähnte WAL-Technologie (Write-Ahead-Logging).

Das Aufkommen des Redo-Logs verbessert nicht nur die Effizienz erheblich, sondern stellt auch sicher, dass MySQL über Absturzsicherheitsfunktionen verfügt und in abnormalen Situationen keine Daten verloren gehen.

In der konkreten Implementierung ist die Größe des Redo-Protokolls festgelegt. Es kann als Gruppe von 4 Dateien konfiguriert werden, wobei jede Datei 1 GB groß ist und die vier Dateien während der Aktualisierung in einer Schleife geschrieben werden.

write pos zeichnet die aktuelle Schreibposition auf und bewegt sich nach dem Schreiben zurück. Wenn das Ende der 4. Datei geschrieben ist, wird ab Position 0 neu geschrieben.

Der Prüfpunkt gibt die aktuelle Position an, die gelöscht werden kann. Wenn die Daten auf der Festplatte aktualisiert werden, bewegt sich der Prüfpunkt zurück.

Die Position zwischen Schreibposition und Prüfpunkt ist der Bereich, in dem Aktualisierungsvorgänge aufgezeichnet werden können. Wenn die Schreibposition den Prüfpunkt einholt, können keine neuen Vorgänge ausgeführt werden. Lassen Sie den Prüfpunkt daher zuerst einige Daten schreiben.

Sie können innodb_flush_log_at_trx_commit auf 1 setzen, um die Redo-Log-Persistenz zu aktivieren.

binlog

Binlog ist ein Protokoll auf Serverebene, das hauptsächlich zur Archivierung verwendet wird und eine Rolle bei der Datensicherung, Master-Slave-Synchronisierung und Datenwiederherstellung spielt. Zu den gängigen Protokollformaten gehören Zeilen-, gemischte und Anweisungsformate. Informationen zur spezifischen Verwendung finden Sie im Binlog-Wiederherstellungsprotokoll.

Sie können das Schreiben des Binärprotokolls auf die Festplatte aktivieren, indem Sie sync_binlog=1 festlegen.

Hier unterscheiden wir zwischen Binlog und Redo:

1. Der Besitzer ist ein anderer. Binlog befindet sich auf Serverebene und kann von allen Engines verwendet werden. Das Redo-Log ist einzigartig für InnoDB.
2. Die Typen sind unterschiedlich. Binlog ist ein logisches Protokoll, das die ursprüngliche Logik der Anweisung (Binlog-Anweisung) aufzeichnet. Das Redo-Log ist ein physisches Protokoll, das aufzeichnet, wie eine Datenseite geändert wurde.
3. Das Schreiben der Daten erfolgt auf unterschiedliche Weise. Binog-Logs werden kontinuierlich angehängt, während Redo-Logs in einer Schleife geschrieben werden.
4. Verschiedene Funktionen: Binlog wird zum Archivieren verwendet, während Redo Log zur Gewährleistung der Absturzsicherheit genutzt wird.
   

Zweiphasiges Commit

Nachfolgend sehen Sie den internen Prozess des Executors und von InnoDB bei der Ausführung von Update:

Nehmen Sie als Beispiel die Anweisung update T set c=c+1 where ID=2;:

1. Der Executor verwendet die InooDB-Engine, um die Zeile zu finden, in der sich die ID befindet, und die ID ist der Primärschlüssel. Die Engine findet die Zeile durch eine Baumsuche und gibt sie an den Executor zurück, wenn sich die Datenseite, auf der sich die Zeile befindet, im Speicher befindet. Andernfalls wird es zuerst von der Festplatte in den Speicher gelesen und dann zurückgegeben.
2. Der Executor ruft die Daten von der Engine ab, addiert 1 zum C-Wert, wartet auf eine neue Zeile und schreibt dann die neuen Daten über die Engine-Schnittstelle neu.
3. Die Engine aktualisiert die Zeile im Speicher, zeichnet den Aktualisierungsvorgang im Redo-Protokoll auf und ändert den Status des Redo-Protokolls auf „Vorbereiten“. Der Testamentsvollstrecker wird dann angewiesen, die Transaktion zum entsprechenden Zeitpunkt durchzuführen.
4. Der Executor generiert ein Binärprotokoll dieser Operation und schreibt das Binärprotokoll auf die Festplatte.
5. Der Executor ruft die Transaktions-Commit-Schnittstelle der Engine auf, um das gerade geschriebene Redo-Protokoll in den Commit-Status zu ändern, und die Aktualisierung ist abgeschlossen.

Helle Farben werden vom Executor ausgeführt und dunkle Farben von der Engine.

Nach der Aktualisierung des Speichers wird das Schreiben des Redo-Protokolls in zwei Schritte aufgeteilt: Vorbereiten und Commit, was häufig als zweiphasiges Commit bezeichnet wird. Wird verwendet, um die Datenkonsistenz sicherzustellen, wenn unerwartete Situationen auftreten.

Nehmen wir hier an, was passieren würde, wenn kein zweiphasiges Commit verwendet würde?

1. Schreiben Sie zuerst das Redo-Log und dann das Binlog. Angenommen, MySQL wird nach dem Schreiben des Redo-Logs abnormal neu gestartet und das Binlog wird zu diesem Zeitpunkt nicht geschrieben. Da das Redolog nach dem Neustart geschrieben wurde, liegt kein Problem mit dem Datenbankinhalt vor. Wenn Sie jedoch zu diesem Zeitpunkt Binlog zum Sichern oder Wiederherstellen verwenden möchten, werden Sie feststellen, dass die Aktualisierungslogik des letzten Eintrags fehlt, was zu Dateninkonsistenzen führt.
2. Schreiben Sie zuerst das Binärprotokoll und dann das Redo-Protokoll. Nachdem das Binärprotokoll geschrieben wurde, wird MySQL abnormal neu gestartet und das Redo-Protokoll wird nicht geschrieben. Nach dem Neustart wird festgestellt, dass das Redo-Protokoll nicht erfolgreich geschrieben wurde und die Transaktion als ungültig angesehen wird. Das Binlog enthält jedoch eine zusätzliche Update-Anweisung. Nach der Wiederherstellung sind die Daten natürlich inkonsistent.

Lassen Sie uns den zweiphasigen Einreichungsprozess analysieren:

1. Der Prozess stürzt während der Redo-Log-Vorbereitungsphase zum Zeitpunkt A ab. Nach dem Neustart wurde festgestellt, dass das Redo-Protokoll nicht geschrieben wurde, sodass die Transaktion zurückgesetzt wurde.

2. Wenn das System beim Schreiben des Binärprotokolls abstürzt, wird nach dem Neustart festgestellt, dass das Binärprotokoll nicht geschrieben wurde. Machen Sie daher den Vorgang rückgängig.

3. Binlog wird geschrieben, aber beim Senden des Redo-Log-Commit-Status tritt ein Absturz auf

• Wenn die Transaktion im Redo-Protokoll abgeschlossen ist und über ein Commit-Flag verfügt, führen Sie ein direktes Commit durch.
• Wenn im Redo-Protokoll nur eine vollständige Vorbereitung vorhanden ist, prüfen Sie, ob das entsprechende Binärprotokoll vollständig ist.

Abgeschlossen, Transaktion bestätigen. Unvollständig, Transaktion zurücksetzen.

Wie kann ich feststellen, ob das Binärprotokoll vollständig ist?

• Anweisungsformat binlog, mit COMMIT; Mark
• Das Binärprotokoll im Zeilenformat enthält ein XID-Ereignis.
• Ab 5.6 gibt es auch einen Binlog-Prüfsummenparameter, um die Richtigkeit des Binlogs zu überprüfen.
  

Wie verknüpft man Redo-Log und Binärlog, um denselben Vorgang darzustellen?

Es gibt ein gemeinsames Datenfeld in der Struktur, XID. Während der Wiederherstellung nach einem Systemabsturz wird das Redo-Protokoll sequenziell gescannt:

• Wenn ein Vorbereitungs- und ein Commit-Redo-Protokoll vorhanden sind, führen Sie das Commit direkt durch.
• Wenn nur ein Vorbereitungs-, aber kein Commit-Redo-Protokoll vorhanden ist, verwenden Sie die XID, um die entsprechende Transaktion zur Beurteilung im Binärprotokoll zu finden.
  

Besteht nach dem Schreiben der Daten eine Beziehung zwischen dem endgültigen Speicherort auf der Festplatte und dem Redo-Protokoll?

• Bei einer normal ausgeführten Instanz ist die Seite im Speicher nach der Änderung nicht mehr mit der Datenseite auf der Festplatte konsistent. Dies wird als „Dirty Page“ bezeichnet. Beim Übertragen von Daten auf die Festplatte werden die Datenseiten im Speicher auf die Festplatte geschrieben.
• Im Absturzszenario stellt InnoDB fest, ob eine Datenseite Aktualisierungen verloren hat, liest sie in den Speicher und lässt anschließend den Speicherinhalt durch das Redo-Log aktualisieren. Nachdem die Aktualisierung abgeschlossen ist, wird die Speicherseite zu einer Dirty Page und kehrt in den Zustand des ersten Falls zurück.

Welche Beziehung besteht zwischen Redo-Log-Puffer und Redo-Log?

Während des Aktualisierungsvorgangs einer Transaktion gibt es mehrere SQL-Anweisungen, sodass mehrere Protokolle geschrieben werden müssen.
Allerdings müssen beim Schreibvorgang die erzeugten Logs zunächst gespeichert werden, können aber nicht direkt vor dem Commit ins Redo-Log geschrieben werden.
Daher wird der Redo-Log-Puffer im Speicher verwendet, um zunächst das Redo-Log zu speichern. Schreiben Sie beim Commit den Inhalt des Puffers in das Redo-Protokoll.

Zusammenfassen

Zu Beginn des Artikels wird erläutert, dass die Gesamtarchitektur von MySQL in die Serverschicht und die Engine-Schicht unterteilt ist, und der Ausführungsprozess einer Anweisung wird kurz beschrieben. Anschließend wählte MySQL nach 5.5 InnoDB als Standard-Engine, da es über mehr Transaktions- und Absturzsicherheitsfunktionen als das native MyISAM verfügt.

Absturzsicherheit wird durch Redo-Log erreicht. Ähnlich wie das Redo-Log gibt es das Binlog, das Protokoll der Server-Engine, das zum Archivieren und Sichern von Daten verwendet wird.

Abschließend sei erwähnt, dass zur Gewährleistung der Datenkonsistenz das Redo-Log und das Binlog in dieselbe Transaktion eingefügt werden, was häufig als zweiphasiger Commit-Vorgang bezeichnet wird.

Oben finden Sie eine ausführliche Einführung in die MySQL-Gesamtarchitektur. Weitere Informationen zur MySQL-Gesamtarchitektur finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!