Index SQL et EXPLAIN

Un index SQL accélère les recherches dans une table : au lieu de lire toutes les lignes, le SGBD consulte une structure triée qui le mène directement aux bonnes. Ce chapitre explique quand créer un index avec CREATE INDEX, comment vérifier avec EXPLAIN qu'une requête s'en sert, et ce qui l'en empêche. Les exemples portent sur les tables de la base d'exemple parc.

Parcours complet ou index

Sans aide, pour trouver les tickets ouverts le 5 octobre, le SGBD lit toutes les lignes de la table et teste chacune : c'est un parcours complet (full table scan). Avec 14 tickets, c'est instantané. Avec 3 millions de tickets accumulés en dix ans, chaque recherche lit 3 millions de lignes.

Un index est une structure annexe, triée sur une ou plusieurs colonnes, qui pointe vers les lignes. Comme l'index d'un livre : au lieu de feuilleter toutes les pages, tu cherches le mot dans une liste triée, qui te donne le numéro de page. InnoDB range ses index dans des arbres B+ : la racine renvoie vers des pages intermédiaires, qui renvoient vers des feuilles triées. Chaque page de 16 Kio contient des centaines de clés : pour des millions de lignes, trois ou quatre lectures de pages suffisent.

Dans InnoDB, la table elle-même est rangée dans l'ordre de sa clé primaire : c'est l'index en grappe (clustered index). Les autres index, dits secondaires, stockent la valeur indexée et la clé primaire de la ligne ; une recherche par un index secondaire passe donc ensuite par la clé primaire pour lire le reste de la ligne. Un argument de plus pour des clés primaires courtes.

Les index créés automatiquement

InnoDB crée un index pour chaque clé primaire, chaque contrainte UNIQUE et chaque clé étrangère qui n'en a pas déjà un.

SHOW INDEX FROM ticket;

(Extrait : SHOW INDEX affiche une quinzaine de colonnes.) Les index des clés étrangères portent le nom de la contrainte. Ils accélèrent les jointures et la vérification de l'intégrité référentielle : quand tu supprimes un employé, MySQL doit vérifier rapidement qu'aucun ticket ne le référence.

CREATE INDEX

-- Index simple
CREATE INDEX idx_ticket_ouverture ON ticket (date_ouverture);

-- Index composite : l'ordre des colonnes compte
CREATE INDEX idx_ticket_statut_priorite ON ticket (statut, priorite);

-- Index unique : accélère et interdit les doublons (un technicien ne commence
-- pas deux interventions à la même minute)
CREATE UNIQUE INDEX uq_intervention_tech_date ON intervention (id_technicien, date_intervention);

-- Supprimer un index
DROP INDEX idx_ticket_ouverture ON ticket;

ALTER TABLE ticket ADD INDEX idx_ticket_ouverture (date_ouverture); est une autre écriture. Un index unique et une contrainte UNIQUE reviennent au même dans MySQL : la contrainte crée un index unique.

Un index composite est trié sur la première colonne, puis sur la deuxième à l'intérieur de chaque valeur de la première, comme un annuaire trié par nom puis par prénom. Il sert donc pour une recherche sur le début de sa liste de colonnes : l'index (statut, priorite) accélère WHERE statut = 'ouvert' et WHERE statut = 'ouvert' AND priorite = 'haute', mais pas WHERE priorite = 'haute' seul. C'est la règle du préfixe gauche. Place en premier les colonnes testées par égalité, puis celle qui sert à un intervalle ou à un tri.

Quand un index contient toutes les colonnes dont la requête a besoin, MySQL lit l'index sans lire la table : c'est un index couvrant. SELECT statut, COUNT(*) FROM ticket GROUP BY statut peut se contenter de l'index (statut, priorite).

Quand indexer, quand s'abstenir

Chaque index a un coût : il occupe de la place et ralentit chaque INSERT, UPDATE et DELETE, qui doivent le tenir à jour. Un index se justifie par une requête fréquente et lente, constatée avec EXPLAIN ou le journal des requêtes lentes.

Lire un plan d'exécution avec EXPLAIN

EXPLAIN placé devant un SELECT (ou un UPDATE, un DELETE) affiche le plan d'exécution choisi par l'optimiseur, sans exécuter la requête.

EXPLAIN SELECT * FROM ticket WHERE id_ticket = 9;

Les colonnes à lire en priorité :

Les valeurs de type, de la meilleure à la pire :

Sur EXPLAIN SELECT * FROM ticket WHERE titre LIKE '%VPN%';, tu lis type = ALL et key = NULL : aucun index ne peut servir. Dans Extra, Using filesort signale un tri sans index et Using temporary une table temporaire, deux opérations coûteuses sur de gros volumes.

EXPLAIN ANALYZE (MySQL 8.0.18 et plus) exécute la requête et affiche, étape par étape, le temps réel et le nombre réel de lignes : c'est l'outil pour comprendre une requête lente. EXPLAIN FORMAT=TREE affiche le plan sous forme d'arbre, plus lisible pour les jointures. Ces deux formes sont propres à MySQL : MariaDB écrit ANALYZE SELECT ... (ou ANALYZE FORMAT=JSON SELECT ...) et n'a pas de format arbre.

Ce qui empêche d'utiliser un index

Un index est trié sur la valeur brute de la colonne. Tout ce qui transforme la colonne avant de la comparer l'empêche de servir.

LIKE 'Accès%' utilise l'index, car le début de la chaîne est fixe : c'est un intervalle de l'ordre alphabétique. LIKE '%VPN' ne le peut pas.

Depuis MySQL 8.0.13, un index fonctionnel indexe le résultat d'une expression, quand on ne peut pas réécrire la condition : CREATE INDEX idx_ticket_annee ON ticket ((YEAR(date_ouverture)));. Note la double parenthèse. MariaDB n'a pas d'index fonctionnel : on y indexe une colonne générée, ALTER TABLE ticket ADD annee SMALLINT AS (YEAR(date_ouverture)) VIRTUAL, ADD INDEX idx_ticket_annee (annee);, puis on filtre sur annee.

Le problème N+1

La lenteur vient souvent de l'application, pas du SQL. Une page PHP affiche les tickets avec le nom du demandeur :

$tickets = $pdo->query('SELECT id_ticket, titre, id_demandeur FROM ticket')->fetchAll();
foreach ($tickets as $t) {
    $stmt = $pdo->prepare('SELECT nom FROM employe WHERE id_employe = ?');
    $stmt->execute([$t['id_demandeur']]);
    echo $t['titre'] . ' - ' . $stmt->fetchColumn() . PHP_EOL;
}

Une requête pour la liste, puis une requête par ticket : N + 1 requêtes. Avec 500 tickets, 501 allers-retours vers le serveur, chacun rapide, mais dont les latences s'additionnent. La correction : une seule requête avec une jointure.

$sql = 'SELECT t.id_ticket, t.titre, e.nom
        FROM ticket t JOIN employe e ON e.id_employe = t.id_demandeur';
foreach ($pdo->query($sql) as $t) {
    echo $t['titre'] . ' - ' . $t['nom'] . PHP_EOL;
}

Les ORM provoquent souvent ce problème sans qu'on le voie, en chargeant les objets liés un par un.

Exercices

SELECT id_ticket, titre, date_ouverture
FROM ticket
WHERE statut = 'ouvert' AND date_ouverture >= '2026-10-01'
ORDER BY date_ouverture;

CREATE INDEX idx_ticket_statut_ouverture ON ticket (statut, date_ouverture);

CREATE INDEX idx_intervention_date ON intervention (date_intervention);

SELECT * FROM intervention
WHERE date_intervention >= '2026-01-01' AND date_intervention < '2027-01-01'
  AND duree_min > 60
ORDER BY date_intervention;