Barcha yozuvlar
Dasturlash

N+1 muammosi: bitta sahifa, 500 ta so'rov — select_related, prefetch_related va so'rovlarni sanash

Lokalda sahifa bir zumda ochiladi, production'da esa sekinlashadi — chunki template har bir qatorda bazaga alohida murojaat qiladi. N+1 ni qanday topish, test bilan qanday qulflash va select_related hamda prefetch_related'ni qachon ishlatishni ko'rib chiqamiz.

Tasavvur qiling: buyurtmalar ro'yxati sahifasi. Har bir buyurtmada mijozning ismi va ichidagi mahsulotlar ko'rsatiladi. Lokalda 5 ta test buyurtma bor, sahifa bir zumda ochiladi. Production'da esa sahifada 100 ta buyurtma chiqadi, har birida o'rtacha 3 tadan mahsulot — va sahifa sezilarli sekinlashadi. Kodda sikl ham, og'ir hisob-kitob ham yo'q. Ammo bazaga bitta emas, taxminan 500 ta so'rov ketyapti.

Bu — N+1 muammosi. Django'da eng ko'p uchraydigan performance xatolaridan biri, chunki u kodda ko'rinmaydi: so'rovlar template yoki serializer ichida, oddiy atributga murojaat qilinganda "jimgina" bajariladi.

Muammo qayerdan paydo bo'ladi

Modellar oddiy:

# shop/models.py
from django.db import models


class Customer(models.Model):
    name = models.CharField(max_length=200)


class Product(models.Model):
    title = models.CharField(max_length=200)
    price = models.DecimalField(max_digits=12, decimal_places=2)


class Order(models.Model):
    customer = models.ForeignKey(
        Customer, on_delete=models.PROTECT, related_name="orders"
    )
    status = models.CharField(max_length=20, default="new")
    created_at = models.DateTimeField(auto_now_add=True)


class OrderItem(models.Model):
    order = models.ForeignKey(Order, on_delete=models.CASCADE, related_name="items")
    product = models.ForeignKey(Product, on_delete=models.PROTECT)
    quantity = models.PositiveIntegerField(default=1)

View ham juda begunoh ko'rinadi:

# shop/views.py
from django.shortcuts import render

from .models import Order


def order_list(request):
    orders = Order.objects.order_by("-created_at")[:100]
    return render(request, "shop/order_list.html", {"orders": orders})

Template esa shunday:

{% for order in orders %}
  <h3>#{{ order.id }} — {{ order.customer.name }}</h3>
  <ul>
    {% for item in order.items.all %}
      <li>{{ item.product.title }} × {{ item.quantity }}</li>
    {% endfor %}
  </ul>
{% endfor %}

Endi so'rovlarni sanab chiqamiz:

  1. orders — buyurtmalar ro'yxati uchun 1 ta so'rov.
  2. order.customer.name — har bir buyurtma uchun mijozni olish: 100 ta so'rov.
  3. order.items.all — har bir buyurtmaning qatorlari: yana 100 ta.
  4. item.product.title — har bir qator uchun mahsulot: 100 × 3 = 300 ta.

Jami 1 + 100 + 100 + 300 = 501. Mana shu "1 + N" (bizda esa "1 + N + N + N×M") — muammoning nomi shundan.

DRF'da ham xuddi shu narsa bo'ladi, faqat yanada yashirinroq:

# shop/serializers.py
from rest_framework import serializers

from .models import Order, OrderItem


class OrderItemSerializer(serializers.ModelSerializer):
    product_title = serializers.CharField(source="product.title")

    class Meta:
        model = OrderItem
        fields = ["id", "product_title", "quantity"]


class OrderSerializer(serializers.ModelSerializer):
    customer_name = serializers.CharField(source="customer.name")
    items = OrderItemSerializer(many=True)

    class Meta:
        model = Order
        fields = ["id", "status", "customer_name", "items"]

source="customer.name" va nested OrderItemSerializer — bular ham har bir obyekt uchun alohida so'rov. Serializer'ni o'qib, bazaga murojaat borligini sezish qiyin.

Nega bu "oddiy" kod ishlamaydi

Gap Django ORM'ning lazy (dangasa) ishlashida. Order.objects.order_by(...) hali hech qanday SQL bajarmaydi — u faqat QuerySet obyekti yaratadi. So'rov template'dagi {% for %} QuerySet'ni iterate qila boshlaganda bajariladi.

order.customer esa ForeignKey descriptor. Siz unga murojaat qilganingizda Django obyekt ichidagi cache'ga qaraydi; agar mijoz u yerda bo'lmasa, SELECT ... FROM shop_customer WHERE id = %s bajaradi. Reverse bog'lanish order.items.all() ham har safar yangi QuerySet qaytaradi, va u alohida WHERE order_id = %s so'rovi bilan baholanadi.

Har bir so'rov o'zi tez — index bo'yicha bitta qator olish millisekundning bir qismi bo'lishi mumkin. Lekin har bir so'rovda network round-trip, SQL'ni parse qilish, connection bilan ishlash va Python tomonida natijani obyektga aylantirish bor. 500 marta takrorlansa, bular sahifa vaqtining asosiy qismiga aylanadi. Eng yomoni — so'rovlar soni ma'lumot hajmi bilan birga o'sadi: test bazada sezilmaydi, production'da esa kuchayib boradi.

N+1 — sekin so'rov emas, balki ko'p tez so'rovlar. Shuning uchun uni EXPLAIN bilan emas, so'rovlarni sanab topasiz.

So'rovlarni sanash: connection.queries, CaptureQueriesContext, assertNumQueries

Eng oddiy usul — django.db.connection.queries. Lekin u faqat DEBUG=True bo'lganda to'ldiriladi. Qulayroq vosita — CaptureQueriesContext, u DEBUG'ga bog'liq emas va istalgan joyda ishlaydi:

# python manage.py shell
from django.db import connection
from django.template.loader import render_to_string
from django.test.utils import CaptureQueriesContext

from shop.models import Order

orders = Order.objects.order_by("-created_at")[:100]

with CaptureQueriesContext(connection) as ctx:
    render_to_string("shop/order_list.html", {"orders": orders})

print(len(ctx.captured_queries))
for q in ctx.captured_queries[:5]:
    print(q["sql"])

E'tibor bering: template'ni context ichida render qilyapmiz. QuerySet lazy bo'lgani uchun, agar faqat orders = ... qatorini o'rab qo'ysangiz, 0 ta so'rov ko'rasiz — haqiqiy ish render paytida bo'ladi.

Development'da django-debug-toolbar ham juda foydali: u har bir sahifa uchun so'rovlar ro'yxatini ko'rsatadi va bir xil SQL takrorlansa, buni alohida belgilaydi.

Lekin eng muhimi — test. N+1 bir marta tuzatilgach, kimdir template'ga yangi {{ order.customer.phone }} qo'shib yana buzib qo'yishi mumkin. Buni assertNumQueries bilan qulflab qo'yamiz:

# shop/tests.py
from django.test import TestCase
from django.urls import reverse

from .models import Customer, Order, OrderItem, Product


class OrderListQueryCountTest(TestCase):
    @classmethod
    def setUpTestData(cls):
        for i in range(10):
            customer = Customer.objects.create(name=f"Customer {i}")
            order = Order.objects.create(customer=customer)
            for j in range(3):
                product = Product.objects.create(title=f"P{i}-{j}", price=10)
                OrderItem.objects.create(order=order, product=product, quantity=1)

    def test_query_count_does_not_grow_with_rows(self):
        with self.assertNumQueries(2):
            response = self.client.get(reverse("order-list"))
        self.assertEqual(response.status_code, 200)

Bu yerda muhim nuqta: test ma'lumotida bir nechta buyurtma va har birida bir nechta qator bo'lishi kerak. Agar bitta buyurtma bilan test qilsangiz, N+1 ham "2 ta so'rov" bo'lib ko'rinishi mumkin. Shuningdek, middleware (session, auth) o'z so'rovlarini qo'shishi mumkin — kutilgan sonni bir marta qo'lda tekshirib oling.

select_related "to-one" bog'lanishlar uchun: ForeignKey va OneToOneField (shu jumladan reverse one-to-one). Django asosiy so'rovga JOIN qo'shadi va bog'liq obyektni o'sha qatordan quradi. Natijada order.customer uchun alohida so'rov kerak bo'lmaydi — obyekt allaqachon cache'da.

ForeignKey null=True bo'lsa, Django INNER JOIN o'rniga LEFT OUTER JOIN ishlatadi, shunda mijozi yo'q buyurtmalar ham natijadan tushib qolmaydi.

"To-many" bog'lanishlar (reverse ForeignKey, ManyToMany) uchun JOIN yomon variant: har bir buyurtma qatori uning item'lari soniga ko'payib ketadi. Shuning uchun prefetch_related boshqacha ishlaydi:

  1. Avval asosiy so'rov bajariladi va buyurtmalar ro'yxati olinadi.
  2. Django ularning idlarini yig'adi.
  3. Bitta qo'shimcha so'rov yuboradi: WHERE order_id IN (...).
  4. Natijani Python tomonida order_id bo'yicha guruhlaydi va har bir buyurtmaning prefetch cache'iga joylaydi.

Endi tuzatilgan view:

# shop/views.py
from django.db.models import Prefetch
from django.shortcuts import render

from .models import Order, OrderItem


def order_list(request):
    items = OrderItem.objects.select_related("product").order_by("id")
    orders = (
        Order.objects.select_related("customer")
        .prefetch_related(Prefetch("items", queryset=items))
        .order_by("-created_at")[:100]
    )
    return render(request, "shop/order_list.html", {"orders": orders})

Natijada bazaga ketadigan SQL taxminan shunday ko'rinadi:

-- 1) select_related("customer"): one JOIN
SELECT "shop_order"."id", "shop_order"."customer_id", "shop_order"."status",
       "shop_order"."created_at", "shop_customer"."id", "shop_customer"."name"
FROM "shop_order"
INNER JOIN "shop_customer" ON ("shop_order"."customer_id" = "shop_customer"."id")
ORDER BY "shop_order"."created_at" DESC
LIMIT 100;

-- 2) prefetch_related("items") + select_related("product"): one IN query
SELECT "shop_orderitem"."id", "shop_orderitem"."order_id",
       "shop_orderitem"."product_id", "shop_orderitem"."quantity",
       "shop_product"."id", "shop_product"."title", "shop_product"."price"
FROM "shop_orderitem"
INNER JOIN "shop_product" ON ("shop_orderitem"."product_id" = "shop_product"."id")
WHERE "shop_orderitem"."order_id" IN (412, 411, 409, 408 /* ... */)
ORDER BY "shop_orderitem"."id" ASC;

Buyurtmalar 100 ta bo'ladimi, 10 ta bo'ladimi — har doim 2 ta so'rov. Testimiz ham aynan shuni tekshiradi.

select_related prefetch_related
Bog'lanish turi ForeignKey, OneToOne reverse FK, ManyToMany, GenericRelation
SQL asosiy so'rovga JOIN alohida so'rov, IN (...)
So'rovlar soni 1 1 + har bir bog'lanish uchun 1
Birlashtirish baza tomonida Python tomonida
Xavf juda ko'p JOIN, keng qatorlar juda uzun IN ro'yxati, ko'p xotira

Prefetch obyekti: qaysi qatorlar va qayerga

prefetch_related("items") barcha bog'liq qatorlarni oladi. Ko'pincha bu yetarli emas: faqat to'langan buyurtmalar kerak, ma'lum tartibda, yoki ichki select_related bilan. Buning uchun Prefetch obyekti bor — biz uni yuqorida items + product uchun ishlatdik.

to_attr bilan natijani alohida atributga oddiy list ko'rinishida saqlash mumkin. Django 4.2 dan boshlab Prefetch queryset'ini slice qilish ham mumkin — masalan, har bir mijozning oxirgi 3 ta to'langan buyurtmasi:

from django.db.models import Prefetch

from shop.models import Customer, Order

last_paid = Prefetch(
    "orders",
    queryset=Order.objects.filter(status="paid").order_by("-created_at")[:3],
    to_attr="last_paid_orders",
)

customers = Customer.objects.prefetch_related(last_paid)

for customer in customers:
    for order in customer.last_paid_orders:  # plain list, no extra query
        print(customer.name, order.id)

to_attr ishlatishning yana bir afzalligi bor: filtrlangan natija standart customer.orders.all() bilan aralashib ketmaydi. Kod o'quvchisi last_paid_orders nima ekanini nomidan tushunadi.

DRF'da tuzatish joyi — view'ning get_queryset metodi. Serializer'ni o'zgartirish shart emas:

# shop/api.py
from django.db.models import Prefetch
from rest_framework import generics

from .models import Order, OrderItem
from .serializers import OrderSerializer


class OrderListAPIView(generics.ListAPIView):
    serializer_class = OrderSerializer

    def get_queryset(self):
        return (
            Order.objects.select_related("customer")
            .prefetch_related(
                Prefetch("items", queryset=OrderItem.objects.select_related("product"))
            )
            .order_by("-created_at")
        )

Async kodda lazy loading nega xato beradi

Django 4.1 dan beri QuerySet'ning async API'si bor: aget(), afirst(), acount(), async for. Shuning uchun quyidagi kod tabiiy ko'rinadi:

# shop/views.py
from django.http import JsonResponse

from .models import Order


async def order_detail(request, pk):
    order = await Order.objects.aget(pk=pk)
    # SynchronousOnlyOperation: You cannot call this from an async context ...
    return JsonResponse({"id": order.id, "customer": order.customer.name})

aget() ishlaydi, lekin order.customer.name SynchronousOnlyOperation xatosini beradi. Sababi: atributga murojaat qilish — oddiy sinxron operatsiya, uning oldiga await qo'yib bo'lmaydi. Lazy loading esa aynan shu joyda bazaga sinxron so'rov yuborishi kerak. Django ORM'ning sinxron qismi esa event loop ichidan chaqirilsa, loop'ni bloklamaslik uchun ataylab xato beradi.

Ya'ni async kontekstda lazy loading umuman yo'q — bu aslida N+1 dan himoya. Barcha kerakli ma'lumotni oldindan, aniq yuklashingiz kerak:

# shop/views.py
from django.db.models import Prefetch
from django.http import JsonResponse

from .models import Order, OrderItem


async def order_detail(request, pk):
    order = await (
        Order.objects.select_related("customer")
        .prefetch_related(
            Prefetch("items", queryset=OrderItem.objects.select_related("product"))
        )
        .aget(pk=pk)
    )
    items = [
        {"product": item.product.title, "quantity": item.quantity}
        for item in order.items.all()  # already in memory, no DB call
    ]
    return JsonResponse(
        {"id": order.id, "customer": order.customer.name, "items": items}
    )

aget() ichida select_related va prefetch_related odatdagidek bajariladi, keyin esa order.customer va order.items.all() xotiradan o'qiladi. Agar bog'lanishni oldindan yuklamagan bo'lsangiz, async for item in order.items.all() yoki await order.items.acount() kabi async metodlardan foydalaning — lekin sikl ichida bu yana N+1 bo'lishini unutmang. Shuningdek, Django 5.0 dan boshlab aiterator() ham prefetch_related bilan ishlaydi.

Yana bir tuzoq: async view ichida render() chaqirilsa, template ham o'sha kontekstda ishlaydi. Template ichidagi {{ order.customer.name }} ham xuddi shu xatoni beradi.

Production'da uchraydigan tuzoqlar

Prefetch cache'ni o'z qo'lingiz bilan buzish. Prefetch faqat .all() uchun ishlaydi. Bog'liq manager'da filter(), exclude() yoki order_by() chaqirsangiz, Django yangi so'rov yuboradi:

orders = Order.objects.prefetch_related("items")

for order in orders:
    order.items.all()                      # from prefetch cache
    order.items.count()                    # from cache: len() of cached results
    order.items.filter(quantity__gt=1)     # NEW query for every order
    order.items.order_by("-quantity")      # NEW query for every order

Filtr kerak bo'lsa — uni Prefetch(queryset=...) ichiga o'tkazing. Faqat son kerak bo'lsa, umuman prefetch qilmang, bazaning o'zida hisoblang:

from django.db.models import Count, Q

orders = Order.objects.annotate(
    items_total=Count("items"),
    bulk_items=Count("items", filter=Q(items__quantity__gt=1)),
)

only() va defer(). Agar Order.objects.only("id") qilib, keyin template'da order.statusni ishlatsangiz, Django har bir obyekt uchun qoldirilgan maydonni alohida so'rov bilan yuklaydi. Bu ham N+1, faqat boshqa ko'rinishda.

order.customer_id va order.customer.id. Birinchisi obyektning o'zida turgan ustun, so'rov kerak emas. Ikkinchisi esa (agar select_related bo'lmasa) mijozni bazadan yuklaydi. Faqat ID kerak bo'lsa — _id atributidan foydalaning.

Haddan tashqari ko'p JOIN. select_related() argumentsiz ham chaqirilishi mumkin — u null=False bo'lgan barcha ForeignKey'larni zanjir bo'yicha JOIN qiladi. Bu juda keng qatorlar va keraksiz ma'lumot degani. Har doim aniq maydonlarni yozing.

Juda uzun IN ro'yxati. Butun jadvalni pagination'siz prefetch qilsangiz, IN (...) ichiga o'n minglab ID tushadi va hammasi xotiraga yuklanadi. Ro'yxat sahifalarida pagination ishlating; fon vazifalarida esa iterator(chunk_size=...) — Django 4.1 dan beri u chunk_size berilganda prefetch_related bilan ishlaydi.

__str__ ichidagi bog'lanish. Agar Order.__str__ self.customer.nameni qaytarsa, admin, log'lar va select maydonlar ham N+1 ga aylanadi. Admin uchun list_select_related yoki get_queryset ni override qiling.

Model property'lari. Model property'si ichida bog'liq obyektga murojaat qilish — template'da ko'rinmaydigan N+1 ning klassik manbai. Property'ni ro'yxatda ishlatsangiz, uning orqasida qanday so'rov borligini tekshiring.

Amaliy checklist

  • Har bir ro'yxat sahifasi va list endpoint uchun assertNumQueries bilan test yozing; test ma'lumotida bir nechta obyekt bo'lsin.
  • ForeignKey/OneToOne — select_related, reverse FK va ManyToMany — prefetch_related.
  • Bog'liq qatorlarni filtrlash, tartiblash yoki ichki select_related kerak bo'lsa — Prefetch(queryset=..., to_attr=...).
  • Faqat son yoki yig'indi kerak bo'lsa — annotate(Count(...)), prefetch emas.
  • DRF'da optimizatsiyani get_queryset ichida qiling, serializer'dagi har bir source="a.b" ni tekshiring.
  • Async view'larda hamma narsani oldindan yuklang; SynchronousOnlyOperation — "bu yerda lazy loading bor" degan signal.
  • Development'da django-debug-toolbar yoki CaptureQueriesContext bilan takrorlanuvchi SQL'larni qidiring.
  • Faqat ID kerak bo'lsa — obj.fk_id.

Xulosa

N+1 sekin so'rovlardan emas, ma'lumot bilan birga o'sadigan so'rovlar sonidan kelib chiqadi, shuning uchun uni sanash orqali topasiz va assertNumQueries bilan qulflaysiz. select_related to-one bog'lanishlarni JOIN bilan, prefetch_related esa to-many bog'lanishlarni bitta IN so'rov bilan yuklaydi, Prefetch esa aynan nimani va qayerga yuklashni boshqarishga imkon beradi. Async kod lazy loading'ga umuman yo'l qo'ymaydi — va bu ma'lumotni har doim oldindan, aniq yuklash odatini shakllantiradi.

// izohlar

Izohlar yuklanmoqda...

    // keyingi qadam

    Keling, birga nimadir quramiz.

    Telegramda yozish
    aslbek@asldev: ~
    ❯