Requêtes N+1

TL; DR

# Bad: N+1 queries
for user in Users.objects.all():
    print(user.profile.bio)  # One query per user

# Good: Single query with select_related
users = User.objects.select_related('profile').all()
for user in users:
    print(user.profile.bio)  # No additional queries

En détails

Le point le plus important de notre requête ci-dessus (Book.objects.filter(author__name__icontains="hobb")) est que la valeur de retour ne sera constituée que d’instances de type Book : le fait de réaliser une requête filtrant sur un sous-ensemble de données n’agrège pas pour autant ces données. Expliqué en d’autres termes, le problème des requêtes N+1 peut être exprimé ainsi :

• Nous recherchons les livres dont l’auteur correspond à un critère de recherche,
• Nous récupérons l’ensemble des livres correspondand à cette recherche,
• Si nous voulons obtenir les informations de l’auteur correspond à nos critères initiaux, une deuxième requête devra être réalisée pour chacun des accès aux informations de cet auteur.

from django.db import connection
from library.models import Book


for book in Book.objects.filter(author__name__icontains="hobb"):
    print(book.author.name)


print(connection.queries)

[
    {
        'sql': """
            SELECT
                "library_book"."id", <1>
                "library_book"."title",
                "library_book"."author_id"
            FROM "library_book"
            INNER JOIN "library_author" ON
                ("library_book"."author_id" = "library_author"."id")
            WHERE "library_author"."name" LIKE \'%robb%\' ESCAPE \'\\\' LIMIT 21""",
        'time': '0.002'
    },
    {
        'sql': """
            SELECT
                "library_author"."id",
                "library_author"."name"
            FROM "library_author"
            WHERE "library_author"."id" = 1
            LIMIT 21""",
        'time': '0.000'
    }
]

Dans le code ci-dessus, nous trouvons bien deux requêtes :

1. La première pour récupérer les éléments correspondant à une requête spécifique,
2. La seconde pour récupérer les informations liées à la relation.

Ce morceau ne pose ici aucun problème : les requêtes sont extrêmement rapides et la base de connaissance est extrêmement réduite.

Danger

Les problèmes se présenteront dès lors que cette couche de persistance grossira (et dans notre exemple de bibliothèque, cela pourrait aller très vite cite:[owidbooks]) : pour chaque élément récupéré lors d’une requête, nous devrons réaliser une deuxième requête afin de récupérer les données relationnelles. Dis autrement, si nous récupérons 1000 éléments lors de notre première requête, nous devrons réaliser autant de nouvelles requêtes qu’il y a d’éléments dans cette première liste. Nous aurons donc :

1. Une première requête initiale afin de récupérer nos mille éléments,
2. Mille requêtes complémentaires.

from django.db import connection
from library.models import Book


for book in Book.objects.all()[:4]: <1>
    print(book.author.name)


print(connection.queries)

[
    {
        'sql': """
            SELECT
                'library_book'.'id',
                'library_book'.'title',
                'library_book'.'author_id'
            FROM 'library_book'""", <2>
        'time': '0.001'
    },
    {
        'sql': """
            SELECT
                "library_author"."id",
                "library_author"."name"
            FROM "library_author"
            WHERE "library_author"."id" = 1 LIMIT 21""", <3>
        'time': '0.000'
    },
    {
        'sql': """
            SELECT
                "library_author"."id",
                "library_author"."name"
            FROM "library_author"
            WHERE "library_author"."id" = 2 LIMIT 21""", <4>
        'time': '0.000'
    },{
        'sql': """
            SELECT
                "library_author"."id",
                "library_author"."name"
            FROM "library_author"
            WHERE "library_author"."id" = 3 LIMIT 21""", <5>
        'time': '0.000'
    },
    {
        'sql': """
            SELECT
                "library_author"."id",
                "library_author"."name"
            FROM "library_author"
            WHERE "library_author"."id" = 4 LIMIT 21""", <6>
        'time': '0.000'
    }
]

<1> Nous ne récupérons que les quatre premiers éléments de notre collection <2> Après quoi, <3> … nous réalisons … <4> … une nouvelle requête … <5> … pour chaque … <6> … auteur lié.

Les solutions pour éviter ces requêtes N+1 consistent à utiliser les méthodes .select_related() ou .prefetch_related() proposées par le queryset.

Select Related

La méthode .select_related() permet de réaliser une jointure externe - afin de conserver un élément, même si la relation demandée n’existe pas - et donc, de récupérer l’ensemble de nos données en une requête unique :

from django.db import connection
from library.models import Book


for book in Book.objects.select_related("author"): <1>
    print(book.author.name)


print(connection.queries)

[
    {
        'sql':
            'SELECT
                "library_book"."id",
                "library_book"."title",
                "library_book"."author_id",
                "library_author"."id",
                "library_author"."name"
            FROM "library_book"
            LEFT OUTER JOIN "library_author" <2>
                ON ("library_book"."author_id" = "library_author"."id")',
        'time': '0.002'
    }
]

<1> Nous réalisons ici une jointure externe au niveau de l’ORM <2> Seule une requête est réellement exécutée, malgré que nous avons plusieurs éléments dans notre base de données.

Le fonctionnement de cette méthode consiste simplement à paramétrer l’ensemble des relations que nous souhaitons interroger. Ainsi, pour récupérer nos auteurs en même temps que nos livres, il nous suffit d’écrire ceci : Book.objects.select_related("author").

Prefetch Related

Le fonctionnement de la méthode .prefetch_related() est identique à la méthode .select_related(), mais dans l’autre sens, en suivant les clés étrangères : pour rappel, nous avons défini une relation entre un livre et un auteur, où

• Chaque livre peut avoir un auteur,
• Un auteur peut être lié à plusieurs livres.

Il faut faire attention au prefetch related, qui fonctionne grâce à une grosse requête dans laquelle nous trouvons un IN (...). Dans cette optique,

1. Django récupère tous les objets demandés,
2. Pour ensuite prendre toutes les clés primaires,
3. Pour finalement faire une deuxième requête et récupérer les relations externes.

Au final, si votre premier queryset est relativement grand (nous parlons de 1000 à 2000 éléments, en fonction du moteur de base de données), cette seconde requête pourrait ne pas fonctionner et vous obtiendrez une exception de type django.db.utils.OperationalError: too many SQL variables, qu’il conviendra de gérer correctement.

Itérateurs

Django propose un mécanisme d’itérateurs, qui permettent d’éviter de mettre tout un queryset en mémoire.

# Bad: Loading entire objects
users = User.objects.all()

# Good: Only loading needed fields
users = User.objects.values('id', 'email')

# Better: Using iterator() for large querysets
for user in User.objects.iterator():
    process_user(user)

Indexes

Les indexes sur une base de données sont à double tranchant :

• Oubliez d’en mettre, et certaines requêtes pourraient prendre plus de temps que nécessaire,
• Mettez-en trop, et votre moteur de base de données mettra plus de temps à calculer la bonne manière de faire qu’à réellement faire son travail - sans parler de la place qui sera nécessaire en mémoire pour que tout ceci rentre au bon endroit.

Il est donc nécessaire de n’ajouter des indexes qu’après une (bonne) analyse. Une fois que vous l’aurez faite (l’analyse), vous pourrez les gérer directement à partir de votre modèle, comme le reste :

class Order(models.Model):
    user = models.ForeignKey(User)
    created_at = models.DateTimeField(auto_now_add=True)
    status = models.CharField(max_length=20)

    class Meta:
        indexes = [
            models.Index(fields=['created_at', 'status']),
            models.Index(fields=['user', 'status']),
        ]

Conclusions