Datenbank-Im­port – rund 10.000 Zeilen
CSV-Datei per PHP-Script in MySQL-Datenbank schreiben

Vorwort

In diesem Artikel wir­d auf Datenbank-Importe und entsprechende MySQL-Optimierung eingegangen. Verfasst im Oktober 2007 © Johannes Schwichtenberg.

Die Artikel-Datenbank einer Webanwendung soll regelmäßig aktualisiert werden. Dies geschieht über eine Schnittstelle die CSV-Dateien empfängt, diese per PHP-Script auswertet, verarbeitet und anschließend in die Datenbank-Tabellen schreibt, welche in der 3. Normalform normalisiert sind.

Geht diese Rechnung auf? Vor allem mit mehr als 10.000 Datensätzen?

Dieser Artikel soll auf die Probleme mit der Verarbeitung der Datenbank-Befehle und mögliche Lösungen eingehen. Die PHP-Verarbeitung der CSV-Datei steht dabei im Hintergrund. – Inzwischen geht mein "PHP how-to" CSV-Datei in MySQL importieren genauer auf diese Fragestellung ein.

Inhalt

1. Lösu­ng wie immer – Folge: Absturz
2. Weitere Schritte und Überlegungen
3. Eine andere Idee: LOAD DATA
4. Ein erster Lichtblick: REPLACE
5. Verwendung der Shell-Konsole und der MySQL-Engine
6. Fazit

Lösung wie immer

Ein Fazit vorab: Die einfachste Lösung ist nicht immer die idealste! Und „geht doch“ gibt es nicht. 

Jeder der schon einmal eine Gästebuch oder ein News-System in PHP mit einer MySQL-Datenbank entwickelt hat, wird den INSERT-Befehl kennen.

Die „Lösung wie immer“ war also: CSV zerlegen und Artikel per INSERT in die Tabelle schreiben, die nötigen Eigenschaften (folgend Attribute) in die jeweiligen Zusatztabellen.

Es muss also rund 10.000 mal ein Artikel eingefügt (eventuell später aktualisiert) werden und entsprechende Attribute wie Farbe oder Mengen ebenfalls, also bestimmt 20-30 Tausend INSERT-Befehle.

Besonders problematisch dabei ist, dass keinerlei Redundanzen entstehen sollen, die Daten sind so atomisiert, dass eine Artikel-Farbe in einer separaten Tabelle gespeichert wird, denn sie kann bei mehreren Artikeln (1:n) vorkommen. Für andere Attribute wie Menge, Größe etc. gilt das gleiche. Man muss also jedes mal die Schlüssel-ID dieser Attribute abfragen. – Herkömmlich per SELECT aus der entsprechenden Tabelle.

Dass für diese ganze Prozedur 30 Sekunden maximale Scriptlaufzeit schnell erreicht sind, ist leider schnell erkennbar.

Erste Optimierung: Attribute in Arrays zwischenspeichern

Die Artikel-Attribute haben einen Schlüssel und einen Wert, das lässt sich einfach in einem Array speichern. Der Array-Key stellt hier den späteren Primärschlüssel der Tabelle dar. Nun kann man mit array_search die gewünschten Schlüssel abfragen, ohne jedes mal ein SELECT an die Datenbank abzusetzen. Das ist schneller, aber braucht trotzdem viel Zeit! (Bei 10.000 Artikeln muss im ungünstigsten Fall pro Attribut in einem bis zu 10.000 Felder großen Array per array_search gesucht werden.)

Nachtrag: Die Praxis hat inzwischen gezeigt, dass die Verwendung von array_search ebenfalls ziemlich zeitintensiv ist und außerdem nicht zuverlässig; bei einem größeren Array werden Werte bzw. deren Indizes nicht gefunden.

Weitere Schritte und Überlegungen

Script-Laufzeit hochstellen gilt nicht, INSERT-Befehle zusammenfassen spart etwas Zeit und nicht mehr benötigen Puffer kann man freigeben! 

Nachdem der Webserver einige Male abgestürzt war, könnte man auf die Idee kommen, die maximale Scriptlaufzeit zu erhöhen – das wird im "PHP how-to" Skriptlaufzeit hochsetzen beschrieben. Wenn man einen Root-Server hat, wäre das prinzipiell kein Problem, aber eigentlich soll es hier doch um Geschwindigkeitsoptimierung gehen!

Um die Performance zu steigern, kann man die INSERT-Befehle zusammenfassen, sodass man an einen „Kopf“ zum Beispiel bis zu 500 einzufügende Datensätze anhängt. Den ganzen Befehl kann man mit einem mysql_query auführen und muss nicht fünfhundert Mal mit dieser Funktion eine Interaktion zwischen PHP und der Datenbank forcieren.

INSERT INTO artikel (id, art_nr, name...) VALUES
(57, 'A155', 'Jahres-Lizenz' ...),
(58, 'A158', '10er Client-Lizenz' ...),
(59, 'A159', 'Wartungsvertrag' ...);

Diese Lösung ist gut. Aber nur für INSERT geeignet, denn den UPDATE-Befehl kann man nicht auf mehrere Datensätze anwenden, es sei denn man möchte überall die gleichen Werte setzen.

Weitere Vorteile bringt die Funktion mysql_unbuffered_query, mit der man ein MySQL-Statement einfach absetzen kann. Durch eine andere Verfahrensweise als mysql_query ist diese weniger Speicherintensiv und bei Befehlen, mit denen man keine Ergebnisse abholen will, problemlos verwendbar.

An die Funktion mysql_free_result sei an dieser Stelle auch einmal hingewiesen, denn um die Import-Daten aufzubereiten, muss man auch einige Menge Daten aus der Datenbank lesen, um Vergleiche zu machen und die 3. NF zu realisieren. Den durch SELECT-Ergebnisse belegten Speicher kann man mit mysql_free_result wieder freigeben.

Eine anderen Idee: LOAD DATA

LOAD DATA hilft beim Importieren von CSV-Dateien. 

Nachdem jegliche Interaktion zwischen dem PHP-Script und der MySQL-Datenbank so viel Resourcen zu benötigen scheint, könnte man sich komplett der Datenbank widmen und auf das PHP-Script verzichten. Aus diesem Gedanken heraus, kam die Funktion LOAD DATA ins Spiel.

Diese MySQL-Funktion erlaubt eine Datei einzulesen und per Parameter anzugeben, wie diese einzulesen und wohin sie zu importieren ist.

Leider stellt sie sich für das gegebene Problem als nicht verwendbar dar, denn sie kann nur mit CSV-Files umgehen. Darin ist aber weder die Normalisierung vorhanden, noch würde es Sinn machen, zuerst eine entsprechend normalisierte CSV-Datei zu generieren (etwa per PHP) um diese später zu „loaden“.

Ein erster Lichtblick: REPLACE

REPLACE erfüllt die Aufgaben von INSERT und UPDATE, für den Programmierer eine bequeme Sache. 

Diese Funktion scheint sich jemand ausgedacht zu haben, der vor dem gleichen Problem stand: Es müssen Daten neu eingefügt oder aktualisiert werden und zwar möglichst viele und möglichst schnell.

Mit dem MySQL-Befehl REPLACE lässt sich UPDATE und INSERT kombinieren. Per definiertem Schlüssel kann man den Datensatz aktualisieren, sofern er vorhanden ist, oder er wird eingefügt. Die Entscheidung nimmt einem die Datenbank auch noch ab und man kann den Query aufbauen wie einen INSERT-Befehl: Mit einer Kopfzeile und beliebig vielen Datensatz-Zeilen:

REPLACE INTO artikel (id, art_nr, name...) VALUES
(1, 'A123', 'Server-Lizenz' ...),
(5, 'A127', '5 Client-Lizenz' ...);

Die Funktion arbeitet so, dass sie anhand des festgelegten Schlüssels (im Idealfall der Primärschlüssel der Tabelle) prüft, ob der Datensatz vorhanden ist und ihn dann  löscht; anschließend – also in jedem Fall – wird der neu übergebene eingefügt.

Die Lösungsvariante mit REPLACE lohnt sich allerdings erst richtig, wenn man tatsächlich den Primärschlüssel (am besten numerisch) der Tabelle verwenden kann. Für das hier gegebene Problem kam er nicht in Frage, denn der eindeutige Schlüssel (entsprechend der CSV-Datenquelle) war eine alphanumerische Zeichenfolge und die entsprechende Tabellen-Spalte nicht der Primary-Key. Außerdem sollte bedacht werden, dass auch die Artikel-Attribute entsprechend der 3. NF aufbereitet und gespeichert werden müssen.

Verwendung der shell-Konsole und MySQL-Engine

Die schnellste Lösung erfordert Root-Rechte, bringt aber einen großen Geschwindigkeitsvorteil und mal etwas Abwechslung in den Script-Alltag.

Da zur Lösung des gegebenen Problems ein Root-Server zur Verfügung stand, wurde die Suche nach einer geeigneten Lösung fortgesetzt und ausgeweitet. Dabei wurde versucht möglichst nah an der Datenbank zu arbeiten um unnötig lange „Übertragungswege“ zu vermeiden.

Die Funktionen der MySQL-Engine scheinen eine sehr schnelle Lösungsvariante zu ermöglichen: Man kann per Kommandozeilen-Aufruf von MySQL eine Datei mit Datenbank-Befehlen einlesen (die User-Kennung vorangestellt) und so sehr viele Anweisungen auf einmal absetzen.

Die Umsetzung und endgültige Realisierung sah folgendermaßen aus:

Im PHP-Script wird die CSV-Datei eingelesen, die Normalisierung gewährleistet und mit selbst geschriebenen Funktionen werden die Datenbank-Befehle gesammelt und in Blöcken zu etwa 500 bis 1000 in eine temporäre Datei geschrieben.

Per PHP-Funktion shell_exec wurde auf die Konsole zugegriffen, die MySQL-Engine mit entsprechenden Parametern angesprochen und so die Befehle aus der vorher erstellten Datei ausgeführt.

Bei einem kompletten Import oder einer Aktualisierung wurden dann bis zu 10 temporäre SQL-Files erzeugt, mit einer Gesamtgröße bis zu 10 Megabyte.

Der entsprechende PHP-Code könnte folgendermaßen aussehen:

shell_exec("mysql -u root --password=geheim
datenbank-name < datei.txt");

Fazit

Durch die Verwendung der Kommandozeilen-Variante konnte die Scriptlaufzeit wesentlich verkürzt werden – wie man Skriptlaufzeit ermitteln kann, steht im "PHP how-to" Scriptlaufzeit messen. Jedoch bringt die Shell die Einschränkung mit sich, dass man nur auf Root- oder vServern zugriff darauf haben wird.

Es wurde ebenfalls deutlich, dass es sich lohnt die eigenen Scripte einmal auf Performanz zu überprüfen. Insbesondere wenn man mit tausenden Datensätzen arbeiten will. Nur zu schnell gewöhnt man sich bequeme Kurzschreibweisen, die ihren Zweck erfüllen, aber bei großen Datenvolumen hier und da ihre Speicherleichen hinterlassen.

 

Hinweis: Sämtliche Informationen ohne Garantie und Anspruch auf Vollständigkeit, jedoch nach bestem Wissen des Autors zum Zeitpunkt der Erstellung.