Zusammenfassung der schnellen Spaltenaddition bei MySQL 8.0 Online DDL

Problembeschreibung

Vor ein paar Tagen stellte mir ein Kollege eine Frage: Aus welchem ​​Grund migriert Unternehmen A von MySQL zu MongoDB?

Ehrlich gesagt ist diese Frage nicht leicht zu beantworten. Warum müssen wir migrieren? Wir müssen auf einen Engpass gestoßen sein, der an der Datenmenge oder dem Datentyp liegen kann. Also habe ich das Unternehmen konsultiert und schließlich die Antwort erhalten: Einige Tabellen in diesem Unternehmen müssen häufig Felder hinzufügen. Die Kosten für das Hinzufügen von Feldern in MongoDB betragen fast null, während die Kosten für das Hinzufügen von Feldern in niedrigeren Versionen von MySQL immer noch ziemlich hoch sind.

Was sind also die allgemein verwendeten Methoden zum Hinzufügen von MySQL-Feldern? Hier werde ich sie kurz auflisten:

1. Perconas pt-osc-Tool

2. GitHub Open Source-Projekt Gh-Ost-Tool

3. MySQL natives Online-DDL

Historische Methode zum Hinzufügen von Spalten in MySQL Online DDL

01 Kopiermethode

Methode zum Hinzufügen von Spalten in MySQL Version 5.5 und früher: Kopieren

Das Ausführungsdiagramm sieht wie folgt aus:

Wir haben eine Originaltabelle A, die nur ein Feld mit den Datensätzen 1, 2, 4 und 6 enthält. Wenn wir den Kopieralgorithmus zum Hinzufügen von Spalten verwenden:

1. Erstellen Sie eine neue Tabelle tmp-A, die 2 Felder enthält.

2. Anschließend kopieren wir alle Daten der Tabelle A zeilenweise in die neue Tabelle tmp-A.

3. Verwenden Sie dann die tmp-A-Tabelle und die A-Tabelle zum Austausch.

Auf diese Weise enthält unsere neue Tabelle 2 Felder. Beachten Sie auch, dass die Datensätze in der neuen Tabelle kompakter sind als in der ursprünglichen Tabelle. Die Originaltabelle weist möglicherweise in der Mitte ein Loch oder eine Speicherplatzfragmentierung auf, da die Datensätze 3 und 5 gelöscht wurden.

Wie Sie sehen, muss der Kopieralgorithmus die Daten einmal kopieren und benötigt zusätzlichen Speicherplatz zum Speichern der temporären Tabelle tmp-A. Darüber hinaus geht während des Datenkopiervorgangs der Schreibvorgang für Tabelle A verloren, d. h. während des Änderungstabellenvorgangs können in Tabelle A keine Daten aktualisiert werden. Dies kann ein fataler Fehler sein.

02 Inplace-Methode

MySQL 5.6 führte Online-DDL ein, wodurch der obige Prozess wie folgt geändert wurde:

Der Ablauf unterscheidet sich etwas vom obigen Kopieralgorithmus:

1. Während des Online-DDL-Prozesses wird der B+-Baum aus Tabelle A extrahiert und in einer Zwischendatei tmp-file anstelle der Zwischentabelle tmp-A gespeichert.

2. Während der Ausführung von Schritt 1 werden alle Schreibvorgänge in Tabelle A im Zeilenprotokoll aufgezeichnet

3. Nachdem Schritt 1 abgeschlossen ist, wenden Sie alle Zeilenprotokolle auf die temporäre Datei an, um eine Datendatei mit denselben Daten wie Tabelle A zu erhalten.

4. Verwenden Sie die Datendatei tmp-file, um die Datendatei in Tabelle A zu ersetzen.

Während dieses Vorgangs können aufgrund des vorhandenen Zeilenprotokolls während des gesamten Vorgangs Tabellen A hinzugefügt, gelöscht, geändert und überprüft werden, da diese Vorgänge nicht verloren gehen. Aus diesem Grund wird dieser Vorgang als Online-DDL bezeichnet.

Darüber hinaus muss hier erklärt werden, dass die im Kopieralgorithmus generierte temporäre Tabelle tmp-A auf Serverebene erstellt wird, während die tmp-Datei im obigen Online-DDL-Vorgang innerhalb der Plug-In-Speicher-Engine Innodb generiert wird. Wir nennen diesen in Innodb abgeschlossenen Änderungsvorgang Inplace (was auf Chinesisch „an Ort und Stelle“ bedeutet), was bedeutet, dass die Daten nicht in die „temporäre Tabelle auf Serverebene“ verschoben werden müssen.

Instant-Methode eingeführt in MySQL 8.0.12

MySQL Version 8.0.12 führte die Instant-Methode ein, die das Hinzufügen von Spalten erleichtert. Wenn der Instant-Algorithmus eine Spalte hinzufügt, muss nicht mehr die gesamte Tabelle neu erstellt werden. Stattdessen müssen nur die grundlegenden Informationen der neu hinzugefügten Spalte in den Tabellenmetadaten aufgezeichnet werden.

Schauen wir uns die Vorteile an. Zuerst erstellen wir eine Tabelle t1 und fügen 260.000 Datensätze ein. Dann fügen wir die Spalten col_1, col_2 und col_3 hinzu und geben die Algorithmen zum Hinzufügen von Spalten als Kopieren, Inplace und Instant an. Die Ergebnisse sind wie folgt:

 [Test] 23:42:45> wähle Anzahl(1) aus t1;
 +----------+
 | Anzahl(1) |
 +----------+
 |262144|
 +----------+
 1 Zeile im Satz (0,06 Sek.)
 
Lösung 1: Kopieren
[Test] 23:43:29> Tabelle t1 ändern, col_1 int hinzufügen, Algorithmus=Kopieren;  
Abfrage OK, 262144 Zeilen betroffen (1,48 Sek.)
Datensätze: 262144 Duplikate: 0 Warnungen: 0

Lösung 2: inplace
[Test] 23:43:46> Tabelle ändern, t1, Spalte_2 int hinzufügen, Algorithmus=inplace; 
Abfrage OK, 0 Zeilen betroffen (0,58 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

Lösung 3: sofort
[Test] 23:44:08> Tabelle t1 ändern, col_3 int hinzufügen, Algorithmus=Instant; 
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

m5480:[email protected] [test] 23:44:14> zeige Tabelle erstellen t1\G
*************************** 1. Reihe ***************************
       Tabelle: t1
Tabelle erstellen: CREATE TABLE `t1` (
  `id` int NICHT NULL AUTO_INCREMENT,
  `name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Alter` int DEFAULT NULL,
  `Punktzahl` int DEFAULT NULL,
  `col_1` int DEFAULT NULL,
  `col_2` int DEFAULT NULL,
  `col_3` int DEFAULT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_sco` (`Punktzahl`)
) ENGINE=InnoDB AUTO_INCREMENT=458730 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 Zeile im Satz (0,01 Sek.)

Aus den Ergebnissen lässt sich leicht erkennen, dass die Ausführungszeit wie folgt beträgt:

Kopieren > an Ort und Stelle > sofort

Gleichzeitig entspricht die Anzahl der vom Kopieralgorithmus betroffenen Zeilen der gesamten Tabelle, während die Anzahl der von den Inplace- und Instant-Algorithmen betroffenen Zeilen 0 beträgt, was darauf hinweist, dass es sich um Online-DDL-Operationen handelt.

Schließlich können wir die Informationen der Instant-Spalte auch mit der folgenden Methode anzeigen:

[test] 23:53:01> SELECT * FROM information_schema.innodb_tables, wobei der Name wie 'test/t1'\G ist
 *************************** 1. Reihe ***************************
      TABELLE_ID: 1079
          NAME: test/t1
          FLAGGE: 33
        N_COLS: 10
         PLATZ: 22
    ROW_FORMAT: Dynamisch
 ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Einzeln
 INSTANT_COLS: 6
1 Zeile im Satz (0,00 Sek.)

Wie Sie sehen, ist die aktuelle Spaltennummer der Tabelle test.t1 6, was bedeutet, dass es sich um die 7. Spalte der Tabelle handelt (die Spaltennummerierung beginnt bei 0).

Natürlich unterstützt der Instant-Algorithmus nicht das Löschen normaler Spalten, kann die Spaltenreihenfolge nicht festlegen und weist einige andere Einschränkungen auf. Einzelheiten finden Sie im offiziellen Dokument: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

Diese Einschränkungen verhindern jedoch nicht, dass es sich um eine hervorragende DDL-Funktion handelt. Ich bin davon überzeugt, dass mit der kontinuierlichen Iteration der MySQL-Versionen in späteren Versionen mehr Änderungsvorgänge effiziente Algorithmen wie Instant verwenden können.

Oben finden Sie eine detaillierte Zusammenfassung der schnellen Spaltenaddition von MySQL 8.0 Online DDL. Weitere Informationen zur schnellen Spaltenaddition von MySQL DDL finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!