Vertiefte Kenntnisse der temporären MySQL-Tabellen

Übersicht

Es gibt zwei Haupttypen von temporären Tabellen in MySQL, einschließlich externer temporärer Tabellen und interner temporärer Tabellen . Die externe temporäre Tabelle ist eine temporäre Tabelle, die durch die Anweisung „create temporary table...“ erstellt wurde. Die temporäre Tabelle ist nur in dieser Sitzung gültig. Nach dem Trennen der Sitzung werden die Daten der temporären Tabelle automatisch gelöscht. Es gibt zwei Haupttypen interner temporärer Tabellen. Die andere ist die temporäre Tabelle in information_schema, und die andere ist, wenn die Sitzung eine Abfrage ausführt. Wenn der Ausführungsplan „Using temporary“ enthält, wird eine temporäre Tabelle generiert. Ein Unterschied zwischen internen temporären Tabellen und externen temporären Tabellen besteht darin, dass wir die Tabellenstrukturdefinitionsdatei aus der internen temporären Tabelle nicht sehen können. Die Tabellendefinitionsdatei frm der externen temporären Tabelle besteht im Allgemeinen aus #sql{Prozess-ID}_{Thread-ID}_Seriennummer, sodass verschiedene Sitzungen temporäre Tabellen mit demselben Namen erstellen können.

Temporäre Tabelle

Der Hauptunterschied zwischen temporären Tabellen und gewöhnlichen Tabellen besteht darin, ob die Daten nach dem Ende der Instanz, Sitzung oder Anweisung automatisch bereinigt werden. Wenn wir beispielsweise in der internen temporären Tabelle den Zwischenergebnissatz während einer Abfrage speichern möchten, wird die temporäre Tabelle nach Abschluss der Abfrage automatisch wiederverwendet, ohne dass sich dies auf die Struktur und Daten der Benutzertabelle auswirkt. Darüber hinaus können temporäre Tabellen in verschiedenen Sitzungen denselben Namen haben. Wenn mehrere Sitzungen Abfragen ausführen und Sie temporäre Tabellen verwenden möchten, müssen Sie sich keine Sorgen über doppelte Namen machen. Nach der Einführung des temporären Tabellenbereichs in 5.7 werden alle temporären Tabellen im temporären Tabellenbereich (nicht komprimiert) gespeichert und die Daten im temporären Tabellenbereich können wiederverwendet werden. Temporäre Tabellen unterstützen nicht nur die Innodb-Engine, sondern auch die Myisam-Engine, die Speicher-Engine usw. Daher können wir die Entität (IDB-Datei) nicht in der temporären Tabelle sehen, sie ist jedoch nicht unbedingt eine Speichertabelle und kann auch in einem temporären Tabellenbereich gespeichert werden.

Temporäre Tabelle vs. Speichertabelle

Temporäre Tabelle kann entweder eine Innodb-Engine-Tabelle oder eine Memory-Engine-Tabelle sein. Die sogenannte Speicher-Engine-Tabelle bezieht sich hier auf die Tabellenerstellungsanweisung „create table...engine=memory“. Die Tabellenstruktur wird über die gleiche interne Speicher-Engine verwaltet Tabelle, die FRM-Datei kann nicht gesehen werden, nicht einmal das Verzeichnis information_schema auf der Festplatte. Innerhalb von MySQL umfassen die temporären Tabellen in information_schema zwei Typen: temporäre Tabellen der Innodb-Engine und temporäre Tabellen der Speicher-Engine. Beispielsweise gehört die Tabelle TABLES zur temporären Speichertabelle, während Spalten und Prozessliste zur temporären Tabelle der Innodb-Engine gehören. Alle Daten in der Speichertabelle befinden sich im Speicher. Die Datenstruktur im Speicher ist ein Array (Heap-Tabelle). Bei Szenarien mit kleinem Datenvolumen ist die Geschwindigkeit relativ hoch beteiligt sind). Aber Speicher ist schließlich eine begrenzte Ressource. Wenn die Datenmenge relativ groß ist, ist die Verwendung einer Speichertabelle daher nicht geeignet. Wählen Sie stattdessen die Verwendung einer temporären Festplattentabelle (innodb-Engine). Baumspeicherstruktur (Innodb-Engine). Die Pufferpoolressource wird gemeinsam genutzt, und die Daten in der temporären Tabelle können einen gewissen Einfluss auf die heißen Daten des Pufferpools haben. Darüber hinaus kann der Vorgang physische E/A erfordern. Speicher-Engine-Tabellen können tatsächlich Indizes erstellen, einschließlich Btree-Indizes und Hash-Indizes, sodass die Abfragegeschwindigkeit sehr hoch ist. Der Hauptnachteil sind begrenzte Speicherressourcen.

Szenarien für die Verwendung temporärer Tabellen

Wie bereits erwähnt, werden temporäre Tabellen verwendet, wenn der Ausführungsplan „Verwendung temporärer Tabellen“ enthält.

Die Testtabellenstruktur ist wie folgt:

mysql> show create table t1_normal\G
*************************** 1. row ***************************
       Table: t1_normal
Create Table: CREATE TABLE `t1_normal` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=770023 DEFAULT CHARSET=utf8

Szenario 1: Union

mysql> explain select * from t1_normal union select * from t1_normal; 
+----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 
+----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ 
| 1 | PRIMARY | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | NULL | | 2 | UNION | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+

Die Bedeutung der Union-Operation besteht darin, die Vereinigung der beiden Unterabfrageergebnisse und zu nehmen Behalten Sie nur eine Zeile doppelter Daten bei. Durch Erstellen einer temporären Tabelle mit einem Primärschlüssel können Sie das Problem der „Duplizierung“ lösen und die endgültige Ergebnismenge über die temporäre Tabelle speichern, sodass Sie im Extra-Element in „Temporär verwenden“ sehen können den Ausführungsplan. Eine mit Union verwandte Operation ist Union All, die ebenfalls die Ergebnisse zweier Unterabfragen zusammenführt, aber das Duplizierungsproblem nicht löst. Daher gibt es für „Union All“ keine Bedeutung der „Entfernung von Duplikaten“, sodass keine temporäre Tabelle erforderlich ist.

mysql> explain select * from t1_normal  union  all select * from t1_normal;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | PRIMARY     | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | NULL  |
|  2 | UNION       | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+

Szenario 2: Gruppieren nach

mysql> explain select c1,count(*) as count from t1_normal group by c1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                           |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+
|  1 | SIMPLE      | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | Using temporary; Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+

Die Bedeutung von „Gruppieren nach“ besteht darin, standardmäßig nach der angegebenen Spalte zu gruppieren und nach der angegebenen Spalte zu sortieren. Die Bedeutung der obigen SQL-Anweisung besteht darin, die Daten in t1_normal nach dem Wert von Spalte c1 zu gruppieren und die Anzahl der Datensätze für jeden Spaltenwert von c1 zu zählen. Aus dem Ausführungsplan sehen wir „Verwenden von temporär; Verwenden von Dateisortierung“. Für die Gruppierung nach müssen wir zunächst die Anzahl der Vorkommen jedes Werts zählen. Dies erfordert die Verwendung einer temporären Tabelle, um ihn schnell zu finden , fügen Sie einen Datensatz ein, falls vorhanden, und die Anzahl wird akkumuliert, sodass Sie „Verwenden von Temporär“ sehen. Da „Gruppieren nach“ eine Sortierung impliziert, müssen Sie die Datensätze nach Spalte c1 sortieren, sodass Sie „Verwenden von Dateisortierung“ sehen.

1). Eliminieren Sie die Dateisortierung.

Tatsächlich kann die Gruppierung nach auch die „Sortierbedeutung“ eliminieren.

mysql> explain select c1,count(*) as count from t1_normal group by c1 order by null;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra           |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
|  1 | SIMPLE      | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | Using temporary |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+

Sie können sehen, dass nach dem Hinzufügen von „order by null“ zur Anweisung „Using filesort“ nicht mehr im Ausführungsplan erscheint.

2). Temporäre Tabellen eliminieren

mysql> explain select SQL_BIG_RESULT c1,count(*) as count from t1_normal group by c1; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | Using filesort | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+

可以看到执行计划中已经没有了“Using temporary”，所以group by并非一定依赖临时表，临时表在group by中的作用主要是“去重”。所以，实际上有另外一种方式，不使用临时表，直接利用sort_buffer排序(sort_buffer不够时，进行文件排序，具体而言是每一个有序数组作为一个单独文件，然后进行外排归并)，然后再扫描得到聚合后的结果集。

3).SQL_BIG_RESULT

同时我们语句中用到了“SQL_BIG_RESULT”这个hint，正是因为这个hint导致了我们没有使用临时表，先说说SQL_BIG_RESULT和SQL_SMALL_RESULT的含义。

SQL_SMALL_RESULT：显示指定用内存表(memory引擎)

SQL_BIG_RESULT：显示指定用磁盘临时表(myisam引擎或innodb引擎)

两者区别在于，使用磁盘临时表可以借助主键做去重排序，适合大数据量；使用内存表写入更快，然后在内存中排序，适合小数据量。下面是从MySQL手册中摘录的说明。

SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively. 

For SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a key on the GROUP BY elements. 

For SQL_SMALL_RESULT, MySQL uses fast temporary tables to store the resulting table instead of using sorting. 

This should not normally be needed.

回到问题本身，这里MySQL优化器根据hint知道需要使用磁盘临时表，而最终直接选择了数组存储+文件排序这种更轻量的方式。

如何避免使用临时表

通常的SQL优化方式是让group by 的列建立索引，那么执行group by时，直接按索引扫描该列，并统计即可，也就不需要temporary和filesort了。

mysql> alter table t1_normal add index idx_c1(c1);
Query OK, 0 rows affected (1 min 23.82 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select c1,count(*) as count from t1_normal group by c1 order by null;
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t1_normal | NULL       | index | idx_c1        | idx_c1 | 5       | NULL | 523848 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+

相关参数与状态监控

1).参数说明

max_heap_table_size

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow,The value of the variable is used to calculate MEMORY table MAX_ROWS values.

这个参数主要针对用户创建的MEMORY表，限制内存表最大空间大小，注意不是记录数目，与单条记录的长度有关。如果超出阀值，则报错。ERROR 1114 (HY000): The table 'xxx' is full

tmp_table_size

The maximum size of internal in-memory temporary tables.

对于用户手工创建的内存表，只有参数max_heap_table_size起作用；对于内部产生的内存表，则参数max_heap_table_size和tmp_table_size同时起作用。对于内部产生的内存表(比如union，group by等产生的临时表)，先是采用内存表(memory表)，然后超过设置的阀值(max_heap_table_size,tmp_table_size)就会转为磁盘表，使用innodb引擎或者myisam引擎，通过参数internal_tmp_disk_storage_engine指定。

tmpdir

如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下

2.状态监控

Created_tmp_tables，内部临时表数目

Created_tmp_disk_tables，磁盘临时表数目

3.information_schema相关

mysql> create temporary table t1_tmp(id int primary key,c1 int); Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO; +----------+---------------+--------+-------+----------------------+---------------+ | TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED | +----------+---------------+--------+-------+----------------------+---------------+ | 10063 | #sql693d_29_0 | 5 | 45 | FALSE | FALSE | +----------+---------------+--------+-------+----------------------+---------------+

总结

本文详细介绍了MySQL中临时表的核心特征，按需创建并且自动销毁，对于纯内存的数据特别适合，但为了避免内存不可控，实际上不仅仅有内存临时表，还有磁盘临时表。临时表和内存表本没有直接关联，因为临时表既可以是memory引擎，又可以innodb引擎将两者联系到了一起，实际上不同类别的临时表也是用到了不同引擎的优势。临时表使用的典型场景是union和group by。为了消除临时表，我们需要对group by列添加索引，或者对于大结果集，使用SQL_BIG_RESULT等。最后本文介绍了临时表相关的参数和状态变量，以及information_schema中的临时表信息。

推荐教程：《MySQL教程》

Das obige ist der detaillierte Inhalt vonVertiefte Kenntnisse der temporären MySQL-Tabellen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!