L'ordre des colonnes dans un index composite change tout
Un index sur (client_id, statut, date_creation) ne sert pas les mêmes requêtes qu'un index sur (statut, client_id, date_creation). MySQL exploite un index composite de gauche à droite : il peut filtrer efficacement sur client_id seul, ou sur client_id + statut, mais pas sur statut seul si cette colonne n'est pas en première position.
Règle pratique : placez en premier les colonnes utilisées en égalité (=), puis celles utilisées en plage (>, BETWEEN), et enfin celles utilisées uniquement pour le tri (ORDER BY).
Vérifier avec EXPLAIN
EXPLAIN SELECT id, date_creation
FROM commandes
WHERE client_id = 42 AND statut = 'payee'
ORDER BY date_creation DESC;
Regardez trois colonnes du résultat : type (idéalement ref ou range, jamais ALL), key (l'index réellement choisi, pas seulement celui listé dans possible_keys), et Extra. Si Extra affiche Using filesort alors que la requête trie déjà via l'index, ou Using index sans Using filesort, c'est le signe d'un index couvrant : toutes les colonnes demandées sont présentes dans l'index, donc MySQL n'a même pas besoin d'aller lire la table.
L'isolation par défaut piège encore du monde
InnoDB utilise REPEATABLE READ par défaut, pas READ COMMITTED comme PostgreSQL. Concrètement, une transaction voit un instantané figé au premier SELECT, même si d'autres transactions commitent entre-temps. Cela évite les lectures non répétables mais peut surprendre lors de contrôles applicatifs de type "vérifier puis insérer". Pour ces cas, préférez un verrou explicite plutôt qu'une relecture :
START TRANSACTION;
SELECT quantite FROM stock WHERE produit_id = 7 FOR UPDATE;
UPDATE stock SET quantite = quantite - 1 WHERE produit_id = 7;
COMMIT;
FOR UPDATE verrouille la ligne lue jusqu'au commit, empêchant deux transactions concurrentes de décrémenter le même stock sur une base obsolète.
Fonctions fenêtrées pour éviter les sous-requêtes corrélées
Depuis MySQL 8.0, les window functions remplacent avantageusement bien des sous-requêtes lentes :
SELECT client_id, montant,
RANK() OVER (PARTITION BY client_id ORDER BY montant DESC) AS rang
FROM commandes;
Cette requête classe les commandes de chaque client sans jointure ni sous-requête répétée par ligne, ce qui reste bien plus lisible et souvent plus rapide que l'équivalent en GROUP BY imbriqué.
Réflexe à garder
- Passez systématiquement vos requêtes lentes dans
EXPLAIN ANALYZE(disponible depuis MySQL 8.0.18) pour voir le temps réel par étape, pas seulement le plan estimé. - Auditez les index inutilisés avec
sys.schema_unused_indexes: chaque index coûte en écriture. - Ne changez le niveau d'isolation global que si vous comprenez son impact sur les verrous en attente (
SHOW ENGINE INNODB STATUS).