Détecter et corriger les problèmes de requêtes N+1 avec Doctrine

N+1 se joue dans la conception de l’accès aux données

Une liste peut afficher le bon contenu tout en sollicitant beaucoup trop la base de données. N+1 désigne un mécanisme courant : une requête charge les enregistrements, puis le traitement de chacun déclenche une autre requête pour ses données associées. La formule 1 + N décrit le phénomène, sans prédire le nombre exact de requêtes dans chaque application.

Avec Doctrine, le décalage se situe souvent entre la requête du repository et les besoins du code qui exploite son résultat. Le SQL supplémentaire peut n’apparaître que dans un service, un mapper, un gabarit Twig ou un composant de sérialisation. Le chargement à la demande est utile. Son coût devient facile à manquer lorsque personne ne suit la lecture de bout en bout.

Des articles, des auteurs et des traductions

Prenons une liste illustrative d’articles publiés. Chaque article possède un auteur obligatoire et une collection de traductions. Cet extrait d’entité ne montre que ces associations :

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

class Post
{
    #[ORM\ManyToOne(targetEntity: User::class, fetch: 'LAZY')]
    #[ORM\JoinColumn(nullable: false)]
    private User $author;

    /** @var Collection<int, PostTranslation> */
    #[ORM\OneToMany(
        mappedBy: 'post',
        targetEntity: PostTranslation::class,
        fetch: 'LAZY',
    )]
    private Collection $translations;

    public function __construct(User $author)
    {
        $this->author = $author;
        $this->translations = new ArrayCollection();
    }
}

L’attribut d’entité, l’identifiant, les autres champs, les accesseurs et le mapping du côté propriétaire PostTranslation::$post sont omis. Le constructeur initialise la collection des nouveaux objets. Post n’est pas final afin que l’exemple convienne aussi aux configurations utilisant des classes proxy générées par héritage ; les objets paresseux natifs ont d’autres contraintes.

Supposons que translationFor($locale) parcoure la collection de traductions en mémoire et retourne une traduction ou null. Cette lecture volontairement naïve suppose que chaque article sélectionné possède la traduction demandée :

$posts = $postRepository->findBy(
    ['published' => true],
    ['publishedAt' => 'DESC', 'id' => 'DESC'],
);

$result = [];

foreach ($posts as $post) {
    $result[] = [
        'title' => $post->translationFor($locale)->getTitle(),
        'author' => $post->getAuthor()->getDisplayName(),
    ];
}

Lire le nom d’un auteur non chargé peut initialiser son objet. Parcourir une collection non chargée peut récupérer les traductions de l’article. Pour le tableau suivant, posons des hypothèses précises : un EntityManager neuf, un auteur différent par article, aucun résultat en cache et une requête pour chaque accès de ce type à une association.

Nombre de requêtes SQL illustratif, non mesuré

Articles  Liste  Auteurs  Traductions  Total
       5      1        5            5     11
      50      1       50           50    101
     500      1      500          500   1001

On retrouve ainsi le cas « environ 101 requêtes pour 50 articles ». Ce n’est pas une prévision universelle. Doctrine peut réutiliser les auteurs communs grâce à sa carte d’identité, ou identity map, qui répertorie les objets gérés par identité au sein d’un EntityManager. Les associations déjà chargées, le mapping, le plan de chargement et les caches de résultats ou de second niveau influencent aussi le total. Les caches de métadonnées et de requêtes DQL, en revanche, ne stockent pas les données métier retournées.

La boucle suppose également qu’une traduction existe et charge tous les articles publiés. Ce sont des simplifications pour expliquer le diagnostic, pas le contrat d’une liste publique prête à être utilisée.

Confirmer le travail répété

Dans Symfony Profiler, commencez par une requête HTTP représentative : nombre de requêtes Doctrine, temps cumulé en base et structures SQL répétées. Une empreinte SQL regroupe les instructions de même structure dont les paramètres diffèrent. Lorsqu’elle est disponible, la pile d’appels permet de relier une instruction répétée à l’accesseur d’une association.

Une petite base locale, une faible latence et des objets de test encore gérés par l’EntityManager peuvent masquer N+1. Comparez, par exemple, 5, 50 et 500 enregistrements dans la plage de charge prévue, avec des états reproductibles de l’EntityManager et des caches. Cherchez les requêtes ajoutées pour chaque élément. Un nombre strictement constant n’est pas exigible pour tout traitement : le découpage en lots peut ajouter une requête à chaque nouveau lot.

L’intégration de profilage ou de journalisation doit correspondre aux versions de DBAL et de DoctrineBundle installées. Les versions modernes de DBAL utilisent des middlewares ; les anciens exemples fondés sur SQLLogger ne sont pas transposables à DBAL 4. Dans un test, le collecteur doit observer l’opération étudiée, sans compter la création des données ni le démarrage général du framework.

Remontez aussi à l’appelant. Ces accès peuvent solliciter la base lorsque les objets ou collections concernés ne sont pas chargés :

$post->getAuthor()->getDisplayName();
$post->getTranslations()->first();
count($post->getComments());

getAuthor() peut simplement retourner un proxy sans l’initialiser ; la lecture d’un champ autre que son identifiant provoque généralement cette initialisation. Avec une collection paresseuse ordinaire, first() ou count() peuvent charger la collection. getComments() représente ici une association supplémentaire, absente de l’extrait d’entité. Un mapper, un débogueur ou le formatage d’un message de journal peut déclencher le même travail en lisant ces valeurs.

Trois façons de préparer les données de la liste

Fetch join : des entités avec les associations nécessaires

Cette méthode de repository conserve un résultat composé d’entités, mais récupère aussi les auteurs et les traductions. La configuration du repository et les imports sont omis :

/**
 * @return list<Post>
 */
public function findPublishedWithAuthorAndTranslations(): array
{
    return $this->createQueryBuilder('post')
        ->addSelect('author')
        ->addSelect('translation')
        ->innerJoin('post.author', 'author')
        ->leftJoin('post.translations', 'translation')
        ->andWhere('post.published = :published')
        ->setParameter('published', true)
        ->orderBy('post.publishedAt', 'DESC')
        ->addOrderBy('post.id', 'DESC')
        ->getQuery()
        ->getResult();
}

En hydratation objet, sélectionner les alias joints avec addSelect() transforme ici les jointures en fetch joins : Doctrine renseigne les associations pendant la construction du résultat. Une jointure servant uniquement au filtrage ne charge pas à elle seule l’association. La jointure interne sur l’auteur correspond à son caractère obligatoire. S’il est facultatif, il faut adapter la jointure et le code consommateur. La jointure gauche sur les traductions conserve les articles non traduits ; leur traitement reste à définir.

La méthode ne limite volontairement pas la page et charge toutes les traductions des articles correspondants. Elle convient à un petit ensemble connu ou comme point de départ pour la pagination décrite plus loin, pas à une liste publique sans limite. Le tri par publishedAt puis id donne un ordre déterministe aux articles, sans fixer celui des traductions dans chaque collection.

Limiter ce fetch join d’entités à une seule langue présenterait uniquement ce sous-ensemble comme collection chargée. Un autre traitement pourrait la croire complète alors qu’il attend toutes les traductions. Une projection exprime souvent plus clairement le besoin d’une liste dans une langue donnée.

Projection DTO : le contrat de données de la liste

Une vue qui utilise quatre valeurs n’a pas nécessairement besoin d’un graphe d’entités modifiable. Voici une déclaration complète de DTO, compatible avec PHP 8.2 et versions ultérieures :

namespace App\ReadModel;

final readonly class PostListItem
{
    public function __construct(
        public int $id,
        public string $title,
        public string $authorName,
        public \DateTimeImmutable $publishedAt,
    ) {
    }
}

Le repository doit importer App\ReadModel\PostListItem. Les arguments de l’expression de construction DQL suivent l’ordre des paramètres du constructeur :

/**
 * @return list<PostListItem>
 */
public function findPublishedList(
    string $locale,
    int $limit,
): array {
    if ($limit < 1) {
        throw new \InvalidArgumentException('limit >= 1');
    }

    return $this->createQueryBuilder('post')
        ->select(sprintf(
            'NEW %s(
                post.id,
                translation.title,
                author.displayName,
                post.publishedAt
            )',
            PostListItem::class,
        ))
        ->innerJoin('post.author', 'author')
        ->innerJoin(
            'post.translations',
            'translation',
            'WITH',
            'translation.locale = :locale',
        )
        ->andWhere('post.published = :published')
        ->setParameter('published', true)
        ->setParameter('locale', $locale)
        ->orderBy('post.publishedAt', 'DESC')
        ->addOrderBy('post.id', 'DESC')
        ->setMaxResults($limit)
        ->getQuery()
        ->getResult();
}

L’exemple suppose un post.id entier, des titres et noms d’auteurs non nuls, ainsi qu’un mapping datetime_immutable pour publishedAt avec une date pour chaque article sélectionné. Un autre mapping exige des types de constructeur compatibles ; le DTO ne transforme pas une chaîne de caractères quelconque en objet date.

La condition sur la langue sélectionne la traduction demandée. Une contrainte d’unicité en base sur le couple article/langue doit garantir au plus une traduction correspondante. Avec un seul auteur, cette hypothèse donne un DTO par article retenu : la limite porte alors sur les éléments de la liste. Sans cette contrainte, des traductions en double peuvent dupliquer les DTO et réduire le nombre d’articles distincts sur la page.

La jointure interne exclut délibérément les articles sans traduction dans cette langue. Si le produit prévoit une langue de remplacement ou un élément sans titre, il faut définir cette règle. Une jointure gauche seule imposerait un titre nullable dans le contrat ou une expression de remplacement appropriée. La méthode retourne le premier ensemble limité ; les pages suivantes nécessitent un décalage ou un curseur, avec le même tri déterministe.

Cette projection construit les DTO à partir des valeurs sélectionnées au lieu d’hydrater des entités. Lire leurs propriétés ne peut pas charger d’associations. Moins de colonnes et l’absence de graphe d’entités géré peuvent réduire la mémoire et le travail d’hydratation, mais les jointures conservent un coût. Un DTO ne garantit ni une requête plus rapide ni le remplacement des entités nécessaires au comportement métier.

Chargement par lots : les données associées à la page

Quand les jointures de collections gonfleraient trop le résultat, une autre solution consiste à charger une page, puis les données associées à ses identifiants. Ici, la méthode propre au projet findPublishedPage() doit retourner une list<Post> ordonnée dont les auteurs sont déjà chargés par une jointure vers une seule entité. $page et $limit sont validés ; getId() retourne un entier pour ces articles persistés.

$posts = $postRepository->findPublishedPage($page, $limit);
$postIds = array_map(
    static fn (Post $post): int => $post->getId(),
    $posts,
);

$translations = $postIds === []
    ? []
    : $translationRepository->findIndexedByPostIds(
        postIds: $postIds,
        locale: $locale,
    );

$result = [];
foreach ($posts as $post) {
    $result[] = [
        'title' => $translations[$post->getId()] ?? null,
        'author' => $post->getAuthor()->getDisplayName(),
    ];
}

findIndexedByPostIds() est une méthode illustrative de repository, pas une API Doctrine. Son contrat est ici array<int, string> : identifiant d’article vers titre dans la langue demandée, obtenu par une requête portant sur l’ensemble des identifiants transmis. Les mêmes restrictions d’accès et règles d’unicité des traductions doivent s’appliquer. La boucle finale lit ce tableau au lieu d’appeler translationFor() ; un titre manquant est représenté par null.

Sous ces hypothèses, une page non vide utilise deux requêtes de données. Un comptage total, d’autres associations ou un découpage lié aux limites de paramètres peuvent en ajouter. Exécuter une requête par identifiant à l’intérieur de la seconde méthode ne ferait que déplacer N+1.

Modes de chargement, multiplication des lignes et pagination

LAZY diffère le chargement d’une association jusqu’au moment où elle est nécessaire. EAGER demande un chargement anticipé, mais ne signifie pas « une seule jointure SQL ». Selon le mapping et le chemin de lecture, Doctrine peut utiliser des jointures ou des requêtes supplémentaires. Généraliser ce réglage peut alourdir des commandes et des écrans qui n’utilisent jamais ces associations.

Pour les collections, EXTRA_LAZY permet des opérations prises en charge telles que count(), contains() ou slice() sans charger tous les éléments. Cette propriété ne s’étend pas à toutes les méthodes. Une itération charge toujours la collection ; compter 50 collections non chargées peut encore exécuter 50 requêtes. Vérifiez les opérations prises en charge dans la version de l’ORM utilisée.

Joindre plusieurs collections indépendantes présente un autre risque. Pour un article avec 4 traductions, 20 commentaires et 8 tags, la combinaison de toutes les associations peut produire 4 × 20 × 8 = 640 lignes SQL. Les filtres, les types de jointure et les cardinalités réelles déterminent le résultat. Il ne s’agit pas de 640 articles distincts : l’hydratation objet habituelle regroupe les entités racines par identité. Le travail de la base, le transfert des lignes et l’hydratation consomment pourtant toujours du temps et de la mémoire.

DISTINCT élimine les lignes SQL sélectionnées identiques lorsqu’il y en a. Des lignes contenant des traductions, commentaires ou tags différents ne sont pas identiques. Il ne supprime donc pas le coût de cette multiplication.

Une limite avec décalage appliquée directement à un fetch join de collection porte sur les lignes SQL, pas sur des articles complets avec leurs collections. La page peut contenir moins d’articles ou des collections incomplètes. Utilisez les mécanismes de pagination adaptés à la version de Doctrine, avec le réglage des jointures de collections et les query walkers, qui transforment les requêtes, appropriés au cas. Les requêtes de comptage et de sélection d’identifiants font aussi partie du coût mesuré.

Une autre approche consiste à sélectionner, par exemple, 25 identifiants d’articles distincts dans l’ordre de la page, puis à charger leurs détails. Les deux phases doivent conserver des filtres et des restrictions d’accès cohérents. IN (:ids) ne respecte pas l’ordre des valeurs fournies : il faut réappliquer le tri ou reconstituer le résultat selon la liste des identifiants. Si les modifications concurrentes entre les deux lectures sont importantes, définissez l’isolation transactionnelle ou la cohérence de l’instantané requise.

Les jointures vers une seule entité évitent généralement la multiplication liée aux collections, mais d’autres jointures dans la même requête peuvent la provoquer. Trier les articles selon une collection exige une règle, par exemple la date du commentaire le plus récent, avant de choisir les identifiants de la page. La pagination par clé, ou keyset, peut faciliter le parcours séquentiel de grandes listes. Elle ne fournit pas naturellement un saut vers n’importe quel numéro de page ni un nombre total. Le choix dépend aussi de la navigation attendue.

Twig et la sérialisation font partie du chemin de lecture

Un gabarit peut révéler une préparation insuffisante des données sans contenir la moindre instruction SQL :

{% for post in posts %}
    {{ post.author.displayName }}
    {{ post.translationFor(app.request.locale).title }}
{% endfor %}

Twig résout ces valeurs à travers les accesseurs de l’objet. Les mêmes hypothèses sur les associations chargées et les traductions disponibles s’appliquent donc que dans la boucle PHP. Avant le rendu, fournissez soit des entités chargées de façon délibérée, soit un modèle de vue. Un DTO aide à borner les données lisibles ; il n’est pas obligatoire pour chaque gabarit.

L’encodage JSON appelle une distinction :

json_encode($entity);

Par défaut, json_encode() inclut les propriétés publiques. Il ne parcourt pas systématiquement toutes les associations privées de Doctrine et n’appelle pas automatiquement leurs accesseurs. En revanche, JsonSerializable::jsonSerialize() ou un code de représentation personnalisé peut lire ces associations. Symfony Serializer utilise des normalizers : un normalizer d’objets peut consulter les accesseurs ou propriétés autorisés par sa configuration et ses groupes de sérialisation. Il faut examiner le normalizer et le contexte réels, pas seulement le contrôleur.

Un test du nombre de requêtes d’une réponse HTTP devrait donc inclure le rendu ou la normalisation. Mesurer uniquement le repository peut laisser passer les requêtes exécutées ensuite.

Des tests de régression avec une fenêtre de mesure précise

Les exemples Pest suivants sont des esquisses de tests d’intégration. createPublishedPosts(), createPostFixtures(), entityManager(), doctrineQueryCollector(), service() et repository() sont des méthodes utilitaires propres au projet. reset(), start(), stop() et queryCount() décrivent le contrat illustratif du collecteur, pas des méthodes natives de Pest ou Doctrine. Le collecteur doit compter les instructions exécutées sur la connexion concernée. Une instrumentation fondée sur un middleware doit être configurée avant la création de cette connexion.

L’environnement de test doit isoler les jeux de données, maîtriser les caches et préparer les services sans charger la liste à l’avance. Après l’enregistrement des données de test, vider le même EntityManager que celui du service évite de réutiliser les objets des fixtures. clear() ne vide toutefois ni le cache de second niveau ni le cache applicatif.

Ici, getPublishedPosts() est censée retourner une liste entièrement construite, pas un itérateur à évaluation différée. Le budget de trois requêtes est un exemple de seuil convenu pour ce service :

it('limite le nombre de requêtes', function (int $count): void {
    $this->createPublishedPosts(count: $count, locale: 'fr');

    $entityManager = $this->entityManager();
    $service = $this->service();
    $collector = $this->doctrineQueryCollector();
    $entityManager->flush();
    $entityManager->clear();

    $collector->reset();
    $collector->start();
    try {
        $result = $service->getPublishedPosts(
            locale: 'fr',
            limit: $count,
        );
    } finally {
        $collector->stop();
    }

    expect($result)->toHaveCount($count)
        ->and($collector->queryCount())->toBeLessThanOrEqual(3);
})->with([5, 50]);

Le jeu de données lance le test pour 5 et 50 articles. Des auteurs distincts et plusieurs traductions évitent que la réutilisation des associations masque une régression ; il faut aussi tester des cas représentatifs d’auteurs communs. Les résultats différés, gabarits ou composants de sérialisation doivent être consommés dans la fenêtre mesurée. Un budget de requêtes protège contre les allers-retours répétés, sans mesurer la latence ni la mémoire.

Le test de repository ci-dessous sépare la construction du résultat des lectures de propriétés ultérieures :

it('lit les DTO sans SQL supplémentaire', function (): void {
    $this->createPostFixtures(count: 20, locale: 'fr');

    $entityManager = $this->entityManager();
    $repository = $this->repository();
    $collector = $this->doctrineQueryCollector();
    $entityManager->flush();
    $entityManager->clear();

    $items = $repository->findPublishedList('fr', 20);

    expect($items)->toHaveCount(20)
        ->each->toBeInstanceOf(PostListItem::class);

    $collector->reset();
    $collector->start();
    try {
        foreach ($items as $item) {
            expect($item->title)->toBeString()
                ->and($item->authorName)->toBeString();
        }
    } finally {
        $collector->stop();
    }

    expect($collector->queryCount())->toBe(0);
});

Il vérifie le nombre et le type des DTO, puis l’absence de SQL supplémentaire lors de la lecture du titre et du nom de l’auteur. Il ne mesure pas le coût de findPublishedList(). Le choix de langue, les traductions absentes, l’ordre, les limites de page et l’unicité des identifiants demandent d’autres assertions avec des valeurs de test connues. Vérifier les types ne suffit pas à prouver ces comportements.

Contrats de types et revue des changements proposés par l’IA

PHPStan peut vérifier que les implémentations et leurs appelants respectent un type de retour déclaré. Cette déclaration a sa place dans une interface :

/**
 * @return list<PostListItem>
 */
public function findPublishedList(
    string $locale,
    int $limit,
): array;

Des types d’éléments précis et des contrats explicitement nullables rendent certaines erreurs plus visibles. PHPStan n’exécute pas de SQL et ne détecte pas directement N+1. Des règles d’architecture arbitraires nécessitent des contrôles propres au projet. L’analyse statique et les mesures à l’exécution répondent à des questions différentes.

Pour une modification proposée par un développeur ou un assistant IA, confrontez les accès aux associations dans les boucles au mapping et à la requête réels du repository. Un tel accès n’est pas incorrect si les données sont déjà chargées comme prévu. La revue doit permettre d’identifier quelle requête fournit chaque valeur, si un mapper ou un composant de sérialisation ajoute du travail et si le tri comme la pagination restent corrects. Une estimation du nombre de requêtes est une hypothèse ; les tests et les mesures apportent les éléments de preuve. Une optimisation locale ne justifie pas une modification générale des modes de chargement.

Mesurer aussi après la mise en production

En production, rapprochez les percentiles de latence d’un point d’accès du nombre de requêtes, du temps total en base, des empreintes SQL répétées, de la taille des pages et de la fréquence des requêtes lentes. Ajoutez les lignes examinées, le coût d’hydratation et la mémoire lorsque les outils permettent de les mesurer. De nombreuses instructions rapides prises séparément peuvent coûter cher ensemble : le journal des requêtes lentes ne repère donc pas tous les N+1.

Adaptez l’échantillonnage des traces et évitez de conserver inutilement des paramètres SQL sensibles ou des données clients. Même normalisé, le SQL peut contenir des valeurs littérales ou des informations identifiantes. Une empreinte ne garantit pas l’anonymisation.

Le cache peut être utile une fois le chemin de lecture compris. Vérifiez le comportement à cache vide, l’invalidation et les clés correspondant au bon utilisateur ou à la bonne organisation. Déplacer la même boucle dans un autre service ne change pas son SQL. Modifier globalement le chargement à la demande peut casser des usages existants sans définir un meilleur plan de lecture.

Un diagnostic utile se termine par une comparaison : reproduire la lecture, identifier le travail répété, choisir la plus petite modification adaptée, puis revérifier le contenu, l’ordre, la langue et la pagination. Comparez requêtes, temps en base et mémoire dans des conditions semblables, puis observez la mise en production. La bonne correction répond au besoin de lecture à un coût global acceptable, pas seulement avec moins d’instructions.

Documentation technique

• Doctrine ORM : DQL, fetch joins et expressions de construction — https://www.doctrine-project.org/projects/doctrine-orm/en/3.6/reference/dql-doctrine-query-language.html
• Doctrine ORM : pagination — https://www.doctrine-project.org/projects/doctrine-orm/en/3.6/tutorials/pagination.html
• Doctrine ORM : associations extra-lazy — https://www.doctrine-project.org/projects/doctrine-orm/en/3.6/tutorials/extra-lazy-associations.html
• PHP : encodage JSON — https://www.php.net/manual/en/function.json-encode.php