Bei der Optimierung von SQL-Datenbanken denkt man zunächst an SELECT-Abfragen, weil diese die häufigste Anfrageart darstellen und es nicht immer ganz einfach ist, die optimale Lösung zu erkennen. Im Vergleich dazu ist das Laden (Speichern) von Daten in Ihre Datenbank zunächst ganz einfach. Aber es gibt Strategien, mit denen Sie auch die Ladeoperationen für Daten verbessern können. Dabei gelten die folgenden grundlegenden Konzepte:
-
Das Laden großer Datenmengen ist im Vergleich zum Laden einzelner Zeilen schneller, weil der Indexcache nicht nach jedem Laden eines Datensatzes geleert werden muss; er kann geleert werden, nachdem alle neuen Datensätze eingefügt wurden.
-
Das Laden erfolgt schneller, wenn eine Tabelle keine Indizes hat. Wenn Indizes vorliegen, müssen nicht nur Datensätze der Datendatei hinzugefügt werden, sondern jeder Index muss geändert werden, um das Einfügen des neuen Datensatzes zu berücksichtigen.
-
Kürzere SQL-Anweisungen sind schneller als lange, weil der Server weniger parsen muss und sie schneller vom Client zum Server über das Netzwerk gesendet werden können.
Unsere neue Serie zur Optimierung von MySQL-Anfragen basiert auf Kapitel 4 “Optimierung von Anfragen“ des Standardwerks „MySQL 4 – Konfiguration, Administration und Entwicklung“ von Paul Dubois aus dem SAMS-Verlag. Weitere MySQL-Bücher und eBooks auch von Paul Dubois finden Sie in unserem Partner-Buchshop bei informit.de.
|
Teil 2: SQL-Optimierung: Indexnutzung in Queries manuell steuern |
|
Teil 4: SQL-Optimierung: Daten effizient in Tabellen einfügen |
Allgemeine Konzepte
Einige dieser Aspekte (insbesondere die Länge der Statements) erscheinen weniger relevant, aber wenn Sie sehr viele Daten laden, bedeuten selbst kleine Effizienzsteigerungen eine Verbesserung. Wir wenden die folgenden allgemeinen Konzepte an, um mehrere praktische Schlussfolgerungen zu demonstrieren, wie Daten am schnellsten geladen werden können:
-
LOAD DATAist (in allen Varianten) effizienter alsINSERT, weil dabei große Datenmengen geladen werden. Der Indexcache muss weniger häufig geleert werden, und der Server muss nur eine Anweisung parsen und interpretieren, nicht mehrere. -
LOAD DATAist effizienter alsLOAD DATA LOCAL. BeiLOAD DATAmuss sich die Datei auf dem Server befinden, und Sie brauchen eineFILE-Berechtigung, aber der Server kann die Datei direkt von der Festplatte lesen. BeiLOAD DATA LOCALliest der Client die Datei und sendet sie über das Netzwerk an den Server, was weniger schnell erfolgt. -
Wenn Sie
INSERTverwenden müssen, benutzen Sie die Form, die erlaubt, mehrere Zeilen in einer einzigen Anweisung anzugeben:
INSERT INTO tbl_name VALUES(...),(...),... ;
Je mehr Zeilen Sie in der Anweisung angeben können, desto besser. Damit brauchen Sie insgesamt weniger Anweisungen, und der Indexcache muss nicht so oft geleert werden. Der Widerspruch zur früheren Bemerkung, dass kürzere Anweisungen schneller verarbeitet werden können, ist nur ein scheinbarer. An dieser Stelle gilt das Prinzip, dass eine einzelne INSERT-Anweisung, die mehrere Zeilen einfügt, insgesamt schneller ist als eine entsprechende Menge einzelner INSERT-Anweisungen für jeweils eine Zeile. Auch kann die mehrzeilige Anweisung auf dem Server mit wesentlich seltenerer Leerung des Indexcaches verarbeitet werden.
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).
Datenmenge reduzieren
-
Verwenden Sie das komprimierte Client/Server-Protokoll, um die über das Netzwerk geschickte Datenmenge zu reduzieren. Für die meisten MySQLClients kann das mithilfe der Kommandozeilenoption --compress realisiert werden. Im Allgemeinen sollte diese Methode nur in langsamen Netzwerken eingesetzt werden, weil die Komprimierung relativ viel Prozessorzeit in Anspruch nimmt.
-
Überlassen Sie es MySQL, Standardwerte für Sie einzufügen, d.h. geben Sie in
INSERT-Anweisungen keine Spalten an, denen ohnehin der Standardwert zugewiesen wird. Ihre Anweisung wird dadurch kürzer, und Sie müssen nicht so viele Zeichen über das Netzwerk an den Server senden. Außerdem hat der Server, weil die Anweisungen weniger Werte enthalten, weniger Arbeit mit dem Parsing und der Wertekonvertierung. -
Wenn eine Tabelle indiziert wird, können Sie den Indizierungsaufwand reduzieren, indem Sie das Einfügen im Rahmen einer Stapelverarbeitung vornehmen (
LOAD DATAoder mehrzeiligeINSERT-Anweisungen). Damit muss der Index nicht so oft aktualisiert werden, weil der Indexcache nur geleert werden muss, nachdem alle Zeilen verarbeitet wurden, und nicht nach jeder Zeile. -
Wenn Sie bei MyISAM- und ISAM-Tabellen sehr viele Daten in eine neue Tabelle einfügen müssen, sollten Sie die Tabelle zunächst ohne Indizes erzeugen, die Daten laden und die Indizes dann anlegen. Es ist schneller, die Indizes alle gleichzeitig zu erzeugen, anstatt sie für jede Zeile zu ändern. Bei Tabellen, die bereits über Indizes verfügen, kann das Laden der Daten schneller sein, wenn Sie den Index vor dem Laden löschen oder deaktivieren und ihn nach dem Laden neu aufbauen oder reaktivieren. Diese Strategien gelten allerdings nicht für InnoDB- und BDB-Tabellen, weil diese Tabellentypen nicht über Optimierungsmöglichkeiten für die getrennte Indexerstellung verfügen.
Indizes löschen
Wenn Sie beim Laden von Daten in MyISAM- oder ISAM-Tabellen Indizes löschen oder deaktivieren wollen, sollten Sie Ihre Situation genau bewerten, um herauszufinden, ob das sinnvoll ist. Wenn Sie nämlich nur wenige Daten in eine große Tabelle laden, kann es ohne spezielle Vorbereitung länger dauern, die Indizes neu aufzubauen, als die Daten zu laden.
Zum Löschen und Wiedererstellen von Indizes verwenden Sie DROP INDEX und CREATE INDEX oder aber die indexbezogenen Formen von ALTER TABLE. Zum De- und Reaktivieren von Indizes stehen hingegen zwei Alternativen bereit:
-
Sie verwenden die Formen
DISABLE KEYSundENABLE KEYSvonALTER TABLE:ALTER TABLE tbl_name DISABLE KEYS;
ALTER TABLE tbl_name ENABLE KEYS;
Diese Anweisungen schalten die Aktualisierung aller nicht eindeutigen Indizes in der Tabelle ab und wieder an. -
Die Dienstprogramme myisamchk und isamchk erlauben die Manipulation von Indizes. Diese Dienstprogramme arbeiten direkt mit den Tabellendateien, d. h., Sie benötigen Schreibrechte für diese Dateien, um myisamchk und isamchk verwenden zu können. Ferner sollten Sie Vorsichtsmaßnahmen treffen, die verhindern, dass der Server auf die Tabellen zugreift, solange Sie die Dateien bearbeiten.
De- und Reaktivierung von Indizes
Die Anweisungen DISABLE KEYS und ENABLE KEYS sind die zu bevorzugende Methode zur De- und Reaktivierung, weil hier der Server die Arbeit erledigt. Allerdings sind sie erst seit MySQL 4 vorhanden (beachten Sie, dass der Server die Optimierung automatisch durchführt, wenn Sie LOAD DATA verwenden, um Daten in eine leere MyISAM-Tabelle zu laden).
Um die Indizes einer MyISAM-Tabelle »manuell« zu deaktivieren, vergewissern Sie sich zunächst, dass der Server die Tabelle tatsächlich in Ruhe lässt. Wechseln Sie dann in das Datenbankverzeichnis, und führen Sie den folgenden Befehl aus:
% myisamchk --keys-used=0 tbl_name
Wenn Sie Daten in die Tabelle geladen haben, reaktivieren Sie den Index wie folgt:
% myisamchk --recover --quick --keys-used=n tbl_name
n ist eine Bitmaske, die angibt, welche Indizes zu aktivieren sind. Dabei entspricht Bit 0 dem Index 1. Wenn also eine Tabelle drei Indizes hat, dann sollte n den Wert 7 haben (binär 111). Sie können die Indexnummern mithilfe der Option --description ermitteln:
% myisamchk --description tbl_name
Die Befehle für ISAM-Tabellen sind ähnlich, nur müssen Sie isamchk statt myisamchk verwenden, und der Wert --keys-used für isamchk gibt den zu verwendenden Index mit der höchsten Nummer an (bei einer Tabelle mit drei Indizes müsste n dann den Wert 3 haben).
Temporäre Tabellen
Die oben beschriebenen Konzepte zum Laden von Daten gelten auch für die Umgebung mit kombinierten Anfragen, in denen Clients unterschiedliche Operationen ausführen. Beispielsweise könnten Sie versuchen, ganz allgemein lange SELECT-Anfragen für Tabellen zu vermeiden, die häufig aktualisiert werden.
Die Folge sind konkurrierender Zugriff auf die Tabelle und außerdem eine unzureichende Performance bei Schreiboperationen. Eine mögliche Abhilfe bestünde darin, beim Schreiben hauptsächlich INSERT-Operationen auszuführen, neue Datensätze in eine temporäre Tabelle einzutragen und diese Datensätze in bestimmten Zeitabständen der Haupttabelle als Ganzes hinzuzufügen.
Das ist nicht sinnvoll, wenn Sie unmittelbar darauf auf die neuen Datensätze zugreifen müssen, aber wenn Sie es sich leisten können, sie für kurze Zeit zwischenzuspeichern, ohne Zugriff auf sie zu haben, dann sind temporäre Tabellen in zweierlei Hinsicht eine gute Lösung: Erstens reduzieren sie den Umfang des konkurrierenden Zugriffs, der durch SELECT-Anfragen auf der Haupttabelle entsteht, sodass die Anfragen schneller ausgeführt werden können. Zweitens dauert es weniger lange, mehrere Datensätze aus der temporären Tabelle in die Haupttabelle zu laden, als einzelne Datensätze zu laden; der Indexcache muss erst nach jedem Laden, das per Stapelverarbeitung erfolgt, geleert werden, nicht nach jeder einzelnen Zeile.
Eine Anwendungsmöglichkeit für diese Strategie ist die Protokollierung von Webseitenzugriffen von Ihrem Webserver in eine MySQL-Datenbank. In diesem Fall ist es mit hoher Wahrscheinlichkeit auch nicht unbedingt erforderlich, die einzelnen Einträge direkt in die Haupttabelle einzutragen.
DELAYED_KEY_WRITE
Bei MyISAM-Tabellen besteht eine weitere Strategie dafür, den Indexcache weniger häufig leeren zu müssen, darin, die Option DELAYED_KEY_WRITE beim Anlegen der Tabelle zu verwenden. Dies ist möglich, wenn es keinen Beinbruch darstellt, falls ein Datensatz bei einem abnormalen Systemausfall nicht in die Haupttabelle geschrieben wird (was etwa der Fall sein könnte, wenn Sie MySQL zur Protokollierung verwenden).
Die Option sorgt dafür, dass der Indexcache nicht nach jedem Einfügevorgang, sondern nur gelegentlich geleert wird. Wenn Sie die verzögerte Leerung des Indexcache serverweit verwenden wollen, starten Sie mysqld mit der Option --delay-key-write. In diesem Fall wird das Schreiben von Indexblöcken für eine Tabelle verzögert, bis der Cache geleert werden muss, um Platz für andere Indexwerte zu schaffen, bis ein Befehl zum Leeren des Caches ausgeführt wird oder bis die Tabelle geschlossen wird.
Für einen Slave-Replikationsserver sollten Sie die Option --delay-key-write=ALL benutzen, um die Leerung des Indexcache für alle MyISAM-Tabellen unabhängig davon zu verzögern, wie sie ursprünglich auf dem Masterserver erstellt wurden.
Ausblick
Dieser Teil der Serie hat sich mit der Effizienz beim Speichern und Einfügen von Daten beschäftigt. Der nächste Teil behandelt die Priorisierung von Clients und Aktionen sowie das geschickte Sperren von Daten. (mzu)
Unsere neue Serie zur Optimierung von MySQL-Anfragen basiert auf Kapitel 4 “Optimierung von Anfragen“ des Standardwerks „MySQL 4 – Konfiguration, Administration und Entwicklung“ von Paul Dubois aus dem SAMS-Verlag. Weitere MySQL-Bücher und eBooks auch von Paul Dubois finden Sie in unserem Partner-Buchshop bei informit.de.
|
Teil 2: SQL-Optimierung: Indexnutzung in Queries manuell steuern |
|
Teil 4: SQL-Optimierung: Daten effizient in Tabellen einfügen |