N+1 문제 해결

MOU 목록을 조회하는 API를 개발하던 중에 Django Debug Toolbar를 통해 쿼리 로그를 확인해보니 심상치 않은 패턴이 보이기 시작했습니다. 메인 쿼리가 2개의 MOU 객체를 가져왔는데, 이와 관련된 ForeignKey 및  ManyToManyField 필드들을 가져오기 위해 각각의 MOU 객체에 대해 추가적인 쿼리들이 다음과 같이 반복적으로 발생하였습니다.

DEBUG (0.001) SELECT "users_user"."id", "users_user"."password", "users_user"."last_login", "users_user"."is_superuser", "users_user"."username", "users_user"."first_name", "users_user"."last_name", "users_user"."email", "users_user"."is_staff", "users_user"."date_joined", "users_user"."department", "users_user"."position", "users_user"."contact", "users_user"."is_active", "users_user"."deactivated_at" FROM "users_user" WHERE ("users_user"."is_active" AND "users_user"."id" = 1) LIMIT 21; args=(1,); alias=default

DEBUG (0.001) SELECT COUNT(*) AS "__count" FROM "mou_mou"; args=(); alias=default

DEBUG (0.000) SELECT "mou_mou"."id", "mou_mou"."created_at", "mou_mou"."updated_at", "mou_mou"."receipt_number", "mou_mou"."title", "mou_mou"."grade", "mou_mou"."requester_id", "mou_mou"."executor_id", "mou_mou"."reviewer_id", "mou_mou"."status", "mou_mou"."signer_id", "mou_mou"."qualitative_goals", "mou_mou"."quantitative_goals", "mou_mou"."expected_execution_date", "mou_mou"."business_division", "mou_mou"."mou_category_id", "mou_mou"."standard_form", "mou_mou"."execution_method", "mou_mou"."background", "mou_mou"."cooperation_summary", "mou_mou"."special_notes", "mou_mou"."current_manager_id" FROM "mou_mou" ORDER BY "mou_mou"."created_at" DESC LIMIT 2; args=(); alias=default

DEBUG (0.000) SELECT "mou_moucategory"."id", "mou_moucategory"."created_at", "mou_moucategory"."updated_at", "mou_moucategory"."name", "mou_moucategory"."description" FROM "mou_moucategory" WHERE "mou_moucategory"."id" = 12 LIMIT 21; args=(12,); alias=default

DEBUG (0.000) SELECT "users_user"."id", "users_user"."password", "users_user"."last_login", "users_user"."is_superuser", "users_user"."username", "users_user"."first_name", "users_user"."last_name", "users_user"."email", "users_user"."is_staff", "users_user"."date_joined", "users_user"."department", "users_user"."position", "users_user"."contact", "users_user"."is_active", "users_user"."deactivated_at" FROM "users_user" WHERE "users_user"."id" = 10 LIMIT 21; args=(10,); alias=default

DEBUG (0.001) SELECT "companies_counterparty"."id", "companies_counterparty"."created_at", "companies_counterparty"."updated_at", "companies_counterparty"."company_name", "companies_counterparty"."corporate_number", "companies_counterparty"."contact_person", "companies_counterparty"."position", "companies_counterparty"."company_contact", "companies_counterparty"."company_email" FROM "companies_counterparty" INNER JOIN "mou_mou_counterparties" ON ("companies_counterparty"."id" = "mou_mou_counterparties"."counterparty_id") WHERE "mou_mou_counterparties"."mou_id" = 49; args=(49,); alias=default

DEBUG (0.001) SELECT "companies_groupcompany"."id", "companies_groupcompany"."created_at", "companies_groupcompany"."updated_at", "companies_groupcompany"."name", "companies_groupcompany"."industry", "companies_groupcompany"."main_business", "companies_groupcompany"."person_in_charge_id" FROM "companies_groupcompany" INNER JOIN "mou_mou_group_companies" ON ("companies_groupcompany"."id" = "mou_mou_group_companies"."groupcompany_id") WHERE "mou_mou_group_companies"."mou_id" = 49 ORDER BY "companies_groupcompany"."name" ASC; args=(49,); alias=default

DEBUG (0.000) SELECT "mou_moucategory"."id", "mou_moucategory"."created_at", "mou_moucategory"."updated_at", "mou_moucategory"."name", "mou_moucategory"."description" FROM "mou_moucategory" WHERE "mou_moucategory"."id" = 11 LIMIT 21; args=(11,); alias=default

DEBUG (0.000) SELECT "users_user"."id", "users_user"."password", "users_user"."last_login", "users_user"."is_superuser", "users_user"."username", "users_user"."first_name", "users_user"."last_name", "users_user"."email", "users_user"."is_staff", "users_user"."date_joined", "users_user"."department", "users_user"."position", "users_user"."contact", "users_user"."is_active", "users_user"."deactivated_at" FROM "users_user" WHERE "users_user"."id" = 7 LIMIT 21; args=(7,); alias=default

DEBUG (0.000) SELECT "users_user"."id", "users_user"."password", "users_user"."last_login", "users_user"."is_superuser", "users_user"."username", "users_user"."first_name", "users_user"."last_name", "users_user"."email", "users_user"."is_staff", "users_user"."date_joined", "users_user"."department", "users_user"."position", "users_user"."contact", "users_user"."is_active", "users_user"."deactivated_at" FROM "users_user" WHERE "users_user"."id" = 6 LIMIT 21; args=(6,); alias=default

DEBUG (0.000) SELECT "companies_counterparty"."id", "companies_counterparty"."created_at", "companies_counterparty"."updated_at", "companies_counterparty"."company_name", "companies_counterparty"."corporate_number", "companies_counterparty"."contact_person", "companies_counterparty"."position", "companies_counterparty"."company_contact", "companies_counterparty"."company_email" FROM "companies_counterparty" INNER JOIN "mou_mou_counterparties" ON ("companies_counterparty"."id" = "mou_mou_counterparties"."counterparty_id") WHERE "mou_mou_counterparties"."mou_id" = 47; args=(47,); alias=default

DEBUG (0.000) SELECT "companies_groupcompany"."id", "companies_groupcompany"."created_at", "companies_groupcompany"."updated_at", "companies_groupcompany"."name", "companies_groupcompany"."industry", "companies_groupcompany"."main_business", "companies_groupcompany"."person_in_charge_id" FROM "companies_groupcompany" INNER JOIN "mou_mou_group_companies" ON ("companies_groupcompany"."id" = "mou_mou_group_companies"."groupcompany_id") WHERE "mou_mou_group_companies"."mou_id" = 47 ORDER BY "companies_groupcompany"."name" ASC; args=(47,); alias=default

[17/Jul/2025 05:04:10] "GET /mous/ HTTP/1.1" 200 2171

 

성능을 개선하기 위해 queryset에 select_related()와 prefetch_related()를 적용했습니다.

class MOUViewSet(viewsets.ModelViewSet):
    queryset = MOU.objects.all().order_by("-created_at").select_related(
        'requester', # User (ForeignKey)
        'executor',  # User (ForeignKey)
        'reviewer',  # User (ForeignKey)
        'signer',    # User (ForeignKey)
        'current_manager', # User (ForeignKey)
        'mou_category', # MOUCategory (ForeignKey)
    ).prefetch_related(
        'counterparties', # Counterparty (ManyToManyField)
        'group_companies', # GroupCompany (ManyToManyField)
    )
    serializer_class = MOUSerializer

    # ...

 

위와 같이 수정하여, MOU 객체들을 가져올 때 연관된 User, MOUCategory 객체들은 JOIN 쿼리를 통해 한 번에 가져오고, Counterparty와 GroupCompany 객체들은 별도의 효율적인 쿼리 한두 번으로 모두 가져온 후 Python 메모리에서 매칭시켜 N+1 문제를 해결했습니다.

DEBUG (0.000) SELECT "users_user"."id", "users_user"."password", "users_user"."last_login", "users_user"."is_superuser", "users_user"."username", "users_user"."first_name", "users_user"."last_name", "users_user"."email", "users_user"."is_staff", "users_user"."date_joined", "users_user"."department", "users_user"."position", "users_user"."contact", "users_user"."is_active", "users_user"."deactivated_at" FROM "users_user" WHERE ("users_user"."is_active" AND "users_user"."id" = 1) LIMIT 21; args=(1,); alias=default

DEBUG (0.001) SELECT COUNT(*) AS "__count" FROM "mou_mou"; args=(); alias=default

DEBUG (0.003) SELECT "mou_mou"."id", "mou_mou"."created_at", "mou_mou"."updated_at", "mou_mou"."receipt_number", "mou_mou"."title", "mou_mou"."grade", "mou_mou"."requester_id", "mou_mou"."executor_id", "mou_mou"."reviewer_id", "mou_mou"."status", "mou_mou"."signer_id", "mou_mou"."qualitative_goals", "mou_mou"."quantitative_goals", "mou_mou"."expected_execution_date", "mou_mou"."business_division", "mou_mou"."mou_category_id", "mou_mou"."standard_form", "mou_mou"."execution_method", "mou_mou"."background", "mou_mou"."cooperation_summary", "mou_mou"."special_notes", "mou_mou"."current_manager_id", "users_user"."id", "users_user"."password", "users_user"."last_login", "users_user"."is_superuser", "users_user"."username", "users_user"."first_name", "users_user"."last_name", "users_user"."email", "users_user"."is_staff", "users_user"."date_joined", "users_user"."department", "users_user"."position", "users_user"."contact", "users_user"."is_active", "users_user"."deactivated_at", T3."id", T3."password", T3."last_login", T3."is_superuser", T3."username", T3."first_name", T3."last_name", T3."email", T3."is_staff", T3."date_joined", T3."department", T3."position", T3."contact", T3."is_active", T3."deactivated_at", T4."id", T4."password", T4."last_login", T4."is_superuser", T4."username", T4."first_name", T4."last_name", T4."email", T4."is_staff", T4."date_joined", T4."department", T4."position", T4."contact", T4."is_active", T4."deactivated_at", T5."id", T5."password", T5."last_login", T5."is_superuser", T5."username", T5."first_name", T5."last_name", T5."email", T5."is_staff", T5."date_joined", T5."department", T5."position", T5."contact", T5."is_active", T5."deactivated_at", "mou_moucategory"."id", "mou_moucategory"."created_at", "mou_moucategory"."updated_at", "mou_moucategory"."name", "mou_moucategory"."description", T7."id", T7."password", T7."last_login", T7."is_superuser", T7."username", T7."first_name", T7."last_name", T7."email", T7."is_staff", T7."date_joined", T7."department", T7."position", T7."contact", T7."is_active", T7."deactivated_at" FROM "mou_mou" LEFT OUTER JOIN "users_user" ON ("mou_mou"."requester_id" = "users_user"."id") LEFT OUTER JOIN "users_user" T3 ON ("mou_mou"."executor_id" = T3."id") LEFT OUTER JOIN "users_user" T4 ON ("mou_mou"."reviewer_id" = T4."id") LEFT OUTER JOIN "users_user" T5 ON ("mou_mou"."signer_id" = T5."id") LEFT OUTER JOIN "mou_moucategory" ON ("mou_mou"."mou_category_id" = "mou_moucategory"."id") LEFT OUTER JOIN "users_user" T7 ON ("mou_mou"."current_manager_id" = T7."id") ORDER BY "mou_mou"."created_at" DESC LIMIT 2; args=(); alias=default

DEBUG (0.001) SELECT ("mou_mou_counterparties"."mou_id") AS "_prefetch_related_val_mou_id", "companies_counterparty"."id", "companies_counterparty"."created_at", "companies_counterparty"."updated_at", "companies_counterparty"."company_name", "companies_counterparty"."corporate_number", "companies_counterparty"."contact_person", "companies_counterparty"."position", "companies_counterparty"."company_contact", "companies_counterparty"."company_email" FROM "companies_counterparty" INNER JOIN "mou_mou_counterparties" ON ("companies_counterparty"."id" = "mou_mou_counterparties"."counterparty_id") WHERE "mou_mou_counterparties"."mou_id" IN (49, 47); args=(49, 47); alias=default

DEBUG (0.001) SELECT ("mou_mou_group_companies"."mou_id") AS "_prefetch_related_val_mou_id", "companies_groupcompany"."id", "companies_groupcompany"."created_at", "companies_groupcompany"."updated_at", "companies_groupcompany"."name", "companies_groupcompany"."industry", "companies_groupcompany"."main_business", "companies_groupcompany"."person_in_charge_id" FROM "companies_groupcompany" INNER JOIN "mou_mou_group_companies" ON ("companies_groupcompany"."id" = "mou_mou_group_companies"."groupcompany_id") WHERE "mou_mou_group_companies"."mou_id" IN (49, 47) ORDER BY "companies_groupcompany"."name" ASC; args=(49, 47); alias=default

[17/Jul/2025 04:59:23] "GET /mous/ HTTP/1.1" 200 2171

• select_related(): ForeignKey나 OneToOneField처럼 1:N 또는 1:1 관계를 SQL JOIN으로 한 번에 가져옵니다.
• prefetch_related(): ManyToManyField나 역방향 ForeignKey처럼 N:M 또는 N:1 관계를 별도의 효율적인 쿼리로 가져온 후 Python 메모리에서 매칭합니다.

성능이 정말 개선되었는지 확인해보기 위해 MOU 데이터의 개수를 100 으로 증가시켜 보았습니다. 

• N+1 문제 발생 시: 68개의 쿼리, 전체 응답 시간 53.42ms으로 만약, 데이터가 더 커진다면 수천 개 이상의 쿼리가 발생하여 응답 시간이 수 초에서 수십 초까지 늘어나는 심각한 성능 저하가 발생합니다. 각 쿼리가 아무리 빨라도 잦은 통신으로 인한 오버헤드가 쌓이기 때문입니다. 

• select_related / prefetch_related 적용 후: 데이터 개수 N이 100개로 늘어나도, 총 쿼리 수는 여전히 17개 정도로 유지됩니다. 쿼리 자체의 실행 시간은 늘어날 수 있지만, 전체 응답 시간은 14.69ms로 N+1 문제 상황에 비해 훨씬 짧고 안정적입니다.