Performance De Base De Données : Trouver La Requête Qui Vous Tue

Le travail sur la performance de base de données commence presque toujours par quelqu’un qui propose une instance plus grosse, et il finit presque toujours par la découverte qu’une seule requête faisait un balayage séquentiel de 4 millions de lignes à chaque chargement de page. Le matériel n’a jamais été la contrainte. Le plan d’exécution, si.

Le schéma se répète assez régulièrement pour mériter d’être posé comme hypothèse par défaut. Quand une application est lente et que la base travaille, la cause tient presque toujours à un petit nombre de requêtes précises et non à un manque général de capacité. Monter en gamme masque le problème exactement le temps qu’il faut à la table pour grossir de nouveau.

Mesurez avant de changer quoi que ce soit. Optimiser une requête que l’on a devinée, c’est ainsi que des équipes passent une semaine à ajouter des index qui ralentissent les écritures sans accélérer la moindre lecture. Toute base sait vous dire quelles instructions consomment le plus de temps cumulé. Commencez par là, corrigez la première, mesurez de nouveau. Deux ou trois itérations suffisent en général à clore l’incident.

La performance de base de données commence par trouver la requête

Le temps cumulé compte davantage que le pire cas. Une requête qui prend deux secondes et s’exécute deux fois par jour n’a aucune importance. Une requête qui prend quarante millisecondes et s’exécute 8 000 fois par minute est votre problème, et elle n’apparaîtra jamais dans un journal des requêtes lentes réglé sur un seuil d’une seconde.

Dans Postgres, l’extension pg_stat_statements agrège exactement cela : nombre d’appels, temps cumulé et temps moyen par instruction normalisée. Triez par temps cumulé et le coupable figure généralement dans les trois premières lignes. MySQL propose une agrégation comparable via le schéma de performance.

Deux vérifications méritent d’être faites avant de conclure que la requête elle-même est en cause. Est-elle lente à chaque exécution, ou seulement à certaines heures, ce qui pointe vers de la contention plutôt que vers le plan ? Et est-elle lente isolément, ou uniquement sous forte concurrence, ce qui pointe vers des verrous ou vers une limite de connexions ?

Lisez le plan au lieu de deviner

Une fois l’instruction identifiée, demandez à la base comment elle compte l’exécuter. Postgres expose cela via EXPLAIN , et la variante importante est EXPLAIN ANALYZE, qui exécute réellement la requête et rapporte des temps mesurés plutôt que des estimations.

Trois éléments de cette sortie portent l’essentiel du signal.

Un balayage séquentiel sur une grande table. La base lit chaque ligne. Sur une petite table, c’est correct et rapide. Sur une grande, cela signifie qu’aucun index utilisable ne couvre la condition que vous avez écrite, ou que le planificateur a jugé l’index peu rentable.

Un écart important entre le nombre de lignes estimé et le nombre réel. Le planificateur choisit sa stratégie à partir de statistiques, donc lorsque son estimation se trompe de plusieurs ordres de grandeur, il choisit mal pour des raisons qui n’ont rien à voir avec votre requête. Des statistiques périmées en sont une cause fréquente et facile à corriger.

Un temps concentré sur un seul nœud. Les plans sont des arbres, et la correction appartient au nœud qui a consommé le temps. Optimiser quoi que ce soit d’autre ne change rien.

Le réflexe d’ajouter un index dès que l’on voit un balayage séquentiel est souvent le bon, et il mérite malgré tout trente secondes de retenue, car la raison pour laquelle l’index n’est pas utilisé compte parfois davantage que son absence.

Pourquoi les index n’aident pas toujours

Un index qui existe n’est pas un index qui sert.

La condition n’est pas sargable. Envelopper une colonne dans une fonction, ou lui appliquer un calcul, empêche généralement l’index posé sur cette colonne d’être utilisé, car l’index stocke les valeurs de la colonne et non les valeurs transformées. Réécrire la condition pour laisser la colonne nue rétablit généralement son usage.

L’ordre des colonnes dans un index composite est mauvais. Un index composite sert les requêtes qui utilisent ses colonnes de tête. Un index sur une colonne puis une autre n’aide pas une requête qui filtre uniquement sur la seconde, et l’erreur revient sans cesse.

Le planificateur estime qu’un balayage coûte moins cher. Si une requête renvoie une grande fraction de la table, la lire séquentiellement est réellement plus rapide que de sauter à travers un index. Ce comportement est correct, et la correction consiste à renvoyer moins de lignes.

Les statistiques sont périmées. Après un chargement en masse ou une suppression importante, la représentation des données dont dispose le planificateur peut être gravement fausse tant que les statistiques ne sont pas rafraîchies.

Et chaque index a un coût. Les écritures doivent le maintenir, et il occupe une mémoire qui servirait autrement à mettre les données en cache. Une table portant quinze index en compte généralement plusieurs dont personne n’a besoin, et chacun d’eux ralentit chaque insertion.

Le problème N+1 reste la première cause à elle seule

La lenteur applicative vient plus souvent de là que de n’importe quel problème de plan, et elle n’apparaît jamais comme une requête lente puisque chaque requête prise isolément est rapide.

La forme est familière. On récupère une liste de 100 enregistrements, puis on boucle dessus pour aller chercher les données liées de chacun. Le résultat, ce sont 101 allers-retours là où un ou deux auraient suffi. Chaque requête revient en trois millisecondes et la page met quand même une demi-seconde, parce que le coût réside dans les allers-retours et non dans le travail.

Les mappeurs objet-relationnel rendent cette erreur facile à écrire sans le vouloir, puisque l’accès aux données liées ressemble à la lecture d’une propriété plutôt qu’à un appel de base de données. La correction consiste à charger les données liées en une seule requête, en même temps que l’ensemble parent, ce que tout ORM mature sait faire et ce que la plupart ne font pas par défaut.

La détection est simple : comptez les requêtes par requête HTTP entrante. Si une page émet un nombre de requêtes proportionnel au nombre d’éléments affichés, vous l’avez trouvé. C’est aussi la vérification la plus rentable quand une application est lente à la périphérie, comme l’explique notre guide Cloudflare Hyperdrive , car les allers-retours coûtent bien plus cher lorsque la distance augmente.

Connexions et contention

Deux problèmes qui ressemblent à de la lenteur sans en être.

L’épuisement des connexions. Toute base impose un plafond de connexions simultanées, et chacune consomme de la mémoire. Quand une application en ouvre plus que le pool ne l’autorise, les requêtes s’accumulent en file d’attente et l’application paraît lente alors que la base reste inactive. Le symptôme est une latence applicative élevée avec un processeur de base de données peu chargé, et la correction s’appelle pooling, pas machine plus grosse.

La contention sur les verrous. Une transaction longue qui détient un verrou bloque tout ce qui attend derrière elle. La cause habituelle est une transaction laissée ouverte pendant un travail qui n’a pas besoin de la base, par exemple un appel HTTP vers un autre service. Gardez les transactions courtes et limitées au travail de base de données lui-même.

Les deux cas méritent d’être écartés tôt, car les deux se lisent facilement comme un problème de requête et aucun ne se règle par un index.

Que faire, et dans quel ordre

Trouvez les instructions qui consomment le plus de temps cumulé. Lancez EXPLAIN ANALYZE sur la pire et lisez où le temps part réellement. Comptez les requêtes par requête entrante pour écarter le N+1 avant d’optimiser quoi que ce soit. Rafraîchissez les statistiques avant d’ajouter un index, car c’est parfois toute la correction. Ajoutez ensuite l’index le plus étroit qui serve la condition, puis mesurez de nouveau.

Mecanik le fait dans le cadre de nos prestations de développement logiciel , et l’issue est presque toujours la même : deux ou trois requêtes étaient responsables, la correction était minime, et la machine plus grosse que personne n’avait achetée n’a jamais été nécessaire.

Articles en relation: Versionnage d’API : quand casser et comment ne pas le faire , Comment créer une application web en 2026 , Stockage des mots de passe : que choisir en 2026 , Développement logiciel sur mesure UK - Guide de l’acheteur .

Questions fréquentes

Comment trouver la requête qui ralentit mon application ? Triez par temps cumulé plutôt que par pire cas. Une requête de quarante millisecondes exécutée 8 000 fois par minute coûte bien plus qu’une requête de deux secondes exécutée deux fois par jour, et elle n’apparaîtra jamais dans un journal des requêtes lentes réglé sur un seuil d’une seconde. Dans Postgres, pg_stat_statements agrège les appels et le temps cumulé par instruction ; MySQL offre la même chose via le schéma de performance.

Que faut-il regarder dans la sortie d’EXPLAIN ANALYZE ? Trois éléments portent l’essentiel du signal : un balayage séquentiel sur une grande table, signe qu’aucun index utilisable n’existe ; un écart important entre lignes estimées et lignes réelles, signe que le planificateur travaille sur de mauvaises statistiques ; et un temps concentré sur un seul nœud de l’arbre du plan, qui est l’endroit où la correction doit être faite. Optimiser un autre nœud ne change rien.

Pourquoi mon index n’est-il pas utilisé ? Généralement pour l’une de ces quatre raisons. La condition enveloppe la colonne dans une fonction ou un calcul, donc l’index ne correspond plus. L’index composite range ses colonnes dans un ordre qui ne sert pas la requête. La requête renvoie une fraction de la table assez grande pour qu’un balayage soit réellement moins coûteux. Ou les statistiques sont périmées après un chargement en masse ou une suppression.

Qu’est-ce qu’un problème de requêtes N+1 ? Récupérer une liste d’enregistrements, puis émettre une requête distincte pour les données liées de chacun, ce qui produit 101 allers-retours là où un ou deux auraient suffi. Cela n’apparaît dans aucun journal des requêtes lentes puisque chaque requête est rapide ; le coût réside dans les allers-retours. Détectez-le en comptant les requêtes par requête entrante et en cherchant un nombre proportionnel aux éléments affichés.

Un serveur de base de données plus puissant corrige-t-il les requêtes lentes ? Rarement, et seulement de façon temporaire. Quand une application est lente et que la base travaille, la cause tient presque toujours à un petit nombre de requêtes précises plutôt qu’à un manque de capacité, donc monter en gamme masque le problème jusqu’à ce que la table grossisse de nouveau. Une latence applicative élevée avec un processeur de base de données peu chargé indique plutôt un pool de connexions épuisé, ce que le matériel supplémentaire ne règle pas.