Der Index existiert, der Optimizer nimmt ihn nicht
Vier Ursachen erklären fast jeden Fall, sie schließen einander weitgehend aus, und jede hinterlässt im Ausführungsplan eine andere Spur.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Ein angelegter Index ist noch keine schnelle Abfrage
Der Ablauf ist fast immer derselbe. Eine Abfrage dauert zu lange, jemand legt einen Index auf die Spalte aus der WHERE-Bedingung, und die Laufzeit bleibt, wo sie war. Der Index steht im Katalog, er wird nachts sogar reorganisiert, und trotzdem liest das System bei jedem Aufruf die ganze Tabelle. Die Ursache steckt selten im Index selbst, sondern in der Frage, ob die Abfrage ihn überhaupt benutzen kann und ob der Optimizer ihn für die günstigere Wahl hält.
Teuer wird das durch die Reaktion darauf. Wenn niemand die Ursache kennt, wächst die Zahl der Indizes weiter, und jeder zusätzliche Index kostet Schreibarbeit bei jedem INSERT und jedem UPDATE, Platz in der Sicherung und Zeit beim Zurückspielen. Nach ein paar Jahren stehen auf einer Tabelle zwölf Indizes, von denen drei jemals in einem Plan auftauchen, und die nächtlichen Ladejobs dauern doppelt so lang wie nötig.
Die vier üblichen Ursachen lassen sich sauber auseinanderhalten: eine Bedingung, die den Index unbrauchbar macht, eine Spaltenreihenfolge, die nicht zur Abfrage passt, eine Trefferzahl, bei der sich der Umweg über den Index nicht mehr lohnt, und Statistiken, die etwas anderes behaupten als die Tabelle. Welche davon vorliegt, steht im Ausführungsplan, nicht in der Definition des Index.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Symptom, Ursache, Lösung
Symptom
Der Index steht auf der abgefragten Spalte, im Plan taucht trotzdem ein vollständiger Tabellenzugriff auf, und zwar bei jedem Suchwert gleichermaßen.
Ursache
Auf der Spalte liegt eine Funktion, eine Berechnung oder ein Muster mit führendem Platzhalter wie LIKE '%meier'. Der Index ist nach den unveränderten Werten sortiert und passt deshalb nicht zur Bedingung.
Lösung
Die Bedingung so umschreiben, dass die Spalte allein links steht, etwa als Bereich statt als YEAR-Ausdruck. Wenn das nicht geht, in PostgreSQL oder Oracle einen Index auf den Ausdruck anlegen, im SQL Server eine berechnete Spalte mit PERSISTED indizieren.
Symptom
Dieselbe Abfrage läuft im SQL-Werkzeug in Millisekunden und aus der Anwendung heraus mehrere Sekunden.
Ursache
Der Treiber schickt den Parameter mit einem anderen Datentyp, als die Spalte hat. Beim SQL Server konvertiert die Rangfolge dann die VARCHAR-Spalte nach NVARCHAR, bei MySQL wird eine Textspalte für den Vergleich mit einer Zahl in eine Zahl umgewandelt.
Lösung
Im Plan die Eigenschaften des Operators auf CONVERT_IMPLICIT prüfen und den Parametertyp im Anwendungscode an die Spalte anpassen. Wo das nicht möglich ist, bleibt die Umstellung der Spalte auf den Typ, den die Anwendung ohnehin liefert.
Symptom
Nach dem nächtlichen Import sind morgens genau die Abfragen langsam, die tagsüber wieder normal laufen.
Ursache
Die Statistiken stammen von vor dem Import. Der Optimizer hält die Tabelle für klein und wählt den vollständigen Zugriff, bis die automatische Pflege die neuen Zahlen eingesammelt hat.
Lösung
Die Statistikaktualisierung als letzten Schritt in den Ladejob aufnehmen, mit ANALYZE, DBMS_STATS.GATHER_TABLE_STATS, UPDATE STATISTICS oder ANALYZE TABLE, je nach System. Das dauert Sekunden und erspart die tägliche Fehlersuche.
Symptom
Bei einem Suchwert nutzt die Abfrage den Index, bei einem anderen Wert derselben Spalte nicht.
Ursache
Das ist meist richtig entschieden. Ein Statuskennzeichen mit einer Million Treffern für den Normalfall und dreißig Treffern für den Sonderfall gehört einmal in den Index und einmal in den Tabellenzugriff, und ein Histogramm auf der Spalte macht genau diesen Unterschied sichtbar.
Lösung
Wenn der häufige Wert nie gesucht werden muss, hilft ein Teilindex, in PostgreSQL mit WHERE-Klausel am CREATE INDEX, im SQL Server als gefilterter Index. Beide bleiben klein und werden dadurch für den Sonderfall wieder attraktiv.
Symptom
Ein Index existiert laut Katalog, taucht aber in keinem einzigen Plan auf und wird auch nach Wochen nicht benutzt.
Ursache
Entweder ist er unsichtbar oder deaktiviert, oder er dupliziert einen anderen Index mit demselben führenden Spaltenpräfix. Oracle und MySQL kennen unsichtbare Indizes, der SQL Server kennt deaktivierte.
Lösung
Die Nutzung messen statt vermuten: pg_stat_user_indexes in PostgreSQL, sys.dm_db_index_usage_stats im SQL Server, DBA_INDEX_USAGE ab Oracle 12.2 und sys.schema_unused_indexes in MySQL. Was über einen vollen Geschäftszyklus keinen Zugriff zeigt, kann weg.
Fünf Prüfungen, bevor du einen weiteren Index anlegst
- 01 Steht die Spalte in der Bedingung allein, ohne Funktion und ohne Umwandlung?
- 02 Ist sie die führende Spalte des Index oder nur eine der hinteren?
- 03 Wie viele Zeilen liefert die Bedingung wirklich, gezählt statt geschätzt?
- 04 Passen die geschätzten Zeilen im Plan zu den tatsächlich gelesenen?
- 05 Fehlt nur eine Spalte, für die zusätzlich in die Tabelle gegriffen wird?
Was du nach dieser Seite am Plan ablesen kannst
Die Prüfungen laufen alle vor der eigentlichen Optimierung und kosten je ein bis zwei Minuten. Sie beantworten der Reihe nach sechs Fragen: Kann die Abfrage den Index technisch benutzen? Steckt eine Typumwandlung dahinter, die niemand hingeschrieben hat? Passt die Spaltenreihenfolge? Lohnt sich der Zugriff bei dieser Trefferzahl? Muss für jede Trefferzeile zusätzlich in die Tabelle gegriffen werden? Und rechnet der Optimizer mit realistischen Zahlen?
Die Spalte nackt stehen lassen
Sobald eine Funktion oder eine Berechnung auf der Spalte liegt, greift ein gewöhnlicher Index nicht mehr. Statt WHERE YEAR(bestelldatum) = 2025 schreibst du einen Bereich auf die Spalte selbst, dann bedient ihn jeder B-Tree.
Umwandlungen finden, die niemand hingeschrieben hat
MySQL wandelt beim Vergleich einer Textspalte mit einer Zahl die Spalte um, nicht die Zahl. Der SQL Server konvertiert eine VARCHAR-Spalte, sobald der Parameter als NVARCHAR ankommt. In beiden Fällen ist der Index aus dem Spiel.
Zusammengesetzte Indizes von links lesen
Ein Index auf (kunde_id, bestelldatum) hilft bei einer Bedingung nur auf bestelldatum kaum. Oracle, MySQL und PostgreSQL können die führende Spalte inzwischen überspringen, aber nur unter engen Bedingungen und nie so gut wie ein passender Index.
Die Trefferzahl messen statt schätzen
Zähl mit COUNT(*), wie viele Zeilen die Bedingung tatsächlich liefert. Trifft sie einen nennenswerten Teil der Tabelle, ist der vollständige Tabellenzugriff die richtige Entscheidung und der Index nicht das Problem.
Den Rückgriff auf die Tabelle vermeiden
Fehlt im Index eine einzige abgefragte Spalte, muss das System für jede Trefferzeile zusätzlich in die Tabelle greifen. Mit INCLUDE beim SQL Server und bei PostgreSQL oder mit einer weiteren Indexspalte entfällt dieser Schritt.
Statistiken als Teil des Ladejobs pflegen
Nach einem großen Import passen die alten Verteilungen nicht mehr. Ein ANALYZE, ein DBMS_STATS-Aufruf oder ein UPDATE STATISTICS am Ende des Jobs verhindert Pläne, die auf der Tabellengröße von gestern beruhen.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Eine Funktion auf der Spalte schaltet den Index ab
Der Index steht auf nachname, gesucht wird mit WHERE UPPER(nachname) = 'MEIER'. Für das System sind das zwei verschiedene Dinge, denn im Index stehen die Werte so, wie sie in der Tabelle stehen, und nicht in Großbuchstaben. Dasselbe passiert bei WHERE YEAR(bestelldatum) = 2025, bei WHERE preis * 1.19 > 100 und bei jeder eigenen Funktion, die um die Spalte gelegt wird.
Die Systeme reagieren unterschiedlich darauf. PostgreSQL und Oracle nehmen einen Index auf genau diesen Ausdruck an, etwa CREATE INDEX kunden_nachname_upper ON kunden (upper(nachname)). Der SQL Server kennt keine Ausdrucksindizes, dort legst du eine berechnete Spalte an und indizierst diese. Ist der Ausdruck deterministisch und exakt, geht das direkt, in allen anderen Fällen musst du die Spalte mit PERSISTED speichern. MySQL kann seit 8.0.13 einen Funktionsindex direkt anlegen, davor führte der Weg ebenfalls über eine generierte Spalte.
Meist ist es besser, die Bedingung umzuschreiben, als einen zweiten Index zu pflegen. Aus YEAR(bestelldatum) = 2025 wird bestelldatum >= '2025-01-01' AND bestelldatum < '2026-01-01'. Das ist ein Bereich auf der unveränderten Spalte, den jeder B-Tree bedient, und es funktioniert in allen vier Systemen gleich. Der Sonderfall bleibt die Suche ohne Rücksicht auf Groß- und Kleinschreibung, dafür ist der Ausdrucksindex tatsächlich der richtige Weg.
Die Umwandlung, die niemand hingeschrieben hat
Der zweite Fall ist unauffälliger, weil im SQL keine Funktion zu sehen ist. Steht die Kundennummer als VARCHAR in der Tabelle und übergibst du sie als Zahl, wandelt MySQL nicht die Zahl in Text um, sondern die ganze Spalte in eine Zahl. Der Index auf dieser Spalte ist damit für die gesamte Tabelle wirkungslos, und die Abfrage sieht dabei völlig harmlos aus.
Der SQL Server hat eine feste Rangfolge der Datentypen, und NVARCHAR steht darin über VARCHAR. Ein Parameter, den der Anwendungstreiber als Unicode schickt, führt deshalb dazu, dass die VARCHAR-Spalte konvertiert wird und nicht der Parameter. Im Ausführungsplan steht das als CONVERT_IMPLICIT in den Eigenschaften des Operators, dazu zeigt der Plan eine Warnung, dass eine Typumwandlung die Schätzung beeinflussen kann.
Der Auslöser sitzt fast immer im Anwendungscode und nicht im SQL. Treiber für Java und .NET setzen für Zeichenketten standardmäßig Unicode-Parameter, während die Tabelle aus dem Altbestand auf VARCHAR steht. Das erklärt auch den häufigsten Begleiteffekt: Dieselbe Abfrage ist aus dem SQL-Werkzeug schnell und aus der Anwendung langsam. Prüf deshalb bei jedem verdächtigen Plan zuerst den Datentyp der Spalte gegen den Datentyp des Parameters.
Zusammengesetzte Indizes werden von links gelesen
Ein Index auf (kunde_id, bestelldatum) ist sortiert wie ein Telefonbuch nach Nachname und Vorname. Für eine Bedingung auf kunde_id ist er perfekt, für eine Bedingung auf kunde_id und bestelldatum ebenfalls, für eine Bedingung nur auf bestelldatum taugt er kaum etwas. Diese Regel gilt in allen vier Systemen und erklärt einen großen Teil der scheinbar ignorierten Indizes.
Umgehen lässt sie sich nur teilweise. Oracle kennt seit Langem den Index Skip Scan und setzt ihn ein, wenn die führende Spalte wenige verschiedene Werte hat, etwa ein Statuskennzeichen mit drei Ausprägungen. MySQL beherrscht ein vergleichbares Verfahren seit 8.0.13 als Skip Scan im Bereichszugriff. PostgreSQL hat es mit Version 18 bekommen, davor konnte es den Index nur vollständig durchlesen. Der SQL Server bietet dafür nichts an, dort bleibt nur ein zusätzlicher Index oder eine geänderte Reihenfolge.
Für die Praxis heißt das: Die Spalte, auf die am häufigsten mit Gleichheit geprüft wird, gehört nach vorn, danach folgt die Spalte für den Bereich. Ein Index auf (kunde_id, bestelldatum) bedient auch die Abfrage nach einem Kunden in einem Zeitraum, während (bestelldatum, kunde_id) für genau diese Abfrage deutlich mehr Indexseiten lesen muss.
Wenn sich der Umweg über den Index nicht mehr lohnt
Ein Indexzugriff liefert zunächst nur einen Verweis auf die Zeile. Alles, was nicht im Index steht, holt das System anschließend aus der Tabelle, und zwar für jede Trefferzeile einzeln und in zufälliger Reihenfolge. Ab einer bestimmten Trefferzahl ist es billiger, die Tabelle einmal am Stück zu lesen. Der Optimizer entscheidet das bewusst, und wenn er richtig liegt, ist der ungenutzte Index kein Fehler, sondern das erwartete Verhalten.
Wie früh diese Grenze erreicht ist, hängt davon ab, wie gut die Sortierung des Index zur physischen Anordnung der Tabelle passt. Oracle bildet das als Clustering Factor in den Indexstatistiken ab, ein Wert nahe der Blockzahl der Tabelle ist gut, ein Wert nahe der Zeilenzahl ist schlecht. Beim SQL Server steckt dieselbe Überlegung in der Entscheidung zwischen Index Seek mit Key Lookup und Index Scan, in PostgreSQL findest du sie als correlation in pg_stats.
Der Ausweg ist der abdeckende Index. Nimmst du die wenigen zusätzlich abgefragten Spalten mit auf, entfällt der Rückgriff auf die Tabelle vollständig. SQL Server und PostgreSQL bieten dafür INCLUDE an, was die Spalten nur im Blattknoten ablegt und den Index schmal hält. Bei PostgreSQL kommt eine Besonderheit dazu: Der Index Only Scan funktioniert nur, wenn die Visibility Map die Seiten als sichtbar markiert, und die pflegt das Vacuum. Nach einem großen Update greift PostgreSQL deshalb vorübergehend wieder auf die Tabelle zu, obwohl der Index alles enthält.
Statistiken, die nicht mehr zur Tabelle passen
Der Optimizer rechnet nicht mit den Daten, sondern mit einer Beschreibung der Daten. Passt die nicht mehr, entstehen Pläne, die auf dem Zustand von vorgestern beruhen. Der typische Auslöser ist ein Import: Die Tabelle wächst nachts von zehntausend auf zehn Millionen Zeilen, die Statistik kennt noch die zehntausend, und für so wenige Zeilen ist der vollständige Tabellenzugriff tatsächlich die bessere Wahl.
Die Pflege unterscheidet sich deutlich. In PostgreSQL sammelt der Autovacuum-Prozess die Statistiken nebenbei ein, nach einem Massenimport lohnt sich trotzdem ein ausdrückliches ANALYZE, weil der Job sonst mit alten Zahlen weiterläuft. Oracle sammelt sie über einen automatischen Task im Wartungsfenster, im Ladejob rufst du DBMS_STATS.GATHER_TABLE_STATS selbst auf. Der SQL Server aktualisiert automatisch, sobald genügend Zeilen geändert wurden. Seit Version 2016 und Kompatibilitätsgrad 130 gilt dafür eine dynamische Schwelle, die mit wachsender Tabelle relativ kleiner wird, sodass gerade große Tabellen häufiger an die Reihe kommen als unter der alten Regel aus 500 Zeilen plus 20 Prozent. Nur wer noch mit einem niedrigeren Kompatibilitätsgrad arbeitet, hat weiterhin die alte Schwelle und damit Tabellen, die selten von allein aktualisiert werden. MySQL schätzt in InnoDB anhand einer Stichprobe von Indexseiten, ANALYZE TABLE erzwingt eine neue Stichprobe.
Eine zweite Ursache ist hartnäckiger: Der Optimizer nimmt an, dass Spalten unabhängig voneinander sind. Postleitzahl und Ort sind das nicht, und bei einer Bedingung auf beide schätzt er die Trefferzahl viel zu niedrig. Dagegen hilft eine mehrspaltige Statistik, in PostgreSQL über CREATE STATISTICS, in Oracle über erweiterte Statistiken auf einer Spaltengruppe, im SQL Server über eine benutzerdefinierte Statistik über beide Spalten.
Dazu passende Kurse
Die Entscheidung zwischen Indexzugriff und Tabellenzugriff versteht man am schnellsten an eigenen Daten, deshalb gibt es bei cmt Datenbankkurse, in denen Indizes und Ausführungspläne durchgearbeitet werden .
Wer den Zusammenhang zwischen Vacuum, Visibility Map und Index Only Scan nachvollziehen will, findet ihn in die PostgreSQL-Reihe mit eigenem Teil zu Statistiken und Vacuum .
Wie sicher bist du beim Thema wirklich?
Lesen fühlt sich schnell nach Können an. Ein kurzer Test zeigt dir, was davon schon sitzt und wo sich ein Kurs lohnt. Kostenlos, ohne Anmeldung, mit einer Erklärung zu jeder Antwort.
Wo du genau das übst
Super Schulung, in der der Trainer auch auf eigene Fragestellungen eingegangen ist.
Super Grundlagenschulung mit allen wichtigen Punkten und hilfreichen Tipps!
Schön, sich 5 Tage ungestört mit Postgres beschäftigen zu können.
Häufige Fragen
Hilft es, für jede langsame Abfrage einen weiteren Index anzulegen?
Kann ich das System zwingen, den Index zu benutzen?
Warum ist die Abfrage nach dem neuen Index sogar langsamer geworden?
Gilt das alles auch für Fremdschlüsselspalten?
Deine Ansprechpartner
Du bist dir nicht sicher, welcher Kurs oder welches Level zu dir passt? Wir beraten dich persönlich und kostenlos.
Yves Hoppe
Weiterbildung & Beratung
Hilft dir, aus dem Datenbanken-Programm den passenden Kurs für deinen Stand zu finden.
Norbert Jansen
Beratung & Inhouse
Plant mit dir Inhouse-Trainings, die auf eure Abläufe und euren Datenbestand zugeschnitten sind.
Vom Raten zur begründeten Entscheidung über Zugriffspfade
Wie du Zugriffspfade misst, Statistiken pflegst und überflüssige Indizes wieder abbaust, arbeitest du bei cmt an einem echten Datenbestand durch.