So importieren Sie schnell Daten in MySQL

Vorwort:

Im täglichen Studium und bei der Arbeit müssen wir häufig Daten exportieren. Beispiele hierfür sind Datenmigration, Datenwiederherstellung, Erstellen einer neuen Slave-Datenbank usw. Diese Vorgänge können den Import großer Datenmengen beinhalten. Manchmal ist der Importvorgang langsam und der Computerlüfter dreht sich wie wild, was wirklich frustrierend ist. Tatsächlich gibt es einige Tipps, die den Import beschleunigen können. In diesem Artikel erläutert der Autor, wie Sie Daten schnell importieren können.

Hinweis: In diesem Artikel wird nur erläutert, wie Sie schnell SQL-Skripts importieren, die durch logische Sicherungen generiert wurden. Andere Dateiformate werden vorerst nicht behandelt.

1. Versuchen Sie, die Größe der importierten Datei zu reduzieren

Zunächst möchte ich Ihnen einen Vorschlag machen: Versuchen Sie beim Exportieren und Importieren von Daten, die mit MySQL gelieferten Befehlszeilentools zu verwenden, und verwenden Sie keine grafischen Tools wie Navicat und Workbench. Insbesondere bei großen Datenmengen ist das Exportieren und Importieren mit den in MySQL integrierten Befehlszeilentools um ein Vielfaches schneller als mit grafischen Tools wie Navicat. Darüber hinaus können grafische Tools wie Navicat bei Operationen mit großen Datenmengen leicht hängen bleiben. Im Folgenden finden Sie eine kurze Einführung in die Verwendung des mit MySQL gelieferten Befehlszeilentools für den Import und Export.

# Exportieren Sie die gesamte Instanz mysqldump -uroot -pxxxxxx --all-databases > all_database.sql

# Exportieren Sie die angegebene Bibliothek mysqldump -uroot -pxxxxxx --databases testdb > testdb.sql

# Exportieren Sie die angegebene Tabelle mysqldump -uroot -pxxxxxx testdb test_tb > test_tb.sql

#Importieren Sie die angegebene SQL-Datei (geben Sie den Import in die Testdb-Bibliothek an)
mysql -uroot -pxxxxxx testdb < testdb.sql

Die meisten importierten SQL-Skripte erstellen zuerst Datenbanken und Tabellen und fügen dann Daten ein. Der zeitaufwändigste Teil ist wahrscheinlich das Einfügen der Daten. Um die Dateigröße zu reduzieren, wird empfohlen, die erweiterte Einfügemethode zu verwenden, also das stapelweise Einfügen mehrerer Zeilen gleichzeitig, ähnlich wie folgt: insert into table_name values ​​​​(),(),(),...,();. Durch die Verwendung des erweiterten Einfügens wird die Dateigröße erheblich verringert und die Einfügegeschwindigkeit ist um ein Vielfaches höher als beim Einfügen einzeln. Mit mysqldump exportierte Dateien verwenden standardmäßig die Batch-Einfügemethode. Beim Exportieren können Sie den Parameter --skip-extended-insert verwenden, um das Einfügen einzeln zu ändern. Im Folgenden testen wir anhand einer Datentabelle mit 10 Millionen Datenpunkten als Beispiel die Geschwindigkeit beim Einfügen von auf unterschiedliche Weise exportierten Dateien.

Wie aus der obigen Abbildung ersichtlich, dauert das Importieren von SQL-Skripten mit erweitertem Einfügen etwa 10 Minuten, während das Importieren von SQL-Skripten einzeln zu lange dauert und nach etwa einer Stunde immer noch nicht abgeschlossen ist. Der Import eines Textes mit mehr als 2 GB ist seit über einer Stunde nicht abgeschlossen und der Autor kann nicht warten und bricht ihn manuell ab? Es ist jedoch ersichtlich, dass das gemeinsame Einfügen mehrerer Einträge um ein Vielfaches mehr Zeit spart als das Einfügen von Daten einzeln.

2. Versuchen Sie, die Parameter zu ändern, um den Import zu beschleunigen

In MySQL gibt es ein Paar berühmter „Double One“-Parameter, nämlich innodb_flush_log_at_trx_commit und sync_binlog. Aus Sicherheitsgründen sind die Standardwerte dieser beiden Parameter 1. Um das Skript schnell zu importieren, können wir diese beiden Parameter vorübergehend ändern. Im Folgenden finden Sie eine kurze Einführung in diese beiden Parameter:

Der Standardwert von innodb_flush_log_at_trx_commit ist 1 und kann auf 0, 1 oder 2 gesetzt werden

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.

Der Standardwert von sync_binlog ist 1 und kann auf [0,N) gesetzt werden.
Wenn sync_binlog = 0 ist, führt MySQL, ähnlich wie der Mechanismus des Betriebssystems zum Leeren anderer Dateien, keine Synchronisierung mit der Festplatte durch, sondern verlässt sich darauf, dass das Betriebssystem das Binärprotokoll aktualisiert.
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.

Diese beiden Parameter können online geändert werden. Wenn Sie schnell importieren möchten, können Sie die folgenden Schritte ausführen:

# 1. Geben Sie die MySQL-Befehlszeile ein, um diese beiden Parameter vorübergehend zu ändern: set global innodb_flush_log_at_trx_commit = 2;
setze globales sync_binlog = 2000;

# 2. Führen Sie das SQL-Skript zum Importieren aus: mysql -uroot -pxxxxxx testdb < testdb.sql

# 3. Nachdem der Import abgeschlossen ist, ändern Sie die Parameter wieder, um global innodb_flush_log_at_trx_commit = 1 festzulegen.
setze globales sync_binlog = 1;

Es gibt ein weiteres Szenario, in dem Sie eine neue Slave-Datenbank erstellen müssen oder kein Binärprotokoll generieren müssen. In diesem Fall können Sie festlegen, dass beim Importieren von SQL-Skripten vorübergehend kein Binärprotokoll aufgezeichnet wird. Sie können am Anfang des Skripts „set sql_log_bin=0;“ hinzufügen und dann den Import ausführen, was den Vorgang weiter beschleunigt. Wenn Binlog auf Ihrer MySQL-Instanz nicht aktiviert ist, müssen Sie diese Anweisung nicht ausführen.

Zusammenfassen:

In diesem Artikel werden hauptsächlich Methoden zum schnellen Importieren von Daten vorgestellt. Es gibt möglicherweise auch andere Methoden zum schnellen Importieren von Daten, z. B. das Laden von Daten oder das Schreiben von Programmen zum Einfügen in mehreren Threads. Die in diesem Artikel beschriebene Methode ist nur für den manuellen Import von SQL-Skripten geeignet. Nachfolgend finden Sie eine Zusammenfassung der in diesem Artikel erwähnten Methoden.

1. Verwenden Sie zum Exportieren und Importieren das mit MySQL gelieferte Befehlszeilentool.
2. Verwenden Sie die erweiterte Einfügemethode, um mehrere Werte gleichzeitig einzufügen.
3. Ändern Sie vorübergehend die Parameter innodb_flush_log_at_trx_commit und sync_binlog.
4. Schließen Sie das Binlog oder stoppen Sie die Aufzeichnung des Binlogs vorübergehend.
   

Tatsächlich gibt es noch andere Lösungen. Beispielsweise kann man den Index nicht zuerst erstellen und ihn dann nach dem Einfügen der Daten hinzufügen. Oder man ändert die Tabelle zuerst in die MyISAM- oder MEMORY-Engine und ändert sie dann nach Abschluss des Imports in die InnoDB-Engine. Die Umsetzung dieser beiden Methoden ist jedoch schwierig und ihre Auswirkungen sind unbekannt. Die oben genannten Methoden sind lediglich eine Zusammenfassung des Autors, die auf persönlichen Erfahrungen basiert und möglicherweise nicht vollständig ist. Sie können sie gerne ergänzen.

Oben finden Sie Einzelheiten zum schnellen Importieren von Daten in MySQL. Weitere Informationen zum Importieren von Daten in MySQL finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!