Python kursi23.9-dars: N+1 muammosi
Python kursi · Bazalar va ORM

23.9-dars: N+1 muammosi

17 daqiqa9/12-dars

23-QISM — BAZALAR VA ORM · 9-dars


1. Kirish va motivatsiya

23.7 da ORM'ning qulayligini ko'rdik: muallif.kurslar — bog'langan ma'lumot atribut kabi. Lekin bu qulaylik yashirin narx bilan keladi. for muallif in mualliflar: print(muallif.kurslar) — ko'rinishda oddiy, lekin ost'da: bir so'rov mualliflar uchun, keyin har muallif uchun alohida so'rov kurslar uchun. 100 muallif → 101 so'rov. Bu — N+1 muammosi.

N+1 muammosi — ORM'ning eng keng tarqalgan unumdorlik tuzog'i: 1 so'rov (asosiy ro'yxat) + N so'rov (har element uchun bog'langan ma'lumot) = N+1 so'rov. Bazaga 101 marta borish — sekin (har so'rov tarmoq/disk). Yechim: bog'langan ma'lumotni oldindan (eager) yuklash — selectinload (alohida IN so'rov), joinedload (bitta JOIN). Bir yoki ikki so'rovda hammasi.

Real vaziyat. Bir sahifa 50 muallif va ularning kitoblarini ko'rsatardi — sekin (2 soniya). Log'da 51 so'rov: 1 muallif ro'yxati + 50 alohida (har muallif kitoblari). N+1 muammosi. selectinload qo'shildi: 2 so'rov (muallif + barcha kitoblar IN bilan). Sahifa 2 soniyadan 50ms'ga tushdi — 40 barobar tez. Bir qatorli o'zgarish (eager yuklash) katta farq qildi.

Bu darsda N+1 muammosini — sababi, aniqlash, yechimlarini o'rganamiz.

Bu darsda:

  • N+1 muammosi nima
  • Dangasa (lazy) yuklash — sabab
  • selectinload (IN so'rov)
  • joinedload (JOIN)
  • Aniqlash (so'rov sanash)
  • Qaysi birini tanlash
  • Django'da N+1
  • Amaliy: N+1'ni tuzatish

ℹ Misollarda SQLAlchemy ORM (SQLite) va so'rov sanagichi bilan sinaladi.


2. Nazariya — chuqur tushuntirish

2.1. N+1 muammosi nima

N+1 — bir amal uchun 1+N so'rov:

python
mualliflar = s.scalars(select(Muallif)).all()   # 1 so'rov
for m in mualliflar:                             # N ta muallif
    print(m.kurslar)                             # har biri +1 so'rov → N so'rov
# jami: 1 + N so'rov

N+1 muammosi — asosiy ro'yxat uchun 1 so'rov, keyin har element (N ta) uchun bog'langan ma'lumot +1 so'rov = N+1 so'rov. 100 muallif → 101 so'rov. Har so'rov narxi bor (tarmoq, disk) — sekin. Bu ORM'ning eng keng tarqalgan unumdorlik tuzog'i.

2.2. Dangasa (lazy) yuklash — sabab

ORM bog'langan ma'lumotni dangasa (lazy) yuklaydi:

python
m = s.get(Muallif, 1)     # faqat muallif so'rovi
m.kurslar                 # ⚠️ SHU YERDA kurslar so'rovi (dangasa)

Dangasa yuklash (lazy loading) — bog'langan ma'lumot (m.kurslar) faqat kerak bo'lganda (birinchi murojaatda) yuklanadi. Bu bitta obyekt uchun yaxshi (keraksiz ma'lumot yuklanmaydi). Lekin siklda (har element) — har biri alohida so'rov → N+1. Sabab: ORM sikl'ni ko'rmaydi, har m.kurslar mustaqil.

2.3. selectinload (IN so'rov)

selectinload — bog'langan ma'lumotni alohida IN so'rov bilan:

python
from sqlalchemy.orm import selectinload

mualliflar = s.scalars(
    select(Muallif).options(selectinload(Muallif.kurslar))
).all()
# 2 so'rov: 1) SELECT muallif  2) SELECT kurs WHERE muallif_id IN (1,2,3,...)

selectinload(Model.bog'lanish) — bog'langan ma'lumotni bitta qo'shimcha so'rov bilan (barcha id'lar uchun IN) oldindan yuklaydi. N+1 → 2 so'rov. .options() — so'rovga yuklash strategiyasi. Ko'p elementli bog'lanish (bir → ko'p) uchun eng yaxshi.

2.4. joinedload (JOIN)

joinedload — bog'langan ma'lumotni JOIN bilan (bitta so'rov):

python
from sqlalchemy.orm import joinedload

mualliflar = s.scalars(
    select(Muallif).options(joinedload(Muallif.kurslar))
).unique().all()
# 1 so'rov: SELECT ... FROM muallif LEFT JOIN kurs ...

joinedload(Model.bog'lanish) — bog'langan ma'lumotni JOIN 23.2-bob bilan bitta so'rovda yuklaydi. N+1 → 1 so'rov. Kolleksiyada .unique() kerak (JOIN qatorlarni takrorlaydi). Bir → bir (yoki ko'p → bir) uchun eng yaxshi; katta kolleksiyada ma'lumot takrori bor.

2.5. Aniqlash (so'rov sanash)

N+1'ni topish:

python
# 1. echo=True — barcha SQL'ni ko'r
engine = create_engine("...", echo=True)

# 2. so'rov sanagich (event)
from sqlalchemy import event
@event.listens_for(engine, "before_cursor_execute")
def sana(*a): counter[0] += 1

# 3. sikl'dan keyin ko'p so'rov → N+1 shubhasi

N+1'ni aniqlash: echo=True (barcha SQL log'da — sikl'da takror SELECT ko'rinadi), so'rov sanagich (event bilan — kutilgandan ko'p bo'lsa), yoki profiler vositasi. Belgi: bir sahifada yuzlab so'rov, sekinlik. Aniqlash — birinchi qadam.

2.6. Qaysi birini tanlash

Holat Tanlov
Bir → ko'p (kolleksiya) selectinload
Ko'p → bir (bitta obyekt) joinedload
Katta kolleksiya selectinload (takrorsiz)
Bir → bir joinedload

selectinload — kolleksiya (bir → ko'p) uchun: 2 so'rov, ma'lumot takrori yo'q. joinedload — bitta obyekt (ko'p → bir) yoki bir → bir uchun: 1 so'rov, lekin kolleksiyada takror. Umumiy qoida: kolleksiya → selectinload, skalyar (bir obyekt) → joinedload. Ikkalasi N+1'ni yechadi.

2.7. Django'da N+1

Django ORM'da ham N+1 bor 21.9-bob:

SQLAlchemy Django
selectinload prefetch_related
joinedload select_related
lazy (default) lazy (default)

Django ORM'da 21.9-bob ham N+1: prefetch_related (kolleksiya — selectinload kabi, alohida so'rov), select_related (ForeignKey — joinedload kabi, JOIN). Ikkalasi ham eager yuklash. Bu — ORM'ning umumiy muammosi (SQLAlchemy, Django, har ORM) va umumiy yechimi.

2.8. N+1 — abstraksiya narxi

N+1 — ORM abstraksiyasining "oqishi" (leaky abstraction, 23.7): ORM SQL'ni yashiradi (m.kurslar — oddiy atribut), lekin ost'da ko'p so'rov. Abstraksiya qulay, lekin nima SQL yaratishini bilmasangiz — sekin. Yechim ORM'ni tark etish emas, balki uni tushunish (eager yuklash). "Qulaylik narxi" — ORM tez rivojlanish beradi, lekin unumdorlik uchun ost qatlamni (SQL) bilish kerak. Bu 23.6 (Core) va xom SQL nega baribir muhim ekanini ko'rsatadi.


3. Tez ma'lumotnoma

python
from sqlalchemy import select
from sqlalchemy.orm import selectinload, joinedload

# ⚠️ N+1 (yomon):
for m in s.scalars(select(Muallif)):
    print(m.kurslar)              # har biri +1 so'rov

# ✅ selectinload (kolleksiya — 2 so'rov):
s.scalars(select(Muallif).options(selectinload(Muallif.kurslar))).all()

# ✅ joinedload (bitta obyekt — 1 so'rov):
s.scalars(select(Kurs).options(joinedload(Kurs.muallif))).all()
# kolleksiya joinedload → .unique() kerak

# Django 21.9-bob:
Muallif.objects.prefetch_related("kurslar")   # = selectinload
Kurs.objects.select_related("muallif")        # = joinedload

N+1 xulosasi

N+1: 1 + N so'rov (dangasa yuklash, siklda)
selectinload: 2 so'rov (IN) · joinedload: 1 so'rov (JOIN)
Aniqlash: echo=True, so'rov sanash

4. Batafsil misollar

Misollarda SQLAlchemy ORM (SQLite) va so'rov sanagichi (event) bilan sinaladi. So'rov soni — N+1'ni ko'rsatadigan asosiy o'lchov.

Misol 1 — N+1 muammosini ko'rsatish

python
"""N+1: dangasa yuklash (m.kurslar) siklda — 1 + N so'rov; so'rov sanagich bilan o'lchash."""

import warnings
warnings.filterwarnings("ignore")

from sqlalchemy import ForeignKey, Integer, String, create_engine, event, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Muallif(Base):
    __tablename__ = "muallif"
    id: Mapped[int] = mapped_column(primary_key=True)
    nom: Mapped[str] = mapped_column(String(50))
    kurslar: Mapped[list["Kurs"]] = relationship(back_populates="muallif")


class Kurs(Base):
    __tablename__ = "kurs"
    id: Mapped[int] = mapped_column(primary_key=True)
    nom: Mapped[str] = mapped_column(String(50))
    muallif_id: Mapped[int] = mapped_column(ForeignKey("muallif.id"))
    muallif: Mapped["Muallif"] = relationship(back_populates="kurslar")


def main() -> None:
    engine = create_engine("sqlite:///:memory:")
    Base.metadata.create_all(engine)
    with Session(engine) as s:
        for i in range(3):
            s.add(Muallif(nom=f"M{i}", kurslar=[Kurs(nom=f"K{i}-{j}") for j in range(2)]))
        s.commit()

    sanoq = [0]

    @event.listens_for(engine, "before_cursor_execute")
    def _(conn, cur, stmt, params, ctx, many):
        if stmt.strip().upper().startswith("SELECT"):
            sanoq[0] += 1

    print("=== 1. Dangasa yuklash (N+1) ===")
    sanoq[0] = 0
    with Session(engine) as s:
        mualliflar = s.scalars(select(Muallif)).all()   # 1 so'rov
        for m in mualliflar:
            _ = list(m.kurslar)                          # har biri +1
    print(f"  3 muallif uchun: {sanoq[0]} SELECT so'rov (1 + 3)")

    print("\n=== 2. Nima bo'ldi ===")
    print("  1 so'rov: mualliflar ro'yxati")
    print("  3 so'rov: har muallif kurslari (dangasa)")
    print(f"  jami: {sanoq[0]} — bu N+1 muammosi")
    print("  ⭐ siklda dangasa yuklash → 1 + N so'rov (sekin)")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text
=== 1. Dangasa yuklash (N+1) ===
  3 muallif uchun: 4 SELECT so'rov (1 + 3)

=== 2. Nima bo'ldi ===
  1 so'rov: mualliflar ro'yxati
  3 so'rov: har muallif kurslari (dangasa)
  jami: 4 — bu N+1 muammosi
  ⭐ siklda dangasa yuklash → 1 + N so'rov (sekin)

Nima ko'rsatdi: 2.1, 2.2, 2.5-bo'limlar.

Misol 2 — selectinload (2 so'rov)

python
"""selectinload: bog'langan ma'lumotni bitta IN so'rov bilan oldindan yuklash — N+1 → 2 so'rov."""

import warnings
warnings.filterwarnings("ignore")

from sqlalchemy import ForeignKey, Integer, String, create_engine, event, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship, selectinload


class Base(DeclarativeBase):
    pass


class Muallif(Base):
    __tablename__ = "muallif"
    id: Mapped[int] = mapped_column(primary_key=True)
    nom: Mapped[str] = mapped_column(String(50))
    kurslar: Mapped[list["Kurs"]] = relationship(back_populates="muallif")


class Kurs(Base):
    __tablename__ = "kurs"
    id: Mapped[int] = mapped_column(primary_key=True)
    nom: Mapped[str] = mapped_column(String(50))
    muallif_id: Mapped[int] = mapped_column(ForeignKey("muallif.id"))
    muallif: Mapped["Muallif"] = relationship(back_populates="kurslar")


def main() -> None:
    engine = create_engine("sqlite:///:memory:")
    Base.metadata.create_all(engine)
    with Session(engine) as s:
        for i in range(3):
            s.add(Muallif(nom=f"M{i}", kurslar=[Kurs(nom=f"K{i}-{j}") for j in range(2)]))
        s.commit()

    sanoq = [0]

    @event.listens_for(engine, "before_cursor_execute")
    def _(conn, cur, stmt, params, ctx, many):
        if stmt.strip().upper().startswith("SELECT"):
            sanoq[0] += 1

    print("=== 1. selectinload bilan ===")
    sanoq[0] = 0
    with Session(engine) as s:
        mualliflar = s.scalars(
            select(Muallif).options(selectinload(Muallif.kurslar))
        ).all()
        jami = sum(len(m.kurslar) for m in mualliflar)
    print(f"  {sanoq[0]} SELECT so'rov (N+1 emas!)")

    print("\n=== 2. Nima bo'ldi ===")
    print("  1 so'rov: mualliflar")
    print("  1 so'rov: barcha kurslar (WHERE muallif_id IN ...)")
    print(f"  jami: {sanoq[0]} so'rov, {jami} kurs yuklandi")

    print("\n=== 3. Solishtirish ===")
    print("  N+1 (dangasa): 4 so'rov")
    print(f"  selectinload: {sanoq[0]} so'rov")
    print("  ⭐ selectinload — kolleksiya uchun 2 so'rov (IN)")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text
=== 1. selectinload bilan ===
  2 SELECT so'rov (N+1 emas!)

=== 2. Nima bo'ldi ===
  1 so'rov: mualliflar
  1 so'rov: barcha kurslar (WHERE muallif_id IN ...)
  jami: 2 so'rov, 6 kurs yuklandi

=== 3. Solishtirish ===
  N+1 (dangasa): 4 so'rov
  selectinload: 2 so'rov
  ⭐ selectinload — kolleksiya uchun 2 so'rov (IN)

Nima ko'rsatdi: 2.3-bo'lim.

Misol 3 — joinedload (1 so'rov)

python
"""joinedload: bog'langan ma'lumotni JOIN bilan bitta so'rovda; kolleksiyada .unique() kerak."""

import warnings
warnings.filterwarnings("ignore")

from sqlalchemy import ForeignKey, Integer, String, create_engine, event, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship, joinedload


class Base(DeclarativeBase):
    pass


class Muallif(Base):
    __tablename__ = "muallif"
    id: Mapped[int] = mapped_column(primary_key=True)
    nom: Mapped[str] = mapped_column(String(50))
    kurslar: Mapped[list["Kurs"]] = relationship(back_populates="muallif")


class Kurs(Base):
    __tablename__ = "kurs"
    id: Mapped[int] = mapped_column(primary_key=True)
    nom: Mapped[str] = mapped_column(String(50))
    muallif_id: Mapped[int] = mapped_column(ForeignKey("muallif.id"))
    muallif: Mapped["Muallif"] = relationship(back_populates="kurslar")


def main() -> None:
    engine = create_engine("sqlite:///:memory:")
    Base.metadata.create_all(engine)
    with Session(engine) as s:
        for i in range(3):
            s.add(Muallif(nom=f"M{i}", kurslar=[Kurs(nom=f"K{i}-{j}") for j in range(2)]))
        s.commit()

    sanoq = [0]

    @event.listens_for(engine, "before_cursor_execute")
    def _(conn, cur, stmt, params, ctx, many):
        if stmt.strip().upper().startswith("SELECT"):
            sanoq[0] += 1

    print("=== 1. joinedload (ko'p → bir: kurs.muallif) ===")
    sanoq[0] = 0
    with Session(engine) as s:
        kurslar = s.scalars(select(Kurs).options(joinedload(Kurs.muallif))).all()
        for k in kurslar:
            _ = k.muallif.nom
    print(f"  6 kurs uchun: {sanoq[0]} SELECT (JOIN — 1 so'rov)")

    print("\n=== 2. joinedload (bir → ko'p: .unique() kerak) ===")
    sanoq[0] = 0
    with Session(engine) as s:
        mualliflar = s.scalars(
            select(Muallif).options(joinedload(Muallif.kurslar))
        ).unique().all()
        jami = sum(len(m.kurslar) for m in mualliflar)
    print(f"  {sanoq[0]} SELECT, {jami} kurs (JOIN qatorlarni .unique() birlashtirdi)")

    print("\n=== 3. Solishtirish ===")
    print("  N+1: 4 so'rov · selectinload: 2 · joinedload: 1")
    print("  ⭐ joinedload — JOIN bilan 1 so'rov (bitta obyekt uchun ideal)")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text
=== 1. joinedload (ko'p → bir: kurs.muallif) ===
  6 kurs uchun: 1 SELECT (JOIN — 1 so'rov)

=== 2. joinedload (bir → ko'p: .unique() kerak) ===
  1 SELECT, 6 kurs (JOIN qatorlarni .unique() birlashtirdi)

=== 3. Solishtirish ===
  N+1: 4 so'rov · selectinload: 2 · joinedload: 1
  ⭐ joinedload — JOIN bilan 1 so'rov (bitta obyekt uchun ideal)

Nima ko'rsatdi: 2.4, 2.6-bo'limlar.

Misol 4 — Amaliy: N+1'ni aniqlab tuzatish

Real jarayon: N+1'ni so'rov sanash bilan aniqlash, keyin eager yuklash bilan tuzatish. Bu — unumdorlik muammosini hal qilishning namunasi.

python
"""to'liq N+1 hal qilish: aniqlash (sanoq), tuzatish (selectinload), tekshirish (so'rov kamaydi)."""

import warnings
warnings.filterwarnings("ignore")

from sqlalchemy import ForeignKey, Integer, String, create_engine, event, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship, selectinload


class Base(DeclarativeBase):
    pass


class Muallif(Base):
    __tablename__ = "muallif"
    id: Mapped[int] = mapped_column(primary_key=True)
    nom: Mapped[str] = mapped_column(String(50))
    kurslar: Mapped[list["Kurs"]] = relationship(back_populates="muallif")


class Kurs(Base):
    __tablename__ = "kurs"
    id: Mapped[int] = mapped_column(primary_key=True)
    nom: Mapped[str] = mapped_column(String(50))
    muallif_id: Mapped[int] = mapped_column(ForeignKey("muallif.id"))
    muallif: Mapped["Muallif"] = relationship(back_populates="kurslar")


def main() -> None:
    engine = create_engine("sqlite:///:memory:")
    Base.metadata.create_all(engine)
    with Session(engine) as s:
        for i in range(5):
            s.add(Muallif(nom=f"M{i}", kurslar=[Kurs(nom=f"K{i}-{j}") for j in range(2)]))
        s.commit()

    sanoq = [0]

    @event.listens_for(engine, "before_cursor_execute")
    def _(conn, cur, stmt, params, ctx, many):
        if stmt.strip().upper().startswith("SELECT"):
            sanoq[0] += 1

    def hisobot(query):
        sanoq[0] = 0
        with Session(engine) as s:
            mualliflar = s.scalars(query).all()
            qatorlar = [f"{m.nom}: {len(m.kurslar)} kurs" for m in mualliflar]
        return sanoq[0], qatorlar

    print("=== 1. Aniqlash (dangasa — N+1) ===")
    n1, _ = hisobot(select(Muallif))
    print(f"  5 muallif → {n1} so'rov (1 + 5 = N+1)")

    print("\n=== 2. Tuzatish (selectinload) ===")
    n2, qatorlar = hisobot(select(Muallif).options(selectinload(Muallif.kurslar)))
    print(f"  5 muallif → {n2} so'rov (2)")

    print("\n=== 3. Natija bir xil (ma'lumot to'g'ri) ===")
    for q in qatorlar:
        print(f"  {q}")

    print("\n=== 4. Yaxshilanish ===")
    print(f"  {n1} so'rov → {n2} so'rov ({n1 - n2} so'rov kamaydi)")
    print("  ⭐ N+1'ni aniqla (sanoq) → eager yukla (selectinload) → tekshir")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text
=== 1. Aniqlash (dangasa — N+1) ===
  5 muallif → 6 so'rov (1 + 5 = N+1)

=== 2. Tuzatish (selectinload) ===
  5 muallif → 2 so'rov (2)

=== 3. Natija bir xil (ma'lumot to'g'ri) ===
  M0: 2 kurs
  M1: 2 kurs
  M2: 2 kurs
  M3: 2 kurs
  M4: 2 kurs

=== 4. Yaxshilanish ===
  6 so'rov → 2 so'rov (4 so'rov kamaydi)
  ⭐ N+1'ni aniqla (sanoq) → eager yukla (selectinload) → tekshir

Nima ko'rsatdi: 2.1–2.8-bo'limlar.


5. To'g'ri va noto'g'ri tushunishlar

Noto'g'ri fikr To'g'risi
"ORM avtomatik optimallashtiradi" N+1 — qo'lda eager yuklash
"m.kurslar bepul" Dangasa — alohida so'rov
"N+1 sezilmaydi" Ko'p ma'lumotda sekin
"joinedload doim yaxshi" Kolleksiyada takror
"selectinload = joinedload" IN vs JOIN (har xil)
"Django'da N+1 yo'q" Bor (prefetch/select_related)
"ORM'ni tark etish kerak" Tushunish (eager)
"N+1 faqat ORM'da" Har lazy yuklashda

6. Keng tarqalgan xatolar va yechimlari

1. Siklda dangasa yuklash

python
for m in mualliflar: m.kurslar          # ⚠️ N+1
select(Muallif).options(selectinload(Muallif.kurslar))  # ✅

2. Kolleksiya joinedload'da .unique() unutish

python
s.scalars(select(M).options(joinedload(M.kurslar))).all()   # ⚠️ xato/takror
s.scalars(...).unique().all()           # ✅

3. Katta kolleksiyada joinedload

python
joinedload(Muallif.kurslar)             # ⚠️ ma'lumot takrori (JOIN)
selectinload(Muallif.kurslar)           # ✅ (IN — takrorsiz)

4. Barcha bog'lanishni eager (keraksiz)

python
selectinload(hamma_boglanish)           # ⚠️ keraksiz ma'lumot
# faqat ishlatiladiganini              # ✅

5. N+1'ni sezmaslik (test'da kam ma'lumot)

python
# 3 ta bilan test → sezilmaydi         # ⚠️
# so'rov sanash yoki ko'p ma'lumot     # ✅

6. Yuklangandan keyin Session yopish

python
with Session() as s: m = s.scalars(...).all()
m[0].kurslar                            # ⚠️ eager bo'lsa ok, aks holda xato
# eager yukla yoki Session ichida        # ✅

7. Session uzoq ochiq qoldirish

python
# uzun ish davomida bir Session         # ⚠️ eski ma'lumot
# qisqa Session (so'rov uchun)          # ✅

7. Integratsiya — bu bilim qayerda kerak bo'ladi

  • 23.7-dars (o'tilgan): SQLAlchemy ORM — relationship (N+1 manbai)
  • 23.2-dars (o'tilgan): JOIN — joinedload asosi
  • 23.3-dars (o'tilgan): Indekslar — so'rov tezligi
  • 21.9-dars (o'tilgan): Django ORM — prefetch/select_related
  • 20.8-dars (o'tilgan): FastAPI baza — N+1 API'da

8. Eng yaxshi amaliyotlar

  1. Siklda bog'langan ma'lumot → eager yukla.

  2. Kolleksiya → selectinload.

  3. Bitta obyekt (ko'p → bir) → joinedload.

  4. So'rov sanash bilan N+1'ni aniqla.

  5. Faqat ishlatiladigan bog'lanishni eager.

  6. Kolleksiya joinedload'da .unique().

  7. Ko'p ma'lumot bilan test (N+1 sezish).

  8. ORM'ni tushun (nima SQL yaratadi).


9. Amaliy topshiriq

Vazifa 1: Bashorat qiling

python
1.  # N+1 muammosi nima?
2.  # sababi nima?
3.  # dangasa yuklash nima?
4.  # selectinload nima?
5.  # joinedload nima?
6.  # selectinload necha so'rov?
7.  # joinedload necha so'rov?
8.  # qaysi kolleksiya uchun?
9.  # .unique() qachon?
10. # Django ekvivalenti?
11. # N+1'ni qanday aniqlash?
12. # ORM'ni tark etish kerakmi?
Javoblar
  1. 1 + N so'rov (bog'langan ma'lumot siklda)
  2. Dangasa yuklash (har element alohida so'rov)
  3. Bog'langan ma'lumot kerak bo'lganda yuklanadi
  4. Eager yuklash (IN so'rov)
  5. Eager yuklash (JOIN)
  6. 2 (1 + IN)
  7. 1 (JOIN)
  8. selectinload (bir → ko'p)
  9. joinedload + kolleksiya
  10. prefetch_related / select_related
  11. So'rov sanash, echo=True
  12. Yo'q (tushunish — eager yuklash)

Vazifa 2: Xatolarni tuzating

python
1.  for m in mualliflar: m.kurslar      # eager

2.  joinedload(M.kurslar) ... .all()    # unique

3.  joinedload(katta_kolleksiya)        # selectinload

4.  # 3 ta bilan test, N+1 ko'rinmaydi  # sanoq

5.  # barcha bog'lanish eager           # faqat kerak
Javoblar
python
1.  select(Muallif).options(selectinload(Muallif.kurslar))

2.  s.scalars(...).unique().all()

3.  selectinload(Muallif.kurslar)

4.  # so'rov sanash yoki ko'p ma'lumot

5.  # faqat ishlatiladiganini eager

Vazifa 3: N+1'ni ko'rsatish

So'rov sanash:

  1. Model, ma'lumot
  2. event sanagich
  3. Siklda dangasa
  4. So'rov sonini ko'r

Vazifa 4: selectinload

Tuzatish:

  1. N+1 so'rov soni
  2. selectinload
  3. So'rov soni (2)
  4. Solishtir

Vazifa 5: joinedload

JOIN:

  1. joinedload (ko'p → bir)
  2. So'rov soni (1)
  3. Kolleksiyada .unique()
  4. selectinload bilan solishtir

Vazifa 6: Django N+1

Taqqoslash:

  1. Django N+1 (21.9)
  2. prefetch_related
  3. select_related
  4. SQLAlchemy bilan solishtir

Vazifa 7: O'ylash

N+1 muammosi — ORM abstraksiyasining "oqishi" (leaky abstraction): m.kurslar oddiy atribut ko'rinadi, lekin ost'da alohida SQL so'rov. Abstraksiya (ORM) SQL'ni yashiradi, lekin butunlay yashirolmaydi — unumdorlik uchun ost qatlamni (SQL) bilish kerak. Nima uchun "abstraksiya oqishi" muqarrar (har abstraksiyada), va yaxshi muhandis qanday qilib abstraksiyadan foydalanadi, lekin uning ost qatlamini ham tushunadi?

Javob

Qisqa javob: N+1 — ORM abstraksiyasining oqishi: ORM SQL'ni yashiradi (qulaylik), lekin m.kurslar ost'da SQL so'rov (yashirin narx). "Abstraksiya oqishi muqarrar" (Joel Spolsky qonuni): har abstraksiya ost qatlamni soddalashtiradi, lekin to'liq yashirolmaydi — muammo (unumdorlik, xato) ost qatlamdan "oqadi". Yaxshi muhandis abstraksiyadan foydalanadi (tez rivojlanish — ORM yozadi), lekin ost qatlamni (SQL) tushunadi (muammoda pastga tusha oladi). Bu "abstraksiyani bilib ishlatish" — ORM qulay, lekin SQL bilish (23.1–23.6) baribir kerak.

1. Abstraksiya oqishi nima

Abstraksiya (ORM) ost qatlamni (SQL) yashiradi (soddalik). Lekin muammo (N+1, sekinlik) ost'dan "oqadi" — abstraksiya to'liq emas.

2. Nega muqarrar

Abstraksiya Oqishi
ORM N+1, sekin so'rov
Tarmoq (TCP) Kechikish, uzilish
Fayl tizimi Disk sekinligi
Xotira (virtual) Sahifa almashish

Har abstraksiya ost qatlamga tayanadi — uning cheklovi (unumdorlik, xato) baribir ko'rinadi.

3. Yaxshi muhandis qanday

  • Abstraksiyadan foydalanadi (ORM — tez, xavfsiz)
  • Lekin ost qatlamni tushunadi (SQL — nima yaratiladi)
  • Muammoda pastga tushadi (echo=True, so'rov sanash)
  • To'g'ri vositani tanlaydi (ORM / Core / xom SQL)

4. N+1 misolda

ORM (abstraksiya) — m.kurslar qulay. Oqishi — N+1 (ost'da ko'p so'rov). Yechim — SQL'ni tushunish (eager yuklash, JOIN — 23.2). ORM'ni tark etish emas.

5. Muhandislik saboqlari

  1. Abstraksiya qulay, lekin oqadi
  2. Ost qatlamni tushun (SQL)
  3. Muammoda pastga tusha ol
  4. To'g'ri vositani tanla

6. Xulosa

  1. N+1 — ORM oqishi
  2. Abstraksiya oqishi muqarrar
  3. Foydalan, lekin tushun (SQL)
  4. Ost qatlam bilimi — muammoda kerak

Nimani mustahkamlaydi: 2.1–2.8-bo'limlar.


Xulosa

Bu darsda N+1 muammosini o'rgandik.

Eng muhim uch fikr:

  1. N+1 — 1 + N so'rov (dangasa yuklash siklda). N+1 muammosi — asosiy ro'yxat uchun 1 so'rov, keyin har element (N ta) uchun bog'langan ma'lumot +1 so'rov = N+1 so'rov. Sabab: ORM bog'langan ma'lumotni (m.kurslar) dangasa (lazy) yuklaydi — faqat kerak bo'lganda, birinchi murojaatda. Bitta obyekt uchun yaxshi, lekin siklda (har element) — har biri alohida so'rov. 100 muallif → 101 so'rov (sekin). Bu ORM'ning eng keng tarqalgan unumdorlik tuzog'i.

  2. selectinload (2 so'rov) va joinedload (1 so'rov). Yechim — bog'langan ma'lumotni oldindan (eager) yuklash. selectinload(Model.bog'lanish) — bitta qo'shimcha IN so'rov (N+1 → 2 so'rov); kolleksiya (bir → ko'p) uchun eng yaxshi (takrorsiz). joinedload(Model.bog'lanish) — JOIN bilan bitta so'rov (N+1 → 1 so'rov); bitta obyekt (ko'p → bir) uchun ideal, kolleksiyada .unique() kerak (JOIN takrori). .options() bilan so'rovga qo'shiladi. Django 21.9-bob: prefetch_related = selectinload, select_related = joinedload.

  3. Aniqlash va abstraksiya narxi. N+1'ni aniqlash: echo=True (SQL log), so'rov sanagich (event), profiler — belgi: ko'p so'rov, sekinlik. N+1 — ORM abstraksiyasining "oqishi" (leaky abstraction, 23.7): ORM SQL'ni yashiradi (qulaylik), lekin nima SQL yaratishini bilmasangiz sekin. Yechim ORM'ni tark etish emas, uni tushunish (eager yuklash). Bu 23.1–23.6 (SQL, Core) nega baribir muhim ekanini ko'rsatadi — abstraksiyadan foydalanib, ost qatlamni (SQL) tushunish.

Keyingi darsda Redis va kesh ni o'rganamiz: tez-tez so'ralgan ma'lumotni xotirada saqlab (kesh), bazaga murojaatni kamaytirish — unumdorlikning yana bir muhim usuli.

Bazalar va ORM · 9-dars yakunlandi

23.9-dars: N+1 muammosi

17 daqvaqt
0belgi
Keyingi dars: 23.10-dars: Redis va kesh
Telegram'da
Darsda xato koʻrdingizmi?

Izohlar · 0