Spezifische Verwendung interner temporärer MySQL-Tabellen

UNION

UNION-Semantik: Nimm die Vereinigung der beiden Unterabfrageergebnisse und behalte nur eine Zeile mit doppelten Zeilen bei.

Tabelleninitialisierung

TABELLE ERSTELLEN t1(id INT PRIMARY KEY, a INT, b INT, INDEX(a));
TRENNUNGSZEICHEN ;;
PROZEDUR ERSTELLEN idata()
BEGINNEN
    Erklären Sie, dass ich INT;

    Setze i = 1;
    WHILE (i<= 1000) DO
        IN t1 WERTE EINFÜGEN (i,i,i);
        Setze i = i + 1;
    ENDE WÄHREND;
ENDE;;
TRENNUNGSZEICHEN ;
Rufen Sie idata() auf;

Execute-Anweisung

(SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);

mysql> ERKLÄREN (SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);
+----+--------------+------------+------------+-------+---------------+--------+---------+---------+------+------+------+----------+----------+----------------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+--------------+------------+------------+-------+---------------+--------+---------+---------+------+------+------+----------+----------+----------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Keine Tabellen verwendet |
| 2 | UNION | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Rückwärts-Indexscan; Index wird verwendet |
| NULL | UNION-ERGEBNIS | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Temporäre | verwenden |
+----+--------------+------------+------------+-------+---------------+--------+---------+---------+------+------+------+----------+----------+----------------------------------+

Die zweite Zeile hat Key=PRIMARY , Using temporary

• Gibt an, dass eine temporäre Tabelle verwendet wird, wenn UNION RESULT auf die Ergebnisse der Unterabfrage angewendet wird.

UNION-ERGEBNIS

• Erstellen Sie eine temporäre Speichertabelle. Diese temporäre Speichertabelle hat nur ein ganzzahliges Feld f und f ist der Primärschlüssel.
• Führen Sie die erste Unterabfrage aus, erhalten Sie 1000 und speichern Sie sie in der temporären Tabelle im Speicher
• Führen Sie die zweite Unterabfrage aus
  • Holen Sie sich die erste Zeile mit der ID=1000 und versuchen Sie, sie in die temporäre In-Memory-Tabelle einzufügen, aber der Wert 1000 ist in der temporären In-Memory-Tabelle bereits vorhanden.
    • Unique-Einschränkung verletzt, Einfügen fehlgeschlagen, Ausführung fortsetzen
  • Holen Sie sich die zweite Zeile mit der ID 999 und fügen Sie sie erfolgreich in die temporäre Speichertabelle ein.
• Daten zeilenweise aus der temporären Tabelle im Speicher abrufen, das Ergebnis zurückgeben und die temporäre Tabelle im Speicher löschen. Das Ergebnis enthält zwei Zeilen mit der ID=1000 und der ID=999.
• Die temporäre Speichertabelle dient zum temporären Speichern von Daten. Außerdem verwendet sie die Eindeutigkeitsbeschränkung der Primärschlüssel-ID der temporären Speichertabelle, um die Semantik von UNION zu implementieren.

UNION ALLE

UNION ALL hat keine Deduplizierungssemantik. Die Unterabfrage wird einmal ausgeführt und die Ergebnisse werden direkt an den Client gesendet, ohne dass eine temporäre Tabelle im Speicher erforderlich ist.

mysql> ERKLÄREN (SELECT 1000 AS f) UNION ALL (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);
+----+----------+----------+---------+-----------+---------------+--------+---------+---------+------+------+------+----------+----------------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+-----------+---------------+--------+---------+---------+------+------+------+----------+----------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Keine Tabellen verwendet |
| 2 | UNION | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Rückwärts-Indexscan; Index wird verwendet |
+----+----------+----------+---------+-----------+---------------+--------+---------+---------+------+------+------+----------+----------------------------------+

GRUPPELN NACH

Ausreichend Speicher

-- 16777216 Bytes = 16 MB
mysql> VARIABLEN ANZEIGEN wie '%tmp_table_size%';
+-----------------+----------+
| Variablenname | Wert |
+-----------------+----------+
| tmp_tabellengröße | 16777216 |
+-----------------+----------+

Execute-Anweisung

-- Ausführen auf MySQL 5.6mysql> EXPLAIN SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
+----+-----------+-------+-----------+---------------+------+---------+---------+------+------+---------+----------------------------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+-----------+-------+-----------+---------------+------+---------+---------+------+------+---------+----------------------------------------------+
| 1 | SIMPLE | t1 | index | PRIMARY,a | a | 5 | NULL | 1000 | Index wird verwendet; Temporär wird verwendet; Filesort wird verwendet |
+----+-----------+-------+-----------+---------------+------+---------+---------+------+------+---------+----------------------------------------------+

mysql> SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
+------+-----+
| m | c |
+------+-----+
| 0 | 100 |
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 100 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
+------+-----+

Using index : Gibt an, dass ein überdeckender Index verwendet wird, Index a ausgewählt ist und keine Tabellenrückgabe erforderlich ist.

Temporär verwenden: Gibt an, dass eine Using temporary

Using filesort : Gibt an, dass eine Sortierung erforderlich ist.

Ausführungsprozess

• Erstellen Sie eine temporäre Speichertabelle mit zwei Feldern m und c, m ist der Primärschlüssel
• Scannen Sie den Index a von t1, nehmen Sie die ID-Werte auf den Blattknoten nacheinander heraus, berechnen Sie id%10 und zeichnen Sie es als x auf
  • Wenn in der temporären Speichertabelle keine Zeile mit dem Primärschlüssel x vorhanden ist, fügen Sie eine Zeile (x,1) ein.
  • Wenn in der temporären Speichertabelle eine Zeile mit dem Primärschlüssel x vorhanden ist, addieren Sie 1 zum c-Wert der Zeile x.
• Nachdem die Durchquerung abgeschlossen ist, sortieren Sie nach Feld m, rufen Sie den Ergebnisset ab und geben Sie ihn an den Client zurück.

Sortierprozess

ORDER BY NULL

-- Überspringen Sie den letzten Sortierschritt und rufen Sie die Daten direkt aus der temporären Tabelle mysql> EXPLAIN SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL; ab.
+----+-----------+-------+-----------+---------------+------+---------+---------+------+---------+---------+------------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+-----------+-------+-----------+---------------+------+---------+---------+------+---------+---------+------------------------------+
| 1 | SIMPLE | t1 | index | PRIMARY,a | a | 5 | NULL | 1000 | Index wird verwendet; Temporär wird verwendet |
+----+-----------+-------+-----------+---------------+------+---------+---------+------+---------+---------+------------------------------+

-- Die Daten in t1 beginnen bei 1 mysql> SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL;
+------+-----+
| m | c |
+------+-----+
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 100 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
| 0 | 100 |
+------+-----+

Nicht genügend Arbeitsspeicher

Setzen Sie die temporäre Tabellengröße auf 1024.

Execute-Anweisung

-- Die Obergrenze der temporären Speichertabelle beträgt 1024 Bytes, aber die temporäre Speichertabelle kann nicht vollständig 100 Datenzeilen aufnehmen. Die temporäre Speichertabelle wird in eine temporäre Festplattentabelle konvertiert und standardmäßig wird die InnoDB-Engine verwendet. -- Wenn t1 sehr groß ist, nimmt die für diese Abfrage erforderliche temporäre Festplattentabelle viel Speicherplatz in Anspruch mysql> SELECT id%100 AS m, count(*) AS c FROM t1 GROUP BY m ORDER BY NULL LIMIT 10;
+------+----+
| m | c |
+------+----+
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 4 | 10 |
| 5 | 10 |
| 6 | 10 |
| 7 | 10 |
| 8 | 10 |
| 9 | 10 |
| 10 | 10 |
+------+----+

Optimierungsplan

Indizes optimieren

Unabhängig davon, ob eine temporäre Tabelle im Speicher oder auf der Festplatte verwendet wird, erfordert GROUP BY die Erstellung einer Tabelle mit einem eindeutigen Index, was in der Ausführung teurer ist.

Der Grund für die Notwendigkeit einer temporären Tabelle: id%100 jeder Zeile ist nicht in der richtigen Reihenfolge, daher wird eine temporäre Tabelle zum Aufzeichnen und Zählen der Ergebnisse benötigt.

Wenn Sie sicherstellen können, dass die Eingabedaten in der richtigen Reihenfolge sind, müssen Sie beim Berechnen GROUP BY nur von links nach rechts scannen und sie einzeln ansammeln.

• Wenn die erste 1 gefunden wird, wurden X Nullen angesammelt und die erste Zeile im Ergebnissatz (0,X)
• Wenn die erste 2 gefunden wird, wurden Y 1en angesammelt und die erste Zeile im Ergebnissatz (1,Y)
• Für den gesamten Vorgang ist keine temporäre Tabelle oder Sortierung erforderlich.

-- Führen Sie ALTER TABLE t1 ADD COLUMN z INT GENERATED ALWAYS AS(id % 100), ADD INDEX(z) unter MySQL 5.7 aus;
-- Es wird ein abdeckender Index verwendet, es ist keine temporäre Tabelle oder Sortierung erforderlichmysql> EXPLAIN SELECT z, COUNT(*) AS c FROM t1 GROUP BY z;
+----+----------+----------+---------+-------+---------------+-----------+---------+---------+------+---------+---------+----------+----------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+-------+---------------+-----------+---------+---------+------+---------+---------+----------+----------+
| 1 | SIMPLE | t1 | NULL | Index | z | z | 5 | NULL | 1000 | 100,00 | Index wird verwendet |
+----+----------+----------+---------+-------+---------------+-----------+---------+---------+------+---------+---------+----------+----------+
2

Direkte Sortierung

Wenn eine GROUP BY -Anweisung eine große Datenmenge in einer temporären Tabelle speichern muss, ist es besser, sie zuerst in einer temporären Tabelle im Speicher zu speichern und sie dann in eine temporäre Tabelle auf der Festplatte zu degenerieren.

Sie können direkt eine temporäre Datenträgertabelle verwenden und SQL_BIG_RESULT in GROUP BY -Anweisung verwenden (um dem Optimierer mitzuteilen, dass es sich um eine sehr große Datenmenge handelt).

Die temporäre Datenträgertabelle verwendet ursprünglich B+-Baumspeicher, der nicht so effizient ist wie Array-Speicher. Wenn der Optimierer SQL_BIG_RESULT sieht, verwendet er direkt Array-Speicher.

• Das heißt, verzichten Sie auf die Verwendung der temporären Tabelle und gehen Sie direkt zur Sortierphase über

Ausführungsprozess

-- Es wird keine temporäre Tabelle verwendet, sondern der Sortieralgorithmus wird direkt verwendet mysql> EXPLAIN SELECT SQL_BIG_RESULT id%100 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
+----+-----------+----------+-----------+---------------+------+---------+---------+------+---------+---------+-----------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+-----------+----------+-----------+---------------+------+---------+---------+------+---------+---------+-----------------------------+
| 1 | SIMPLE | t1 | index | PRIMARY,a | a | 5 | NULL | 1000 | Index wird verwendet; Filesort wird verwendet |
+----+-----------+----------+-----------+---------------+------+---------+---------+------+---------+---------+-----------------------------+

Initialisieren Sie sort_buffer und fügen Sie ein Integer-Feld darin ein, bezeichnet als m

Scannen Sie den Index a von t1, nehmen Sie die darin enthaltenen ID-Werte nacheinander heraus und fügen Sie den Wert von id%100 in sort_buffer ein

Sortieren Sie nach Abschluss des Scanvorgangs das Feld m des sort_buffer (wenn der Speicher des Sortierpuffers nicht ausreicht, werden temporäre Dateien auf der Festplatte zum Sortieren verwendet).

Nach dem Sortieren erhalten wir ein geordnetes Array. Wir durchlaufen das geordnete Array, um die Häufigkeit zu ermitteln, mit der jeder Wert vorkommt (ähnlich der Methode zur Indexoptimierung oben).

Vergleich mit DISTINCT

-- Standard-SQL, fügen Sie eine Aggregatfunktion COUNT(*) im SELECT-Teil hinzu
Wählen Sie a, COUNT(*) aus t GROUP BY a ORDER BY NULL;
-- Nicht standardmäßiges SQL
WÄHLEN Sie ein AUS t GRUPPE NACH einem ORDER BY NULL;

WÄHLEN SIE DISTINCT a FROM t;

Standard-SQL: Gruppieren nach Feld a und Zählen, wie oft a in jeder Gruppe vorkommt

Nicht-Standard-SQL: Kein COUNT(*) mehr und keine Notwendigkeit, die Gesamtberechnungslogik auszuführen

• Gruppieren Sie nach Feld a und geben Sie nur eine Zeile mit demselben Wert von a zurück, was mit DISTINCT Semantik übereinstimmt

Wenn keine Aggregatfunktion erforderlich ist, sind Semantik, Ausführungsfluss und Leistung von DISTINCT und GROUP BY identisch.

• Erstellen Sie eine temporäre Tabelle mit einem Feld a und erstellen Sie einen eindeutigen Index für dieses Feld a
• Durchlaufen Sie die Tabelle t, entnehmen Sie die Daten einzeln und fügen Sie sie in die temporäre Tabelle ein
  • Wenn ein eindeutiger Schlüsselkonflikt gefunden wird, überspringen Sie ihn
  • Andernfalls ist das Einfügen erfolgreich
• Nach Abschluss der Durchquerung wird die temporäre Tabelle als Ergebnismenge an den Client zurückgegeben.

Zusammenfassung

• Szenarien, in denen interne temporäre Tabellen verwendet werden
  • Wenn die Anweisung während der Ausführung gleichzeitig Daten lesen und Ergebnisse abrufen kann, ist kein zusätzlicher Speicher erforderlich.
  • Andernfalls wird zusätzlicher Speicher benötigt, um Zwischenergebnisse zu speichern.
• join_buffer ist ein ungeordnetes Array, sort_buffer ist ein geordnetes Array und die temporäre Tabelle ist eine zweidimensionale Tabellenstruktur
• Wenn die Ausführungslogik die Verwendung zweidimensionaler Tabellenfunktionen erfordert, sind temporäre Tabellen vorzuziehen. Wenn für die Ergebnisse GROUP BY Anweisung keine explizite Sortieranforderung besteht, fügen Sie ORDER BY NULL hinzu (MySQL 5.6).
• Versuchen Sie, Indizes im GROUP BY -Prozess zu verwenden, und stellen Sie sicher, dass die EXPLAIN-Ergebnisse weder Using temporary noch Using filesort
• Wenn die in GROUP BY zu zählende Datenmenge nicht groß ist, versuchen Sie, eine temporäre Speichertabelle zu verwenden (Sie können tmp_table_size entsprechend erhöhen).
• Wenn die Datenmenge zu groß ist, verwenden Sie SQL_BIG_RESULT , um dem Optimierer mitzuteilen, dass er den Sortieralgorithmus direkt verwenden soll (und die temporäre Tabelle überspringen soll).

Verweise

„MySQL Praxis 45 Vorlesungen“

Dies ist das Ende dieses Artikels über die spezifische Verwendung interner temporärer MySQL-Tabellen. Weitere relevante interne temporäre MySQL-Tabellen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!