Une requête lente n’est pas juste un chiffre de plus sur un graphique : c’est un clic qui traîne, un panier qui abandonne, une facture cloud qui grimpe. L’optimisation des performances en base (SQL/NoSQL) begin par une détection fiable, se poursuit par un diagnostic rigoureux, puis se gagne avec des choix de modélisation et d’architecture cohérents. Tu vas voir comment reconnaître et mesurer les requêtes lentes, utiliser les bons outils, éviter les anti-patterns qui coûtent cher et appliquer des stratégies ciblées sans mettre en péril la production.
Définir Et Détecter Les Requêtes Lentes
Signes D’Alerte Et Seuils
Tu ne peux pas optimiser ce que tu ne définis pas. Begin par un seuil clair. Côté SQL, MySQL fournit long_query_time (slow query log), PostgreSQL s’appuie sur log_min_duration_statement. En pratique, pour un parcours utilisateur interactif, vise des requêtes en dessous de 100–200 ms au p95 et <500 ms au p99. Pour des traitements batch, des latences de plusieurs secondes peuvent être acceptables, mais surveille la dérive.
Les signaux d’alerte ne se résument pas au temps total. Surveille aussi le nombre de lectures logiques (buffer/cache) vs physiques (disque), la cardinalité estimée vs réelle dans les plans, la croissance d’OFFSET, les timeouts d’ORM, la saturation de pool de connexions et les locks qui s’allongent. Une hausse du p95 sans hausse du trafic est souvent le signe d’un plan d’exécution défavorable ou d’un index manquant.
Mesurer L’Impact Utilisateur Et Coûts
Une requête lente qui ne touche pas le parcours critique n’a pas la même priorité qu’une requête lente dans la page d’accueil. Connecte la latence base avec l’expérience utilisateur via des SLO orientés latence (p95/p99) et un score de type Apdex. Côté coûts, mesure le CPU, l’I/O, le temps réseau et… la facture : sur le cloud, une requête mal indexée peut multiplier les lectures par 10 et gonfler la ligne « IOPS provisionnées ». Donne-toi une métrique de « coût par requête » (I/O + CPU + temps) pour arbitrer objectivement.
Différences Entre Charge Interactive Et Batch
Sous charge interactive, tu optimises la queue de requêtes et la variance, pas juste la moyenne. Tu limites les scans, tu évites OFFSET élevé, tu réduis la contention. En batch, tu vises le débit et la fenêtre de traitement, acceptes des scans séquentiels, et utilises des ressources dédiées (resource groups, workload management). Les deux mondes ne doivent pas se gêner : isole les pools de connexions, réserve les créneaux batch hors pics et mets des priorités de requêtes quand le moteur le permet.
Outils Et Méthodes De Diagnostic
Logs Et Tracing Distribué
Active les logs de requêtes lentes et corrèle-les à tes requêtes applicatives via un trace_id propagé avec OpenTelemetry. Un span côté HTTP gRPC doit référencer le span SQL/NoSQL. Avec Jaeger, Tempo ou Zipkin, tu visualises la cascade : DNS → TLS → app → pool → base. Tu identifies si 80% du temps est perdu en attente de connexion, en sérialisation JSON, ou dans le moteur lui-même.
Plans D’Exécution: EXPLAIN/ANALYZE
EXPLAIN (ANALYZE, BUFFERS) dans PostgreSQL, EXPLAIN FORMAT=JSON dans MySQL, ou les Query Plans natifs de BigQuery/Redshift te disent le vrai coût. Regarde le type d’accès (Seq Scan vs Index Scan/Seek), les jointures (Nested Loop, Hash, Merge), le tri (Sort) et les filtres sur colonnes. Si une fonction est appliquée sur la colonne indexée (LOWER(col), DATE(col)), l’index saute. Si l’estimation de cardinalité est très différente du réel, des statistiques obsolètes peuvent forcer un mauvais plan. Côté NoSQL, examine la part des requêtes qui touchent plusieurs partitions, et la taille des documents transférés.
Profilage Au Niveau Serveur Et Réseau
Quand tout « semble » lent, profile en dessous. Vérifie le CPU utilisateur/système, les attentes I/O (iostat), la pression mémoire (evictions), la taille du page cache et le taux de cache hit. Observe le RTT réseau, le Nagle/désactivation TCP_NODELAY si tu enchaînes de petits paquets, et l’impact TLS si tu ouvres/fermes trop souvent des connexions. Côté base, regarde la saturation de WAL/redo, la contention sur des verrous (row-level, table-level) et les files d’attente du pool de connexions.
Causes Fréquentes Et Anti-Patterns
SQL: Index Manquants, Jointures Coûteuses, Fonctions Sur Colonnes, N+1
Le grand classique : un WHERE sur une colonne non indexée, et c’est le scan. Les jointures sur des colonnes de faible sélectivité sans index adéquat explosent le coût. Les fonctions sur colonnes (LOWER, CAST, DATE) invalident l’index, sauf index fonctionnels. Le N+1 côté ORM reste un tueur silencieux : 1 requête pour la liste, puis 1 requête par ligne pour les détails. Préfère des jointures bien ciblées ou des FETCH/IN avec jeux de clés.
NoSQL: Mauvaises Clés De Partition, Scans Complets, Agrégations À Chaud
Une clé de partition trop peu variée crée des partitions « brûlantes »: trop variée, et tu perds la localité. Les scans complets sont acceptables pour des petits jeux de données, mais catastrophiques en croissance. Les agrégations à chaud (top N en temps réel sur un flux massif) étouffent les nœuds. Utilise des clés composées (partition + sort), des index secondaires quand ils existent, et déporte les agrégations en pipeline ou via des vues matérialisées.
Stratégies D’Optimisation Ciblées
Indexation Avancée (Multi-Colonnes, Partiels, Couvrants)
Pense « accès minimal ». Un index composite doit respecter l’ordre des prédicats les plus sélectifs et ceux utilisés pour le tri. Les index partiels (PostgreSQL) ciblent un sous-ensemble (WHERE status=’ACTIVE’) et réduisent taille et maintenance. Les index couvrants (INCLUDE en Postgres, covering index en MySQL) évitent le lookup vers la table. Sur des tables temporelles, un BRIN (Postgres) réduit le coût des scans par bloc. N’oublie pas qu’InnoDB clusterise le primaire : choisis une clé courte et stable.
Réécriture De Requête Et Pagination Efficiente
Réécris pour guider le plan. Remplace les fonctions sur colonne par des colonnes dérivées ou des index fonctionnels. Évite SELECT *, limite les colonnes utiles. Pour la pagination, évite OFFSET profond qui coûte linéairement. Utilise la keyset pagination (WHERE id > last_id ORDER BY id) ou un curseur basé sur l’index de tri. Les agrégations lourdes gagnent à être préfiltrées et, si possible, à utiliser des fenêtres (window functions) plutôt qu’un auto-join mal maîtrisé.
Caches Et Matérialisations (Views, TTL)
Cache ce qui est cher et peu changeant. Un cache applicatif ou Redis avec TTL absorbe le trafic hot. Les vues matérialisées (ou tables d’agrégats rafraîchies) déplacent le coût en amont. Dans NoSQL, stocke des projections prêtes à lire, avec invalidation par événement (CDC) plutôt que par timer générique. Le cache doit être mesuré : hit ratio, staleness acceptable, et stratégie d’invalidation claire.
Gérer La Concurrence, Verrouillages Et Temps D’Attente
Réduis la contention en raccourcissant les transactions et en gardant un ordre d’accès stable aux ressources. Fixe des timeouts réalistes (statement_timeout, lock_timeout) pour éviter les files de zombies. Utilise l’isolation adaptée : READ COMMITTED pour la plupart, REPEATABLE READ/ SERIALIZABLE seulement quand nécessaire. L’optimistic locking (version/timestamp) et les retrys exponentiels sont souvent plus performants que le verrouillage pessimiste. Sépare lecture/écriture avec des réplicas seulement si ta cohérence le tolère, et surveille la latence de réplication.
Modélisation Des Données Et Architecture
SQL: Normaliser Où Il Faut, Dénormaliser Quand Il Faut
La normalisation réduit les anomalies et les mises à jour coûteuses. Mais pour les lectures intensives, une dose de dénormalisation (colonnes dérivées, tables de résumé) simplifie les requêtes et réduit les jointures. Conçois les index en fonction des requêtes réelles, pas de la théorie. Si une requête critique nécessite trois jointures, demande-toi si une table de faits agrégée quotidienne ne ferait pas mieux l’affaire.
NoSQL: Modéliser Par Accès, Clés De Partition Et Cardinaux
En NoSQL, tu modèles par cas d’usage. Choisis une clé de partition qui répartit la charge tout en regroupant ce que tu lis ensemble. Gère la cardinalité : trop faible → points chauds: trop élevée → dispersion et coûts réseau. Les clés composées et les index globaux/locaux, quand disponibles, aident à couvrir plusieurs requêtes types. Accepte la duplication contrôlée pour des lectures constantes O(1) par clé.
Schémas Évolutifs, Agrégations Hors Ligne Et Pipelines
Le schéma évolue en continu : adopte le pattern expand-migrate-contract pour les changements. Déporte les agrégations lourdes en pipelines (ETL/ELT) via un bus d’événements (CDC, Kafka) vers un entrepôt ou des tables de métriques. Rafraîchis les vues matérialisées de manière incrémentale. Tu gagnes en prévisibilité de latence côté application, car le « cher » quitte le chemin utilisateur.
Observabilité, Tests Et Opérations
SLO, Tableaux De Bord Et Alertes Orientées Latence
Tes SLO doivent refléter l’expérience : par exemple, p95 < 200 ms pour la lecture de profil, p99 < 500 ms. Les tableaux de bord affichent p50/p95/p99, le taux d’erreur, la taille du pool de connexions et les requêtes top N par coût. Alerte sur l’épuisement du budget d’erreur (burn rate) plutôt que sur un simple seuil.
Tests De Performance Et Régression Automatisés
Teste avec des données réalistes (cardinalités, distributions, skew). k6, Gatling ou JMeter pour le trafic: EXPLAIN/ANALYZE en CI pour capter un plan qui déraille. Gèle des « snapshots » de plans pour les requêtes critiques et détecte les régressions après changement de stats ou d’index. Mesure aussi la stabilité (variance, jitter), pas juste la moyenne.
Déploiements Sûrs: Index En Ligne, Flags Et Rollback
Crée les index en ligne quand le moteur le permet (CONCURRENTLY sur Postgres, ONLINE sur InnoDB). Déploie en deux temps : d’abord les colonnes/structures compatibles, puis l’usage applicatif derrière un feature flag. Prévois un rollback simple (migrations réversibles, scripts idempotents) et des canaries par pourcentage de trafic pour vérifier l’impact réel avant généralisation.
Conclusion
Traiter les requêtes lentes, c’est une boucle : définir, observer, diagnostiquer, corriger, vérifier. Tu gagnes du temps en posant des seuils clairs, en corrélant traces et plans d’exécution, puis en ciblant les quelques requêtes qui consomment la majorité des ressources. Entre une indexation bien pensée, une pagination keyset, des caches avec TTL et une modélisation orientée accès, tu peux réduire la latence de façon spectaculaire sans surprovisionner. Et surtout, fais-le en sécurité : tests de charge réalistes, déploiements progressifs, et surveillance du p95/p99 après chaque changement. Le résultat? Une base qui tient la charge, des coûts maîtrisés, et des utilisateurs qui ne se rendent même pas compte qu’il y a une base derrière, et c’est exactement le but de l’optimisation des performances.
No responses yet