Django QuerySet N+1 문제 해결 완벽 가이드

Django ORM은 편리하지만, 올바르게 사용하지 않으면 N+1 쿼리 문제가 발생하여 성능이 크게 저하될 수 있습니다.

Environment

$ python --version
Python 3.11.5

$ pip show django
Name: Django
Version: 4.2.5

$ pip show django-debug-toolbar
Name: django-debug-toolbar
Version: 4.2.0

Problem: N+1 쿼리 발생

# models.py
from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)
    email = models.EmailField()

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    published_date = models.DateField()
    
    def __str__(self):
        return self.title

# views.py
def book_list(request):
    books = Book.objects.all()
    return render(request, 'books/list.html', {'books': books})

{% for book in books %}
    

        
{{ book.title }}
        
Author: {{ book.author.name }}
        
Published: {{ book.published_date }}
    
{% endfor %}

실행 결과:

$ python manage.py shell
>>> from django.db import connection
>>> from books.models import Book

>>> books = Book.objects.all()
>>> for book in books:
...     print(f"{book.title} by {book.author.name}")

>>> print(f"Total queries: {len(connection.queries)}")
# Total queries: 101 (1 for books + 100 for each author)

Analysis: N+1 문제 원인 분석

# Debug SQL 출력
from django.db import connection
from django.conf import settings

settings.DEBUG = True

# 쿼리 로깅 활성화
import logging
logger = logging.getLogger('django.db.backends')
logger.setLevel(logging.DEBUG)

# 쿼리 확인
books = Book.objects.all()
for book in books:
    _ = book.author.name  # This triggers a new query!

SQL 로그 출력:

SELECT "books_book"."id", "books_book"."title", ... FROM "books_book";

-- For each book, this query is executed:
SELECT "books_author"."id", "books_author"."name", ... 
FROM "books_author" WHERE "books_author"."id" = 1;

SELECT "books_author"."id", "books_author"."name", ... 
FROM "books_author" WHERE "books_author"."id" = 2;

-- ... repeated for each book

Solution: select_related와 prefetch_related

select_related (ForeignKey, OneToOne)

# Before: N+1 queries
books = Book.objects.all()
for book in books:
    print(book.author.name)  # New query each time

# After: 1 query with JOIN
books = Book.objects.select_related('author').all()
for book in books:
    print(book.author.name)  # No additional query

SQL 생성:

SELECT "books_book"."id", "books_book"."title", ...,
       "books_author"."id", "books_author"."name", ...
FROM "books_book"
INNER JOIN "books_author" ON ("books_book"."author_id" = "books_author"."id");

prefetch_related (ManyToMany, reverse ForeignKey)

# models.py
class Category(models.Model):
    name = models.CharField(max_length=100)
    books = models.ManyToManyField(Book, related_name='categories')

# Before: N+1 queries
categories = Category.objects.all()
for category in categories:
    books = category.books.all()  # New query each time

# After: 2 queries
categories = Category.objects.prefetch_related('books').all()
for category in categories:
    books = category.books.all()  # Uses cached data

SQL 생성:

-- Query 1: Get all categories
SELECT "books_category"."id", "books_category"."name" FROM "books_category";

-- Query 2: Get all books for all categories
SELECT "books_book"."id", "books_book"."title", ...,
       "books_category"."id" AS "category_id"
FROM "books_book"
INNER JOIN "books_category_books" ON ("books_book"."id" = "books_category_books"."book_id")
WHERE "books_category_books"."category_id" IN (1, 2, 3, ...);

복잡한 관계에서의 최적화

# Multiple levels of related objects
books = Book.objects.select_related(
    'author',           # ForeignKey
    'publisher',        # ForeignKey
    'author__profile'   # Double relation
).prefetch_related(
    'categories',       # ManyToMany
    'reviews',          # Reverse ForeignKey
    Prefetch(
        'reviews',
        queryset=Review.objects.filter(rating__gte=4).select_related('user'),
        to_attr='top_reviews'  # Cache as attribute
    )
)

Lessons Learned

1. select_related: ForeignKey나 OneToOneField를 JOIN하여 단일 쿼리로 가져올 때 사용합니다.

2. prefetch_related: ManyToMany나 역방향 ForeignKey를 별도 쿼리로 가져올 때 사용합니다.

3. N+1 문제 식별: django-debug-toolbar나 django-silk를 사용하여 쿼리 수를 모니터링하세요.

4. Prefetch 객체: 복잡한 prefetch 조건이 필요하면 Prefetch 객체를 사용하여 세밀하게 제어할 수 있습니다.

5. 쿼리 수 확인: len(connection.queries)로 쿼리 수를 확인하고 최적화하세요.

This blog does not accept any external sponsorships, affiliate marketing, or ad revenue.