Analyser les requêtes lentes en PHP sans indexer à l’aveugle

Un écran lent ne prouve ni que la base de données est le problème, ni qu’un index est la solution. Cette même perception peut provenir du code PHP, d’une saturation des connexions, d’un appel HTTP externe, de la sérialisation des réponses, de verrous ou d’une requête qui renvoie trop de données. Savoir comment analyser les requêtes lentes en PHP consiste à constituer des preuves avant de modifier le schéma ou d’ajouter des optimisations qui pourraient alourdir les écritures.

Distinguer la latence de l’application de la latence des données

Commencez par décomposer le temps total d’une requête. Enregistrez une route ou une commande identifiable, l’heure de début et de fin, les requêtes exécutées, leurs durées et les dépendances externes. Il ne suffit pas de mesurer le temps moyen : une API peut sembler saine et pourtant échouer pour certains filtres, clients ou pages profondes.

Pour chaque incident, il convient de distinguer :

• Temps en PHP : transformation de collections, boucles, sérialisation JSON, génération de documents ou utilisation excessive de mémoire.
• Temps de base de données : durée de chaque requête, attente de verrous, ouverture de connexion et nombre de lignes transférées.
• Temps réseau et dépendances : caches distants, API tierces, stockage de fichiers, files d’attente ou services d’identité.
• Temps d’attente en file : requêtes qui attendent des processus d’exécution PHP, des connexions disponibles ou des ressources de la base de données.

Utilisez des traces ou des logs structurés avec un identifiant de requête. Une requête isolée de 20 ms peut devenir un problème de plusieurs secondes si elle est exécutée des centaines de fois dans la même réponse. À l’inverse, une requête de 500 ms peut ne pas être la cause principale si le processus reste en attente d’un service externe pendant plusieurs secondes.

Recueillir des preuves avant de modifier le code

Capturez la requête paramétrée et, séparément, des paramètres représentatifs. Évitez d’enregistrer des secrets, des données personnelles complètes ou des valeurs qui ne sont pas nécessaires pour reproduire le cas. Une recherche sur un état fréquent ne se comporte pas comme une recherche sur un identifiant unique ; n’évaluer que le cas facile conduit à des décisions erronées.

Les preuves minimales doivent inclure :

• La route, le job asynchrone ou la commande concernés, ainsi que leur fréquence.
• La durée observée, les percentiles élevés et le moment d’apparition.
• Le SQL, les paramètres typés et le nombre d’exécutions par requête.
• Les lignes renvoyées et, lorsque cela est possible, les lignes lues ou examinées.
• La taille approximative des tables et la distribution des valeurs filtrées.
• La concurrence, les opérations d’écriture simultanées et les verrous pertinents.

Les logs de requêtes lentes du moteur aident à découvrir des candidats, mais ils ne remplacent pas une trace applicative : ils n’indiquent généralement pas quel endpoint a construit une requête ni combien de fois elle s’est répétée. Dans MySQL et PostgreSQL, combinez ces informations avec les métriques de connexion, de CPU, d’I/O et de temps d’attente afin de ne pas confondre un mauvais plan avec une infrastructure temporairement saturée.

Repérer les motifs qui multiplient le coût

Avant d’analyser une instruction complexe, recherchez les motifs fréquents. Le N+1 apparaît lorsqu’une liste obtient ses lignes principales, puis exécute une requête supplémentaire pour chaque relation. Même si chaque requête individuelle est rapide, le volume d’allers-retours vers la base de données, le travail de planification et la contention augmentent avec la taille de la page.

Les filtres à faible sélectivité, tels que des états très courants ; les fonctions appliquées sur des colonnes filtrées ; les conversions de type implicites ; les recherches avec un joker initial ; les tris de grands ensembles et la pagination avec un OFFSET élevé sont également suspects. Demander SELECT * peut accroître les transferts, la mémoire et le travail de lecture, même lorsque le plan utilise déjà un index.

La correction ne consiste pas toujours en une requête unique. Charger les relations de manière contrôlée peut résoudre un N+1, mais un chargement anticipé sans limites peut créer une requête ou une réponse énorme. Définissez quelles relations sont réellement nécessaires à la route, limitez leurs colonnes et mesurez l’effet avec la taille de page attendue.

Interpréter le plan d’exécution avec des données réelles

Exécutez EXPLAIN pour connaître le plan proposé et utilisez la variante qui intègre l’exécution réelle lorsqu’elle est sûre et adaptée à l’environnement. Dans PostgreSQL, EXPLAIN ANALYZE exécute la requête ; une instruction de modification ne doit pas être analysée ainsi sans en comprendre l’effet. Dans MySQL, les modes disponibles dépendent de la version et de la configuration, mais l’objectif est le même : comparer les estimations avec le travail réel.

Un balayage séquentiel n’est pas automatiquement mauvais. Si la requête nécessite une grande partie d’une table petite ou peu sélective, la parcourir peut coûter moins cher que de naviguer entre l’index et la table. En revanche, enquêtez lorsque le plan affiche beaucoup plus de lignes réelles qu’estimées, des tris coûteux, des lectures temporaires, des jointures sur de grands ensembles ou des boucles internes exécutées de nombreuses fois.

Questions utiles lors de la révision d’un plan

• Combien de lignes l’optimiseur attendait-il et combien en a-t-il réellement traitées ?
• Quel nœud concentre le plus de temps, de lectures ou d’itérations ?
• Le filtre est-il appliqué tôt ou après la combinaison de grands ensembles ?
• Le tri s’effectue-t-il sur plus de lignes que n’en nécessite la réponse ?
• Les statistiques reflètent-elles la distribution actuelle des données ?

Les écarts entre estimation et réalité peuvent nécessiter de mettre à jour les statistiques ou de revoir les types et les conditions, et non de créer immédiatement un index. Le plan est une explication d’une exécution sous certains paramètres et une certaine charge ; ce n’est pas un ordre automatique de changement.

Choisir entre réécriture, pagination, accès aux données et index

Réduisez d’abord le travail inévitable. Sélectionnez uniquement les colonnes nécessaires, appliquez des limites raisonnables, éliminez les relations inutilisées et évitez de transporter des historiques complets vers PHP pour les filtrer ensuite. Si le cas d’usage demande d’explorer un historique croissant, remplacez la pagination profonde par une pagination par curseur ou par clé : par exemple, poursuivez depuis une combinaison stable de date et d’identifiant au lieu d’écarter des milliers de lignes avec OFFSET.

Revoyez les jointures et les filtres afin qu’ils comparent des colonnes compatibles et expriment clairement la condition. Il est parfois préférable d’interroger une relation par lots ; dans d’autres cas, une seule requête bien délimitée est préférable. La décision dépend de la cardinalité, du volume de réponse et de la fréquence, et non d’une règle universelle.

Un index composite aide lorsqu’il correspond au motif d’accès. Son ordre est important : normalement, les colonnes d’égalité et sélectives doivent faciliter le filtrage avant celles utilisées pour une plage ou un tri, mais la requête concrète et le moteur déterminent le résultat. Un index peut également aider à éviter un tri s’il couvre un ordre compatible, bien que toutes les combinaisons de WHERE et ORDER BY ne le permettent pas.

Évitez d’indexer des colonnes simplement parce qu’elles apparaissent dans une condition. Les index occupent de l’espace, consomment de la mémoire et ajoutent du travail à INSERT, UPDATE et DELETE. Des index redondants ou peu utiles peuvent dégrader un système avec beaucoup d’écritures. Vérifiez également si l’index permet d’obtenir les colonnes requises sans lectures supplémentaires, mais n’ajoutez pas de colonnes de couverture sans mesurer le coût.

Exemple hypothétique : un historique d’opérations

Supposez une liste qui affiche des opérations par compte, état et date. À mesure que l’historique augmente, la page 200 se dégrade. La première hypothèse pourrait être de créer un index sur la date. Cependant, la trace révèle une requête principale suivie d’une requête pour chaque opération afin d’obtenir l’utilisateur responsable : il y a un N+1. Le plan de la requête principale lit en outre de nombreuses lignes pour écarter les précédentes avec OFFSET.

La séquence raisonnable serait de charger les responsables de manière groupée ou au moyen d’une jointure limitée aux colonnes nécessaires, de remplacer la pagination profonde par un curseur fondé sur created_at et id, puis de mesurer à nouveau. Ce n’est qu’alors qu’un index aligné sur le filtre de compte, l’ordre stable et le curseur est évalué. Le changement d’accès peut réduire davantage de travail qu’un index isolé, et l’index final doit aussi être vérifié au regard de la création de nouvelles opérations.

Valider sous charge et surveiller les régressions d’écriture

Comparez avant et après avec des paramètres, une distribution des données et une concurrence représentatifs. Mesurez la durée, les lignes traitées, les lectures, l’utilisation du CPU, la mémoire en PHP, la taille de la réponse et le nombre de requêtes par requête. Pour un nouvel index, mesurez également la latence et la capacité des opérations d’écriture affectées.

Définissez des seuils d’acceptation explicites : par exemple, une réduction vérifiable du percentile élevé de la route sans augmenter de manière inacceptable le temps de création ou de mise à jour. Testez les cas vides, les valeurs très fréquentes, les filtres rares, les premières et dernières pages, ainsi que les autorisations qui modifient le périmètre des données.

Déployer des changements de manière observable et réversible

Séparez, lorsque cela est possible, le déploiement du code de la création de l’index. La création d’index peut entrer en concurrence pour les ressources ou acquérir des verrous selon le moteur, l’opération et l’environnement. Planifiez le moment, vérifiez la méthode prise en charge par votre base de données et surveillez la durée, les erreurs et la pression sur les I/O.

Introduisez progressivement la nouvelle requête si l’architecture le permet et maintenez une réversion claire : restaurer la requête précédente, désactiver une voie d’accès alternative ou supprimer un index qui s’avère préjudiciable. Une réversion ne remplace ni les sauvegardes ni la révision des migrations, mais elle réduit le temps d’exposition face à un comportement inattendu.

Checklist pour une amélioration reproductible

1. Identifiez la route, le symptôme et les paramètres qui le reproduisent.
2. Distinguez le temps de PHP, de la base de données, du réseau et des dépendances externes.
3. Quantifiez les répétitions, les lignes renvoyées et les lignes traitées.
4. Recherchez les N+1, la pagination profonde, les filtres peu sélectifs et les tris.
5. Examinez le plan et confrontez ses estimations à l’exécution réelle.
6. Testez la réduction des données, la réécriture et le changement de pagination avant d’indexer.
7. Concevez l’index selon le motif complet de filtrage, de tri et d’écriture.
8. Validez les lectures et les écritures avec une charge représentative, de l’observabilité et une réversion.