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.