Compréhension approfondie des tables temporaires MySQL-tutoriel mysql-php.cn

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

Copier après la connexion

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

Copier après la connexion

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

Copier après la connexion

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

Copier après la connexion

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

Copier après la connexion

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

Copier après la connexion

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

Copier après la connexion