So verwenden Sie den temporären MySQL 5.7-Tablespace, um Fallstricke zu vermeiden

Einführung

MySQL 5.7 soll der sicherste MySQL-Server aller Zeiten sein und bietet einige wichtige Änderungen bei SSL/TLS und der allgemeinen Sicherheitsentwicklung.

MySQL 5.7 und höher unterstützt unabhängige temporäre Tablespaces, aber manchmal können Probleme auftreten.

Ab MySQL 5.7 wird ein unabhängiger temporärer Tablespace verwendet (der nicht dasselbe ist wie ein unabhängiger Undo-Tablespace). Die Datei heißt ibtmp1, ist auf 12 MB initialisiert und hat standardmäßig keine Obergrenze.

Die Option innodb_temp_data_file_path konfiguriert Parameter für temporäre Tablespaces.

innodb_temp_data_file_path = ibtmp1:12M:autoextend

Einige Hinweise zum temporären Tablespace

• Temporäre Tablespaces unterstützen keine Raw-Geräte wie normale InnoDB-Tablespaces.
• Der temporäre Tablespace verwendet eine dynamische Tablespace-ID, sodass sie sich bei jedem Neustart ändert (die temporäre Tablespace-Datei wird bei jedem Neustart neu initialisiert).
• Wenn die Optionseinstellung falsch ist oder andere Gründe (z. B. unzureichende Berechtigungen) die Erstellung des temporären Tablespace verhindern, kann auch die MySQLD-Instanz nicht gestartet werden.
• Der temporäre Tablespace speichert nicht komprimierte temporäre InnoDB-Tabellen. Wenn es komprimierte temporäre InnoDB-Tabellen sind, müssen sie separat in ihren eigenen Tablespace-Dateien gespeichert werden, die im Verzeichnis tmpdir (/tmp) gespeichert sind.
• Temporäre Tabellenmetadaten werden in der Ansicht INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO gespeichert.

Manchmal werden bei der Ausführung von SQL-Anfragen temporäre Tabellen generiert. In extremen Fällen kann dies zu einem plötzlichen Anstieg der temporären Tabellenbereichsdateien führen. In den Fällen, bei denen ich Leuten geholfen habe, betrug der maximale Anstieg fast 300 G, was noch schwerwiegender ist als der plötzliche Anstieg der ibdata1-Dateien, den ich zuvor erlebt habe ...

Einige Vorschläge zur Verwendung temporärer Tabellen

• Legen Sie die Option innodb_temp_data_file_path fest, um die maximale Dateigröße festzulegen. Wenn die Größe das Maximum überschreitet, können die SQL-Anweisungen, die temporäre Tabellen generieren müssen, nicht ausgeführt werden (im Allgemeinen ist die Effizienz solcher SQL-Anweisungen auch relativ gering, sodass Sie diese Gelegenheit nutzen können, um sie zu optimieren).
• Überprüfen Sie INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO, suchen Sie den Thread, der der größten temporären Tabelle entspricht, und beenden Sie ihn, um sie freizugeben. Die Datei ibtmp1 kann jedoch nicht freigegeben werden (es sei denn, das System wird neu gestartet).
• Starten Sie die Instanz zu einem geeigneten Zeitpunkt neu, um die ibtmp1-Datei freizugeben. Im Gegensatz zu ibdata1 wird ibtmp1 beim Neustart neu initialisiert, während dies bei ibdata1 nicht möglich ist.
• Überprüfen Sie regelmäßig SQL-Anweisungen, die länger als N Sekunden ausgeführt wurden (z. B. N = 300), und ziehen Sie in Erwägung, sie zu löschen, um zu verhindern, dass Junk-SQL-Anweisungen über einen längeren Zeitraum ausgeführt werden und das Geschäft beeinträchtigen.

Anhang: Testfall für temporäre Tabelle

Tabellen-DDL

ERSTELLEN SIE TEMPORARY TABLE `tmp1` (
 `id` int(10) unsigned NICHT NULL STANDARD '0',
 `name` varchar(50) NICHT NULL STANDARD '',
 `aid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `nid` int(11) unsigned IMMER GENERIERT ALS ((`id` + 1)) VIRTUELL NICHT NULL,
 `nnid` int(11) unsigned GENERIERT IMMER ALS ((`id` + 1)) GESPEICHERT NICHT NULL,
 Primärschlüssel (`Hilfe`),
 SCHLÜSSEL `Name` (`Name`),
 SCHLÜSSEL `id` (`id`),
 SCHLÜSSEL `nid` (`nid`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8

Die ursprüngliche Tabellengröße beträgt nur 120 MB. Führen Sie INSERT…SELECT direkt aus dieser Tabelle aus, um Daten in die Tabelle tmp1 zu importieren.

-rw-r----- 1 Jahr imysql 120M 14. Apr. 10:52 /data/mysql/test/sid.ibd

Generieren Sie eine temporäre Tabelle (entfernen Sie die virtuelle Spalte, die temporäre Tabelle unterstützt keine virtuellen Spalten, und schreiben Sie dann Daten), und sie ist sogar noch größer (ich verstehe es nicht, ich werde den Grund später herausfinden).

-rw-r----- 1 yejr imysql 140M 25. Juni 09:55 /Benutzer/yejinrong/mydata/ibtmp1

Anzeigen der Metadateninformationen für temporäre Tabellen

[email protected] [test]>Wählen Sie * aus 
 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO_G
*********************** 1. Reihe ***********************
   TABLE_ID: 405
    NAME: #sql14032_300000005_3
    N_COLS: 6
    PLATZ: 421
PER_TABLE_TABLESPACE: FALSCH
  IS_COMPRESSED: FALSE

Löschen Sie den Index erneut, und er wird noch größer.

-rw-r----- 1 Jahr imysql 204M 25. Juni 09:57 /data/mysql/ibtmp1

Nach dem Löschen des Indexes im zweiten Test wurde er 200 MB groß (weil ich im zweiten Test die maximale Größe der temporären Tabelle auf 200 MB festgelegt hatte).

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:200M

-rw-r----- 1 Jahr imysql 200M 25. Juni 10:15 /data/mysql/ibtmp1

Führen Sie eine langsame SQL-Anweisung aus, die eine temporäre Tabelle generiert.

Hinweis: Ab MySQL 5.7 wird bei der Ausführung von UNION ALL keine temporäre Tabelle mehr generiert (es sei denn, eine zusätzliche Sortierung ist erforderlich).

[email protected] [test]>erklären Sie select * from tmp1 union 
 wähle ID, Name, Hilfe aus Seite\G
*************************** 1. Reihe ***************************
   ID: 1
 Auswahltyp: PRIMARY
  Tabelle: tmp1
 Partitionen: NULL
   Typ: ALLE
mögliche Schlüssel: NULL
   Schlüssel: NULL
  key_len: NULL
   Ref: NULL
   Reihen: 3986232
  gefiltert: 100,00
  Extra: NULL
*************************** 2. Reihe ***************************
   ID: 2
 Auswahltyp: UNION
  Tabelle: sid
 Partitionen: NULL
   Typ: ALLE
mögliche Schlüssel: NULL
   Schlüssel: NULL
  key_len: NULL
   Ref: NULL
   Reihen: 802682
  gefiltert: 100,00
  Extra: NULL
*************************** 3. Reihe ***************************
   ID: NULL
 select_type: UNION-ERGEBNIS
  Tabelle: <union1,2>
 Partitionen: NULL
   Typ: ALLE
mögliche Schlüssel: NULL
   Schlüssel: NULL
  key_len: NULL
   Ref: NULL
   Zeilen: NULL
  gefiltert: NULL
  Extra: Verwendung von temporären

Die Dateigröße ist auf 588M angewachsen und es ist noch nicht fertig. Ich stecke direkt fest

-rw-r----- 1 Jahr imysql 588M 25. Juni 10:07 /data/mysql/ibtmp1

Während des zweiten Tests wurde die maximale Größe der temporären Tablespace-Datei auf 200 MB festgelegt und bei der erneuten Ausführung wurde ein Fehler gemeldet:

[email protected] [test]>Wählen Sie * aus tmp1 Union 
 wähle ID, Name, Hilfe von SID;
FEHLER 1114 (HY000): Die Tabelle '/var/folders/bv/j4tjn6k54dj5jh1tl8yn6_y00000gn/T/#sql14032_5_8' ist voll

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.

siehe

[MySQL FAQ] Serie - Wann werden temporäre Tabellen verwendet?

FAQ-Serie | So verhindern Sie, dass die Größe der ibdata1-Datei in die Höhe schießt

https://dev.mysql.com/doc/refman/5.7/en/temporary-files.html

https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_temp_data_file_path

https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html