Detaillierte Erläuterung des Ausführungsprozesses der MySQL-Update-Anweisung

Es gab bereits einen Artikel über den Ausführungsprozess von MySQL-Abfrageanweisungen. Hier finden Sie eine Zusammenfassung des Ausführungsprozesses von Aktualisierungsanweisungen. Da bei der Aktualisierung eine Datenänderung erfolgt, ist leicht davon auszugehen, dass die Update-Anweisung komplizierter ist als die Select-Anweisung.

1. Vorbereitung

Erstellen einer Testtabelle

CREATE TABLE `test` (
 `id` int(11) NICHT NULL AUTO_INCREMENT,
 `c` int(11) NICHT NULL STANDARD '0' KOMMENTAR 'Wert',
 PRIMÄRSCHLÜSSEL (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Testtabelle';

Einfügen von drei Daten

EINFÜGEN IN `test` (`c`) WERTE (1), (2), (3);

2. Testen

Wenn ich 1 zum c-Wert der ersten Daten addieren möchte, dann

AKTUALISIEREN SIE `test` SETZEN SIE `c` = `c` + 1, WO `id` = 1;

Nach unserer üblichen Denkweise müssen wir nur diesen Datensatz finden, seinen Wert ändern und ihn speichern.

Aber schauen wir uns die Details an. Da es um die Änderung von Daten geht, sind auch Protokolle erforderlich.

3 Operationsreihenfolge

3.1 Datensätze durchsuchen: Der Executor durchsucht die Engine zuerst nach der Zeile mit der ID=1. Die ID ist der Primärschlüssel und die Engine verwendet direkt die Baumsuche, um diese Zeile zu finden. Wenn sich die Datenseite, auf der sich die Zeile mit der ID = 1 befindet, bereits im Speicher befindet, wird sie direkt an den Executor zurückgegeben. Andernfalls muss sie zuerst von der Festplatte in den Speicher gelesen und dann zurückgegeben werden.

3.2 Der Executor ruft die vom Motor zurückgegebenen Zeilendaten ab, ändert „Num“ in „2“, ruft eine neue Datenzeile ab und ruft dann die Motorschnittstelle auf, um diese neue Datenzeile zu schreiben.

3.3 Die Engine aktualisiert die neue Datenzeile im Speicher und zeichnet den Aktualisierungsvorgang im Redo-Protokoll auf . Zu diesem Zeitpunkt befindet sich das Redo-Protokoll im Vorbereitungszustand .

3.4 Die Engine informiert den Ausführenden, dass die Ausführung abgeschlossen ist und Sie meine Schnittstelle jederzeit aufrufen können, um die Transaktion zu übermitteln.

3.5 Der Executor generiert das Binlog dieser Operation und schreibt das Binlog auf die Festplatte.

3.6 Der Executor ruft die Commit-Transaktionsschnittstelle der Engine auf , und die Engine ändert das gerade geschriebene Redo-Protokoll in den Commit- Status, und die Aktualisierung ist abgeschlossen.

binlog ist der Koordinator der Zwei-Phasen-Commit- Implementierung in MySQL. Es weist jeder Transaktion eine Transaktions-ID zu: XID
Phase 1 :
Starten Sie die Transaktion. Das Redo-Log und das Undo-Log haben die entsprechenden Protokolle aufgezeichnet. Zu diesem Zeitpunkt lautet der Transaktionsstatus „ prepare
Phase 2 :

Nachdem binlog write和fsync abgeschlossen hat, muss die Transaktion erfolgreich festgeschrieben werden. Andernfalls wird die Transaktion zurückgesetzt und ein Commit gesendet, die Undo-Informationen werden gelöscht, Redo wird geleert und der Transaktionsstatus wird auf „Abgeschlossen“ gesetzt.
4. Zwei Arten von Protokollen 4.1 Redo-Protokoll

Ein Redo-Protokoll ist normalerweise ein physisches Protokoll, das physische Änderungen an Datenseiten aufzeichnet und nicht, wie eine bestimmte Zeile oder bestimmte Zeilen geändert werden. Es wird verwendet, um physische Datenseiten nach der Übermittlung wiederherzustellen (Datenseiten wiederherstellen und kann nur bis zur letzten übermittelten Position wiederhergestellt werden).

Der allgemeine Aktualisierungsprozess läuft wie folgt ab:

Fragen Sie die Originaldaten direkt ab und aktualisieren Sie sie sofort. Suchen Sie zunächst ein temporäres Notizbuch, um eine Aufzeichnung zu erstellen, und führen Sie dann die Berechnung und Aktualisierung durch, wenn Sie nicht beschäftigt sind/während der Abrechnung.

Der erste Ansatz ist unter Bedingungen mit vielen gleichzeitigen E/A-Vorgängen nicht sehr optimistisch. Daher wird im Allgemeinen die zweite Methode angewendet.

Auch in MySQL gibt es ein solches Problem. Wenn jeder Aktualisierungsvorgang auf die Festplatte geschrieben werden muss und die Festplatte auch den entsprechenden Datensatz finden und dann aktualisieren muss, sind die E/A- und Suchkosten des gesamten Prozesses sehr hoch. Um dieses Problem zu lösen, verwendeten die Entwickler von MySQL die Idee des Redo-Logs, um die Aktualisierungseffizienz zu verbessern.

Der gesamte Prozess des Betriebs des temporären Notizbuchs und der Originaldaten entspricht auch der in MySQL häufig erwähnten WAL-Technologie (Write-Ahead Logging) . Der vollständige Name von WAL lautet Write-Ahead Logging . Der entscheidende Punkt besteht darin, zuerst das Protokoll zu schreiben und es dann auf die Festplatte zu schreiben . Insbesondere wenn ein Datensatz aktualisiert werden muss, schreibt die InnoDB-Engine den Datensatz zuerst in das Redo-Protokoll (Notizblock) und aktualisiert den Speicher. An diesem Punkt ist die Aktualisierung abgeschlossen. Gleichzeitig aktualisiert die InnoDB-Engine bei Bedarf die Vorgangsdatensätze in diesem Notebook auf der Festplatte. Diese Aktualisierung wird häufig durchgeführt, wenn das System relativ inaktiv ist.

Frage: Wie kann das Problem gelöst werden, dass das Redo-Protokoll (Notepad) voll ist?
Das Redo-Log von InnoDB hat eine feste Größe. Es kann beispielsweise als Gruppe von 4 Dateien konfiguriert werden, von denen jede 1 GB groß ist. Dann kann dieser „Notizblock“ insgesamt 4 GB an Vorgängen aufzeichnen. Beginnen Sie mit dem Schreiben von Anfang an, und wenn Sie das Ende erreicht haben, gehen Sie zurück zum Anfang und schreiben Sie in einer Schleife, wie in der folgenden Abbildung gezeigt.

Schreibposition ist die Position des aktuellen Datensatzes. Sie bewegt sich beim Schreiben rückwärts. Nach dem Schreiben bis zum Ende von Datei Nr. 3 kehrt sie zum Anfang von Datei Nr. 0 zurück. Der Prüfpunkt ist die aktuelle zu löschende Position, die ebenfalls rückwärts und zyklisch verschoben wird. Vor dem Löschen des Datensatzes muss der Datensatz in der Datendatei aktualisiert werden.

Der Bereich zwischen Schreibposition und Prüfpunkt ist der leere Bereich im „Notizblock“, der zum Aufzeichnen neuer Vorgänge verwendet werden kann. Wenn die Schreibposition den Prüfpunkt einholt, bedeutet dies, dass das „Pink Board“ voll ist. Zu diesem Zeitpunkt können keine neuen Aktualisierungen durchgeführt werden. Um den Prüfpunkt zu erreichen, müssen zunächst einige Datensätze angehalten und gelöscht werden.

Mit dem Redo-Log kann InnoDB sicherstellen, dass die zuvor übermittelten Datensätze auch bei einem abnormalen Neustart der Datenbank nicht verloren gehen. Diese Funktion wird als Absturzsicherheit bezeichnet.

4.2 Archivprotokoll binlog

MySQL als Ganzes besteht eigentlich aus zwei Teilen: Der eine ist die Serverschicht, die hauptsächlich die funktionalen Aspekte von MySQL handhabt, der andere ist die Engine-Schicht, die für bestimmte speicherbezogene Angelegenheiten verantwortlich ist. Das obige Redo-Protokoll ist ein Protokoll, das spezifisch für die InnoDB-Engine ist, und die Serverebene verfügt auch über ein eigenes Protokoll namens Binlog (Archivprotokoll).

Warum gibt es zwei Protokolle?

Anfangs gab es in MySQL keine InnoDB-Engine. Die mit MySQL gelieferte Engine ist MyISAM, MyISAM verfügt jedoch nicht über Absturzsicherungsfunktionen und Binlog-Protokolle können nur zum Archivieren verwendet werden. InnoDB wurde von einem anderen Unternehmen in Form eines Plug-Ins in MySQL eingeführt. Da das alleinige Verlassen auf Binlog keine Absturzsicherheit bietet, verwendet InnoDB ein anderes Protokollsystem, nämlich Redo Log, um Absturzsicherheit zu erreichen.

Es gibt drei Unterschiede zwischen diesen beiden Protokollen.

Redo-Log ist spezifisch für die InnoDB-Engine; Binlog ist auf der Serverebene von MySQL implementiert und kann von allen Engines verwendet werden.

Das Redo-Protokoll ist ein physisches Protokoll, das aufzeichnet, „welche Änderungen an einer bestimmten Datenseite vorgenommen wurden“. Das Binärprotokoll ist ein logisches Protokoll, das die ursprüngliche Logik der Anweisung aufzeichnet, z. B. „Füge dem Feld c der Zeile mit der ID = 2 1 hinzu“.

Das Redo-Protokoll wird zyklisch geschrieben, und der Speicherplatz ist irgendwann erschöpft. Das Binärprotokoll kann in angehängter Form geschrieben werden. "Schreiben anhängen" bedeutet, dass, nachdem die Binlog-Datei eine bestimmte Größe erreicht hat, zur nächsten gewechselt wird und das vorherige Protokoll nicht überschrieben wird.

5 Zwei-Phasen-Commit

Das Schreiben des Redo-Protokolls ist in zwei Schritte unterteilt: Vorbereiten und Übernehmen, was als „Zwei-Phasen-Übernehmen“ bezeichnet wird.

5.1 Warum ist ein „Zwei-Phasen-Commit“ notwendig? Dies dient dazu, die Logik zwischen den beiden Protokollen konsistent zu machen.

Binlog zeichnet alle logischen Operationen in einem „ Append Write “-Format auf. Wenn Ihr DBA verspricht, dass die Wiederherstellung innerhalb eines halben Monats möglich ist, speichert das Sicherungssystem auf jeden Fall alle Binärprotokolle des letzten halben Monats und führt regelmäßig Sicherungskopien der gesamten Datenbank durch. Das „regelmäßig“ hängt hierbei von der Wichtigkeit des Systems ab, es kann einmal täglich oder einmal wöchentlich sein.

Wenn Sie zu einer bestimmten Sekunde eine Wiederherstellung durchführen müssen, z. B. an einem Tag um 14:00 Uhr, und Sie feststellen, dass eine Tabelle versehentlich um 12:00 Uhr gelöscht wurde, und die Daten wiederherstellen müssen, haben Sie folgende Möglichkeiten:

Suchen Sie zunächst nach der aktuellsten vollständigen Sicherung. Wenn Sie Glück haben, ist es möglicherweise die Sicherung von gestern Abend. Stellen Sie die temporäre Datenbank aus dieser Sicherung wieder her.

Nehmen Sie dann, beginnend mit dem Sicherungszeitpunkt, nacheinander die gesicherten Binärprotokolle heraus und spielen Sie sie bis zu dem Zeitpunkt vor dem versehentlichen Löschen der Tabelle am Mittag erneut ab.

Auf diese Weise ist Ihre temporäre Datenbank dieselbe wie die Online-Datenbank vor dem versehentlichen Löschen. Anschließend können Sie die Tabellendaten aus der temporären Datenbank nehmen und bei Bedarf in der Online-Datenbank wiederherstellen.

Nachdem wir über den Datenwiederherstellungsprozess gesprochen haben, wollen wir darüber sprechen, warum das Protokoll ein „Zwei-Phasen-Commit“ erfordert. Lassen Sie uns dies anhand eines Widerspruchsbeweises erklären.

5.2 Wenn kein Zwei-Phasen-Commit verwendet wird

Da es sich bei Redo-Log und Binlog um zwei unabhängige Logiken handelt, wird, wenn kein zweiphasiges Commit verwendet wird, entweder zuerst das Redo-Log und dann das Binlog geschrieben oder die Reihenfolge wird umgekehrt.

Sehen wir uns an, welche Probleme bei diesen beiden Ansätzen auftreten.

Wenn wir immer noch die vorherige Aktualisierungsanweisung als Beispiel verwenden, ist der Wert des Felds c in der aktuellen Zeile mit ID=1 1.

5.2.1 Zuerst Redo-Log und dann Binlog schreiben

Angenommen, der MySQL-Prozess wird abnormal neu gestartet, wenn das Redo-Protokoll geschrieben wird, das Binärprotokoll jedoch nicht. Wie bereits erwähnt, können die Daten nach dem Schreiben des Redo-Protokolls auch bei einem Systemabsturz wiederhergestellt werden. Daher beträgt der Wert von c in dieser Zeile nach der Wiederherstellung 2. Da das Binärprotokoll jedoch abstürzte, bevor es fertig geschrieben war, wurde diese Anweisung nicht im Binärprotokoll aufgezeichnet. Wenn dieses Binärprotokoll zum Wiederherstellen der temporären Datenbank verwendet werden muss, fehlt der temporären Datenbank dieses Update, da das Binärprotokoll dieser Anweisung verloren gegangen ist. Der Wert von c in der wiederhergestellten Zeile ist 1, was sich vom Wert in der ursprünglichen Datenbank unterscheidet.

5.2.2 Zuerst Binlog schreiben, dann Log wiederholen

Wenn die Transaktion abstürzt, nachdem das Binärprotokoll geschrieben wurde, wurde das Redo-Protokoll noch nicht geschrieben und die Transaktion ist nach der Wiederherstellung nach dem Absturz ungültig. Der Wert von c in dieser Zeile ist also 1. Allerdings wurde das Protokoll „Ändern von c von 1 auf 2“ im Binärprotokoll aufgezeichnet. Wenn Binlog später zur Wiederherstellung verwendet wird, beträgt der c-Wert dieser Zeile in der wiederhergestellten temporären Datenbank daher 2, was sich vom Wert in der ursprünglichen Datenbank unterscheidet.

Es ist ersichtlich, dass der Zustand der Datenbank, wenn kein „Zwei-Phasen-Commit“ verwendet wird, möglicherweise nicht mit dem Zustand der anhand ihres Protokolls wiederhergestellten Datenbank übereinstimmt.

Tatsächlich ist dieser Vorgang nicht nur erforderlich, um Daten nach einer fehlerhaften Operation wiederherzustellen. Wenn eine Kapazitätserweiterung erforderlich ist, d. h. wenn mehr Backup-Datenbanken benötigt werden, um die Lesekapazität des Systems zu erhöhen, besteht die gängige Praxis heute darin, ein vollständiges Backup plus Anwendungs-Binlog zu verwenden, um dies zu erreichen. Diese „Inkonsistenz“ führt zu Inkonsistenzen in Ihrer Master-Slave-Datenbank online.

Einfach ausgedrückt können sowohl das Redo-Log als auch das Binlog verwendet werden, um den Commit-Status einer Transaktion anzuzeigen, und das Zwei-Phasen-Commit sorgt dafür, dass diese beiden Zustände logisch konsistent bleiben.

Das Redo-Log wird verwendet, um Absturzsicherheit zu gewährleisten. Wenn der Parameter innodb_flush_log_at_trx_commit auf 1 gesetzt ist, bedeutet dies, dass das Redo-Protokoll jeder Transaktion direkt auf der Festplatte gespeichert wird. Es wird empfohlen, diesen Parameter auf 1 zu setzen, um sicherzustellen, dass nach einem abnormalen Neustart von MySQL keine Daten verloren gehen.

Wenn der Parameter sync_binlog auf 1 gesetzt ist, bedeutet dies, dass das Binärprotokoll jeder Transaktion auf der Festplatte gespeichert wird. Es wird außerdem empfohlen, diesen Parameter auf 1 zu setzen, damit das Binärprotokoll nach einem abnormalen Neustart von MySQL nicht verloren geht.

Oben finden Sie den detaillierten Inhalt der ausführlichen Erläuterung des Ausführungsprozesses der MySQL-Update-Anweisung. Weitere Informationen zum Ausführungsprozess der Update-Anweisung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!