در این فصل چه یاد میگیری#
این فصل هیچ indexی را برای سریع کردن اضافه نمیکند و هیچ تنظیمی را دست نمیزند. فقط متنِ پرسش عوض میشود — و چهار بازنویسی را امتحان میکنیم که چهار سرنوشتِ کاملاً متفاوت دارند.
یکی از آنها جوابِ 0 میدهد در حالی که جوابِ درست ۹٬۱۶۰ است، و خطایی هم نمیگیرد. دو تا هیچ فرقی نمیکنند و همان را هم گزارش میکنیم. و یکی که واقعاً کار میکند، بعد از ساختنِ یک index تبدیل به کندترِ دو تا میشود — یعنی ارزشِ آن بازنویسی به چیزی بند بود که خودش بخشی از پرسش نبود.

آخر این فصل میتوانی:
- بگویی چرا
NOT INروی ستونی کهNULLدارد جوابِ خالی میدهد EXISTSوINرا بسنجی و نتیجه را صادقانه گزارش کنی- زیرپرسشِ همبسته را با یک تجمیع عوض کنی و اثرش را اندازه بگیری
- بگویی چرا
LIMITنقشهٔ اجرا را عوض نمیکند - یک نتیجهٔ منفی را طوری بنویسی که به دردِ نفرِ بعدی بخورد
قبل از شروع#
از ترمِ ۱ فصلِ ۶: NULL و منطقِ سهارزشی. از ترمِ ۳: زیرپرسش، EXISTS، و تجمیع قبل از پیوند. از فصلِ ۳ همین ترم: CREATE INDEX.
قاعدهٔ اندازهگیریِ این فصل: هر دو نسخهٔ هر بازنویسی زیرِ دقیقاً همان indexها اندازه گرفته میشوند. اگر وسطِ مقایسه index بسازی، دیگر نمیدانی کدامیک از دو تغییر عدد را عوض کرد.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
events |
۴۰۰٬۰۰۰ | چند ثانیه |
books |
۴۰٬۰۰۰ | کمتر از یک ثانیه |
members |
۱۲٬۰۰۰ | آنی |
branches |
۸ | آنی |
۱. بازنویسیای که جوابش غلط است#
پرسش: چند عضو هیچ کتابی اهدا نکردهاند؟ سه راهِ کاملاً معقول برای نوشتنش هست. اول یک index میسازیم تا هر سه زیرِ شرایطِ یکسان سنجیده شوند:
con.execute("CREATE INDEX idx_books_donor ON books(donated_by)")
con.commit()
NOT_IN = """
SELECT COUNT(*) AS n FROM members
WHERE id NOT IN (SELECT donated_by FROM books)
"""
NOT_EXISTS = """
SELECT COUNT(*) AS n FROM members AS m
WHERE NOT EXISTS (SELECT 1 FROM books AS b WHERE b.donated_by = m.id)
"""
NOT_IN_FIXED = """
SELECT COUNT(*) AS n FROM members
WHERE id NOT IN (SELECT donated_by FROM books WHERE donated_by IS NOT NULL)
"""
print("با NOT IN :", int(q(NOT_IN).iloc[0, 0]))
print("با NOT EXISTS :", int(q(NOT_EXISTS).iloc[0, 0]))
print("با NOT IN اصلاحشده :", int(q(NOT_IN_FIXED).iloc[0, 0]))
print()
print("کل اعضا :", int(q("SELECT COUNT(*) AS n FROM members").iloc[0, 0]))
print("اعضایی که اهدا کردهاند :",
int(q("SELECT COUNT(DISTINCT donated_by) AS n FROM books").iloc[0, 0]))
print("کتابهایی که اهداکننده ندارند:",
int(q("SELECT COUNT(*) AS n FROM books WHERE donated_by IS NULL").iloc[0, 0]))
با NOT IN : 0
با NOT EXISTS : 9160
با NOT IN اصلاحشده : 9160
کل اعضا : 12000
اعضایی که اهدا کردهاند : 2840
کتابهایی که اهداکننده ندارند: 36749
۱۲٬۰۰۰ منهای ۲٬۸۴۰ میشود ۹٬۱۶۰. شمارشِ دوم میگوید جوابِ درست همین است، و NOT IN صفر داد.
🔧 اگر کار نکرد: این خطا هیچ پیامی ندارد، پس رفعش با خواندنِ خطا شروع نمیشود — با فهمیدنِ
NULLشروع میشود. ستونِdonated_byدر ۳۶٬۷۴۹ سطرNULLاست، پس فهرستِ داخلِNOT INیکNULLدارد. وid NOT IN (…, NULL)یعنی «idبا هیچکدام از اینها برابر نیست» — که برایNULLجوابش نامعلوم است، نه «درست». هیچ سطری شرط را رد نمیکند و جواب صفر میشود. رفعش دو راه دارد و هر دو بالا هست: یاWHERE donated_by IS NOT NULLرا به زیرپرسش اضافه کن، یا اصلاً ازNOT EXISTSاستفاده کن که این تله را ندارد.
حالا زمانهایش:
print("NOT IN :", timed(NOT_IN), "میلیثانیه (جواب غلط)")
print("NOT EXISTS :", timed(NOT_EXISTS), "میلیثانیه")
print("NOT IN اصلاحشده :", timed(NOT_IN_FIXED), "میلیثانیه")
NOT IN : 2.27 میلیثانیه (جواب غلط)
NOT EXISTS : 2.43 میلیثانیه
NOT IN اصلاحشده : 2.22 میلیثانیه
و بدترین بخشِ ماجرا همین است: نسخهٔ غلط سریعتر هم نبود.
هیچ معاملهای در کار نبود، هیچ سرعتی خریده نشد. فقط یک جواب از دست رفت — و اگر آن عدد در گزارشی میرفت، کسی نتیجه میگرفت که همهٔ دوازده هزار عضو دستِکم یک کتاب اهدا کردهاند.
✅ چک کن: هر بار که یک عددِ شمارشی صفر یا برابرِ کل درآمد، قبل از هر نتیجهگیری با یک شمارشِ دوم بسنجش. اینجا شمارشِ دوم دو خط بود و کلِ خطا را گرفت.
۲. EXISTS در برابرِ IN#
حالا همان پرسش را برعکس میپرسیم — چند عضو دستِکم یک کتاب اهدا کردهاند؟ — که در آن IN تلهٔ NULL را ندارد:
WITH_IN = """
SELECT COUNT(*) AS n FROM members
WHERE id IN (SELECT donated_by FROM books)
"""
WITH_EXISTS = """
SELECT COUNT(*) AS n FROM members AS m
WHERE EXISTS (SELECT 1 FROM books AS b WHERE b.donated_by = m.id)
"""
print("با IN :", int(q(WITH_IN).iloc[0, 0]))
print("با EXISTS:", int(q(WITH_EXISTS).iloc[0, 0]))
با IN : 2840
با EXISTS: 2840
show_plan(WITH_IN)
print()
show_plan(WITH_EXISTS)
print()
print("IN :", [timed(WITH_IN) for _ in range(3)])
print("EXISTS:", [timed(WITH_EXISTS) for _ in range(3)])
id parent detail
3 0 SEARCH members USING INTEGER PRIMARY KEY (rowid=?)
7 0 LIST SUBQUERY 1
9 7 SCAN books USING COVERING INDEX idx_books_donor
id parent detail
3 0 SCAN m USING COVERING INDEX sqlite_autoindex_members_1
6 0 CORRELATED SCALAR SUBQUERY 1
10 6 SEARCH b USING COVERING INDEX idx_books_donor (donated_by=?)
IN : [2.65, 2.7, 2.58]
EXISTS: [2.68, 2.47, 2.41]
دو نقشهٔ کاملاً متفاوت، همان جواب، و دو مشت عدد که در نوسانِ هم میافتند.
نقشهها واقعاً فرق دارند: IN اول فهرستی از اهداکنندهها میسازد و بعد در members دنبالشان میگردد؛ EXISTS برعکس، members را میپیماید و برای هر عضو یک جستوجوی کوچک میکند. ولی هر دو در نهایت از همان index استفاده میکنند، و همین تفاوت را کوچک نگه میدارد.
نتیجهای که باید نوشت: روی این داده و با این index، بازنویسیِ IN به EXISTS هیچ چیزی نخرید. آن جملهای که همهجا میشنوی — «EXISTS همیشه از IN بهتر است» — با این شش عدد رد میشود؛ نه اینکه غلط باشد، بلکه به یک قاعدهٔ همیشگی تبدیل نمیشود.
۳. فیلترِ زودهنگام: بازنویسیای که هیچ فرقی نکرد#
توصیهٔ رایج این است که «فیلتر را هرچه زودتر اعمال کن تا سطرهای کمتری وارد پیوند شوند». امتحانش میکنیم:
MONTH = ("2022-03-01", "2022-04-01")
PLAIN = """
SELECT b.category, COUNT(*) AS n
FROM events AS e
JOIN books AS b ON b.id = e.book_id
WHERE e.happened_at >= ? AND e.happened_at < ?
GROUP BY b.category
ORDER BY b.category
"""
NESTED = """
SELECT b.category, COUNT(*) AS n
FROM (SELECT book_id FROM events WHERE happened_at >= ? AND happened_at < ?) AS x
JOIN books AS b ON b.id = x.book_id
GROUP BY b.category
ORDER BY b.category
"""
plain_rows = q(PLAIN, MONTH)
print(plain_rows)
print("جمع هشت دسته :", int(plain_rows["n"].sum()))
print("دو نسخه دقیقاً یک نتیجه دادند:", plain_rows.equals(q(NESTED, MONTH)))
category n
0 تاریخ 1392
1 رمان 1419
2 زندگینامه 1456
3 شعر 1434
4 علم 1421
5 فلسفه 1380
6 هنر 1429
7 کودک 1490
جمع هشت دسته : 11421
دو نسخه دقیقاً یک نتیجه دادند: True
show_plan(PLAIN, MONTH)
print()
show_plan(NESTED, MONTH)
print()
print("نسخهٔ ساده :", [timed(PLAIN, MONTH, repeat=3) for _ in range(3)])
print("نسخهٔ تودرتو:", [timed(NESTED, MONTH, repeat=3) for _ in range(3)])
id parent detail
8 0 SCAN e
14 0 SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
17 0 USE TEMP B-TREE FOR GROUP BY
id parent detail
8 0 SCAN events
14 0 SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
17 0 USE TEMP B-TREE FOR GROUP BY
نسخهٔ ساده : [63.88, 64.43, 55.74]
نسخهٔ تودرتو: [56.0, 56.07, 58.68]
دو نقشهٔ یکسان — تنها تفاوتشان نامِ مستعارِ جدول است — و دو مشت عدد که در نوسانِ هم میافتند.
دلیلش این است که SQLite خودش این کار را از قبل کرده بود. بهینهساز فیلتر را به عمقِ پرسش میبَرد، پس نسخهٔ «ساده» و نسخهٔ «تودرتو» به یک برنامهٔ اجرایی تبدیل میشوند.
این نتیجهٔ منفی است و باید نوشته شود، چون نفرِ بعدی وگرنه همین یک ساعت را دوباره خرج میکند. و از آن مهمتر: نسخهٔ تودرتو خواناتر نیست و یک لایهٔ پرانتز اضافه کرده — یعنی معاملهای انجام شده که هیچ چیزی در ازایش نگرفتهایم.
جمعِ هشت دسته هم ۱۱٬۴۲۱ شد، همان عددی که فصلهای ۱ و ۲ برای همان ماه دادند — پس این بازنویسی حتی یک سطر هم گم نکرده.
۴. زیرپرسشِ همبسته: بازنویسیای که کار کرد، تا وقتی که نکرد#
حالا پرسشی که واقعاً بازنویسی میخواهد: هر شعبه چند رویداد دارد؟ نسخهٔ اول برای هر شعبه یک زیرپرسشِ جدا اجرا میکند؛ نسخهٔ دوم همه را در یک عبور میشمارد.
PER_BRANCH_SUB = """
SELECT br.name,
(SELECT COUNT(*)
FROM events AS e
JOIN books AS b ON b.id = e.book_id
WHERE b.branch_id = br.id) AS n
FROM branches AS br
ORDER BY br.name
"""
PER_BRANCH_GROUP = """
SELECT br.name, COUNT(*) AS n
FROM events AS e
JOIN books AS b ON b.id = e.book_id
JOIN branches AS br ON br.id = b.branch_id
GROUP BY br.id, br.name
ORDER BY br.name
"""
sub_rows = q(PER_BRANCH_SUB)
print(sub_rows)
print("جمع هشت شعبه :", int(sub_rows["n"].sum()))
print("دو نسخه دقیقاً یک نتیجه دادند:", sub_rows.equals(q(PER_BRANCH_GROUP)))
name n
0 آفتاب 48767
1 بهار 49635
2 دانش 51048
3 ساحل 49651
4 مرکزی 51026
5 نسیم 49983
6 پویا 49829
7 کوشا 50061
جمع هشت شعبه : 400000
دو نسخه دقیقاً یک نتیجه دادند: True
print("زیرپرسشِ همبسته:", timed(PER_BRANCH_SUB, repeat=3), "میلیثانیه")
print("تجمیعِ یکعبوری:", timed(PER_BRANCH_GROUP, repeat=3), "میلیثانیه")
print()
show_plan(PER_BRANCH_SUB)
زیرپرسشِ همبسته: 1649.19 میلیثانیه
تجمیعِ یکعبوری: 899.66 میلیثانیه
id parent detail
3 0 SCAN br USING COVERING INDEX sqlite_autoindex_branches_1
7 0 CORRELATED SCALAR SUBQUERY 1
13 7 SCAN b
20 7 BLOOM FILTER ON e (book_id=?)
29 7 SEARCH e USING AUTOMATIC COVERING INDEX (book_id=?)
بازنویسی جواب داد: نزدیک به دو برابر. و جمعِ هشت شعبه دقیقاً ۴۰۰٬۰۰۰ شد، یعنی هیچ رویدادی نه گم شد و نه دو بار شمرده شد.
ولی نقشه یک چیزِ غافلگیرکننده دارد: SEARCH e USING AUTOMATIC COVERING INDEX. آن کلمهٔ AUTOMATIC یعنی SQLite دید که قرار است یک زیرپرسش هشت بار اجرا شود، پس خودش سرِ اجرا یک index موقت ساخت، استفاده کرد، و دورش ریخت. پس نسخهٔ «بد» آنقدرها هم بد نبود — چون پایگاه داده نجاتش داد.
حالا همان index را دائمی میسازیم و هر دو نسخه را دوباره اندازه میگیریم:
con.execute("CREATE INDEX idx_events_book ON events(book_id)")
con.commit()
print("همان نتیجه بعد از index:", sub_rows.equals(q(PER_BRANCH_SUB)))
همان نتیجه بعد از index: True
print("زیرپرسشِ همبسته:", timed(PER_BRANCH_SUB, repeat=3), "میلیثانیه")
print("تجمیعِ یکعبوری:", timed(PER_BRANCH_GROUP, repeat=3), "میلیثانیه")
print()
show_plan(PER_BRANCH_SUB)
زیرپرسشِ همبسته: 74.43 میلیثانیه
تجمیعِ یکعبوری: 196.37 میلیثانیه
id parent detail
3 0 SCAN br USING COVERING INDEX sqlite_autoindex_branches_1
7 0 CORRELATED SCALAR SUBQUERY 1
13 7 SCAN b
18 7 SEARCH e USING COVERING INDEX idx_events_book (book_id=?)
ترتیب وارونه شد. نسخهای که بدون index کندتر بود، حالا سریعترِ دو تاست.
و منطقش کاملاً روشن است: زیرپرسشِ همبسته با این index برای هر شعبه books را میپیماید و فقط برای کتابهای همان شعبه در index جستوجو میکند: روی هم حدودِ ۴۰٬۰۰۰ جستوجو، و هر چه لازم دارد داخلِ خودِ index است. تجمیعِ یکعبوری همچنان مجبور است برای هر ۴۰۰٬۰۰۰ سطر به books و branches سر بزند و گروهبندی کند.
درسِ این بخش از خودِ عددها بزرگتر است: «کدام شکلِ پرسش بهتر است» سؤالِ بیجوابی است. جواب به indexها، به اندازهٔ داده، و به توزیعِ مقدارها بستگی دارد — و هر سهشان فردا عوض میشوند. پس بازنویسی بدونِ اندازهگیری، خرافه است.
۵. LIMIT روی پرسشِ مرتبشده#
TOP_BOOKS = """
SELECT book_id, COUNT(*) AS n
FROM events
GROUP BY book_id
ORDER BY n DESC, book_id
LIMIT 5
"""
ALL_BOOKS = """
SELECT book_id, COUNT(*) AS n
FROM events
GROUP BY book_id
ORDER BY n DESC, book_id
"""
print(q(TOP_BOOKS))
print("گروههای ساختهشده بدون LIMIT:", len(q(ALL_BOOKS)))
print("کل کتابها :", int(q("SELECT COUNT(*) AS n FROM books").iloc[0, 0]))
book_id n
0 333 26
1 20996 24
2 22200 24
3 25736 24
4 28673 24
گروههای ساختهشده بدون LIMIT: 39998
کل کتابها : 40000
show_plan(TOP_BOOKS)
print()
show_plan(ALL_BOOKS)
print()
print("با LIMIT 5:", [timed(TOP_BOOKS, repeat=3) for _ in range(3)])
print("بدون LIMIT:", [timed(ALL_BOOKS, repeat=3) for _ in range(3)])
id parent detail
8 0 SCAN events USING COVERING INDEX idx_events_book
42 0 USE TEMP B-TREE FOR ORDER BY
id parent detail
7 0 SCAN events USING COVERING INDEX idx_events_book
36 0 USE TEMP B-TREE FOR ORDER BY
با LIMIT 5: [30.44, 26.83, 27.2]
بدون LIMIT: [54.51, 50.66, 49.61]
نقشهٔ هر دو یکی است. LIMIT هیچ مرحلهای را از نقشه حذف نکرد.
هر ۴۰۰٬۰۰۰ سطر خوانده شد و هر ۳۹٬۹۹۸ گروه ساخته شد. چیزی که LIMIT صرفهجویی کرد دو کارِ آخر بود: موتور فقط پنج گروهِ برتر را نگه داشت و بقیه را مرتب نکرد، و بهجای ۳۹٬۹۹۸ سطر فقط ۵ سطر به پایتون رفت — همان هزینهٔ fetchall که فصلِ ۱ اندازهاش گرفت.
پس LIMIT یک ابزارِ سرعت نیست؛ یک ابزارِ کم کردنِ حجمِ خروجی است. اگر پرسشی کند است و تو LIMIT اضافه میکنی تا سریع شود، در بهترین حالت نصفِ راه را رفتهای.
و یک نکتهٔ کوچکِ کنترلی: ۳۹٬۹۹۸ گروه از ۴۰٬۰۰۰ کتاب — یعنی دقیقاً دو کتاب هیچ رویدادی ندارند و در این جدول اصلاً دیده نمیشوند. یک GROUP BY فقط دربارهٔ چیزهایی حرف میزند که دستِکم یک سطر دارند.
۶. چرا نتیجهٔ منفی را مینویسیم#
از چهار بازنویسیِ این فصل، دو تا هیچ سودی نداشتند و یکی جواب را خراب کرد. اگر فقط آن یکی را که کار کرد گزارش کنی، سه چهارمِ چیزی را که یاد گرفتی دور ریختهای.
سه دلیل، بهترتیبِ اهمیت:
- نفرِ بعدی همان یک ساعت را دوباره خرج میکند. «فیلترِ زودهنگام را امتحان کردم، دو نقشه یکی درآمد» یک جملهٔ کوتاه است که ساعتها صرفهجویی میکند.
- بازنویسیِ بیاثر رایگان نیست. پرسشِ تودرتوی بخشِ ۳ یک لایهٔ اضافه دارد که هر کسی باید بخواندش، و هیچ چیزی در ازایش نمیدهد.
- نتیجهٔ منفی، شرطِ اعتماد به نتیجهٔ مثبت است. گزارشی که فقط موفقیت دارد، خوانندهاش نمیداند چند چیز امتحان شده و چند تا کار کرده.
📏 اندازه بگیر: یک سطر یعنی چه؟ در بخشِ ۳ یک دسته، در بخشِ ۴ یک شعبه، در بخشِ ۵ یک کتاب. با کدام شمارشِ دوم سنجیدی؟ با چهارتا: ۱۲٬۰۰۰ منهای ۲٬۸۴۰ که ۹٬۱۶۰ شد و
NOT INرا رد کرد؛ جمعِ هشت دستهٔ بخشِ ۳ که همان ۱۱٬۴۲۱ فصلهای قبل شد؛ جمعِ هشت شعبهٔ بخشِ ۴ که دقیقاً ۴۰۰٬۰۰۰ شد؛ وequalsکه در هر بازنویسی خودش تأیید کرد دو نسخه یک نتیجه دادند. چه کسی از قلم افتاد؟ دو کتابی که هیچ رویدادی ندارند و در جدولِ بخشِ ۵ نیستند — و در بخشِ ۱، اگرNOT INرا باور کرده بودیم، ۹٬۱۶۰ عضوی که هیچوقت چیزی اهدا نکردهاند از گزارش ناپدید میشدند.
🤖 از دستیارت بپرس: «چرا
NOT INبا یک زیرپرسش کهNULLدارد جوابِ خالی میدهد؟» بعد این را بپرس: «آیاEXISTSهمیشه ازINسریعتر است؟» — جوابِ درست میگوید نه، و بهindexها و اندازهٔ دو طرف اشاره میکند. اگر جواب یک قاعدهٔ قطعی داد، با دو مشت عددِ بخشِ ۲ و با وارونه شدنِ بخشِ ۴ ردش کن.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| query rewriting | کوئری ریرایتینگ | بازنویسیِ متنِ پرسش بدونِ عوض کردنِ جوابش |
| optimizer | آپتیمایزر | بخشی از پایگاه داده که تصمیم میگیرد پرسش چطور اجرا شود |
| predicate pushdown | پردیکیت پوشداون | بردنِ فیلتر به عمقِ پرسش، تا سطرهای کمتری بالا بیایند |
| automatic index | اتومتیک ایندکس | index موقتی که SQLite سرِ اجرا میسازد و بعد دور میریزد |
| negative result | نگتیو ریزالت | نتیجهٔ منفی؛ آزمایشی که تفاوتی نشان نداد و باید گزارش شود |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
شش فصل ابزار جمع کردیم و همهشان یک فرض داشتند: sqlite3 ابزارِ درستِ این کار است.
فصلِ بعد همان فرض را میسنجد. با عدد نشان میدهیم که یک نویسنده یعنی چه، که فهرستِ پارامترها سقف دارد و سقفش کجاست — با پیامِ خطای واقعیاش — و اینکه با ده برابر شدنِ داده چه چیزی خطی رشد میکند و چه چیزی نمیکند.
و مهمتر از همه: کدام یک از چیزهایی که در این پنج ترم یاد گرفتی به پایگاه دادهٔ بعدیات منتقل میشود، و کدام نمیشود.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.