Speicher-Engine und Protokollbeschreibung basierend auf MySQL (ausführliche Erklärung)

1.1 Einführung in Speicher-Engines

1.1.1 Dateisystemspeicher

Dateisystem: Ein von einem Betriebssystem verwendeter Mechanismus zum Organisieren und Zugreifen auf Daten. Ein Dateisystem ist eine Art Software.

Geben Sie ein: ext2 3 4, xfs-Daten. Unabhängig davon, welches Dateisystem verwendet wird, ändert sich der Dateninhalt nicht. Was sich unterscheidet, sind Speicherplatz, Größe und Geschwindigkeit.

1.1.2 MySQL-Datenbankspeicher

MySQL-Engine: Sie kann als das „Dateisystem“ von MySQL verstanden werden, verfügt jedoch über leistungsfähigere Funktionen.

MySQL-Engine-Funktionen: Neben den grundlegenden Zugriffsfunktionen verfügt es auch über weitere Funktionen wie Transaktionsfunktionen, Sperren, Backup und Wiederherstellung, Optimierung und spezielle Funktionen.

1.1.3 MySQL-Speicher-Engine-Typen

MySQL bietet die folgenden Speicher-Engines:

InnoDB, MyISAM (die beiden am häufigsten verwendeten)
SPEICHER, ARCHIV, FÖDERIERT, BEISPIEL
SCHWARZES HOLDE, ZUSAMMENFÜHREN, NDBCLUSTER, CSV

Darüber hinaus können auch Speicher-Engines von Drittanbietern verwendet werden.

1.1.4 Vergleich zwischen InnoDB und MyISAM

InnoDb-Engine

Unterstützt ACID-Transaktionen und vier Transaktionsisolationsebenen;

Unterstützt Zeilensperren und Fremdschlüsseleinschränkungen: Daher können gleichzeitige Schreibvorgänge unterstützt werden;

Die Gesamtzahl der Zeilen wird nicht gespeichert;

Eine InnoDb-Engine wird in einem Dateibereich (gemeinsam genutzter Tabellenbereich, die Tabellengröße wird nicht vom Betriebssystem gesteuert und eine Tabelle kann auf mehrere Dateien verteilt sein) oder in mehreren Dateien (auf unabhängigen Tabellenbereich eingestellt, die Tabellengröße wird durch die Dateigröße des Betriebssystems begrenzt, im Allgemeinen 2 G) gespeichert, die wiederum durch die Dateigröße des Betriebssystems begrenzt ist.

Der Primärschlüsselindex verwendet einen gruppierten Index (das Datenfeld des Index speichert die Datendatei selbst), und das Datenfeld des Hilfsindex speichert den Wert des Primärschlüssels. Um daher im Hilfsindex nach Daten zu suchen, müssen Sie zuerst den Primärschlüsselwert über den Hilfsindex finden und dann auf den Hilfsindex zugreifen. Am besten verwenden Sie einen automatisch inkrementierenden Primärschlüssel, um beim Einfügen von Daten größere Anpassungen an der Datei zu vermeiden und die B+-Baumstruktur beizubehalten.

Die primäre Indexstruktur von Innodb ist wie folgt:

MyISAM-Engine

Transaktionen werden nicht unterstützt, aber jede Abfrage ist atomar;

Unterstützt Sperren auf Tabellenebene, d. h. jeder Vorgang sperrt die gesamte Tabelle.

Die Gesamtzahl der Zeilen in der Speichertabelle;

Eine MYISAM-Tabelle hat drei Dateien: Indexdatei, Tabellenstrukturdatei und Datendatei;

Mithilfe eines gruppierten Indexes speichert das Datenfeld der Indexdatei einen Zeiger auf die Datendatei. Der Sekundärindex ist grundsätzlich derselbe wie der Primärindex, aber der Sekundärindex muss nicht eindeutig sein.

Die primäre Indexstruktur von MYISAM ist wie folgt:

Die beiden Indexdatensuchvorgänge sind wie folgt:

1.2 InnoDB-Speicher-Engine

Nach MySQL Version 5.5 ist es die Standardspeicher -Engine und bietet hohe Zuverlässigkeit und hohe Leistung.

1.2.1 Vorteile der Innodb-Engine

a) Transaktionssicherheit (ACID-konform)
b) MVCC (Multi-Versioning Concurrency Control)
c) InnoDB-Sperren auf Zeilenebene d) Unterstützung für referenzielle Integritätsbeschränkungen von Fremdschlüsseln e) Schnelle automatische Wiederherstellung nach einem Fehler (absturzsichere Wiederherstellung)
f) Pufferpool (Datenpufferseite, Protokollpufferseite, Undo-Pufferseite) zum Zwischenspeichern von Daten und Indizes im Speicher
g) Maximale Leistung bei großen Datenmengen h) Mischen von Abfragen auf Tabellen mit unterschiedlichen Speicher-Engines i) Konsistentes Lesen ohne Sperren im Oracle-Stil (gemeinsame Sperren)
j) Tabellendaten werden so organisiert, dass Abfragen auf Basis von Primärschlüsseln optimiert werden (Clusterindex).

1.2.2 Übersicht über die Innodb-Funktionen

1.2.3 So fragen Sie die Speicher-Engine ab

Wählen Sie @@default_storage_engine;
oder zeigen Sie Variablen wie „%engine%“ an.

2. Verwenden Sie SHOW, um die Speicher-Engine jeder Tabelle zu bestätigen:

SHOW CREATE TABLE Stadt_G
TABELLENSTATUS ANZEIGEN WIE 'Land/Sprache'\G

3. Verwenden Sie INFORMATION_SCHEMA, um die Speicher-Engine jeder Tabelle zu bestätigen:

Wählen Sie Tabellenname, Engine aus 
INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Stadt'
UND TABLE_SCHEMA = 'world_innodb'\G

4. Migration von Version 5.1 auf Version 5.5 oder höher

Angenommen, alle Produktionstabellen der Datenbankversion 5.1 sind MyISAM.

Nachdem Sie mysqldump zum Sichern verwendet haben, müssen Sie das Engine-Feld in der Sicherungsdatei von myisam durch innodb ersetzen (Sie können den Befehl sed verwenden), da die Migration sonst sinnlos ist.

Achten Sie beim Upgrade der Datenbank auf die Kompatibilität anderer unterstützender Einrichtungen und darauf, ob der Code mit neuen Funktionen kompatibel ist.

1.2.4 Einstellen der Speicher-Engine

1. Legen Sie die Server-Speicher-Engine in der Startkonfigurationsdatei fest:

[mysqld]
default-storage-engine=<Speicher-Engine>

2. Verwenden Sie den Befehl SET, um für die aktuelle Clientsitzung Folgendes festzulegen:

SET @@storage_engine=<Speicher-Engine>;

3. Geben Sie in der Anweisung CREATE TABLE Folgendes an:

CREATE TABLE t (i INT) ENGINE = <Speicher-Engine>;

1.3 Speicherstruktur der InnoDB-Speicher-Engine

1.3.1 Funktionen des InnoDB-System-Tablespace

Standardmäßig werden InnoDB-Metadaten, Undo-Protokolle und Puffer in System-„Tablespaces“ gespeichert.

Dies ist ein einzelner logischer Speicherbereich, der eine oder mehrere Dateien enthalten kann.

Jede Datei kann eine normale Datei oder eine Rohpartition sein.

Die endgültige Datei kann automatisch erweitert werden.

1.3.2 Definition des Tablespace

Tablespace: MySQL-Datenbank-Speichermethode

Der Tablespace enthält die Datendateien

MySQl-Tabellenbereich und Datendatei haben eine 1:1-Beziehung

Außer bei gemeinsam genutztem Tablespace ist eine 1:N-Beziehung möglich

1.3.3 Tablespace-Typ

1. Gemeinsam genutzter Tablespace: ibdata1~ibdataN, normalerweise 2-3

2. Unabhängiger Tabellenbereich: Wird im angegebenen Bibliotheksverzeichnis gespeichert, z. B. city.ibd im Verzeichnis data / world /

Speicherort des Tablespace (Datenverzeichnis):

Im Verzeichnis data/

1.3.4 Speicherinhalte des System-Tablespace

Gemeinsam genutzter Tablespace (physische Speicherstruktur)

ibdata1~N wird normalerweise als Systemtabellenbereich bezeichnet, der durch Dateninitialisierung generiert wird

Systemmetadaten, Basistabellendaten, andere Daten als Tabelleninhaltsdaten.

tmp-Tablespace (generell wenig Aufmerksamkeit)

Undo-Protokoll: Daten – Rollback-Daten (vom Rollback-Protokoll verwendet)

Redo-Protokoll: ib_logfile0~N speichert einige Redo-Protokolle der InnoDB-Tabelle des Systems.

Hinweis: Das Undo-Protokoll wird standardmäßig in ibdata gespeichert und kann nach 5.6 separat definiert werden.

Der tmp-Tablespace wurde nach Version 5.7 aus ibdata1 verschoben und wurde zu ibtmp1

Vor Version 5.5 wurden alle Anwendungsdaten standardmäßig in ibdata gespeichert.

Separater Tablespace (eine Funktion der Speicher-Engine)

Ab 5.6 wird standardmäßig jede Tabelle in einer separaten Tablespace-Datei gespeichert.

Zusätzlich zum Systemtabellenbereich erstellt InnoDB für jede InnoDB-Tabelle zusätzliche Tabellenbereiche im Datenbankverzeichnis für .ibd-Dateien.

Jede neue von InnoDB erstellte Tabelle richtet im Datenbankverzeichnis eine .ibd-Datei ein, die zur .frm-Datei der Tabelle gehört.

Diese Einstellung kann mit der Option innodb_file_per_table gesteuert werden. Durch eine Änderung wird nur die Standardeinstellung für neu erstellte Tabellen geändert. .

1.3.5 Einrichten eines gemeinsam genutzten Tablespace

Anzeigen der aktuellen Einstellungen für den freigegebenen Tablespace

mysql> Variablen wie „innodb_data_file_path“ anzeigen;
+----------------------+------------------------+
| Variablenname | Wert |
+----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+----------------------+------------------------+
Zeile im Satz (0,00 Sek.)

Richten Sie einen gemeinsam genutzten Tablespace ein:

Die Nummer wird normalerweise beim ersten Erstellen der Umgebung konfiguriert. Der Standardwert beträgt in der Regel 1 G. Der letzte Wert wird automatisch erweitert.

[root@db02 Welt]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:100M:autoextend

Starten Sie den Dienst neu, um die aktuellen Einstellungen für den gemeinsam genutzten Tablespace anzuzeigen

mysql> Variablen wie „innodb_data_file_path“ anzeigen;
+----------------------+-------------------------------------+
| Variablenname | Wert |
+----------------------+-------------------------------------+
| innodb_data_file_path | ibdata1:76M;ibdata2:100M:autoextend |
+----------------------+-------------------------------------+
Zeile im Satz (0,00 Sek.)

1.3.6 Einrichten eines unabhängigen Tablespace

Unabhängige Tablespaces sind in Version 5.6 standardmäßig aktiviert.

Hinweise zu unabhängigen Tablespaces: Wenn keine unabhängigen Tablespaces geöffnet werden, nimmt der gemeinsam genutzte Tablespace einen großen Bereich ein.

mysql> Variablen wie „%per_table%“ anzeigen;
+--------------------------+----------+
| Variablenname | Wert |
+--------------------------+----------+
| innodb_file_per_table | EIN |
+--------------------------+----------+
Zeile im Satz (0,00 Sek.)

Unabhängige Tablespaces können in der Parameterdatei /etc/my.cnf gesteuert werden

Unabhängigen Tablespace schließen (0 ist geschlossen, 1 ist geöffnet)

[root@db02 clsn]# vim /etc/my.cnf
[mysqld]
innodb_file_per_table=0

Anzeigen der Konfiguration unabhängiger Tabellenbereiche

mysql> Variablen wie „%per_table%“ anzeigen;
+--------------------------+----------+
| Variablenname | Wert |
+--------------------------+----------+
| innodb_file_per_table | AUS |
+--------------------------+----------+
Zeile im Satz (0,00 Sek.)

Zusammenfassung:

innodb_file_per_table=0 Unabhängigen Tablespace schließen innodb_file_per_table=1 Unabhängigen Tablespace öffnen, Einzeltabellenspeicher

1.4 Transaktionen in MySQL

Eine Reihe von Ausführungsschritten zur Datenmanipulation, die als Arbeitseinheit betrachtet werden

Wird zum Gruppieren mehrerer Anweisungen verwendet und kann verwendet werden, wenn mehrere Clients gleichzeitig auf Daten in derselben Tabelle zugreifen.

Alle Schritte sind erfolgreich oder schlagen fehl

Sind alle Schritte in Ordnung, werden diese ausgeführt, sind Schritte fehlerhaft oder unvollständig, werden sie abgebrochen.

Einfach ausgedrückt soll eine Transaktion sicherstellen, dass die Anweisungen in einer Arbeitseinheit gleichzeitig erfolgreich sind oder fehlschlagen.

Flussdiagramm der Transaktionsverarbeitung

1.4.1 Was ist eine Transaktion?

Anstatt Transaktionen zu definieren, ist es besser, über die Merkmale von Transaktionen zu sprechen. Wie wir alle wissen, müssen Transaktionen die vier ACID-Eigenschaften erfüllen.

A (Atomizität) Atomizität.

Die Ausführung einer Transaktion wird als unteilbare Kleinsteinheit betrachtet. Die Vorgänge einer Transaktion müssen entweder vollständig erfolgreich ausgeführt oder bei einem Fehler rückgängig gemacht werden. Eine nur teilweise Ausführung ist nicht möglich.

Alle Anweisungen werden erfolgreich ausgeführt oder alle werden gemeinsam abgebrochen.
Update t1 setze Geld = 10000-17, wobei ID = wxid1
Update t1 setze Geld=10000+17, wobei ID=wxid2

C (Konsistenz) Konsistenz.

Die Ausführung einer Transaktion sollte die Integritätsbeschränkungen der Datenbank nicht verletzen. Wenn das System nach der Ausführung der zweiten Operation im obigen Beispiel abstürzt, ist garantiert, dass sich der Gesamtbetrag von A und B nicht ändert.

Wenn sich die Datenbank zu Beginn einer Transaktion in einem konsistenten Zustand befindet, bleibt sie während der Ausführung dieser Transaktion in einem konsistenten Zustand.
 Update t1 setze Geld = 10000-17, wobei ID = wxid1
 Update t1 setze Geld=10000+17, wobei ID=wxid2
 Überprüfen Sie während des obigen Vorgangs Ihr Konto und es ist immer noch 10000

Ich (Isolation) Isolation.

Im Allgemeinen sollte sich das Verhalten der Transaktionen nicht gegenseitig beeinflussen. In der Realität wird das Ausmaß der gegenseitigen Beeinflussung von Transaktionen jedoch durch die Isolationsebene beeinflusst. Dies wird später im Artikel ausführlich besprochen.

Die Transaktionen beeinflussen sich gegenseitig nicht. Bei der Durchführung von Vorgängen können andere Personen beliebige Vorgänge auf diesen beiden Konten unter unterschiedlichen Isolationsbedingungen durchführen, und die Konsistenzgarantien können unterschiedlich sein.

Isolationsstufe

Die Isolationsebene beeinflusst die Konsistenz.
 Lesen/Aufheben der Festschreibung X
 read-commit Eine Ebene, die verwendet werden kann repeatable-read Die Standardebene, dieselbe wie Oracle SERIALIZABLE Strikter Standard, im Allgemeinen nicht verwendet

Diese Regel wird nicht nur durch die Isolationsebene gesteuert, sondern auch durch Sperren. Sie können an die Implementierung von NFS denken.

D (Haltbarkeit) Haltbarkeit.

Nachdem die Transaktion festgeschrieben wurde, muss sie auf der Festplatte gespeichert werden. Auch bei einem Systemabsturz sollen die übermittelten Daten nicht verloren gehen.

Nur wenn die Daten übermittelt werden, kann die Transaktion wirklich sicher sein

1.4.2 Transaktionskontrollanweisungen

Häufig verwendete Transaktionssteuerungsanweisungen:

START TRANSACTION (oder BEGIN): explizit eine neue Transaktion starten COMMIT: Änderungen der aktuellen Transaktion dauerhaft aufzeichnen (Transaktion erfolgreich beendet)
 ROLLBACK: Die durch die aktuelle Transaktion vorgenommenen Änderungen rückgängig machen (Transaktion schlägt fehl)

Transaktionskontrollanweisungen, die Sie kennen müssen:

 SAVEPOINT: Weist einer Transaktion einen Speicherort für zukünftige Referenzzwecke zu. ROLLBACK TO SAVEPOINT: Macht Änderungen rückgängig, die nach einem Savepoint vorgenommen wurden. RELEASE SAVEPOINT: Entfernt eine Savepoint-Kennung. SET AUTOCOMMIT: Deaktiviert oder aktiviert den Standard-Autocommit-Modus für die aktuelle Verbindung.

1.4.3 Autocommit-Parameter

Ab MySQL 5.5 ist die Anweisung „begin“ oder „start transaction“ nicht mehr erforderlich, um eine Transaktion zu starten. Außerdem ist der Autocommit-Modus standardmäßig aktiviert, wodurch jede Anweisung implizit als Transaktion festgeschrieben wird.

In manchen hektischen Geschäftsszenarien kann diese Konfiguration erhebliche Auswirkungen auf die Leistung haben, verbessert die Sicherheit jedoch erheblich. In Zukunft müssen wir unsere Geschäftsanforderungen abwägen, um die automatische Übermittlung zu bestimmen.

Hinweis: Wählen Sie in der Produktion je nach tatsächlichem Bedarf aus, ob es aktiviert werden soll. Im Allgemeinen werden Bankdienstleistungen geschlossen.

Zeigen Sie den aktuellen Autocommit-Status an:

mysql> Variablen wie „%autoc%“ anzeigen;
+---------------+-------+
| Variablenname | Wert |
+---------------+-------+
| Autocommit | EIN |
+---------------+-------+
Zeile im Satz (0,00 Sek.)

Ändern Sie die Konfigurationsdatei und starten Sie neu

[root@db02 Welt]# vim /etc/my.cnf
[mysqld]
automatisches Commit = 0

Überprüfen Sie den Autocommit-Status erneut

mysql> Variablen wie „%autoc%“ anzeigen;
+---------------+-------+
| Variablenname | Wert |
+---------------+-------+
| Autocommit | AUS |
+---------------+-------+
Zeile im Satz (0,00 Sek.)
mysql> wählen Sie @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
Zeile im Satz (0,00 Sek.)

Hinweis: Vergleich mit aktiviertem Autocommit

Vorteile: Gute Datensicherheit, jede Änderung wird übernommen

Nachteile: Kann keine Bankgeschäfte durchführen, erzeugt eine große Anzahl kleiner IOs

1.4.4 Nicht-transaktionale Anweisungen, die ein Commit verursachen:

DDL-Anweisungen: (ALTER, CREATE und DROP)
DCL-Anweisungen: (GRANT, REVOKE und SET PASSWORD)
Sperranweisungen: (LOCK TABLES und UNLOCK TABLES)

Beispiele für Anweisungen, die ein implizites Commit verursachen:

TRUNCATE-TABELLE
DATEN INDATEI LADEN
ZUM UPDATE AUSWÄHLEN

SQL-Anweisung für implizites Commit:

TRANSAKTION STARTEN
AUTOCOMMIT festlegen = 1

1.5 Wiederherstellen und Rückgängigmachen

1.5.1 Rückgängigmachen des Transaktionsprotokolls

Undo-Prinzip:

Das Prinzip des Undo-Logs ist sehr einfach. Um die Atomizität von Transaktionen zu erfüllen, müssen die Daten vor der Verarbeitung an einem Ort gesichert werden (der Ort, an dem die Datensicherung gespeichert wird, wird Undo-Log genannt). Ändern Sie anschließend die Daten.

Wenn ein Fehler auftritt oder der Benutzer eine ROLLBACK-Anweisung ausführt, kann das System die Sicherung im Undo-Protokoll verwenden, um die Daten in den Zustand vor Beginn der Transaktion wiederherzustellen.

Zusätzlich zur Sicherstellung der Atomarität von Transaktionen kann Undo Log auch zur Unterstützung bei der Vervollständigung der Transaktionspersistenz verwendet werden.

Was bedeutet Undo ?

Undo, wie der Name schon sagt, ist „Rollback-Protokoll“ eine Art Transaktionsprotokoll.

Was ist die Funktion?

Im Transaktions-ACID-Prozess wird die Rolle der „A“-Atomizität realisiert.

Vereinfachen Sie die Implementierung atomarer und persistenter Transaktionen mit Undo Log

Angenommen, es gibt zwei Daten, A und B, mit den Werten 1 bzw. 2.
 A.Transaktion beginnt.
 B. Zeichnen Sie A=1 auf, um das Protokoll rückgängig zu machen.
 C. Ändern Sie A=3.
 D. Notieren Sie B=2 im Rückgängig-Protokoll.
 E. Ändern Sie B=4.
 F. Schreiben Sie das Undo-Protokoll auf die Festplatte.
 G. Schreiben Sie die Daten auf die Festplatte.
 H. Transaktions-Commit

Hier gibt es eine implizite Prämisse: „Die Daten werden zuerst in den Speicher gelesen, dann im Speicher geändert und schließlich wieder auf die Festplatte geschrieben. Der Grund, warum Atomizität und Persistenz gleichzeitig garantiert werden können, liegt in den folgenden Merkmalen:

A. Notieren Sie das Undo-Protokoll, bevor Sie Daten aktualisieren.
B. Um die Dauerhaftigkeit zu gewährleisten, müssen die Daten vor dem Festschreiben der Transaktion auf die Festplatte geschrieben werden. Solange die Transaktion erfolgreich abgeschlossen wurde, müssen die Daten bestehen geblieben sein.
C. Das Undo-Protokoll muss vor den Daten auf der Festplatte gespeichert werden. Wenn das System zwischen G und H abstürzt, ist das Undo-Protokoll intakt und kann zum Zurücksetzen der Transaktion verwendet werden.
D. Wenn das System zwischen AFs abstürzt, weil die Daten nicht auf der Festplatte gespeichert sind. Die Daten auf der Festplatte verbleiben also im Zustand vor Beginn der Transaktion.

Defekt:

Vor dem Festschreiben jeder Transaktion werden Daten und Undo-Protokoll auf die Festplatte geschrieben, was zu einer großen Menge an Festplatten-E/A und damit zu einer sehr geringen Leistung führt. Wenn Daten für einen bestimmten Zeitraum zwischengespeichert werden können, lässt sich die E/A reduzieren und die Leistung verbessern. Dadurch geht jedoch die Dauerhaftigkeit der Transaktion verloren.

Daher wird ein anderer Mechanismus zur Erreichung der Persistenz eingeführt, nämlich Redo Log.

1.5.2 Transaktionsprotokoll wiederholen

Redo-Prinzip:

Im Gegensatz zum Undo-Log zeichnet das Redo-Log die Sicherung neuer Daten auf. Bevor eine Transaktion festgeschrieben wird, reicht es aus, das Redo-Protokoll dauerhaft zu speichern. Es besteht keine Notwendigkeit, die Daten dauerhaft zu speichern. Bei einem Systemabsturz bleiben zwar die Daten erhalten, das Redo-Protokoll bleibt jedoch erhalten.

Anhand des Inhalts des Redo-Logs kann das System alle Daten auf den neuesten Stand wiederherstellen.

Was ist Redo ?

Redo, wie der Name schon sagt, „Redo-Log“ ist eine Art Transaktionsprotokoll.

Was ist die Funktion?

Im Transaktions-ACID-Prozess wird die Rolle der „D“-Persistenz realisiert.

Vereinfachter Prozess zum Rückgängigmachen und Wiederherstellen von Transaktionen

Angenommen, es gibt zwei Daten, A und B, mit den Werten 1 bzw. 2.
 A.Transaktion beginnt.
 B. Zeichnen Sie A=1 auf, um das Protokoll rückgängig zu machen.
 C. Ändern Sie A=3.
 D. Notieren Sie A=3 im Redo-Protokoll.
 E. Notieren Sie B=2 im Rückgängig-Protokoll.
 F. Ändern Sie B=4.
 G. Notieren Sie B=4 im Redo-Protokoll.
 H. Schreiben Sie das Redo-Protokoll auf die Festplatte.
 I. Transaktions-Commit

Merkmale von Undo + Redo-Transaktionen

 A. Um die Dauerhaftigkeit sicherzustellen, muss das Redo-Protokoll gespeichert werden, bevor die Transaktion festgeschrieben wird.
 B. Daten müssen nicht auf die Festplatte geschrieben werden, bevor eine Transaktion ausgeführt wird, sondern werden im Speicher zwischengespeichert.
 C. Redo Log stellt die Dauerhaftigkeit von Transaktionen sicher.
 D. Undo Log stellt die Atomarität von Transaktionen sicher.
 E. Es gibt eine implizite Eigenschaft, dass Daten nach dem Redo-Protokoll in den permanenten Speicher geschrieben werden müssen.

Ob Redo auf der Festplatte gespeichert wird

innodb_flush_log_at_trx_commit=1/0/2

1.5.3 Sperren in Transaktionen

Was ist ein „Schloss“?

„Lock“ bedeutet, wie der Name schon sagt, verriegeln.

Welche Funktion hat ein „Schloss“?

Im ACID-Prozess von Transaktionen arbeiten „Sperren“ und „Isolationsebenen“ zusammen, um die Rolle der „I“-Isolation zu erreichen.

Sperrgranularität:

1. MyIasm: Geringe Parallelitätssperre - Sperre auf Tabellenebene

2. Innodb: Hohe Parallelitätssperre - Sperre auf Zeilenebene

Vier Isolationsebenen:

READ UNCOMMITTED ermöglicht Transaktionen, nicht festgeschriebene Änderungen anderer Transaktionen anzuzeigen. READ COMMITTED ermöglicht Transaktionen, festgeschriebene Änderungen anderer Transaktionen anzuzeigen. REPEATABLE READ****** stellt sicher, dass die SELECT-Ausgabe jeder Transaktion konsistent ist; InnoDBs Standardebene SERIALIZABLE isoliert die Ergebnisse einer Transaktion vollständig von anderen Transaktionen.

Overhead, Sperrgeschwindigkeit, Deadlock, Granularität und Parallelitätsleistung

Sperre auf Tabellenebene: geringer Overhead, schnelle Sperrung, kein Deadlock, hohe Sperrgranularität, höchste Wahrscheinlichkeit eines Sperrkonflikts und geringste Parallelität.
Sperre auf Zeilenebene: Hoher Overhead, langsame Sperrung; es kann zu Deadlocks kommen; die Sperrgranularität ist am geringsten, die Wahrscheinlichkeit eines Sperrkonflikts am geringsten und die Parallelität am höchsten.
Seitensperre: Der Overhead und die Sperrzeit liegen zwischen der Tabellensperre und der Zeilensperre. Es können Deadlocks auftreten. Die Sperrgranularität liegt zwischen der Tabellensperre und der Zeilensperre und die Parallelität ist durchschnittlich.

Aus den oben genannten Merkmalen ist ersichtlich, dass es schwierig ist, allgemein zu sagen, welches Schloss besser ist. Wir können nur anhand der Merkmale der spezifischen Anwendung sagen, welches Schloss besser geeignet ist!

Rein aus der Perspektive der Sperren: Sperren auf Tabellenebene eignen sich eher für abfrageorientierte Anwendungen, die nur eine kleine Datenmenge entsprechend den Indexbedingungen aktualisieren, wie etwa Webanwendungen; Sperren auf Zeilenebene eignen sich dagegen eher für Anwendungen, bei denen eine große Anzahl kleiner Mengen unterschiedlicher Daten entsprechend den Indexbedingungen und gleichzeitigen Abfragen gleichzeitig aktualisiert wird, wie etwa einige Systeme zur Online-Transaktionsverarbeitung (OLTP).

1.6 MySQL-Protokollverwaltung

1.6.1 Einführung in MySQL-Protokolltypen

Beschreibung der Protokolltypen:

1.6.2 Konfigurationsmethode

Status-Fehlerprotokoll:

[mysqld]
log-error=/data/mysql/mysql.log

Sehen Sie sich die Konfigurationsmethode an:

mysql> Variablen wie „%log%error%“ anzeigen;

Wirkung:

Die Erfassung allgemeiner Statusinformationen und Fehlerinformationen der MySQL-Datenbank ist unser

Allgemeine Protokolle zur allgemeinen Datenbankfehlerverarbeitung.

mysql> Variablen wie „%log%err%“ anzeigen;
+---------------------+----------------------------------+
| Variablenname | Wert |
+---------------------+----------------------------------+
| binlog_error_action | IGNORE_ERROR |
| log_error | /anwendung/mysql/data/db02.err |
+---------------------+----------------------------------+
Zeilen im Set (0,00 Sek.)

1.6.3 Allgemeines Abfrageprotokoll

Konfigurationsmethode:

[mysqld]
general_log=ein
general_log_file=/data/mysql/server2.log

Sehen Sie sich die Konfigurationsmethode an:

Variablen wie „%gen%“ anzeigen;

Wirkung:

Zeichnen Sie zu Prüfzwecken alle erfolgreich ausgeführten SQL-Anweisungen in MySQL auf. Wir aktivieren dies jedoch selten.

mysql> Variablen wie „%gen%“ anzeigen;
+------------------+----------------------------------+
| Variablenname | Wert |
+------------------+----------------------------------+
| general_log | AUS |
| allgemeine_Protokolldatei | /application/mysql/data/db02.log |
+------------------+----------------------------------+
Zeilen im Set (0,00 Sek.)

1.7 Binäres Logging

Das Binärprotokoll ist nicht von der Speicher-Engine abhängig.

Hängt von der SQL-Schicht ab und zeichnet Informationen zu SQL-Anweisungen auf

Binlog-Protokollfunktion:

1. Backup-Funktion bereitstellen

2. Führen Sie eine Master-Slave-Replikation durch

3. Jede Wiederherstellung basierend auf dem Zeitpunkt

Notieren Sie die Anweisungen, die in der SQL-Schicht ausgeführt wurden. Wenn es sich um eine Transaktion handelt, notieren Sie die abgeschlossene Transaktion.

Funktion: Point-in-Time Backup und Point-in-Time Recovery, Master-Slave

Das „Haupttor“ des Binärlogs

Wirkung:

1. Ob aktiviert werden soll 2. Binärprotokollpfad /data/mysql/
3. Präfix für den Namen der Binärprotokolldatei mysql-bin 
4. Der Dateiname beginnt mit "prefix".000001~N
log-bin=/Daten/mysql/mysql-bin

Binärlog "Schalter":

Dies ist nur sinnvoll, wenn der Hauptschalter eingeschaltet ist und standardmäßig eingeschaltet ist.
Wir schließen zeitweise vorübergehend.
Betrifft nur die aktuelle Sitzung.
sql_log_bin=1/0

1.7.1 Binäres Protokollformat

Anweisung, Anweisungsmodus:

Die aufgezeichneten Informationen sind präzise und es wird nur die SQL-Anweisung selbst aufgezeichnet. Wenn in der Anweisung jedoch Funktionsoperationen vorkommen, können die aufgezeichneten Daten ungenau sein.

Dies ist der Standardmodus in 5.6, sollte aber in Produktionsumgebungen mit Vorsicht verwendet werden. Es wird empfohlen, ihn in „row“ zu ändern.

Zeile, Zeilenmodus

Der Vorgang der Änderung von Zeilendaten in einer Tabelle.
Die aufgezeichneten Daten sind detailliert und stellen hohe Anforderungen an die IO-Leistung. Die aufgezeichneten Daten sind unter allen Umständen genau.
Dies ist im Allgemeinen der Modus in der Produktion.
Der Standardmodus nach 5.7.

gemischt, gemischter Modus

Nach der Beurteilung wird ein gemischter Zeilen- und Anweisungsaufzeichnungsmodus ausgewählt. (Im Allgemeinen nicht verwendet)

1.7.2 Binäres Logging aktivieren

mysql> Variablen wie „%log_bin%“ anzeigen;
+---------------------------------+--------+
| Variablenname | Wert |
+---------------------------------+--------+
| log_bin | AUS |
| log_bin_Basisname | |
| log_bin_index | |
| log_bin_trust_function_creators | AUS |
| log_bin_use_v1_row_events | AUS |
| sql_log_bin | EIN |
+---------------------------------+--------+
Zeilen im Set (0,00 Sek.)

Ändern Sie die Konfigurationsdatei, um die binäre Protokollierung zu aktivieren

[root@db02 tmp]# vim /etc/my.cnf
[mysqld]
log-bin=/Anwendung/mysql/Daten/mysql-bin

Befehlszeilenänderungsmethode

mysql> SET GLOBAL binlog_format = 'ANWEISUNG'
mysql> GLOBAL SETZEN binlog_format = 'ZEILE';
mysql> SET GLOBAL binlog_format = "GEMISCHT";

Zeigen Sie den Typ der binären Protokolldatei an

[root@db02 Daten]# Datei mysql-bin.*
mysql-bin.000001: MySQL-Replikationsprotokoll
mysql-bin.index: ASCII-Text

Zeigen Sie die MySQL-Konfiguration an:

mysql> Variablen wie „%log_bin%“ anzeigen;
+---------------------------------+-----------------------------------------+
| Variablenname | Wert |
+---------------------------------+-----------------------------------------+
| log_bin | EIN |
| log_bin_basename | /Anwendung/mysql/Daten/mysql-bin |
| log_bin_index | /Anwendung/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | AUS |
| log_bin_use_v1_row_events | AUS |
| sql_log_bin | EIN |
+---------------------------------+-----------------------------------------+
Zeilen im Set (0,00 Sek.)

1.7.3 Festlegen der Aufzeichnungsmethode

Aktuelles Format anzeigen

mysql> Variablen wie „%format%“ anzeigen;
+--------------------------+---------------------+
| Variablenname | Wert |
+--------------------------+---------------------+
| binlog_format | ANWEISUNG |
| Datumsformat | %Y-%m-%d |
| Datums-/Uhrzeitformat | %Y-%m-%d %H:%i:%s |
| Standardwochenformat | 0 |
| innodb_Dateiformat | Antilope |
| innodb_file_format_check | EIN |
| innodb_file_format_max | Antilope |
| Zeitformat | %H:%i:%s |
+--------------------------+---------------------+
Zeilen im Set (0,00 Sek.)

Ändern des Formats

[root@db02 Daten]# vim /etc/my.cnf
[mysqld]
binlog_format=Zeile

Kontrolle nach Änderung

mysql> Variablen wie „%format%“ anzeigen;
+--------------------------+---------------------+
| Variablenname | Wert |
+--------------------------+---------------------+
| binlog_format | REIHE |
| Datumsformat | %Y-%m-%d |
| Datums-/Uhrzeitformat | %Y-%m-%d %H:%i:%s |
| Standardwochenformat | 0 |
| innodb_Dateiformat | Antilope |
| innodb_file_format_check | EIN |
| innodb_file_format_max | Antilope |
| Zeitformat | %H:%i:%s |
+--------------------------+---------------------+
Zeilen im Set (0,00 Sek.)

1.8 Binärprotokolloperation

1.8.1 Ansicht

Ansicht auf Betriebssystemebene

[root@db02-Daten]# ll mysql-bin.*
-rw-rw---- 1 mysql mysql 143 20. Dez. 20:17 mysql-bin.000001
-rw-rw---- 1 mysql mysql 120 20. Dez. 20:17 mysql-bin.000002
-rw-rw---- 1 mysql mysql 82 Dez 20 20:17 mysql-bin.index

Protokoll aktualisieren

mysql> Protokolle leeren;

Protokollverzeichnis nach Abschluss der Aktualisierung

[root@db02-Daten]# ll mysql-bin.*
-rw-rw---- 1 mysql mysql 143 20. Dez. 20:17 mysql-bin.000001
-rw-rw---- 1 mysql mysql 167 20. Dez. 20:24 mysql-bin.000002
-rw-rw---- 1 mysql mysql 120 20. Dez. 20:24 mysql-bin.000003
-rw-rw---- 1 mysql mysql 123 Dez 20 20:24 mysql-bin.index
[root@db02 Daten]#

Zeigen Sie die aktuell verwendete Binärprotokolldatei an.

mysql> Masterstatus anzeigen;
+------------------+----------+--------------+------------------+-------------------+
| Datei | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
Zeile im Satz (0,00 Sek.)

Alle binären Protokolldateien anzeigen

mysql> Binärprotokolle anzeigen;
+------------------+------------+
| Protokollname | Dateigröße |
+------------------+------------+
|mysql-bin.000001 | 143 |
|mysql-bin.000002 | 167 |
| mysql-bin.000003 | 120 |
+------------------+------------+
Zeilen im Set (0,00 Sek.)

1.8.2 Anzeigen des Binärprotokollinhalts

Glossar:

1. Veranstaltungen

So definieren Sie ein Binärprotokoll: die kleinste Einheit des Befehlsauftretens

2. Stellung

Die Positionsnummer, der jedes Ereignis in der gesamten Binärdatei entspricht, ist die Positionsnummer.

mysql> Masterstatus anzeigen;
+------------------+----------+--------------+------------------+-------------------+
| Datei | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
Zeile im Satz (0,00 Sek.)
[root@db02 Daten]# mysqlbinlog mysql-bin.000003 >/tmp/aa.ttt

Alle Informationen exportieren

[root@db02 Daten]# mysqlbinlog mysql-bin.000003 >/tmp/aa.ttt

So zeigen Sie das Binärprotokoll an:

1. Zeigen Sie die ursprünglichen Binlog-Informationen an

mysqbin mysql-bin.000002

2. Im Zeilenmodus in Anweisungen übersetzen

mysqlbinlog --base64-output='Zeilen dekodieren' -v mysql-bin.000002

3. Binlog-Ereignisse anzeigen

Binärprotokolle anzeigen; alle verwendeten Binärprotokollinformationen; Binärprotokollereignisse in „Protokolldatei“ anzeigen

4. So fangen Sie Binlog-Inhalte ab und stellen sie bei Bedarf wieder her (konventionelle Ideen)

(1) Binärprotokolle anzeigen; Masterstatus anzeigen;

(2) Zeigen Sie Binlog-Ereignisse in „Vom Ende bis zum Anfang lesen“ an, finden Sie die Transaktion mit der falschen Operation und bestimmen Sie die Start- und Endpositionen der Transaktion.

(3) Eliminieren Sie die fehlerhaften Operationen und belassen Sie die normalen Operationen in zwei SQL-Dateien

(4) Stellen Sie zunächst die Testdatenbank wieder her, exportieren Sie die durch den fehlerhaften Vorgang verursachten Daten und nehmen Sie dann die Produktion wieder auf.

Bei der Verwendung der obigen Methode sind folgende Probleme aufgetreten:

Längere Wiederherstellungsereignisse

Dies hat einen gewissen Einfluss auf die Produktionsdaten und es können redundante Daten auftreten

Bessere Lösung.

1. Flashback-Funktion

2. Verzögerung aus der Datenbank durch Backup

1.8.3 So fangen Sie Binärprotokolle mit mysqlbinlog ab

Die allgemeinen Optionen für mysqlbinlog sind wie folgt:

1.8.4 Binärprotokolle löschen

Setzen Sie GLOBAL exit_logs_days = 7;
oder BINÄRPROTOKOLLE VOR jetzt() LÖSCHEN – INTERVALL 3 Tage;

Protokolle entsprechend dem Dateinamen löschen:

BINÄRPROTOKOLLE NACH „mysql-bin.000010“ LÖSCHEN;

Setzen Sie die Anzahl der Binärprotokolle zurück, beginnen Sie bei 1 zu zählen und löschen Sie das ursprüngliche Binärprotokoll.

Master zurücksetzen

1.9 MySQL-Protokoll für langsame Abfragen (langsames Protokoll)

1.9.1 Was ist das?

slow-log zeichnet alle langsamen SQL-Anweisungen unter den Bedingungen auf

Ein Tool-Log zur Optimierung. Kann uns helfen, das Problem zu lokalisieren.

1.9.2 Langsames Abfrageprotokoll

Es zeichnet die relevanten SQL-Anweisungen, die die Datenbankleistung im MySQL-Server beeinflussen, in der Protokolldatei auf.

Durch die Analyse und Verbesserung dieser speziellen SQL-Anweisungen kann das Ziel einer Verbesserung der Datenbankleistung erreicht werden. Langsame Protokolleinstellungen

long_query_time: Legen Sie den Schwellenwert für langsame Abfragen fest. SQL, das den festgelegten Wert überschreitet, wird im Protokoll für langsame Abfragen aufgezeichnet. Der Standardwert beträgt 10 Sekunden.
slow_query_log: Gibt an, ob das langsame Abfrageprotokoll aktiviert werden soll slow_query_log_file: Gibt den Speicherort an, an dem die langsame Protokolldatei gespeichert wird. Sie kann leer sein und das System gibt eine Standarddatei host_name-slow.log aus
min_examined_row_limit: SQL, das weniger Zeilen zurückgibt als der durch die Abfrageprüfung angegebene Parameter, wird nicht im langsamen Abfrageprotokoll aufgezeichnet log_queries_not_using_indexes: Ob das langsame Abfrageprotokoll, das den Index nicht verwendet, im Index aufgezeichnet wird

Konfiguration des langsamen Abfrageprotokolls

[root@db02 htdocs]# vim /etc/my.cnf
slow_query_log=EIN
slow_query_log_file=/tmp/slow.log
long_query_time=0.5 # Kontrollieren Sie den Schwellenwert langsamer Protokolldatensätze log_queries_not_using_indexes

Nachdem die Konfiguration abgeschlossen ist, starten Sie den Dienst neu …

Überprüfen Sie, ob das Protokoll für langsame Abfragen aktiviert ist und wo es sich befindet.

mysql> Variablen wie '%slow%' anzeigen
 -> ;
+-----------------------------+------------------+
| Variablenname | Wert |
+-----------------------------+------------------+
| log_slow_admin_statements | AUS |
| log_slow_slave_statements | AUS |
| langsame Startzeit | 2 |
| slow_query_log | EIN |
| slow_query_log_datei | /tmp/slow.log |
+-----------------------------+------------------+
Zeilen im Set (0,00 Sek.)

1.9.3 mysqldumpslow-Befehl

/Pfad/mysqldumpslow -sc -t 10 /Datenbank/mysql/slow-log

Dadurch werden die 10 wichtigsten SQL-Anweisungen mit den meisten Datensätzen ausgegeben. Dabei gilt:

1.9.4 So stellen Sie die Konsistenz festgeschriebener Transaktionen in Binlog und Redolog sicher

mysql> Variablen wie „%sync_binlog%“ anzeigen;
+---------------+-------+
| Variablenname | Wert |
+---------------+-------+
| sync_binlog | 0 | #Commit-Phase des Binlogs steuern+---------------+----------+
Zeile im Satz (0,00 Sek.)

sync_binlog stellt sicher, dass jede festgeschriebene Transaktion in das Binärprotokoll geschrieben wird.

1.9.5 Double One-Standard in MySQL:

Die beiden Parameter innodb_flush_log_at_trx_commit und sync_binlog sind Schlüsselparameter zur Steuerung der MySQL-Festplattenschreibstrategie und Datensicherheit.

Bedeutung der Parameter:

innodb_flush_log_at_trx_commit = 1

Wenn innodb_flush_log_at_trx_commit auf 0 gesetzt ist, wird der Protokollpuffer einmal pro Sekunde in die Protokolldatei geschrieben und gleichzeitig der Leerungsvorgang der Protokolldatei ausgeführt. In diesem Modus wird der Schreibvorgang auf die Festplatte nicht aktiv ausgelöst, wenn die Transaktion festgeschrieben wird.

Wenn innodb_flush_log_at_trx_commit auf 1 gesetzt ist, schreibt MySQL die Daten im Protokollpuffer in die Protokolldatei und schreibt sie bei jedem Commit einer Transaktion auf die Festplatte.

Wenn innodb_flush_log_at_trx_commit auf 2 gesetzt ist, schreibt MySQL bei jedem Commit einer Transaktion die Daten im Protokollpuffer in die Protokolldatei. Der Flush-Vorgang wird jedoch nicht gleichzeitig ausgeführt. In diesem Modus führt MySQL einmal pro Sekunde einen Flush-Vorgang durch.

Beachten:

Aufgrund der Prozessplanungsstrategie kann nicht garantiert werden, dass dieser „Spülvorgang einmal pro Sekunde ausgeführt wird“ zu 100 % „pro Sekunde“ erfolgt.

Bedeutung der Parameter:

sync_binlog=1

Der Standardwert von sync_binlog ist 0. Wie der Mechanismus des Betriebssystems zum Aktualisieren anderer Dateien führt MySQL keine Synchronisierung mit der Festplatte durch, sondern verlässt sich beim Aktualisieren des Binärprotokolls auf das Betriebssystem.

Wenn sync_binlog = N (N>0), verwendet MySQL die Funktion fdatasync(), um sein Binärprotokoll jedes Mal mit der Festplatte zu synchronisieren, wenn es das Binärprotokoll N-mal schreibt.

Notiz:

Wenn Autocommit aktiviert ist, gibt es einen Schreibvorgang pro Anweisung, andernfalls gibt es einen Schreibvorgang pro Transaktion.

Sicherheitshinweise

Wenn innodb_flush_log_at_trx_commit und sync_binlog beide 1 sind, ist es am sichersten. Im Falle eines Absturzes des mysqld-Dienstes oder eines Serverhosts kann das Binärprotokoll maximal eine Anweisung oder eine Transaktion verlieren. Allerdings kann man nicht alles haben und gleichzeitig alles essen. Double 11 führt zu häufigen IO-Operationen, daher ist dieser Modus auch der langsamste Weg.

Wenn innodb_flush_log_at_trx_commit auf 0 gesetzt ist, führt ein Absturz des mysqld-Prozesses in der letzten Sekunde zum Verlust aller Transaktionsdaten.

Wenn innodb_flush_log_at_trx_commit auf 2 gesetzt ist, können alle Transaktionsdaten in der letzten Sekunde nur verloren gehen, wenn das Betriebssystem abstürzt oder das System keinen Strom mehr hat.

Double 1 eignet sich für Unternehmen mit sehr hohen Anforderungen an die Datensicherheit und ausreichender Festplatten-E/A-Schreibkapazität zur Unterstützung von Diensten wie Bestell-, Transaktions-, Auflade- und Zahlungsverbrauchssystemen. Im Doppel-1-Modus, wenn die Festplatten-E/A die Geschäftsanforderungen nicht erfüllen kann, wie z. B. aufgrund des Drucks des 11.11-Ereignisses. Der empfohlene Ansatz besteht darin, innodb_flush_log_at_trx_commit=2, sync_binlog=N (N ist 500 oder 1000) festzulegen und einen Cache mit einer Batterie-Notstromversorgung zu verwenden, um Stromausfälle des Systems zu verhindern.

Systemleistung und Datensicherheit sind notwendige Faktoren für eine hohe Verfügbarkeit und Stabilität von Geschäftssystemen. Wir müssen einen Gleichgewichtspunkt für die Systemoptimierung finden. Der richtige ist der beste. Je nach den Anforderungen verschiedener Geschäftsszenarien können wir die beiden Parameter kombinieren und anpassen, um die Leistung des Datenbanksystems zu optimieren.

Die obige auf MySQL basierende Speicher-Engine und Protokollbeschreibung (umfassende Erklärung) ist der gesamte Inhalt, den der Herausgeber mit Ihnen teilt. Ich hoffe, dass er Ihnen als Referenz dienen kann. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden.