23.9-dars: N+1 muammosi
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:
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'rovN+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:
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:
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):
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:
# 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
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") # = joinedloadN+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 sanash4. 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
"""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:
=== 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)
"""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:
=== 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)
"""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:
=== 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.
"""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:
=== 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) → tekshirNima 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
for m in mualliflar: m.kurslar # ⚠️ N+1
select(Muallif).options(selectinload(Muallif.kurslar)) # ✅2. Kolleksiya joinedload'da .unique() unutish
s.scalars(select(M).options(joinedload(M.kurslar))).all() # ⚠️ xato/takror
s.scalars(...).unique().all() # ✅3. Katta kolleksiyada joinedload
joinedload(Muallif.kurslar) # ⚠️ ma'lumot takrori (JOIN)
selectinload(Muallif.kurslar) # ✅ (IN — takrorsiz)4. Barcha bog'lanishni eager (keraksiz)
selectinload(hamma_boglanish) # ⚠️ keraksiz ma'lumot
# faqat ishlatiladiganini # ✅5. N+1'ni sezmaslik (test'da kam ma'lumot)
# 3 ta bilan test → sezilmaydi # ⚠️
# so'rov sanash yoki ko'p ma'lumot # ✅6. Yuklangandan keyin Session yopish
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
# 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
Siklda bog'langan ma'lumot → eager yukla.
Kolleksiya →
selectinload.Bitta obyekt (ko'p → bir) →
joinedload.So'rov sanash bilan N+1'ni aniqla.
Faqat ishlatiladigan bog'lanishni eager.
Kolleksiya
joinedload'da.unique().Ko'p ma'lumot bilan test (N+1 sezish).
ORM'ni tushun (nima SQL yaratadi).
9. Amaliy topshiriq
Vazifa 1: Bashorat qiling
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 + N so'rov (bog'langan ma'lumot siklda)
- Dangasa yuklash (har element alohida so'rov)
- Bog'langan ma'lumot kerak bo'lganda yuklanadi
- Eager yuklash (IN so'rov)
- Eager yuklash (JOIN)
- 2 (1 + IN)
- 1 (JOIN)
- selectinload (bir → ko'p)
- joinedload + kolleksiya
- prefetch_related / select_related
- So'rov sanash, echo=True
- Yo'q (tushunish — eager yuklash)
Vazifa 2: Xatolarni tuzating
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 kerakJavoblar
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 eagerVazifa 3: N+1'ni ko'rsatish
So'rov sanash:
- Model, ma'lumot
eventsanagich- Siklda dangasa
- So'rov sonini ko'r
Vazifa 4: selectinload
Tuzatish:
- N+1 so'rov soni
selectinload- So'rov soni (2)
- Solishtir
Vazifa 5: joinedload
JOIN:
joinedload(ko'p → bir)- So'rov soni (1)
- Kolleksiyada
.unique() - selectinload bilan solishtir
Vazifa 6: Django N+1
Taqqoslash:
- Django N+1 (21.9)
prefetch_relatedselect_related- 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
- Abstraksiya qulay, lekin oqadi
- Ost qatlamni tushun (SQL)
- Muammoda pastga tusha ol
- To'g'ri vositani tanla
6. Xulosa
- N+1 — ORM oqishi
- Abstraksiya oqishi muqarrar
- Foydalan, lekin tushun (SQL)
- 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:
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.selectinload(2 so'rov) vajoinedload(1 so'rov). Yechim — bog'langan ma'lumotni oldindan (eager) yuklash.selectinload(Model.bog'lanish)— bitta qo'shimchaINso'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.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.
Izohlar · 0
Izoh yozish uchun kiring.