MySQL – Warum ist mein Query so langsam?

Wir alle kennen das: Kaum ist das neue Update auf den Produktivservern, wird der Sysadmin panisch und erzählt was von zu hoher Last auf der Datenbank. Was nun? Kaffee holen und zusehen wie alles zur Hölle fährt, wäre eine Möglichkeit – in diesem Artikel soll es um eine weitere gehen.

Ursachenforschung

Query finden

Plötzliche Performance-Probleme bei der Datenbank entstehen durch zu langsame Abfragen. Der erste Schritt ist also die entsprechende Abfrage zu finden. Manchmal hat man direkt einen Verdacht, aber normalerweise benötigt man jetzt das Slow-Query-Log. Das lässt man am besten mal für ein paar Minuten an und erstellt dann eine Zusammenfassung (das macht der Sysadmin).

War wir erhalten sieht so aus:

Das ist der vollständige Block für ein Query. In der Zusammenfassung stehen vermutlich die „Top 10“ mit dem schlimmsten auf Platz 1. Die wichtigsten Informationen, die wir hier finden können sind Häufigkeit (Count), Laufzeit (Exec time, 95%) und natürlich das abgeschickte Query (ganz unten).

Mit Häufigkeit und Laufzeit können wir entscheiden wie „schlimm“ die Abfrage ist. Es kann allerdings sein, dass die Häufigkeit lediglich 1 ist, aber das Query dennoch sehr oft auftritt. Dann variieren die Parameter für die Where-Bedingung zu stark und es findet keine Zusammenfassung statt. Im obigen Beispiel würde eine Abfrage mit town = 'München' separat aufgeführt.

Im Laufe der Zeit entwickelt man ein Gespür dafür, welche Laufzeiten/Häufigkeiten noch im Rahmen sind und bei welchen Handlungsbedarf besteht. Grundsätzlich gilt aber, dass alleine die Tatsache, dass es überhaupt im Slow-Query-Log steht, bereits bedeutet, dass es nicht optimal ist.

Was ein Query langsam macht

Das Query wäre also gefunden, im nächsten Schritt müssen wir somit klären warum diese Abfrage langsam ist.

Dafür gibt es drei Gründe:

  • Sie verwendet keinen Index (häufigste Ursache)
  • Die Operationen zur Ermittlung der Ergebnismenge sind zu komplex
  • Die Abfrage an sich ist zu komplex

Mit EXPLAIN vor der Abfrage lässt sich herausfinden in welche Kategorie wir fallen. Praktischerweise liefert das Log gleich eine Copy-Paste-Vorlage dafür mit. Was die Ausgabe dessen genau bedeutet kann hier nachgelesen werden.

Als Quintessenz gilt für die Zuordnung zu obigen Gründen folgendes:

  • Steht nichts unter possible_keys (also null) dann gibt es keinen Index, der der Abfrage helfen könnnte. Das resultiert dann zwangsläufig auch in type=ALL und es muss die gesamte Tabelle gelesen werden.
  • Steht in Extra „Using filesort“ oder „Using temporary“, muss die Datenbank mit einer physischen Datei oder einer Hilfstabelle die gefundenen Daten umorganisieren – was entsprechend Zeit kostet.
  • Trifft keine der beiden anderen Aussagen zu, aber das Ergebnis enthält mehrere Zeilen, dann ist die Abfrage an sich zu komplex.

 

Lösungen

Fehlender Index

Eigentlich ganz einfach: Wenn ein Index fehlt, dann müssen wir einen anlegen. Das stimmt wohl auch oft, aber es gibt leider auch Ausnahmen.

ODER

Folgende Abfrage wird Probleme machen:

Das EXPLAIN dazu sieht so aus:

type possible_keys key Extra
ALL PRIMARY, idx_town Using where

Obwohl für beide zu durchsuchenden Felder ein Index existiert, wird keiner verwendet. Das liegt daran, dass MySQL bei ODER-Bedingungen nur dann einen Index verwenden kann, wenn dieser in allen Oder-Zweigen vorkommt. Also zum Beispiel so:

type possible_keys key Extra
range PRIMARY, idx_town idx_town Using where

Abhilfe kann hier ein UNION schaffen. Damit zerlegen wir die Abfrage in zwei logische Teile, die separat bearbeitet werden können.

Die Antwort sieht zwar komplex aus, ist aber unkritisch.

select type possible_keys key Extra
PRIMARY range PRIMARY PRIMARY Using where
UNION ref idx_town idx_town Using where
UNION RESULT ALL

Die Zeile mit UNION RESULT muss uns nicht weiter kümmern, sie repräsentiert nur den Schritt, in dem die beiden Ergebnislisten kombiniert werden. Entscheidend ist, dass die beiden anderen einen Index verwenden.

Ist die Abfrage besonders einfach, übernimmt auch MySQL selbst den Union, verlassen sollte man sich aber nicht darauf.

possible_keys key Extra
PRIMARY, idx_town PRIMARY, idx_town Using union(PRIMARY,idx_town); Using where

Wir sehen den Union im Extra und die Zusammenführung der beiden Indizes.


Zu komplexe Operationen

ORDER BY

Auch diese Abfrage zeigt possible_keys, ist aber dennoch langsam:

type possible_keys key rows Extra
ref idx_town idx_town 2381 Using where; Using filesort

Einfache Aufgabenstellung: Alle Firmen in Berlin nach Name sortiert. Wir haben einen Index und die Treffer sind schnell gefunden. Leider sind es aber 2.381 Stück und die müssen jetzt sortiert werden. Hier kann ein mehrspaltiger Index Abhilfe schaffen.

Jetzt liefert obiges EXPLAIN:

type possible_keys key rows Extra
ref idx_town, idx_town_name idx_town_name 768 Using where

Indizes werden sortiert gespeichert (default ist ASC, wer DESC möchte muss das bei der Erstellung angeben). Wenn also jetzt die Datenbank mit dem neuen Index arbeitet sind die Ergebnisse bereits in der Reihenfolge, in der wie sie haben wollen. Die aufwendige Sortierung in einer Datei („Using filesort“) entfällt also.

GROUP BY

Durch Gruppierungen wird MySQL oft dazu gezwungen mit temporären Tabellen zu arbeiten.

type possible_keys key Extra
ref idx_town idx_town Using where; Using temporary; Using filesort

Hier kann ebenfalls durch einen mehrspaltigen Index optimiert werden.

type possible_keys key Extra
ref idx_town, idx_town_street idx_town_street Using where

Im Index steht ja genau welche Zeilen zu einem Suchbegriff gehören, die Gruppierung ist also bereits vorhanden. MySQL benutzt den ersten Teil des Indexes für die Suche und den zweiten Teil für die Gruppierung. Wichtig ist hier also die Reihenfolge der Spalten im Index. Anders herum (idx_street_town) wäre er wertlos.

LIKE

Dann suchen wir mal alle Bars in Berlin:

Ein EXPLAIN hilft uns hier leider nicht weiter:

type possible_keys key Extra
ref idx_town idx_town Using where

Was läuft hier schief?

Anfrage beginnt nicht mit einem Platzhalter

Als Platzhalter kennt MySQL das Prozentzeichen (%) für mehrere beliebige Zeichen und den Unterstrich (_) für ein beliebiges Zeichen. Die Auswirkungen auf die Performance sind in beiden Fällen identisch.

Beginnt unser Suchbegriff mit konstanten Zeichen wird MySQL ganz normal einen passenden Index verwenden und die Anfrage ausreichend schnell bearbeiten.

Platzhalter am Anfang des Suchbegriffs

Steht der Platzhalter allerdings zu Beginn des Suchbegriffs, sind unsere Indizes nutzlos.

Hier sollten wir uns also die Bedingungen für unsere Anfrage genauer ansehen. Möglicherweise können wir die Suche um eine indizierte Spalte ergänzen und die Anfrage damit beschleunigen.

Manchmal lässt sich auch bereits bei der Speicherung der Einträge viel optimieren. Wollen wir beispielsweise eine alphabetische Liste aller Firmen darstellen und dem Anwender den Anfangsbuchstaben festlegen lassen, wäre unser Query:

Bei derartigen Anfragen können wir viel Performance gewinnen, wenn wir ausnahmsweise redundante Daten speichern. In diesem Fall würden wir eine indizierte Spalte für den Anfangsbuchstaben (firstLetter) anlegen.

Die resultierende Anfrage ist dann wieder trivial und schnell:

Hier könnten wir sogar noch den netten Nebeneffekt erzeugen, dass auf diesem Feld Groß- und Kleinschreibung keine Rolle spielt.


Zu komplexes Query

Letztlich wird es auch immer wieder vorkommen, dass nicht mehr optimiert werden kann. Statistiken landen gerne in dieser Kategorie. Manchmal ist es unumgänglich viele Daten aus mehreren Tabellen zu aggregieren und sortiert auszugeben. Dauert die Abfrage dann 1 Sekunde, müssen wir in solchen Fällen damit leben.

Wir sollten dann allerdings durch gutes Caching dafür sorgen, dass sie nicht jede Sekunde ausgeführt wird und die Performance des Servers gefährdet.