Neue Funktionen in MySQL 8.0: Hash Join

Neue Funktionen in MySQL 8.0: Hash Join

Das MySQL-Entwicklungsteam hat am 14. Oktober 2019 die MySQL 8.0.18 GA-Version offiziell veröffentlicht, die einige neue Funktionen und Verbesserungen mit sich bringt. Das auffälligste Merkmal ist, dass Multi-Table-Join-Abfragen den Hash-Join-Modus unterstützen. Werfen wir zunächst einen Blick auf die offizielle Beschreibung:

MySQL implementiert eine Hash-Join-Methode für Inner-Join-Abfragen. Ab MySQL 8.0.18 kann beispielsweise die folgende Abfrage Hash-Join für Join-Abfragen verwenden:

WÄHLEN * 
  VON t1 
  t2 beitreten 
    EIN t1.c1=t2.c1;

Hash-Join erfordert keine Indexunterstützung. In den meisten Fällen ist der Hash-Join effizienter als der vorherige Block-Nested-Loop-Algorithmus für gleichwertige Joins ohne Indizes. Verwenden Sie die folgenden Anweisungen, um drei Testtabellen zu erstellen:

TABELLE ERSTELLEN t1 (c1 INT, c2 INT);
TABELLE ERSTELLEN t2 (c1 INT, c2 INT);
TABELLE ERSTELLEN t3 (c1 INT, c2 INT);

Verwenden Sie den Befehl EXPLAIN FORMAT=TREE, um den Hash-Join im Ausführungsplan anzuzeigen, zum Beispiel:

mysql> EXPLAIN FORMAT=BAUM
  -> AUSWÄHLEN * 
  -> VON t1 
  -> t2 beitreten 
  -> EIN t1.c1=t2.c1\G
*************************** 1. Reihe ***************************
ERKLÄRUNG: -> Innerer Hash-Join (t2.c1 = t1.c1) (Kosten=0,70 Zeilen=1)
  -> Tabellenscan auf t2 (Kosten=0,35 Zeilen=1)
  -> Hash
    -> Tabellenscan auf t1 (Kosten=0,35 Zeilen=1)

Sie müssen die Option FORMAT=TREE des Befehls EXPLAIN verwenden, um die Hash-Joins im Knoten anzuzeigen. Darüber hinaus kann der Befehl EXPLAIN ANALYZE auch die Verwendungsinformationen des Hash-Joins anzeigen. Dies ist auch eine neue Funktion, die dieser Version hinzugefügt wurde.

Auch Abfragen, die Equi-Joins zwischen mehreren Tabellen verwenden, werden auf diese Weise optimiert. Beispielsweise die folgende Abfrage:

WÄHLEN * 
  VON t1
  t2 beitreten 
    EIN (t1.c1 = t2.c1 UND t1.c2 < t2.c2)
  WERDEN SIE MITGLIED bei t3 
    EIN (t2.c1 = t3.c1);

Im obigen Beispiel werden alle anderen Nicht-Equijoin-Bedingungen nach der Join-Operation als Filter verwendet. Sie können dies in der Ausgabe des Befehls EXPLAIN FORMAT=TREE sehen:

mysql> EXPLAIN FORMAT=BAUM
  -> AUSWÄHLEN * 
  -> VON t1
  -> t2 beitreten 
  -> EIN (t1.c1 = t2.c1 UND t1.c2 < t2.c2)
  -> t3 beitreten 
  -> EIN (t2.c1 = t3.c1)\G
*************************** 1. Reihe ***************************
ERKLÄRUNG: -> Innerer Hash-Join (t3.c1 = t1.c1) (Kosten=1,05 Zeilen=1)
  -> Tabellenscan auf t3 (Kosten=0,35 Zeilen=1)
  -> Hash
    -> Filter: (t1.c2 < t2.c2) (Kosten=0,70 Zeilen=1)
      -> Innerer Hash-Join (t2.c1 = t1.c1) (Kosten=0,70 Zeilen=1)
        -> Tabellenscan auf t2 (Kosten=0,35 Zeilen=1)
        -> Hash
          -> Tabellenscan auf t1 (Kosten=0,35 Zeilen=1)

Aus der obigen Ausgabe ist außerdem ersichtlich, dass Abfragen, die mehrere gleiche Join-Bedingungen enthalten, auch mehrere Hash-Join-Verbindungen verwenden können (werden).

Wenn jedoch eine beliebige Verbindungsanweisung (ON) nicht die entsprechende Verbindungsbedingung verwendet, wird die Hash-Join-Verbindungsmethode nicht verwendet. Zum Beispiel:

mysql> EXPLAIN FORMAT=BAUM
  -> AUSWÄHLEN * 
  -> VON t1
  -> t2 beitreten 
  -> EIN (t1.c1 = t2.c1)
  -> t3 beitreten 
  -> EIN (t2.c1 < t3.c1)\G
*************************** 1. Reihe ***************************
EXPLAIN: <nicht ausführbar durch Iterator-Executor>

Zu diesem Zeitpunkt wird der langsamere block nested loop Verbindungsalgorithmus verwendet. Dies ist dasselbe wie vor MySQL 8.0.18 ohne Indizes:

mysql> ERKLÄREN
  -> AUSWÄHLEN * 
  -> VON t1
  -> t2 beitreten 
  -> EIN (t1.c1 = t2.c1)
  -> t3 beitreten 
  -> EIN (t2.c1 < t3.c1)\G       
*************************** 1. Reihe ***************************
      ID: 1
 select_type: EINFACH
    Tabelle: t1
  Partitionen: NULL
     Typ: ALLE
mögliche Schlüssel: NULL
     Schlüssel: NULL
   key_len: NULL
     Ref: NULL
     Reihen: 1
   gefiltert: 100,00
    Extra: NULL
*************************** 2. Reihe ***************************
      ID: 1
 select_type: EINFACH
    Tabelle: t2
  Partitionen: NULL
     Typ: ALLE
mögliche Schlüssel: NULL
     Schlüssel: NULL
   key_len: NULL
     Ref: NULL
     Reihen: 1
   gefiltert: 100,00
    Extra: Verwenden von „where“; Verwenden des Join-Puffers (Block Nested Loop)
*************************** 3. Reihe ***************************
      ID: 1
 select_type: EINFACH
    Tabelle: t3
  Partitionen: NULL
     Typ: ALLE
mögliche Schlüssel: NULL
     Schlüssel: NULL
   key_len: NULL
     Ref: NULL
     Reihen: 1
   gefiltert: 100,00
    Extra: Verwenden von „where“; Verwenden des Join-Puffers (Block Nested Loop)

Der Hash-Join gilt auch für das kartesische Produkt, wenn keine Abfragebedingungen angegeben sind, zum Beispiel:

mysql> EXPLAIN FORMAT=BAUM
  -> AUSWÄHLEN *
  -> VON t1
  -> t2 beitreten
  -> WO t1.c2 > 50\G
*************************** 1. Reihe ***************************
ERKLÄRUNG: -> Innerer Hash-Join (Kosten=0,70 Zeilen=1)
  -> Tabellenscan auf t2 (Kosten=0,35 Zeilen=1)
  -> Hash
    -> Filter: (t1.c2 > 50) (Kosten=0,35 Zeilen=1)
      -> Tabellenscan auf t1 (Kosten=0,35 Zeilen=1)

Standardmäßig verwendet MySQL, sofern möglich, Hash-Joins. Gleichzeitig werden zwei Methoden bereitgestellt, um zu steuern, ob ein Hash-Join verwendet werden soll:

Setzen Sie die Serversystemvariable optimizer_switch auf globaler oder Sitzungsebene hash_join=on oder hash_join=off . Der Standardwert ist hash_join=on .

Geben Sie den Optimierungshinweis HASH_JOIN oder NO_HASH_JOIN für einen bestimmten Join auf Anweisungsebene an.

Die für den Hash-Join zulässige Speichermenge kann durch die Systemvariable join_buffer_size gesteuert werden. Der Hash-Join verwendet nicht mehr Speicher als die durch diese Variable festgelegte Menge. Wenn der für einen Hash-Join erforderliche Speicher diesen Schwellenwert überschreitet, führt MySQL den Vorgang auf der Festplatte aus. Beachten Sie, dass der Verbindungsvorgang möglicherweise fehlschlägt, wenn der Hash-Join im Speicher nicht abgeschlossen werden kann und die Anzahl der geöffneten Dateien den Wert der Systemvariablen open_files_limit überschreitet. Um dieses Problem zu beheben, verwenden Sie eine der folgenden Methoden:

Erhöhen Sie den Wert von join_buffer_size , um sicherzustellen, dass hash join im Speicher abgeschlossen werden kann.

Erhöhen Sie den Wert von n_files_limit .

Als nächstes vergleichen sie die Leistung von hash join und block nested loop . Zunächst werden jeweils 1.000.000 Datensätze für t1, t2 und t3 generiert:

setze join_buffer_size=2097152000;
SETZEN @@cte_max_recursion_depth = 99999999;
EINFÜGEN IN t1
-- IN t2 EINFÜGEN
-- IN t3 EINFÜGEN
MIT REKURSIVEM t ALS (
 WÄHLEN Sie 1 als c1, 1 als c2
 UNION ALLE
 WÄHLEN Sie t.c1 + 1, t.c1 * 2
  VON t
  Wobei t.c1 < 1000000
)
WÄHLEN *
 VON t;

Hash-Join ohne Index:

mysql> ERKLÄREN ANALYSIEREN
  -> ANZAHL AUSWÄHLEN(*)
  -> VON t1
  -> t2 beitreten 
  -> EIN (t1.c1 = t2.c1)
  -> t3 beitreten 
  -> EIN (t2.c1 = t3.c1)\G
*************************** 1. Reihe ***************************
ERKLÄRUNG: -> Aggregat: Anzahl(0) (tatsächliche Zeit=22993.098..22993.099 Zeilen=1 Schleifen=1)
  -> Innerer Hash-Join (t3.c1 = t1.c1) (Kosten=9952535443663536,00 Zeilen=9952435908880402) (tatsächliche Zeit=14489,176..21737,032 Zeilen=1000000 Schleifen=1)
    -> Tabellenscan in t3 (Kosten=0,00, Zeilen=998412) (tatsächliche Zeit=0,103..3973,892, Zeilen=1000000, Schleifen=1)
    -> Hash
      -> Innerer Hash-Join (t2.c1 = t1.c1) (Kosten=99682753413,67 Zeilen=99682653660) (tatsächliche Zeit=5663,592..12236,984 Zeilen=1000000 Schleifen=1)
        -> Tabellenscan in t2 (Kosten=0,01 Zeilen=998412) (tatsächliche Zeit=0,067..3364,105 Zeilen=1000000 Schleifen=1)
        -> Hash
          -> Tabellenscan in t1 (Kosten=100539,40 Zeilen=998412) (tatsächliche Zeit=0,133..3395,799 Zeilen=1000000 Schleifen=1)

1 Reihe im Satz (23,22 Sek.)

mysql> ANZAHL AUSWÄHLEN(*)
  -> VON t1
  -> t2 beitreten 
  -> EIN (t1.c1 = t2.c1)
  -> t3 beitreten 
  -> EIN (t2.c1 = t3.c1);
+----------+
| ANZAHL(*) |
+----------+
| 1000000 |
+----------+
1 Zeile im Satz (12,98 Sek.)

Der eigentliche Lauf dauerte 12,98 Sekunden. Wenn Sie zu diesem Zeitpunkt eine blockverschachtelte Schleife verwenden:

mysql> EXPLAIN FORMAT=BAUM
  -> AUSWÄHLEN /*+ NO_HASH_JOIN(t1, t2, t3) */ ANZAHL(*)
  -> VON t1
  -> t2 beitreten 
  -> EIN (t1.c1 = t2.c1)
  -> t3 beitreten 
  -> EIN (t2.c1 = t3.c1)\G
*************************** 1. Reihe ***************************
EXPLAIN: <nicht ausführbar durch Iterator-Executor>

1 Zeile im Satz (0,00 Sek.)

AUSWÄHLEN /*+ NO_HASH_JOIN(t1, t2, t3) */ ANZAHL(*)
 VON t1
 t2 beitreten 
  EIN (t1.c1 = t2.c1)
 WERDEN SIE MITGLIED bei t3 
  EIN (t2.c1 = t3.c1);

EXPLAIN zeigt, dass Hash-Join nicht verwendet werden kann. Die Abfrage lief Dutzende Minuten lang, ohne Ergebnisse zu liefern, und eine der CPUs war zu 100 % ausgelastet, da sie ständig verschachtelte Schleifen ausführte (1.000.000 hoch 3).

Schauen wir uns den Block Nested Loop-Methode mit einem Index an und fügen einen Index hinzu:

mysql> ERSTELLEN Sie den Index idx1 auf t1 (c1);
Abfrage OK, 0 Zeilen betroffen (7,39 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0
mysql> ERSTELLEN Sie den Index idx2 auf t2 (c1);
Abfrage OK, 0 Zeilen betroffen (6,77 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0
mysql> ERSTELLEN Sie den Index idx3 auf t3 (c1);
Abfrage OK, 0 Zeilen betroffen (7,23 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

Zeigen Sie den Ausführungsplan an und führen Sie dieselbe Abfrage aus:

mysql> ERKLÄREN ANALYSIEREN
  -> ANZAHL AUSWÄHLEN(*)
  -> VON t1
  -> t2 beitreten 
  -> EIN (t1.c1 = t2.c1)
  -> t3 beitreten 
  -> EIN (t2.c1 = t3.c1)\G
*************************** 1. Reihe ***************************
ERKLÄRUNG: -> Aggregat: Anzahl(0) (tatsächliche Zeit=47684.034..47684.035 Zeilen=1 Schleifen=1)
  -> Innerer Join mit verschachtelter Schleife (Kosten=2295573,22 Zeilen=998412) (tatsächliche Zeit=0,116..46363,599 Zeilen=1000000 Schleifen=1)
    -> Innerer Join mit verschachtelter Schleife (Kosten=1198056,31 Zeilen=998412) (tatsächliche Zeit=0,087..25788,696 Zeilen=1000000 Schleifen=1)
      -> Filter: (t1.c1 ist nicht null) (Kosten=100539,40 Zeilen=998412) (tatsächliche Zeit=0,050..5557,847 Zeilen=1000000 Schleifen=1)
        -> Index-Scan auf t1 mit idx1 (Kosten=100539,40 Zeilen=998412) (tatsächliche Zeit=0,043..3253,769 Zeilen=1000000 Schleifen=1)
      -> Indexsuche auf t2 mit idx2 (c1=t1.c1) (Kosten=1,00, Zeilen=1) (tatsächliche Zeit=0,012..0,015, Zeilen=1, Schleifen=1000000)
    -> Indexsuche auf t3 mit idx3 (c1=t1.c1) (Kosten=1,00, Zeilen=1) (tatsächliche Zeit=0,012..0,015, Zeilen=1, Schleifen=1000000)

1 Reihe im Satz (47,68 Sek.)

mysql> ANZAHL AUSWÄHLEN(*)
  -> VON t1
  -> t2 beitreten 
  -> EIN (t1.c1 = t2.c1)
  -> t3 beitreten 
  -> EIN (t2.c1 = t3.c1);
+----------+
| ANZAHL(*) |
+----------+
| 1000000 |
+----------+
1 Reihe im Satz (19,56 Sek.)

Der eigentliche Lauf dauerte 19,56 Sekunden. Die Testergebnisse in unserem Szenario lauten also wie folgt:

Hash Join (kein Index) Block Nested Loop (kein Index) Block Nested Loop (mit Index)
12,98 Sek. Nicht zurückgegeben 19,56 Sek.

Fügen Sie in Oracle 12c ein weiteres Hash-Join-Ergebnis ohne Index hinzu: 1,282 s.

Hier ist ein weiteres Hash-Join-Ergebnis ohne Index in PostgreSQL 11.5: 6,234 s.

Fügen Sie in SQL 2017 ein weiteres Hash-Join-Ergebnis ohne Index hinzu: 5,207 s.

Zusammenfassen

Oben ist die neue Funktion von MySQL 8.0, Hash Join, die ich Ihnen vorstellen möchte. Ich hoffe, sie wird Ihnen hilfreich sein. Wenn Sie Fragen haben, hinterlassen Sie mir bitte eine Nachricht und ich werde Ihnen rechtzeitig antworten. Ich möchte auch allen für ihre Unterstützung der Website 123WORDPRESS.COM danken! Wenn Sie diesen Artikel hilfreich finden, können Sie ihn gerne abdrucken und dabei bitte die Quelle angeben. Vielen Dank!

Das könnte Sie auch interessieren:
  • Neue Funktionen in MySQL 8.0 - Einführung in Check Constraints
  • Ausführliche Erläuterung versteckter Felder, einer neuen Funktion von MySQL 8.0
  • Implementierung von Check Constraints in MySQL 8.0
  • Analyse der neuen Funktionen von MySQL 8.0 - Transaktionales Datenwörterbuch und Atomic DDL
  • Eine kurze Diskussion über die Fallstricke und Lösungen der neuen Features von MySQL 8.0 (Zusammenfassung)
  • Neue Funktionen in MySQL 8.0: Unterstützung für atomare DDL-Anweisungen
  • Detaillierte Erläuterung der neuen relationalen Datenbankfunktionen in MySQL 8.0
  • Lösung für IDEA, das keine Verbindung zur MySQL-Portnummernbelegung herstellen kann
  • Verwenden Sie MySQL, um Port 3306 zu öffnen/ändern und Zugriffsberechtigungen in der Ubuntu/Linux-Umgebung zu öffnen
  • Perfekte Lösung für MySQL, das nach der Installation von phpstudy nicht gestartet werden kann (keine Notwendigkeit, die ursprüngliche Datenbank zu löschen, keine Notwendigkeit, eine Konfiguration zu ändern, keine Notwendigkeit, den Port zu ändern) direkte Koexistenz
  • Aktivieren Sie Remote-Zugriffsrechte für MySQL unter Linux und öffnen Sie Port 3306 in der Firewall
  • Neue Funktionen in MySQL 8.0 - Einführung in die Verwendung des Management-Ports

<<:  Verzeichnisberechtigungen beim Erstellen eines Containers mit Docker

>>:  Bootstrap+Jquery zum Erreichen eines Kalendereffekts

Artikel empfehlen

So installieren Sie OpenJDK in Docker und führen das JAR-Paket aus

Bild herunterladen Docker-Pull OpenJDK Erstellen ...

Zusammenfassung der Tipps zum Festlegen der maximalen Anzahl von Verbindungen in MySQL

Methode 1: Befehlszeilenänderung Wir müssen nur d...

So installieren Sie Tomcat-8.5.39 auf centos7.6

So installieren Sie Tomcat-8.5.39 auf CentOS 7.6....

Beispiel für asynchronen Dateiupload in HTML

Code kopieren Der Code lautet wie folgt: <form...

Zusammenfassung der Tipps zum Erstellen von Webseiten

Vorwort Dieser Artikel fasst hauptsächlich einige...

Zusammenfassung der HTML-Hack-Tags im IE-Browser

Code kopieren Der Code lautet wie folgt: <!--[...

JavaScript-Entwurfsmuster – Muster der Verantwortungskette

Inhaltsverzeichnis Überblick Code-Implementierung...

Code zum Ausrichten von Kontrollkästchen und Radiotext im Formular

Ausrichtungsprobleme wie type="radio" un...

Diagramm des Tutorials zum Herunterladen und Installieren von MySQL8.0 für Win10 64-Bit

Wie lade ich MySQL von der offiziellen Website he...

Lösung zum Verbergen der Versionsnummer und der Webserverinformationen von Nginx

Nginx kann nicht nur Versionsinformationen verber...

So zeigen Sie die Netzwerkroutingtabelle in Ubuntu an

Was sind Routing und Routing-Tabellen in Linux? U...

Spezifische Verwendung von MySQL-Globalsperren und Sperren auf Tabellenebene

Inhaltsverzeichnis Vorwort Globale Sperre Tabelle...

Eine kurze Analyse des Ereignisdelegierungsmechanismus und des tiefen und flachen Kopierens in JavaScript

Inhaltsverzeichnis 1. Veranstaltungsdelegation Er...

8 Befehle zur effektiven Verwaltung von Prozessen in Linux

Vorwort Die Rolle des Prozessmanagements: Integri...

Das Fehlschlagen der Wiedergabe von FLV-/MP4- und anderen Videodateien auf der Website hängt mit dem MIME-Typ zusammen.

Beim Erstellen einer Website habe ich festgestellt...