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.