Optimiser les requêtes MySQL sans douleur inutile

Quand une page se fige et qu’un panier s’échappe, la cause n’est pas toujours le serveur, mais la phrase SQL qui traîne. Le geste le plus payant consiste à Optimiser les requêtes MySQL pour rendre au système sa respiration, avant toute fuite en avant matérielle. Le diagnostic précis transforme une lenteur chronique en incident ponctuel.

Où se perd le temps d’une requête MySQL ?

Le temps s’évapore dans l’attente d’I/O, de verrous, de CPU, et parfois sur le réseau. Le repérer exige de découper la latence en segments concrets, mesurables, sur lesquels agir sans approximation.

Chaque requête traverse un petit théâtre technique : file d’attente côté connexion, lecture de pages depuis le disque ou le buffer pool, calcul des jointures, agrégations, tri, puis renvoi des lignes au client. Pareille à un fleuve, elle ralentit au moindre méandre : un index manquant, une cardinalité mal estimée, un tri externe, un verrou qui retarde la traversée. La compréhension vient lorsque les pièces s’alignent : un EXPLAIN qui annonce un range scan, un Performance Schema qui pointe des attentes sur le buffer pool, un slow log qui révèle un filesort non indexé. Là, l’effort se concentre, et chaque seconde reprend chair.

Comment lire un plan d’exécution sans se tromper ?

Un plan parle d’ordres de grandeur : lignes estimées, type d’accès, utilisation d’index, coût d’un tri. Sa lecture cherche la ligne qui gonfle les volumes, pas la décoration autour.

Face à EXPLAIN, l’œil entraîné suit la rivière depuis la table la plus sélective. Un access type en range sonne mieux qu’un full scan, mais seul le rows estimé confirme le gain. Quand un filtre reste “post” (Using where après lecture), la table souffre d’une lecture inutile. Les “ref” bien guidés par un index composé réduisent la dérive, tandis que “ALL” annonce un balayage total à sanctionner. Sur MySQL 8, EXPLAIN ANALYZE ajoute la chair des temps réels et des lignes lues ; l’estimation croisée à la réalité dévoile les illusions statistiques, souvent dues à une distribution biaisée des valeurs.

Cardinalité et sélectivité : la boussole

La bonne clé est celle qui coupe court au flot de lignes. Un index gagnant concentre la distribution ; un index “populaire” sur une colonne quasi binaire crée un mirage.

Un pourcentage infime de valeurs distinctes sur une colonne statutaire n’aidera guère ; l’index doit viser la colonne qui élimine le plus tôt. Le moteur estime à partir d’échantillons ; si ces statistiques vieillissent, il surestime ou sous-estime, choisit un plan trop optimiste, et la requête prend l’eau. La mise à jour des stats (ANALYZE TABLE), couplée à une sélection d’ordres pertinents dans les index composés, redresse la route. La sélectivité guide aussi l’ordre des prédicats, même si l’optimiseur réécrit ; l’expérience retient qu’un index commençant par la colonne la plus discriminante limite le balayage.

Filtrage vs post-filtrage : éviter les faux gains

Le meilleur filtre s’applique dans l’index, pas après coup. Les conditions non sargables obligent à tout lire puis à trier, comme un pêcheur qui relève tout le filet pour ne garder qu’un poisson.

Une fonction sur la colonne (DATE(created_at)) ou un calcul à gauche (col + 1 = 42) casse la recherche indexée. L’écriture équivalente (created_at >= ? AND created_at < ?) rend le prédicat navigable dans l’index. De même, un LIKE '%abc' empêche l’index d’aider ; en forçant le préfixe ('abc%') avec éventuellement un index sur une colonne normalisée (lower-cased), le plan bascule d’un scanning pénible à un range ciblé. L’économie n’est pas cosmétique : elle détermine la taille du jeu intermédiaire, donc les I/O et les tris.

Quels index créer pour gagner sans surcoût ?

Un index utile réduit les lectures et sert au tri. Il coûte en écriture et en mémoire ; l’objectif reste le minimum efficace, pas la collection.

Construire des index ressemble à disposer les pas d’un sentier dans un jardin : il s’agit d’anticiper les trajectoires réelles. Les requêtes les plus fréquentes dictent les colonnes de tête, les tris prescrivent l’ordre, et la couverture épargne des allers-retours vers la table. En revanche, chaque index alourdit l’insertion et la mise à jour ; multiplier les variantes pour “toutes les éventualités” finit par saboter la cadence d’écriture. La discipline consiste à lier chaque index à un patron de requête mesuré, et à retirer ceux qui ne servent plus.

Index composés et ordre qui compte

La tête de l’index sert de clé de tri et de filtre. Quand WHERE et ORDER BY s’alignent, la requête traverse la B-Tree sans filesort.

Un patron classique — WHERE status = ? AND created_at >= ? ORDER BY created_at DESC — appelle un index sur (status, created_at DESC). Inversement, un ordre de colonnes mal posé mène à de grandes lectures et à un tri externe. L’acuité se renforce en mesurant le “handler read next/first” et les “sort merge passes” pour vérifier l’effet réel du choix d’ordre, au-delà de l’intuition.

Index de couverture : quand la table devient optionnelle

Si l’index contient tout ce que la requête lit, le moteur n’a plus besoin de revenir à la table. Le gain se sent dans la latence et la stabilité.

Ajouter une colonne faiblement variable pour couvrir un SELECT change parfois tout ; la B-Tree suffit, le buffer pool respire, les accès aléatoires chutent. Toutefois, la colonne de plus devient un poids en écriture. La balance s’évalue par la fréquence de la requête et son rôle métier ; un rapport hebdomadaire n’exige pas le même arsenal qu’un endpoint critique en temps réel.

LIKE, collations et casse : éviter les pièges mous

Les recherches textuelles butent sur la collation et la casse. Standardiser et indexer le même “canon” évite les surprises de tri et de filtre.

Indexer une version normalisée (email_normalized) plutôt que jouer avec des fonctions au runtime améliore la sélectivité et la prévisibilité. Les collations accent-insensitive influencent l’ordre et l’usage des index ; un choix hâtif se paie en filesort récurrents. Un refactoring léger des colonnes et de leur collation évite des temp tables invisibles mais coûteuses.

• Associer chaque index à une requête mesurée (slow log, top N)
• Aligner WHERE et ORDER BY pour éviter le filesort
• Préférer la sélectivité en tête des index composés
• Retirer les index orphelins et surveiller le coût en écriture

Comment réécrire une requête pour la rendre prédictive ?

Écrire “sargable”, réduire la projection, filtrer tôt et paginer sans trous. Ce sont des gestes simples qui fixent un plafond de travail au lieu de subir une dérive.

Une requête prévisible ressemble à une trajectoire balisée : pas de fonctions sur les colonnes filtrées, pas de wildcard en tête, pas de SELECT étoile quand trois colonnes suffisent. Les sous-requêtes corrélées, séduisantes sur petit échantillon, s’étouffent à l’échelle ; une jointure bien indexée ou une CTE matérialisée logiquement fait gagner de la constance. La pagination par offset relit toujours les mêmes pages et décale l’effort ; la pagination à curseur (WHERE id < ?) saute directement au bon endroit. Le principe s’applique à l’agrégation : grouper sur une clé indexée et réduire tôt les lignes plie la courbe de latence.

• Projeter le strict nécessaire, viser l’index de couverture
• Filtrer par intervalles navigables, bannir les fonctions à gauche
• Remplacer OFFSET par curseur sémantique
• Pré-calculer les champs de recherche (normalisation, casse)

Que se passe-t-il côté moteur InnoDB ?

Le moteur orchestre mémoire, I/O et verrous. Comprendre ses priorités transforme une intuition SQL en amélioration systémique.

InnoDB vit par son buffer pool : tout passe par là, tant la lecture que l’écriture différée. Quand le cache est trop petit, chaque requête excave des pages depuis le disque, puis les perd aussitôt. Les flushes trop agressifs étranglent l’instant présent au nom de la durabilité. S’ajoutent les verrous de lignes, parfois élargis par une recherche non indexée, déclenchant des attentes en cascade. Une requête bien écrite, sans appui du moteur, reste vulnérable ; l’inverse aussi. Le réglage d’ensemble donne l’assise.

Verrous et isolation : la géographie du conflit

Le niveau d’isolation et la forme du parcours déterminent qui attend qui. Un range non indexé verrouille trop large, et c’est tout le carrefour qui bloque.

En READ COMMITTED, une requête évite certains pièges, mais un UPDATE sans index dérive en verrouillage de nombreuses lignes ; un SELECT FOR UPDATE hérite des mêmes travers. Le remède s’obtient rarement par changement d’isolation ; il vient d’un index qui localise le range et d’un ordre d’accès cohérent entre transactions sœurs. Le journal des deadlocks raconte l’histoire exacte ; on y entend la colonne manquante, l’ordre inversé, et parfois la pagination fautive qui retarde la libération.

Mémoire, temp tables et tri : l’invisible qui coûte

Les tables temporaires existent pour soulager, mais leur migration sur disque devient une enclave de lenteur. Mieux vaut les éviter que les gonfler.

Un GROUP BY sans index tombe vite en temp table, et si la mémoire allouée ne suffit pas, le disque prend le relais. Aligner la clé de groupement, réduire la projection, et, si besoin, répartir l’agrégat en étapes maîtrisées, ramène le calcul en mémoire. Les réglages globaux (tmp_table_size, max_heap_table_size) aident, mais c’est l’écriture de la requête qui arrête l’hémorragie.

Quand dénormaliser, partitionner ou mettre en cache ?

Quand la requête idéale reste lourde, l’architecture prend le relais. La décision doit être chirurgicale et mesurée, pour ne pas déplacer la dette ailleurs.

La dénormalisation condense une lecture coûteuse en un accès simple, mais elle s’accompagne d’une discipline d’écriture. Le partitionnement borne le volume scanné à la partition active, encore faut-il que la clé de partition colle aux filtres réels. Le cache applicatif coupe court au trafic répétitif ; il exige une invalidation crédible. Plutôt qu’un réflexe, ce sont des outils à saisir quand les preuves l’exigent et que la charge le justifie.

• Choisir la technique alignée sur les filtres et l’accès réel
• Documenter l’invalidation et le cycle de vie des données
• Mesurer avant/après avec le même trafic et la même période

Comment mesurer et garder le cap en production ?

Mesurer, comparer, puis budgéter une latence cible par requête critique. La performance devient un contrat, pas une espérance.

Le slow query log révèle le haut du panier à traiter ; pt-query-digest regroupe, trie, raconte l’histoire derrière le pic. Le Performance Schema expose les attentes concrètes : I/O, verrous, stages de tri. Le schéma sys synthétise les métriques en vues utiles. Pour chaque requête vitale, un budget de temps se fixe : tant pour le réseau, tant pour le plan, tant pour l’I/O. EXPLAIN ANALYZE affine la réalité, et un tableau de bord suit la médiane et le p95, parce que c’est la queue qui blesse l’expérience. Les tests de charge rejouent le trafic réel, pas un simple tir au canon, afin d’éviter les optimisations de laboratoire qui fanent au premier lundi de pointe.

Conclusion : faire taire le bruit, laisser parler les données

L’optimisation de requêtes n’a rien d’un tour de passe-passe. C’est un travail de montre : ouvrir, observer la danse des engrenages, ajuster l’axe, changer la dent qui accroche, puis refermer avec douceur. Les symptômes se ressemblent, mais la cause, elle, se niche dans un détail mesurable.

Quand l’écriture devient sargable, que l’indexation respecte l’usage réel, que le moteur trouve de la mémoire pour travailler et que la mesure cadence les décisions, la plate-forme cesse de lutter contre elle-même. Les pages cessent de se figer, les paniers poursuivent leur route, et le temps repris se comptabilise autant en satisfaction qu’en marge. L’essentiel, au fond, tient en deux idées : laisser les index guider la lecture, et ne jamais discuter sans mesures à la main.