Die Datenbank glücklich machen

Die Datenbank glücklich machen

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 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.

Inhalt

  1. Weniger Queries stellen
    1. Datensätze auswählen (SELECT)
  2. Datensätze hinzufügen (INSERT)
    1. Datensätze löschen (DELETE)
    2. Datensätze aktualisieren (UPDATE)
    3. Queries vermeiden
  3. Queries optimieren
    1. Transaktionen nutzen (InnoDB-only)
    2. Indexe richtig nutzen
    3. Sparsam sein
    4. Abschluss

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:

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 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_input_size) 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 xrstf_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 rex_article FROM rex_article a, bad_articles b [...]

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 FROM rex_article WHERE id IN (SELECT id FROM bad_articles)

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:

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.

Queries optimieren

Natürlich kann ich hier keine vollständige Behandlung aller Details zur Optimierung von Datenbank-Queries aufzählen und behandeln. Dafür gibt es entsprechende Fachliteratur und Leute, die sich nur damit befassen. Stattdessen möchte ich nur an ein paar Dinge erinnern, die schnell umgesetzt sind und die Datenbank erfreuen.

Transaktionen nutzen (InnoDB-only)

Gerade wenn man viele Queries ausführen muss (auch wenn es so klingen mag: auch unsere REDAXO-AddOns kommen nicht in allen Fällen mit einer einzelnen Datenbank-Abfrage aus1), können Transaktionen die Geschwindigkeit massiv erhöhen. Während eine Transaktion läuft (also ab dem COMMIT-Statement) muss sich MySQL nicht um Index-Aktualisierungen oder konkurrierende Zugriffe kümmern.

Für Tabellen, für die ein schneller Zugriff wichtig ist und Dinge wie Transaktionssicherheit nicht so sehr zählen, sollte man weiterhin MyISAM verwenden. Will man jedoch Transaktionen nutzen, muss im Falle von MySQL InnoDB zum Einsatz kommen.

1) Na ja, doch, wir könnten schon, wenn wir beim Scriptstart direkt ein Kreuzprodukt aller unserer Tabellen selektieren würden. Aber wo läuft schon PHP mit einem Speicherlimit von über 2 GB? Und wie viele Besucher wären dann gleichzeitig möglich?

Indexe richtig nutzen

Indexe kosten wenig Speicher, können aber die Performance dramatisch verbessern. Hierbei ist zu bedenken, dass Datenbanken niemals monolithische Gebilde sind, die sich niemals ändern dürfen. Oft stellt sich erst im Laufe der Entwicklung heraus, dass besonders oft Selektionen über ein bestimmtes Attribut durchgeführt werden. Dann ist es kein Beinbruch, erst im Nachhinein das Schema anzupassen und einen Index zu vergeben.

Mittels EXPLAIN kann man MySQL anweisen, den Ausführungsplan einer SELECT-Anfrage (und nur von SELECT-Anfragen!) zurückzugeben.

EXPLAIN SELECT * FROM t

In dem Ergebnis dieser Anfrage ist für jede beteiligte Tabelle eine Zeile enthalten. Sollte bei possible_keys ein NULL erscheinen, könnte das auf Probleme hindeuten. Interessant ist auch die Spalte rows, der man entnehmen kann, wie viele Datensätze MySQL betrachten muss, um das Ergebnis zu finden. Multipliziert man alle Angaben miteinander erhält man die Anzahl aller Datensätze, die betrachtet werden müssen (MySQL führt Kreuprodukte durch, daher sind bei zwei beteiligten Tabellen mit jeweils rows = 100 schon 100×100 = 10.000 Operationen notwendig).

Eine Ausnahme gibt es von der „Nutze Indexe, wo immer es Sinn macht!“-Regel: Attribute mit sog. „geringer Selektivität“ (d.h., Spalten, in denen wenige verschiedene Werte vorkommen, zum Beispiel bei Spalten wie online oder visible, die nur die Werte 0 oder 1 annehmen können) sollten keinen Index erhalten. Das gilt zumindest fast immer. Unter bestimmen Verteilungen und Queries kann auch hier ein Index Sinn machen.

Ein Beispiel verdeutlicht, warum ein Index bei binären Attributen kontraproduktiv ist. Dazu spielen wir Datenbank und führen eine Anfrage aus. Bildlich gesprochen haben wir vor uns zwei Bücher auf dem Tisch zu liegen: Eines mit den Daten und ein extra Inhaltsverzeichnis. Jetzt nutzen wir beide für die Anfrage:

Und jetzt entfernen wir den Index und legen damit das Inhaltsverzeichnis weg. Das große Datenbuch (das mit dem güldenen Einband und der eingravierten Signatur mit Grußspruch von Grace Hopper) liegt nun direkt vor uns. Let's go:

Quod erat demonstrandum. Wir erkennen, dass der Gewinn für die Methode ohne Index stark davon abhängt, wie die Daten verteilt sind. Hätten wir in 10.000 Datensätzen nur 5 mit attribut=1, wäre der Index wesentlich schneller. Hätten jedoch alle eine 1, ist der Index um ein Vielfaches langsamer. In diesem Fall ist es nicht „schlimm“, dass im EXPLAIN für dieses Attribute ein possible_keys = NULL auftaucht.

Sparsam sein

Der letzte Tipp, den ich in diesem Beitrag noch erwähnen möchte, ist, dass wir sparsam sein sollten in dem, was wir selektieren. Schreiben wir SELECT *, weil wir wirklich alle Daten brauchen oder einfach nur, weil es bequem ist? Man sollte immer bedenken, dass alle selektierten Daten im Endeffekt auch im Speicher von PHP landen.

Abschluss

Halten wir also fest:

Übrigens: Wer mit dem Gedanken spielt, Informatik zu studieren oder bereits studiert: Die Vorlesungen Datenbanken 1 und Datenbanken: 2 (Implementierungstechniken) von Prof. Saake an der Uni Magdeburg sind hervorragend.

Druckansicht