Detaillierte Erläuterung des MySQL-Mechanismus zur gemeinsamen Abfrageoptimierung

Strategie zur Ausführung föderierter MySQL-Abfragen.

Nehmen wir als Beispiel eine UNION-Abfrage. Wenn MySQL eine UNION-Abfrage ausführt, behandelt es sie als eine Reihe einzelner Abfrageanweisungen, legt die entsprechenden Ergebnisse in einer temporären Tabelle ab und liest sie schließlich aus und gibt sie zurück. In MySQL ist jede unabhängige Abfrage eine Join-Abfrage und das Gleiche gilt für die Rückgabe von Ergebnissen aus einer temporären Tabelle.

In diesem Fall ist die Ausführung der Join-Abfrage von MySQL einfach – es behandelt die Join-Abfrage hier als Nested Loop Join-Abfrage. Dies bedeutet, dass MySQL eine Schleife ausführt, um Zeilen aus einer Tabelle zu lesen, und dann eine verschachtelte Schleife ausführt, um passende Zeilen aus der nächsten Tabelle zu lesen. Dieser Vorgang wird fortgesetzt, bis alle übereinstimmenden Zeilen in der Union-Abfrage gefunden wurden. Erstellen Sie dann das Rückgabeergebnis entsprechend den in der SELECT-Anweisung erforderlichen Spalten. Wie in der folgenden Abfrageanweisung gezeigt:

Wählen Sie tb1.col1, tb2.col2 aus.
VON tb1 INNER JOIN tb2 MIT (col3)
WO tb1.col1 IN(5,6);

Der tatsächliche Pseudocode, den MySQL ausführen könnte, ist wie folgt:

outer_iter = Iterator über tb1, wobei col1 IN(5,6);
äußere_Zeile = äußerer_Iter.nächster;
während äußere_Zeile
	inner_iter = Iterator über tb2, wobei col3 = outer_row.col3;
	inner_row = inner_iter.next
    während innere_Zeile
    	Ausgabe [äußere_Zeile.Spalte1, innere_Zeile.Spalte2];
        inner_row = inner_iter.next;
	Ende
    äußere_Zeile = äußerer.iter.nächster;
Ende

Nach der Konvertierung in Pseudocode sieht es so aus

outer_iter = Iterator über tb1, wobei col1 IN(5,6);
äußere_Zeile = äußerer_Iter.nächster;
während äußere_Zeile
	inner_iter = Iterator über tb2, wobei col3 = outer_row.col3;
	inner_row = inner_iter.next
    wenn innere_Zeile
        während innere_Zeile
            Ausgabe [äußere_Zeile.Spalte1, innere_Zeile.Spalte2];
            inner_row = inner_iter.next;
        Ende
    anders
    	Ausgabe [äußere_Zeile.spalte1, NULL];
	Ende
    äußere_Zeile = äußerer.iter.nächster;
Ende

Eine andere Möglichkeit zum Visualisieren des Abfrageplans ist die Verwendung eines Swimlane-Diagramms. Die folgende Abbildung zeigt ein Swimlane-Diagramm für eine Inner-Join-Abfrage.

MySQL führt alle Arten von Abfragen grundsätzlich auf die gleiche Weise aus. Wenn beispielsweise eine Unterabfrage zuerst in der FROM-Bedingung ausgeführt werden muss, wird das Ergebnis zuerst in eine temporäre Tabelle eingefügt. Anschließend wird die temporäre Tabelle wie eine normale Tabelle behandelt und zur Verarbeitung verbunden. MySQL verwendet bei der Ausführung von Union-Abfragen auch temporäre Tabellen und schreibt dann die Right-Join-Abfrage in einen entsprechenden Left-Join um. Kurz gesagt, die aktuelle Version von MySQL konvertiert verschiedene Abfragen so weit wie möglich in diese Verarbeitungsmethode (die neueste Version von MySQL 5.6 und später führte komplexere Verarbeitungsmethoden ein).

Natürlich ist dies nicht bei allen gültigen SQL-Abfrageanweisungen möglich, und die Leistung einiger Abfragen kann auf diese Weise beeinträchtigt werden.

Ausführungsplan

Im Gegensatz zu vielen anderen Datenbankprodukten generiert MySQL keine Bytecodes für Abfrageanweisungen zum Ausführen von Abfrageplänen. Tatsächlich ist der Abfrageausführungsplan ein Anweisungsbaum, und die Abfrageausführungs-Engine generiert Abfrageergebnisse basierend auf diesem Baum. Der endgültige Abfrageplan enthält genügend Informationen, um die ursprüngliche Abfrage zu rekonstruieren. Wenn Sie EXPLAIN EXTENDED auf der Abfrageanweisung ausführen (MySQL 8 und höher müssen EXTENDED nicht hinzufügen) und dann SHOW WARNINGS ausführen, können Sie die rekonstruierte Abfrage sehen.

Konzeptionell können Abfragen mehrerer Tabellen durch einen Baum dargestellt werden. Eine Abfrage mit vier Tabellen könnte beispielsweise wie der folgende Baum aussehen. Dies wird in Computern als ausgeglichener Baum bezeichnet.

MySQL führt Abfragen jedoch nicht auf diese Weise aus. Wie bereits erwähnt, beginnt MySQL immer mit einer Tabelle und sucht dann in der nächsten Tabelle nach übereinstimmenden Zeilen. Daher sieht der Abfrageplan von MySQL wie der folgende linkstiefe Join-Baum aus.

Föderierter Abfrageoptimierer

Der wichtigste Teil des MySQL-Abfrageoptimierers ist der gemeinsame Abfrageoptimierer, der die optimale Reihenfolge der Ausführung von Abfragen mehrerer Tabellen bestimmt. Durch die Verwendung mehrerer Sequenzen von Join-Abfragen können Sie häufig dieselben Ergebnisse erzielen. Der föderierte Abfrageoptimierer versucht, die Kosten dieser Pläne zu schätzen und wählt dann den Plan mit den niedrigsten Kosten zur Ausführung aus.

Das Folgende ist ein Beispiel für eine Union-Abfrage, die dieselben Ergebnisse, jedoch in einer anderen Reihenfolge zurückgibt.

SELECT film.film_id, film.title, film.release_year, actor.actor_id, actor.first_name, actor.last_name
VON sakila.film
INNER JOIN sakila.film_actor USING(film_id)
INNER JOIN sakila.actor USING(Schauspieler-ID);

Hier kann es unterschiedliche Abfragemöglichkeiten geben. Beispielsweise kann MySQL mit der Filmtabelle beginnen, den Index film_id von film_actor verwenden, um den entsprechenden Wert actor_di zu finden, und dann den Primärschlüssel aus der Schauspielertabelle verwenden, um die entsprechende Schauspielerdatenzeile zu finden. Ein Oracle-Benutzer könnte angeben: „Die Filmtabelle ist die treibende Tabelle für film_actor, und film_actor ist die treibende Tabelle für die Schauspielertabelle.“ Die Ergebnisse der Verwendung der Explain-Analyse sind wie folgt:

******** 1.Reihe ********
ID: 1
select_type: EINFACH
Tabelle: Schauspieler
Typ: ALLE
mögliche Schlüssel: PRIMARY
Schlüssel: NULL
key_len: NULL
Ref: NULL
Reihen: 200
Extra:
******** 2. Reihe ********
ID: 1
select_type: EINFACH
Tabelle: Filmschauspieler
Typ: ref
mögliche Schlüssel: PRIMARY, idx_fk_film_id
Schlüssel: PRIMARY
Schlüssellänge: 2
Referenz: sakila.film.film_id
Reihen: 1
Extra: USING-Index
******** 3.Reihe ********
ID: 1
select_type: EINFACH
Tabelle: Film
Typ: eq_ref
mögliche Schlüssel: PRIMARY
Schlüssel: PRIMARY
Schlüssellänge: 2
Referenz: sakila.film_actor.film_id
Reihen: 1
Extra: 

Dieser Ausführungsplan unterscheidet sich stark von dem, was wir erwartet haben. MySQL beginnt zuerst mit der Akteurtabelle und geht dann in umgekehrter Reihenfolge vor. Ist das tatsächlich effizienter? Wir können STRAIGHT_JOIN zu EXPLAIN hinzufügen, um eine Optimierung zu vermeiden:

EXPLAIN SELECT STRAIGHT_JOIN film.film_id, film.title, film.release_year, actor.actor_id, actor.first_name, actor.last_name
VON sakila.film
INNER JOIN sakila.film_actor USING(film_id)
INNER JOIN sakila.actor USING(Schauspieler-ID);

******** 1.Reihe ********
ID: 1
select_type: EINFACH
Tabelle: Film
Typ: ALLE
mögliche Schlüssel: PRIMARY
Schlüssel: NULL
key_len: NULL
Ref: NULL
Reihen: 951
Extra:
******** 2.Reihe ********
ID: 1
select_type: EINFACH
Tabelle: Filmschauspieler
Typ: ref
mögliche Schlüssel: PRIMARY, idx_fk_film_id
Schlüssel: idx_fk_film_id
Schlüssellänge: 2
Referenz: sakila.film.film_id
Reihen: 1
Zusätzlich: USING-Index
******** 3.Reihe ********
ID: 1
select_type: EINFACH
Tabelle: Schauspieler
Typ: eq_ref
mögliche Schlüssel: PRIMARY
Schlüssel: PRIMARY
Schlüssellänge: 2
Referenz: sakila.film_actor.actor_id
Reihen: 1
Extra: 

Dies erklärt, warum MySQL die Abfrage in umgekehrter Reihenfolge ausführen muss, was dazu führt, dass weniger Zeilen untersucht werden.

• Wenn Sie zuerst die Filmtabelle abfragen, sind 951 Abfragen für film_actor und actor erforderlich (äußerste Schleife).
• Wenn Sie die Actor-Tabelle nach vorne bringen, müssen Sie die anderen Tabellen nur 200 Mal abfragen.
  

Anhand dieses Beispiels können wir erkennen, dass der gemeinsame Abfrageoptimierer von MySQL die Abfragekosten durch Anpassen der Reihenfolge der Abfragetabellen senken kann. Neu geordnete Join-Abfragen sind in der Regel sehr effektive Optimierungen, die die Leistung oft um ein Vielfaches verbessern. Wenn sich dadurch die Leistung nicht verbessert, können Sie auch STRAIGHT_JOIN verwenden, um eine Neuanordnung zu vermeiden und die Abfragemethode zu verwenden, die Sie für die beste halten. Diese Situation kommt in der Praxis selten vor und in den meisten Fällen leistet der gemeinsame Abfrageoptimierer bessere Arbeit als Menschen.

Der Abfrageoptimierer betrachtet die Vereinigung, um einen Abfrageausführungsbaum mit den niedrigsten Abschlusskosten zu erstellen. Wenn möglich, beginnt es mit allen Einzeltabellenplänen und überprüft alle möglichen Teilbaumkombinationen. Leider hat eine Join-Abfrage von N Tabellen eine N-Fakultät möglicher Kombinationen. Dies wird als Suchraum aller möglichen Abfragepläne bezeichnet und er wächst sehr schnell. Ein gemeinsamer Index von 10 Tabellen hätte 3.628.800 verschiedene Möglichkeiten! Wenn der Suchraum zu groß wird, dauert die Abfrageoptimierung sehr lange. Zu diesem Zeitpunkt beendet der Server die vollständige Analyse und führt die Optimierung stattdessen auf eine Weise durch, die einem Greedy-Algorithmus ähnelt. Diese Zahl wird durch die Systemvariable optimizer_search_depth gesteuert, die Sie selbst ändern können.