Detaillierte Erklärung zum effizienten Importieren mehrerer SQL-Dateien in MySQL

MySQL bietet mehrere Möglichkeiten, mehrere SQL-Dateien (mit SQL-Anweisungen) zu importieren. Es gibt zwei häufig verwendete Befehle: mysql und source.

Die Importeffizienz dieser beiden Befehle ist jedoch sehr unterschiedlich. Weitere Einzelheiten finden Sie im abschließenden Vergleich.

(Es gibt auch Importmethoden wie sqlimport und LOAD DATA INFILE, diese werden jedoch hauptsächlich zum Importieren von CSV- oder XML-Dateidaten verwendet, nicht von SQL-Dateien.)

Angenommen, wir haben eine große Datei users.sql. Der Einfachheit halber teilen wir sie in drei unabhängige kleine SQL-Dateien auf: user1.sql, user2.sql und user3.sql.

1. MySQL-Befehl importieren

MySQL-Befehl zum Importieren mehrerer SQL-Dateien:

$ für SQL in *.sql; mache mysql -uroot -p"123456" mydb < $SQL; fertig

2. Quellbefehl importieren

Für den Quellbefehl müssen Sie zuerst die MySQL-Befehlszeile eingeben:

$ mysql -uroot -p"123456"

Um mehrere SQL-Dateien zu importieren, muss man zunächst eine zusätzliche Datei erstellen. Der Name ist beliebig. Hier nehmen wir: all.sql, der Inhalt ist:

Quelle user1.sql
Quelle user2.sql
Quelle user3.sql

Beachten Sie, dass jede Zeile mit einem Quellbefehl beginnen muss.

Führen Sie dann die Datei mit dem Quellbefehl aus:

mysql > mydb verwenden;
mysql > Quelle /home/gary/all.sql

3. Wie kann die Importgeschwindigkeit verbessert werden?

Bei Dateien über 100 MB ist die Geschwindigkeit extrem langsam, wenn Sie sie nur auf diese Weise importieren.

Gemäß den offiziellen Empfehlungen von MySQL gibt es mehrere Maßnahmen, mit denen sich die Importgeschwindigkeit erheblich steigern lässt:

Passen Sie für MyISAM den Systemparameter an: bulk_insert_buffer_size (mindestens doppelt so groß wie eine einzelne Datei)

Passen Sie für InnoDB den Systemparameter an: innodb_log_buffer_size (mindestens doppelt so groß wie eine einzelne Datei. Sie können ihn nach Abschluss des Imports wieder auf den Standardwert von 8 MB ändern. Beachten Sie, dass es sich nicht um innodb_buffer_pool_size handelt.)

Löschen Sie mit Ausnahme des Primärschlüssels alle anderen Indizes und erstellen Sie den Index nach Abschluss des Imports neu.

Autocommit deaktivieren: autocommit=0. (Verwenden Sie zum Abschalten nicht den Befehl „set global autocommit=1;“, da sonst das gesamte MySQL-System die automatischen Commits einstellt und der InnoDB-Log-Puffer bald voll ist. Auch die Punkte 5 und 6 gelten nur in der Sitzung. Die richtige Vorgehensweise finden Sie weiter unten.)

Deaktivieren Sie eindeutige Indexprüfungen: unique_checks=0. (Das Ausschalten dieser Option wirkt sich auf die Aktualisierung doppelter Schlüssel aus.)

Deaktivieren Sie die Fremdschlüsselprüfung: foreign_key_checks=0.

Der Einfügewert wird in einer Anweisung geschrieben, wie etwa: INSERT INTO yourtable VALUES (1,2), (5,5), ...;

Wenn eine Auto-Increment-Spalte vorhanden ist, setzen Sie den Wert von innodb_autoinc_lock_mode auf 2.

Unter diesen ändern die Punkte 1-2 und 8 die Datei my.cnf und starten MySQL dann neu:

Bulk_Insert_Buffer_Größe = 2G;
innodb_log_buffer_size=2G;
innodb_autoinc_lock_mode=2;

Die in Artikel 3 verwendeten Befehle:

# Löschen Sie den Index DROP INDEX index_name ON talbe_name
ALTER TABLE Tabellenname DROP INDEX Indexname
ALTER TABLE Tabellenname DROP PRIMARY KEY

# Einen Index hinzufügen ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE Tabellenname ADD UNIQUE (Spaltenliste)
ALTER TABLE Tabellenname ADD PRIMARY KEY (Spaltenliste)

Die Punkte 4 bis 6 sind in .sql geschrieben und das Batch-Bash-Skript lautet wie folgt:

für SQL in *.sql;
Tun
  echo $SQL;
  sed -i '1i\SET autocommit=0;\nSET unique_checks=0;\nSET foreign_key_checks=0;' $SQL
  sed -i '$a\COMMIT;\nSET autocommit=1;\nSET unique_checks=1;\nSET foreign_key_checks=1;' $SQL
Erledigt

Nach der Anpassung gemäß den obigen Schritten wird die Einfügegeschwindigkeit erheblich verbessert.

4. Vergleich der Effizienz zwischen MySQL und Quelle

Wenn die SQL-Datei klein ist, ist die Quelle schneller als MySQL.

Beim eigentlichen Testimport von 5 SQL-Dateien mit einer Gesamtgröße von 25 MB ist der MySQL-Befehl 2 Sekunden schneller als die Quelle (mein eigener Test, nicht repräsentativ für die allgemeinen Ergebnisse).

Grob lässt sich sagen, dass beim Importieren großer SQL-Dateien die Verwendung des MySQL-Befehls empfohlen wird.

Oben ist die Methode zum effizienten Importieren mehrerer SQL-Dateien in MySQL beschrieben. Ich hoffe, sie kann Ihnen helfen.