Pourquoi ma base de données est lente ? Trouver et corriger les requêtes SQL lentes

Toutes les histoires de bases de données lentes commencent pareil. L’application démarre, tout est fluide, l’équipe est contente. Six mois plus tard, une page qui prenait 200 millisecondes en prend deux secondes. Un an après, quelqu’un s’excuse auprès d’un client pendant qu’un rapport charge. Personne n’a rien changé de « majeur » — les données ont simplement grandi, et des requêtes inoffensives sur 1 000 lignes sont devenues coûteuses sur 1 000 000.

Je fais de l’optimisation de bases de données mon métier, et la vérité rassurante est celle-ci : une base lente n’est presque jamais lente partout. Ce sont généralement cinq à dix requêtes qui causent 90 % des dégâts. Les trouver, les corriger proprement, et toute l’application semble neuve. Voici la méthode que j’applique réellement.

Étape 1 : arrêter de deviner — mesurer

La pire façon de corriger une base lente est d’ajouter des index au hasard, à l’intuition. Le premier travail est la preuve :

• Activez le journal des requêtes lentes. log_min_duration_statement sous PostgreSQL (je commence à 500 ms) ou le slow query log de MySQL enregistrent chaque requête qui dépasse le seuil, avec sa durée réelle. En une journée, vous avez une liste de suspects, classée par douleur.

• Agrégez avant d’optimiser. Des outils comme pg_stat_statements (PostgreSQL) montrent le temps total par motif de requête — une requête médiocre exécutée 50 000 fois par jour fait plus mal qu’une requête terrible exécutée deux fois.

• Vérifiez les signes vitaux. Nombre de connexions, taux de cache, taille des tables et des plus gros index. Parfois, la « requête lente » est le symptôme de quelque chose de plus bête, comme chaque serveur d’application ouvrant son propre flot de connexions sans pool devant.

Ce n’est que lorsque vous savez quelles requêtes, à quelle fréquence et à quel coût que vous touchez à quoi que ce soit.

Étape 2 : lire le plan d’exécution comme un détective

Pour chaque requête suspecte, EXPLAIN ANALYZE est la salle d’interrogatoire. Il exécute la requête et montre ce que la base a réellement fait — ce qui est souvent différent de ce que vous supposiez. Les expressions que je traque :

• Seq Scan sur une grande table — la base a lu toute la table faute d’index utilisable pour le filtre. Pas toujours fautif (petites tables, majorité de lignes correspondantes), mais sur une grosse table avec un filtre sélectif, c’est le suspect numéro un.

• Rows removed by filter par milliers ou millions — la base récupère des montagnes de lignes pour presque toutes les jeter. Un index adapté au filtre (et au tri) met fin à cela.

• Tris sur disque (external merge) — le tri ou la jointure a débordé sur le disque parce que la mémoire de travail manquait ou que les estimations de lignes étaient très fausses (souvent le signe de statistiques périmées).

• Boucles imbriquées avec d’énormes nombres de lignes — généralement le problème N+1 déguisé, ou une jointure à qui il manque son index côté interne.

Le plan ne révèle pas seulement le problème ; il dit quelle correction lui correspond.

Étape 3 : appliquer les corrections qui changent vraiment la donne

Par ordre de fréquence à laquelle elles sauvent la situation dans mon travail :

Le bon index — pas juste un index. Un index sur (status, created_at) sert la requête qui filtre par statut et trie par date ; un index sur (created_at, status) souvent non. Les index composites suivent la règle du préfixe le plus à gauche, les index partiels (WHERE status = 'pending') peuvent être minuscules et chirurgicaux, et un index qu’aucune requête n’utilise est une pure taxe sur les écritures — supprimez aussi ceux-là. Je détaille le sujet dans mon guide des index PostgreSQL.

Tuer le N+1. Une requête pour la liste, puis une requête par ligne pour ses détails : anodin à 10 lignes, catastrophique à 1 000. Le chargement anticipé (eager loading) ou une vraie jointure ramènent des centaines d’allers-retours à une seule requête.

Arrêter de récupérer ce que vous n’affichez pas. SELECT * sur des tables larges, charger 10 000 lignes pour paginer côté application, renvoyer des colonnes de texte intégral pour une vue liste — la base et le réseau paient pour des données que personne ne voit. Sélectionnez les colonnes nécessaires ; paginez en SQL.

Corriger la forme de la requête. Les fonctions autour des colonnes indexées (WHERE DATE(created_at) = ...) neutralisent les index ; la réécriture en condition d’intervalle les ressuscite. Les chaînes de OR, les recherches LIKE '%terme%' à joker initial et les conversions de type implicites sont les autres suspects habituels.

Ensuite, et seulement ensuite, régler le serveur. Buffers partagés, mémoire de travail, pool de connexions (PgBouncer), comportement de l’autovacuum. La configuration multiplie les bonnes requêtes ; elle ne peut pas sauver les mauvaises — c’est pourquoi elle vient en dernier, pas en premier.

Étape 4 : prouver, puis maintenir la vitesse

Chaque correction est mesurée : la même requête, avant et après, des chiffres réels dans un tableau. « Ça semble plus rapide » n’est pas un résultat ; « 2 300 ms → 14 ms à volume de données égal » en est un. Cette discipline avant/après est le cœur de mon service d’architecture et d’optimisation de bases de données, parce qu’elle dit aussi quand s’arrêter — il arrive un point où une heure de réglage supplémentaire achète 3 millisecondes que personne ne sentira jamais.

Puis empêchez la rechute : laissez le journal des requêtes lentes actif en permanence, alertez sur les seuils, et revoyez les principales requêtes chaque mois. Une base devient rarement lente en un jour ; elle le devient en cent petits commits, et le journal est la façon d’attraper la tendance pendant qu’elle coûte encore peu cher.

Quand la réponse est « ce n’est pas (que) la base »

Section honnêteté : parfois, le profilage montre que la base est innocente — le temps part dans une API bavarde, une page non mise en cache qui tout recalcule, ou un serveur à l’autre bout de la planète par rapport à ses utilisateurs. L’audit dit quelle couche est coupable avant que vous dépensiez sur la mauvaise. Mon article sur pourquoi votre site Next.js est lent couvre la moitié front-end de ce diagnostic.

Si votre application ralentit de mois en mois, n’achetez pas encore un plus gros serveur. Décrivez-moi les symptômes — un audit propre prend quelques jours, et dans la plupart des cas la correction tient en une poignée de requêtes, pas en une refonte.