Doctrine et MySQL : en finir avec le problème N+1 et les requêtes lentes

Votre page de liste s'affiche en une fraction de seconde avec dix produits en base. Six mois plus tard, avec des milliers de produits, elle met trois secondes. Le coupable est très souvent le même : pas une requête lente, mais des centaines de petites requêtes que Doctrine exécute sans que vous l'ayez demandé. C'est le fameux problème N+1. Voici comment le repérer, le corriger, et aider MySQL à répondre plus vite.

Le problème N+1

Prenons une boutique avec des produits, chacun dans une catégorie :

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    #[ORM\ManyToOne(inversedBy: 'products')]
    #[ORM\JoinColumn(nullable: false)]
    private ?Category $category = null;

    #[ORM\OneToMany(targetEntity: Review::class, mappedBy: 'product')]
    private Collection $reviews;

    // ...
}

Le contrôleur récupère les produits de la façon la plus simple :

$products = $productRepository->findAll();

Et le template affiche la catégorie de chacun :

{% for product in products %}
    <li>{{ product.name }} ({{ product.category.name }})</li>
{% endfor %}

Tout fonctionne, mais regardez ce qui se passe réellement. findAll() exécute une requête pour récupérer les produits. Doctrine ne charge pas les catégories : à leur place, il met des objets « proxy » vides. Au moment où Twig lit product.category.name, Doctrine s'aperçoit qu'il ne connaît pas encore cette catégorie, et exécute une nouvelle requête. Pour 100 produits, on obtient donc 1 requête pour la liste, plus jusqu'à 100 requêtes pour les catégories : N+1.

Ce comportement s'appelle le lazy loading, le chargement paresseux. Il est pratique, puisque les données sont chargées seulement quand on en a besoin, mais il devient un piège dès qu'on parcourt une liste.

Le repérer avec le Profiler

En développement, la barre de debug de Symfony affiche le nombre de requêtes SQL de chaque page, avec l'icône de base de données. Un chiffre élevé sur une page de liste est le premier signal d'alerte.

En cliquant dessus, l'onglet Doctrine du Profiler liste toutes les requêtes. Le bouton « Group similar statements » est particulièrement utile : il regroupe les requêtes identiques. Si vous voyez la même requête exécutée 100 fois avec un paramètre différent, vous tenez votre N+1 :

SELECT t0.id, t0.name FROM category t0 WHERE t0.id = ?

La correction : la jointure avec addSelect

La solution consiste à demander à Doctrine de charger les catégories dans la même requête que les produits. On écrit pour cela une méthode dans le repository :

// src/Repository/ProductRepository.php

/**
 * @return Product[]
 */
public function findAllWithCategory(): array
{
    return $this->createQueryBuilder('p')
        ->addSelect('c')
        ->innerJoin('p.category', 'c')
        ->orderBy('p.name', 'ASC')
        ->getQuery()
        ->getResult();
}

Le point clé est addSelect('c'). Un innerJoin() seul ajouterait bien la jointure en SQL, mais Doctrine ne remplirait pas les catégories : il faut lui demander explicitement de les sélectionner. C'est ce qu'on appelle un fetch join.

Résultat : une seule requête, quel que soit le nombre de produits.

SELECT p0_.*, c1_.* FROM product p0_
INNER JOIN category c1_ ON p0_.category_id = c1_.id
ORDER BY p0_.name ASC

Utilisez innerJoin() quand la relation est obligatoire, comme ici, et leftJoin() quand elle peut être vide : sinon, les produits sans catégorie disparaîtraient du résultat.

Le piège des collections : compter sans tout charger

Deuxième cas très courant : afficher le nombre d'avis de chaque produit.

{{ product.reviews|length }} avis

C'est encore pire que le N+1 précédent : pour chaque produit, Doctrine charge tous ses avis en mémoire, uniquement pour les compter. Avec des centaines d'avis par produit, la page devient très lente et très gourmande en mémoire.

Première amélioration possible : le mode EXTRA_LAZY sur la relation.

#[ORM\OneToMany(targetEntity: Review::class, mappedBy: 'product', fetch: 'EXTRA_LAZY')]
private Collection $reviews;

Avec ce mode, un count() sur la collection exécute un simple SELECT COUNT(*) au lieu de charger les avis. C'est mieux, mais on reste à une requête par produit.

La vraie solution est de laisser MySQL faire le calcul, dans la requête de la liste :

public function findAllWithReviewCount(): array
{
    return $this->createQueryBuilder('p')
        ->select('p', 'c', 'COUNT(r.id) AS reviewCount')
        ->innerJoin('p.category', 'c')
        ->leftJoin('p.reviews', 'r')
        ->groupBy('p.id', 'c.id')
        ->orderBy('p.name', 'ASC')
        ->getQuery()
        ->getResult();
}

Quand une requête sélectionne à la fois une entité et une valeur calculée, Doctrine renvoie pour chaque ligne un tableau : l'entité à l'index 0, et la valeur sous son alias.

{% for row in products %}
    <li>{{ row[0].name }} ({{ row[0].category.name }}) : {{ row.reviewCount }} avis</li>
{% endfor %}

Une seule requête, et aucun avis chargé en mémoire.

Ne charger que ce qu'on affiche

Pour une liste ou un export, on n'a souvent besoin que de quelques champs. Plutôt que de charger des entités complètes, Doctrine peut créer directement des objets simples, appelés DTO :

// src/Dto/ProductListItem.php
final readonly class ProductListItem
{
    public function __construct(
        public int $id,
        public string $name,
        public string $categoryName,
        public int $reviewCount,
    ) {
    }
}

public function findListItems(): array
{
    return $this->getEntityManager()->createQuery(
        'SELECT NEW App\Dto\ProductListItem(p.id, p.name, c.name, COUNT(r.id))
         FROM App\Entity\Product p
         JOIN p.category c
         LEFT JOIN p.reviews r
         GROUP BY p.id, c.name
         ORDER BY p.name ASC'
    )->getResult();
}

La requête SQL ne récupère que les colonnes utiles, et Doctrine n'a pas à gérer des entités qu'on ne modifiera jamais. Sur de gros volumes, la différence de mémoire et de temps est nette.

Aider MySQL avec les bons index

Une fois le nombre de requêtes réduit, il reste à s'assurer que chacune est rapide. Pour les clés étrangères, bonne nouvelle : Doctrine crée automatiquement un index sur chaque colonne de jointure, comme category_id. En revanche, les colonnes sur lesquelles vous filtrez ou triez n'en ont pas.

Imaginons la requête la plus fréquente de la boutique : les produits en stock, du plus récent au plus ancien.

$qb->where('p.inStock = true')
    ->orderBy('p.createdAt', 'DESC');

On déclare un index adapté directement sur l'entité :

#[ORM\Entity(repositoryClass: ProductRepository::class)]
#[ORM\Index(name: 'product_stock_created', columns: ['in_stock', 'created_at'])]
class Product

Attention, les noms de colonnes sont ceux de la base de données (in_stock), pas ceux des propriétés PHP (inStock). Puis on génère et on applique la migration :

php bin/console make:migration
php bin/console doctrine:migrations:migrate

L'ordre des colonnes compte : un index sur (in_stock, created_at) sert à filtrer sur in_stock puis à trier par created_at. L'inverse ne serait pas aussi efficace pour cette requête.

Pour vérifier que MySQL utilise bien l'index, le Profiler propose un bouton « Explain query » sous chaque requête. Dans le résultat, la colonne type à ALL signale un parcours complet de la table : MySQL lit toutes les lignes. Si elle affiche ref ou range, et que la colonne key indique le nom de votre index, c'est gagné.

Les traitements de masse

Dernier piège : les commandes qui parcourent toute une table, pour un export ou une mise à jour de masse. Un findAll() sur 100 000 produits charge tout en mémoire d'un coup, et le script finit par planter. Doctrine permet de parcourir les résultats un par un :

$query = $em->createQuery('SELECT p FROM App\Entity\Product p');

$i = 0;
foreach ($query->toIterable() as $product) {
    // traitement du produit...

    if (++$i % 100 === 0) {
        $em->flush();
        $em->clear();
    }
}

$em->flush();
$em->clear();

toIterable() récupère les produits au fur et à mesure, et clear() vide régulièrement la mémoire de Doctrine, qui garde sinon une trace de chaque entité chargée. La consommation de mémoire reste ainsi stable, quel que soit le nombre de lignes.

Protéger le résultat avec un test

Une fois la page optimisée, rien n'empêche un futur changement de template de réintroduire un N+1. Un test fonctionnel peut vérifier le nombre de requêtes grâce au Profiler :

public function testProductListDoesNotTriggerNPlusOne(): void
{
    $client = static::createClient();
    $client->enableProfiler();

    $client->request('GET', '/produits');

    self::assertResponseIsSuccessful();

    $queryCount = $client->getProfile()->getCollector('db')->getQueryCount();
    self::assertLessThanOrEqual(3, $queryCount);
}

Si quelqu'un ajoute un jour l'affichage d'une relation sans jointure, le test échoue immédiatement, bien avant la mise en production.

En résumé

• Surveillez le nombre de requêtes dans la barre de debug, surtout sur les pages de liste.
• Utilisez addSelect() avec vos jointures pour charger les relations affichées en une seule requête.
• Comptez avec MySQL, avec COUNT() et GROUP BY, plutôt que de charger des collections entières.
• Ne chargez que ce qu'il faut, avec des DTO pour les listes et les exports.
• Indexez les colonnes filtrées et triées, et vérifiez-le avec « Explain query ».
• Utilisez toIterable() et clear() pour les traitements de masse.

La plupart de ces optimisations ne prennent que quelques lignes, et elles font souvent passer une page de plusieurs secondes à quelques millisecondes.

Et vous, quel est le pire N+1 que vous ayez rencontré ? Racontez-le en commentaire !