In einer Applikation kann es an vielen Stellen zu Geschwindigkeits-Engpässen kommen. In der kurzen Zeit, in der ich nun in der kommerziellen Software-Entwicklung tätig bin, habe ich einige Tricks aufgeschnappt, um den Umgang mit SQL-Datenbanken effizienter zu gestalten.

Wirbel um SQL-Datenbanken gab es schon seit dem es sie gibt, jedoch hat die Kritik an Speichersystemen wie MySQL, in einer Zeit, in der NoSQL-Datenbanken im Trend liegen, deutlich zugenommen. Ein Teil der Kritik mag berechtigt sein, jedoch lassen sich auch viele Punkte auf Situationen zurück führen, bei denen SQL-Datenbanken nicht effizient gehandhabt werden, oder die Problem-Domäne mit anderen Speichermedien deutlich besser abgebildet werden kann (Graphendatenbanken, Triplestores, …). Auf eines lässt Kritik aber auf jeden Fall immer schließen, nämlich dass SQL-Systeme noch immer stark in Verwendung sind, sonst würde ja keiner über sie nörgeln.

An dieser Stelle will ich euch nun einige Techniken zeigen, die euch helfen sollen, Queries zu beschleunigen.

*) Indizieren
Indexe ermöglichen das Durchsuchen einer Tabelle zu beschleunigen. Fragt ihr eine Spalte in einer SQL-Query ab (WHERE-Klausel), die keinen Index besitzt, muss die Datenbank-Engine jede Reihe in eurer Tabelle überprüfen. Das Suchen wird also mit jeder weiteren Reihe langsamer. Besitzt die Spalte jedoch einen Index, wird ein Eintrag in einer speziellen Datenstruktur angelegt, welcher von diesem Index auf die Spalten zeigt, zu dem der Index gehört. Ein solcher “Lookup” ist also um einiges schneller, als jede Reihe überprüfen zu müssen.
Es gibt Datentypen, welche Indexe in manchen SQL-Engines nicht unterstützen. Ich hatte das Problem mit einer Tabelle, in der jede Reihe eine Link-Spalte besaß. Diese Link-Spalte war vom Typ TEXT, da Links oft weit über VARCHARs hinausgehen können. Es durften nur neue Elemente in die Tabelle gelangen, deren Link-Feld es noch nicht gab. Die Link-Spalte konnte jedoch wegen des TEXT-Typs keinen Unique-Index besitzen. Die erste Umsetzung bestand darin, bei jedem Insert auf Einzigartigkeit zu überprüfen, e.g.
“INSERT INTO tabelle1 (info, link)
SELECT :info, :link FROM DUAL
WHERE NOT EXISTS (SELECT * FROM tabelle1 WHERE link = :link LIMIT 1)
LIMIT 1;”
Diese Lösung funktionierte, ist aber schnell an ihre Grenzen gestoßen, da dieses Statement mit jedem Eintrag in der Tabelle länger dauert.
Die Abhilfe verschaffte ein Hash-Feld. Anstatt die Link-Spalte selbst Unique machen zu wollen, wird der Inhalt jedes Link-Feldes in einen Hash verwandelt, welcher einen Datentyp hat, der leicht indiziert werden kann. Folgende Spalte wurde hinzugefügt: `linkhash` BINARY(20) mit einem Unique-Index. Nachdem es hinzugefügt wurde, musste es natürlich mit dem gehashten Wert des Link-Feldes befüllt werden.
“UPDATE tabelle1 SET linkhash = UNHEX(SHA1(tabelle1.link));”
Wie man anhand des Statements sieht, wird ein SHA1-Hash des Links berechnet und in einer effizienten Binär-Form gespeichert.
Das Insert-SQL vereinfachte sich wie folgt:
“INSERT IGNORE INTO tabelle1 (info, link, linkhash)
VALUES (:info, :link, UNHEX(SHA1(:link)));”
Da das Linkhash-Feld einen Unique-Index besitzt, wird nur ein neuer Eintrag in der Tabelle angelegt, wenn der Hash des Links einzigartig ist. Diese Umstellung hat einen immer langsamer werdenden Vorgang wieder in den unteren Millisekunden-Bereich beschleunigt.
Trotzdem ein Wort der Warnung. Indexe erhöhen den verursachten Datenaufwand, da diese eben weitere Datenstrukturen anlegen müssen. Zudem können Update-Statements langsamer werden, da der Hash bei jedem Update neu berechnet und in den Index-Datenstruktur gespeichert werden muss. Daher empfiehlt es sich Indexe auf Spalten und Tabellen anzuwenden, welche hauptsächlich durchsucht und weniger upgedatet werden.

*) Aggregates
Aggregates sind Funktionen wie zum Beispiel AGV, COUNT und SUM. Angenommen wir haben einen Webshop und die Tabellen “personen”, “produkte” und eine M:M Tabelle zwischen “personen” und “produkte”, welche speichert, wer wann was gekauft hat. Nun wollen wir auf jeder Produkt-Seite einen Zähler anzeigen, wie oft dieses Produkt schon gekauft wurde. Ein entsprechendes SQL könnte so aussehen:
“SELECT COUNT(*) FROM personen_produkte WHERE produkt_id = :id;”
Auch hier könnten wir wieder Indizes auf produkt_id setzen, jedoch müsste die SQL-Engine trotzdem immer wieder alle gefundenen Einträge zusammen zählen.
Hier hilft es oft, den aktuellen Stand zu “cachen”. Anstatt immer wieder neu zu zählen, fügen wir in der Tabelle “produkte” eine neue Spalte hinzu, welche immer den aktuellen Zähler-Stand für dieses Produkt beinhaltet. Gedanklich passt das Feld sogar sehr gut zu der “produkte”-Tabelle. Kauf nun jemand das Produkt, wird neben dem Eintrag in der M:M-Tabelle zusätzlich der Zähler inkrementiert. Dies muss unter der Verwendung von Datenbank-Triggern nicht einmal zusätzlichen Programmier-Aufwand bedeuten. Nun können wir für den Zähler einfach die entsprechende Reihe abfragen:
“SELECT zaehler FROM produkte WHERE id = :id;”
Vorsicht ist bei dieser Lösung trotzdem geboten. Wird das Produkt theoretisch hunderte Mal in der Sekunde gekauft, können Trigger diesen Prozess verlangsamen. Man muss sich fragen, muss ich den Zähler öfters anzeigen, oder muss ich öfters einen neuen Einkauf registrieren und wann rentiert sich die eine, oder andere Methode. Natürlich kann man Zähler auch anders Cachen, zum Beispiel auf Anwendungsebene, es soll aber als Beispiel für alle möglichen Fälle dienen.

*) Batch-Processing
Mit dieser Optimierung sind wir nun auf der Anwendungs-Seite gelandet. Latenzen entstehen ja nicht nur durch die Datenbank-Operationen selbst, sondern auch durch die Verbindungen zwischen Client/Server und Server/Datenbank. Durch Batch-Processing lassen sich mehrere Queries auf einmal zum Server übertragen. Hier kommt es auf die Library an, mit welcher man die Datenbank benutzt.
JDBC verfügt über die Möglichkeit, Batches zusammen zubauen und auf einmal zu versenden. Wer mehr zu JDBC lesen will, findet hier einen guten Überblick (http://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm).
Meines Wissensstandes nach verfügt PDO nicht über ein natives Batch-Feature. In diesem Fall kann man aber immer noch SQL zusammenstoppeln. Ein Beispiel lässt sich durch Pseudo-Code (nicht PDO) verdeutlichen:

1)
var items = [{title: “Title 1”}, {title: “Title 2”}, …];
for (item in items) {
var sql = “INSERT INTO tabelle 1 (title) VALUES (:title);”
connection.execute(sql, item);
}

2)
var items = [{title: “Title 1”}, {title: “Title 2”}, …];
var items_sql = [];
var sql = “INSERT INTO tabelle 1 (title) VALUES “;
for (item in items) {
items_sql.push(“(:title)”);
}
sql += items_sql.join(“, “) + “;”; // “INSERT INTO tabelle 1 (title) VALUES (:title), (:title), …”
connection.execute(sql, items);

In Fall 1) rufen wir immer wieder execute auf, was jedes Mal eine Netzwerk-Latenz auslöst. Dies können wir durch Fall 2) vermeiden. Wir bauen ein großes SQL und senden es nur einmal zur Datenbank. Durch dieses Verfahren habe ich Laufzeiten schon um 90% verringern können. Es gibt jedoch auch Datenbanken, welche in der Applikation embedded sind (läuft im selben Prozess, hat also keine Netzwerk-Latenz). Hier lassen sich natürlich geringere Verbesserungen erzielen.

Ich hoffe diese Tipps werden euch von Nutzen sein. Mir haben sie auf jeden Fall geholfen. Es gilt jedoch: es handelt sich hier um anekdotische Evidenz. Nur weil es für meine Anwendung funktioniert hat, muss es nicht für jede andere auch passen. Vielleicht ist eure Datenbank nicht das Bottleneck, sondern die Anwendung oder eine andere Schicht. Daher immer die Performance vor und nach den Änderungen vergleichen und gegebenen Falls rückgängig machen. Es heißt ja auch “Premature optimization is the root of all evil”.