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:

# Query 1: 0.17 QPS, 0.82x concurrency, ID 0x0A6F5C at byte 866853
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.37
# Time range: 2014-11-24 09:00:02 to 10:08:32
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         98     692
# Exec time     98   3383s      2s      9s      5s      7s      1s      5s
# Lock time     98   223ms    78us     8ms   321us   568us   416us   260us
# Rows sent     99 135.16k     200     200     200     200       0     200
# Rows examine  83 237.82M 148.09k 470.02k 351.92k 440.37k  65.55k 345.04k
# Query size    97 800.57k   1.16k   1.16k   1.16k   1.14k       0   1.14k
# String:
# Hosts        127.0.0.1 (403/58%), 127.0.0.2 (289/41%)
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'table'\G
#    SHOW CREATE TABLE `table`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `table` WHERE 1 = 1 AND `table`.`town` = 'Berlin' ORDER BY `table`.`name` ASC LIMIT 200 OFFSET 233600\G

# Query 1: 0.17 QPS, 0.82x concurrency, ID 0x0A6F5C at byte 866853

# This item is included in the report because it matches --limit.

# Scores: V/M = 0.37

# Time range: 2014-11-24 09:00:02 to 10:08:32

# Attribute pct total min max avg 95% stddev median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count 98 692

# Exec time 98 3383s 2s 9s 5s 7s 1s 5s

# Lock time 98 223ms 78us 8ms 321us 568us 416us 260us

# Rows sent 99 135.16k 200 200 200 200 0 200

# Rows examine 83 237.82M 148.09k 470.02k 351.92k 440.37k 65.55k 345.04k

# Query size 97 800.57k 1.16k 1.16k 1.16k 1.14k 0 1.14k

# String:

# Hosts 127.0.0.1 (403/58%), 127.0.0.2 (289/41%)

# Users root

# Query_time distribution

# 1us

# 10us

# 100us

# 1ms

# 10ms

# 100ms

# 1s ################################################################

# 10s+

# Tables

# SHOW TABLE STATUS LIKE 'table'\G

# SHOW CREATE TABLE `table`\G

# EXPLAIN /*!50100 PARTITIONS*/

SELECT * FROM `table` WHERE 1 = 1 AND `table`.`town` = 'Berlin' ORDER BY `table`.`name` ASC LIMIT 200 OFFSET 233600\G

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:

SELECT * FROM company WHERE id > 500 OR town = 'Berlin';

1	SELECT * FROM company WHERE id > 500 OR town = 'Berlin';

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:

SELECT * FROM company WHERE (town = 'Nürnberg' AND id > 500) OR town = 'Berlin';

1	SELECT * FROM company WHERE (town = 'Nürnberg' AND id > 500) OR town = 'Berlin';

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.

SELECT * FROM company WHERE id > 500 
UNION
SELECT * FROM company WHERE town = 'Berlin';

SELECT * FROM company WHERE id > 500

UNION

SELECT * FROM company WHERE town = 'Berlin';

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.

SELECT * FROM company WHERE id = 17 OR town = 'Berlin'

1	SELECT * FROM company WHERE id = 17 OR town = 'Berlin'

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:

SELECT * FROM company WHERE town = 'Berlin' ORDER BY businessName ASC LIMIT 10

1	SELECT * FROM company WHERE town = 'Berlin' ORDER BY businessName ASC LIMIT 10

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.

ALTER TABLE company ADD INDEX idx_town_name (town, businessName);

1	ALTER TABLE company ADD INDEX idx_town_name (town, businessName);

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.

SELECT count(1), name FROM company WHERE town = 'Berlin' GROUP BY street;

1	SELECT count(1), name FROM company WHERE town = 'Berlin' GROUP BY street;

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:

SELECT * FROM company WHERE town = 'Berlin' AND name LIKE "%Bar%";

1	SELECT * FROM company WHERE town = 'Berlin' AND name LIKE "%Bar%";

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.

SELECT * FROM company WHERE name LIKE "Bar%";

1	SELECT * FROM company WHERE name LIKE "Bar%";

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.

SELECT * FROM company WHERE name LIKE "%Bar%";

1	SELECT * FROM company WHERE name LIKE "%Bar%";

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:

SELECT * FROM company WHERE name LIKE "A%";

1	SELECT * FROM company WHERE name LIKE "A%";

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:

SELECT * FROM company WHERE firstLetter = "A";

1	SELECT * FROM company WHERE firstLetter = "A";

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.