Wozu dient die temporäre MySQL-Tabelle?

Die Rolle der temporären MySQL-Tabelle: 1. Von Benutzern selbst erstellte temporäre Tabellen werden zum Speichern temporärer Daten verwendet. 2. Wenn Benutzer komplexes SQL ausführen, können sie temporäre Tabellen zum Durchführen von Gruppierungen, Sortierungen, Deduplizierungen und anderen Vorgängen verwenden Standardmäßig wird die temporäre Tabelle automatisch zerstört, wenn Sie die Verbindung zur Datenbank trennen.

Die Betriebsumgebung dieses Tutorials: Windows 10-System, MySQL Version 5.7, Dell G3-Computer.

Wozu dienen temporäre MySQL-Tabellen?

Die Rolle temporärer MySQL-Tabellen

Temporäre MySQL-Tabellen werden in vielen Szenarien verwendet, z. B. als temporäre Tabellen, die von Benutzern selbst erstellt wurden, um temporäre Daten zu speichern. und bei der internen Ausführung von komplexem SQL muss MySQL temporäre Tabellen verwenden, um Vorgänge wie Gruppierung, Sortierung und Deduplizierung durchzuführen. Im Folgenden werden einige Konzepte, Klassifizierungen und häufige Probleme temporärer MySQL-Tabellen erläutert.

Temporärer MySQL-Tabellentyp

1. Externe temporäre Tabelle, eine temporäre Tabelle, die über die Syntax zum Erstellen temporärer Tabellen erstellt wird. Sie können die Speicher-Engine als Speicher, Innodb, Myisam usw. angeben. Dieser Tabellentyp wird sein nach Beendigung der Sitzung gelöscht werden. Wenn eine temporäre Tabelle gleichzeitig mit einer nicht temporären Tabelle vorhanden ist, ist die nicht temporäre Tabelle nicht sichtbar. Der Befehl „show tables“ zeigt keine temporären Tabelleninformationen an.
Sie können relevante Informationen zu externen temporären Tabellen über die Systemtabelle information_schema.INNODB_TEMP_TABLE_INFO anzeigen. Dieser Teil wird noch relativ selten verwendet.

2. Interne temporäre Tabellen werden normalerweise verwendet, wenn komplexes SQL ausgeführt wird, z. B. Gruppieren nach, Sortieren nach, Unterscheiden, Vereinigen usw. Wenn der Ausführungsplan die Verwendung temporärer Tabellen enthält, gibt es auch ein Rückgängigmachen eines Rollbacks, wenn dies jedoch nicht ausreicht Speicherplatz verwendet MySQL intern automatisch generierte temporäre Tabellen, um die Arbeit abzuschließen.

MySQL-Parameter für temporäre Tabellen

1.max_heap_table_size: Der Maximalwert der vom Benutzer erstellten Speichertabelle. Wird auch zusammen mit tmp_table_size verwendet, um die Größe der internen temporären Tabelle im Speicher zu begrenzen.
2.tmp_table_size: Der Maximalwert der internen temporären Tabelle im Speicher wird zusammen mit dem Parameter max_heap_table_size bestimmt und der Minimalwert von beiden wird verwendet. Wenn die temporäre Tabelle diesen Wert überschreitet, wird sie vom Speicher auf die Festplatte verschoben.
3.innodb_tmpdir: Online-ALTER TABLE-Operationen, die die Tabelle max_tmp_tables neu erstellen

4.default_tmp_storage_engine: Die Standardspeicher-Engine für externe temporäre Tabellen (Tabellen, die durch „Create Temporary Table“ erstellt wurden).

5.innodb_temp_data_file_path: Attribut der temporären Datei unter der Innodb-Engine. Es wird empfohlen, innodb_temp_data_file_path = ibtmp1:1G:autoextend:max:30G

6 zu begrenzen.Internal_tmp_disk_storage_engine: Die interne temporäre Tabellenspeicher-Engine auf der Festplatte, der optionale Wert ist myisam oder innodb. Bei Verwendung einer Innodb-Tabelle kann in einigen Szenarien, wenn die temporäre Tabelle beispielsweise zu viele Spalten enthält oder die Zeilengröße den Grenzwert überschreitet, der Fehler „Zeilengröße zu groß oder zu viele Spalten“ auftreten Die Innodb-Engine der temporären Tabelle sollte wieder in Myisam geändert werden. tmpdir: Temporäres Tabellenverzeichnis. Wenn die Größe der temporären Tabelle einen bestimmten Schwellenwert überschreitet, wird sie vom Speicher auf die Festplatte übertragen. 7. Die Variable tmpdir stellt das Verzeichnis dar, in dem sich die temporäre Tabelle auf der Festplatte befindet.

MySQL temporäre Tabellenbezogene Statusvariablen

1.Created_tmp_disk_tables: Bei der Ausführung einer SQL-Anweisung die Anzahl der von MySQL auf der Festplatte erstellten internen temporären Tabellen. Wenn dieser Wert groß ist, liegt der mögliche Grund darin, dass der maximale Speicherwert erreicht ist Die der temporären Tabelle zugewiesene Größe ist relativ klein, oder es gibt eine große Anzahl von Sortier-, Gruppierungs-, Deduplizierungs- und anderen Vorgängen, und SQL muss optimiert werden.

2.Created_tmp_files: Die Anzahl der erstellten temporären Tabellen

3.Created_tmp_tables: Die Anzahl der internen temporären Tabellen, die von MySQL beim Ausführen von SQL-Anweisungen erstellt wurden.

4.Slave_open_temp_tables-Anweisung oder Mix-Modus sind nur bei Verwendung sichtbar.

Der Wert von „slave_open_temp_tables“ zeigt an, wie viele temporäre Tabellen vom aktuellen Slave durch Replikation erstellt wurden. Binlog_format ist nur unter Anweisung und gemischt gültig.
Hinweis: Stop Slave ist nutzlos, die Hauptbibliothek muss manuell gelöscht werden oder die Sitzung muss beendet werden .
Das Folgende sind die Binlog-Datensatzinformationen aus der Bibliothek:

Vorsichtsmaßnahmen für temporäre MySQL-Tabellen

1. Temporäre MySQL-Tabellen können zu einer Reduzierung des verfügbaren Speicherplatzes führen:
Vor MySQL-Version 5.7 löschte die Speicher-Engine für temporäre Tabellen, die standardmäßig auf Myisam basiert, die temporären Tabellen automatisch, nachdem die SQL-Ausführung abgeschlossen ist. Ab Version 5.7 wurde die Standardspeicher-Engine für temporäre Tabellen jedoch in innodb geändert. Obwohl es zu einer gewissen Leistungsverbesserung gekommen ist, erstellen mehrere Sitzungen temporäre temporäre Tabellen, da die temporären Tabellen der innodb-Engine den Tabellenbereich ibtmp1 gemeinsam nutzen Gleichzeitig wird der Tabellenbereich bei hoher Parallelität sehr groß und kann nicht dynamisch reduziert und nicht freigegeben werden, es sei denn, MySQL wird neu gestartet.

Sie können einen Maximalwert für den temporären Tabellenbereich, z. B. 10G, wie folgt festlegen:
innodb_temp_data_file_path = ibtmp1:128M:autoextend:max:10G
Wenn der temporäre Tabellenbereich den Maximalwert von 10G erreicht, erfolgt die SQL-Ausführung meldet einen Fehler, der sich auf die Anwendung der normalen Ausführung auswirkt.
Für das Problem des übermäßigen temporären Tabellenplatzes gibt es normalerweise einige andere Methoden, um das Problem zu lösen, wie zum Beispiel:
Stellen Sie die Speicher-Engine der temporären Tabelle auf myisam ein. Obwohl es zu Leistungsproblemen kommen kann, verursacht dies keinen Speicherplatz Probleme.

2. SQL-Anweisung:
(1) Passende Indizes hinzufügen
(2) Weitere Daten in der Where-Bedingung filtern
(3) SQL neu schreiben und Ausführungsplan optimieren
(4) Wenn Sie eine temporäre Tabelle verwenden müssen, dann Parallelität müssen reduziert werden. Es wird empfohlen, eine SSD-Festplatte zu verwenden.

3.undo bezogen
1) Verwenden Sie die Konfigurationsoption innodb_rollback_segments, um die Anzahl der Rollback-Segmente zu definieren. Die Standardeinstellung ist 128, was auch der Maximalwert ist. Ein Rollback-Segment wird immer dem Systemtabellenbereich zugewiesen und 32 Rollback-Segmente sind für den temporären Tabellenbereich (ibtmp1) reserviert. Um Rollback-Segmente zum Rückgängigmachen des Tabellenbereichs zuzuweisen, setzen Sie daher innodb_rollback_segments auf einen Wert größer als 33. Wenn Sie einen separaten Undo-Tablespace konfigurieren, wird das Rollback-Segment im System-Tablespace inaktiv gemacht.

Das heißt, wenn das Rollback-Segment 128 überschreitet, ist zur Nothilfe eine temporäre Tabelle erforderlich.

tablespace -> segment -> extent(64个page，1M) -> page（16kb）

2) abschneiden und rückgängig machen
Wenn innodb_undo_log_truncate ausgelöst wird, erstellt der Vorgang zum Rückgängigmachen des Abschneidens des Tabellenbereichs eine temporäre Datei „undo_space_number_trunc.log“ im Serverprotokollverzeichnis, das durch innodb_log_group_home_dir definiert wird. Wenn während eines Abschneidevorgangs ein Systemfehler auftritt, ermöglicht die temporäre Protokolldatei dem Startvorgang, den abgeschnittenen Undo-Tablespace zu erkennen und den Vorgang fortzusetzen.

4.binlog-Cache-bezogen
Verwenden Sie den Binärprotokoll-Cache und der Wert erreicht den durch binlog_cache_size festgelegten Wert, und verwenden Sie temporäre Dateien, um die Anzahl der Transaktionen aus Änderungen in Transaktionen zu speichern. Kann separat über die Statusvariable Binlog_stmt_cache_disk_use verfolgt werden.

Zusammenfassung

1. Nach dem obigen Verständnis können temporäre MySQL-Tabellen bei der täglichen Überwachung und Optimierung nicht vermieden werden.
2. Darüber hinaus kann es auch in der Geschäftsimplementierung sinnvoll verwendet werden, z. B. als Zwischentabelle zum vorübergehenden Speichern einer kleinen Menge an Informationen usw.
3. Wenn binlog_format während des Replikationsprozesses dem ROW-Modus entspricht, werden die mit der temporären Tabelle verbundenen Binlog-Protokolle nicht aufgezeichnet (mit Ausnahme des Drop-Befehls).
【Verwandte Empfehlungen: MySQL-Video-Tutorial】

Das obige ist der detaillierte Inhalt vonWozu dient die temporäre MySQL-Tabelle?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!