PHPGangsta - Der praktische PHP Blog

Um SQL kommt man in der heutigen Webentwicklung nicht herum, und jeder von uns kann SQL-Queries erstellen. Doch nicht jeder SQL-Query, der das korrekte Ergebnis zurückliefert, ist optimal aus Sicht der Performance. Hier ein kleines Beispiel:

Wir haben folgende Tabelle:

CREATE TABLE IF NOT EXISTS `Login` (
  `LoginId` int(11) NOT NULL AUTO_INCREMENT,
  `UserId` int(11) NOT NULL,
  `LoginDateTime` datetime NOT NULL,
  PRIMARY KEY (`LoginId`)
)

Nehmen wir an in der Tabelle sind 10.000.000 Zeilen. Es sind 10.000 verschiedene UserIds enthalten und jeder User hat im Schnitt 1.000 Logeinträge in den letzten 3 Jahren produziert. Die Tabelle enthält erstmal keine Indizes.

Die Aufgabe ist die folgende: Hole alle Logeinträge die in den letzten 30 Minuten stattgefunden haben.

Die Queries

Ein Query der diese Aufgabe erfüllt ist der folgende:

SELECT *
FROM Login
WHERE DATE_ADD(LoginDateTime, INTERVAL 30 MINUTE) > NOW();

Ein anderer Query sieht fast genauso aus und gibt das selbe Ergebnis zurück:

SELECT *
FROM Login
WHERE DATE_SUB(NOW(), INTERVAL 30 MINUTE) < LoginDateTime;

Bitte beide Queries genau anschauen und überlegen welcher von beiden besser ist.

Beide Queries dauern zwischen 1 und 1.4 Sekunden, das ist natürlich viel zu viel.

Nun mit Index

Also fügen wir einen Index hinzu für das Feld das im WHERE vorkommt:

ALTER TABLE `Login` ADD INDEX ( `LoginDateTime` );

Der erste Query benötigt nach wie vor zwischen 1 und 1.4 Sekunden, der zweite jedoch nur noch 0.0006 Sekunden. Warum ist der zweite Query so viel schneller als der erste? Beide sind quasi gleich, haben im WHERE Statement das Feld LoginDateTime, auf dem ein Index liegt, und es werden die MySQL-Datumsfunktionen NOW() und DATE_SUB() bzw. DATE_ADD() genutzt.

Ein Index allein reicht nicht, er muss auch genutzt werden.

Warum nun ist der zweite Query besser?

Werfen wir erneut einen Blick auf die beiden Abfragen.

Das Problem des ersten Queries ist die Nichtausnutzung des LoginDateTime-Index. In der WHERE-Bedingung ist sowohl die linke Seite durch das DATE_ADD() dynamisch und muss berechnet werden, der rechte Teil besteht aus der MySQL-Funktion NOW(). Demnach ist es nicht möglich auf den neu hinzugefügten Index zuzugreifen, dem MySQL Server bleibt nichts anderes übrig als für alle 10.000.000 Zeilen diese Datumsberechnung durchzuführen.

Die zweite Abfrage jedoch vergleicht einen einmalig berechneten Wert (DATE_SUB(NOW()..)) mit der existieren Spalte LoginDateTime, auf der ein Index liegt. Dadurch kann dieser genutzt werden und die Abfrage ist rasend schnell.

Die Ausführungspläne

Hier zum Vergleich nochmal die beiden EXPLAINS der untersuchten Queries:

Hier sieht man das oben beschriebene Prozedere: Der erste Query kann keinen Key (sprich Index) nutzen und muss alle (ALL) Zeilen durchsuchen, was in diesem Testfall 10 Millionen sind. Der zweite Query nutzt den LoginDateTime Key und muss mit deutlich weniger Zeilen arbeiten.

Je nachdem wie oft dieser Query aufgerufen wird und wie groß die Tabelle ist auf der gearbeitet wird, kann solch eine kleine Umstellung in der WHERE Bedingung einen großen Unterschied machen, nämlich ob und wenn ja welcher Index genutzt wird.

Das tückische dabei ist dass man sich häufig keine Gedanken darüber macht. Anfangs sind die Tabellen klein und die Queries schnell. Mit den Monaten oder Jahren füllen sich die Tabellen und die Software wird immer langsamer. Mit Hilfe des MySQL Slow Query Logs oder Profilern kommt man dem Problem dann schnell auf die Schliche. Ideal ist es natürlich wenn man selbst merkt dass die Software langsamer wird, zum Beispiel mit Hilfe von regelmäßigen und automatisierten Performance-Tests auf dem Live-System.