Compréhension approfondie des tables temporaires MySQL

Vue d'ensemble

Il existe deux principaux types de tables temporaires dans MySQL, notamment les tables temporaires externes et les tables temporaires internes. . La table temporaire externe est une table temporaire créée via l'instruction create Temporary table.... La table temporaire n'est valide que dans cette session. Une fois la session déconnectée, les données de la table temporaire seront automatiquement effacées. Il existe deux principaux types de tables temporaires internes. L'une est la table temporaire dans information_schema et l'autre est lorsque la session exécute une requête. Si le plan d'exécution contient "Utilisation de temporaire", une table temporaire sera générée. Une différence entre les tables temporaires internes et les tables temporaires externes est que nous ne pouvons pas voir le fichier de définition de structure de table à partir de la table temporaire interne. Le fichier de définition de table de la table temporaire externe est généralement composé de #sql{process id}_{thread id}_serial number, donc différentes sessions peuvent créer des tables temporaires avec le même nom.

Table temporaire

La principale différence entre les tables temporaires et les tables ordinaires est de savoir si les données sont automatiquement nettoyées après la fin de l'instance, de la session ou de l'instruction. Par exemple, dans la table temporaire interne, si nous souhaitons stocker le jeu de résultats intermédiaire lors d'une requête, la table temporaire sera automatiquement recyclée une fois la requête terminée, sans affecter la structure et les données de la table utilisateur. De plus, les tables temporaires de différentes sessions peuvent avoir le même nom lorsque plusieurs sessions exécutent des requêtes, si vous souhaitez utiliser des tables temporaires, vous n'aurez pas à vous soucier des noms en double. Après l'introduction de l'espace table temporaire dans la version 5.7, toutes les tables temporaires sont stockées dans l'espace table temporaire (non compressées) et les données de l'espace table temporaire peuvent être réutilisées. Les tables temporaires prennent non seulement en charge le moteur Innodb, mais prennent également en charge le moteur myisam, le moteur de mémoire, etc. Par conséquent, nous ne pouvons pas voir l'entité (fichier idb) dans la table temporaire, mais ce n'est pas nécessairement une table mémoire et peut également être stockée dans un espace table temporaire.

Table temporaire VS table mémoire

La table temporaire peut être soit une table de moteur innodb, soit une table de moteur de mémoire. La table mémoire fait ici référence à la table du moteur de mémoire. Grâce à l'instruction de création de table create table...engine=memory, toutes les données sont dans la mémoire. La structure de la table est gérée via frm Pour le même moteur de mémoire interne. table, le fichier frm n'est pas visible, je ne peux même pas voir le répertoire information_schema sur le disque. Dans MySQL, les tables temporaires dans information_schema incluent deux types : les tables temporaires du moteur innodb et les tables temporaires du moteur de mémoire. Par exemple, la table TABLES appartient à la table temporaire de la mémoire, tandis que les colonnes et la liste de processus appartiennent à la table temporaire du moteur innodb. Toutes les données de la table mémoire sont dans la mémoire. La structure des données dans la mémoire est un tableau (table de tas). Toutes les opérations de données sont effectuées dans la mémoire. Pour les scénarios de petits volumes de données, la vitesse est relativement rapide (pas d'opérations d'E/S physiques). sont impliqués). Mais la mémoire est une ressource limitée après tout. Par conséquent, si la quantité de données est relativement importante, il n'est pas approprié d'utiliser une table mémoire. Choisissez plutôt d'utiliser une table temporaire sur disque (moteur innodb). Structure de stockage arborescente (moteur innodb). Innodb La ressource du pool de tampons est partagée et les données de la table temporaire peuvent avoir un certain impact sur les données chaudes du pool de tampons. De plus, l'opération peut impliquer des E/S physiques. Les tables du moteur de mémoire peuvent en fait créer des index, notamment des index Btree et des index de hachage, de sorte que la vitesse de requête est très rapide. Le principal inconvénient est les ressources mémoire limitées.

Scénarios d'utilisation de tables temporaires

Comme mentionné précédemment, lorsque le plan d'exécution contient "Utilisation de tables temporaires", voici deux scénarios principaux.

La structure de la table de test est la suivante :

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

Scénario 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 | +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+

Le sens de l'opération d'union est de prendre l'union des deux résultats de la sous-requête, et ne conservez qu'une seule ligne pour les données en double, en créant une table temporaire avec une clé primaire, vous pouvez résoudre le problème de "duplication" et stocker le résultat final défini via la table temporaire, afin que vous puissiez voir "Utilisation temporaire" dans l'élément supplémentaire dans le plan d'exécution. Une opération liée à l'union est l'union all, qui fusionne également les résultats de deux sous-requêtes, mais ne résout pas le problème de duplication. Par conséquent, pour l’union all, la « suppression des doublons » n’a aucune signification, il n’est donc pas nécessaire d’avoir une table temporaire.

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

Scénario 2 : regrouper par

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

La signification de regrouper par est de regrouper par la colonne spécifiée et de trier par la colonne spécifiée par défaut. La signification de l'instruction SQL ci-dessus est de regrouper les données dans t1_normal par la valeur de la colonne c1 et de compter le nombre d'enregistrements pour chaque valeur de colonne de c1. Dans le plan d'exécution, nous voyons "Utilisation de temporaire ; Utilisation de tri de fichiers". Pour le regroupement par, il faut d'abord compter le nombre d'occurrences de chaque valeur. Cela nécessite l'utilisation d'une table temporaire pour la localiser rapidement si elle n'existe pas. , insérez un enregistrement. s'il existe et que le nombre est accumulé, vous voyez donc "Utilisation temporaire" et parce que le regroupement par implique un tri, vous devez trier les enregistrements en fonction de la colonne c1, vous voyez donc "Utilisation du tri de fichiers".

1). Éliminer le tri des fichiers

En fait, regrouper par peut également éliminer le « sens du tri ».

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

Vous pouvez voir qu'après avoir ajouté "order by null" à l'instruction, "Using filesort" n'apparaît plus dans le plan d'exécution.

2). Supprimer les tables temporaires

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教程》

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!