Detaillierte Erläuterung der Datenbank-Hash-Verbindung (neue Funktion von MySQL)

Übersicht

Lange Zeit war der einzige Algorithmus, den MySQL zum Durchführen von Verknüpfungen verwendete, der Nested-Loop-Algorithmus ), aber der Nested-Loop-Algorithmus ist in bestimmten Szenarien sehr ineffizient, was ebenfalls ein Problem ist, für das MySQL kritisiert wurde.

Mit der Veröffentlichung von MySQL 8.0.18 kann MySQL Server Hash-Join verwenden und sehen, wie es in MySQL funktioniert, wann es verwendet wird und was es ist Einschränkungen.

Empfohlenes Lernen: MySQL-Tutorial

Einführung in Hash Join

Was ist ein Hash Join?

Hash-Join ist ein Join-Algorithmus, der in relationalen Datenbanken verwendet wird und nur für Joins mit gleichen Join-Bedingungen (auf a.b = c.b) verwendet werden kann. Er ist im Allgemeinen effizienter als der Nested-Loop-Algorithmus (außer wenn das Sondenende sehr, sehr klein ist), insbesondere wenn kein Indextreffer vorliegt.

Einfach ausgedrückt besteht der Hash-Join-Algorithmus darin, zuerst eine kleine Tabelle in die Speicher-Hash-Tabelle zu laden, dann die Daten in der großen Tabelle zu durchlaufen, die qualifizierten Daten in der Hash-Tabelle Zeile für Zeile abzugleichen und Geben Sie es an den Kunden zurück.

(Die Hash-Tabelle ist nur ein Beispiel. Zum Verständnis ist der Schlüssel des tatsächlichen Hashs der Wert der Verbindung und der Wert ist die Datenzeilenliste)

Normalerweise ist die Hash-Verbindung in zwei Phasen unterteilt, die Build-Phase und die Probe-Phase. Wählen Sie in der Konstruktionsphase zunächst die entsprechende Tabelle als „Build-Eingabe“ aus, erstellen Sie die Hash-Tabelle und durchlaufen Sie dann die Datensätze einer anderen „Erkennungseingabe“-Tabelle, um die Hash-Tabelle zu erkennen und Datensätze zu finden, die die Verbindungsbedingungen erfüllen.

Das obige Bild ist ein Beispiel für die Abfrage der Provinz, die der Stadt entspricht. Wir gehen davon aus, dass Stadt die Konstruktionseingabe ist. Während der Konstruktionsphase erstellt der Server eine Stadt-Hash-Tabelle, durchläuft die Stadt-Tabelle und fügt die Zeilen der Reihe nach in die Hash-Tabelle ein. Der Schlüssel ist hash(province_id) und der Wert ist entsprechende Stadtzeile. `

Während der Probe-Phase beginnt der Server, Zeilen vom Probe-Eingang (Provinz) zu lesen. Für jede Zeile wird die Hash-Tabelle nach übereinstimmenden Zeilen durchsucht, wobei der Wert hash(province.province_id) als Suchschlüssel verwendet wird.

Das heißt, wenn alle Konstruktionseingaben in den Speicher geladen werden können, wird jede Erkennungslinie nur einmal gescannt und eine passende Linie zwischen den beiden Eingaben kann mithilfe einer zeitkonstanten Suche gefunden werden.

Was soll ich tun, wenn zu viele Daten vorhanden sind, die nicht im Speicher abgelegt werden können?

Das Laden aller Build-Eingaben in den Speicher ist zweifellos am effizientesten, aber in manchen Fällen reicht der Speicher nicht aus, um die gesamte Tabelle in den Speicher zu laden, sodass sie stapelweise verarbeitet werden muss.

Es gibt zwei gängige Methoden:

Laden in den Speicher zur stapelweisen Verarbeitung

1. Auslesen der maximalen Speicherdose Erstellen Sie eine Hash-Tabelle, um die Datensätze aufzunehmen, und generieren Sie eine Hash-Tabelle.

2. Durchlaufen Sie die Erkennungseingabe und führen Sie eine vollständige Erkennung dieses Teils der Hash-Tabelle durch 3. Bereinigen Sie die Hash-Tabelle und starten Sie sie neu. Setzen Sie diesen Vorgang fort, bis die gesamte Verarbeitung abgeschlossen ist.

Diese Methode führt dazu, dass die gesamte Erkennungseingabetabelle mehrmals gescannt wird.

Write to File Processing

1. Wenn der Speicher während der Build-Hash-Tabellenphase erschöpft ist, schreibt der Server die verbleibenden Build-Eingaben in kleine Dateien auf viele Festplatten , alle kleinen Dateiblöcke können nach der Berechnung in den Speicher eingelesen und eine Hash-Tabelle erstellt werden (um zu große Dateiblöcke zu vermeiden, die später nicht in den Speicher geladen werden können und erneut getrennt werden müssen); In der Erkennungsphase kann die Erkennungszeile mit einer auf die Festplatte geschriebenen Zeile übereinstimmen, daher muss die Erkennungseingabe auch auf die Festplatte geschrieben werden

3 Blockdatei wird von der Festplatte gelesen und in den Speicher geladen. Lesen Sie in der Hash-Tabelle die Antwortblockdatei aus dem Erkennungseingang und erkennen Sie das passende Element

4. Fahren Sie nach der Verarbeitung mit dem nächsten Blockdateipaar fort Die gesamte Verarbeitung ist abgeschlossen.

Hash-Join-Implementierung in MySQL

MySQL wählt die kleinere der beiden Eingaben als Build-Eingabe aus (berechnet in Bytes) und wenn genügend Speicher vorhanden ist In einigen Fällen wird die Build-Eingabe zur Verarbeitung in den Speicher geladen. In Fällen, in denen dies nicht ausreicht, wird sie durch Schreiben in eine Datei verarbeitet. Mit der Systemvariablen „join_buffer_size“ können Sie die Speichernutzung von Hash-Verbindungen steuern. Wenn diese Menge überschritten wird, verwendet MySQL Dateien zur Verarbeitung.

Die Ausführung schlägt möglicherweise fehl, wenn der Speicher die Join_Buffer_Size überschreitet und die Dateien das Open_Files_Limit überschreiten.

Sie können die folgenden zwei Lösungen verwenden:

● Erhöhen Sie die Größe von „join_buffer_size“, um einen Hash-Join-Überlauf auf die Festplatte zu vermeiden. ● Erhöhen Sie „open_files_limit“

Unter welchen Umständen verwendet MySQL Hash-Joins?

In MySQL Version 8.0.18 wird ein Hash-Join verwendet, wenn Tabellen mithilfe einer oder mehrerer gleicher Join-Bedingungen verknüpft werden und kein Index für die Join-Bedingung verfügbar ist. MySQL bevorzugt die Verwendung von Indexsuchen, um verschachtelte Schleifen zu unterstützen, sofern ein Index verfügbar ist.

Standardmäßig verwendet MySQL wann immer möglich Hash-Joins, die auf die folgenden zwei Arten aktiviert oder deaktiviert werden können:

● Legen Sie globale oder Sitzungsvariablen fest (hash_join = on oder hash_join = off);

SET optimizer_switch="hash_join=off";

● Verwenden Sie Hinweise (HASH_JOIN oder NO_HASH_JOIN).

Wir verwenden die folgende Abfrage als Beispiel:

EXPLAIN FORMAT = tree
SELECT
  city.name AS city_name,
  province.name AS province_name
FROM
  city
  JOIN province
    ON city.province_id = province.province_id;

Die Ausgabe lautet:

| -> Inner hash join (city.province_id = province.province_id)  (cost=1333.82 rows=1329)
    -> Table scan on city  (cost=0.14 rows=391)
    -> Hash
        -> Table scan on province  (cost=3.65 rows=34)

Hash-Joins können auch in mehreren Join-Abfragen verwendet werden, sofern vorhanden Bei einem Equijoin können Sie eine Hash-Verbindung verwenden.

Zum Beispiel die folgende Abfrage:

EXPLAIN FORMAT= TREE
SELECT
  city.name AS city_name,
  province.name AS province_name,
  country.name AS country_name
FROM
  city
  JOIN province
    ON city.province_id = province.province_id
    AND city.id < 50
  JOIN country
    ON province.province_id = country.id

Die Ausgabe lautet:

| -> Inner hash join (city.province_id = country.id)  (cost=23.27 rows=2)
    -> Filter: (city.id < 50)  (cost=5.32 rows=5)
        -> Index range scan on city using PRIMARY  (cost=5.32 rows=49)
    -> Hash
        -> Inner hash join (province.province_id = country.id)  (cost=4.00 rows=3)
            -> Table scan on province  (cost=0.59 rows=34)
            -> Hash
                -> Table scan on country  (cost=0.35 rows=1)

Hash-Verbindung ist auch auf „Kartesisches Produkt“ anwendbar, d. h. es werden keine Abfragebedingungen angegeben , wie folgt:

EXPLAIN FORMAT= TREE
SELECT
  *
FROM
  city
  JOIN province;

Die Ausgabe lautet:

| -> Inner hash join  (cost=1333.82 rows=13294)
    -> Table scan on city  (cost=1.17 rows=391)
    -> Hash
        -> Table scan on province  (cost=3.65 rows=34)

Unter welchen Umständen verwendet MySQL keine Hash-Joins?

1. Derzeit unterstützt MySQL-Hash-Join nur Inner-Joins, Semi-Joins und Outer-Joins, die immer noch mithilfe von verschachtelten Blockschleifen ausgeführt werden.

2. Wenn der Index verfügbar ist, verwendet MySQL vorzugsweise die Indexsuche, um verschachtelte Schleifen zu unterstützen.

3. Wenn keine entsprechende Abfrage vorhanden ist, werden verschachtelte Schleifen verwendet.

lautet wie folgt:

EXPLAIN FORMAT=TREE
SELECT
  *
FROM
  city
  JOIN province
    ON city.province_id < province.province_id;

Die Ausgabe lautet:

| <not executable by iterator executor>

Wie überprüfe ich, ob die Anweisungsausführung eine Hash-Verbindung verwendet?

EXPLAIN FORMAT= TREE kann in MySQL 8.0.16 und späteren Versionen verwendet werden und beschreibt die Abfrageverarbeitung genauer als das herkömmliche Format Format der griechischen Verbindungsverwendung.

Darüber hinaus können Sie EXPLAIN ANALYZE auch verwenden, um Hash-Verbindungsinformationen anzuzeigen.

---

Das Obige basiert auf MySQL Community Server 8.0.18.

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung der Datenbank-Hash-Verbindung (neue Funktion von MySQL). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!