Eine kurze Diskussion über die Ausführungsdetails der Mysql Multi-Table Join-Abfrage

Erstellen Sie zunächst die Falldemonstrationstabelle für dieses Blog:

Tabelle a erstellen (a1 int Primärschlüssel, a2 int, Index (a2)); – Beide Felder haben Indizes. Tabelle c erstellen (c1 int Primärschlüssel, c2 int, Index (c2), c3 int); – Beide Felder haben Indizes. Tabelle b erstellen (b1 int Primärschlüssel, b2 int); – Index des Primärschlüssels. Tabelle d erstellen (d1 int, d2 int); – Kein Index. In einen Wert einfügen (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), (9,9), (10,10);
in b Werte einfügen(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
in c einfügen: Werte(1,1,1),(2,4,4),(3,6,6),(4,5,5),(5,3,3),(6,3,3),(7,2,2),(8,8,8),(9,5,5),(10,3,3);  
in d Werte einfügen(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);

Wie wählt man die Treibertabelle aus?

Der Begriff „treibende Tabelle“ bezieht sich auf die erste Tabelle, die bei der Abfrage mehrerer Tabellen verarbeitet wird. Die Datensätze dieser Tabelle werden verwendet, um andere Tabellen zu verknüpfen. Die Bestimmung der treibenden Tabelle ist kritisch, da sie die Assoziationsreihenfolge mehrerer Tabellenverbindungen direkt beeinflusst und auch die Abfrageleistung während nachfolgender Assoziationen bestimmt.

Die Auswahl der treibenden Tabelle folgt einem Prinzip:在對最終結果集沒影響的前提下，優先選擇結果集最小的那張表作為驅動表. Das Ändern der Treibertabelle bedeutet, dass die Verbindungsreihenfolge geändert wird. Die Treibertabelle kann nur optimiert werden, wenn sich dadurch das endgültige Ausgabeergebnis nicht ändert. Bei äußeren Verknüpfungen wirkt sich das Ändern der treibenden Tabelle häufig auf die Ausgabeergebnisse aus. Beispielsweise die linke Tabelle einer linken Verknüpfung und die rechte Tabelle einer rechten Verknüpfung. Wenn die treibende Tabelle die linke oder rechte Seite der Verknüpfung auswählt, sind die endgültigen Ausgabeergebnisse wahrscheinlich unterschiedlich.

Verwenden Sie den Ergebnissatz, um die treibende Tabelle auszuwählen. Was ist der Ergebnissatz? Wie berechnet man den Ergebnissatz? Vor der Auswahl nimmt MySQL für jede Tabelle, die als Treibertabelle verwendet werden kann, eine Schätzung der Ergebnisdatensätze vor. Dabei werden die Screeningbedingungen der einzelnen Tabellen berücksichtigt, und es schätzt die Anzahl der von jeder Tabelle zurückgegebenen Zeilen. Anschließend multipliziert es diese mit der Gesamtbytegröße der in „select“ abgefragten Felder:

每行查詢字節數* 預估的行數= 預估結果集

Verwenden Sie where, um die Anzahl der Ergebniszeilen zu schätzen. Beachten Sie dabei die folgenden Regeln:

• Wenn in „where“ keine Filterbedingung für die entsprechende Tabelle vorhanden ist, wird als Standard die gesamte Tabelle verwendet, unabhängig davon, ob in „on“ eine entsprechende Bedingung vorhanden ist.
• Wenn in „where“ eine Filterbedingung vorhanden ist, der Index jedoch nicht zum Filtern verwendet werden kann, wird standardmäßig die gesamte Tabelle verwendet.
• Wenn eine Filterbedingung vorhanden ist und ein Index verwendet werden kann, wird die Anzahl der zurückgegebenen Zeilen basierend auf dem Index geschätzt.

Wir verwenden die oben erstellte Tabelle als Grundlage und verwenden zur Veranschaulichung das folgende SQL:

Wählen Sie a.*,c.c2 aus einem Join c auf a.a2=c.c2, wobei a.a1>5 und c.c1>5;

Sehen Sie sich den Ausführungsplan anhand der Erläuterungen an:

Die erste Zeile im Anzeigeergebnis „Explain“ ist die treibende Tabelle. In diesem Fall ist Tabelle c die treibende Tabelle.

Wenn Sie das SQL modifizieren, ändern Sie die Bedingung c.c2 in der Auswahl in c.* :

Wählen Sie a.*,c.* aus einem Join c auf a.a2=c.c2, wobei a.a1>5 und c.c1>5;

Sehen Sie sich den Ausführungsplan anhand der Erläuterungen an:

Zu diesem Zeitpunkt ist die treibende Tabelle immer noch c. Logischerweise muss die Datenmenge in c.* größer sein als die in a.*. Es scheint, dass die Regel der Ergebnismengengröße hier nicht funktioniert.

In diesem Fall müssen Sie, wenn a als treibende Tabelle verwendet wird und über den Index c2 mit der Tabelle c verknüpft ist, für die Abfrage erneut zur Tabelle zurückkehren, da die Daten von c.* nicht allein über c2 abgerufen werden können und Sie die Abfrage erneut über den Primärschlüssel c1 auf c2 durchführen müssen. Die vorherige SQL-Abfrage ist für c2, daher ist keine weitere Abfrage erforderlich. Da Tabelle a nur zwei Felder hat, kann a.* gleichzeitig direkt über den Index a2 abgerufen werden, ohne dass zusätzliche Abfragen erforderlich sind.

Zusammenfassend lässt sich sagen, dass bei Verwendung der C-Tabelle als Treiber zwar der Ergebnisset größer ist, die Anzahl zusätzlicher Tabellenrückgabeabfragen jedoch reduziert werden kann. MySQL ist daher der Ansicht, dass die Verwendung der C-Tabelle als Treiber effizienter ist.

Der Ergebnissatz ist ein wichtiger Faktor bei der Auswahl einer treibenden Tabelle, aber nicht der einzige.

Was ist die interne Logik der Zwei-Tabellen-Assoziationsabfrage?

Die Assoziationsabfrage zwischen MySQL-Tabellen verwendet den Nested-Loop-Join-Algorithmus, der, wie der Name schon sagt, ein Nested-Loop-Join ist. Je nach Szenario kann es jedoch verschiedene Varianten geben: z. B. Index Nested-Loop-Join, Simple Nested-Loop-Join, Block Nested-Loop-Join, Betched Key Access-Join usw.

• Bei使用索引-Joins gibt es zwei Algorithmen: Index Nested-Loop join und Batched Key Access join .
• Wenn未使用索引, gibt es zwei Algorithmen: Simple Nested-Loop join und Block Nested-Loop join .

Betrachten wir zunächst den Fall mit einem Index. Dabei verwenden wir die am Anfang des Blogs erstellte Tabelle. Das SQL lautet wie folgt:

Wählen Sie a.*,c.* aus einem Join c auf a.a2=c.c2, wobei a.a1>4;

Sehen Sie sich den Ausführungsplan anhand der Erläuterungen an:

Bestimmen Sie zunächst die treibende Tabelle a gemäß der Logik des ersten Schritts, fragen Sie dann einen Datensatz a1 = 5 über a.a1>4 ab, a. verknüpfen Sie c2 dieses Datensatzes mit der Tabelle c, ermitteln Sie den Primärschlüssel c1 im Index c2, verwenden Sie dann den Wert von c1, um c.* im Clustered-Index abzufragen, um ein vollständiges Ergebnis zu bilden, legen Sie es in den Netzpuffer und nehmen Sie dann gemäß der Bedingung a.a1>4 den nächsten Datensatz, a. wiederholen Sie diesen Vorgang. Das Prozessdiagramm sieht wie folgt aus:

Die gesteuerte Tabelle wird über den Index verknüpft. Dabei wird der Index Nested-Loop-Join-Algorithmus verwendet. Der MSYQL-Join-Puffer wird nicht verwendet. Gemäß den Filterbedingungen der Treibertabelle werden die Indizes der angetriebenen Tabelle nacheinander verknüpft. Jedes Mal, wenn ein passender Datensatz verknüpft wird, wird er in den Netzpuffer gestellt und dann wird die Verknüpfung fortgesetzt. Dieser Pufferbereich wird durch den Parameter net_buffer_length gesteuert, mit einem Minimum von 4 KB, einem Maximum von 16 M und einem Standardwert von 1 M. Wenn der Netzpuffer voll ist, senden Sie es an den Client, löschen Sie den Netzpuffer und setzen Sie den vorherigen Vorgang fort.

Aus dem obigen Prozess wissen wir, dass, wenn jeder Datensatz der treibenden Tabelle mit der getriebenen Tabelle verknüpft ist, es notwendig ist, einmal zur Tabelle zurückzukehren, um den Datensatz im gruppierten Index abzufragen, wenn Daten benötigt werden, die nicht im Index enthalten sind. Dies ist ein zufälliger Abfrageprozess. Jeder Datensatz ist eine zufällige Abfrage und die Leistung ist nicht sehr hoch. MySQL optimiert diese Situation gezielt und wandelt diese zufällige Abfrage in eine sequentielle Abfrage um. Der Ausführungsprozess läuft wie folgt ab:

Zu diesem Zeitpunkt wird der Batched Key Access-Join-Algorithmus verwendet. Wie der Name schon sagt, handelt es sich um eine Batch-Key-Access-Verbindung.

Fragen Sie die treibende Tabelle nacheinander entsprechend der Where-Bedingung ab, fügen Sie die Datenzeilen, die dem Datensatz entsprechen, in den Verbindungspuffer ein, rufen Sie dann den Indexdatensatz der getriebenen Tabelle entsprechend dem zugehörigen Index ab und speichern Sie ihn im read_rnd_buffer. Sowohl der Join-Buffer als auch der Read_rnd_Buffer haben Größenbeschränkungen. Wenn einer von beiden die Obergrenze erreicht, wird die Datenverarbeitung für diesen Batch gestoppt. Nach der Verarbeitung werden die Daten gelöscht und der nächste Batch ausgeführt. Das heißt, die Daten, die die Bedingungen in der Treibertabelle erfüllen, können möglicherweise nicht alle auf einmal verarbeitet werden, sondern müssen stapelweise verarbeitet werden.

Wenn das Batch-Limit erreicht ist, werden die Indizes der angetriebenen Tabelle im read_rnd_buffer aufsteigend nach Primärschlüssel sortiert, sodass beim Zurückkehren zur Tabelle zur Abfrage eine annähernd sequentielle Abfrage durchgeführt werden kann:

Wie in der Abbildung oben gezeigt, ist die linke Seite ein schematisches Diagramm einer zufälligen Abfrage vor dem Sortieren und die rechte Seite ein schematisches Diagramm einer sequentiellen Abfrage mit MRR ( Multi-Range Read ) nach dem Sortieren.

Da die Daten der InnoDB-Engine von MySQL nach dem gruppierten Index angeordnet sind, wird beim Sortieren des nicht gruppierten Index nach dem Primärschlüssel der Primärschlüssel zum Abfragen verwendet, wodurch die zufällige Abfrage in eine sequentielle Abfrage umgewandelt wird. Die sequentielle Abfrage des Computers verfügt über einen Vorlesemechanismus, der beim Lesen einer Datenseite bis zu 1 MB Daten liest. Hier ist das sequenzielle Lesen praktisch.

Der BKA-Algorithmus kann die Ausführungslogik optimieren, wenn es erforderlich ist, die getriebene Tabelle an die Tabelle zurückzugeben. Wenn es nicht erforderlich ist, die Tabelle zurückzugeben, wird der BKA-Algorithmus natürlich nicht benötigt.

Wenn Sie den BKA-Optimierungsalgorithmus verwenden möchten, müssen Sie ihn vor der Ausführung der SQL-Anweisung festlegen:

Setzen Sie optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

Die ersten beiden Parameter werden verwendet, um MRR ( Multi-Range Read ) zu aktivieren. Der Grund hierfür ist, dass die Optimierung des BKA-Algorithmus auf MRR basieren muss. Laut der offiziellen Dokumentation neigt die aktuelle Optimierungsstrategie eher dazu, MRR bei der Beurteilung des Verbrauchs nicht zu verwenden. Wenn mrr_cost_based auf „off“ gesetzt wird, wird MRR immer verwendet. ）

Verwenden Sie abschließend „explain“, um den Ausführungsplan anzuzeigen, nachdem Sie den Parameter aktiviert haben:

In den oben genannten Fällen sind der gesteuerten Tabelle Indizes zugeordnet. Als Nächstes betrachten wir den Fall, in dem der gesteuerten Tabelle keine Indizes zugeordnet sind.

Wenn kein Index-Join verwendet wird, besteht der einfachste Simple Nested-Loop join darin, entsprechend der Where-Bedingung ein Datenelement aus der treibenden Tabelle abzurufen, dann die getriebene Tabelle vollständig zu scannen und die Datensätze, die die Bedingung erfüllen, in den endgültigen Ergebnissatz einzufügen. Auf diese Weise wird jeder Datensatz der treibenden Tabelle von einem vollständigen Tabellenscan der angetriebenen Tabelle begleitet. Dies ist ein einfacher Nested-Loop-Join.

Natürlich verwendet MySQL den Simple Nested-Loop-Join nicht direkt, sondern optimiert ihn. Anstatt die Daten der Treibertabelle einzeln abzurufen, werden mehrere Datensätze abgerufen, d. h. sie werden Stück für Stück abgerufen, was als Block Nested-Loop-Join bezeichnet wird. Bei jeder Datenerfassung entspricht die Obergrenze der Größe des Verbindungspuffers. Anschließend wird die gesteuerte Tabelle vollständig gescannt, alle Daten werden mit allen Zeilen im Verbindungspuffer abgeglichen und die abgeglichenen Daten werden in den endgültigen Ergebnissatz eingefügt. Dadurch wird die Anzahl der Scanvorgänge auf der angetriebenen Tabelle erheblich reduziert.

Die Prozesse von BNL ( Block Nested-Loop join ) und BKA ( Batched Key Access join ) sind einigermaßen ähnlich, aber es gibt keinen read_rnd_buffer Schritt.

Das Beispiel-SQL lautet wie folgt:

Wählen Sie a.*, d.* aus einem Join d auf a.a2=d.d2, wobei a.a1>7;

Verwenden Sie „explain“, um den Ausführungsplan anzuzeigen:

Wie führe ich eine Verknüpfung mehrerer Tabellen durch? Handelt es sich um die Ergebnismenge, die entsteht, wenn zuerst zwei Tabellen verknüpft und dann die dritte Tabelle zugeordnet wird, oder gibt es einen Datensatz, der sich durch das Ganze zieht?

Tatsächlich erkennt man schon am Namen des Join-Algorithmus: Nested-Loop-Join, dass es sich um einen Nested-Loop-Join mehrerer Tabellen handelt, statt zuerst zwei Tabellen zu verbinden, um das Ergebnis zu erhalten, und sie dann nacheinander zu verbinden. Seine Form ähnelt der folgenden:

für Zeile1 in Tabelle1, gefiltert nach where{
	für Zeile2 in Tabelle2, verknüpft durch Tabelle1.index1, gefiltert nach where{
		für Zeile3 in Tabelle3, verknüpft mit Tabelle2.index2, gefiltert nach where{
			in den Netzpuffer einfügen und dann an den Client senden;
		}
	}	
}

Für unterschiedliche Verbindungsmethoden gibt es die folgenden Situationen:

Index Nested-Loop join :

Das SQL lautet wie folgt:

Wähle a.*, b.*, c.* aus einem Join c auf a.a2=c.c2, Join b auf c.c2=b.b2, wobei b.b1>4;

Sehen Sie sich den Ausführungsplan anhand der Erläuterungen an:

Der interne Ausführungsprozess ist wie folgt:

Vor der Ausführung bestimmt der MySQL-Executor die Assoziationsreihenfolge jeder Tabelle. Zuerst wird der erste Datensatz b5 der treibenden Tabelle b durch die Where-Bedingung gefiltert und dann das zugehörige Feld b2 dieses Datensatzes mit dem Index a2 der zweiten Tabelle a verknüpft. Die Indexposition wird durch Btree lokalisiert. Es kann mehr als einen übereinstimmenden Index geben. Wenn das vorherige Element übereinstimmt, prüfen Sie, ob die Filterbedingung von a2 vorhanden ist und ob die Bedingung Daten außerhalb des Index erfordert. Wenn ja, kehren Sie zur Tabelle zurück, verwenden Sie den Primärschlüssel im Index a2, um die Daten abzufragen, und treffen Sie dann eine Entscheidung. Verwenden Sie dann die verknüpften Informationen, um Tabelle C in Kapitel 3 auf die gleiche Weise zu verknüpfen.

Block Nested-Loop join und Batched Key Access join : Diese beiden Join-Algorithmen ähneln Index Nested-Loop join -Algorithmus, aber da sie den Join-Puffer verwenden können, können sie jedes Mal einen Batch von Daten aus der Antriebstabelle filtern, anstatt einer Zeile. Gleichzeitig entspricht jedes Join-Schlüsselwort einem Join-Puffer, d. h. die treibende Tabelle und die zweite Tabelle verwenden einen Join-Puffer, und der erhaltene Blockergebnissatz und die dritte Kapiteltabelle verwenden einen Join-Puffer.

In diesem Blog geht es hauptsächlich um die oben genannten drei Probleme, um die Bestimmung der treibenden Tabelle, um die Ausführungsdetails der Zuordnung zwischen zwei Tabellen und um den Ausführungsprozess der Zuordnung zwischen mehreren Tabellen.

Dies ist das Ende dieses Artikels über die Ausführungsdetails der MySQL-Multitabellen-Join-Abfrage. Weitere relevante Inhalte zur MySQL-Multitabellen-Join-Abfrage finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!