Daten in MySQL-Datenbank laden

Wer oft mit großen Datenmengen hantiert, benötigt geeignete Lösungen, um diese analysieren und visuell aufbereiten zu können. Der Import von Daten in eine Datenbank ist dabei eine Grundvoraussetzung. Erst nachdem Daten in einer festen Struktur abgelegt werden kann über den Inhalt und die Qualität eine Aussage gemacht werden.

Big Data

Die Zeiten manueller Analyse sind schon lange vorbei. Massendaten können zwar nach wie vor mit handwerklichen Mitteln ausgewertet werden, allerdings steigt die Arbeitszeit dafür ins Unermessliche. Große Datenmengen werden idealerweise in eine Datenbank importiert und danach ausgewertet. Die Voraussetzung für den Import ist allerdings eine feste Struktur der Daten:

Die Zeilen enthalten die gleiche Anzahl an Spalten
Die Werte der Zeilen sind einheitlich getrennt (Komma, Semikolon, Doppelpunkt, Pipe, …)
Der Separator ist nicht Teil eines Wertes. Ausnahme: Die Werte werden durch Anführungsstriche eingeschlossen (Anführungsstriche dürfen nicht Teil des Wertes sein)

Als Datenbankbasis eignet sich MySQL hervorragend. Die Open-Source -Datenbank kann kostenlos für alle gängigen Betriebssysteme heruntergeladen werden. Es empfielt sich die Erweiterung PhpMyAdmin zu installieren, die dem Benutzer ein grafisches Web-Frontend zur Verfügung stellt. Über dieses Webinterfaces kann beispielsweise die Struktur der Daten vorab festgelegt werden.

Struktur festlegen

Bevor die Struktur festgelegt wird, sollten ein paar Beispieldatensätze der Daten herangezogen werden. Neben der Anzahl der Datenfelder ist der Typ dieser von besonderer Bedeutung. MySQL unterscheidet zwischen vielen Datentypen und deren Länge. Wird also für eine Spalte nur eine ganze Zahl erwartet, die kleiner als 1000 ist, so sollte als Datentyp Integer und als Länge 3 gewählt werden. Auch auf eine Indizierung sollte nicht verzichtet werden, denn diese bietet bei späteren Abfragen ein enormer Performance-Schub.

Daten importieren

Beim Import von Massendaten kommt es besonders auf die Methode an, wie die Daten eingelesen werden. Oftmals befinden sich die zu importieren Daten in einer CSV oder Text-Datei. Bei einigen tausend Datensätzen spielt die Importmethode noch keine große Rolle. Bei mehreren millionen Datensätzen kann der Unterschied Stunden oder sogar Tage betragen. Es werden nun drei gängige Importmethoden gegenübergestellt, die den Unterschied in Bezug auf Importdauer verdeutlichen.

Folgende CSV-Datei mit Inhalt liegt vor:

Martin;Mechatroniker
Stefanie;Bäckerin
Christoph;Pilot
Daniel;Bürokaufmann
Sabine;Friseurin
Julia;Grafikerin

Jede Zeile Text entspricht einem Datensatz. Die Felder sind per Semikolon getrennt.

1. Methode – Die Schlechteste

Diese Methode ist wohl die schlechteste und die zugleich am häufigsten angewendet wird.

// Jeden Datensatz einzeln in die DB schreiben
$benutzer_liste = file($csv_datei);
foreach ($benutzer_liste as $benutzer)
{
    list($name, $beruf) = explode(';', $benutzer);
    $db->query("INSERT INTO `{$tabelle}` (`name`, `beruf`) VALUES ('{$name}', '{$beruf}')");
}

Zunächst wird mit der file-Funktion die CSV-Datei als Array eingelesen. In der anschließenden foreach-Schleife wird Datensatz für Datensatz ausgelesen, die Zeilen am Separator zerlegt, wodurch man die getrennten Werte Name und Beruf erhält. Diese werden dann direkt mittels query() sofort an die Datenbank gesendet, die den neuen Datensatz dann in die Tabelle schreibt. Jeder Datensatz wird also einzeln in die Datenbank geschrieben. Im o.g. Beispiel der CSV Datei befinden sich 6 Datensätze, also wird auch „nur“ 6 mal die Datenbank kontaktiert.

Diese Methode verschwendet massiv Zeit und Ressourcen, deswegen ist sie ein absolutes no-go!

2. Methode – Die Notlösung

Um die Schreibvorgänge zu beschleunigen und um Ressourcen zu sparen muss dafür sorge getragen werden, dass wie im Vergleich zur letzten Methode die Anzahl der Queries stark reduziert werden.

// Datensätze zusammenfassen
$benutzer_liste = file($csv_datei);
$value_array = array();
foreach ($benutzer_liste as $benutzer)
{
    list($name, $beruf) = explode(';', $benutzer);
    $value_array[] = "('{$name}', '{$beruf}')";
}
$values = join(',', $value_array);
$db->query("INSERT INTO `{$tabelle}` (`name`, `beruf`) VALUES {$values}");

Es wird zunächst wie im vorherigen Beispiel gestartet, indem die Datei als Array eingelesen und in einer Schleife durchlaufen wird. Bei dieser Methode wird allerdings nichts an die Datenbank gesendet, sondern Wertepaare gebildet, die dann en bloc an die Datenbank geschickt werden. Dies geschieht in dieser Zeile:

$value_array[] = "('{$name}', '{$beruf}')";

Im Array werden also Zeichenketten abgelegt, die z.B. so aussehen: (‚Martin‘,’Mechatroniker‘)
Das entspricht also einem Wertepaar, wie es mittels VALUE an die DB geschickt wird. Nachdem alle Wertepaare gebildet wurden, werden diese mit join, das ist ein Alias von implode, und dem Klebstoff Komma (,) zu einer Zeichenkette zusammengefügt und an VALUE übergeben. Dadurch erhält man eine Anweisung die in etwas so aussieht:

INSERT INTO `{$tabelle}` (`name`, `beruf`) VALUES ('Martin','Mechatroniker'),('Michael','Schreiner'),('Matthias','KFZ-Mechaniker') ... usw.

Anschließend wird diese Anweisung nur ein Mal an die DB geschickt und alle Datensätze werden hinzugefügt. Es wird also nicht so häufig die Datenbank kontaktiert, dafür wird aber PHP mehr Arbeit übergeben. Wie sich das auf die Performance auswirkt wird weiter unten gezeigt.

3. Methode – Die Beste

Die schnellste und für den Server schonenste Möglichkeit die CSV Daten in die Datenbank zu bekommen ist, wenn das die Datenbank selbst erledigen kann. Datenbankaufgaben sind immer um ein vielfaches schneller, als gekünstelte Lösungen mit PHP. Zunächst der Code:

// Datenbankanweisung
$sql = "LOAD DATA LOCAL INFILE '{$csv_datei}'
        INTO TABLE `{$tabelle}`
        FIELDS TERMINATED BY ';'
        OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '\n'
        (`name`, `beruf`)";
 
// Query senden
$db->query($sql);

Wie man sieht wird nur eine mehr oder weniger einfache Anweisung an die Datenbank gesendet. Es werden keine Arrays oder Schleifen durchlaufen.
MySQL wird mitgeteilt, dass eine lokale CSV Datein geladen werden soll:

LOAD DATA LOCAL INFILE '{$csv_datei}'

Der Pfad zu der Datei muss ein absoluter Serverpfad sein. Es reicht also nicht aus nur den Dateiname zu übergeben, selbst wenn diese im gleichen Verzeichnis wie das PHP Script liegt. Dann muss noch MySQL mitgeteilt werden, wohin die geladene Datei eingefügt werden soll:

INTO TABLE `{$tabelle}`

Jetzt folgen Anweisungen an MySQL die der DB mitteilen, wie die Daten in der CSV vorliegen:

FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'

Die erste Zeile bestimmt, dass die Werte in der CSV durch Semikola getrennt sind (Martin;Mechatroniker). Die Zweite Zeile sagt, dass unsere Werte auch durch Double-Quotes eingeschlossen sein dürfen. Dadurch zerbricht die Struktur nicht, falls ein Separator im Wert vorkommen würde, z.B.: (Martin;just4it“;Webentwickler). Ohne die Double-Quotes würde MySQL denken, dass Martin der Name ist und just4it der Beruf. Mit Mechatroniker könnte es nichts anfangen und da man auch keine 3 Werte in 2 Spalten einfügen kann, käme es zu einem Fehler.
Die dritte Zeile mit LINES TERMINATED BY legt fest, wo ein Datensatz endet und ein neuer anfängt. Bei einer CSV ist das der Zeilenumbruch, der durch das Steuerzeichen \n repräsentiert wird.

Bleibt noch die letzte Zeile, die eigentlich selbsterklärend sein sollte.

(`name`, `beruf`)

Das sind schlicht die Felder in der Tabelle, in die die Werte aus der CSV eingefügt werden sollen. Das ist vergleichbar mit:

INSERT INTO `tabelle` (`name`, `beruf`) VALUES (…)

Auf den ersten Blick sieht die Anweisung etwas heftig aus, erklärt sich dann aber sehr schnell von allein. Fragt sich jetzt aber, was das effektiv bringt im Vergleich zu den anderen Methoden.

Der Methodenvergelich

Für einen Vergleich der Methoden wurden knapp 40.000 Beispieldatensätze (Vorname, Nachname) generiert und in einer CSV-Datei abgespeichert. Diese Datei wurde mit den drei Methoden in die Datenbank importiert. Das folgende Ergebnis wurde dabei erzielt:

Methode 1: Scriptlaufzeit 6.426 Sekunden – Memory Peak 6 429 KB
Methode 2: Scriptlaufzeit 0.908 Sekunden – Memory Peak 14 191 KB
Methode 3: Scriptlaufzeit 0.241 Sekunden – Memory Peak 338 KB

Wie zu erkennen ist stinkt die 1. Methode total ab. Sie dauert sehr lange und mit 6 MB RAM Verbrauch ist sie auch nicht gerade sparsam. Bedenkt man, dass man auf Shared Hosting Server, also normaler Mietwebspace, häufig nur 8 MB RAM für PHP zur Verfügung hat, wird das schon richtig eng, wenn noch andere Aktivitäten auf dem Server stattfinden.
Methode 2 ist schon um ein vielfaches schneller, aber dadurch das von den Wertpaaren ein ewiglanger String erzeugt wird, damit wir nur ein Mal die Datenbank kontaktieren müssen, wächst auch der RAM Verbrauch entsprechend an. 14 MB, nur für eine kleine Datenbank Operation ist schon heftig!
Methode 3 braucht nur einen Bruchteil an Scriptlaufzeit und mit gerade einmal 338 KB hält sich der RAM Verbrauch auch in Grenzen. ABER … so toll diese Methode auch ist, hat sie auch einen Haken. Auf Mietwebspace, besonders bei billigen Paketen, hat man in der Regel keine ausreichenden Rechte eine INFILE Operation auszuführen. Hier muss man sich dann mit einer der anderen Methoden begnügen. Im Zweifelfall kann man sehr einfach herausfinden ob man diese Methode wählen kann, indem man es einfach ausprobiert!