Hinweise zum Import, Export und zur Migration von MySQL Billions of Data

Ich habe in letzter Zeit viele MySQL-Notizen gemacht, hauptsächlich weil das Oracle des Unternehmens relativ stabil ist und weniger Wartung erfordert. Letzte Woche wurde mir die Migration von Milliarden MySQL-Daten zugewiesen, also habe ich die Gelegenheit genutzt, um meine Studiennotizen aufzuzeichnen.

Die Datenmigration ähnelt in ihrem Funktionsprinzip und technischen Support dem Datenexport, BI-Berichten usw. Der größte Unterschied liegt in der Menge der importierten und exportierten Daten. Im Allgemeinen übersteigt die Menge der Berichtsdaten nicht mehrere Millionen. Bei der Datenmigration von Internetunternehmen geht es jedoch oft um Dutzende oder Hunderte Millionen Daten.

Importieren und Exportieren sind zwei Prozesse. Auch bei der Datenmigration sollten wir sie getrennt betrachten. Gleichzeitig werden die Import-/Exportmethoden unterteilt in:

1. MySQL verfügt über eine Import/Export-Methode

2. Verschiedene Client-Import-/Exportmethoden

Fassen Sie zunächst den Export zusammen:

1. Für Daten mit weniger Feldern oder weniger Feldinhalt können Sie Tools wie Navicat verwenden, um sie über den Client zu exportieren. Hier exportiere ich drei Felder, die alle Werte innerhalb von 11 Ziffern sind. Mit Navicat werden etwa 2,5 Millionen Daten pro Minute exportiert.

2. MySQLs eigene Exportanweisung: „select into outfile“-Anweisung;

SELECT ... FROM TABLE_A --Sie können eine Where-Bedingung hinzufügen INTO OUTFILE "/Pfad/zur/Datei" --Speicherort der Exportdatei FIELDS TERMINATED BY ',' OPTIONAL ENCLOSED BY '"' --Feldtrennzeichen und Einschlusszeichen LINES TERMINATED BY '\n';--Zeilenumbruchzeichen

Die Felder davor sind sehr einfach und verständlich, deshalb werde ich sie hier nicht erklären. Lassen Sie uns über die Felder danach sprechen:

FELDER MIT ',' ABGESCHLOSSEN bedeutet, dass die Felder durch Kommas getrennt sind, z. B.: Feld A Feld B, das Anzeigeformat beim Export ist: A, B

OPTIONAL ENCLOSED BY '"' bedeutet, dass der Feldinhalt in doppelte Anführungszeichen eingeschlossen ist. Das Exportformat ist: "A","B"

Zeilen, die durch '\n' abgeschlossen sind; Jede Datenzeile ist durch eine neue Zeile getrennt. Das Exportformat ist wie folgt:

"A","B"

"A1","B1"

Natürlich können Sie die Feldunterscheidungs- und Inklusionssymbole auch selbst definieren, etwa: '#

Der Vorteil der Verwendung des in MySQL integrierten Exports/Imports besteht darin, dass es extrem schnell ist. Der Nachteil besteht jedoch darin, dass Dateien nur an die lokale Adresse des Serverhosts exportiert werden können. Für Kollegen wie bi, die keine Datenbankhostberechtigungen haben, kann diese Methode ein Luxus sein. Glücklicherweise ist die Exportgeschwindigkeit von Client-Tools von Drittanbietern für Berichte mit weniger Feldern/Inhalten nicht besonders langsam;

Import:

Der Schlüssel ist der Datensatzimport. Der Import wird hauptsächlich von DBAs durchgeführt, um Daten zu migrieren. Die Methoden sind in Client- und MySQL-integrierte Methoden unterteilt:

Ich empfehle hier dringend, MySQL-Import zu verwenden, da ich zuvor 130 Millionen Daten migrieren musste. Der Import der Daten mit dem Navicat-Client dauerte 22 Stunden, was zu zeitaufwändig und unsicher war. Tools wie Navicat bergen das Risiko eines Pseudotodes. Daher ist es nicht zu empfehlen, mehr als 10.000 Daten über Navicat zu importieren.

Integrierte MySQL-Importmethode:

--Die offizielle Dokumentdefinition lautet wie folgt und die Kommentare wurden basierend auf meinem eigenen Verständnis hinzugefügt:

DATEN LADEN 、
[LOW_PRIORITY | CONCURRENT]--Ausführen/sofort, wenn niemand die Datenbank verwendet[LOCAL]--Mit diesem Parameter liest die Serverseite die Datei nicht auf dem Serverhost. Ohne diesen Parameter wird die Datei standardmäßig auf dem Serverhost gelesenINFILE 'file_name'--Dateiadresse und Dateiname lesen[REPLACE | IGNORE]--Bei doppelten Daten heißt es: Ersetzen/Schreiben wiederholen. Es wird empfohlen, wiederholtes Schreiben zu ignorierenINTO TABLE tbl_name--In welche Tabelle importieren[PARTITION (partition_name [, partition_name] ...)]--Diese Parameterzeile kann weggelassen werden. Es wird empfohlen, die folgenden Felder zu verwenden
    [CHARACTER SET charset_name]--Legt das Zeichenformat des importierten Inhalts fest. Sie können utf-8 oder GBK usw. angeben. [{FIELDS | COLUMNS} --Feldkennung [TERMINATED BY 'string'] --Welches Symbol wird verwendet, um die Systemfelder zu unterscheiden? [[OPTIONALLY] ENCLOSED BY 'char']--Welches Symbol wird verwendet, um den Anfang und das Ende des Systemfelds selbst zu unterscheiden? [ESCAPED BY 'char']--Escape-Zeichen. Wenn es sich um eine Textdatei handelt, gibt es im Textfeld Sonderzeichen wie doppelte Anführungszeichen. Sie können die Sonderzeichen der Textdatei ignorieren, indem Sie ein Escape-Zeichen definieren.]
    [LINES --Zeilenkennung [STARTING BY 'string'] -- Zeichenfolge, die den Anfang einer Zeile definiert. Wenn am Anfang einer Zeile keine Zeichenkennung steht, ist es normalerweise nicht notwendig, [TERMINATED BY 'string'] -- Zeichenfolgekennung am Zeilenende zu schreiben. Die Daten zwischen den Zeilen werden durch definierende Zeichen unterschieden]
    [IGNORE number {LINES | ROWS}]--Ignoriere die erste Anzahl von Zeilen in der Datei, die normalerweise nicht geschrieben werden-das Folgende gibt an, welche Felder in [(col_name_or_user_var) eingefügt werden sollen
        [, Spaltenname_oder_Benutzervariable] ...)]
    [SET col_name={Ausdruck | DEFAULT},
        [, col_name={Ausdruck | DEFAULT}] ...] 

Im Originaltext heißt es, dass mit „Daten laden“ Daten sehr schnell in die Datenbank importiert werden können. Wenn Sie jedoch die Parameter „Felder“ und „Zeilen“ verwenden möchten, müssen Sie über einen Parameterwert verfügen und die „Felder“ müssen vor dem Parameter „Zeilen“ stehen.

Die Aussage, die ich dieses Mal verwendet habe, ist:

Daten in Datei „/data/files/T_CUST_INFO.txt“ laden --Standardserverordner
ignore into table t_dq_user -- Einfügen doppelter Datensätze zulassen
Mit ',' abgeschlossene Felder -- legen Sie fest, ob die Felder durch Kommas getrennt sind
Zeilen, die mit '\n' (Kunden-ID, Gerätenummer, Anmeldetyp) abgeschlossen sind. -- Analysieren Sie jedes Datenelement anhand der Zeilenumbruchkennung und fügen Sie es in das von mir angegebene Feld ein.

Einfügen ist eine sehr einfache Anweisung. Ich werde hier keine spezifischen Beispiele geben. Was ich zeigen möchte, ist, wie man die Effizienz des Einfügens verbessern kann.

Denn als ich die Anweisung zum Einfügen zum ersten Mal verwendet habe, begann die Ausführung um 12 Uhr abends und war bis 11 Uhr des nächsten Tages noch nicht abgeschlossen. Das bedeutet also nicht, dass die Verwendung von load ohne die Konfiguration anderer Dinge definitiv schnell sein wird.

Das Datenformat, das ich dieses Mal eingefügt habe, ist wie folgt:

Das Textformat ist wie folgt:

Es gibt insgesamt 140 Millionen Datensätze, die in Form eines 4,3 GB großen Textdokuments exportiert und per FTP-Software in den Ordner „Server/Daten/Dateien“ hochgeladen wurden.

Beschwerde 1:

Da das Projekt erforderte, dass alle drei Felder Indizes haben mussten, habe ich beim Erstellen der Tabelle Indizes hinzugefügt, was zu einer unbegrenzt langen Wartezeit führte.

Grund:

Indizes beanspruchen Speicherplatz. Wenn ich zum Importieren von drei Feldern Indizes hinzufügen muss, bedeutet das, dass ich den Index einmal für jedes Feld schreiben muss, was um ein Vielfaches länger dauert, als wenn ich keine Indizes hinzufüge.

Optimierungsmethode:

Entfernen Sie vor dem Importieren den Tabellenindex und hinterlassen Sie eine Auto-Inkrement-ID. Fügen Sie diese dann nach Abschluss des Imports hinzu.

Beschwerdepunkt 2:

Motorauswahl:

Die MySQL-Engine behandelt das Laden und Schreiben anders, insbesondere der Master-Slave-Sicherungsmechanismus:

Für die MyISAM-Engine:
(1) Führen Sie auf dem Masterserver einen Ladevorgang durch.
(2) Die auf dem Master ausgeführte Datei load.txt wird synchron auf den Slave übertragen und eine Datei load.txt wird im Verzeichnis tmp_dir generiert.
Der Master-Server übergibt so viele Daten an den Slave-Server, wie er eingefügt hat.
(3) Wenn der Ladevorgang auf dem Master abgeschlossen ist, ist auch die Dateiübertragung auf den Slave abgeschlossen.
Das heißt: Erzeugen Sie eine vollständige load.txt-Datei auf dem Slave
An diesem Punkt beginnt der Slave, Daten aus load.txt zu lesen und in die lokale Tabelle einzufügen.

Für die InnoDB-Engine:
(1) Die Hauptdatenbank führt einen 'Laden'-Vorgang durch
(2) Nachdem der Master-Datenbankvorgang abgeschlossen ist, wird die Datei load.txt auf den Slave übertragen.
Der Slave akzeptiert die Datei und generiert eine Datei load.txt im Verzeichnis tmp_dir
Nach der Annahme und Generierung der vollständigen load.txt beginnt es mit dem Lesen der Datei und dem Einfügen der Daten in die lokale Tabelle

Wenn Sie also extreme Geschwindigkeit anstreben und Milliarden von Daten haben, können Sie die MyISAM-Engine wählen. Der Standard für MySQL sollte InnoDB sein. Dieses Mal habe ich die Engine jedoch nicht geändert. Ich empfehle nicht, die Standard-InnoDB-Engine zu ändern. Schließlich ist die offizielle Haupt-Engine von Oracle die umfassendste. Sofern keine besonderen Umstände vorliegen, wird die Verwendung von MyISAM nicht empfohlen. Wenn Sie MyISAM verwenden, achten Sie auf zwei Punkte:

Bei Verwendung von MyISAM können Sie mehrere Sitzungswerte anpassen, um den Lesespeicher zu erweitern und die Lesedaten zu verbessern. Die Anweisungen lauten wie folgt:

SETZEN SIE DIE SITZUNG BULK_INSERT_BUFFER_SIZE = 256217728;
Setzen Sie die Sitzung myisam_sort_buffer_size auf 256217728.

Für die MyISAM-Engine kann die eindeutige Prüfung vor dem Importieren zunächst ausgeschaltet und dann wieder eingeschaltet werden:

SET UNIQUE_CHECKS=0 -- Deaktivieren SET UNIQUE_CHECKS=1 -- Aktivieren

Beschwerdepunkt 3:

Obwohl MySQL lokale Clients beim Lesen von Dateien unterstützt, hat es aus verschiedenen Netzwerkgründen nur geringe Auswirkungen auf Dutzende oder Hunderte von Daten. Wenn die Datenmenge jedoch Hunderte von Millionen erreicht, hat sogar 1 Millisekunde besonders große Auswirkungen. Daher wird empfohlen, FTP zum Übertragen auf den Server zum Lesen zu verwenden.

Beschwerdepunkt 4:

Erfahrungsaustausch: Überprüfen Sie nach dem Importieren den Serverstatus: Verwenden Sie den Befehl top, um die MySQL-CPU-Auslastung des Hosts zu überprüfen. Theoretisch wird dadurch mehr CPU belegt. Bei meinem ersten Import, der sehr lange dauerte, war die CPU zu 10 % belegt, was ein äußerst abnormaler Import war. Beim zweiten Import war die CPU zu 110 % belegt, was dem Zustand des schnellen Schreibens entsprach. Die letzten 140 Millionen Daten dauerten nur mehr als 7 Minuten, daher müssen Sie den Server nach der Ausführung der Anweisung überwachen, da die Anweisung sonst möglicherweise nicht normal ausgeführt wird.

CPU-Auslastung:

Hinweis: Der größte Unterschied zwischen Laden und Einfügen besteht darin, dass beim Laden die Syntax nur einmal ausgeführt wird und die Daten dann stapelweise eingefügt werden, während beim Einfügen alle Daten einmal bearbeitet werden und der Feldindex einmal durchlaufen wird. Daher ist das Einfügen selbst bei großen Datenmengen extrem langsam.

Zusammenfassen:

Die größte und offensichtlichste Änderung dieser Optimierung ist, dass die Importgeschwindigkeit nach dem Entfernen des Indexes extrem hoch ist. Index, ich sage es noch einmal:

Beim Importieren können Sie den Index zunächst entfernen und nach Abschluss des Imports hinzufügen.

Aktualisiert am 3. Juli 2020

Beim Importieren großer Datenmengen in MySQL müssen Sie auf das maximale Transaktionslimit achten. Vor einigen Monaten trat bei einer Datenmigration im MySQL 8.0 MGR-Cluster ein hohes Transaktionslimit auf, wodurch die Instanz Probleme bekam und MySQL neu gestartet werden musste. Die Standardkonfiguration sollte ein Transaktionslimit von 150 Millionen aufweisen. Zu diesem Zeitpunkt waren die importierten Daten relativ groß und es wurde keine Parametererweiterung durchgeführt. Gleichzeitig wurde für den Import keine Datensegmentierung oder Flusssteuerung durchgeführt, wodurch die Datenbank blockiert und neu gestartet wurde. Aufgrund der Unternehmensanforderungen eines 7*24*365-Mechanismus wurde dies als Unfall angesehen. Wenn das Unternehmen hohe Anforderungen stellt, wird empfohlen, beim Importieren auf die MySQL-Konfiguration selbst oder auf die Limits für die Übermittlung von Importtransaktionen zu achten.

Damit ist dieser Artikel über MySQL-Datenimport, -export und -migrationshinweise auf Milliardenebene abgeschlossen. Weitere relevante MySQL-Datenimport-, -export- und -migrationsinhalte auf Milliardenebene finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!