Schnelles Einfügen neuer Datensätze

Neben dem Selektieren und dem Aktualisieren ist das Einfügen neuer Datensätze in eine Tabelle eine sehr häufig durchgeführte Operation. Ein sehr häufiger Anwendungsfall ist es, dass nicht nur ein paar einzelne Neueinträge hinzugefügt werden, sondern gleich ein ganzer Rutsch neuer Datensätze hinzugefügt werden sollen. Dieser Beitrag soll einige Tipps geben, wie man neue Datensätze möglichst schnell einfügt und somit die Sperrzeiten der Tabellen möglichst gering hält, denn beispielsweise MyISAM unterstützt nur Table-Locks, wodurch die gesamte Tabelle für den Zugriff gesperrt ist. Fügt man dann mal nebenbei einige Tausend Datensätze hinzu, kann die Tabelle für einen recht langen Zeitraum gesperrt sein, denn Inserts und Updates sind in MySQL stets höher priorisiert als Selects (außer man verzögert das Einfügen explizit mit DELAYED oder LOW_PRIORITY). Es sollte demzufolge das Ziel sein das Einfügen so schnell wie möglich fertig zu bekommen, damit die Datenbank sich wieder mit dem Selektieren für Zwecke der eigentlichen Anwendung beschäftigen kann.

Jeder Datenbankspalte wird neben einem Datentyp ein Standardwert zugewiesen. Das hat auch einen guten Grund, denn das DBMS verifiziert diesen Wert beim Anlegen der Tabelle und weiß somit, dass er valide ist und zum Datentyp der Spalte passt. Wenn wir nun bei einem Insert neben variablen Spalten (z.B. Name eines Artikels, Eintragszeit) auch Spalten mit konstantem Initialwert haben (z.B. Anzahl der bisherigen Klicks auf einen Artikel (ist am Anfang immer 0)), sollten wir diese Konstanten nicht über die SQL-Anweisung nochmal mit angeben, sondern den Standardwert nutzen, indem wir diese Spalten in der Insert-Query weglassen.
Das erleichtert MySQL das Leben, weil es nun nicht mehr prüfen muss, ob der Wert überhaupt valide ist sondern es sofort den Standardwert eintragen kann.

Außerdem kennen anscheinend einige (unerfahrene) Entwickler die Fähigkeiten des Insert-Befehls nicht ganz. Bei einem solchen Code kann man keine große Performance erwarten:

//hier könnte genauso eine for-Schleife stehen, in der viele Datensätze eingefügt werden
$i = 0;
while(Bedingung ist true) {
  mysql_query("INSERT INTO tabelle
(ID, spalte1, spalte2) VALUES (LAST_INSERT_ID()+1,'wert1',".$i.")");
  $i++;
}

An diesem Stück Code gibt es mehrere Probleme: Einerseits wird in jedem Schleifendurchlauf ein Insert durchgeführt, was ein grob fahrlässiger Verstoß gegen einen wichtigen Grundsatz bei Datenbanken ist: Führe Queries in Schleifen nur aus, wenn dies absolut notwendig ist! Der zweite Fehler ist, dass die ID lediglich den Zweck der eindeutigen Identifizierbarkeit eines Datensatzes hat und somit zu einer Auto_Increment-Spalte gemacht werden kann und wir uns damit nicht mehr um diese Spalte beim Einfügen kümmern müssen.
Außerdem sehen wir, dass die spalte1 immer mit dem gleichen Wert gefüllt wird. Dafür sollten wir den Standardwert für diese Spalte per PHPMyAdmin oder SQL-Query auf wert setzen und können diese Spalte bei zukünftigen Einfügeoperationen dann einfach weglassen.

Der viel gröberen Schnitzer mit der Mehrfachausführung innerhalb der Schleife ist durch eine kleine Änderung des PHP-Codes erreichbar:

$i = 0;
$insert_parts = array();
while(Bedingung ist true) {
  $insert_parts[] = $i;
  $i++;
}
mysql_query("INSERT INTO tabelle (spalte2) 
VALUES (".implode("),(",$insert_parts).")");

Dadurch wird erreicht, dass wir erst alle Inserts in einem Array sammeln und dann mit nur einer Einfügeoperation alle Datensätze hinzufügen können. Somit braucht sich die Datenbank nur ein mal um das Einfügen kümmern und ist wesentlich weniger Zeit damit beschäftigt. Außerdem wurden die Spalten ID und spalte1 weggelassen. Diese werden nun einfach durch den Standardwert gefüllt – wir müssen uns darum nicht mehr kümmern.
Man könnte das übrigens auch über einen String lösen, der die Einzel-Inserts sammelt, aber ich finde die Lösung mit dem implode eleganter.

Jan hat 152 Beiträge geschrieben

16 Kommentare zu “Schnelles Einfügen neuer Datensätze

  1. langleY sagt:

    Oder wenn man mehrere tausend oder 10tausende oder sogar noch viel mehr einfügen möchte mit mehreren felder die angegeben werden die keinem standardwert entsprechen, könnte man das per LOAD DATA INFILE machen. vorher textdatei erstellen dann mit LOAD DATA INFILE in die tabelle speichern.

  2. Christoph sagt:

    Irgendwie ist dein Code falsch, in Opera 9 wird alles nach deinem letzten Code rot gefärbt. Fehlt da ggf. das letzte Anführungszeichen? 😉

    Sehr nett finde ich am Ende die Variante mit dem Implode. Wird nur bei sehr viel Daten schwer, wenn MySQL z.B. so eingestellt ist, dass zu große Queries nicht akzeptiert werden (BLOB/TEXT-Spalten können ja mal laaang werden).

  3. admin sagt:

    Upps, nicht nur in Opera 9 😉
    Ist nun behoben.

    Nunja, Blobs füge ich auch nicht mit dieser Variante dazu, aber man könnte es ja so machen, dass man mal ein Zwischen-Insert macht, wenn es zu viele Daten werden.

  4. Postaria sagt:

    Welche möglichkeiten hat man denn bei einem Mehrdimensionalen Array ?
    Unglaublich die Performance bei dem eindimensionalen Array.
    nur habe ich in meiner Tabelle 2 Spalten die ich befüllen muss.
    Hoffe auf schnelle Antwort bzw. Lösungsansatz.

    Danke im Vorraus

  5. admin sagt:

    Bei nem mehrdimensionalen Array hilft einfach eine Zusatzfunktion, in der ein foreach drin ist, mit dem das Array durchlaufen wird und dann der String mit den einzelnen Tupeln zusammengebaut wird.
    Letztendlich macht ja implode() nix anderes 😉

  6. Postaria sagt:

    Also damit ich das richtig verstanden habe:
    Array
    (
    [0] => Array
    (
    [0] => 1
    [1] => 2
    [2] => 3
    )

    [1] => Array
    (
    [0] => a
    [1] => b
    [2] => c
    )

    )
    Soll eingefügt werden in Tabelle zahlenbuchstaben
    wobei die zahlen in die Spalte Zahlen kommen und Buchstaben in die Spalte Buchstaben.

    wenn ich das ganze ja zusammenführe kann ich ja immer nur eine Spalte mit implode ansprechen
    sprich mein insert befehl würde lauten:
    Insert into zahlenbuchstaben (Zahlen,Buchstaben) Values (1,a,2,b,3,c);
    das geht doch so glaube ich nicht oder ?

  7. admin sagt:

    Stimmt, das geht ttsächlich schlecht. Allerdings baut man eigentlich so auch kein mehrdimensionales Array auf, sondern da packt man alle zusammengehörigen Daten in eine Ebene
    Array
    (
    [0] => Array
    (
    [0] => 1
    [1] => a
    )

    [1] => Array
    (
    [0] => 2
    [1] => b
    )

    [2] => Array
    (
    [0] => 3
    [1] => c
    )
    )

    Und dann ist die implode()-Funktion auch ganz einfach.

  8. Postaria sagt:

    hmm .. also irgendwie klappt es diesmal aber nicht so wie mit nur einem eindimensionalen Array…
    meine Struktur dient nur zu testzwecken:

    Bearbeitungsdauer für 8000 Einträge : 38 Sekunden

    Oder hatte ich dich anders verstanden ?

  9. Postaria sagt:

    $i=1;
    while($i „;
    $i++;
    }
    foreach ($fa as $key){
    $test=implode(„,“,$key);
    $test2[] =“($test)“;
    }
    $test3=implode(„,“,$test2);

    $sql= „INSERT INTO test (FileID,SpielDatum) VALUES „.$test3;

  10. Postaria sagt:

    Super gut bin echt zufrieden !
    Fülle nun über 100.000.000 Einträge innerhalb kürzester Zeit in die DB.
    Deine Methode hat mir echt geholfen !
    Auf jedenfall besser als in irgendeinem Forum !

  11. Jonathan sagt:

    Würde mich an dieser Stelle freuen, wenn der entsprechende Code, wie Postaria, schrieb, für mehrere Felder gezeigt würde. In meinem Anwendungsbeispiel sind es beispielsweise vier.

    Danke!

  12. Jonathan sagt:

    $query = mysql_query(„SELECT id, name, text, datum FROM tabelle1“);

    while($row = mysql_fetch_assoc($query))
    {
    $id[] = $row[„id“];
    $name[] = $row[„name“];
    $text[] = $row[„text“];
    $datum[] = $row[„datum“];
    }

    So an dieser Stelle fehlt praktisch nur der INSERT. Ich möchte folgende Daten in eine Tabelle einspielen mit denselben Feldern, sprich id, name, text, datum. Der einfachheithalber nennen wir sie jetzt mal tabelle2.

    Wie genau sähe der INSERT-Befehl aus? Schon mal vielen Dank!

  13. admin sagt:

    Wenn Du wirklich lediglich Datensätze von einer in eine andere Tabelle kopieren möchtest, brauchst Du gar kein PHP.
    INSERT INTO tabelle 2 SELECT id, name, text, datum FROM tabelle1

    Ansonsten:
    $ins_parts = array();
    while($row = mysql_fetch_assoc($query))
    {
    $ins_parts[] = „‚“.$row[‚id‘].“‚,'“.$row[’name‘].“‚,'“.$row[‚text‘].“‚,'“.$row[‚datum‘].“‚“;
    }
    mysql_query(„INSERT INTO tabelle2 (id,name,text,datum) VALUES („.implode(„),(„,$ins_parts).“)“);

    Ungetestet.

  14. Jonathan sagt:

    Abgesehen davon, dass jetzt die PHP-Fehlermeldung

    Got a packet bigger than ‚max_allowed_packet‘ bytes kommt, geht’s 😉

  15. Efebdevout sagt:

    Hallo Carsten,
    irgendwie stehe ich auf dem Schlauch. Ich wollte das SQL in einer Schleife vwrwenden, aber PL/SQL steigt immer nach der ersten Iteration aus. Habe ich etwas Entscheidendes übersehen?
    Viele Grüße,
    Sabine

    SET SERVEROUTPUT ONBEGIN
    FOR rec IN (
    SELECT rownum zeile
    FROM dual
    CONNECT BY LEVEL <= 100
    )
    LOOP
    DBMS_OUTPUT.PUT_LINE(rec.zeile);
    END LOOP;
    END;/

Eine Antwort schreiben

Ihre E-Mail-Adresse wird nicht veröffentlicht. Benötigte Felder sind markiert mit *

You may use these HTML tags and attributes: <a href=""> <blockquote cite=""> <pre lang=""> <b> <strong> <i> <em>