SQL-Indizes — Warum Ihre Abfrage langsam ist und was Sie tatsächlich dagegen tun sollten

Ihre Abfrage dauert 2 ms auf localhost mit 500 Zeilen. Auf der Produktion mit 3 Millionen Zeilen benötigt sie 9 Sekunden und Ihre Benutzer schauen auf einen Spinner. Die Abfrage selbst ist wahrscheinlich in Ordnung – das Schema ist es nicht. Sie brauchen einen Index und Sie müssen verstehen. warum daher stoppen Sie das Raten beim nächsten Auftreten.

Was die Datenbank ohne Index macht

Ein B-Baum-Index ist eine sortierte Kopie einer Spalte (oder mehrerer Spalten), gespeichert in einem balancierten Baum, den die Datenbank in O(log n) Zeit binär durchsuchen kann. Ohne einen solchen Index führt die Datenbank zu einem vollständigen Tabellen-Scan — sie liest jede einzelne Zeile, um Übereinstimmungen zu finden. Bei 3 Millionen Zeilen bedeutet das 3 Millionen Zeilen-Lesungen. Mit einem Index auf der richtigen Spalte sind es vielleicht 20 bis 30 Baumknotenabläufe, um die passenden Zeilen zu finden.

Denken Sie daran wie ein Telefonbuch. Die Suche nach „Smith, John“ im alphabetischen Verlauf dauert einige Sekunden. Die Suche nach allen Personen mit dem Namen John, indem man jede Eintragung von Seite 1 liest, dauert ewig. Ein Index ist die alphabetische Sortierung – die Datenbank nutzt ihn, um direkt auf die relevanten Zeilen zuzugreifen und nicht alles zu scannen.

Ein wichtiger Punkt: Indizes existieren auf Festplatte neben Ihrer Tabelle. Die Datenbank behält sie automatisch bei Schreibvorgängen. Das ist kostenlos für Lesen, aber kostet bei jedem INSERT, UPDATE und DELETE – deshalb können Sie nicht einfach alles indizieren.

Das Problem mit EXPLAIN erkennen

Bevor Sie etwas ändern, führen Sie EXPLAIN auf der langsamen Abfrage aus. Hier ist, was Sie in MySQL suchen:

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

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

Zwei Felder, die wichtig sind:

• type: ALL — vollständiger Tabellen-Scan. Fast immer schlecht, sobald Ihre Tabelle mehr als ~100.000 Zeilen hat.
• rows: 2847391 — der Optimizer schätzt, dass 2,8 Millionen Zeilen geprüft werden müssen, um Ihre Ergebnisse zu liefern.

Fügen Sie nun den Index hinzu und führen Sie es erneut aus:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys          | key                    | key_len | ref   | rows | Extra |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_orders_customer_id | idx_orders_customer_id | 4       | const |   12 |       |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+

type: ref und rows: 12. Das ist die Lösung. 2,8 Millionen Zeilen geprüft → 12.

Bei PostgreSQL verwenden Sie EXPLAIN ANALYZE (die Abfrage tatsächlich ausführt), um echte Zeitwerte zu erhalten:

-- Before index
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Seq Scan on orders  (cost=0.00..89432.00 rows=14 width=156)
                    (actual time=142.341..8934.221 rows=12 loops=1)
  Filter: (customer_id = 12345)
  Rows Removed by Filter: 2847379
Planning Time: 0.4 ms
Execution Time: 8934.8 ms

-- After: CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Index Scan using idx_orders_customer_id on orders
                    (cost=0.43..92.15 rows=12 width=156)
                    (actual time=0.042..0.198 rows=12 loops=1)
  Index Cond: (customer_id = 12345)
Planning Time: 0.5 ms
Execution Time: 0.3 ms

8934ms → 0,3ms. Das ist kein ausgewähltes Beispiel – dies ist die realistische Wirkung eines einzigen fehlenden Indexes auf eine Tabelle mit einigen Millionen Zeilen.

Komposite Indizes und die linke Präfixregel

Sobald Sie mehrere Spalten in Ihrer WHERE-Klausel haben, benötigen Sie komposite Indizes – und die Spaltenreihenfolge spielt eine Rolle.

SELECT * FROM orders
WHERE customer_id = 12345
  AND status = 'pending'
  AND created_at > '2024-01-01';

Der linke Präfixregel: Die Datenbank verwendet einen kompositen Index ab der linksten Spalte und geht von links nach rechts, aber wird ungenutzt, sobald eine Lücke erreicht wird. Gegeben ein Index auf (a, b, c):

• WHERE a = 1 — nutzt den Index
• WHERE a = 1 AND b = 2 — nutzt beide Spalten des Indexes
• WHERE a = 1 AND b = 2 AND c = 3 — nutzt den vollständigen Index
• WHERE b = 2 — nutzt den Index nicht (übersprungenes Feld a)
• WHERE a = 1 AND c = 3 — nutzt nur den Teil a, ignoriert c

Die praktische Reihenfolge-Regel: Gleichheitsbedingungen zuerst, Bereichsbedingungen zuletzt. Bereichsbedingungen (>, <, BETWEEN, LIKE 'prefix%') brechen die nutzbare Präfix für die folgenden Spalten. Also:

-- Good: equality columns first, range column last
CREATE INDEX idx_orders_compound ON orders (customer_id, status, created_at);

-- This query uses all three columns of the index:
SELECT * FROM orders
WHERE customer_id = 12345   -- equality, goes first
  AND status = 'pending'    -- equality, goes second
  AND created_at > '2024-01-01'; -- range, goes last

-- Bad ordering for this query: range column in the middle
-- CREATE INDEX idx_bad ON orders (customer_id, created_at, status);
-- The status filter won't use the index because created_at is a range predicate

Denken Sie auch an die Selektivität: Setzen Sie die spaltenspezifische Spalte mit den wenigsten Duplikaten früher in den Index. Wenn customer_id 50.000 verschiedene Werte hat und status 3 hat, customer_id sollte zuerst kommen – es reduziert die Ergebnismenge schneller.

Wenn Indizes Dinge verschlechtern

Indizes sind nicht kostenlos. Jeder Index kostet Leistung bei Schreibvorgängen und Speicherplatz. Die Datenbank aktualisiert jeden Index bei jedem INSERT, UPDATE und DELETE auf dieser Tabelle. Bei schreibintensiven Tabellen führt ein zu viel Index tatsächlich zu einer Leistungsverlust. Hier sind Situationen, in denen Indizes scheitern oder sich verhalten:

• Spalten mit geringer Dichte. Ein Index auf einem Boolean-Feld oder auf einer Spalte mit nur 3 verschiedenen Werten wird oft von der Optimierer ignoriert. Wenn eine Abfrage mehr als ~10–15% Zeilen über einen Index zurückgibt, bevorzugt die Datenbank normalerweise einen vollständigen Tabellen-Scan – die Kosten für die Indexsuche sind nicht wertvoll. Prüfen Sie mit EXPLAIN, ob der Index tatsächlich genutzt wird. status Schreibintensive Tabellen.
• Protokolltabellen, Ereignis-Tabellen, Audit-Tabellen – bleiben Sie vorsichtig. Eine Tabelle mit 10 Indizes und Millionen Schreibvorgängen pro Stunde zahlt 10-fachen Schreibaufwand auf diesen Indizes. Nutzungslose Indizes.
• In PostgreSQL, zeigt pg_stat_user_indexes — wie oft jeder Index seit der letzten Statistik-Neuinitialisierung genutzt wurde. Ein Index mit Null-Skans ist reiner Aufwand. MySQL verfolgt dies über die Performance Schema ( idx_scan in MySQL 5.7+).sys.schema_unused_indexes Der Optimierer vermutet falsch.
• Abfrageplaner sind nicht immer korrekt. Manchmal wählen sie einen Index, der langsamer ist als ein vollständiger Scan. Wird dies erkennen – vergleichen Sie die geschätzten mit den tatsächlichen Zeilen. In MySQL können Sie dies mit EXPLAIN ANALYZE ; in PostgreSQL können Sie dies mit USE INDEX (idx_name)(verwenden Sie dies nicht in der Produktion). SET enable_seqscan = off Index oder Abfrageumformung? Die echte Entscheidung

Manchmal ist der Index die Lösung. Manchmal ist die Abfrage das Problem und der Index verdeckt es. Einige Muster, bei denen Sie die Abfrage umformen sollten, anstatt (oder zusätzlich zu) einen Index hinzuzufügen:

Funktionen auf indizierten Spalten

Das Wrapping einer Spalte in eine Funktion verhindert die Standardnutzung eines Indexes. Der Index speichert Werte von

-- Will NOT use an index on created_at — the function result isn't indexed:
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Rewrite to use the index:
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

, nicht Werte von created_at— das sind verschiedene Dinge. PostgreSQL unterstützt Ausdrucksindizes ( YEAR(created_at) ) als Workaround, aber die Abfrageumformung ist sauberer und funktioniert überall.CREATE INDEX ON orders (YEAR(created_at))Abdeckende Indizes für heiße Lesepfade

Abdeckender Index

A enthält alle Spalten, die eine Abfrage benötigt, sodass die Datenbank nie die tatsächlichen Tabellenzeilen lesen muss – sie antwortet vollständig aus dem Index. Dies kann bei stark gelesenen Tabellen erheblich schneller sein als ein regulärer Index: Muster mit Vorwärtswildcards

-- Query that only needs id, status, total:
SELECT id, status, total FROM orders WHERE customer_id = 12345;

-- Regular index: database finds matching rows via index, then reads each row from table
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Covering index: database reads everything it needs from the index alone
CREATE INDEX idx_orders_cover ON orders (customer_id, id, status, total);
-- Note: customer_id first (the WHERE column), then the SELECT columns

Vorwärtswildcards sind nicht mit B-Baum-Indizes fixierbar. Wenn Sie Nachhängen oder Teilzeichenabfragen benötigen, benötigen Sie einen Volltextindex (

-- Uses the index (prefix match — database knows where to start):
SELECT * FROM users WHERE email LIKE 'john%';

-- Does NOT use the index (leading wildcard — database doesn't know where to start):
SELECT * FROM users WHERE email LIKE '%@gmail.com';

in MySQL,FULLTEXT in PostgreSQL) oder ein externes Suchsystem wie Elasticsearch. GIN ist der schnelle Weg, aber es behandelt Attribute unkonsequent und kann Daten in Randfällen verlieren. Für Produktionsarbeit mit SOAP-Antworten, betrachten Sie pg_trgm N+1 Abfragen

Ein Index löst ein N+1-Problem nicht. Wenn Ihr ORM eine Abfrage für 200 Bestellungen durchführt und dann eine separate Abfrage pro Bestellung für den Kundenname durchführt, gibt es 201 Abfragen. Jede einzelne könnte schnell sein. Das Problem ist die Menge. Lösung: Verwenden Sie einen JOIN oder eine batchte Abfrage – keine Menge an Indizierung verändert die Anzahl der Abfragen.

Noch etwas: Reinigen Sie die Abfrage, bevor Sie sie analysieren

Bevor Sie EXPLAIN auf eine langsame Abfrage ausführen, hilft es, die SQL klar zu formatieren. Eine 200-Zeichen-Einzeilige Abfrage ohne Einrückung ist schwerer zu verstehen als eine strukturierte Abfrage. Der

SQL-Formatter bei IO Tools behandelt MySQL, PostgreSQL, SQL Server und andere Dialekte – nützlich, wenn Sie eine Abfrage aus Protokollen oder ORM-Debug-Ausgaben kopieren und ihre Struktur schnell sehen möchten. Die praktische Checkliste

bevor Sie einen Index hinzufügen. Bestätigen Sie das Problem zuerst.

• Führen Sie EXPLAIN Suchen Sie nach
• (MySQL) oder type: ALL (PostgreSQL) – normalerweise wo das Problem liegt. Seq Scan Prüfen Sie die Schätzung in EXPLAIN. Millionen von Zeilen gelesen, um Dutzende zurückzugeben = fehlender Index.
• Bei kompositen Indizes: Gleichheitsbedingungen zuerst, Bereichsbedingungen zuletzt. rows Spalten mit höherer Selektivität kommen früher in einem kompositen Index.
• Vermeiden Sie das Indizieren von Spalten mit geringer Dichte, ohne die Optimierer zu überprüfen, ob der Index tatsächlich genutzt wird.
• Überprüfen Sie regelmäßig unnütze Indizes:
• (PostgreSQL),
• (MySQL). pg_stat_user_indexes Wenn eine Funktion Ihre Spalte in der WHERE-Klausel umhüllt, ersetzen Sie die Abfrage anstatt den Funktionsindex zu erstellen. sys.schema_unused_indexes Für stark gelesene heiße Pfade mit einer kleinen Spaltenmenge, betrachten Sie abdeckende Indizes.
• N+1 ist ein Problem der Abfragearchitektur, nicht ein Indexproblem.
• SQL-Indizes – Warum Ihre Abfrage langsam ist und was Sie tatsächlich tun sollten 2
• SQL-Indizes – Warum Ihre Abfrage langsam ist und was Sie tatsächlich tun sollten 1

Das könnte Ihnen auch gefallen