Lors d'un récent développement, j'ai dû remplacer des tables temporaires utilisées pour des rapports de remboursement et de retour par des tables permanentes.

Voici un exemple de création de table :

CREATE TABLE tasks ( 
    task_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
    parent_id INT UNSIGNED NOT NULL DEFAULT 0, 
    task VARCHAR(100) NOT NULL, 
    test_id INT UNSIGNED NOT NULL DEFAULT 0, 
    date_added TIMESTAMP NOT NULL, 
    date_completed TIMESTAMP, 
    PRIMARY KEY (task_id), 
    KEY parent_id(parent_id), 
    KEY test_id (test_id)
) ENGINE=INNODB;

Les colonnes parent_id et test_id étant fréquemment utilisées dans les clauses WHERE des jointures, il était nécessaire d'ajouter des index sur ces colonnes pour optimiser les performances.

Cela m'a amené à m'interroger sur plusieurs points : pourquoi utiliser KEY plutôt que INDEX ? Quelles sont les particularités des tables temporaires et sont-elles stockées en mémoire ?

Différence entre KEY et INDEX dans MySQL

Dans MySQL, KEY et INDEX sont souvent employés de manière interchangeable, mais ils ont des significations distinctes :

• KEY est une structure physique au niveau du modèle. Il a un double rôle : la contrainte (pour maintenir l'intégrité et la structure de la base) et l'index (pour optimiser les requêtes). Cela inclut PRIMARY KEY, UNIQUE KEY et FOREIGN KEY.
• INDEX est une structure physique au niveau de l'implémentation. Il sert uniquement à accélérer les recherches sans imposer de contraintes. Il est stocké dans un espace dédié (par exemple, l'espace de table InnoDB) sous forme de structure arborescente.

Plus précisément :

• PRIMARY KEY : assure une contrainte d'unicité et d'intégrité (constraint) tout en créant un index associé.
• UNIQUE KEY : garantit l'unicité des données (contrainte) et crée un index.
• FOREIGN KEY : garantit l'intégrité référentielle (contrainte) et crée un index.

En résumé, une KEY dans MySQL remplit à la fois un rôle de contrainte et d'index. MySQL impose que toute KEY soit également indexée pour des raisons de performance.

MySQL exige que chaque KEY soit aussi un index, ce qui est un détail d'implémentation spécifique à MySQL pour améliorer les performances.

Les types d'index courants dans MySQL incluent : index primaire, index unique, index ordinaire, index plein texte et index composite.

Tables mémoire vs tables temporaires

Table mémoire (MEMORY)

• Paramètre de contrôle : max_heap_table_size (ex. 1024 Mo).
• Si la limite est atteinte, une erreur est générée.
• La définition de la table est stockée sur le disque, les données et les index en mémoire.
• Ne prend pas en charge les champs TEXT ou BLOB.
• Le nom de la table doit être unique entre les sessions.
• Visible par toutes les sessions.
• Les données sont perdues lors du redémarrage de MySQL, mais la structure persiste.
• Prend en charge la création et la suppression d'index, y compris les index uniques.
• Visible via SHOW TABLES.

Points d'attention : les données doivent être supprimées manuellement (DELETE ou DROP), ce qui nécessite des privilèges DROP. La persistance de la structure sur disque peut entraîner un grand nombre de petits fichiers si elle est utilisée fréquemment.

Table temporaire

• Paramètre de contrôle : tmp_table_size (ex. 1024 Mo).
• Si la limite est atteinte, la table est écrite sur le disque.
• La définition et les données peuvent être en mémoire (jusqu'à une certaine limite).
• Accepte les champs TEXT et BLOB.
• Le nom de la table peut être identique entre sessions.
• Les données et la structure disparaissent à la fin de la session.
• Non visible avec SHOW TABLES.
• Non répliquée sur le serveur secondaire.

Note importante : les tables temporaires utilisent par défaut le moteur MyISAM, tandis que les tables mémoire utilisent le moteur MEMORY.

Dans notre cas, l'utilisation de tables temporaires présentait les avantages suivants :

• Isolation des sessions : plusieurs sessions peuvent utiliser le même nom de table sans interférence.
• Suppression automatique des données à la fin de la session.

Cependant, cela rendait le débogage difficile, car les données disparaissaient immédiatement. De plus, les tables temporaires et mémoire consomment de la mémoire, ce qui peut être difficile à maîtriser. Le paramètre max_tmp_tables (valeur par défaut : 32) limite le nombre de tables temporaires simultanées par client.

Index simple vs index composite

Une erreur courante consiste à créer un index séparé pour chaque colonne apparaissant dans la clause WHERE. Cette approche ne donne au mieux qu'un "index une étoile" selon le système de notation à trois étoiles décrit dans High Performance MySQL :

• Une étoile : les lignes pertinentes sont regroupées.
• Deux étoiles : l'ordre des index correspond à l'ordre de la requête.
• Trois étoiles : l'index contient toutes les colonnes nécessaires à la requête (index couvrant).

Pour obtenir un index optimal, il faut réfléchir à l'ordre des colonnes dans un index composite, voire créer un index couvrant. Par exemple, si les colonnes parent_id et test_id sont souvent utilisées ensemble dans des conditions, un index composite sur (parent_id, test_id) peut être plus performant que deux index séparés.

Une lecture approfondie de la documentation et des ouvrages comme High Performance MySQL reste indispensable pour maîtriser ces concepts.