Vertiefte Kenntnisse der temporären MySQL-Tabellen-MySQL-Tutorial-php.cn

Ü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

Nach dem Login kopieren

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 | +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+

Nach dem Login kopieren

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  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+

Nach dem Login kopieren

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 |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+

Nach dem Login kopieren

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 |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+

Nach dem Login kopieren

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 | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+

Nach dem Login kopieren

可以看到执行计划中已经没有了“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 |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+

Nach dem Login kopieren