Die Zeit, die für das Einfügen eines Datensatzes erforderlich ist, wird von den folgenden Faktoren bestimmt (hierbei geben die Zahlen in den Klammern näherungsweise den Anteil an):
Verbindung herstellen: (3)
Abfrage an den Server senden: (2)
Abfrage analysieren: (2)
Datensatz einfügen: (1 × Datensatzlänge)
Indizes einfügen: (1 × Anzahl der Indizes)
Schließen: (1)
Hierbei wird der Mehraufwand für das Öffnen von Tabellen nicht berücksichtigt (dies ist einmal je nebenläufiger Abfrageausführung erforderlich).
Die Größe der Tabelle verlangsamt das Einfügen von Indizes um
den Faktor LogN
(für
B-Tree-Indizes).
Sie können Einfügeoperationen mit den folgenden Methoden beschleunigen:
Wenn Sie gleichzeitig viele Datensätze vom selben Client
aus einfügen, verwenden Sie
INSERT
-Anweisungen mit mehreren
VALUES
-Listen, um mehrere Datensätze zur
selben Zeit einzufügen. Dies ist erheblich (in manchen
Fällen sogar um mehrere Größenordnungen) schneller als
die Verwendung separater
INSERT
-Anweisungen für je einen
Datensatz. Wenn Sie zu einer Tabelle, die nicht leer ist,
Daten hinzufügen, dann können Sie die Variable
bulk_insert_buffer_size
optimieren, um
das Einfügen noch mehr zu beschleunigen. Siehe auch
Abschnitt 5.2.2, „Server-Systemvariablen“.
Wenn Sie eine große Menge Datensätze von verschiedenen
Clients einfügen, können Sie den Vorgang durch Verwendung
der INSERT DELAYED
-Anweisung
beschleunigen. Siehe auch Abschnitt 13.2.4.2, „INSERT DELAYED
“.
Bei einer MyISAM
-Tabelle können Sie
nebenläufige Einfügeoperationen verwenden, um Datensätze
einzufügen, während gleichzeitig
SELECT
-Anweisungen ausgeführt werden,
sofern sich in der Mitte der Tabelle keine gelöschten
Datensätze befinden. Siehe auch
Abschnitt 7.3.3, „Gleichzeitige Einfügevorgänge“.
Wenn Sie eine Tabelle aus einer Textdatei laden, verwenden
Sie LOAD DATA INFILE
. Dies ist
normalerweise 20-mal schneller als die Verwendung von
INSERT
-Anweisungen. Siehe auch
Abschnitt 13.2.5, „LOAD DATA INFILE
“.
Durch geringen Mehraufwand können Sie LOAD DATA
INFILE
für eine MyISAM
-Tabelle
noch schneller machen, wenn diese Tabelle viele Indizes
enthält. Gehen Sie wie folgt vor:
Erstellen Sie die Tabelle mit CREATE
TABLE
(optional).
Führen Sie eine FLUSH
TABLES
-Anweisung oder den Befehl
mysqladmin flush-tables aus.
Setzen Sie myisamchk --keys-used=0 -rq
/path/to/db/tbl_name
ab. Hierdurch wird die Verwendung aller Indizes für die
Tabelle unterbunden.
Fügen Sie mit LOAD DATA INFILE
Daten
in die Tabelle ein. Hierbei werden keine Indizes
aktualisiert – der Vorgang ist also sehr schnell.
Wenn Sie zukünftig nur aus der Tabelle lesen wollen, komprimieren Sie sie mit myisampack. Siehe auch Abschnitt 14.1.3.3, „Kennzeichen komprimierter Tabellen“.
Erstellen Sie die Indizes mit myisamchk -rq
/path/to/db/tbl_name
neu. Hierdurch wird der Indexbaum zunächst im Speicher
erstellt, bevor er auf die Festplatte geschrieben wird.
Dies ist viel schneller als die Aktualisierung des
Indexes im Zuge von LOAD DATA INFILE
,
da zahlreiche Suchvorgänge auf der Festplatte
entfallen. Außerdem ist der resultierende Indexbaum
einwandfrei verteilt.
Führen Sie eine FLUSH
TABLES
-Anweisung oder den Befehl
mysqladmin flush-tables aus.
Beachten Sie, dass LOAD DATA INFILE
obige
Optimierung automatisch durchführt, wenn die
MyISAM
-Tabelle, in die Sie die Daten
einfügen, leer ist. Der wesentliche Unterschied besteht
darin, dass Sie myisamchk wesentlich mehr
Temporärspeicher für die Indexerstellung zuweisen können,
als Sie durch den Server zur Indexneuerstellung reservieren
lassen würden, wenn er die LOAD DATA
INFILE
-Anweisung ausführt.
Sie können die Indizes für eine
MyISAM
-Tabelle ferner aktivieren oder
deaktivieren, indem Sie statt myisamchk
die folgenden Anweisungen verwenden. Bei Verwendung dieser
Anweisungen können Sie die FLUSH
TABLE
-Operationen übergehen:
ALTER TABLEtbl_name
DISABLE KEYS; ALTER TABLEtbl_name
ENABLE KEYS;
Um INSERT
-Operationen zu beschleunigen,
die mit mehreren Anweisungen durchgeführt werden, sperren
Sie Ihre Tabellen:
LOCK TABLES a WRITE; INSERT INTO a VALUES (1,23),(2,34),(4,33); INSERT INTO a VALUES (8,26),(6,29); UNLOCK TABLES;
Hiervon profitiert die Leistungsfähigkeit, weil der
Indexpuffer nur einmal auf die Festplatte synchronisiert
wird, nachdem alle INSERT
-Anweisungen
abgeschlossen wurden. Im Normalfall gäbe es so viele
Synchronisierungsvorgänge für Indexpuffer, wie
INSERT
-Anweisungen vorhanden sind.
Explizite Sperranweisungen sind nicht erforderlich, wenn Sie
alle Datensätze mit einer einzelnen
INSERT
-Anweisung einfügen können.
Bei transaktionssicheren Tabellen sollten Sie START
TRANSACTION
und COMMIT
statt
LOCK TABLES
benutzen, um die
Einfügeoperationen zu beschleunigen.
Eine Sperrung verringert auch die Gesamtdauer für die Überprüfung mehrerer Verbindungen, auch wenn sich die Gesamtwartezeit für einzelne Verbindungen erhöhen kann, weil diese auf das Erwirken der Sperren warten. Zum Beispiel:
Verbindung 1 führt 1.000 Einfügeoperationen durch.
Die Verbindungen 2, 3 und 4 fügen je eine Einfügeoperation durch.
Verbindung 5 führt 1.000 Einfügeoperationen durch.
Wenn Sie keine Sperrung verwenden, werden die Verbindungen 2, 3 und 4 vor den Verbindungen 1 und 5 beendet; setzen Sie hingegen eine Sperre, dann werden die Verbindungen 2, 3 und 4 zwar (wahrscheinlich) nicht eher als die Verbindungen 1 und 5 enden, aber der gesamte Zeitbedarf verringert sich um ca. 40 Prozent.
INSERT
-, UPDATE
- und
DELETE
-Operationen sind in MySQL sehr
schnell, aber Sie können eine noch bessere
Gesamtperformance erzielen, indem Sie Sperren für alles
setzen, was mehr als fünf Einfüge- oder
Änderungsoperationen in einem Datensatz umfasst. Wenn Sie
sehr viele Einfügeoperationen in einem Datensatz vornehmen,
könnten Sie ab und zu (d. h. etwa alle 1.000 Datensätze)
ein LOCK TABLES
gefolgt von einem
UNLOCK TABLES
absetzen, damit auch andere
Threads auf die Tabelle zugreifen können. Dies bringt trotz
allem einen ansehnlichen Leistungsgewinn.
Auch wenn Sie die soeben beschriebenen Strategien verwenden,
ist INSERT
beim Laden von Daten
allerdings nach wie vor wesentlich langsamer als
LOAD DATA INFILE
.
Um die Leistung bei MyISAM
-Tabellen für
LOAD DATA INFILE
und
INSERT
gleichermaßen zu optimieren,
vergrößern Sie den Schlüssel-Cache, indem Sie den Wert
der Systemvariablen key_buffer_size
erhöhen. Siehe auch Abschnitt 7.5.2, „Serverparameter feineinstellen“.
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.