So fügen Sie einer großen MySQL-Tabelle eine Spalte hinzu

Die Frage wird hier zitiert: https://www.zhihu.com/question/440231149. In MySQL gibt es 300 Millionen Daten in einer Tabelle, die nicht in Tabellen unterteilt ist. Die Anforderung besteht darin, dieser großen Tabelle eine Datenspalte hinzuzufügen. Die Datenbank kann nicht gestoppt werden und es finden weiterhin Hinzufügungs-, Lösch- und Änderungsvorgänge statt. Wie mache ich das? Die Antwort ist originell

In der alten MySQL-Version wurde eine Spalte folgendermaßen hinzugefügt:

ALTER TABLE Ihre Tabelle ADD COLUMN neue Spalte char(128);

Führt zu einer Tabellensperre. Der einfache Vorgang ist wie folgt:

• Erstellen Sie eine neue Tabelle2, die vollständig isomorph zu Tabelle1 ist
• Fügen Sie eine Schreibsperre zu Tabelle1 hinzu
• Führen Sie ALTER TABLE 你的表ADD COLUMN 新列char(128)
• Kopieren Sie die Daten in Tabelle1 nach Tabelle2
• Benennen Sie Table2 in Table1 um und entfernen Sie Table1, wobei Sie alle zugehörigen Sperren aufheben.

Bei extrem großen Datenmengen ist die Tabellensperrzeit sehr lang. Während dieser Zeit werden sämtliche Tabellenaktualisierungen blockiert und Online-Geschäfte können nicht normal ausgeführt werden.

Bei Versionen vor MySQL 5.6 (nicht enthalten) werden Trigger verwendet, um Aktualisierungen einer Tabelle in einer anderen Tabelle zu wiederholen und Daten zu synchronisieren. Wenn die Datensynchronisierung abgeschlossen ist, ändert das Unternehmen den Tabellennamen in eine neue Tabelle und veröffentlicht diese. Der Geschäftsbetrieb wird nicht eingestellt. Die Trigger-Einrichtung ähnelt:

Trigger „person_trigger_update“ NACH DEM UPDATE für jede Zeile der Originaltabelle erstellen 
beginne mit dem Setzen von @x = "UPDATE auslösen";
In neue Tabelle ersetzen. SELECT * aus Originaltabelle, wobei neue Tabellen-ID = Original-Tabellen-ID;
ENDE, WENN;
Ende;

MySQL 5.6 und spätere Versionen führen die Online-DDL-Funktion ein:

Ändern Sie Ihre Tabelle, ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}, LOCK [=] { DEFAULT| NONE| SHARED| EXCLUSIVE }

Die Parameter sind:

ALGORITHMUS:

• DEFAULT : Standardmodus. Wenn in MySQL 8.0 ALGORITHM nicht angegeben ist, wird zuerst der INSTANT-Algorithmus ausgewählt. Wenn dies fehlschlägt, wird der INPLACE-Algorithmus verwendet. Wenn der INPLACE-Algorithmus nicht unterstützt wird, wird die COPY-Methode verwendet.
• SOFORT : Ein neuer Algorithmus, der in 8.0 hinzugefügt wurde. Die hinzugefügte Spalte wird sofort zurückgegeben. Es kann sich jedoch nicht um eine virtuelle Spalte handeln. Das Prinzip ist sehr einfach. Wenn eine neue Spalte erstellt wird, ändern sich nicht sofort alle Originaldaten in der Tabelle. Nur die Spalte und der Standardwert werden im Tabellenwörterbuch aufgezeichnet. Beim standardmäßigen dynamischen Zeilenformat (das eigentlich eine Variante von Compressed ist) werden die Originaldaten als gelöscht markiert, wenn die Spalte aktualisiert wird, und der aktualisierte Datensatz wird am Ende angehängt. Dies bedeutet, dass im Voraus kein Warteschlangenspeicherplatz reserviert wird und nachfolgende Aktualisierungen häufig zu Änderungen des Zeilendatensatzspeicherplatzes führen können. Da bei den meisten Unternehmen jedoch nur die aktuellsten Datensätze geändert werden , stellt dies kein großes Problem dar.
• INPLACE : Ändern Sie die Originaltabelle direkt, ohne die temporäre Tabelle zu kopieren. Ändern Sie Datensätze nacheinander, ohne eine große Menge an Undolog- und Redolog-Aufrufen zu erzeugen und ohne viel Puffer zu belegen. Dadurch können Sie die durch den Neuaufbau der Tabelle verursachte E/A- und CPU-Auslastung vermeiden und während des Zeitraums eine gute Leistung und Parallelität sicherstellen.
• KOPIEREN : Zur Änderung in eine temporäre neue Tabelle kopieren. Durch das Kopieren von Datensätzen wird eine große Menge an Undolog- und Redolog-Vorgängen generiert und ein großer Puffer belegt, was die Geschäftsleistung beeinträchtigt.

SPERREN:

• DEFAULT : Ähnlich wie ALGORITHM's DEFAULT
• NONE : Keine Sperren, ermöglicht gleichzeitiges Lesen und Aktualisieren der Tabelle
• SHARED : Gemeinsames Schloss, das Lesen, aber kein Aktualisieren erlaubt
• EXKLUSIV : Lesen und Aktualisieren sind nicht erlaubt

Vergleich der von verschiedenen Versionen unterstützten Online-DDL-Änderungsalgorithmen:

Referenzdokumente:

MySQL 5.6: https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.htmlMySQL

5.7: https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.htmlMySQL

8.0: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

Dies kann folgendermaßen erfolgen:

ALTER TABLE Ihre Tabelle ADD COLUMN neue Spalte char(128), ALGORITHM=INSTANT, LOCK=NONE;

Ähnliche Anweisungen werden verwendet, um Felder online hinzuzufügen. Am besten geben Sie ALGORITHM und LOCK an , damit Sie bei der Ausführung von DDL genau wissen, welchen Einfluss es auf das Online-Geschäft hat .

Gleichzeitig läuft die Ausführung von Online-DDL ungefähr wie folgt ab:

Es ist ersichtlich, dass in der Anfangsphase eine Metadatensperre erforderlich ist. Die Metadatensperre wurde in MySQL in 5.5 eingeführt. Es gab bereits zuvor ähnliche Mechanismen zum Schutz von Metadaten, aber das Konzept der Metadatensperre wurde nicht klar vorgeschlagen. Allerdings gibt es hinsichtlich des Metadatenschutzes einen erheblichen Unterschied zwischen Versionen vor 5.5 (z. B. 5.1) und Versionen nach 5.5. 5.1 schützt Metadaten auf Anweisungsebene, während 5.5 Metadaten auf Transaktionsebene schützt. Die sogenannte Anweisungsebene bedeutet, dass nach der Ausführung der Anweisung ihre Tabellenstruktur von anderen Sitzungen aktualisiert werden kann, unabhängig davon, ob die Transaktion festgeschrieben oder zurückgesetzt wurde; während die Transaktionsebene bedeutet, dass die Metadatensperre erst nach Beendigung der Transaktion aufgehoben wird.

Die Einführung der Metadatensperre löst hauptsächlich zwei Probleme. Das eine ist das Problem der Transaktionsisolierung. Wenn beispielsweise Sitzung A unter der wiederholbaren Isolationsstufe die Tabellenstruktur während zweier Abfragen ändert, sind die beiden Abfrageergebnisse inkonsistent und können die Anforderungen für wiederholbares Lesen nicht erfüllen. Das andere ist das Problem der Datenreplikation. Wenn beispielsweise Sitzung A mehrere Aktualisierungsanweisungen ausführt und eine andere Sitzung B die Tabellenstruktur ändert und diese zuerst festschreibt, führt der Slave zuerst die Änderung und dann die Aktualisierung erneut aus, was zu Replikationsfehlern führt.

Wenn derzeit viele Transaktionen ausgeführt werden und einige Transaktionen große Abfragen enthalten, beispielsweise:

TRANSAKTION STARTEN;
Wählen Sie count(*) aus Ihrer Tabelle

Dies führt dazu, dass die Ausführung von Transaktionen lange dauert und diese zudem blockiert werden.

Also im Prinzip:

• Vermeiden Sie große Transaktionen
• Nehmen Sie Änderungen an der Tabellenstruktur außerhalb der Geschäftszeiten vor

Dies ist das Ende dieses Artikels zum Hinzufügen einer Spalte zu einer großen MySQL-Tabelle. Weitere Informationen zum Hinzufügen einer Spalte zu einer großen MySQL-Tabelle finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!