Grundlegendes zum MySQL-Abfrageoptimierungsprozess

Die MySQL-Abfrageoptimierung erfordert drei Schritte: Parsen, Vorverarbeitung und Optimierung. Bei jedem dieser Prozesse können Fehler auftreten. In diesem Artikel wird nicht ausführlich auf die Fehlerbehandlung eingegangen, er soll Ihnen jedoch dabei helfen, zu verstehen, wie MySQL Abfragen ausführt, damit Sie bessere Abfragen schreiben können.

Parser und Präprozessoren

Zunächst zerlegt der MySQL-Parser die Abfrage in eine Reihe von Anweisungen und erstellt daraus einen „Analysebaum“. Der Parser verwendet die SQL-Syntax von MySQL, um Abfrageanweisungen zu übersetzen und zu validieren. Der Parser stellt beispielsweise sicher, dass die Anweisungen in der Abfrage gültig und in der richtigen Reihenfolge sind, und prüft, ob Fehler wie nicht übereinstimmende Anführungszeichen in Zeichenfolgen vorliegen.

Der Präprozessor überprüft den erstellten Analysebaum auf semantische Informationen, die der Parser nicht verarbeiten kann. Dabei wird beispielsweise die Existenz von Tabellen und Spalten geprüft und Feldnamen und Aliase aufbereitet, um die Eindeutigkeit von Spaltenverweisen sicherzustellen. Als Nächstes überprüft der Präprozessor die Berechtigungen, was normalerweise ziemlich schnell geht (es sei denn, auf Ihrem Server sind eine Reihe von Berechtigungen konfiguriert).

Abfrage-Optimierer

Nach dem Durchlaufen des Parsers und des Präprozessors wird der Parsebaum als gültig eingestuft und kann vom Optimierer verarbeitet und schließlich in einen Abfrageplan umgewandelt werden. Es gibt oft viele Möglichkeiten, eine Abfrage auszuführen, die zum selben Ergebnis führt, und die Aufgabe des Optimierers besteht darin, die beste Option zu finden.

MySQL verwendet einen auf Kostenschätzung basierenden Optimierer. Das heißt, es versucht, die Kosten mehrerer Ausführungspläne vorherzusagen und wählt denjenigen mit den niedrigsten Kosten aus. Die ursprünglichen Stückkosten entsprachen dem zufälligen Lesen einer 4-KB-Datenseite. Jetzt ist es jedoch komplexer geworden und umfasst die Kosten für die Durchführung von WHERE-Vergleichsbedingungen. Sie können die Kostenschätzung des Abfrageoptimierers für eine Abfrageanweisung anzeigen, indem Sie die Sitzungsvariable Last_query_cost anzeigen.

Wählen Sie SQL_NO_CACHE COUNT(*) aus sakila.film_actor;
STATUS ANZEIGEN WIE „Last_query_cost“;

Die angezeigten Last_query_cost bedeuten, dass der Optimierer schätzt, dass er die entsprechende Anzahl an zufälligen Datenseitenzugriffen durchführen muss, um die Abfrage abzuschließen. Dies basiert auf den folgenden statistischen Schätzungen:

• Die Anzahl der Datenseiten, die von der Datentabelle oder dem Index belegt werden;
• Kandidatenwerte für den Index;
• Die Datenlänge entspricht der Datenzeilen-, Schlüssel- und Schlüsselwertverteilung.
  

Der Optimierer berücksichtigt keine Cache-Schätzungen – er geht davon aus, dass das Ergebnis jedes Mal vom Festplatten-E/A gelesen wird. Aus folgenden Gründen wählt der Optimierer möglicherweise nicht immer den besten Ausführungsplan:

• Die Statistiken selbst können falsch sein. Die statistischen Ergebnisse auf der Serverseite hängen von der Speicher-Engine ab und können sehr genau oder sehr ungenau sein. Beispielsweise speichert InnoDB aufgrund seiner MVCC-Architektur nicht die genaue Zeilenanzahl einer Tabelle.
• Die geschätzten Kosten entsprechen nicht den tatsächlichen Betriebskosten. Selbst wenn die Statistiken genau sind, weichen die Abfragekosten daher mehr oder weniger stark von der MySQL-Schätzung ab. Ein Abfrageplan, der mehr Datenseiten liest, kann auch kostengünstiger sein, beispielsweise wenn der Festplatten-E/A-Zugriff in sequenzieller Reihenfolge schneller erfolgt oder wenn sich die Ergebnisse bereits im Cache befinden. Daher weiß der Optimierer selbst nicht, wie viele E/A-Operationen eine Abfrage nach sich ziehen wird.
• Die künstliche Optimierung von MySQL kann von unseren Erwartungen abweichen. Was wir wollen, ist möglicherweise eine schnellere Ausführungszeit, aber bei MySQL geht es nicht nur um Geschwindigkeit, sondern auch darum, die Kosten zu minimieren. Deshalb ist die Weitergabe dieser Kosten nicht unbedingt wissenschaftlich fundiert.
• MySQL berücksichtigt keine gleichzeitigen Abfragen, was sich auf die Geschwindigkeit auswirken kann, mit der Abfragen ausgeführt werden.
• MySQL führt nicht immer eine Optimierung auf Grundlage von Kostenschätzungen durch. Manchmal müssen lediglich einige Regeln befolgt werden, z. B. die Verwendung des Volltextindexes, wenn eine Volltext-Übereinstimmungsbedingung vorliegt (MATCH-Methode). Selbst wenn ein schnellerer alternativer Index und eine Nicht-Volltextabfragebedingung vorhanden sind, führt MySQL die Abfrage nicht schneller aus.
• Der Optimierer berücksichtigt nicht die Kosten von Vorgängen, die nicht unter seiner Kontrolle stehen, wie beispielsweise die Ausführung gespeicherter Prozeduren oder benutzerdefinierter Funktionen.
• Der Optimierer kann nicht immer jeden Ausführungsplan einschätzen und übersieht manchmal einen optimaleren Plan.
  

Der MySQL-Abfrageoptimierer ist ein sehr komplexes Teil, das viele Optimierungsmethoden verwendet, um eine Abfrageanweisung in einen Abfrageausführungsplan umzuwandeln. Es gibt normalerweise zwei Arten der Optimierung: statische Optimierung und dynamische Optimierung. Die statische Optimierung kann einfach durch die Überprüfung des Analysebaums durchgeführt werden. Beispielsweise kann der Optimierer die WHERE-Bedingung durch mathematische Operationsregeln in eine Gleichung umwandeln. Die statische Optimierung bezieht sich nicht auf konkrete Werte, wie etwa konstante Werte in WHERE-Bedingungen. Sie werden einmal ausgeführt und bleiben gültig, auch wenn die Abfrage mit anderen Werten erneut ausgeführt wird. Es kann als „Optimierung zur Kompilierungszeit“ verstanden werden.

Im Gegensatz dazu ist die dynamische Optimierung kontextspezifisch und hängt von einer Vielzahl von Faktoren ab. Beispielsweise der Wert in der WHERE-Bedingung oder die entsprechende Anzahl der Datenzeilen im Index. Dieser Vorgang muss bei jeder Abfrage neu geschätzt werden und kann als „Laufzeitoptimierung“ verstanden werden. Hier sind einige typische Optimierungsmethoden für MySQL:

• Neuanordnung von Union-Abfragen: Datentabellen müssen nicht unbedingt in der Reihenfolge der Abfrageanweisungen vereinigt werden. Die Bestimmung der besten Reihenfolge zum Zusammenführen von Abfragen ist eine sehr wichtige Optimierung.
• Äußere Verknüpfungen in innere Verknüpfungen umwandeln: Eine äußere Verknüpfung muss nicht zwangsläufig als äußere Verknüpfung abgefragt werden. Einige Faktoren wie WHERE-Bedingungen und die Struktur der Datentabelle können dazu führen, dass eine Outer-Join-Abfrage einem Inner-Join entspricht. MySQL kann diese Fälle erkennen und die Join-Abfrage umschreiben.
• Mathematisch äquivalente Formeln anwenden: MySQL wendet mathematisch äquivalente Konvertierungen an, um Ausdrücke zu vereinfachen. Dies kann durch die Erweiterung und Reduzierung von Konstanten sowie die Eliminierung unmöglicher Fälle und konstanter Ausdrücke erfolgen. Beispielsweise wird der Ausdruck (5=5 UND a>5) zu (a>5) vereinfacht. Ebenso (a 5 UND b=c UND a=5). Diese Regeln sind sehr nützlich für Abfragen mit Bedingungen.
• COUNT()-, MIN()- und MAX()-Optimierungen: Indizes und Spalten mit Nullwerten können MySQL häufig dabei helfen, diese Funktionen zu optimieren. Wenn Sie beispielsweise nach dem Minimalwert in der äußersten linken Spalte eines Binärbaums suchen, kann MySQL nur die erste Datenzeile im Index anfordern. Dies kann sogar während der Abfrageoptimierungsphase erfolgen und für den Rest der Abfrage als konstanter Wert behandelt werden. Das gleiche gilt für die Abfrage des Maximalwertes, hier müssen nur die letzten u Zeilen gelesen werden. Wenn der Server diese Optimierung verwendet, können Sie in EXPLAIN die Meldung „Select tables optimized away optimized“ sehen. Dies bedeutet, dass der Optimierer die Tabelle aus dem Abfrageplan entfernt und durch eine Konstante ersetzt hat. In ähnlicher Weise können COUNT(*)-Abfragen auch in einigen Speicher-Engines optimiert werden (wie etwa MyISAM, das immer die genaue Anzahl von Zeilen in der Tabelle speichert), wenn keine WHERE-Bedingung angegeben ist.
• Auswerten und Vereinfachen konstanter Ausdrücke: Sobald MySQL erkennt, dass ein Ausdruck zu einer Konstanten vereinfacht werden kann, tut es dies während der Optimierungsphase. Beispielsweise kann eine benutzerdefinierte Variable in eine Konstante umgewandelt werden, wenn sie sich während der Abfrage nicht ändert. Überraschenderweise werden während der Optimierungsphase auch einige Anweisungen, die Sie für eine Abfrage halten, in Konstanten umgewandelt. Ein Beispiel ist MIN() auf einem Index. Diese Situation kann auch auf konstante Abfragen von Primärschlüsseln oder unabhängigen Indizes erweitert werden. Wenn die WHERE-Bedingung eine Konstante für einen solchen Index angibt, weiß der Optimierer, dass MySQL am Anfang der Abfrage nach dem entsprechenden Wert suchen soll. Dieser Wert wird dann im Rest der Abfrage als Konstante behandelt. Hier ist ein Beispiel:

EXPLAIN SELECT film.film_id, film_actor.actor_id
VON sakila.film
	INNER JOIN sakila.film_actor USING(film_id)
WO film.film_id = 1;

MySQL teilt diese Abfrage in zwei Schritte auf, sodass das Analyseergebnis zwei Zeilen umfasst. Der erste Schritt besteht darin, die entsprechende Datenzeile in der Filmtabelle zu finden. Da die Abfrage auf dem Primärschlüssel film_id basiert, weiß MySQL, dass nur eine Datenzeile vorhanden ist. Daher ist der Verweis auf das Abfrageanalyseergebnis zu diesem Zeitpunkt eine Konstante. Im zweiten Schritt behandelt MySQL die film_id als bekannten Wert, sodass der Verweis für die Abfrage von film_actor ebenfalls eine Konstante ist. Andere ähnliche Szenarios umfassen, wenn die Einschränkung in der WHERE-, USING- oder ON-Bedingung eine Gleichheit ist. In diesem Beispiel weiß MySQL, dass die film_id in der USING-Bedingung in allen Abfragen derselbe Wert ist und dieser Wert mit der film_id in der WHERE-Bedingung übereinstimmen muss.

• Abdeckungsindex: MySQL verwendet manchmal Indexdaten, um das Lesen von Zeilendaten zu vermeiden, wenn der Index alle von der Abfrage benötigten Spalten enthält.
• Unterabfrageoptimierung: MySQL kann einige Arten von Unterabfragen in effizientere Varianten umwandeln und sie zu Indexabfragen statt zu unabhängigen Abfragen vereinfachen.
• Frühzeitiger Abbruch: MySQL kann den Abfragevorgang vorzeitig abbrechen, nachdem das Abfrageergebnis zufriedenstellend ist. Das offensichtlichste Beispiel ist die LIMIT-Bedingung. Es gibt auch einige andere Situationen, in denen eine vorzeitige Kündigung erforderlich ist. MySQL kann beispielsweise die gesamte Abfrage abbrechen, nachdem es einen möglichen Zustand erkannt hat, wie im folgenden Beispiel gezeigt:

ERKLÄREN SIE: SELECT film.film_id FROM sakila.film WHERE film_id=1;

Im Feld „Extra“ der Analyseergebnisse wird „Unmögliches WHERE nach dem Lesen von Konstantentabellen festgestellt“ angezeigt. Es gibt noch weitere Situationen, in denen eine vorzeitige Kündigung möglich ist, zum Beispiel:

SELECT film.film_id
VON sakila.film
	LINKER ÄUSSERER JOIN sakila.film_actor USING (film_id)
WO sakila.film_actor.film_id NULL IST;

Diese Abfrage schließt Filme aus, in denen Schauspieler mitspielen. Jeder Film kann mehrere Schauspieler haben, aber sobald ein Schauspieler gefunden wurde, beendet MySQL die Verarbeitung des aktuellen Films und fährt mit dem nächsten fort. Eine ähnliche Situation tritt für DISTINCT und NOT EXISTS auf.

• Äquivalenzausbreitung: MySQL erkennt, ob die in einer Abfrage enthaltenen Spalten äquivalent sind. Beispielsweise wirkt sich in einer JOIN-Bedingung die WHERE-Bedingung auf dieselben Spalten aus wie in der folgenden Abfrage:

SELECT film.film_id
VON sakila.film
	INNER JOIN sakila.film_actor USING(film_id)
WO film.film_id > 500;

MySQL erkennt, dass die WHERE-Einschränkung nicht nur für die Filmtabelle, sondern auch für die film_actor-Tabelle gilt. Dieser Optimierungseffekt kann jedoch bei anderen Datenbanken möglicherweise nicht erreicht werden.

• Vergleich von IN-Abfragen: Bei vielen Datenbankservern entspricht eine IN-Abfrage mehreren ODER-Bedingungen und die beiden sind logisch äquivalent. Dies ist bei MySQL jedoch nicht der Fall. MySQL sortiert die Listenwerte der IN-Abfrage und überprüft mithilfe der binären Suche, ob der Abfragewert in der Liste enthalten ist. Dies reduziert die Komplexität des Algorithmus von O(n) auf O(log n).

Tatsächlich verwendet MySQL noch viel mehr Optimierungsmethoden als die oben aufgeführten und es ist unmöglich, sie hier alle aufzulisten. Denken Sie einfach an die Komplexität des MySQL-Optimierers und wie intelligent er ist. Daher sollte man dem Optimierer erlauben, seine Rolle zu spielen, anstatt die Abfrageanweisung endlos zu optimieren, bis der MySQL-Optimierer keinen Raum mehr für Verbesserungen hat. Obwohl der MySQL-Optimierer sehr intelligent ist, liefert er natürlich nicht unbedingt die besten Ergebnisse. Manchmal kennen Sie das beste Ergebnis, aber MySQL weiß es möglicherweise nicht. In diesem Fall können Sie die Abfrageanweisung optimieren, um MySQL bei der Fertigstellung der Optimierungsarbeit zu unterstützen. Manchmal müssen Sie jedoch Abfragehinweise hinzufügen oder die Abfrage neu schreiben, das Design der Datentabelle ändern oder Indizes hinzufügen.

Oben finden Sie Einzelheiten zum Verständnis des MySQL-Abfrageoptimierungsprozesses. Weitere Informationen zur MySQL-Abfrageoptimierung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!