MySQL – Wie bekomme ich möglichst effizient und schnell viele Daten in die Datenbank?

Schreibzugriffe in der Datenbank sind immer kostenintensiv, das hat jeder schon mehrfach gehört. Der Ausweg beim INSERT ist denkbar einfach: Mehrere Datensätze pro Anfrage abschicken. Aber wo ist da die Grenze? Und wie genau geht das bei Updates? In diesem Artikel werde ich mich genau damit befassen.

Eine Anmerkung noch bevor es losgeht: Wer nur 100 Datensätze einfügen will, der muss sich um Performance keine Gedanken machen. Im schlimmsten Fall dauert’s ein paar Minuten. Interessant wird es so ab 10.000 Datensätzen, wenn der Unterschied mehrere Stunden sind.

Caching beim INSERT vieler Datensätze

Wir haben also unsere Daten vorbereitet (heißt sie liegen als Objekte in einem Array vor) und wollen sie jetzt in die Datenbank einfügen. Ich verwende den Helfer am Ende des Beitrags, der das Caching übernimmt.

$db   = new \PDO('mysql:dbname=testdb;host=127.0.0.1', 'root', '123');
$data = [ /* ... */];

$sql     = 'INSERT IGNORE INTO table (id, zip, name, citizens) VALUES ';
$dbCache = new DbCache($db, $sql, 500, true);

foreach ($data as $entry) {
	$dbCache->add('(null,'.$db->quote($entry->zip).','.$db->quote($entry->name).','.((int)$entry->citizens).')');
}

$dbCache->flush();

$db = new \PDO('mysql:dbname=testdb;host=127.0.0.1', 'root', '123');

$data = [ /* ... */];

$sql = 'INSERT IGNORE INTO table (id, zip, name, citizens) VALUES ';

$dbCache = new DbCache($db, $sql, 500, true);

foreach ($data as $entry) {

$dbCache->add('(null,'.$db->quote($entry->zip).','.$db->quote($entry->name).','.((int)$entry->citizens).')');

}

$dbCache->flush();

Den Cache lassen wir auf 500 Einträge anwachsen, bevor tatsächlich etwas an die Datenbank geschickt wird. Die Menge findet sich durch ausprobieren und wird irgendwo zwischen 200 und 2000 liegen.

Irgendwann wird die SQL-Anfrage zu lang und die Datenbank steigt mit einem Out-Of-Memory-Fehler aus. Oberhalb von 500 Einträgen gleichzeitig werden wir aber ohnehin kaum noch eine Beschleunigung sehen.

Wie schnell obiges Skript sein kann, zeigt ein Beispiel aus der Praxis:

Es werden 1.400 Einträge auf einmal geladen, etwas umgewandelt und dann zu 500er Paketen in zwei neue Tabellen eingefügt. Der Speicherverbrauch um die 1.400 Datensätze als Array vorzuhalten ist mit 1 MB geradezu lächerlich. Die Geschwindigkeit mit 5.688 Einträgen pro Sekunde ermöglichte hier die Konvertierung von über 9 Millionen Datensätzen in einer knappen halben Stunde.

Caching beim UPDATE

Um bei einem INSERT von einem Datensatz auf mehrere zu erweitern benötigen wir nur ein Komma und zwei Klammern. Beim UPDATE ist das leider nicht so einfach – aber möglich.

$db   = new \PDO('mysql:dbname=testdb;host=127.0.0.1', 'root', '123');
$sqlPrefix = 'UPDATE table SET ';

$data = [ /* ... */];
$up = [];
foreach ($data as $entry) {
	$up[$entry->id] = 'IF(id='.$entry->id.','.$db->quote($entry->newValue).',';

	if (count($up) >= 200) {
		$sqlSet   = ' col1 = '.implode('', $up).' col1'.str_repeat(')', count($up));
		$sqlWhere = ' WHERE id IN ('.implode(',', array_keys($up)).')';
		$db->query($sqlPrefix.$sqlSet.$sqlWhere);
		$up = [];
	}
}

$db = new \PDO('mysql:dbname=testdb;host=127.0.0.1', 'root', '123');

$sqlPrefix = 'UPDATE table SET ';

$data = [ /* ... */];

$up = [];

foreach ($data as $entry) {

$up[$entry->id] = 'IF(id='.$entry->id.','.$db->quote($entry->newValue).',';

if (count($up) >= 200) {

$sqlSet = ' col1 = '.implode('', $up).' col1'.str_repeat(')', count($up));

$sqlWhere = ' WHERE id IN ('.implode(',', array_keys($up)).')';

$db->query($sqlPrefix.$sqlSet.$sqlWhere);

$up = [];

}

Wir verwenden hier die IF-Struktur von MySQL, die folgende Syntax hat:

IF ( condition , then , else )

1	IF ( condition , then , else )

Als Bedingung nutzen wir die ID des Eintrags und fügen im else immer weitere IF-Konstrukte ein. Am Ende ergänzen wir die Spalte selbst (col1) für alle Einträge die wir nicht aktualisieren möchten. Abschließend benötigen wir noch die richtige Anzahl an schließenden Klammern. Mit einem WHERE sorgen wir noch dafür, dass nur relevante Einträge von der Datenbank geladen werden.

Da wir hier sehr viel mehr Logik im Query erzeugen, wird die Datenbank sehr viel früher Speicherprobleme bekommen, als bei einem INSERT – daher die geringere Menge von 200 pro Request.

Du findest das ist ultra-hässliches SQL? Stimmt – aber es ist auch ultra-schnell!

Mehr Performance beim SELECT

Wie oben im Beispiel schon gesehen ist der Speicherverbrauch eines Datensatzes nicht sonderlich der Rede wert. Daraus resultiert, dass wir möglichst viele Einträge gleichzeitig laden. Wenn die Datenbank die ersten 100 Zeilen gefunden hat, dann sind die nächsten 100 nur minimal mehr Aufwand. Insbesondere bei komplexeren Queries ist hier einiges an Performance zu holen.

Einen weiteren Geschwindigkeitsschub bekommen die Abfragen, wenn wir immer nur LIMIT 0,2000 im Gegensatz zu LIMIT 50000,2000 verwenden. Das heißt wir benutzen eine WHERE-Bedingung um den Start zu bestimmen und nicht LIMIT.

LIMIT Anweisungen mit einem OFFSET ersparen der Datenbank keine Arbeit, sie muss dennoch alle möglichen Ergebnisse finden und sortieren. Lediglich die Rückgabe beginnt später. Diese Art von Abfrage wird somit zunehmend langsamer, je größer das OFFSET wird.

SELECT * FROM table
WHERE id > ?
ORDER BY id ASC
LIMIT 2000

SELECT * FROM table

WHERE id > ?

ORDER BY id ASC

LIMIT 2000

Wichtig: Das ORDER muss kostenlos sein! Wir sortieren hier nach dem Primärschlüssel, der ohnehin aufsteigend sortiert ist. Genau genommen ist die Sortieranweisung hier also überflüssig und dient nur als Absicherung.

Im Skript merken wir uns die ID des letzten Eintrags, den wir bearbeiten und nutzen sie als Startpunkt für die nächste Abfrage.

Kleiner Helfer für die Queries: DbCache

Hier noch die Klasse zum Cachen von INSERT Requests.

class DbCache
{
	public $lastQuery;

	private $db, $limit, $sql, $cache = [], $auto;

	/**
	 * @param \PDO   $db
	 * @param string $sqlPrefix
	 * @param int    $limit
	 * @param bool   $autoFlush
	 */
	public function __construct(\PDO $db, $sqlPrefix, $limit, $autoFlush = false)
	{
		$this->db    = $db;
		$this->limit = $limit;
		$this->auto  = $autoFlush;
		$this->sql   = $sqlPrefix;
	}

	/**
	 * @param string $query
	 */
	public function add($query)
	{
		$this->cache[] = $query;

		if ($this->auto) {
			$this->softFlush();
		}
	}

	/**
	 * Flush cache and query database.
	 */
	public function flush()
	{
		if (empty($this->cache)) {
			return;
		}

		// save query for logging, if something goes wrong
		$this->lastQuery = $this->sql.implode(',', $this->cache);

		$this->db->query($this->lastQuery);
		$this->cache = [];
	}

	/**
	 * Flush cache if limit is reached.
	 */
	public function softFlush()
	{
		if (count($this->cache) >= $this->limit) {
			$this->flush();
		}
	}
}

class DbCache

{

public $lastQuery;

private $db, $limit, $sql, $cache = [], $auto;

/**

* @param \PDO $db

* @param string $sqlPrefix

* @param int $limit

* @param bool $autoFlush

public function __construct(\PDO $db, $sqlPrefix, $limit, $autoFlush = false)

{

$this->db = $db;

$this->limit = $limit;

$this->auto = $autoFlush;

$this->sql = $sqlPrefix;

}

/**

* @param string $query

public function add($query)

{

$this->cache[] = $query;

if ($this->auto) {

$this->softFlush();

}

/**

* Flush cache and query database.

public function flush()

{

if (empty($this->cache)) {

return;

}

// save query for logging, if something goes wrong

$this->lastQuery = $this->sql.implode(',', $this->cache);

$this->db->query($this->lastQuery);

$this->cache = [];

}

/**

* Flush cache if limit is reached.

public function softFlush()

{

if (count($this->cache) >= $this->limit) {

$this->flush();

}