In diesem Artikel werden die Strategien von MySQL zur Verbesserung der Effizienz von Datenladevorgängen vorgestellt. Oft beschäftigt Sie die Optimierung von SELECT-Abfragen, da es sich um die am häufigsten verwendeten Abfragen handelt und es nicht immer einfach ist, zu bestimmen, wie sie optimiert werden können. Das Laden von Daten in eine Datenbank ist relativ einfach. Oft beschäftigt Sie die Optimierung von SELECT-Abfragen, da es sich um die am häufigsten verwendeten Abfragen handelt und es nicht immer einfach ist, zu bestimmen, wie sie optimiert werden können. Das Laden von Daten in eine Datenbank ist relativ einfach. Es gibt jedoch Strategien, mit denen die Effizienz von Datenladevorgängen verbessert werden kann. Die Grundprinzipien lauten wie folgt:
Das Massenladen ist schneller als das Laden einzelner Zeilen, da der Indexcache nicht nach dem Laden jedes Datensatzes geleert werden muss.
Das Laden einer Tabelle ohne Index ist schneller als das Laden nach einem Index. Wenn Indizes vorhanden sind, müssen nicht nur Datensätze zu den Datendateien hinzugefügt werden, sondern jeder Index muss auch geändert werden, um das Hinzufügen der neuen Datensätze widerzuspiegeln.
Kürzere SQL-Anweisungen sind schneller als längere SQL-Anweisungen, da sie weniger Analysen auf der Serverseite erfordern und schneller über das Netzwerk vom Client an den Server gesendet werden können. Einige dieser Faktoren mögen trivial erscheinen (insbesondere der letzte), aber wenn Sie eine große Datenmenge laden, können selbst kleine Faktoren einen großen Unterschied in den Ergebnissen bewirken. Aus den oben genannten allgemeinen Prinzipien können wir mehrere praktische Schlussfolgerungen darüber ableiten, wie Daten am schnellsten geladen werden können:
LOAD DATA (in all seinen Formen) ist effizienter als INSERT, da es Zeilen in Stapeln lädt. Es gibt weniger Indexaktualisierungen und der Server muss statt mehrerer nur eine Anweisung analysieren und interpretieren.
LOAD DATA ist effizienter als LOAD DATA LOCAL. Bei LOAD DATA muss sich die Datei auf dem Server befinden und über FILE-Berechtigungen verfügen, aber der Server kann die Datei direkt von der Festplatte lesen. Bei LOAD DATA LOCAL liest der Client die Datei und sendet sie über das Netzwerk an den Server, was langsam ist.
Wenn Sie INSERT verwenden müssen, sollten Sie ein Formular verwenden, das die Angabe mehrerer Zeilen in einer einzigen Anweisung ermöglicht, wie zum Beispiel:
Je mehr Zeilen Sie in einer Anweisung angeben können, desto besser. Dies reduziert die Anzahl der erforderlichen Anweisungen und die Anzahl der Indexaktualisierungen. Wenn Sie mysqldump zum Generieren einer Datenbanksicherungsdatei verwenden, sollten Sie die Option --extended-insert verwenden, damit die Dumpdatei mehrzeilige INSERT-Anweisungen enthält. Sie können auch --opt (Optimierung) verwenden, was die Option --extended-insert aktiviert. Umgekehrt sollte die Verwendung der Option --complete-insert für mysqldump vermieden werden; diese Option führt dazu, dass INSERT-Anweisungen einzeilig sind, länger zur Ausführung benötigen und mehr Analyse erfordern als Anweisungen, die ohne die Option --complete-insert generiert werden.
Verwenden Sie komprimierte Client/Server-Protokolle, um den Netzwerkdatenverkehr zu reduzieren. Für die meisten MySQL-Clients kann dies mit der Befehlszeilenoption --compress angegeben werden. Es wird im Allgemeinen nur in langsameren Netzwerken verwendet, da die Komprimierung viel Prozessorzeit erfordert.
Lassen Sie MySQL Standardwerte einfügen. Geben Sie in der INSERT-Anweisung keine Spalten an, denen in irgendeiner Weise Standardwerte zugewiesen werden. Dies führt im Durchschnitt zu kürzeren Anweisungen und reduziert die Anzahl der über das Netzwerk an den Server gesendeten Zeichen. Darüber hinaus erfordern Anweisungen mit weniger Werten weniger Analyse und Transformation durch den Server.
Wenn die Tabelle indiziert ist, können Sie Masseneinfügungen (LOAD DATA oder mehrzeilige INSERT-Anweisungen) verwenden, um den Indexaufwand zu reduzieren. Dadurch werden die Auswirkungen von Indexaktualisierungen minimiert, da der Index erst dann aktualisiert werden muss, wenn alle Zeilen verarbeitet wurden, und nicht nach jeder Zeile.
Wenn Sie eine große Datenmenge in eine neue Tabelle laden müssen, sollten Sie die Tabelle erstellen und laden, wenn sie nicht indiziert ist, und dann den Index erstellen, nachdem die Daten geladen wurden. Dies geht schneller. Das einmalige Erstellen des Index (anstatt ihn nur einmal pro Zeile zu ändern) ist schneller.
Wenn Sie einen Index vor dem Laden löschen oder deaktivieren, kann das erneute Erstellen oder Aktivieren des Index nach dem Laden der Daten den Ladevorgang beschleunigen. Wenn Sie zum Laden von Daten eine Lösch- oder Deaktivierungsstrategie verwenden möchten, führen Sie unbedingt einige Experimente durch, um herauszufinden, ob sich dies lohnt (wenn Sie eine kleine Datenmenge in eine große Tabelle laden, kann die Neuerstellung und Indizierung länger dauern als das Laden). die Daten) ).
Mit DROP INDEX und CREATE INDEX können Indizes gelöscht und neu erstellt werden. Eine Alternative besteht darin, Indizes mit myisamchk oder isamchk zu deaktivieren und zu aktivieren. Hierzu ist ein Konto auf dem MySQL-Server-Host mit Schreibzugriff auf die Tabellendateien erforderlich. Um Tabellenindizes zu deaktivieren, geben Sie das entsprechende Datenbankverzeichnis ein und führen Sie einen der folgenden Befehle aus:
Verwenden Sie myisamchk für MyISAM-Tabellen mit Indexdateien mit der Erweiterung .MYI und isamchk für ISAM-Tabellen mit Indexdateien mit der Erweiterung .ISM. Nachdem Sie Daten in die Tabelle geladen haben, aktivieren Sie den Index wie folgt:
Wenn Sie sich für die Indexdeaktivierung und -aktivierung entscheiden, sollten Sie das in Kapitel 13 beschriebene Tabellenreparatur-Sperrprotokoll verwenden, um zu verhindern, dass der Server gleichzeitig die Sperren ändert (obwohl die Tabelle zu diesem Zeitpunkt nicht repariert wird, wird sie wie eine Tabelle geändert). Reparaturprozess, daher müssen Sie dasselbe Sperrprotokoll verwenden).
Die oben beschriebenen Datenladeprinzipien gelten auch für feste Abfragen im Zusammenhang mit Clients, die unterschiedliche Vorgänge ausführen müssen. Beispielsweise möchten Sie im Allgemeinen vermeiden, lange SELECT-Abfragen für häufig aktualisierte Tabellen auszuführen. Lang laufende SELECT-Abfragen können zu großen Konflikten führen und die Schreibleistung beeinträchtigen. Eine mögliche Lösung besteht darin, die Datensätze zunächst in einer temporären Tabelle zu speichern und sie dann regelmäßig zur Haupttabelle hinzuzufügen, wenn es sich bei den Schreibvorgängen hauptsächlich um INSERT-Vorgänge handelt. Dies ist kein praktikabler Ansatz, wenn sofortiger Zugriff auf neue Datensätze erforderlich ist. Diese Methode kann jedoch verwendet werden, solange auf sie für kurze Zeit nicht zugegriffen wird. Die Verwendung temporärer Tabellen bietet zwei Vorteile. Erstens reduziert es Konflikte mit der SELECT-Abfrageanweisung in der Haupttabelle und führt daher schneller aus. Zweitens ist die Gesamtzeit zum Laden von Datensätzen aus der temporären Tabelle in die Haupttabelle kürzer als die Gesamtzeit zum separaten Laden der Datensätze. Der entsprechende Indexcache muss nur am Ende jedes Stapelladens und nicht nach jeder Zeile aktualisiert werden laden. Eine Anwendung dieser Strategie besteht darin, über die Webseite des Webservers auf die MySQL-Datenbank zuzugreifen. In diesem Szenario gibt es möglicherweise keine höhere Autoritätsebene, die die sofortige Eintragung des Datensatzes in die Haupttabelle garantiert.
Wenn es sich bei den Daten nicht genau um die Art von Einzeldatensätzen handelt, die im Falle eines Systemabsturzes eingefügt würden, besteht eine weitere Strategie zur Reduzierung von Indexaktualisierungen darin, die Tabellenerstellungsoption DELAYED_KEY_WRITE für MyISAM-Tabellen zu verwenden (was möglich sein kann, wenn MySQL verwendet wird). einige Dateneingabearbeiten). Diese Option bewirkt, dass der Indexcache nur gelegentlich und nicht nach jedem Einfügen aktualisiert wird.
Wenn Sie die serverweite verzögerte Indexaktualisierung nutzen möchten, starten Sie mysqld einfach mit der Option --delayed-key-write. In diesem Szenario werden Indexblockschreibvorgänge verzögert, bis der Block geleert werden muss, um Platz für andere Indexwerte zu schaffen, bis ein Flush-Tables-Befehl ausgeführt wird oder bis die Indextabelle geschlossen wird.
-