MySQL-Joinpufferprinzip

1. MySQL-Joinpuffer

Bei der Verarbeitung von MySQL-Joinvorgängen ist der Join-Puffer ein wichtiges Konzept und eine wichtige Optimierungsmethode für den MySQL-Tabellenjoin. Obwohl die Implementierung dieses Konzepts nicht kompliziert ist, stellt es eine wichtige Methode zur Optimierung von MySQL-Joinverbindungen dar. Es kann die Effizienz von Join-Abfragen bei der Durchführung von „Brute-Force“-Verbindungen erheblich verbessern.

Die maßgebliche Beschreibung dieses Konzepts stammt aus der Beschreibung dieses Konzepts in der MySQL-Dokumentation. Die Beschreibung ist kurz, aber prägnant und erklärt die Hauptimplementierungsidee dieser Optimierung:
Angenommen, Sie haben die folgende Verbindung:

Tabellenname Typ
t1-Bereich
t2 Referenz
t3 ALLE
Der Join wird dann wie folgt durchgeführt:
 
- Während Zeilen im t1-Übereinstimmungsbereich
 - Alle Zeilen in t2 nach Referenzschlüssel durchlesen
  - Verwendete Felder aus t1, t2 im Cache speichern
  - Wenn der Cache voll ist
    - Alle Zeilen in t3 durchlesen
      - Vergleichen Sie die T3-Zeile mit allen T1-, T2-Kombinationen im Cache
        - Wenn die Zeile die Join-Bedingung erfüllt, senden Sie sie an den Client
    - Cache leeren
 
- Alle Zeilen in t3 durchlesen
 - Vergleichen Sie die T3-Zeile mit allen gespeicherten T1-, T2-Kombinationen im Cache
   - Wenn die Zeile die Join-Bedingung erfüllt, senden Sie sie an den Client

2. Verbinden Sie die Puffercache-Speicherplatzzuweisung

In der folgenden Funktion gibt table_count die Anzahl der nicht-konstanten Tabellen vor dieser Tabelle in allen Join-Tabellen an, da diese Tabelle die „zu lesenden“ Datensätze in allen vorherigen Tabellen zwischenspeichern muss (tables[i].table->read_set ist gesetzt).

Jede Ausführung der Doppelschleife kopiert die Beschreibungsstruktur des zu cachenden Felds (und seiner entsprechenden Datenquelle). Mit anderen Worten, die Doppelschleife dient nur zur Zuweisung und Speicherung von Metadaten, und das abschließende cache->buff=(uchar*) my_malloc(size,MYF(0)) ist die tatsächliche Zuweisung des Datensatzinhalts, der die Bedingungen erfüllt.

statische int
join_init_cache(THD *thd,JOIN_TAB *Tabellen,uint Tabellenanzahl)
{
…
  für (i=0; i < Tabellenanzahl; i++)
  {
    bool have_bit_fields = FALSE;
    uint null_felder=0,verwendete_felder;
    Feld **f_ptr,*Feld;
    MY_BITMAP *read_set = Tabellen[i].table->read_set;
    für (f_ptr=tables[i].table->field,used_fields=tables[i].used_fields;
 verwendete Felder;
 f_ptr++)
    {
      Feld= *f_ptr;
      wenn (Bitmap ist gesetzt (gelesener Satz, Feld->Feldindex))
      {
verwendete_Felder--;
Länge+=Feld->Cache-Feld füllen(kopieren);
…
      }
  }
 
  Cache->Länge = Länge + Blobs * Größe von (Zeichen *);
  Cache->Blobs=Blobs;
  *blob_ptr=0; /* Sequenz beenden */
  Größe=max(thd->Variablen.join_buff_size, Cache->Länge);
  wenn (!(cache->buff=(uchar*) my_malloc(Größe,MYF(0))))
    DBUG_RETURN(1); /* Cache nicht verwenden */ /* purecov: überprüft */
  Cache->Ende=Cache->Buff+Größe;
  cache_schreiben_zurücksetzen(cache);
  DBUG_RETURN(0);
}

3. Gewöhnliche Implementierung einer Abfrage mit mehreren Tabellen

Natürlich kann dieses „gewöhnliche“ auch als „einfach“ und „intuitiv“ verstanden werden, was in den meisten Fällen auch der Ausführungsprozess ist. Eine normale Abfrage ist eigentlich ein rekursiver Aufruf jeder Tabelle, was genau dasselbe ist wie eine Matrixmultiplikation. Diese Entsprechung ist sehr intuitiv und sehr gebräuchlich.

Diese reguläre Abfrageaktion wird durch die Funktion sub_select implementiert, die im Wesentlichen ausführt

tsecer_select()
{
für (r = erstes; r != Ende; r = nächstes)
{
wenn(sofartest())
{
nächsteTabelle.tsecer_select()
}
}
}

Das Sofartest() bedeutet hier „alle aktuell gelesenen Tabellen zur Urteilsbildung verwenden“, was der in „where“ nach unten verschobene Ausdruck ist. Beispielsweise ist in der Abfrage „select * from a, b where aa > 10 and bb + aa = 10“ nach dem Einlesen der Tabelle a die Prüfung, ob aa > 10 möglich ist, bereits möglich. Natürlich handelt es sich hierbei um eine Beschreibungsmethode, die nicht einmal ein Pseudocode ist. Der tatsächliche Code entspricht:

enum_nested_loop_state
Unterauswahl (JOIN *join, JOIN_TAB *join_tab, bool Ende_der_Datensätze)
{
…
    Fehler= (*Join_Tab->Ersten_Datensatz_lesen)(Join_Tab);
    rc = Join-Datensatz auswerten (Join, Join-Tab, Fehler);
…
  während (rc == NESTED_LOOP_OK)
  {
    Fehler = Info->Datensatz lesen(Info);
    rc = Join-Datensatz auswerten (Join, Join-Tab, Fehler);
  }
…
  rc zurückgeben;
}
statischer enum_nested_loop_state
Join_Record auswerten(JOIN *Join, JOIN_TAB *Join_Tab,
                     int-Fehler)
{
…
  wenn (Auswahlbedingung)
  {
    Wählen Sie das Ergebnis aus, und geben Sie die Bedingung aus.
 
    /* auf Fehler bei der Auswertung der Bedingung prüfen */
    wenn (join->thd->is_error())
      gibt NESTED_LOOP_ERROR zurück;
  }
…
    wenn (gefunden)
    {
      Aufzählung enum_nested_loop_state rc;
      /* Für den aktuellen Teiljoin wurde eine Übereinstimmung aus join_tab gefunden. */
      rc = (*Join-Tab->nächste_Auswahl)(Join, Join-Tab+1, 0);
      wenn (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
        rc zurückgeben;
      wenn (beitreten->return_tab < beitreten_tab)
        gibt NESTED_LOOP_OK zurück;
      /*
        Testen Sie, ob dies eine SELECT DISTINCT-Abfrage für eine Tabelle war, die
        war nicht in der Feldliste; In diesem Fall können wir abbrechen, wenn
        wir haben eine Zeile gefunden, da dem Ergebnis keine neuen Zeilen hinzugefügt werden können.
      */
      wenn (nicht_in_unterschiedlichen_Datensätzen_verwendet und_gefundene_Datensätze != join->gefundene_Datensätze)
        gibt NESTED_LOOP_NO_MORE_ROWS zurück;
    }
…
}

Wie Sie hier sehen können, handelt es sich um eine Rekursion, die zur Generierung eines kartesischen Kreuzproduktsatzes verwendet wird, der sowohl hinsichtlich der Programmimplementierung als auch des mathematischen Ausdrucks sehr prägnant und ansprechend ist.
In der MySQL-Implementierung entspricht die For-Schleife in der Funktion tsecer_select ungefähr der While-Schleife in sub_select, und der Inhalt des Schleifenkörpers in der Funktion tsecer_select wird in die Funktion evaluate_join_record eingefügt, wobei sofartest evaluate_join_record::test(select_cond->val_int()) entspricht; die Anweisung nexttable.tsecer_select() in tsecer_select entspricht evaluate_join_record::(*join_tab->next_select)(join, join_tab+1, 0).

4. Implementierung des Join-Puffers auswählen

Bei Verwendung des Join-Buffer-Cache zeigt die Funktion next_select auf sub_select_cache

enum_nested_loop_state
sub_select_cache(JOIN *join,JOIN_TAB *join_tab,bool Ende_der_Datensätze)
{
  enum_nested_loop_state rc;
 
  wenn (Ende_der_Datensätze)
  {
    rc = zwischengespeicherte Datensätze leeren(beitreten, beitreten_tab, FALSCH);
    wenn (rc == NESTED_LOOP_OK || rc == NESTED_LOOP_NO_MORE_ROWS)
      rc = Unterauswahl (Join, Join-Tab, Ende der Datensätze);
    rc zurückgeben;
  }
  if (join->thd->killed) // Bei Abbruch durch Benutzer
  {
    beitreten->thd->send_kill_message();
    return NESTED_LOOP_KILLED; /* purecov: überprüft */
  }
  wenn (join_tab->use_quick != 2 || test_if_quick_select(join_tab) <= 0)
  {
    wenn (!Datensatz im Cache speichern(&join_tab->Cache))
      return NESTED_LOOP_OK; // Es ist noch Platz im Cache
    gibt flush_cached_records(join,join_tab,FALSE) zurück;
  }
  rc = zwischengespeicherte Datensätze leeren(join, join_tab, TRUE);
  wenn (rc == NESTED_LOOP_OK || rc == NESTED_LOOP_NO_MORE_ROWS)
    rc = Unterauswahl (Join, Join-Tab, Ende der Datensätze);
  rc zurückgeben;
}

In Kombination mit den Anweisungen in der MySQL-Dokumentation ist die Bedeutung des Codes hier ziemlich offensichtlich. Die Beurteilung von end_of_records am Anfang entspricht

    wenn (!Datensatz im Cache speichern(&join_tab->Cache))
      return NESTED_LOOP_OK; // Es ist noch Platz im Cache
    gibt flush_cached_records(join,join_tab,FALSE) zurück;

entsprechen

  - Verwendete Felder aus t1, t2 im Cache speichern
  - Wenn der Cache voll ist

Die Funktion store_record_in_cache ermittelt, ob der Cache voll ist. Wenn der Cache mehr Cache aufnehmen kann, speichert sie die kombinierten Datensätze der vorherigen Tabelle im Cache und gibt NESTED_LOOP_OK zurück. Hinweis: Dieser Ort kann als Schlüssel zur gesamten Cache-Optimierung bezeichnet werden, da der Tabellenscan hier nicht gestartet wird. Wenn der Cache hingegen voll ist, wird die Funktion flush_cached_records aufgerufen, um den folgenden Vorgang auszuführen:

    - Alle Zeilen in t3 durchlesen
      - Vergleichen Sie die T3-Zeile mit allen T1-, T2-Kombinationen im Cache
        - Wenn die Zeile die Join-Bedingung erfüllt, senden Sie sie an den Client
    - Cache leeren

Das Besondere an diesem Prozess besteht darin, dass die Durchquerung durch den Vergleich jedes Datensatzes der Tabelle mit allen Kombinationen von t1 und t2 im Cache gesteuert wird, um zu ermitteln, ob die Pushdown-Where-Bedingung erfüllt ist (wenn die Zeile die Join-Bedingung erfüllt), dann wird die Funktion join_tab->next_select ausgeführt (an den Client gesendet).

statischer enum_nested_loop_state
zwischengespeicherte Datensätze leeren(JOIN *join,JOIN_TAB *join_tab,bool skip_last)
{
…
  info= &join_tab->Datensatz lesen;
  Tun
  {//Alle Datensätze in Tabelle t3 durchlaufen...
        für (i=(join_tab->cache.records- (skip_last ? 1 : 0)) ; i-- > 0 ;)
        {//Alle t1- und t2-Datensatzkombinationen im Cache durchlaufen read_cached_record(join_tab);
          Datensatz überspringen = FALSCH;
          wenn (auswählen und auswählen->Datensatz überspringen(verbinden->thd, undDatensatz überspringen))
          {//
            cache_schreiben(&join_tab->cache);
            gibt NESTED_LOOP_ERROR zurück;
          }
          wenn (! Datensatz überspringen)
          {//Erfüllen Sie die Push-Down-Where-Bedingung//Führen Sie die Durchquerung der nächsten Tabelle aus rc= (join_tab->next_select)(join,join_tab+1,0);
            wenn (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
            {
              cache_schreiben(&join_tab->cache);
              rc zurückgeben;
            }
          }
…
  } während (!(Fehler=info->read_record(info)));

5. Lassen Sie uns diesen Prozess anhand eines Beispiels veranschaulichen

Die Kernidee dieser Implementierung ist nicht kompliziert und in Kombination mit konkreten Beispielen noch einfacher und intuitiver.
Beispielsweise verwenden wir zwei einfache Tabellen, von denen eine einen Wert von x und die andere einen Wert von y speichert. Wir möchten alle Werte in diesen beiden Tabellen, die x erfüllen, durch eine Join-Operation berechnen.

x + y

y == 5 * 5, was der häufigste klassische pythagoräische Zahlenwert ist: „Die Hypothenuse ist drei, die Kathete ist vier, die Seite ist fünf.“

mysql> Tabelle erstellen harry (x int);
Abfrage OK, 0 Zeilen betroffen (0,03 Sek.)
 
mysql> Harry-Werte einfügen (1),(2),(3),(4),(5);
Abfrage OK, 5 Zeilen betroffen (0,00 Sek.)
Datensätze: 5 Duplikate: 0 Warnungen: 0
 
mysql> Tabelle tsecer erstellen (y int);                   
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)
 
mysql> tsecer-Werte einfügen (1),(2),(3),(4),(5);     
Abfrage OK, 5 Zeilen betroffen (0,00 Sek.)
Datensätze: 5 Duplikate: 0 Warnungen: 0
 
mysql> erklären Sie „select * from harry, tsecer“, wobei x * x + y * y = 5 * 5;
+----+----------+--------+------+---------------+-----------+---------+---------+------+--------------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+--------+------+---------------+-----------+---------+---------+------+--------------------------------+
| 1 | EINFACH | Harry | ALLE | NULL | NULL | NULL | NULL | 5 | |
| 1 | SIMPLE | tsecer | ALL | NULL | NULL | NULL | NULL | 5 | Where verwenden; Join-Puffer verwenden |
+----+----------+--------+------+---------------+-----------+---------+---------+------+--------------------------------+
2 Zeilen im Satz (0,00 Sek.)
 
MySQL>

1. Joinbuffer nicht verwenden

Ohne den Verbindungspuffer muss für jeden x-Wert in der Harry-Tabelle die entsprechende Tsecer-Tabelle vollständig gescannt werden. Anschließend wird durch die Kombination von x und y ermittelt, ob x erfüllt ist.

x + y

Die Bedingung y == 5 * 5. Da x insgesamt 5 Werte hat, muss tsecer die gesamte Tabelle 5 Mal scannen.

2. Verwenden Sie Joinbuffer

Für jeden Wert von x speichert die tsecer-Tabelle diesen Wert bei der Ausführung zunächst im Joinbuffer zwischen. Wenn der Pufferinhalt nicht leer ist, wird der Wert von x zu diesem Zeitpunkt im Puffer gespeichert und dann direkt zurückgegeben. Wenn der Joinbuffer voll ist oder es sich um den letzten Datensatz handelt, wird der Scan der tsecer-Tabelle gestartet. Für jeden aus der tsecer-Tabelle gelesenen Datensatz wird er mit jedem zuvor zwischengespeicherten Datensatz kombiniert, um zu sehen, ob er seine eigenen Beurteilungsbedingungen erfüllt.
Für das Beispiel, das wir gesehen haben, befinden sich alle fünf Werte der Harry-Tabelle im Cache. Während des Scannens der Tsecer-Tabelle wird für jeden aus Tsecer gelesenen Datensatz in Kombination mit dem „each“-Cache im Cache ermittelt, ob das kombinierte Ergebnis die Bedingungen erfüllt. Wenn eine beliebige Gruppe die Bedingungen erfüllt, fahren Sie mit next_select fort.
In diesem Beispiel mit Puffer können Sie sehen, dass nur ein Scan für die Tabelle tsecer durchgeführt wird. Im Allgemeinen ist der Datenbank-Scan-Code der höchste (da er Festplatten-Lesevorgänge beinhaltet). Durch die Verwendung von Puffer wird der Scan der Tabelle tsecer auf einen reduziert, sodass die Effizienz erheblich verbessert wird, insbesondere wenn mehrere Tabellen beteiligt sind und/oder die Anzahl der Datensätze in jeder Tabelle groß ist.

3. Gründe, warum der Cache optimiert werden kann

Der Grund für diese Effizienzsteigerung liegt im Wesentlichen darin, dass die „Auslastungsrate“ jedes aus der Tabelle erhaltenen Datensatzes verbessert wird. Bei Verwendung der intuitiven Scanmethode stimmt der vollständige Tabellenscan nur mit einer Kombination überein, während er nach Verwendung des Puffers mit allen Kombinationen im Cache übereinstimmt.

Oben finden Sie den detaillierten Inhalt des MySQL-Joinpufferprinzips. Weitere Informationen zum MySQL-Joinpuffer finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!