Detaillierte Einführung in das Prinzip von MySQL JOIN

1. Übersicht über die Join-Syntax

Join wird zum Verbinden von Feldern in mehreren Tabellen verwendet. Die Syntax lautet wie folgt:

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

Tisch1: linker Tisch; Tisch2: rechter Tisch.

JOIN ist entsprechend seiner Funktion grob in die folgenden drei Kategorien unterteilt:

INNER JOIN (Inner Join oder gleichwertiger Join): Erhält Datensätze mit einer Verbindungsübereinstimmungsbeziehung in zwei Tabellen.

LEFT JOIN (linker Join): Ruft die vollständigen Datensätze der linken Tabelle (Tabelle1) ab, dh es gibt keinen entsprechenden übereinstimmenden Datensatz in der rechten Tabelle (Tabelle2).

RIGHT JOIN (Right Join): Im Gegensatz zu LEFT JOIN werden vollständige Datensätze der rechten Tabelle (Tabelle2) erhalten, d. h. es gibt keinen passenden entsprechenden Datensatz in der linken Tabelle (Tabelle1).

Hinweis: MySQL unterstützt keinen vollständigen Join, aber Sie können das Schlüsselwort UNION verwenden, um LEFT JOIN und RIGHT JOIN zu kombinieren, um einen FULL Join zu simulieren.

Schauen Sie sich zunächst die beiden Tabellen im Experiment an :

Tabellenkommentare, die Gesamtzahl der Zeilen beträgt 28856

Tabellenkommentare_for, die Gesamtzahl der Zeilen beträgt 57, comments_id ist indiziert und die ID Spalte ist der Primärschlüssel.

Die beiden oben genannten Tabellen sind die Grundlage für unseren Test. Schauen Sie sich dann den Index an. Die comments_for-Tabelle ist indiziert und die ID ist der Primärschlüssel.

Kürzlich fragte mich ein Entwickler des Unternehmens nach MySQL JOIN JOIN. Nachdem ich es ausführlich dargelegt hatte, sagte ich, dass mein Verständnis von MySQL JOIN nicht sehr tief sei, also habe ich auch viele Dokumente überprüft und es schließlich gepostet Auf dem offiziellen InsideMySQL-Konto habe ich zwei Analyseartikel zu JOIN gesehen und fand, dass sie sehr gut geschrieben waren. Lassen Sie uns zunächst den JOIN-Algorithmus von MySQL vorstellen, der in drei Typen unterteilt ist (Quelle: InsideMySQL):

MySQL unterstützt im Gegensatz zu anderen Unternehmen nur einen JOIN-Algorithmus, Nested-Loop Join (Nested-Loop-Link). Die Datenbank kann Hash unterstützen Links und Merge-Verbindungen, aber MySQLs Nested-Loop Join (Nested-Loop-Link) hat auch viele Varianten, die MySQL dabei helfen können, JOIN-Vorgänge effizienter durchzuführen:

(1) Simple Nested-Loop Join (Bild von InsideMySQL )

Dieser Algorithmus ist relativ einfach. R1 wird aus der Treibertabelle entnommen, um alle Spalten der S-Tabelle abzugleichen, und dann R2, R3, bis alle Die Daten in der R-Tabelle werden abgeglichen und dann werden die Daten zusammengeführt. Sie können sehen, dass dieser Algorithmus RN-Zugriffe auf die S-Tabelle erfordert. Obwohl er einfach ist, ist der Overhead immer noch relativ hoch

(2) Index Nested-Loop Join, die Implementierungsmethode lautet wie folgt:

Index verschachtelte Verbindung Da es Indizes für die nicht gesteuerte Tabelle gibt, beim Vergleichen Anstatt zu vergleichen Da Datensätze einzeln erfasst werden, können Indizes verwendet werden, um Vergleiche zu reduzieren und so Abfragen zu beschleunigen. Dies ist einer der Hauptgründe, warum wir bei verwandten Abfragen normalerweise verlangen, dass die zugehörigen Felder über Indizes verfügen.

Wenn dieser Algorithmus eine Linkabfrage durchführt, sucht die Treibertabelle basierend auf dem Index des zugehörigen Felds. Wenn ein passender Wert im Index gefunden wird, kehrt er zur Abfrage in die Tabelle zurück. nur wenn der Index übereinstimmt. Nur dann wird die Tabelle zurückgegeben. Was die Auswahl der Treibertabelle betrifft, wählt der MySQL-Optimierer im Allgemeinen die Treibertabelle mit einer geringen Anzahl von Datensätzen. Bei besonders komplexer SQL sind jedoch falsche Auswahlen nicht auszuschließen.

Wenn im Index-Verschachtelungsmodus der zugehörige Schlüssel der nicht gesteuerten Tabelle der Primärschlüssel ist, ist die Leistung sehr hoch. Wenn es sich nicht um den Primärschlüssel handelt, beträgt die Anzahl der zurückgegebenen Zeilen Wenn die Zuordnung groß ist, ist die Effizienz besonders gering, da mehrere Tabellenrückgabevorgänge erforderlich sind. Ordnen Sie zunächst den Index zu und führen Sie dann den Tabellenrückgabevorgang basierend auf der Primärschlüssel-ID des Sekundärindex durch. In diesem Fall wird die Leistung relativ schlecht sein.

(3) Nested-Loop-Join blockieren, wie folgt implementiert:

Wenn ein Index vorhanden ist, versucht MySQL, Index Nested zu verwenden -Loop-Join-Algorithmus In einigen Fällen verfügt die Join-Spalte möglicherweise nicht über einen Index. In diesem Fall wird MySQL nicht zuerst den Simple Nested-Loop-Join-Algorithmus auswählen, sondern dem Block Nested-Loop-Join-Algorithmus Vorrang geben .

Im Vergleich zum einfachen Nested-Loop-Join verfügt der Block-Nested-Loop-Join über einen zusätzlichen Zwischenverarbeitungsprozess, nämlich den Join-Puffer. Verwenden Sie den Join-Puffer, um alle mit der Abfrage verbundenen Spalten der Treibertabelle zu puffern JOIN BUFFER und dann Batches mit nicht gesteuerten Tabellen vergleichen. Wenn dies ebenfalls implementiert ist, können mehrere Vergleiche zu einem zusammengefasst werden, wodurch die Zugriffshäufigkeit nicht gesteuerter Tabellen verringert wird. Das heißt, auf die S-Tabelle muss nur einmal zugegriffen werden. Auf diese Weise wird nicht mehrmals auf die nicht gesteuerte Tabelle zugegriffen, und nur in diesem Fall wird auf den Join-Puffer zugegriffen.

In MySQL können wir den Wert des Join-Puffers über den Parameter join_buffer_size festlegen und dann den Vorgang ausführen. Standardmäßig „join_buffer_size=256K“ speichert MySQL während der Suche alle erforderlichen Spalten im Join-Puffer zwischen, einschließlich der ausgewählten Spalten, anstatt nur die zugehörigen Spalten zwischenzuspeichern. In einer SQL mit N JOIN-Zuordnungen werden während der Ausführung N-1 Join-Puffer zugewiesen.

Die obige Einführung ist abgeschlossen. Schauen wir uns die spezifischen Beispiele an

(1) Vollständiger Tisch JOIN

EXPLAIN SELECT * FROM comments gc
JOIN comments_for gcf ON gc.comments_id=gcf.comments_id;

Sehen Sie sich die Ausgabeinformationen an:

Sie können comments_for während sehen Vollständiger Tabellenscan Da es sich um eine gesteuerte Tabelle handelt, wird ein vollständiger Indexscan für den Index idx_commentsid durchgeführt, um die nicht gesteuerten Tabellenkommentare abzugleichen, da die zugehörigen Felder indiziert sind. Dabei kann jedes Mal eine Zeile abgeglichen werden. Zu diesem Zeitpunkt wird Index Nested-Loop Join verwendet und die gesamte Tabelle wird über den Index abgeglichen. Da die Größe der Tabelle comments_for viel kleiner ist als die der Kommentare, gibt MySQL der kleinen Tabelle comments_for als Treiber Priorität Tisch.

(2) Vollständige Tabelle JOIN + Filterbedingungen

SELECT * FROM comments gc
JOIN comments_for gcf ON gc.comments_id=gcf.comments_id
WHERE gc.comments_id =2056

Zu diesem Zeitpunkt wird der Index-Nested-Loop-Join verwendet, um den Index idx_commentsid der nicht gesteuerten Tabelle comments_for zu finden Es wird erwartet, dass sich das endgültige Übereinstimmungsergebnis auf ein Element auswirkt. Dies ist nur eine Zugriffsoperation für den idx_commentsid-Index der nicht gesteuerten Tabelle, und die Effizienz war relativ hoch.

(3) Sehen Sie sich die Situation an, in der das zugehörige Feld keinen Index hat:

EXPLAIN SELECT * FROM comments gc
JOIN comments_for gcf ON gc.order_id=gcf.product_id

Werfen wir einen Blick auf den Ausführungsplan:

Aus dem Ausführungsplan können wir ersehen, dass diese Tabelle JOIN zur Ausführung Block Nested-Loop Join verwendet Tabellenzuordnung. Zuerst wird die kleine Tabelle comments_for (nur 57 Zeilen) als Treibertabelle verwendet, dann werden die erforderlichen Daten von comments_for im JOIN-Puffer zwischengespeichert und die Kommentartabelle wird stapelweise gescannt, d. h. nur eine Übereinstimmung Voraussetzung ist, dass der Join-Puffer groß genug ist, um comments_for zwischengespeicherte Daten zu speichern.

Und wir sehen eine sehr klare Eingabeaufforderung im Ausführungsplan: Using where; Using join buffer (Block Nested Loop)

Wenn dies geschieht, beweist dies im Allgemeinen, dass unser SQL optimiert werden muss . .

Es ist zu beachten, dass MySQL in diesem Fall auch die gewalttätige Methode des Simple Nested-Loop Join wählt. Ich habe nicht verstanden, wie dieser Optimierer ausgewählt wird, aber normalerweise wird Block Nested-Loop Join verwendet Da CBO auf Overhead basiert, ist die Leistung von Block Nested-Loop Join viel besser als die von Simple Nested-Loop Join.

(4) Schauen Sie sich den linken Join an

EXPLAIN SELECT * FROM comments gc
LEFT JOIN comments_for gcf ON gc.comments_id=gcf.comments_id

Sehen Sie sich den Ausführungsplan an:

Da unsere zugehörigen Felder in diesem Fall indiziert sind, rufen wir Index Nested-Loop Join auf. Wenn jedoch keine Filterbedingung vorliegt, wird die erste Tabelle als ausgewählt Verwenden Sie die Treibertabelle, um JOIN auszuführen, und verknüpfen Sie den Index der nicht gesteuerten Tabelle, um Index Nested-Loop Join durchzuführen.

Wenn Sie die Filterbedingung gc.comments_id =2056 hinzufügen, wird ein Index Nested-Loop Join für die nicht gesteuerte Tabelle herausgefiltert, was sehr effizient ist.

Wenn es das Folgende ist:

EXPLAIN SELECT * FROM comments_for gcf
LEFT JOIN comments gc ON gc.comments_id=gcf.comments_id
WHERE gcf.comments_id =2056

Wenn Sie durch die gcf-Tabelle filtern, wird es so sein Standardmäßig ausgewählt Die gcf-Tabelle wird als Treibertabelle verwendet, da sie offensichtlich gefiltert wurde und es nur sehr wenige übereinstimmende Bedingungen gibt. Einzelheiten finden Sie im Ausführungsplan:

Hier Punkt, der Join ist im Grunde klar und noch nicht fertig. Fortsetzung: Jeder ist herzlich eingeladen, auf Fehler hinzuweisen, und ich werde sie ernsthaft korrigieren. . . .

Das obige ist der detaillierte Inhalt vonDetaillierte Einführung in das Prinzip von MySQL JOIN. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!