A quoi sert la table temporaire MySQL ?

Le rôle de la table temporaire MySQL : 1. Les tables temporaires créées par les utilisateurs eux-mêmes sont utilisées pour enregistrer des données temporaires ; 2. Lorsque les utilisateurs exécutent du SQL complexe, ils peuvent utiliser des tables temporaires pour effectuer le regroupement, le tri, la déduplication et d'autres opérations, et par par défaut, lorsque vous vous déconnecterez de la base de données, la table temporaire sera automatiquement détruite.

L'environnement d'exploitation de ce tutoriel : système Windows 10, MySQL version 5.7, ordinateur Dell G3.

Quelles sont les utilisations des tables temporaires MySQL ?

Le rôle des tables temporaires MySQL

Les tables temporaires MySQL sont utilisées dans de nombreux scénarios, tels que les tables temporaires créées par les utilisateurs eux-mêmes pour enregistrer des données temporaires, et Lors de l'exécution de SQL complexes en interne, MySQL doit utiliser des tables temporaires pour le regroupement, le tri, la déduplication et d'autres opérations. Ce qui suit traitera de certains concepts, classifications et problèmes courants des tables temporaires MySQL.

Type de table temporaire MySQL

1. Table temporaire externe, une table temporaire créée via la syntaxe de création de table temporaire, vous pouvez spécifier le moteur de stockage comme mémoire, innodb, myisam, etc. supprimés après la fin de la session sont automatiquement nettoyés. Si une table temporaire existe en même temps qu'une table non temporaire, la table non temporaire n'est pas visible. La commande show tables n'affiche pas les informations des tables temporaires.
Vous pouvez afficher des informations pertinentes sur les tables temporaires externes via la table système information_schema.INNODB_TEMP_TABLE_INFO. Cette partie est encore relativement rarement utilisée.

2. Les tables temporaires internes sont généralement utilisées lors de l'exécution de SQL complexes, tels que group by, order by, distinct, union, etc. Si le plan d'exécution contient Using Temporary, il y a également une annulation de restauration, mais lorsqu'elle est insuffisante. espace, MySQL utilisera des tables temporaires générées automatiquement en interne pour vous aider à terminer le travail.

Paramètres liés à la table temporaire MySQL

1.max_heap_table_size : La valeur maximale de la table mémoire créée par l'utilisateur. Elle est également utilisée avec tmp_table_size pour limiter la taille de la table temporaire interne en mémoire.
2.tmp_table_size : La valeur maximale de la table temporaire interne en mémoire est déterminée avec le paramètre max_heap_table_size, et la valeur minimale des deux est prise. Si la table temporaire dépasse cette valeur, elle sera déplacée de la mémoire vers le disque.
3.innodb_tmpdir : opérations ALTER TABLE en ligne qui reconstruisent la table max_tmp_tables

4.default_tmp_storage_engine : Le moteur de stockage par défaut pour les tables temporaires externes (tables créées par create Temporary table).

5.innodb_temp_data_file_path : attribut de fichier temporaire sous le moteur innodb. Il est recommandé de limiter innodb_temp_data_file_path = ibtmp1:1G:autoextend:max:30G

6.Internal_tmp_disk_storage_engine : Le moteur de stockage de table temporaire interne sur le disque, la valeur facultative est myisam ou innodb. Lors de l'utilisation d'une table innodb, dans certains scénarios, par exemple, si la table temporaire comporte trop de colonnes ou si la taille des lignes dépasse la limite, une erreur de « Taille de ligne trop grande ou Trop de colonnes » peut se produire. Le moteur innodb de la table temporaire doit être rétabli en myisam . tmpdir : Répertoire de la table temporaire Lorsque la taille de la table temporaire dépasse un certain seuil, elle sera transférée de la mémoire vers le disque
7. La variable tmpdir représente le répertoire où se trouve la table temporaire sur le disque.

Variables d'état liées aux tables temporaires MySQL

1.Created_tmp_disk_tables : lors de l'exécution d'une instruction SQL, le nombre de tables temporaires internes créées par MySQL sur le disque. Si cette valeur est grande, la raison possible est que la valeur de mémoire maximale. alloué à la table temporaire est relativement volumineux, ou il existe un grand nombre d'opérations de tri, de regroupement, de déduplication et autres dans SQL, et SQL doit être optimisé.

2.Created_tmp_files : Le nombre de tables temporaires créées

3.Created_tmp_tables : Le nombre de tables temporaires internes créées par MySQL lors de l'exécution d'instructions SQL.

4.L'instruction Slave_open_temp_tables ou le mode mix ne peut être vu qu'en cours d'utilisation.
La valeur de slave_open_temp_tables indique combien de tables temporaires ont été créées par l'esclave actuel via la réplication. Binlog_format n'est valide que sous instruction et mixte
Remarque : l'arrêt de l'esclave est inutile, la bibliothèque principale doit être supprimée manuellement ou la session doit être quittée. .
Voici les informations d'enregistrement du journal binaire de la bibliothèque :

Précautions relatives aux tables temporaires MySQL

1. Les tables temporaires MySQL peuvent entraîner une réduction de l'espace disque disponible :
Avant la version MySQL 5.7, le moteur de stockage des tables temporaires par défaut myisam supprimera automatiquement les tables temporaires une fois l'exécution SQL terminée. Cependant, à partir de la version 5.7, le moteur de stockage par défaut des tables temporaires a été remplacé par innodb. Bien qu'il y ait eu une certaine amélioration des performances, les tables temporaires du moteur innodb partageant l'espace table ibtmp1, plusieurs sessions créent des tables temporaires temporaires. en même temps sous une concurrence élevée, lorsque la table est ouverte, l'espace de la table deviendra très grand et ne pourra pas être réduit dynamiquement et ne pourra être libéré que si MySQL est redémarré.

Vous pouvez définir une valeur maximale pour l'espace table temporaire, telle que 10G, comme suit :
innodb_temp_data_file_path = ibtmp1:128M:autoextend:max:10G
Lorsque l'espace table temporaire atteint la valeur maximale de 10G, l'exécution SQL signalera une erreur, affectant l’application de l’exécution normale.
Pour le problème d'espace de table temporaire excessif, il existe généralement d'autres méthodes pour résoudre le problème, telles que :
Définissez le moteur de stockage de la table temporaire sur myisam. Bien qu'il puisse y avoir des problèmes de performances, cela ne générera pas d'espace disque. problèmes.

2. Instruction SQL :
(1) Ajoutez des index appropriés
(2) Filtrez plus de données dans la condition Where
(3) Réécrivez SQL et optimisez le plan d'exécution
(4) Si vous devez utiliser une table temporaire, alors la concurrence doit être réduit. Il est recommandé d'utiliser un disque dur SSD.

3.undo Related
1) Utilisez l'option de configuration innodb_rollback_segments pour définir le nombre de segments d'annulation. Le paramètre par défaut est 128, qui est également la valeur maximale. Un segment d'annulation est toujours alloué à l'espace table système et 32 ​​segments d'annulation sont réservés à l'espace table temporaire (ibtmp1). Par conséquent, pour allouer des segments d'annulation pour annuler le tablespace, définissez innodb_rollback_segments sur une valeur supérieure à 33. Lors de la configuration d'un tablespace d'annulation distinct, le segment d'annulation dans le tablespace système sera rendu inactif.

C'est-à-dire que lorsque le segment de restauration dépasse 128, une table temporaire est nécessaire pour les secours d'urgence.

tablespace -> segment -> extent(64个page，1M) -> page（16kb）

2) truncate undo
Lorsque innodb_undo_log_truncate est déclenché, l'opération d'annulation de troncature de l'espace table crée un fichier temporaire undo_space_number_trunc.log dans le répertoire des journaux du serveur, qui est défini par innodb_log_group_home_dir. Si une panne du système se produit lors d'une opération de troncature, le fichier journal temporaire permet au processus de démarrage de reconnaître l'espace table d'annulation tronqué et de poursuivre les opérations.

4.binlog cache Related
Utilisez le cache de journaux binaires et la valeur atteint la valeur définie par binlog_cache_size, et utilisez des fichiers temporaires pour stocker le nombre de transactions à partir des modifications des transactions. Peut être suivi séparément via la variable d'état Binlog_stmt_cache_disk_use.

Résumé

1. D'après la compréhension ci-dessus, les tables temporaires MySQL ne peuvent pas être évitées dans la surveillance et l'optimisation quotidiennes.
2. De plus, il peut également être utilisé de manière appropriée dans la mise en œuvre commerciale, comme comme table intermédiaire pour enregistrer temporairement une petite quantité d'informations, etc.
3. Lorsque binlog_format est égal au mode ROW pendant le processus de réplication, les journaux binlog liés à la table temporaire ne sont pas enregistrés (sauf pour la commande drop).
【Recommandations associées : tutoriel vidéo 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!