Optimizer-Hints gezielt einsetzen, ohne den Optimizer auszutricksen

1. Was der Optimizer standardmäßig tut und wann er irrt

Der MySQL Query Optimizer entscheidet für jede Anfrage automatisch, welcher Ausführungsplan voraussichtlich am effizientesten ist, basierend auf Tabellenstatistiken, Indexkardinalität und geschätzten Kosten für verschiedene Zugriffspfade. Diese Entscheidung trifft der Optimizer in aller Regel richtig, denn moderne Kostenmodelle berücksichtigen Faktoren wie I/O-Kosten, CPU-Kosten und die Selektivität von Filterbedingungen. In den allermeisten Fällen ist ein manueller Eingriff über Optimizer-Hints weder nötig noch sinnvoll.

Der Optimizer irrt sich jedoch systematisch in bestimmten, wiederkehrenden Situationen. Veraltete Tabellenstatistiken nach großen Bulk-Inserts oder -Deletes ohne anschließenden ANALYZE TABLE führen zu falschen Kardinalitätsschätzungen. Stark schiefe Datenverteilungen, bei denen ein Indexwert neunzig Prozent aller Zeilen abdeckt und ein anderer nur ein Prozent, überfordern die Standard-Histogramme in manchen Versionen. Komplexe Anfragen mit vielen JOINs können zudem eine suboptimale Join-Reihenfolge wählen, weil der Suchraum für die perfekte Reihenfolge exponentiell wächst und der Optimizer nach einer begrenzten Anzahl Permutationen abbricht.

Genau für diese Ausnahmefälle existieren Optimizer-Hints, gezielte Anweisungen an den Optimizer, eine bestimmte Entscheidung zu erzwingen oder einzuschränken, statt der automatischen Kostenschätzung vollständig zu vertrauen. Wichtig ist dabei die Reihenfolge der Maßnahmen: Ein Hint sollte immer erst nach einer bestätigten Fehlentscheidung des Optimizers eingesetzt werden, belegt durch EXPLAIN, nicht vorbeugend aus Unsicherheit.

-- Check what the optimizer chose and why, before reaching for a hint
EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending';
-- +----+-------+---------------+------+---------+------+
-- | id | table | possible_keys | key  | key_len | rows |
-- +----+-------+---------------+------+---------+------+
-- |  1 | orders| idx_customer  | NULL | NULL    | 84021|
-- +----+-------+---------------+------+---------+------+
-- possible_keys shows idx_customer, but key is NULL:
-- the optimizer chose a full table scan instead

2. USE INDEX, FORCE INDEX und IGNORE INDEX

Die klassischen Index-Hints, USE INDEX, FORCE INDEX und IGNORE INDEX, existieren seit sehr frühen MySQL-Versionen und werden direkt hinter dem Tabellennamen in der FROM-Klausel notiert. USE INDEX(idx_name) schränkt die Auswahl des Optimizers auf die genannten Indizes ein, überlässt ihm aber weiterhin die Entscheidung, ob überhaupt ein Index oder ein Tabellenscan gewählt wird. FORCE INDEX(idx_name) geht einen Schritt weiter und zwingt den Optimizer, den genannten Index zu verwenden, selbst wenn seine eigene Kostenschätzung einen Tabellenscan für günstiger hält.

IGNORE INDEX(idx_name) schließt einen bestimmten Index explizit von der Betrachtung aus, nützlich in Situationen, in denen der Optimizer fälschlicherweise einen ungeeigneten Index bevorzugt, während ein anderer, nicht genannter Index oder ein Tabellenscan tatsächlich schneller wäre. Alle drei Hints akzeptieren zusätzlich die Angabe des Kontexts, etwa FORCE INDEX FOR JOIN oder FORCE INDEX FOR ORDER BY, um den Hint gezielt nur für einen bestimmten Teil der Anfrageverarbeitung wirken zu lassen.

-- USE INDEX: narrows the candidate set, optimizer still decides
SELECT * FROM orders USE INDEX (idx_customer_status)
WHERE customer_id = 42 AND status = 'pending';

-- FORCE INDEX: overrides the optimizer's own cost estimate
SELECT * FROM orders FORCE INDEX (idx_customer_status)
WHERE customer_id = 42 AND status = 'pending';

-- IGNORE INDEX: excludes a specific index from consideration
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE customer_id = 42 AND status = 'pending';

-- Scoped to a specific phase of query processing
SELECT * FROM orders FORCE INDEX FOR ORDER BY (idx_created_at)
WHERE customer_id = 42 ORDER BY created_at DESC;

3. STRAIGHT_JOIN und die Join-Reihenfolge

STRAIGHT_JOIN zwingt den Optimizer, die Tabellen exakt in der im SQL-Statement angegebenen Reihenfolge zu verknüpfen, statt selbst eine vermeintlich bessere Reihenfolge zu berechnen. Der Optimizer wählt normalerweise die Join-Reihenfolge basierend auf geschätzten Zwischenergebnisgrößen, um die Anzahl der insgesamt zu verarbeitenden Zeilenkombinationen zu minimieren. Bei komplexen Anfragen mit mehr als fünf oder sechs Tabellen wird der Suchraum für alle möglichen Join-Reihenfolgen jedoch so groß, dass der Optimizer aus Performance-Gründen nur eine Teilmenge der Permutationen tatsächlich bewertet.

In solchen Fällen kann eine von einem erfahrenen Entwickler manuell gewählte Join-Reihenfolge, kombiniert mit STRAIGHT_JOIN, tatsächlich effizienter sein als die automatisch gewählte, insbesondere wenn der Entwickler die tatsächliche Selektivität der Filterbedingungen besser einschätzen kann als die vom Optimizer verwendeten, möglicherweise veralteten Statistiken. Wichtig: STRAIGHT_JOIN wirkt auf die gesamte Anfrage und erfordert daher, dass die Tabellenreihenfolge im Statement bewusst von klein nach groß beziehungsweise von restriktivstem zu am wenigsten restriktivem Filter gewählt wird.

-- Optimizer picks the join order automatically
SELECT o.id, c.name, p.title
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = o.product_id
WHERE c.country = 'DE';

-- STRAIGHT_JOIN forces the exact order written: customers first
-- (useful when country='DE' is highly selective and the optimizer
-- misjudges it due to stale statistics)
SELECT STRAIGHT_JOIN c.name, o.id, p.title
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN products p ON p.id = o.product_id
WHERE c.country = 'DE';

4. Die Optimizer-Hint-Syntax seit MySQL 8

Mit MySQL 8.0 wurde eine deutlich feingranularere und modernere Hint-Syntax eingeführt, notiert als Kommentar direkt nach dem SELECT-Schlüsselwort in der Form /*+ HINT_NAME(args) */. Diese Syntax ersetzt nicht die klassischen Index-Hints, ergänzt sie aber um deutlich mehr Kontrolle, etwa JOIN_ORDER zur expliziten Angabe der Join-Reihenfolge ohne die physische Umstellung des SQL-Statements, INDEX und NO_INDEX als modernere Alternativen zu FORCE INDEX und IGNORE INDEX, sowie MAX_EXECUTION_TIME zur Begrenzung der maximalen Ausführungszeit einer einzelnen Anfrage.

Der entscheidende Vorteil dieser neuen Syntax gegenüber den klassischen Hints liegt darin, dass sie als Kommentar geparst wird und daher von älteren MySQL-Versionen oder anderen Datenbanksystemen ignoriert statt als Syntaxfehler abgelehnt wird, was portable Codebasen erleichtert. Zudem lassen sich mehrere Hints in einem einzigen Kommentarblock kombinieren, was komplexere, gezielte Eingriffe in einer einzigen, klar lesbaren Zeile erlaubt.

-- Modern hint syntax (MySQL 8.0+): comment-based, ignored by older parsers
SELECT /*+ INDEX(orders idx_customer_status) */ *
FROM orders
WHERE customer_id = 42 AND status = 'pending';

-- Explicit join order without rewriting table order in FROM
SELECT /*+ JOIN_ORDER(c, o, p) */ c.name, o.id, p.title
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = o.product_id
WHERE c.country = 'DE';

-- Bounding a potentially runaway query in an admin tool
SELECT /*+ MAX_EXECUTION_TIME(2000) */ * FROM orders
WHERE YEAR(created_at) = 2024;

5. Index-Hints versus aktualisierte Statistiken

Bevor überhaupt ein Optimizer-Hint in Betracht gezogen wird, sollte die naheliegendste Ursache für Fehlentscheidungen des Optimizers ausgeschlossen werden: veraltete Tabellenstatistiken. ANALYZE TABLE aktualisiert die Kardinalitätsschätzungen, auf denen der Optimizer seine Kostenberechnung aufbaut, und behebt in der Praxis einen erheblichen Anteil der Fälle, in denen Entwickler vorschnell zu einem Hint greifen. Nach großen Datenimporten, Bulk-Löschungen oder signifikanten Änderungen der Datenverteilung ist ein manueller oder automatisierter ANALYZE TABLE-Lauf oft die einfachere und robustere Lösung als ein dauerhaft im Code verankerter Hint.

Der Unterschied in der Wartbarkeit ist erheblich: Aktualisierte Statistiken passen sich künftigen Datenverteilungen automatisch an, während ein fest im SQL-Code verankerter FORCE INDEX-Hint bei jeder Schema-Änderung oder Datenverteilungsverschiebung manuell überprüft werden muss. Die Faustregel lautet daher, erst ANALYZE TABLE zu versuchen und die Wirkung mit EXPLAIN zu verifizieren, bevor ein Hint als dauerhafte Lösung in den Code aufgenommen wird.

6. Wann ein Hint ein Code Smell ist

Ein Optimizer-Hint wird zum Code Smell, sobald er ohne vorherige Diagnose mit EXPLAIN eingesetzt wird, quasi als reflexartige Reaktion auf eine als langsam empfundene Anfrage. Ebenso problematisch ist ein Hint, der eine grundsätzlich fehlerhafte Schema-Modellierung kaschiert, etwa einen fehlenden zusammengesetzten Index, statt diesen einfach anzulegen. Ein FORCE INDEX auf einen suboptimalen Index ist in solchen Fällen nur ein Pflaster über einer tieferliegenden strukturellen Schwäche, die früher oder später erneut auffällt, sobald sich die Datenverteilung ändert.

Besonders riskant sind Hints, die ohne Dokumentation und ohne begleitenden Kommentar im Code verbleiben. Ein späterer Entwickler, der die Anfrage optimieren möchte, sieht den Hint, versteht aber nicht, welche konkrete Fehlentscheidung des Optimizers er ursprünglich korrigieren sollte, und kann daher nicht beurteilen, ob der Hint nach einem Upgrade oder einer Schema-Änderung noch notwendig ist. Jeder Optimizer-Hint im Produktivcode sollte daher immer von einem Kommentar begleitet werden, der die ursprüngliche Fehlentscheidung des Optimizers und das Datum der EXPLAIN-Analyse dokumentiert.

7. Wann ein Hint legitim ist

Ein Optimizer-Hint ist legitim, wenn drei Bedingungen gleichzeitig erfüllt sind: Erstens wurde die Fehlentscheidung des Optimizers durch EXPLAIN oder EXPLAIN ANALYZE konkret nachgewiesen, nicht nur vermutet. Zweitens wurden einfachere Lösungen wie aktualisierte Statistiken oder ein neu angelegter Index bereits ausprobiert und als unzureichend erkannt. Drittens ist der Hint dokumentiert, mit einem Kommentar, der erklärt, warum er nötig ist und wann er zuletzt überprüft wurde.

Typische legitime Anwendungsfälle sind Reporting-Queries mit stark schiefen Datenverteilungen, bei denen der Optimizer aufgrund unzureichender Histogramm-Granularität systematisch falsch schätzt, sowie komplexe Multi-Table-Joins in Batch-Prozessen, bei denen der Entwickler die Datenverteilung besser kennt als die zum Zeitpunkt der Anfrage verfügbaren Statistiken. Auch MAX_EXECUTION_TIME als defensive Maßnahme gegen Runaway-Queries in Admin-Tools ist ein klar legitimer, risikoarmer Einsatz eines Hints, weil er keine Optimierungslogik überschreibt, sondern nur eine Obergrenze setzt.

8. Hints und Wartbarkeit bei Schema-Änderungen

Ein zentrales Risiko von Optimizer-Hints liegt in ihrer Fragilität gegenüber Schema-Änderungen. Wird ein Index, auf den sich ein FORCE INDEX bezieht, umbenannt oder entfernt, schlägt die betroffene Anfrage mit einem klaren Fehler fehl, was zwar auf den ersten Blick sicherer wirkt als ein stiller Performance-Verlust, in der Praxis aber ungeplante Ausfälle nach eigentlich harmlosen Wartungsarbeiten verursachen kann. Ein Team, das einen scheinbar ungenutzten Index löscht, ohne zu wissen, dass er in einem FORCE INDEX-Hint referenziert wird, produziert damit unbeabsichtigt einen Produktionsausfall.

Die moderne Hint-Syntax mit /*+ ... */ mildert dieses Risiko teilweise, weil ungültige oder nicht mehr anwendbare Hints in vielen Fällen als Warnung statt als Fehler behandelt werden, wodurch die Anfrage weiterläuft, wenn auch möglicherweise mit einem suboptimalen Plan. Unabhängig von der gewählten Syntax gilt jedoch: Jede Migration, die einen von einem Hint referenzierten Index betrifft, muss den betroffenen Anwendungscode als Teil derselben Änderung mit anpassen, nicht als nachgelagerte Aufgabe.

-- Documented hint: the only acceptable way to keep one in production code
-- Reason: optimizer chose a full scan on skewed status distribution
-- Verified with EXPLAIN ANALYZE on 2026-06-14, MySQL 8.0.36
SELECT /*+ INDEX(orders idx_status_created) */ id, total
FROM orders
WHERE status = 'refunded'
ORDER BY created_at DESC
LIMIT 100;

-- Review checklist before removing a legacy FORCE INDEX hint:
-- 1. Run ANALYZE TABLE orders;
-- 2. Compare EXPLAIN with and without the hint
-- 3. If plans match or the hint-free plan is equal/better, remove it

9. Hints regelmäßig validieren statt vergessen

Ein Optimizer-Hint, der heute korrekt ist, kann in einem Jahr durch gewachsene Datenmengen, verbesserte Optimizer-Versionen oder veränderte Zugriffsmuster überflüssig oder sogar kontraproduktiv geworden sein. Der MySQL-Optimizer wird mit jeder Major-Version weiterentwickelt, sodass ein Hint, der eine Fehlentscheidung in MySQL 5.7 korrigierte, nach einem Upgrade auf MySQL 8.0 unter Umständen eine inzwischen bessere automatische Entscheidung blockiert.

Empfehlenswert ist ein periodischer Review-Prozess, etwa einmal pro Quartal oder nach jedem Major-Upgrade, bei dem alle im Code vorhandenen Optimizer-Hints systematisch aufgelistet und mit einem frischen EXPLAIN ANALYZE gegen den aktuellen Zustand ohne Hint verglichen werden. Zeigt sich, dass der Optimizer inzwischen eigenständig zur gleichen oder einer besseren Entscheidung kommt, sollte der Hint entfernt werden, um die Codebasis schlank zu halten und dem Optimizer wieder die volle Entscheidungsfreiheit zurückzugeben.

10. Zusammenfassung

Optimizer-Hints wie USE INDEX, FORCE INDEX, STRAIGHT_JOIN und die moderne /*+ ... */-Syntax seit MySQL 8 sind mächtige Werkzeuge, aber kein Ersatz für saubere Schema-Modellierung und aktuelle Tabellenstatistiken. Der Optimizer trifft die überwiegende Mehrheit seiner Entscheidungen richtig, weshalb ein Hint immer erst nach einem konkreten, mit EXPLAIN belegten Fehlurteil in Betracht kommen sollte, niemals vorbeugend oder reflexartig.

Legitime Einsätze zeichnen sich durch drei Merkmale aus: nachgewiesene Fehlentscheidung, bereits ausgeschöpfte einfachere Alternativen wie ANALYZE TABLE, und lückenlose Dokumentation im Code. Ohne diese Disziplin werden Optimizer-Hints schnell zum Code Smell, der tiefer liegende Schema-Probleme kaschiert und bei künftigen Migrationen zur Falle wird. Ein regelmäßiger Review-Prozess stellt sicher, dass alte Hints nicht dauerhaft im Code verbleiben, nachdem der Optimizer selbst längst besser geworden ist.

11. FAQ: Optimizer-Hints gezielt einsetzen