Optimierung von MySQL-Abfragen: Ein praktischer Leitfaden

Langsame MySQL-Abfragen sind selten ein Rätsel, sobald man den Ausführungsplan betrachtet. Die Schwierigkeit liegt nicht darin zu wissen, dass Indizes wichtig sind. Die Schwierigkeit besteht darin, nachzuweisen, welche Abfrage langsam ist, zu verstehen, warum MySQL einen bestimmten Plan gewählt hat, und die Abfrage oder den Index zu ändern, ohne Schreibvorgänge, Speicher oder andere Abfragen zu verschlechtern.

Beginnen Sie mit Beweisen. Verwenden Sie das Slow-Query-Protokoll, das Performance Schema, Anwendungsspuren oder ein Überwachungswerkzeug wie PMM, um Abfragen zu finden, die Benutzer tatsächlich beeinträchtigen. Verwenden Sie dann EXPLAIN und, wenn sicher, EXPLAIN ANALYZE, um zu sehen, was MySQL tut.

Verständnis der Abfrageleistung

Häufige Ursachen sind:

Fehlende oder ineffektive Indizes: Ohne geeignete Indizes muss MySQL vollständige Tabellenscans durchführen, was bei großen Tabellen sehr ineffizient ist.
Schlecht geschriebenes SQL: Nicht-sargbare Filter, unnötiges SELECT *, versehentliche Cross-Joins und ineffiziente Join-Bedingungen können die Leistung beeinträchtigen.
Große Datenmengen: Mehr Daten bedeuten mehr Seiten zum Lesen, Sortieren, Gruppieren und Cachen.
Hardware und Konfiguration: Suboptimale Serverkonfiguration oder unzureichende Hardwareressourcen können ebenfalls eine Rolle spielen, obwohl sich dieser Leitfaden auf die Optimierung auf Abfrageebene konzentriert.

Die Macht von `EXPLAIN`

EXPLAIN ist das erste Werkzeug, zu dem Sie greifen sollten, wenn Sie verstehen möchten, wie MySQL eine Abfrage plant. Bei einfachem EXPLAIN SELECT zeigt MySQL den vom Optimierer gewählten Plan an, ohne das Ergebnisset zurückzugeben. EXPLAIN ANALYZE führt die Abfrage aus und meldet die tatsächliche Zeit, also verwenden Sie es vorsichtig auf Produktionssystemen.

So verwenden Sie `EXPLAIN`

Fügen Sie bei einer Leseabfrage EXPLAIN voran:

EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

Interpretation der `EXPLAIN`-Ausgabe

Die Ausgabe von EXPLAIN ist eine Tabelle mit mehreren wichtigen Spalten:

id: Die Sequenznummer des SELECT innerhalb der Abfrage. Höhere Zahlen werden in der Regel zuerst ausgeführt.
select_type: Der Typ des SELECT (z. B. SIMPLE, PRIMARY, SUBQUERY, DERIVED).
table: Die Tabelle, auf die zugegriffen wird.
partitions: Die verwendeten Partitionen (falls Partitionierung aktiviert ist).
type: Der Join-Typ. Dies ist eine der nützlichsten Spalten. Streben Sie nach const, eq_ref, ref oder range, wenn die Abfrageform dies zulässt. Seien Sie misstrauisch bei index und insbesondere ALL bei großen Tabellen.
possible_keys: Zeigt an, welche Indizes MySQL verwenden könnte.
key: Der Index, den MySQL tatsächlich verwendet hat.
key_len: Die Länge des Indexteils, den MySQL voraussichtlich verwenden wird. Kürzer ist nicht automatisch besser; es hängt von der Selektivität und der Abfrage ab.
ref: Die Spalte oder Konstante, die mit dem Index (key) verglichen wird.
rows: Eine Schätzung der Anzahl der Zeilen, die MySQL voraussichtlich untersuchen wird.
filtered: Der Prozentsatz der Zeilen, die durch die Tabellenbedingung gefiltert werden.
Extra: Enthält zusätzliche Informationen darüber, wie MySQL die Abfrage auflöst. Wichtige Werte, auf die Sie achten sollten, sind:
- Using where: Zeigt an, dass MySQL eine Bedingung anwendet, während es Zeilen verarbeitet. Dies ist üblich und nicht immer schlecht.
- Using index: Bedeutet, dass die Abfrage von einem Index abgedeckt wird (alle erforderlichen Spalten sind im Index), was gut ist.
- Using temporary: MySQL muss eine temporäre Tabelle erstellen, oft für GROUP BY- oder ORDER BY-Operationen. Dies kann langsam sein.
- Using filesort: MySQL muss eine externe Sortierung durchführen (verwendet keinen Index für die Sortierung). Dies ist oft ein Zeichen für eine ineffiziente ORDER BY-Klausel.

Identifizieren von Engpässen mit `EXPLAIN`

Schauen wir uns einige häufige Szenarien an und wie EXPLAIN bei der Identifizierung von Problemen hilft:

Szenario 1: Vollständiger Tabellenscan

Betrachten Sie eine Abfrage wie:

SELECT * FROM orders WHERE order_date = '2023-10-26';

Wenn die Spalte order_date nicht indiziert ist, könnte EXPLAIN Folgendes anzeigen:

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 1000000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

Problem: type: ALL zeigt einen vollständigen Tabellenscan an. rows: 1000000 zeigt, dass MySQL jede Zeile in der Tabelle orders untersuchen muss. key: NULL bedeutet, dass kein Index verwendet wurde.

Lösung: Fügen Sie einen Index für die Spalte order_date hinzu:

CREATE INDEX idx_order_date ON orders (order_date);

Führen Sie nach dem Hinzufügen des Index erneut EXPLAIN aus. Sie sollten einen selektiveren Zugriffstyp wie ref oder range sehen, und die geschätzte Zeilenanzahl sollte sinken, wenn der Datumsfilter selektiv ist.

Szenario 2: Ineffizientes `ORDER BY` oder `GROUP BY`

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id ORDER BY customer_id;

Wenn customer_id nicht indiziert ist, könnte EXPLAIN Folgendes anzeigen:

+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra                            |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
|  1 | SIMPLE      | orders | index | NULL          | NULL | NULL    | NULL | 100000 | Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+

Problem: Using temporary und Using filesort zeigen an, dass MySQL kostspielige Operationen zum Sortieren und Gruppieren der Daten durchführt. Dies liegt oft daran, dass kein Index sowohl die Gruppierungs- als auch die Sortieranforderungen effizient erfüllen kann.

Lösung: Für diese spezifische Abfrage kann ein Index auf (customer_id) es MySQL ermöglichen, Zeilen in Gruppierungsreihenfolge zu scannen. Wenn die eigentliche Abfrage zuerst nach Datum, Status oder Mandant filtert, ist ein zusammengesetzter Index möglicherweise besser, z. B. (tenant_id, status, customer_id).

CREATE INDEX idx_customer_id ON orders (customer_id);

Szenario 3: Unnötige Verwendung von `SELECT *`

Wenn Sie alle Spalten (*) auswählen, aber nur wenige benötigen, übertragen Sie mehr Daten und verhindern möglicherweise, dass ein abdeckender Index nützlich ist. Dies macht sich besonders bei breiten Tabellen mit JSON-Spalten, Text-Blobs oder vielen nullable Feldern bemerkbar.

-- Angenommen, es gibt einen Index auf 'status'
SELECT * FROM tasks WHERE status = 'pending';

EXPLAIN zeigt möglicherweise Using where an, aber wenn die Abfrage Spalten benötigt, die nicht im für die Filterung verwendeten Index enthalten sind, muss dennoch auf die Tabellendaten zugegriffen werden.

Lösung: Geben Sie nur die benötigten Spalten an:

SELECT task_id, description FROM tasks WHERE status = 'pending';

Wenn Sie diese genaue Form häufig abfragen, sollten Sie einen abdeckenden Index in Betracht ziehen, der die Filterspalte und die zurückgegebenen Spalten enthält:

CREATE INDEX idx_tasks_status_id_description
  ON tasks (status, task_id, description);

Erstellen Sie nicht für jede Abfrage abdeckende Indizes. Sie beschleunigen Lesevorgänge auf Kosten von Speicher und Schreib-Overhead.

Umschreiben langsamer Abfragen

Über die Indizierung hinaus kann die Art und Weise, wie Sie SQL strukturieren, den Arbeitsaufwand von MySQL verändern.

Vermeiden Sie korrelierte Unterabfragen

Korrelierte Unterabfragen können für jede von der äußeren Abfrage verarbeitete Zeile einmal ausgeführt werden. MySQL kann einige davon optimieren, aber wenn EXPLAIN wiederholte abhängige Lookups zeigt, ist ein Join oder eine abgeleitete Tabelle oft klarer und schneller.

Oft ineffizient:

SELECT o.order_id, o.order_date
FROM orders o
WHERE o.customer_id IN (
    SELECT c.customer_id
    FROM customers c
    WHERE c.country = 'USA'
);

Oft besser als Join:

SELECT o.order_id, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA';

Verwenden Sie EXPLAIN für beide Versionen. Der Join ist nicht automatisch in jedem Schema schneller, aber für viele Teams ist er einfacher zu verstehen und zu indizieren.

Optimieren Sie `LIKE`-Klauseln

Führende Wildcards (%) in LIKE-Klauseln verhindern in der Regel, dass ein normaler B-Tree-Index für einen Bereichszugriff verwendet wird.

Ineffizient:

SELECT * FROM products WHERE product_name LIKE '%widget';

Besser (wenn möglich):

SELECT * FROM products WHERE product_name LIKE 'widget%';

Wenn Sie eine Contains-ähnliche Übereinstimmung benötigen, ziehen Sie MySQL-Volltextindizes für geeignete Textsuche, N-Gramm-Ansätze für bestimmte Sprachen oder eine Suchmaschine in Betracht, wenn Relevanz und flexible Übereinstimmung wichtig sind.

Verwenden Sie nach Möglichkeit `UNION ALL` anstelle von `UNION`

UNION entfernt doppelte Zeilen, was einen zusätzlichen Sortier- und Deduplizierungsschritt erfordert. Wenn Sie wissen, dass es keine Duplikate gibt oder Sie diese nicht entfernen müssen, ist UNION ALL schneller.

Langsam:

SELECT name FROM table1
UNION
SELECT name FROM table2;

Schnell:

SELECT name FROM table1
UNION ALL
SELECT name FROM table2;

Weitere Optimierungstipps

Halten Sie Statistiken aktuell: Stellen Sie sicher, dass die Tabellenstatistiken aktuell sind, damit der Abfrageoptimierer fundierte Entscheidungen treffen kann. Dies wird oft automatisch erledigt, kann aber manuell mit ANALYZE TABLE aktualisiert werden.
Serverkonfiguration: Die Abfrageoptimierung kann einen winzigen InnoDB-Pufferpool oder überlastete Festplatten nicht kompensieren. In MySQL 8.0 wurde der alte Abfragecache entfernt, planen Sie also keine neue Optimierung um query_cache_size herum.
Regelmäßige Überwachung: Verwenden Sie Tools wie MySQL Enterprise Monitor, Percona Monitoring and Management (PMM) oder integrierte Performance-Schema-Ansichten, um langsame Abfragen zu verfolgen und Trends zu identifizieren.

Ein praktischer Optimierungs-Workflow

Optimieren Sie für Produktionssysteme von der langsamen Abfrage nach außen:

Erfassen Sie das genaue SQL, die gebundenen Werte, die Zeilenanzahlen und die Zeitmessung.
Führen Sie EXPLAIN FORMAT=TREE oder EXPLAIN FORMAT=JSON aus, wenn Ihre MySQL-Version dies unterstützt.
Überprüfen Sie, ob der ausgewählte Index dem Filter- und Join-Muster entspricht.
Testen Sie eine Abfrageumschreibung oder Indexänderung mit realistischen Daten.
Vergleichen Sie die untersuchten Zeilen, temporären Tabellen, das Sortierverhalten und die Wanduhr-Latenz.

Dies verhindert, dass Sie Indizes hinzufügen, nur weil eine Abfrage „langsam aussieht“. Indizes haben Kosten. Jedes INSERT, UPDATE und DELETE muss sie verwalten. Eine Tabelle mit zehn überlappenden Indizes kann insgesamt langsamer werden, selbst wenn sich eine Leseabfrage verbessert.

Für eine häufige Multi-Tenant-Anwendungsabfrage ist die Indexreihenfolge oft wichtiger als die Anzahl der indizierten Spalten:

SELECT id, created_at, total
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;

Ein nützlicher Index könnte sein:

CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

Dieser Index beginnt mit Gleichheitsfiltern und unterstützt dann den Datumsbereich und die Sortierung. Wenn Sie created_at zuerst setzen, scannt MySQL möglicherweise viele Mandanten, bevor es den richtigen findet. Wenn Sie status weglassen, funktioniert die Abfrage möglicherweise immer noch, untersucht aber viele zusätzliche Zeilen.

Achten Sie auf nicht-sargbare Filter

Eine Bedingung ist sargbar, wenn MySQL einen Index verwenden kann, um nach übereinstimmenden Zeilen zu suchen. Das Verpacken einer indizierten Spalte in eine Funktion bricht dies oft:

-- Schwerer, einen Index auf created_at zu verwenden
SELECT * FROM orders
WHERE DATE(created_at) = '2025-01-15';

Schreiben Sie es als Bereich um:

SELECT *
FROM orders
WHERE created_at >= '2025-01-15'
  AND created_at <  '2025-01-16';

Die zweite Version ermöglicht es MySQL, in einen Index auf created_at zu suchen. Die gleiche Idee gilt für LOWER(email), Mathematik auf numerischen Spalten und implizite Typkonvertierungen. Wenn die Spalte indiziert ist, halten Sie die Spaltenseite des Vergleichs nach Möglichkeit sauber.

Seien Sie vorsichtig mit Paginierung

Offset-Paginierung wird bei tiefen Seiten teuer:

SELECT id, title
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20 OFFSET 200000;

MySQL muss dennoch die früheren Zeilen durchgehen, bevor es die angeforderte Seite zurückgibt. Für Feeds, Audit-Logs und Admin-Tabellen ist die Keyset-Paginierung in der Regel besser:

SELECT id, title, published_at
FROM posts
WHERE status = 'published'
  AND (published_at, id) < ('2025-05-01 12:00:00', 987654)
ORDER BY published_at DESC, id DESC
LIMIT 20;

Kombinieren Sie es mit einem Index wie (status, published_at, id). Dies ändert das Produktverhalten ein wenig, da sich Benutzer durch einen Cursor bewegen, anstatt zu Seite 10.000 zu springen, aber es kann eine schmerzhafte Abfrage in eine vorhersagbare verwandeln.

Validieren Sie mit echten Daten

Kleine Staging-Datenbanken lügen. Eine Abfrage, die bei 20.000 Zeilen sofort ausgeführt wird, kann bei 200 Millionen Zeilen schrecklich sein, insbesondere wenn die Datenverteilung verzerrt ist. Testen Sie nach Möglichkeit gegen produktionsähnliches Volumen und Kardinalität. Wenn Sie keine Produktionsdaten kopieren können, generieren Sie zumindest Daten mit ähnlichen Mandantengrößen, Statusverteilungen und Datumsbereichen.

Eine letzte Gewohnheit hilft: Bewahren Sie den alten und den neuen Plan im Ticket auf. Ihr zukünftiges Ich wird wissen wollen, warum ein Index existiert.

Die beste MySQL-Optimierungsgewohnheit ist, jede Änderung ihren Platz verdienen zu lassen. Erfassen Sie die langsame Abfrage, überprüfen Sie den Plan, ändern Sie eine Abfrage oder einen Index, und vergleichen Sie dann die Latenz und die untersuchten Zeilen. Ein sauberer EXPLAIN-Plan ist nützlich, aber der wahre Gewinn ist eine geringere Produktionslatenz, ohne neuen Schreibdruck oder Speicherüberblähung zu erzeugen.

Optimierung von MySQL-Abfragen: Ein praktischer Leitfaden

Verständnis der Abfrageleistung

Die Macht von EXPLAIN

So verwenden Sie EXPLAIN

Interpretation der EXPLAIN-Ausgabe

Identifizieren von Engpässen mit EXPLAIN