Le problème des requêtes N+1 et comment l'éviter

#sql#databases#performance

Une page qui liste 50 articles de blog avec le nom de chaque auteur paraît instantanée en développement, avec une poignée de lignes de test, puis s'effondre en production dès que de vraies données arrivent, non pas parce que la requête est lente, mais parce que ce n'est pas une seule requête. Ce sont 51 requêtes : une pour récupérer les articles, puis une de plus par article pour récupérer son auteur. C'est le problème N+1, l'un des bugs de performance les plus courants dans les applications construites sur un ORM, précisément parce que l'ORM le rend invisible tant qu'on ne va pas le chercher.

Comment il apparaît

const posts = await Post.findAll(); // 1 query

for (const post of posts) {
  post.author = await User.findByPk(post.authorId); // N queries, one per post
}

Rien ici ne paraît faux : c'est lisible, c'est la façon évidente de l'écrire, et avec 5 articles de test, cela s'exécute en quelques millisecondes. Avec 5 000 articles en production, cela fait 5 001 allers-retours vers la base de données, et c'est la latence des allers-retours (et non le temps d'exécution des requêtes) qui domine. Même à 2 ms très rapides par aller-retour, cela fait 10 secondes passées presque entièrement à attendre le réseau, pour des données qu'une seule requête bien formée pourrait renvoyer en un aller-retour.

Le repérer

La signature, c'est un nombre de requêtes qui croît avec la taille d'un jeu de résultats. La plupart des ORM et frameworks permettent de journaliser chaque requête émise par une requête HTTP : activez cela pour une page suspecte et comptez. Si charger une liste de 10 éléments émet environ 11 requêtes, et une liste de 100 environ 101, c'est du N+1.

// Sequelize example: log every query for the duration of one request
const posts = await Post.findAll({ logging: console.log });

Voir la même forme SELECT * FROM users WHERE id = ? se répéter à chaque ligne, avec seulement le paramètre qui change, est le signe qui ne trompe pas.

La solution : le chargement anticipé (eager loading)

Chargez les données liées dans la même requête, ou dans une seule requête groupée de suivi, au lieu d'une requête par ligne.

Chargement anticipé par jointure, avec le chargement de relations intégré à l'ORM :

// Sequelize
const posts = await Post.findAll({ include: User }); // 1 query, joined

// Prisma
const posts = await prisma.post.findMany({ include: { author: true } });

// Django ORM
posts = Post.objects.select_related('author')

Cela émet une seule requête avec un JOIN, de sorte que les données de l'auteur reviennent rattachées à chaque ligne d'article dans le même jeu de résultats.

Chargement groupé, quand une jointure ne convient pas (récupération d'une grande collection liée par ligne, où la jointure multiplierait le nombre de lignes) :

// 2 queries total, not N+1
const posts = await Post.findAll();
const authorIds = [...new Set(posts.map((p) => p.authorId))];
const authors = await User.findAll({ where: { id: authorIds } });

const authorsById = new Map(authors.map((a) => [a.id, a]));
for (const post of posts) {
  post.author = authorsById.get(post.authorId);
}

Une requête pour les articles, une requête pour tous les auteurs référencés par ces articles (dédoublonnés avec le Set), puis la jointure en mémoire applicative. Deux allers-retours, qu'il y ait 10 ou 10 000 articles.

En SQL brut, la même idée

Si vous ne passez pas du tout par un ORM, l'équivalent du chargement anticipé consiste simplement à écrire la jointure vous-même au lieu de boucler et d'interroger :

-- N+1: one query per post, issued from application code in a loop
SELECT * FROM posts;
SELECT * FROM users WHERE id = ?; -- repeated per post

-- one query
SELECT posts.*, users.name AS author_name
FROM posts
JOIN users ON users.id = posts.author_id;

Le problème N+1 n'est pas vraiment un problème d'ORM : les ORM facilitent simplement l'écriture accidentelle du schéma boucle-puis-requête, puisque post.author ressemble à un accès normal à une propriété et masque la requête qu'il déclenche.

Le cas imbriqué : du N+1 un niveau plus bas

Le problème s'aggrave quand une autre relation se trouve un niveau plus bas, par exemple les commentaires de chaque article :

const posts = await Post.findAll({ include: User }); // fixed the first N+1

for (const post of posts) {
  post.comments = await Comment.findAll({ where: { postId: post.id } }); // reintroduced it
}

Corriger la recherche d'auteur ne sert à rien si les commentaires sont toujours récupérés dans une boucle par article. Chargez les relations imbriquées de la même façon :

const posts = await Post.findAll({ include: [User, Comment] });

Quand tout charger à l'avance est lui-même le problème

L'erreur inverse existe aussi : charger par anticipation chaque relation de chaque requête « au cas où » ramène bien plus de données que la plupart des requêtes n'en ont besoin, et peut transformer une recherche peu coûteuse sur une seule table en une requête joignant six tables à chaque fois. La solution n'est pas « toujours charger à l'avance », mais de charger ce qu'un chemin de code utilise réellement, ce qui signifie généralement préciser explicitement les inclusions par requête plutôt que de définir une valeur par défaut globale.

À retenir

Les requêtes N+1 sont invisibles en revue de code et sur un petit jeu de données de développement, et ne deviennent un vrai problème que lorsque des données à l'échelle de la production font du nombre de requêtes (et non de la complexité d'une requête isolée) le goulot d'étranglement. Observez les vrais logs de requêtes sur des données réalistes, et pas seulement la correction des requêtes sur une poignée de lignes de test, et recourez au chargement anticipé (une jointure ou une requête groupée de suivi) dès qu'une boucle émet une requête par itération.