23. Oktober

von Christoph 5 Kommentare

Die meisten Websites sind Dank schneller Platten und schneller Datenbanken auf Entwickler-Rechnern (die sich im Prinzip nur damit befassen müssen, das aktuelle Projekt zu befeuern) fast immer “schnell genug”, um sie an den Kunden auszuliefern. Erst, wenn man sie auf 1und1, Strato, langsame Hoster (über meist auch ebenso langsame FTP-Verbindungen) deployed, fällt auf, dass die Performance doch nicht das hergibt, was sie lokal versprach.

In diesem Blog-Beitrag geht es um die Entlastung der Datenbank. Das ist natürlich nur ein Aspekt der Optimierung von Websites, aber leider einer, der allzu oft ignoriert wird. Sicher, ein einzelnes kleines SELECT tut nicht weh und liegt auf dem eigenen PC wahrscheinlich im Query Cache, aber wenn man falsch plant oder diese einfache Abfrage “aus Versehen” 1.000-mal ausführt, sieht die Sache plötzlich anders aus.

Wir befassen uns mit zwei Möglichkeiten, die Datenbank zu entlasten: Weniger Queries stellen und die Queries, die man stellt, zu optimieren.

Weniger Queries stellen

auch bekannt als “keine Queries in Schleifen ausführen”

Die Logik hinter dieser Maßnahme ist einfach nachzuvollziehen: je weniger Queries die Datenbank bearbeiten muss, desto besser. Ein häufiges Problem sind Queries, die man aus Bequemlichkeit in Schleifen ausführt, wie in diesem Beispiel:

$objects = array(1,4,6,82,189,200,201,400,401,402,403);
 
foreach ($objects as $object) {
    query("SELECT * FROM table WHERE id = $object");
}

Meist schleicht sich der folgende Gedanke ein:

Ach, wie viele Elemente ($objects) können das schon werden?.. Das wird nicht wehtun.

Oder etwas REDAXO-näher:

Ach, wie viele Sprachen wird meine Website schon haben? Mehr als 5 sicher nicht. Das passt schon…

Und genau das ist eben nicht der Fall. Wir haben in REDAXO schon Projekte mit 13 Sprachen umgesetzt; auf redaxo.de wurde extra ein AddOn veröffentlicht, das mehr als 15 Sprachen im Backend ermöglicht. Wer weiß, ob man die Sprachen nicht für etwas anderes missbrauchen möchte? Dann können aus den 4 Sprachen ganz schnell wesentlich mehr werden. Auch wenn es natürlich eine sehr gesunde Sache ist, abzuschätzen, wie viele Elemente man wahrscheinlich bearbeiten muss. Leider verschätzt man sich zu oft.

Sehen wir uns an, wie man Queries in Schleifen optimieren kann.

Datensätze auswählen (SELECT)

Unser böses Beispiel lautet wie folgt:

$objects = array(1,4,6,82,189,200,201,400,401,402,403);
 
foreach ($objects as $object) {
    $data[] = query("SELECT * FROM table WHERE id = $object");
}

Wir haben hier zwei Möglichkeiten: kommen die $objects auch aus der Datenbank, bietet sich in den meisten Fällen ein mehr oder weniger komplizierter Join an.

$data = query("SELECT * FROM table,objects WHERE table.id = objects.foo");

Sollten die $objects aus einer anderen Quelle stammen, sollte man lieber mit IN() arbeiten:

$data = query("SELECT * FROM table WHERE id = IN (".implode(',', $objects).")");

Wichtig ist dabei anzumerken: Ist $objects leer, schlägt die Abfrage fehl — MySQL verbietet IN-Klauseln mit leeren Mengen.

Soviel zum einfachsten Fall.

Datensätze hinzufügen (INSERT)

Das böse Beispiel sieht dem SELECT-Beispiel sehr ähnlich:

$objects = array(1,4,6,82,189,200,201,400,401,402,403);
 
foreach ($objects as $object) {
    $data[] = query("INSERT INTO table (a,id) VALUES (5,$object)");
}

Für dieses Beispiel gibt es ebenfalls wieder zwei Lösungsmöglichkeiten: Extended Inserts oder Insert-Select-Queries. Gehen wir davon aus, dass $objects eine Menge von Daten ist, die nicht aus einer anderen Abfrage konstruiert werden kann. Dann kann man das Problem wie folgt lösen:

$objects   = array(1,4,6,82,189,200,201,400,401,402,403);
$maxPerRun = 100;
$start     = 0;
 
do {
    $objectsThisRun = array_slice($objects, $start, $maxPerRun);
    $values         = array();
 
    foreach ($objectsThisRun as $object) {
         $values[] = '(5,'.$object.')';
    }
 
    if (!empty($values)) {
        query("INSERT INTO table (a,id) VALUES ".implode(',', $values));
    }
 
    $start += $maxPerRun;
    unset($values);
} while ($start < $objects);

Im Allgemeinen halte ich derartigen Code für unschön: Ich weiß nicht, wie groß meine Eingabemenge ist und muss daher, da ich im Zweifelsfall 1 Million Werte gleichzeitig einfügen möchte, die Menge segmentieren.

Den Wert für $maxPerRun kann man sicher für derartig einfache Werte (zwei Zahlen) gewaltig erhöhen, beispielsweise auf 10.000. Hier ist zu beachten, dass in $objectsThisRun immer eine Kopie mit $maxPerRun Elementen angelegt wird. Das kostet Speicher!

Sollten statt Zahlen Texte eingefügt werden, muss man noch beachten, dass MySQL ein Limit für die Größe für Anfragen (max_allowed_packet) hat. Bei einer XAMPP-Standardinstallation ist dieses Limit sogar auf gerade einmal 1 MB festgelegt. Persönlich halte ich 16 MB für realistischer. Bei 16 MB stehen für 10.000 Datensätze schon nur noch ca. 1,6 KB zur Verfügung. Schon dieser Beitrag hat bis zu den beiden Gedanken oben bereits 1.800 Zeichen. 1,6 KB sind also nicht viel. Ganz zu schweigen davon, dass es 16 MB kostet, eine 16 MB Query aufzubauen… Hier sollte man eine gute Balance zwischen zu vielen Werten (Überschreitung der max_allowed_packet-Größe) und zu wenigen (Effekt der Optimierung verschwindet) finden.

Wer die Balance nicht selber suchen möchte, kann seine Queries auch wie folgt aufbauen:

$objects = array(1,4,6,82,189,200,201,400,401,402,403);
$maxSize = 10*1024*1024; // 10 MB
$values  = array();
 
foreach ($objects as $object) {
    $values[] = '(5,'.$object.')';
    $size    += strlen('(5,'.$object.')');
 
    if ($size <= $maxSize) {
        query("INSERT INTO table (a,id) VALUES ".implode(',', $values));
        $size   = 0;
        $values = array();
    }
}
 
if (!empty($values)) {
    query("INSERT INTO table (a,id) VALUES ".implode(',', $values));
}
 
unset($values, $objects);

Wann immer möglich sollte man daher die Menge der Daten, die man einfügt, gar nicht erst ins PHP-Script laden müssen. In vielen Fällen kann die Menge ($objects) über eine andere Abfrage erzeugt werden.

An einem Beispiel möchte ich zeigen, wie man in REDAXO zum Beispiel die Artikel für eine neue Sprache hinzufügen kann (nicht, dass man das jemals müsste, da REDAXO die Arbeit ja übernimmt, aber es zeigt sehr schön, was möglich ist und wie man die Technik praktisch einsetzt).

REDAXO hat dazu zwei wichtige Tabellen: rex_article und rex_clang. In rex_article ist jeder Artikel n-mal enthalten, wobei n die Anzahl der im System vorhandenen Sprachen ist. Wenn wir eine neue Sprache $newLang hinzufügen, können wir die Artikelmenge wie folgt erweitern:

INSERT INTO rex_article (spalte_a,spalte_b,clang,name,catname)
    SELECT spalte_a,spalte_b,$newLang,name,catname
        FROM rex_article
        WHERE clang = 0

Hier selektieren wir alle Artikel der Sprache 0 (unsere Quellsprache, aus der wir kopieren möchten) und fügen diese Menge direkt in die Tabelle selbst ein. Hierbei selektieren wir nicht die Originalsprache, sondern an der entsprechenden Stelle (clang) schreiben wir direkt den Wert von $newLang in die Abfrage. Im Ergebnis haben wir nun alle Artikel einer Sprache kopiert.

Da die SELECT-Anweisung beliebig gestaltet sein kann, kann auf diese Weise ein Großteil der oft teuren einzelnen Queries beseitigt werden. Wir könnten hier auch mit rex_clang joinen, wenn wir in anderen Tabellen arbeiten. Das bietet sich zum Beispiel bei Dateien (rex_file) an, da diese nicht pro Sprache abgespeichert werden.

Datensätze löschen (DELETE)

Bei DELETE-Queries sieht die Situation ähnlich der beiden vorausgehenden aus. Für einfache Objektmengen kann wieder mit IN() gearbeitet werden. Für Mengen, die man aus Queries erstellen kann, kann wieder eine einzelne Abfrage ausgeführt werden.

Da ein DELETE-Query mit Joins etwas komplizierter ist, hier ein Beispiel dazu. Wir haben eine Tabelle mit Artikel-IDs und wollen alle Artikel von REDAXO löschen, deren ID in der anderen Tabelle (nennen wir sie bad_articles) auftauchen.

Eine Möglichkeit ist ein Subselect. Nicht sehr elegant und unnötig, aber möglich:

DELETE FROM rex_article WHERE id IN (SELECT id FROM bad_articles)

Stattdessen kann man auch aus Queries mit Joins löschen:

DELETE a FROM rex_article a, bad_articles b WHERE a.id = b.id

Hier ist wichtig, dass MySQL alle Datensätze aus allen beteiligten Tabellen löscht, es sei denn, man notiert direkt nach dem DELETE die Tabellen, aus denen gelöscht werden soll. Außerdem muss nach dem DELETE der Aliasname (a) verwendet werden, der auch nach dem FROM gegeben wurde. Ein

DELETE rex_article FROM rex_article a, bad_articles b [...]

wird daher nicht funktionieren.

Datensätze aktualisieren (UPDATE)

UPDATE-Queries sind die einzigen, die man meines Wissens nach nicht sonderlich gut optimieren kann. Jede Query kann nur eine Aktualisierung vornehmen, ein

UPDATE TABLE SET value = "foo" WHERE id = 5, value = "bar" WHERE id = 6

ist nicht möglich. Alternativen gibt es aber, wenn man wirklich viele UPDATE-Queries ausführen muss:

• DELETE + INSERT
• REPLACE INTO

Ich würde hier unbedingt zu DELETE + INSERT raten, da man auf diesem Wege je nach Datenbankstruktur nur 2 Queries ausführen muss (1x DELETE und 1x INSERT). Das REPLACE INTO würde für jeden Datensatz einzeln im Konfliktfall ein DELETE durchführen. Nutzt man es also als UPDATE-Ersatz für 100 Queries, werden im Hintergrund 100x DELETE und 100x INSERT ausgeführt — nicht gerade sonderlich gut optimiert

Queries vermeiden

Eine weitere Möglichkeit, Queries zu sparen, ist der Einsatz von Singletons. Meist kapselt ein einzelnes Objekt einen einzelnen Datensatz. Es macht keinen Sinn, für diesen Datensatz immer wieder neue Objekte in PHP zu erzeugen. Würde ein einzelnes sich selbst ändern und ein UPDATE durchführen, wären alle anderen Instanzen im Speicher obsolet. Neben diesem gewonnen Komforteffekt (dass man immer, wenn man ein Objekt erhält, garantiert eines erhält, dass dem Datenbank-Stand entspricht) spart man auch für jedes weitere Objekt mindestens eine Datenbank-Abfrage. Von dem gewonnen Speicher auf Seiten von PHP ganz zu schweigen.

Im nächsten Beitrag zu diesem Thema widme ich dann noch dem Kapitel des “Queries optimieren”, zu dem es ebenfalls einiges zu sagen gibt.