Die Aufgabe des Abfrageoptimierers besteht darin, einen optimalen Plan für die Ausführung einer SQL-Abfrage zu entwickeln. Da der Unterschied zwischen „gut“ und „schlecht“ aus leistungstechnischer Sicht mehrere Größenordnungen betragen kann (d. h. Sekunden im Vergleich zu Stunden oder sogar Tagen), führten die meisten Abfrageoptimierer (einschließlich des MySQL-Optimierers) unter allen möglichen Plänen zur Abfragebewertung eine mehr oder minder umfangreiche Suche nach dem optimalen Plan durch. Bei Join-Abfragen wächst die Anzahl möglicher Pläne, die vom MySQL-Optimierer untersucht werden, exponentiell mit der Anzahl der Tabellen, die in einer Abfrage referenziert werden. Bei einer kleinen Anzahl von Tabellen (etwa 7 bis 10) ist dies unproblematisch. Werden hingegen größere Abfragen abgesetzt, dann kann die für die Abfrageoptimierung erforderliche Zeit schnell zum Engpass für die Leistung des Servers werden.
Eine flexiblere Methode zur Abfrageoptimierung gewährt dem Benutzer Kontrolle darüber, wie erschöpfend der Optimierer bei der Suche nach einem optimalen Abfragebewertungsplan vorgeht. Hintergedanke ist, dass umso weniger Zeit für die Kompilierung einer Abfrage aufgewendet wird, je weniger Pläne vom Optimierer untersucht werden. Andererseits kann es sein, dass der Optimierer, weil er einige Pläne überspringt, den optimalen Plan übersieht.
Das Verhalten des Optimierers in Bezug auf die Anzahl der Pläne, die er bewertet, kann über zwei Systemvariablen gesteuert werden:
Die Variable optimizer_prune_level
weist
den Optimierer an, bestimmte Pläne basierend auf der
geschätzten Anzahl der Datensätze, auf die pro Tabelle
zugegriffen wird, zu übergehen. Unsere Erfahrung zeigt,
dass diese Art der „begründeten Annahme“
optimale Pläne nur in sehr seltenen Fällen verfehlt. Aus
diesem Grund ist die Option standardmäßig aktiviert
(optimizer_prune_level=1
). Wenn Sie
allerdings das Gefühl haben, dass der Optimierer einen
besseren Abfrageplan hätte finden können, dann können Sie
die Option auch abschalten
(optimizer_prune_level=0
); allerdings
besteht dann das Risiko, dass die Abfragekompilierung
deutlich länger dauert. Beachten Sie, dass der Optimierer
auch bei Verwendung dieser Heuristik eine grob exponentielle
Anzahl von Plänen untersucht.
Die Variable optimizer_search_depth
sagt
dem Optimierer, wie weit er bei unvollständigen Plänen
„vorausschauen“ soll, um einzuschätzen, ob ein
Plan erweitert werden soll. Niedrige Werte für
optimizer_search_depth
führen zu um
mehrere Größenordnungen kürzeren
Abfragekompilierungszeiten. So kann die Kompilierung von
Abfragen mit 12, 13 oder mehr Tabellen leicht Stunden oder
sogar Tage dauern, wenn
optimizer_search_depth
einen Wert hat,
der annähernd der Anzahl der Tabellen in der Abfrage
entspricht. Umgekehrt benötigt der Compiler für dieselbe
Abfrage noch nicht einmal eine Minute, wenn
optimizer_search_depth
den Wert 3 oder 4
hat. Wenn Sie nicht sicher sind, welcher Wert für
optimizer_search_depth
sinnvoll sein
könnte, können Sie ihn auf 0 setzen; so weisen Sie den
Optimierer an, den Wert automatisch zu ermitteln.
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.