La lenteur d'une requête SQL peut provenir de multiples facteurs, qu'il convient d'analyser selon deux scénarios distincts : soit la requête est généralement rapide mais connaît des ralentissements ponctuels, soit elle est chroniquement lente malgré des données stables.

Cas 1 : Ralentissements intermittents

Quand une requête fonctionne normalement la plupart du temps, ses baisses de performance occasionnelles ne viennent généralement pas de son écriture, mais d'événements externes.

Synchronisation des pages sales

MySQL utilise une architecture où les modifications sont d'abord appliquées en mémoire (buffer pool), puis enregistrées dans le redo log avant d'être persistées sur disque. Lorsque le redo log atteint sa capacité maximale, le moteur doit suspendre momentanément les opérations en cours pour vider les données modifiées vers le stockage permanent. Ce phénomène de checkpoint forcé peut bloquer temporairement l'exécution de requêtes habituellement fluides.

Contentions de verrouillage

La requête peut être en attente d'accès à une table ou une ligne verrouillée par une autre transcation. Pour identifier ce blocage, la commande SHOW ENGINE INNODB STATUS permet d'observer les verrous en cours et les transactions en attente.

Cas 2 : Lenteur systématique

Une requête chroniquement lente révèle généralement des problèmes structurels dans son écriture ou dans la conception des index.

Absence d'uitlisation des index

Considérons une table définie ainsi :

CREATE TABLE `produits` (
  `reference` INT NOT NULL,
  `quantite` INT DEFAULT NULL,
  `prix` INT DEFAULT NULL,
  PRIMARY KEY (`reference`)
) ENGINE=InnoDB;

Index inexistant

Une recherche sur quantite sans index approprié oblige le moteur à parcourir l'intégralité de la table :

SELECT * FROM produits WHERE quantite BETWEEN 50 AND 5000;

Index présent mais contourné

Même avec un index sur quantite, certaines écritures empêchent son utilisation. Par exemple :

SELECT * FROM produits WHERE quantite - 10 = 90;

L'optimiseur ne peut pas exploiter l'index car la colonne subit une transformation arithmétique. La formulation correcte préserve l'intégrité de l'index :

SELECT * FROM produits WHERE quantite = 100;

Application de fonctions

De même, l'encapsulation dans une fonction désactive l'index :

SELECT * FROM produits WHERE ROUND(prix, 0) = 100;

Sélection inappropriée d'index par l'optimiseur

MySQL peut décider de ne pas utiliser un index disponible s'il estime qu'un parcours complet serait plus efficace. Cette estimation repose sur la cardinalité — le nombre de valeurs distinctes dans l'index.

Le moteur échantillonne les données pour évaluer cette cardinalité. Un échantillon non représentatif peut conduire à une sous-estimation drastique, poussant l'optimiseur à ignorer un index pertinent au profit d'un scan complet coûteux.

La commande ANALYZE TABLE produits; force le recalcul des statistiques d'index. En cas de mauvaise sélection persistante, SELECT * FROM produits FORCE INDEX(idx_quantite) WHERE ... impose l'utilisation d'un index spécifique.

Récapitulatif des causes