Detaillierte Erklärung der Verwendung von zwei Arten von temporären Tabellen in MySQL

Externe temporäre Tabellen

Eine mit CREATE TEMPORARY TABLE erstellte temporäre Tabelle wird als externe temporäre Tabelle bezeichnet. Diese temporäre Tabelle ist nur für den aktuellen Benutzer sichtbar und wird automatisch geschlossen, wenn die aktuelle Sitzung endet. Diese temporäre Tabelle kann denselben Namen wie eine nicht temporäre Tabelle haben (die nicht temporäre Tabelle ist für die aktuelle Sitzung nicht sichtbar, bis die temporäre Tabelle gelöscht wird).

Interne temporäre Tabelle

Eine interne temporäre Tabelle ist eine spezielle leichte temporäre Tabelle, die zur Leistungsoptimierung verwendet wird. Diese temporäre Tabelle wird automatisch von MySQL erstellt und dient zum Speichern von Zwischenergebnissen bestimmter Vorgänge. Diese Vorgänge können in die Optimierungsphase oder in die Ausführungsphase einbezogen werden. Diese interne Tabelle ist für den Benutzer nicht sichtbar, aber Sie können mit EXPLAIN oder SHOW STATUS überprüfen, ob MYSQL zum Abschließen eines Vorgangs eine interne temporäre Tabelle verwendet. Interne temporäre Tabellen spielen im Optimierungsprozess von SQL-Anweisungen eine sehr wichtige Rolle. Viele Operationen in MySQL sind zur Optimierung auf interne temporäre Tabellen angewiesen. Die Verwendung interner temporärer Tabellen erfordert jedoch das Erstellen von Tabellen sowie das Speichern und Ersetzen von Zwischendaten. Daher sollten Benutzer beim Schreiben von SQL-Anweisungen versuchen, die Verwendung temporärer Tabellen zu vermeiden.

Es gibt zwei Arten interner temporärer Tabellen: Eine davon ist die HEAP-Temporärtabelle, in der alle Daten im Speicher abgelegt sind und für Operationen an dieser Tabelle kein IO-Vorgang erforderlich ist. Die andere ist die temporäre OnDisk-Tabelle. Wie der Name schon sagt, speichert diese temporäre Tabelle Daten auf der Festplatte. Zur Verarbeitung von Vorgängen mit großen Zwischenergebnissen werden temporäre OnDisk-Tabellen verwendet. Wenn die in der temporären HEAP-Tabelle gespeicherten Daten größer als MAX_HEAP_TABLE_SIZE sind (Einzelheiten finden Sie im Abschnitt zu Systemvariablen im MySQL-Handbuch), wird die temporäre HEAP-Tabelle automatisch in eine temporäre OnDisk-Tabelle konvertiert. In 5.7 können temporäre OnDisk-Tabellen die MyISAM-Engine oder die InnoDB-Engine über die Systemvariable INTERNAL_TMP_DISK_STORAGE_ENGINE verwenden.

Dieser Artikel stellt hauptsächlich vor, bei welchen Vorgängen interne temporäre Tabellen verwendet werden können. Wenn Benutzer beim Schreiben von SQL-Anweisungen interne temporäre Tabellen so wenig wie möglich zur Abfrageoptimierung verwenden können, wird die Effizienz der Abfrageausführung effektiv verbessert.

Zuerst definieren wir eine Tabelle t1.

TABELLE ERSTELLEN t1( a int, b int); IN t1 EINFÜGEN WERTE(1,2),(3,4);

Alle folgenden Vorgänge basieren auf Tabelle t1.

Verwenden des SQL_BUFFER_RESULT-Hinweises in SQL-Anweisungen

SQL_BUFFER_RESULT wird hauptsächlich verwendet, um MySQL zu ermöglichen, die Sperre der Tabelle so früh wie möglich freizugeben. Denn wenn die Datenmenge groß ist, dauert das Senden der Daten an den Client lange. Durch das Puffern der Daten in einer temporären Tabelle kann die Zeit, in der die Lesesperre die Tabelle belegt, effektiv reduziert werden.

Zum Beispiel:

mysql> erläutern Sie das Format = json, wählen Sie SQL_BUFFER_RESULT * aus t1; 
ERKLÄREN 
{ 
 "Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "query_cost": "2,00" 
}, 
"Pufferergebnis": { 
 "using_temporary_table": wahr, 
 "Tisch": { 
"Tabellenname": "t1", 
"Zugriffstyp": "ALLE", 
...

Wenn die SQL-Anweisung DERIVED_TABLE enthält.

In 5.7 müssen wir aufgrund der Einführung einer neuen Optimierungsmethode „optimizer_switch='derived_merge=off'“ verwenden, um zu verhindern, dass die abgeleitete Tabelle in die äußere Abfrage integriert wird.

Zum Beispiel:

mysql> Erläuterung des Formats = json Wählen Sie * aus (Wählen Sie * aus t1) als tt; 
ERKLÄREN 
{ 
 "Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "query_cost": "2,40" 
}, 
"Tisch": { 
 "Tabellenname": "tt", 
 "Zugriffstyp": "ALLE", 
 ... 
 "materialisiert_aus_Unterabfrage": { 
"using_temporary_table": wahr, 
...

Wenn wir die Systemtabelle abfragen, werden die Daten in der Systemtabelle in einer internen temporären Tabelle gespeichert.

Mit EXPLAIN können wir derzeit nicht prüfen, ob zum Lesen der Systemtabellendaten interne temporäre Tabellen erforderlich sind. Mit SHOW STATUS können wir jedoch prüfen, ob interne temporäre Tabellen verwendet werden.

Zum Beispiel:

mysql> wähle * aus information_schema.character_sets; 
mysql> Status wie „CREATE%“ anzeigen;

Wenn die DISTINCT-Anweisung nicht wegoptimiert wird, das heißt, die DISTINCT-Anweisung optimiert und in eine GROUP BY-Operation umgewandelt wird oder DISTINCT mithilfe eines UNIQUE INDEX eliminiert wird, wird eine interne temporäre Tabelle verwendet.

mysql> erläutern Sie das Format = json, wählen Sie „distinct a“ aus „t1“; 
ERKLÄREN 
{ 
{ 
 "Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "Abfragekosten": "1,60" 
}, 
"Duplikate_entfernen": { 
 "using_temporary_table": wahr, 
...

Wenn die Abfrage eine ORDER BY-Klausel hat und nicht durch Optimierung entfernt werden kann. In den folgenden Fällen werden interne temporäre Tabellen verwendet, um Zwischendaten zwischenzuspeichern und dann die Zwischendaten zu sortieren.

1) Wenn die Verbindungstabelle BNL (Batched Nestloop)/BKA (Batched Key Access) verwendet

Zum Beispiel:

1))BNL ist standardmäßig aktiviert

mysql> Erläuterungsformat = json, Auswahl * von t1, t1 als t2, sortiert nach t1.a; 
ERKLÄREN 
{ 
 "Abfrageblock": { 
 "select_id": 1, 
 "Kosteninfo": { 
"query_cost": "22,00" 
 }, 
 "Bestellvorgang": { 
"using_temporary_table": wahr, 
 ...

2)) Nach dem Ausschalten von BNL verwendet ORDER BY direkt die Dateisortierung.

mysql> setze optimizer_switch='block_nested_loop=off'; 
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.) 
mysql> Erläuterungsformat = json, Auswahl * von t1, t1 als t2, sortiert nach t1.a; 
ERKLÄREN 
{ 
  "Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "query_cost": "25,00" 
}, 
"Bestellvorgang": { 
 "using_filesort": wahr, 
...

2) Die ORDER BY-Spalten gehören nicht zu den Spalten der ersten verknüpften Tabelle im Ausführungsplan.

Zum Beispiel:

mysql> Erläuterungsformat = json, Auswahl * von t als t1, t als t2, sortiert nach t2.a; 
ERKLÄREN 
{ 
  "Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "query_cost": "25,00" 
}, 
"Bestellvorgang": { 
 "using_temporary_table": wahr, 
...

3) Wenn der ORDER BY-Ausdruck ein komplexer Ausdruck ist.

Welche Art von ORDER BY-Ausdruck betrachtet MySQL als komplexen Ausdruck?

1)) Wenn der Sortierausdruck ein SP oder UDF ist.

Zum Beispiel:

Funktion löschen, falls vorhanden, func1; 
Trennzeichen | 
Funktion func1(x int) erstellen 
gibt int deterministisch zurück 
beginnen 
Deklarieren Sie z1, z2 int; 
setze z1 = x; 
setze z2 = z1+2; 
Rückgabe z2; 
Ende| 
Trennzeichen ; 
erläutern Sie Format = JSON, wählen Sie * aus t1, um nach func1 (a) zu sortieren; 
{ 
"Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "Abfragekosten": "2,20" 
}, 
"Bestellvorgang": { 
 "using_temporary_table": wahr, 
...

2)) Die Spalte ORDER BY enthält eine Aggregatfunktion

Um den Ausführungsplan zu vereinfachen, verwenden wir INDEX, um die GROUP BY-Anweisung zu optimieren.

Zum Beispiel:

 Erstelle den Index idx1 auf t1(a); 
 Erläutern Sie das Format = json. Wählen Sie eine FROM t1-Gruppe nach einer Reihenfolge nach Summe (a) aus. 
 | { 
  "Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "Abfragekosten": "1,20" 
}, 
"Bestellvorgang": { 
 "using_temporary_table": wahr, 
 "using_filesort": wahr, 
 "Gruppierungsvorgang": { 
"using_filesort": falsch, 
... 
 Index idx1 bei t1 löschen;

3)) Die Spalte ORDER BY enthält eine SCALAR SUBQUERY, aber die SCALAR SUBQUERY ist nicht optimiert.

Zum Beispiel:

erläutern Sie format=json select (select rand() from t1 limit 1) als a from t1 order by a; 
| { 
 "Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "Abfragekosten": "1,20" 
}, 
"Bestellvorgang": { 
 "using_temporary_table": wahr, 
 "using_filesort": wahr, 
...

4) Wenn die Abfrage sowohl ORDER BY- als auch GROUP BY-Anweisungen enthält, die in den beiden Anweisungen verwendeten Spalten jedoch unterschiedlich sind.

Hinweis: Wenn es 5.7 ist, müssen wir den SQL-Modus auf einen anderen Modus als „only_full_group_by“ setzen, andernfalls wird ein Fehler gemeldet.

Um den Ausführungsplan zu vereinfachen, verwenden wir INDEX, um die GROUP BY-Anweisung zu optimieren.

Zum Beispiel:

setze sql_mode=''; 
Erstelle den Index idx1 auf t1(b); 
Erläuterung: Format = JSON, wähle t1.a aus t1, gruppiere nach t1.b, sortiere nach 1; 
| { 
"Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "Abfragekosten": "1,40" 
}, 
"Bestellvorgang": { 
 "using_temporary_table": wahr, 
 "using_filesort": wahr, 
 "Gruppierungsvorgang": { 
"using_filesort": falsch, 
... 
Index idx1 bei t1 löschen;

Wenn die Abfrage eine GROUP BY-Klausel hat und nicht durch Optimierung entfernt werden kann. In den folgenden Fällen werden interne temporäre Tabellen zum Zwischenspeichern von Zwischendaten verwendet und anschließend GROUP BY auf den Zwischendaten ausgeführt.

1) Wenn die Verbindungstabelle BNL (Batched Nestloop)/BKA (Batched Key Access) verwendet.

Zum Beispiel:

Erläuterung: Format = JSON: Wähle t2.a aus t1, t1 als t2, gruppiere nach t1.a; 
| { 
"Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "Abfragekosten": "8,20" 
}, 
"Gruppierungsvorgang": { 
 "using_temporary_table": wahr, 
 "using_filesort": wahr, 
 "Kosteninfo": { 
"sort_cost": "4,00" 
...

2) Wenn die Spalte GROUP BY nicht zur ersten verknüpften Tabelle im Ausführungsplan gehört.

Zum Beispiel:

Erläuterung: Format = JSON. Wähle t2.a aus t1, t1 als t2, gruppiere nach t2.a. 
| { 
"Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "Abfragekosten": "8,20" 
}, 
"Gruppierungsvorgang": { 
 "using_temporary_table": wahr, 
 "using_filesort": wahr, 
 "verschachtelte_Schleife": [ 
...

3) Wenn sich die in der GROUP BY-Anweisung verwendeten Spalten von den in der ORDER BY-Anweisung verwendeten Spalten unterscheiden.

Zum Beispiel:

setze sql_mode=''; 
Erläuterung: Format = JSON. Wähle t1.a aus t1, gruppiere nach t1.b, sortiere nach t1.a. 
| { 
  "Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "Abfragekosten": "1,40" 
}, 
"Bestellvorgang": { 
 "using_filesort": wahr, 
 "Gruppierungsvorgang": { 
"using_temporary_table": wahr, 
"using_filesort": falsch, 
...

4) Wenn GROUP BY ROLLUP enthält und auf Multi-Table-Outer-Joins basiert.

Zum Beispiel:

Erläuterung: Format = JSON, wähle Summe (t1.a) von t1 links, verbinde t1 als t2 auf True, gruppiere nach t1.a mit Rollup; 
| { 
"Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "Abfragekosten": "7,20" 
}, 
"Gruppierungsvorgang": { 
 "using_temporary_table": wahr, 
 "using_filesort": wahr, 
 "Kosteninfo": { 
"sort_cost": "4,00" 
 }, 
...

5) Wenn die in der GROUP BY-Anweisung verwendeten Spalten aus SCALAR SUBQUERY stammen und nicht durch Optimierung entfernt wurden.

Zum Beispiel:

erläutern Sie Format=JSON, wählen Sie (select avg(a) from t1) als eine aus t1-Gruppe nach a; 
| { 
"Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "query_cost": "3,40" 
}, 
"Gruppierungsvorgang": { 
 "using_temporary_table": wahr, 
 "using_filesort": wahr, 
 "Kosteninfo": { 
"sort_cost": "2,00" 
 }, 
... 

Konvertieren Sie IN-Ausdrücke zur Optimierung in Semi-Joins

1) Wenn der Semi-Join-Ausführungsmodus Materialisierung ist

Zum Beispiel:

Setzen Sie optimizer_switch='firstmatch=off,duplicateweedout=off'. 
Erläuterung: Format = JSON. Wählen Sie * aus t1, wobei a in (wählen Sie b aus t1 aus); 
| { 
"Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "query_cost": "5,60" 
}, 
"verschachtelte_Schleife": [ 
 { 
"pro Scan untersuchte Zeilen": 1, 
 "materialisiert_aus_Unterabfrage": { 
"using_temporary_table": wahr, 
"Abfrageblock": { 
 "Tisch": { 
"Tabellenname": "t1", 
"Zugriffstyp": "ALLE", 
... 

2) Wenn der Semi-Join-Ausführungsmodus Duplicate Weedout ist

Zum Beispiel:

Setzen Sie optimizer_switch='firstmatch=off'. 
Erläuterung: Format = JSON. Wählen Sie * aus t1, wobei a in (wählen Sie b aus t1 aus); 
| { 
"Abfrageblock": { 
"select_id": 1, 
"Kosteninfo": { 
 "query_cost": "4,80" 
}, 
"Duplikate_entfernen": { 
 "using_temporary_table": wahr, 
 "verschachtelte_Schleife": [ 
{ 
...

Wenn die Abfrageanweisung UNION enthält, verwendet MySQL eine interne temporäre Tabelle, um der UNION-Operation dabei zu helfen, Duplikate zu eliminieren.

Zum Beispiel:

erläutern Sie Format=JSON, wählen Sie * von t1, Union, wählen Sie * von t1; 
| { 
"Abfrageblock": { 
"union_result": { 
 "using_temporary_table": wahr, 
 "Tabellenname": "", 
... 

Wenn die Abfrageanweisung mehrere Tabellenaktualisierungen verwendet.

Hier kann Explain nicht anzeigen, dass die interne temporäre Tabelle verwendet wird, daher müssen Sie den Status überprüfen.

Zum Beispiel:

Aktualisiere t1, t1 als t2, setze t1.a=3; 
Status wie „CREATE%“ anzeigen; 

Wenn die Aggregatfunktion die folgenden Funktionen enthält, wird auch die interne temporäre Tabelle verwendet.

1) Anzahl (unterschiedliche *) 
Zum Beispiel: 
Erläuterung: Format = JSON, wähle Anzahl (unterschiedliches A) aus t1; 
2) group_concat 

Zum Beispiel:

erläutern Sie Format=JSON, wählen Sie group_concat(b) aus t1; 

Zusammenfassend lässt sich sagen, dass MySQL in den oben aufgeführten 10 Situationen interne temporäre Tabellen zum Zwischenspeichern von Zwischenergebnissen verwendet. Wenn die Datenmenge groß ist, speichert die interne temporäre Tabelle die Daten auf der Festplatte, was offensichtlich die Leistung beeinträchtigt. Um den Leistungsverlust zu minimieren, müssen wir die oben genannte Situation so weit wie möglich vermeiden.

Zusammenfassen

Dies ist der gesamte Inhalt dieses Artikels zur detaillierten Verwendung von zwei temporären MySQL-Tabellen. Ich hoffe, er wird für alle hilfreich sein. Interessierte Freunde können sich auf Folgendes beziehen: Mehrere wichtige MySQL-Variablen, detaillierte Erklärung der MySQL-Vorbereitungsprinzipien, So löschen Sie MySQL-Tabellendaten usw. Wenn Sie Fragen haben, hinterlassen Sie bitte eine Nachricht. Jeder ist herzlich eingeladen, zu kommunizieren und zu diskutieren.