1. Qu'est-ce qu'un index dans MySQL ?

Un index est une structure de données utilisée par le moteur de stockage pour accélérer la recherche d'enregistrements, similaire à un index dans un livre.

2. Types d'index supportés par InnoDB

InnoDB gère plusieurs types d'index : l'index primaire (PRIMARY), l'index standard (INDEX), l'index unique (UNIQUE) et les index composites. Ils se divisent en deux catégories : l'index primaire, appelé index clusterisé (clustered index), et les index secondaires (secondary index) pour tous les autres.

3. Structure des index dans InnoDB

InnoDB utilise un modèle d'arbre B+ pour ses index. Considérons une table employees avec les champs emp_id (clé primaire auto-incrémentée), full_name (nom complet) et department (département). Insérons quatre enregistremenst : ("Alice Dupont", "Marketing"), ("Bob Martin", "Ventes"), ("Claire Rousseau", "IT"), ("David Leroy", "Finance").

CREATE TABLE `employees` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `full_name` varchar(50) NOT NULL,
  `department` varchar(30) NOT NULL,
  PRIMARY KEY (`emp_id`),
  INDEX `idx_name` (`full_name`)
) ENGINE=InnoDB;

INSERT INTO employees (full_name, department) VALUES
('Alice Dupont', 'Marketing'),
('Bob Martin', 'Ventes'),
('Claire Rousseau', 'IT'),
('David Leroy', 'Finance');

Dans InnoDB, chaque index correspond à un arbre B+. L'index primaire stocke les données complètes dans ses feuilles, tandiss que les index secondaires stockent la valeur de la clé primaire. Par exemple, pour la requête :

SELECT department FROM employees WHERE full_name = 'Claire Rousseau';

Le processus implique : 1) Trouver le nœud dans l'arbre index full_name pour obtenir emp_id = 3. 2) Rechercher dans l'arbre index primaire pour récupérer les données complètes. 3) Extraire la valeur department. Ce retour à l'index primaire est appelé requête de re-table (table lookup), ce qui peut impacter les performances.

4. Définition de l'index de couverture

Un index de couverture (covering index) permet de récupérer toutes les colonnes requises directement à partir d'un index secondaire, évitant ainsi la recherche dans l'index primaire. Cela réduit le nombre d'accès aux arbres et améliore l'efficacité des requêtes.

5. Mise en œuvre d'un index de couverture

Supposons une exigence métier fréquente : obtenir le département d'un employé par son nom complet. Nous pouvons remplaecr l'index existant par un index composite sur full_name et department.

ALTER TABLE employees DROP INDEX idx_name;
ALTER TABLE employees ADD INDEX idx_name_dept(full_name, department);

La même requête :

SELECT department FROM employees WHERE full_name = 'Claire Rousseau';

Exécutée avec l'index composite, le processus simplifié : 1) Trouver le nœud dans l'arbre index idx_name_dept pour full_name = 'Claire Rousseau'. 2) La valeur department est disponible directement dans l'index, retournant "IT" sans re-table.

6. Vérification de l'utilisation d'un index de couverture

Pour confirmer qu'une requête utilise un index de couverture, inspectez la colonne Extra de la sortie EXPLAIN. La présence de Using index indique que l'index de couverture a été appliqué avec succès.

Par exemple, en exécutant :

EXPLAIN SELECT department FROM employees WHERE full_name = 'Claire Rousseau';

L'information Using index dans les résultats confirme l'optimisation.