Discussion sur les tables temporaires et les tables dérivées de MySQL

Sur les tables dérivées

Lorsque la requête principale contient une table dérivée, ou lorsque l'instruction SELECT contient une clause UNION, ou lorsque l'instruction SELECT contient une clause ORDER BY sur un champ (par rapport à une clause GROUP BY sur un autre champ), MySQL doit créer automatiquement une table temporaire pour stocker le résultat temporaire de la requête. Ces tables temporaires sont créées et gérées par MySQL elles-mêmes et deviennent des tables temporaires automatiquement créées. Pour les tables temporaires automatiquement créées, en raison de la performance supérieure des tables temporaires en mémoire, MySQL utilise toujours d'abord les tables temporaires en mémoire. Lorsque les tables temporaires en mémoire deviennent trop grandes, atteignant un certain seuil, elles sont converties en tables temporaires sur disque. Autrement dit, les tables temporaires sur disque sont une extension de la table temporaire en mémoire. Le seuil de conversion des tables temporaires en mémoire en tables temporaires sur disque est déterminé par la valeur plus petite entre les variables système max_heap_table_size et tmp_table_size.

Les tables dérivées sont généralement utilisées dans la clause FROM. Par exemple :

select * from (select * from table) as t;

Sur les tables temporaires

Lorsque vous travaillez sur de très grandes tables, vous pourriez parfois devoir exécuter de nombreuses requêtes pour obtenir un petit sous-ensemble de grandes données, plutôt que de courir ces requêtes sur l'ensemble de la table, il pourrait être plus rapide de faire trouver par MySQL les quelques enregistrements nécessaires à chaque fois, et de sélectionner ces enregistrements dans une table temporaire, puis d'exécuter des requêtes sur ces tables.

Il est facile de créer une table temporaire, il suffit d'ajouter le mot-clé TEMPORARY à l'instruction CREATE TABLE habituelle:

CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)

Les tables temporaires existent pendant que vous êtes connecté à MySQL. Lorsque vous vous déconnectez, MySQL supprime automatiquement les tables et libère l'espace utilisé. Bien sûr, vous pouvez supprimer les tables et libérer l'espace lorsque vous êtes toujours connecté.

DROP TABLE tmp_table

Si l'objet temporaire nommé tmp_table existe déjà dans la base de données lors de la création d'une table temporaire nommée tmp_table, il est nécessaire de masquer (cacher) l'objet temporaire non temporaire tmp_table.

Si vous déclarez qu'une table temporaire est une table HEAP, MySQL vous permet également de spécifier sa création en mémoire :

CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP

Les tables HEAP sont stockées en mémoire, donc les requêtes que vous exécutez dessus peuvent être plus rapides que les tables temporaires sur le disque. Cependant, les tables HEAP diffèrent des tables générales et ont leurs propres limitations. Voir le Manuel de référence MySQL pour plus de détails.

Comme recommandé précédemment, vous devriez tester les tables temporaires pour voir si elles sont vraiment plus rapides pour exécuter des requêtes sur de grandes bases de données. Si les données sont bien indexées, les tables temporaires peuvent ne pas être très rapides.

1. Lorsque vous rompez la connexion à MySQL, le système supprimera automatiquement les données de la table temporaire, mais cela ne s'applique qu'aux tables créées avec la requête suivante :

Définir les champs :

CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)

2) directement importer les résultats de la requête dans la table temporaire

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

2. De plus, MySQL vous permet de créer des tables temporaires directement en mémoire, car elles sont stockées en mémoire, elles sont très rapides. La syntaxe est la suivante :

CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP

3À partir de l'analyse ci-dessus, il est évident que les données des tables temporaires seront effacées. Si vous rompez la connexion, elles seront automatiquement effacées. Cependant, il est impossible de se connecter à la base de données à chaque exécution d'une requête SQL dans votre programme (si c'était le cas, vous rencontreriez les problèmes que vous craignez). Seule la fermeture de la connexion à la base de données permet l'effacement des données des tables temporaires. Si vous exécutez plusieurs requêtes SQL dans une seule connexion à la base de données, le système ne supprimera pas automatiquement les données des tables temporaires.

Déclaration : Le contenu de cet article est issu du réseau, appartenant à ses auteurs respectifs. Le contenu est apporté par les utilisateurs d'Internet et téléchargé spontanément. Ce site Web ne détient pas de droits de propriété, n'a pas été traité par l'éditeur humain et n'assume aucune responsabilité juridique. Si vous trouvez du contenu susceptible de violer les droits d'auteur, vous pouvez envoyer un e-mail à : notice#w3Déclaration : Le contenu de cet article est issu du réseau, et les droits d'auteur appartiennent aux propriétaires respectifs. Le contenu est apporté par les utilisateurs d'Internet et téléchargé spontanément. Ce site Web ne détient pas de droits de propriété, n'a pas été traité par l'éditeur humain et n'assume aucune responsabilité juridique. Si vous trouvez du contenu susceptible de violer les droits d'auteur, veuillez envoyer un e-mail à : notice#w