MySQL und MariaDB: Langsame Abfragen finden und mit Indizes beschleunigen
So finden Sie langsame MySQL- und MariaDB-Abfragen mit dem Slow Query Log, analysieren sie mit EXPLAIN und beschleunigen sie mit Indizes und Buffer Pool.
Wenn eine dynamische Website langsam antwortet, steckt die Ursache oft nicht im Webserver, sondern in der Datenbank. Eine einzige Abfrage ohne passenden Index kann bei jeder Seitenansicht Zehntausende Zeilen durchsuchen und die Serverzeit in die Höhe treiben. Das sehen Sie direkt an einer schlechten Time to First Byte. Dieser Artikel zeigt Schritt für Schritt, wie Sie langsame Abfragen in MySQL und MariaDB finden, verstehen und beschleunigen. Die Angaben stützen sich auf das MySQL-8.4-Referenzhandbuch und die MariaDB-Dokumentation.
Der Ablauf im Überblick
- Finden: Das Slow Query Log zeichnet Abfragen auf, die länger als ein Grenzwert dauern.
- Zusammenfassen:
mysqldumpslowoderpt-query-digestgruppieren gleichartige Abfragen und zeigen, welche am meisten Zeit kosten. - Verstehen:
EXPLAINzeigt, wie die Datenbank eine Abfrage ausführt und ob sie einen Index nutzt. - Beheben: Passenden Index anlegen oder die Abfrage umschreiben.
- Prüfen: Erneut messen, ob die Abfrage aus dem Log verschwindet.
Zusätzlich lohnt ein Blick auf die Speicherkonfiguration, vor allem auf den InnoDB Buffer Pool.
Schritt 1: Das Slow Query Log aktivieren
Das Slow Query Log ist in MySQL standardmässig ausgeschaltet. Sie können es zur Laufzeit ohne Neustart einschalten:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'FILE';
Damit die Einstellungen einen Neustart überstehen, tragen Sie sie zusätzlich in die Konfigurationsdatei ein, zum Beispiel unter [mysqld] in der my.cnf:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
Wichtige Variablen im Überblick:
| Variable | Standard | Bedeutung |
|---|---|---|
slow_query_log | aus | Schaltet das Log ein |
long_query_time | 10 Sekunden | Abfragen, die länger dauern, werden protokolliert. Minimum 0, Auflösung in Mikrosekunden |
slow_query_log_file | hostname-slow.log im Datenverzeichnis | Pfad der Logdatei |
log_queries_not_using_indexes | aus | Protokolliert zusätzlich Abfragen ohne Index |
min_examined_row_limit | – | Nur Abfragen, die mindestens so viele Zeilen untersuchen |
log_slow_admin_statements | aus | Protokolliert auch ALTER TABLE, OPTIMIZE TABLE usw. |
log_output | – | FILE, TABLE oder NONE |
Der Standardwert von 10 Sekunden ist für Websites viel zu hoch. Eine Abfrage, die bei jedem Seitenaufruf 0,5 Sekunden braucht, ist bereits ein grosses Problem. Beginnen Sie mit 1 Sekunde und senken Sie den Wert schrittweise, etwa auf 0,2 oder 0,1. Ein Wert von 0 protokolliert jede Abfrage. Das ist für kurze Analysen nützlich, erzeugt auf einem stark besuchten Server aber schnell sehr grosse Dateien.
log_queries_not_using_indexes findet Abfragen, die heute noch schnell sind, weil die Tabelle klein ist, aber mit wachsenden Daten langsam werden. Die Option kann viele Einträge erzeugen. Mit log_throttle_queries_not_using_indexes begrenzen Sie die Anzahl solcher Einträge pro Minute.
Besonderheiten bei MariaDB
MariaDB kennt dieselben Variablen. Ab Version 10.11 wurden sie umbenannt: slow_query_log heisst dort log_slow_query, long_query_time heisst log_slow_query_time und min_examined_row_limit heisst log_slow_min_examined_row_limit. Abfragen ohne Index protokollieren Sie wahlweise mit log_queries_not_using_indexes=ON oder indem Sie not_using_index in log_slow_filter aufnehmen. Die Standarddatei heisst auch hier hostname-slow.log im Datenverzeichnis.
Shared Hosting
Auf Shared Hosting haben Sie meist keine Rechte für SET GLOBAL. Manche Anbieter zeigen langsame Abfragen im Kundenpanel an, andere nicht. Für WordPress und andere CMS gibt es Debug-Plugins, die die Abfragen einer Seite mit Laufzeit auflisten. Auf eigenen Servern und VPS ist das Slow Query Log der zuverlässigere Weg.
Schritt 2: Das Log auswerten
Ein Slow Query Log enthält schnell Tausende Einträge. Lesen Sie es nicht von Hand, sondern lassen Sie gleichartige Abfragen zusammenfassen.
mysqldumpslow
mysqldumpslow wird mit MySQL ausgeliefert. Es ersetzt Zahlen durch N und Zeichenketten durch 'S' und fasst so Abfragen zusammen, die sich nur in den Werten unterscheiden.
# Die 10 Abfragen mit der höchsten Gesamtzeit
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# Die 10 häufigsten Abfragen
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
Die Sortierung wählen Sie mit -s: t (Abfragezeit), at (durchschnittliche Zeit, Standard), l (Lock-Zeit), r (gesendete Zeilen) oder c (Anzahl). -t N begrenzt die Ausgabe auf N Einträge, -g filtert nach einem Suchmuster.
pt-query-digest
pt-query-digest aus dem Percona Toolkit liefert ausführlichere Berichte:
pt-query-digest /var/log/mysql/slow.log
Das Werkzeug gruppiert Abfragen nach einem «Fingerprint» (Abfrage ohne konkrete Werte) und sortiert sie standardmässig nach der gesamten Ausführungszeit. Oben im Bericht steht ein Profil mit Rang, Gesamtzeit, Anteil an der Gesamtzeit, Anzahl Aufrufe und mittlerer Zeit pro Aufruf. Danach folgt für jede Abfrage ein Detailabschnitt mit Statistiken zu Ausführungszeit, untersuchten und gesendeten Zeilen sowie einer Beispielabfrage.
Worauf es ankommt: Eine Abfrage mit 50 ms, die 100 000 Mal am Tag läuft, kostet mehr Zeit als eine mit 5 Sekunden, die einmal nachts läuft. Optimieren Sie zuerst die Abfragen mit dem grössten Anteil an der Gesamtzeit. Ein weiteres Warnsignal: Die Zahl der untersuchten Zeilen («Rows examined») ist viel grösser als die der gesendeten Zeilen.
Schritt 3: Abfragen mit EXPLAIN analysieren
Setzen Sie EXPLAIN vor die verdächtige Abfrage:
EXPLAIN SELECT id, title FROM posts
WHERE status = 'publish' AND author_id = 42
ORDER BY created_at DESC LIMIT 10;
Die wichtigsten Spalten der Ausgabe:
| Spalte | Bedeutung |
|---|---|
type | Zugriffsart (siehe unten) |
possible_keys | Indizes, die in Frage kommen |
key | Tatsächlich verwendeter Index, NULL = keiner |
rows | Geschätzte Anzahl zu untersuchender Zeilen |
filtered | Geschätzter Anteil der Zeilen, die die Bedingung erfüllen |
Extra | Zusätzliche Hinweise |
Die Zugriffsart type, von gut nach schlecht:
type | Bedeutung |
|---|---|
const | Höchstens eine passende Zeile über Primärschlüssel oder UNIQUE-Index |
eq_ref | Pro Zeile der vorherigen Tabelle genau eine Zeile (typisch bei Joins über den Primärschlüssel) |
ref | Alle Zeilen mit passendem Indexwert, gut bei wenigen Treffern |
range | Bereich über einen Index, etwa bei >, BETWEEN, IN() |
index | Der gesamte Index wird durchlaufen |
ALL | Vollständiger Tabellenscan, laut MySQL-Handbuch in den meisten Fällen sehr schlecht |
In der Spalte Extra sollten Sie besonders auf zwei Hinweise achten, die das Handbuch ausdrücklich nennt:
Using filesort: Die Datenbank muss die Ergebnisse in einem zusätzlichen Durchgang sortieren.Using temporary: Für das Ergebnis wird eine temporäre Tabelle angelegt, oft beiGROUP BYundORDER BYauf unterschiedlichen Spalten.
Using index ist dagegen ein gutes Zeichen: Die Abfrage kann vollständig aus dem Index beantwortet werden.
Tatsächliche Laufzeit messen
EXPLAIN zeigt nur Schätzungen. MySQL bietet zusätzlich EXPLAIN ANALYZE, MariaDB den Befehl ANALYZE. Beide führen die Abfrage tatsächlich aus und zeigen echte Zeilenzahlen und Laufzeiten neben den Schätzungen. Bei MariaDB stehen die realen Werte in den Spalten r_rows und r_filtered.
Achtung: Weil die Abfrage wirklich ausgeführt wird, verändern ANALYZE UPDATE und ANALYZE DELETE in MariaDB Ihre Daten. Testen Sie solche Anweisungen nur auf einer Kopie.
Schritt 4: Den richtigen Index anlegen
In den meisten Fällen ist ein fehlender oder ungeeigneter Index die Ursache. Für das Beispiel oben hilft ein zusammengesetzter Index:
CREATE INDEX idx_status_author_created
ON posts (status, author_id, created_at);
Die Reihenfolge der Spalten zählt
Ein Index über mehrere Spalten funktioniert wie ein Telefonbuch: sortiert nach Nachname, innerhalb des Nachnamens nach Vorname. Das MySQL-Handbuch erklärt dies am Beispiel eines Index auf (last_name, first_name):
| Abfrage | Index nutzbar? |
|---|---|
WHERE last_name = 'Jones' | Ja |
WHERE last_name = 'Jones' AND first_name = 'John' | Ja |
WHERE last_name = 'Jones' AND first_name >= 'M' AND first_name < 'N' | Ja |
WHERE first_name = 'John' | Nein |
WHERE last_name = 'Jones' OR first_name = 'John' | Nein |
Der Index wird nur genutzt, wenn die Abfrage die Spalten von links her verwendet («Leftmost Prefix»). Faustregeln für die Reihenfolge:
- Spalten mit Gleichheitsbedingungen (
=) zuerst - Danach Spalten mit Bereichsbedingungen oder Sortierung (
ORDER BY) - Einen Index auf
(a, b)brauchen Sie nicht zusätzlich als Einzelindex auf(a)
Typische Gründe, warum ein Index nicht greift
- Funktion auf der Spalte:
WHERE YEAR(created_at) = 2026kann einen Index aufcreated_atnicht direkt nutzen. Besser:WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'. - Platzhalter am Anfang:
LIKE '%suchwort'kann den Index nicht nutzen,LIKE 'suchwort%'hingegen schon. - Unterschiedliche Datentypen: Wird eine Textspalte mit einer Zahl verglichen, muss die Datenbank umwandeln und den Index womöglich ignorieren.
- Falsche Spaltenreihenfolge im zusammengesetzten Index (siehe oben).
Indizes haben ihren Preis
Jeder Index muss bei INSERT, UPDATE und DELETE mitgepflegt werden und belegt Speicher. Legen Sie Indizes gezielt für die Abfragen aus dem Slow Query Log an, nicht vorsorglich auf jede Spalte. Auf grossen Tabellen kann das Anlegen eines Index eine Weile dauern. Planen Sie es ausserhalb der Spitzenzeiten und erstellen Sie vorher ein Backup.
Schritt 5: Speicher richtig einstellen
InnoDB Buffer Pool
Der Buffer Pool ist der Arbeitsspeicher, in dem InnoDB Tabellen- und Indexdaten zwischenspeichert. Ist er zu klein, muss die Datenbank ständig von der Festplatte lesen. Der Standardwert in MySQL 8.4 beträgt nur 128 MB.
Laut MySQL-Handbuch werden auf dedizierten Datenbankservern oft bis zu 80 Prozent des physischen Arbeitsspeichers dem Buffer Pool zugewiesen. Läuft auf demselben Server auch der Webserver mit PHP, wie bei vielen kleinen VPS, müssen Sie entsprechend weniger einplanen.
Die Grösse lässt sich in MySQL im laufenden Betrieb ändern:
SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2 GB
Der Wert wird automatisch auf ein Vielfaches von innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances angepasst. Tragen Sie ihn zusätzlich in die Konfigurationsdatei ein, damit er nach einem Neustart erhalten bleibt.
Automatische Konfiguration bei dedizierten Servern
MySQL bietet die Option innodb_dedicated_server. Ist sie beim Start aktiviert, setzt MySQL den Buffer Pool automatisch:
| Erkannter Arbeitsspeicher | innodb_buffer_pool_size |
|---|---|
| Unter 1 GB | 128 MB |
| 1 bis 4 GB | 50 % des Speichers |
| Über 4 GB | 75 % des Speichers |
Das Handbuch empfiehlt die Option nur, wenn MySQL einen Server, eine VM oder einen Container für sich allein hat. Auf einem gemeinsam genutzten Webserver ist sie ungeeignet.
Kein Query Cache mehr in MySQL 8
Ältere Anleitungen empfehlen, query_cache_size zu erhöhen. Der Query Cache wurde jedoch mit MySQL 8.0.3 entfernt, mitsamt den Variablen query_cache_size und query_cache_type. Steht eine dieser Variablen nach einem Upgrade noch in der Konfiguration, startet der Server mit einer Fehlermeldung über eine unbekannte Variable. Entfernen Sie solche Zeilen. Als Ersatz dienen Caches auf Anwendungsebene, etwa ein Objekt-Cache mit Redis oder Memcached oder ein Seiten-Cache.
Checkliste
- Slow Query Log aktiviert,
long_query_timeauf 1 Sekunde oder weniger - Log nach einigen Tagen mit
mysqldumpslowoderpt-query-digestausgewertet - Abfragen nach Gesamtzeit priorisiert, nicht nach Einzellaufzeit
- Top-Abfragen mit
EXPLAINgeprüft: keinALL, kein unnötigesUsing filesort - Passende zusammengesetzte Indizes in der richtigen Spaltenreihenfolge angelegt
- Keine Funktionen auf indizierten Spalten in
WHERE-Bedingungen -
innodb_buffer_pool_sizepassend zum verfügbaren Arbeitsspeicher - Veraltete Query-Cache-Einstellungen entfernt
- Wirkung mit erneuter Messung überprüft
Eine schnelle Datenbank ist eine Voraussetzung für eine schnelle Website, aber nur ein Baustein. Ergänzen Sie sie mit einem aktiven PHP OPcache und kontrollieren Sie den Effekt mit PageSpeed Insights, wo sich eine kürzere Serverzeit in den Felddaten zeigt.