Implementierung und Optimierung von MySql-Unterabfragen IN

Warum ist IN langsam?

Nach der Verwendung von Unterabfragen in der Anwendung wird die Abfrageleistung der SQL-Anweisung sehr schlecht. Zum Beispiel:

Wählen Sie Treiber-ID aus dem Treiber aus, wobei Treiber-ID in (Wählen Sie Treiber-ID aus dem Treiber aus, wobei _Erstellungsdatum > '2016-07-25 00:00:00');

Die unabhängige Unterabfrage gibt die Treiber-ID zurück, die die Bedingungen erfüllt. Dieses Problem ist gelöst, dauert aber 6 Sekunden. Sie können den Ausführungsplan der SQL-Anweisung über EXPLAIN anzeigen:

Sie können sehen, dass die obige SQL-Anweisung zu einer korrelierten Unterabfrage wird. Durch die Befehle EXPLAIN EXTENDED und SHOW WARNINGS können Sie die folgenden Ergebnisse sehen:

Es ist ersichtlich, dass der MySql-Optimierer die IN-Klausel direkt in eine korrelierte Unterabfrage von EXISTS umwandelt. Hier ist eine korrelierte IN-Unterabfrage:

Wählen Sie Treiber-ID aus dem Treiber aus, wobei Treiber-ID in (Wählen Sie Treiber-ID aus dem Benutzer aus, wobei Benutzer.uid = Treiber.Treiber-ID);

Zeigen Sie den Ausführungsplan der SQL-Anweisung an:

Dies ist eine korrelierte Unterabfrage. Durch die Befehle EXPLAIN EXTENDED und SHOW WARNINGS können wir die folgenden Ergebnisse sehen:

Es ist ersichtlich, dass der Optimierer vor MySQL 5.5 IN- in EXISTS-Anweisungen umwandelt, unabhängig davon, ob es sich um eine unabhängige oder eine korrelierte Unterabfrage handelt. Wenn die Unterabfrage und die äußere Abfrage M bzw. N Zeilen zurückgeben, wird die Unterabfrage in O(N+N*M) statt in O(N+M) gescannt. Aus diesem Grund ist IN langsam.

Was ist schneller, IN oder EXISTS?

Baidu hat im Internet zahlreiche Artikel gefunden, in denen es heißt, die Behauptung, IN und EXISTS seien gleich effizient, sei falsch.

Wenn die beiden abgefragten Tabellen eine ähnliche Größe haben, macht die Verwendung von „in“ und „exists“ kaum einen Unterschied.
Wenn eine der beiden Tabellen kleiner und die andere größer ist, besteht use für die größere Unterabfragetabelle und in für die kleinere Unterabfragetabelle:
Beispiel: Tisch A (kleiner Tisch), Tisch B (großer Tisch)
1:
select * from A where cc in (select cc from B) ist ineffizient, da es den Index der cc-Spalte in Tabelle A verwendet.
select * from A where exists(select cc from B where cc=A.cc) ist effizient, da es den Index der cc-Spalte in Tabelle B verwendet.

Gegenteil

2:
select * from B where cc in (select cc from A) ist effizient, da es den Index der cc-Spalte in Tabelle B verwendet.
select * from B where exists(select cc from A where cc=B.cc) ist ineffizient, da es den Index der cc-Spalte in Tabelle A verwendet.

Um die obige Beschreibung zusammenzufassen: Ich persönlich denke, dass der Hauptgrund in der Verwendung von Indizes liegt. In jedem Fall kann die Effizienz verbessert werden, solange der Index einer großen Tabelle verwendet wird.

Beim Bearbeiten dieses Artikels habe ich es jedoch viele Male getestet, konnte jedoch nicht die oben zusammengefassten Ergebnisse erzielen. Nachfolgend sehen Sie die SQL-Testanweisung. Erstens ist die äußere Tabelle eine große Tabelle und die innere Tabelle eine kleine Tabelle. (Beispiel 1)

Wählen Sie Anzahl (Treiber-ID) aus dem Treiber aus, wobei die Treiber-ID in (Wählen Sie uid aus dem Benutzer aus);
SELECT count(driver_id) FROM driver, sofern vorhanden (SELECT 1 FROM user, wobei uid = driver.driver_id);

Das Ausführungsergebnis ist:

Dann ist die Außenfläche ein kleiner Tisch und die Innenfläche ein großer Tisch. (Beispiel 2)

Wählen Sie Anzahl (UID) vom Benutzer, wobei die UID in (SELECT driver_id FROM driver) ist;
Wählen Sie count(uid) vom Benutzer, sofern vorhanden (SELECT 1 FROM driver, wobei driver.driver_id = user.uid);

Das Ausführungsergebnis ist:

Es zeigt sich, dass die Ausführungseffizienz von IN und EXISTS in jedem Fall genau gleich ist. Darauf aufbauend betrachten wir weiterhin die Ausführungspläne der ersten und zweiten SQL-Anweisung in Beispiel 1, wie folgt:

Es ist ersichtlich, dass die Ausführungspläne von IN und EXISTS identisch sind, und die daraus gezogene Schlussfolgerung ist, dass die Ausführungseffizienz der beiden gleich sein sollte.

„MySql Technology Insider: SQL Programming“: Das Buch beschreibt, dass viele DBAs EXISTS für effizienter halten als IN. Es kann sein, dass der Optimierer zu diesem Zeitpunkt nicht sehr stabil oder gut genug war. In den meisten Fällen haben IN und EXISTS jedoch denselben Ausführungsplan.

Wie kann die Effizienz verbessert werden?

Die Ausführung der SQL-Anweisung im obigen Beispiel 2 dauert etwa 8 Sekunden. Die langsame Abfrage wird durch das Vorhandensein von M*N verursacht, kann aber dennoch optimiert werden. Beachten Sie, dass der Grund für die Langsamkeit darin liegt, dass jedes Mal, wenn die interne Abfrage mit der externen Abfrage verglichen wird, die Tabelle einmal durchlaufen werden muss. Eine andere Methode kann verwendet werden, um eine Schicht von Unterabfragen zu verschachteln, um mehrere Durchlaufvorgänge zu vermeiden. Die Anweisung lautet wie folgt:

SELECT count(driver_id) FROM driver, sofern vorhanden (SELECT uid FROM (SELECT uid from user) als b, wobei b.uid = driver.driver_id);

Der Ausführungseffekt ist wie unten dargestellt:

Es ist festzustellen, dass die Optimierung die Ausführungszeit um mehr als 6 Sekunden verkürzt. Nachfolgend sehen Sie den SQL-Ausführungsplan:

Es handelt sich immer noch um eine korrelierte Unterabfrage, die internen Durchlaufabfragevorgänge sind jedoch reduziert. Daher kann die Vorabfrage verwendet werden, um Durchlaufvorgänge zu reduzieren und die Effizienz zu verbessern.

Tatsächlich verwenden viele Entwickler bei der tatsächlichen Programmierung keine Join-Tabellenabfragen, sondern rufen zuerst die Daten aus einer Tabelle ab und führen dann eine WHEREIN-Operation in einer anderen Tabelle aus. Das Prinzip ist dasselbe wie das in der obigen SQL-Anweisung implementierte.

MySQL 5.6 optimiert Unterabfragen?

SEMI JOIN-Strategie

Der Optimierer erkennt, dass die IN-Anweisung eine Unterabfrage erfordert, um eine Instanz jedes Regionsschlüssels aus der Regionstabelle zurückzugeben. Dies bewirkt, dass MySQL die SELECT-Anweisung in einer halbverbundenen Weise ausführt, so dass es in der globalen Tabelle nur eine Instanz jeder Region gibt, die mit dem Datensatz übereinstimmt.

Es gibt zwei sehr wichtige Unterschiede zwischen Semi-Joins und regulären Joins:

• Bei einem Semi-Join führt die innere Tabelle nicht zu doppelten Ergebnissen.
• Durch diesen Vorgang werden die Felder in der internen Tabelle nicht zum Ergebnis hinzugefügt.

Daher ist das Ergebnis eines Semi-Joins häufig eine Teilmenge der Datensätze aus der äußeren Tabelle. Aus Sicht der Effektivität besteht die Optimierung des Semi-Joins darin, doppelte Elemente effektiv aus der inneren Tabelle zu entfernen. MySQL wendet vier verschiedene Semi-Join-Ausführungsstrategien an, um doppelte Elemente zu entfernen.

Tischauszugsoptimierung

Wandeln Sie die Unterabfrage in einen Join um oder verwenden Sie die Tabellen-Pullout-Funktion und führen Sie die Abfrage als inneren Join zwischen den Unterabfragetabellen und den äußeren Tabellen aus. Die Tabellen-Pullout-Funktion zieht eine Tabelle aus der Unterabfrage in die äußere Abfrage. Wandeln Sie die Unterabfrage in einen Join um oder verwenden Sie die Tabellen-Pullout-Funktion und führen Sie die Abfrage als inneren Join zwischen den Unterabfragetabellen und den äußeren Tabellen aus. Mit dem Tabellen-Pullout wird eine Tabelle aus einer Unterabfrage für die äußere Abfrage extrahiert.

Manchmal kann eine Unterabfrage als JOIN umgeschrieben werden, zum Beispiel:

SELECT OrderID FROM Orders where EmployeeID IN (wählen Sie EmployeeID aus Employees, wobei EmployeeID > 3 ist);

Wenn bekannt ist, dass die OrderID eindeutig ist, also ein Primärschlüssel oder ein eindeutiger Index ist, wird die SQL-Anweisung als Join umgeschrieben.

Wählen Sie OrderID FROM Bestellungen verbinden Mitarbeiter, wobei Bestellungen.EmployeeID = Mitarbeiter.EmployeeID und Mitarbeiter.EmployeeID > 3;

Der Zweck des Tabellen-Pullouts besteht darin, die Unterabfrage basierend auf dem eindeutigen Index in eine JOIN-Anweisung umzuschreiben. In MySQL 5.5 lautet der Ausführungsplan der obigen SQL-Anweisung:

Wenn Sie die Befehle EXPLAIN EXTENDED und SHOW WARNINGS verwenden, können Sie die folgenden Ergebnisse sehen:

Genau deshalb ist es, wie oben erwähnt, langsam?

In MySQL 5.6 schreibt der Optimierer die SQL-Anweisung neu und erhält den folgenden Ausführungsplan:

In MySQL 5.6 schreibt der Optimierer unabhängige Unterabfragen nicht in korrelierte Unterabfragen um. Der Ausführungsmodus des Optimierers wird über die Befehle EXPLAIN EXTENDED und SHOW WARNINGS ermittelt:

Offensichtlich schreibt der Optimierer die obige Unterabfrage in eine JOIN-Anweisung um. Dabei handelt es sich um die Table-Pullout-Optimierung.

Optimierung der Duplikatsaussonderung

Führen Sie den Semi-Join wie einen Join aus und entfernen Sie doppelte Datensätze mithilfe einer temporären Tabelle.

Die in der obigen inneren Tabelle gefundenen Spalten sind eindeutig, daher schreibt der Optimierer die Unterabfrage in eine JOIN-Anweisung um, um die Effizienz der SQL-Ausführung zu verbessern. Bei der Optimierung zur Aussonderung von Duplikaten werden die Spalten in der externen Abfragebedingung eindeutig sein und der MySql-Optimierer wird zunächst die von der Unterabfrage gefundenen Ergebnisse deduplizieren. Beispielsweise die folgende SQL-Anweisung:

SELECT ContactName FROM Customers, wobei CustomerID in (wählen Sie CustomerID aus Orders, wobei OrderID > 10000 und Customers.Country = Orders.ShipCountry);

Da CustomerID der Primärschlüssel ist, sollten die aus der Unterabfrage erhaltenen Ergebnisse dedupliziert werden. Ausführungsplan in MySql 5.6:

Die Option „Temporär starten“ in der Option „Extra“ bedeutet, eine temporäre Tabelle für die Deduplizierung zu erstellen, und „Temporär beenden“ bedeutet, die temporäre Tabelle zu löschen. Durch die Befehle EXPLAIN EXTENDED und SHOW WARNINGS wird der Ausführungsmodus des Optimierers wie folgt ermittelt:

Anders als bei der Table Pullout-Optimierung wird Semi Join statt Join angezeigt. Der Grund dafür ist, dass mehr Deduplizierungsarbeit anfällt. Für den obigen Ausführungsplan betragen die Scankosten etwa 830+830*1=1660 Mal.
Der Ausführungsplan in MySql 5.5 lautet:

Wie Sie sehen, wird die Anweisung in MySql 5.5 immer noch in eine korrelierte Unterabfrage umgewandelt und die Scankosten betragen etwa das 93+93*9=930-fache.

Wir können sehen, dass die Scankosten von MySql 5.6 nach der Optimierung höher sind als die von 5.5. Tatsächlich ist dies nur das Ergebnis, wenn die beiden Tabellen klein sind. Wenn die Tabelle sehr groß ist, wird der Effekt der Optimierung sehr deutlich.

Materialisierungsoptimierung

Materialisieren Sie die Unterabfrage in einer temporären Tabelle mit einem Index und verwenden Sie die temporäre Tabelle, um einen Join durchzuführen. Der Index wird verwendet, um Duplikate zu entfernen. Der Index kann später auch für Nachschlagevorgänge verwendet werden, wenn die temporäre Tabelle mit den äußeren Tabellen verknüpft wird. Andernfalls wird die Tabelle gescannt.

Die obige Unterabfrage ist eine korrelierte Unterabfrage. Wenn die Unterabfrage eine unabhängige Unterabfrage ist, kann der Optimierer die Ergebnisse der unabhängigen Unterabfrage in eine einzelne materialisierte temporäre Tabelle eintragen, wie in der Abbildung dargestellt:

Entsprechend der Reihenfolge des Beitritts kann die Materialisierungsoptimierung wie folgt unterteilt werden:

• Materialisierungsscan: JOIN dient zum Verknüpfen der materialisierten temporären Tabelle mit der Tabelle.
• Materialisierungssuche: JOIN dient zum Verknüpfen der Tabelle mit der materialisierten temporären Tabelle.

Die folgende Unterabfrage kann mithilfe der Materialisierung optimiert werden:

SELECT OrderID FROM Bestellungen, bei denen OrderID enthalten ist (wählen Sie OrderID aus „Bestelldetails“, wo UnitPrice < 50);

Ausführungsplan der SQL-Anweisung:

Es ist ersichtlich, dass beim Ausführen von JOIN (d. h. dem Schritt mit der ID 1) zuerst die Tabelle „Orders“ und dann Unterabfrage2 gescannt wird. Dies ist also eine Optimierung der Materialisierungssuche. Für das folgende SQL:

Wählen Sie * FROM Treiber, wobei Treiber-ID in (wählen Sie UID vom Benutzer aus);

Ausführungsplan der SQL-Anweisung:

Zuerst wird Subquery2 gescannt und dann die Treibertabelle. Dies ist die Optimierung des Materialisierungsscans.

FirstMacth-Optimierung

Wenn Sie die inneren Tabellen nach Zeilenkombinationen durchsuchen und mehrere Instanzen einer bestimmten Wertegruppe vorhanden sind, wählen Sie eine aus, anstatt sie alle zurückzugeben. Dadurch wird das Durchsuchen verkürzt und die Erstellung unnötiger Zeilen vermieden. Dies stellt einen Mechanismus zum frühzeitigen Beenden von Tabellenscans bereit und verhindert die Generierung unnötiger Datensätze.

Die Art und Weise, wie die FirstMatch-Strategie des Semi-Joins Unterabfragen ausführt, ist IN-TO-EXISTS in früheren MySQL-Versionen sehr ähnlich. Für jeden übereinstimmenden Datensatz in der äußeren Tabelle sucht MySQL nach einer Übereinstimmung in der inneren Tabelle. Wenn eine Übereinstimmung gefunden wird, wird der Datensatz aus der Fremdtabelle zurückgegeben. Nur wenn keine Übereinstimmung gefunden wird, greift die Engine auf die gesamte interne Tabelle zurück und durchsucht diese.

LooseScan-Optimierung

Durchsuchen Sie eine Unterabfragetabelle mithilfe eines Index, der die Auswahl eines einzelnen Werts aus der Wertegruppe jeder Unterabfrage ermöglicht.

SEMI JOIN-Variablen

Jede dieser Strategien außer Duplicate Weedout kann mit der Systemvariable optimizer_switch aktiviert oder deaktiviert werden. Das Flag semijoin steuert, ob Semi-Joins verwendet werden. Wenn es auf on gesetzt ist, ermöglichen die Flags firstmatch, loosescan und materialization eine feinere Kontrolle über die zulässigen Semi-Join-Strategien. Diese Flags sind standardmäßig aktiviert. Jede Strategie außer Duplicate Weedout kann mit der Systemvariable optimizer_switch aktiviert oder deaktiviert werden. Das Flag semijoin steuert, ob die Semi-Join-Optimierung aktiviert ist. Wenn es auf on gesetzt ist, verfügen auch andere Strategien über eine unabhängige Variablensteuerung. In 5.6 sind alle Variablen standardmäßig aktiviert.

mysql> AUSWÄHLEN @@optimizer_switch\G;
*************************** 1. Reihe ***************************
@@optimizer_switch: index_merge=ein,index_merge_union=ein,index_merge_sort_union=ein,index_merge_intersection=ein,engine_condition_pushdown=ein,index_condition_pushdown=ein,mrr=ein,mrr_cost_based=ein,block_nested_loop=ein,batched_key_access=aus,materialization=ein,semijoin=ein,loosescan=ein,firstmatch=ein,duplicateweedout=ein,subquery_materialization_cost_based=ein,use_index_extensions=ein,condition_fanout_filter=ein,derived_merge=ein
1 Zeile im Satz (0,00 Sek.)

EXPLAIN-Ansichtsstrategie

• Halbverbundene Tabellen werden in der äußeren Auswahl angezeigt. EXPLAIN EXTENDED plus SHOW WARNINGS zeigt die neu geschriebene Abfrage, die die Halbverbundstruktur anzeigt. Daraus können Sie sich ein Bild davon machen, welche Tabellen aus dem Halbverbund herausgezogen wurden. Wenn eine Unterabfrage in einen Halbverbund umgewandelt wurde, sehen Sie, dass das Unterabfrageprädikat verschwunden ist und seine Tabellen und WHERE-Klausel in die Verbindungsliste und WHERE-Klausel der äußeren Abfrage integriert wurden.
• Die Verwendung einer temporären Tabelle zum Aussortieren von Duplikaten wird durch „Start temporary“ und „End temporary“ in der Spalte „Extra“ angezeigt. Tabellen, die nicht herausgezogen wurden und sich im Bereich der EXPLAIN-Ausgabezeilen befinden, die durch „Start temporary“ und „End temporary“ abgedeckt sind, haben ihre Zeilen-ID in der temporären Tabelle.
• FirstMatch(tbl_name) in der Spalte „Extra“ (列) zeigt eine Verknüpfung zum Beitritt an.
• LooseScan(m..n) in der Spalte „Extra“ zeigt die Verwendung der LooseScan-Strategie an. m und n sind wichtige Teilenummern.
• Ab MySQL 5.6.7 wird die temporäre Verwendung von Tabellen für die Materialisierung durch Zeilen mit einem select_type-Wert von MATERIALIZED und Zeilen mit einem Tabellenwert von angezeigt.
• Vor MySQL 5.6.7 wurde die Verwendung temporärer Tabellen zur Materialisierung in der Spalte Extra durch Materialisieren angezeigt, wenn eine einzelne Tabelle verwendet wird, oder durch Start materialize und End materialize, wenn mehrere Tabellen verwendet werden. Wenn Scan vorhanden ist, wird kein temporärer Tabellenindex für Tabellenlesevorgänge verwendet. Andernfalls wird eine Indexsuche verwendet.
  

In der obigen Einführung gibt es keine guten Beispiele, um die spezifischen Effekte der FirstMacth-Optimierung und der LooseScan-Optimierung zu zeigen. Es gibt Möglichkeiten zur Kommunikation und zum Lernen.

siehe

„MySql-Technologie-Insider: SQL-Programmierung“

http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html

http://tech.it168.com/a2013/0506/1479/000001479749.shtml

Dies ist das Ende dieses Artikels über die Ausführung und Optimierung von MySql-Unterabfragen IN. Weitere verwandte Inhalte zu MySql-Unterabfragen IN finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!