Beispielanalyse für MySQL-Jointabelle und automatische ID-Inkrementierung

Wie schreibt man „join“?

Wenn Sie „Left Join“ verwenden, ist die Tabelle auf der linken Seite zwangsläufig die treibende Tabelle? Wenn die Verknüpfung zweier Tabellen mehrere gleiche Übereinstimmungsbedingungen enthält, sollen wir dann für alle oder nur für eine davon „on“ schreiben und den Rest in den „Where“-Teil schreiben?

Tabelle erstellen a(f1 int, f2 int, index(f1))engine=innodb;
 Tabelle erstellen b(f1 int, f2 int)engine=innodb;
 in Werte einfügen(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
 in b-Werte einfügen (3,3), (4,4), (5,5), (6,6), (7,7), (8,8);
wähle * aus einem linken Join b auf (a.f1=b.f1) und (a.f2=b.f2); /*Q1*/
 wähle * aus einem linken Join b auf (a.f1=b.f1), wobei (a.f2=b.f2);/*Q2*/

Ausführungsergebnis:

Da Tabelle b keinen Index hat, wird der Block Nexted Loop Join (BNL)-Algorithmus verwendet.

• Liest den Inhalt der Tabelle a in den Join-Buffer. Aufgrund von select * werden die Felder f1 und f2 beide in den Join-Buffer eingefügt.
• Scannen Sie b sequenziell. Bestimmen Sie für jede Datenzeile, ob die Verknüpfungsbedingung erfüllt ist. Die Datensätze, die die Bedingung erfüllen, werden als Zeile des Ergebnissatzes übernommen. Wenn eine Where-Klausel vorhanden ist, bestimmen Sie vor der Rückgabe, ob der Where-Teil die Bedingung erfüllt.
• Nachdem der Scan der Tabelle b abgeschlossen ist, werden die Zeilen der Tabelle a, die keine Übereinstimmungen aufweisen, mit Nullen aufgefüllt und in den Ergebnissatz eingefügt.

Erläutern Sie in der Q2-Erklärung das Ergebnis:

b ist die treibende Tabelle. Wenn das EXTRA-Feld einer Anweisung nichts enthält, handelt es sich um den Index Nested_Loop Join-Algorithmus. Der Prozess ist also:

Scannen Sie b sequenziell, verwenden Sie b.f1, um jede Zeile in a zu überprüfen, und vergleichen Sie, ob a.f2=b.f2 erfüllt ist, und geben Sie es als Ergebnissatz zurück.

Der Unterschied zwischen dem Ausführungsprozess von Q1 und Q2 besteht darin, dass der Optimierer basierend auf der Abfragesemantik von Q2 optimiert: In MySQL ist das Ergebnis der Gleichheits- und Ungleichheitsbeurteilung zwischen null und einem beliebigen Wert null, einschließlich „select null = null“, das ebenfalls null zurückgibt.

In Q2 bedeutet a.f2 = b.f2, dass das Abfrageergebnis keine Zeilen enthält, in denen b.f2 null ist. Die Semantik des Left Join besteht darin, in den beiden Tabellen dieselben Zeilen zu finden, in denen f1 und f2 einander entsprechen. Wenn a vorhanden ist, aber b nicht übereinstimmt, geben Sie auf. Daher schreibt der Optimierer den linken Join dieser Anweisung in einen Join um. Da f1 von a einen Index hat, wird b als treibende Tabelle verwendet, sodass der NLJ-Algorithmus verwendet werden kann. Daher ist bei Verwendung des linken Join die Tabelle auf der linken Seite nicht unbedingt die treibende Tabelle.

Wenn die Semantik des Left Join erforderlich ist, können die Felder der getriebenen Tabelle nicht in die Where-Bedingung für die Gleichheits- oder Ungleichheitsbeurteilung gestellt werden. Sie müssen in die On-Bedingung geschrieben werden.

Leistungsprobleme mit Nested Loop Join

Ausführungslogik des BLN-Algorithmus

• Lesen Sie alle Daten in der Treibertabelle in den Join_Buffer, ein ungeordnetes Array.
• Durchlaufen Sie nacheinander alle Zeilen der gesteuerten Tabelle, gleichen Sie jede Zeile mit dem Join_Buffer ab und geben Sie sie bei Erfolg als Teil des Ergebnissatzes zurück.

Die Logik des Simple Nested Loop Join-Algorithmus besteht darin, nacheinander jede Datenzeile in der Antriebstabelle zu entfernen und einen vollständigen Tabellenabgleich in der angetriebenen Tabelle durchzuführen.

Unterschiede zwischen den beiden:

Wenn beim Ausführen eines vollständigen Tabellenscans der angetriebenen Tabelle die Daten nicht im Pufferpool vorhanden sind, muss gewartet werden, bis einige Daten von der Festplatte gelesen wurden. Dies wirkt sich auf die Trefferquote des Pufferpools im normalen Geschäftsbetrieb aus und führt mehrere Zugriffe auf die gesteuerte Tabelle aus, sodass diese Datenseiten leichter an den Anfang des Pufferpools gestellt werden können. Daher wird die Leistung des BNL-Algorithmus besser sein. ID automatisch erhöhen

Die Auto-Increment-ID in MySQL definiert einen Anfangswert, der weiter wächst, aber eine Obergrenze von 2^32-1 hat. Was passiert, wenn die Auto-Increment-ID aufgebraucht ist?

Wenn der in der Tabelle definierte Auto-Increment-Wert die Obergrenze erreicht, bleibt der beim Beantragen der nächsten ID erhaltene Wert unverändert. Beim erneuten Einfügen wird ein Primärschlüsselkonfliktfehler gemeldet. Wenn beim Erstellen einer Tabelle häufig Hinzufügungen, Löschungen und Änderungen vorkommen, sollten Sie daher einen 8-Byte-Bigint ohne Vorzeichen erstellen.

Das Innodb-System erhöht automatisch die row_id

Wenn eine Innodb-Tabelle ohne Angabe eines Primärschlüssels erstellt wird, erstellt Innodb eine unsichtbare row_id mit einer Länge von 6 Bytes. Für alle Innodb-Tabellen ohne Primärschlüssel wird bei jedem Einfügen einer Datenzeile der aktuelle dict_sys.row_id-Wert als row_id der einzufügenden Daten verwendet und dann um 1 erhöht.

Tatsächlich ist row_id bei der Codeimplementierung eine vorzeichenlose lange Ganzzahl mit einer Länge von 8 Bytes, aber bei der Entwicklung von innodb ist row_id nur 6 Bytes lang, sodass beim Schreiben von Daten nur die letzten 6 Bytes platziert werden. Also:

• Der Bereich der in die Tabelle geschriebenen Zeilen-IDs liegt zwischen 0 und 2^48-1.
• Wenn das Maximum erreicht ist und ein weiterer Dateneinfügungsvorgang zum Anwenden auf die Zeilen-ID erfolgt, sind die letzten 6 Bytes nach dem Abrufen 0, und dann wird der Zyklus fortgesetzt.
• In der Logik von InnoDB werden nach Erreichen der maximalen Schleife die vorhandenen Daten durch neue Daten überschrieben.

Aus dieser Perspektive sollten wir aktiv einen automatisch inkrementierenden Primärschlüssel erstellen, sodass beim Erreichen der Obergrenze beim Einfügen von Daten ein Fehler gemeldet wird. Die Zuverlässigkeit der Daten wird dadurch besser gewährleistet.

XID

Wenn Redo-Log und Binlog zusammenarbeiten, verfügen sie über ein gemeinsames Feld namens xid, das den Transaktionen in MySQL entspricht. Der Maximalwert von xid beträgt 2^64 und existiert theoretisch nur, wenn er erschöpft ist.

Thread-ID

Das System speichert die globale Variable thread_id_counter. Jedes Mal, wenn eine neue Verbindung erstellt wird, wird thread_id_counter der Thread-Variable der neuen Verbindung zugewiesen. Die Größe des thread_id_counter ist als 4 Bytes definiert, sodass er auf 0 zurückgesetzt wird, wenn er 2^32-1 erreicht, und dann weiter ansteigt. Sie werden jedoch in der Prozessliste keine zwei identischen Thread-IDs sehen. Dies liegt daran, dass MySQL eine einzigartige Array-Logik entwickelt hat, um neuen Threads Thread-IDs zuzuweisen:

Tun {
 		neue_ID= Thread-ID-Zähler++;
 } während (!thread_ids.insert_unique(neue_id).Sekunde);

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.