27. Oktober

von Christoph Ein Kommentar

Im ersten Teil von “Die Datenbank glücklich machen” ging es darum, weniger Queries zu stellen. Hier gehts nun darum, die noch vorhandenen Queries zu optimieren.

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 aus¹), 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 Kreuzprodukte 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:

• Schaue in den Index, finde Datensatz 1.
• Schaue in die Daten, hole Datensatz 1 und kopiere ihn.
• Schaue in den Index, finde Datensatz 3.
• Schaue in die Daten, hole Datensatz 3 und kopiere ihn.
• Schaue in den Index, finde Datensatz 4.
• Schaue in die Daten, hole Datensatz 4 und kopiere ihn.
• …
• Schaue in den Index, finde Datensatz X.
• Schaue in die Daten, hole Datensatz X und kopiere ihn.

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:

• Schaue in Datensatz 1. “Ah, eine 1.” Kopieren.
• Schaue in Datensatz 2. “Ah, eine 0.” Weiter.
• Schaue in Datensatz 3. “Ah, eine 1.” Kopieren.
• Schaue in Datensatz 4. “Ah, eine 1.” Kopieren.
• …
• Schaue in Datensatz X. “Ah, eine 1.” Kopieren.

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. Meist wird die Ergebnismenge einer Anfrage durch andere Bedingungen und Indexe schon soweit eingeschränkt, dass ein weiterer Index keinen Gewinn mehr bringen kann.

Die abgerufenen Daten richtig ansprechen

Wenn man nun schon seine Queries bis ins Letzte optimiert hat, sollte man nicht beim letzten Schritt noch auf die Nase fallen: Wie holt man sich am besten die Daten aus den Resultsets in die eigenen Variablen?

• Verwende keinesfalls mysql_result, da hier jedesmal das gesamte Resultset vertikal und horizontal durchlaufen werden muss. Das ist einer der größeren Patzer von REDAXO, leider. Wir arbeiten daran, mysql_result durch effizientere Methoden zu ersetzen.

• Versuche, auch mysql_fetch_array zu vermeiden. Man übersieht gern, dass diese Funktion die Daten doppelt in das Ergebnis-Array packt: Sowohl mit assoziativen Schlüsseln als auch mit numerischen. Aber wann braucht man schon einmal beide?

• mysql_fetch_assoc sollte (wenn man schon die MySQL-Funktionen nutzt) unser bester Freund sein. Schnell (so schnell wie auch mysql_fetch_row) und einfach zu verwenden.

• Wenn wir mit der Verarbeitung der Daten fertig sind, sollten wir sie mit mysql_free_result wieder aus dem Speicher entfernen. Gerade, wenn man nur ein kleines Rädchen in einem System ist (soll heißen, nur eines von vielen AddOns in einem CMS), kann man nie wissen, wie viel Speicher und Rechenzeit die anderen Komponenten um einen herum benötigen. Also räumt man gefälligst seinen eigenen Speicher wieder auf, bevor man die Kontrolle abgibt.

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:

• Nimm dir die Zeit, deine Queries [in Schleifen] zu optimieren! Für kleine Projekte ist jeder Code schnell (Grundregel der Informatik: Für kleine Eingaben ist jeder Algorithmus schnell). Wenn du deinen Code veröffentlichst, denke daran, dass andere vielleicht mehr Daten damit verwalten möchten.

• Nutze Indexe und Transaktionen!

• Selektiere nur, was du wirklich brauchst!

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

Das war’s dann auch für diese Woche mit der Moralkeule meinerseits.

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.

21. Oktober

von Christian Metzeler Kommentieren

Eine schlechte Angewohnheit vieler Website-Redakteure ist, Texte für Websites mit dem Office-Programm “Word” vorzubereiten und dann diese Texte per Copy&Paste in den Texteditor der Website einzugeben. Egal ob man mit Typo3, Redaxo oder einem anderen CMS arbeitet, wenn man von Word aus Texte einfach mit der Tastenkombination STRG-C herauskopiert und dann per STRG-V in den Texteditor reinkopiert, entstehen häufig merkwürdige Effekte und das Ergebnis sieht anders aus als gewünscht. Der Grund ist, das der Text beim Herauskopieren Formatierungsbefehle von Word sozusagen mitnimmt – und diese können vom Texteditor nicht immer und oft nur falsch interpretiert werden. Es entsteht unsauberer Quelltext auf der Website. Um das zu vermeiden, bieten sich drei Methoden an:

1. Via Texteditor (lokal)

Wenn man keine Internetverbindung hat und seine Website erstmal lokal befüllt bzw. Texte vorbereiten will, bietet sich der gute alte Texteditor an. Einfach auf dem Desktop > Rechte Maustaste > Neu > Textdatei eine neue .txt-Datei erzeugen und dort seinen Text reinschreiben bzw. reinkopieren. Beim Reinkopieren wird der Text von allen Word-Formatierungen befreit und man hat einen sauberen Text.

2. Via Wordoff (Web)

Wordoff ist eine ganz simple Website. Ein dickes Textfeld für den Input und ein Button “clean up” für die Arbeit. Also einfach den Word-Text reinkopieren, säubern lassen, und rauskopieren. Fertig. http://wordoff.org/

3. “Copy&Paste aus Word”-Funktion des CMS-Editors

Mehrere Texteditoren verfügen über eine ähnliche Funktion wie Wordoff: man markiert in Word den gewünschten Text, kopiert ihn und fügt ihn in den Texteditor via Klick auf einen entsprechenden Button ein. Dieser hat meist die Benennung “Word-Text einfügen”, “unformatierten Text einfügen” oder ähnlich. In diesem Fall wird der Text oft zunächst in ein Popup reinkopiert und dann beim Einfügen von allem Word-Unrat befreit – bei STRG-V geschieht dies nicht, also sollte man diese Funktion benutzen. Typo3 verfügt über eine Funktion “Formatierung entfernen”, dargestellt durch einen kleinen gelben Besen im Texteditor. Was der tut, kann man sich ja denken…

Und zur Handhabung des Texteditors tinymce im Internet Explorer 8 hier noch ein interessanter Beitrag.

9. Oktober

von Christian Metzeler Kommentieren

Ja, ich weiß, wir haben uns in den letzten beiden Wochen mit keinem Blogbeitrag zu Wort gemeldet. Das ist nicht gut. Der Grund ist einfach: wir werden aktuell von Arbeit nur so überrannt. Das ist natürlich gut und freut uns. Andererseits müssen wir eben ranklotzen, um Projekte abzuschliessen. Dadurch leiden dann diejenigen Tätigkeiten, die nicht zum absoluten Kern der Tätigkeit gehören. Denn auch ein Blogbeitrag will ausgedacht, vorbereitet, geschrieben, korrigiert, nochmal korrigiert, ggf. mit Bildern versehen und veröffentlicht werden. Dazu fehlt aktuell leider allen die Zeit. Aber wir wünschen zunächst unseren geneigten Lesern ein feines Wochenende und hoffen auf Besserung unserer Auslastung in der nächsten Zeit.