SQL-Optimierung: Daten effizient in Tabellen einfügen

Sicherungsdateien mit mysqldump anlegen

Wenn Sie mit mysqldump Sicherungsdateien der Datenbank anlegen, sollten Sie die Option --extended-insert verwenden, sodass die Sicherungsdatei mehrzeilige INSERT-Anweisungen enthält. Sie können auch --opt (optimieren), die die Option --extended-insert automatisch aktiviert, sowie ein paar weitere Optionen verwenden, mit denen die Sicherungsdatei beim Neuladen effizienter verarbeitet werden kann. Die mysqldump-Option --complete-insert sollten Sie dagegen nicht verwenden, denn sie erzeugt INSERT-Anweisungen für einzelne Zeilen, die länger dauern und mehr Parsing erforderlich machen als bei Anweisungen, die ohne --complete-insert erzeugt wurden.

Wenn Sie mehrere INSERT-Anweisungen verwenden müssen, sollten Sie sie gruppieren, um die Leerung des Indexcache so weit wie möglich zu reduzieren. Bei transaktionssicheren Tabellentypen können Sie dies tun, indem Sie die INSERT-Anweisungen innerhalb einer einzelnen Transaktion statt im Autocommit-Modus ausführen:

BEGIN;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
COMMIT;

Bei nichttransaktionssicheren Tabellentypen müssen Sie eine Schreibsperre für die Tabelle konfigurieren und die INSERT-Anweisungen dann absetzen, solange die Sperre Bestand hat:

LOCK TABLES tbl_name WRITE;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
UNLOCK TABLES;

Der Vorteil ist bei beiden Methoden der gleiche: Der Indexcache wird nur einmal insgesamt geleert (statt einmal pro INSERT-Anweisung, wie es im Autocommit-Modus oder bei nicht gesperrter Tabelle der Fall wäre).