در این فصل چه یاد میگیری#
فصلِ قبل یک پرسش را زمان گرفت و جواب داد «سیوچند میلیثانیه». این فصل همان پرسش را میپرسد و جوابِ کاملاً دیگری میگیرد: SCAN events — یعنی پایگاه داده هر ۴۰۰٬۰۰۰ سطر را خواند تا ۱۱٬۴۲۱ سطر را پیدا کند.
بعد یک آزمایشِ کوچک: همان یک کتاب را دو جور صدا میزنیم و همان عنوان برمیگردد، ولی یکی از دو پرسش SEARCH میگیرد و دیگری SCAN — و اختلافِ زمانشان دهها برابر است.

آخر این فصل میتوانی:
- نقشهٔ اجرای هر پرسشی را چاپ کنی، بدونِ اینکه پرسش اجرا شود
SCANرا ازSEARCHتشخیص بدهی و بگویی هر کدام یعنی چهUSING INDEXوUSING COVERING INDEXرا از هم جدا کنی- بفهمی
USE TEMP B-TREEکجا از مرتبسازی و گروهبندی میآید - نقشه را بهشکلِ درخت بخوانی، نه فهرست
قبل از شروع#
از فصلِ ۱: timed، میانهٔ چند اجرا، و پهنای نوسان. از ترمِ ۴: PRIMARY KEY و UNIQUE.
دانهبندی: یک سطر از events یک رویداد است. یک سطر از نقشهٔ اجرا یک مرحله از کارِ پایگاه داده است — و این فصل دربارهٔ خواندنِ همان جدولِ دوم است.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
events |
۴۰۰٬۰۰۰ | چند ثانیه |
books |
۴۰٬۰۰۰ | کمتر از یک ثانیه |
members |
۱۲٬۰۰۰ | آنی |
branches |
۸ | آنی |
۱. سه کلمه که پرسش را اجرا نمیکنند#
EXPLAIN QUERY PLAN را جلوی هر SELECTی بگذاری، SQLite بهجای اجرای پرسش، نقشهٔ کاری را که قرار بود بکند برمیگرداند. هیچ سطری خوانده نمیشود و هیچ عددی حساب نمیشود.
تابعِ plan در سلولِ راهاندازی دقیقاً همین پیشوند را به متنِ پرسش میچسباند، و show_plan نتیجهاش را کامل چاپ میکند. show_plan فقط در این ترم وجود دارد — چون فقط اینجا ستونِ detail آنقدر بلند میشود که pandas میبُردش؛ ترمهای دیگر همان plan(...) را مستقیم چاپ میکنند.
و اینجا یک استثنای مهم است که باید صریح گفته شود: ما داریم متن به یک پرسش میچسبانیم، در حالی که کلِ این دوره میگوید هرگز چیزی به متنِ پرسش نچسبان. تفاوت این است که آنچه میچسبانیم مقدار نیست — یک کلیدواژهٔ ثابتِ SQL است که ما در کد نوشتهایم و از هیچ ورودیای نمیآید. مقدارها همچنان با ? میروند، و در پرسشِ زیر دقیقاً همینطور است.
SQL_MONTH = """
SELECT COUNT(*) AS n
FROM events
WHERE happened_at >= ? AND happened_at < ?
"""
MONTH = ("2022-03-01", "2022-04-01")
print("پاسخ:", int(q(SQL_MONTH, MONTH).iloc[0, 0]), "رویداد از",
int(q("SELECT COUNT(*) AS n FROM events").iloc[0, 0]))
پاسخ: 11421 رویداد از 400000
show_plan(SQL_MONTH, MONTH)
id parent detail
3 0 SCAN events
یک کلمه، و کلِ معما حل شد: SCAN events.
SCAN یعنی «از سطرِ اول تا سطرِ آخرِ این جدول را میخوانم». پایگاه داده هیچ راهِ میانبری به تاریخ ندارد، پس هر ۴۰۰٬۰۰۰ سطر را برداشت، شرط را روی هرکدام سنجید، و ۱۱٬۴۲۱ تا را نگه داشت. آن سیوچند میلیثانیهٔ فصلِ قبل، هزینهٔ خواندنِ ۳۸۸٬۵۷۹ سطری بود که به کارمان نیامدند.
۲. SCAN در برابرِ SEARCH#
حالا همان کتاب را دو جور صدا میزنیم. هر دو پرسش دقیقاً یک عنوان برمیگردانند:
BY_KEY = "SELECT title FROM books WHERE id = ?"
BY_MATH = "SELECT title FROM books WHERE id + 0 = ?"
print("با کلید :", q(BY_KEY, (17,)).iloc[0, 0])
print("با محاسبه:", q(BY_MATH, (17,)).iloc[0, 0])
با کلید : سنگ پشت ستاره
با محاسبه: سنگ پشت ستاره
show_plan(BY_KEY, (17,))
print()
show_plan(BY_MATH, (17,))
print()
print("با کلید :", timed(BY_KEY, (17,)), "میلیثانیه")
print("با محاسبه:", timed(BY_MATH, (17,)), "میلیثانیه")
id parent detail
2 0 SEARCH books USING INTEGER PRIMARY KEY (rowid=?)
id parent detail
2 0 SCAN books
با کلید : 0.06 میلیثانیه
با محاسبه: 2.83 میلیثانیه
همان جواب، همان جدول، همان یک سطر — و دهها برابر اختلاف.
SEARCHیعنی پایگاه داده مستقیم رفت سرِ جای درست.INTEGER PRIMARY KEYدرSQLiteهمان شناسهٔ داخلیِ سطر است، پس رسیدن به سطرِ شمارهٔ ۱۷ یعنی چند پرش در یک درخت.SCANیعنی همان کارِ بخشِ ۱: هر ۴۰٬۰۰۰ سطر خوانده شد.
و دلیلِ SCAN شدنِ پرسشِ دوم را همینجا نگو، فقط ثبتش کن: بهمحضِ اینکه ستونِ فیلتر داخلِ یک محاسبه برود، پایگاه داده دیگر نمیتواند از ترتیبِ ذخیرهشده استفاده کند. فصلِ ۴ کلِ این خانواده از تلهها را باز میکند.
✅ چک کن: دو پرسشِ بالا باید دقیقاً یک عنوان بدهند. اگر ندادند، شمارهٔ کتاب را در یکی از دو پرسش عوض کردهای — و آنوقت داری دو کارِ متفاوت را با هم میسنجی، نه دو راهِ رسیدن به یک کار.
۳. USING INDEX: چیزی که از قبل آنجاست#
جدولِ members هیچ indexی که ما ساخته باشیم ندارد. ولی ستونِ national_id قیدِ UNIQUE دارد، و SQLite برای اجرای هر قیدِ UNIQUE خودش یک index میسازد:
print(q("PRAGMA index_list(members)")[["name", "unique", "origin"]])
print()
print(q("PRAGMA index_info(sqlite_autoindex_members_1)"))
name unique origin
0 sqlite_autoindex_members_1 1 u
seqno cid name
0 0 3 national_id
ستونِ origin مقدارِ u دارد، یعنی «از یک قیدِ UNIQUE آمده» — نه از دستِ ما. حالا ببینیم در نقشه چطور دیده میشود:
BY_NATIONAL = "SELECT name, city FROM members WHERE national_id = ?"
print(q(BY_NATIONAL, ("1000079190",)))
name city
0 شیرین توکلی قم
show_plan(BY_NATIONAL, ("1000079190",))
print()
print("زمان:", timed(BY_NATIONAL, ("1000079190",)), "میلیثانیه")
id parent detail
3 0 SEARCH members USING INDEX sqlite_autoindex_members_1 (national_id=?)
زمان: 0.06 میلیثانیه
SEARCH ... USING INDEX <نام> یعنی پایگاه داده از یک ساختارِ جانبی استفاده کرد تا مستقیم به سطر برسد. فصلِ بعد نشان میدهد چطور خودت چنین چیزی بسازی؛ اینجا فقط داریم یاد میگیریم که در نقشه چه شکلی است.
۴. USING COVERING INDEX: وقتی اصلاً به جدول سر نمیزند#
یک کلمهٔ اضافه در نقشه هست که ارزشِ توجه دارد. این دو پرسش را با هم بسنج:
NEED_ROW = "SELECT name, city FROM members WHERE national_id < ?"
NEED_NOTHING = "SELECT COUNT(*) AS n FROM members WHERE national_id < ?"
HALF = ("1050000000",)
print("سطرهایی که در بازهاند:", int(q(NEED_NOTHING, HALF).iloc[0, 0]))
print("کل اعضا :", int(q("SELECT COUNT(*) AS n FROM members").iloc[0, 0]))
سطرهایی که در بازهاند: 6313
کل اعضا : 12000
show_plan(NEED_NOTHING, HALF)
id parent detail
3 0 SEARCH members USING COVERING INDEX sqlite_autoindex_members_1 (national_id<?)
COVERING یعنی هر چیزی که این پرسش لازم داشت، داخلِ خودِ index بود. پرسش فقط میخواست بشمارد، و آن index تمامِ مقدارهای national_id را دارد — پس پایگاه داده اصلاً به جدولِ members سر نزد.
پرسشِ NEED_ROW ولی name و city میخواهد که در index نیستند، پس مجبور است برای هر سطرِ پیداشده به جدول هم مراجعه کند. همین یک کلمه در نقشه، تفاوتِ یک مراجعه با دو مراجعه به ازای هر سطر است، و فصلِ بعد اندازهاش میگیرد.
۵. USE TEMP B-TREE: کاری که در نقشه پیدا میشود ولی در پرسش نوشته نشده#
مرتبسازی و گروهبندی هزینه دارند، و نقشه صریح میگویدشان:
BY_CATEGORY = """
SELECT category, COUNT(*) AS n
FROM books
GROUP BY category
ORDER BY category
"""
first_five = "SELECT id, happened_at FROM events ORDER BY happened_at LIMIT 5"
rows = q(BY_CATEGORY)
print(rows)
print("جمع هشت دسته:", int(rows["n"].sum()))
print()
print(q(first_five))
category n
0 تاریخ 4949
1 رمان 4943
2 زندگینامه 5004
3 شعر 5127
4 علم 4967
5 فلسفه 4879
6 هنر 5006
7 کودک 5125
جمع هشت دسته: 40000
id happened_at
0 1 2022-01-01 00:02:34
1 2 2022-01-01 00:04:37
2 3 2022-01-01 00:04:44
3 4 2022-01-01 00:04:56
4 5 2022-01-01 00:06:25
show_plan(BY_CATEGORY)
print()
show_plan(first_five)
print()
print("گروهبندی :", timed(BY_CATEGORY), "میلیثانیه")
print("پنج سطر اول:", timed(first_five), "میلیثانیه")
id parent detail
7 0 SCAN books
9 0 USE TEMP B-TREE FOR GROUP BY
id parent detail
4 0 SCAN events
16 0 USE TEMP B-TREE FOR ORDER BY
گروهبندی : 13.23 میلیثانیه
پنج سطر اول: 39.82 میلیثانیه
پرسشِ دوم فقط پنج سطر میخواهد و گرانتر از پرسشی است که هشت گروه میسازد.
USE TEMP B-TREE FOR ORDER BY میگوید چرا: برای اینکه بدانی کدام پنج سطر اولیناند، باید هر ۴۰۰٬۰۰۰ سطر را دیده باشی. LIMIT 5 این خواندن را کم نمیکند؛ فقط به موتور اجازه میدهد بهجای مرتب کردنِ همه، پنجتای برتر را نگه دارد. کارِ خواندن همچنان انجام شده است.
این جملهٔ کوتاه را نگه دار، چون در فصلِ ۶ دوباره سرِ راهمان سبز میشود: LIMIT یک ابزارِ سرعت نیست.
۶. نقشه یک درخت است، نه فهرست#
دو ستونِ اولِ نقشه تا حالا بیکار بودهاند. id شمارهٔ هر مرحله است و parent میگوید هر مرحله زیرمجموعهٔ کدام مرحله است. مرحلهای که parentش صفر نیست، داخلِ مرحلهٔ دیگری اجرا میشود.
DONORS = "SELECT COUNT(*) AS n FROM members WHERE id IN (SELECT donated_by FROM books)"
print("اعضایی که دستِکم یک کتاب اهدا کردهاند:", int(q(DONORS).iloc[0, 0]))
print("کتابهایی که اهداکننده دارند :",
int(q("SELECT COUNT(donated_by) AS n FROM books").iloc[0, 0]))
اعضایی که دستِکم یک کتاب اهدا کردهاند: 2840
کتابهایی که اهداکننده دارند : 3251
show_plan(DONORS)
id parent detail
3 0 SEARCH members USING INTEGER PRIMARY KEY (rowid=?)
7 0 LIST SUBQUERY 1
9 7 SCAN books
مرحلهٔ ۹ زیرِ مرحلهٔ ۷ است. یعنی: اول زیرپرسش اجرا میشود و فهرستِ اهداکنندهها ساخته میشود (SCAN books)، بعد برای هر مقدارِ آن فهرست یک SEARCH روی members انجام میشود.
و توجه کن که ۳٬۲۵۱ کتابِ اهدایی از ۲٬۸۴۰ عضو آمدهاند — بعضی اعضا بیش از یک کتاب دادهاند، و همین است که فهرستِ زیرپرسش کوتاهتر از تعدادِ کتابهاست.
🔧 اگر کار نکرد: پرتکرارترین خطای این فصل وقتی است که متنی را که از قبل پیشوند دارد دوباره به
planبدهی:
try:
con.execute("EXPLAIN QUERY PLAN EXPLAIN QUERY PLAN SELECT 1").fetchall()
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
sqlite3.OperationalError
near "EXPLAIN": syntax error
رفعش این است که متغیرهای پرسش را همیشه بدونِ پیشوند نگه داری و پیشوند را فقط از راهِ plan اضافه کنی. اگر یک بار پیشوند را داخلِ متنِ ذخیرهشدهٔ پرسش بگذاری، آن متن دیگر نه اجرا میشود و نه نقشه میدهد.
📏 اندازه بگیر: یک سطر یعنی چه؟ در جدولهای این فصل دو دانهبندی داشتیم: یک سطر از نتیجه (یک دسته، یک عضو) و یک سطر از نقشه، که یک مرحله از کارِ پایگاه داده است. با کدام شمارشِ دوم سنجیدی؟ با سهتا: دو پرسشِ بخشِ ۲ همان یک عنوان را دادند؛ جمعِ هشت دستهٔ بخشِ ۵ دقیقاً ۴۰٬۰۰۰ شد؛ و ۲٬۸۴۰ عضو در برابرِ ۳٬۲۵۱ کتابِ اهدایی با هم سازگارند چون یک عضو میتواند چند کتاب داده باشد. چه کسی از قلم افتاد؟ ۳۶٬۷۴۹ کتابی که
donated_byندارند و در نقشهٔ بخشِ ۶ اصلاً وارد فهرست نمیشوند — و فصلِ ۶ نشان میدهد همینNULLها چطور میتوانند جوابِ یک پرسشِ کاملاً معقول را صفر کنند.
🤖 از دستیارت بپرس: «تفاوتِ
SCANوSEARCHدر خروجیِEXPLAIN QUERY PLANچیست؟» بعد این را بپرس: «اگر نقشه بگویدSEARCH، آیا حتماً پرسشم سریع است؟» — جوابِ درست میگوید نه:SEARCHفقط میگوید راهِ میانبری استفاده شد، و اگر آن میانبر نصفِ جدول را برگرداند باز هم کند است. اگر جواب گفت «بله،SEARCHیعنی سریع»، همین حالا یادداشتش کن؛ فصلِ ۴ با عدد ردش میکند.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| query plan | کوئری پلن | نقشهٔ اجرا؛ فهرستِ کارهایی که پایگاه داده برای یک پرسش میکند |
SCAN |
اسکن | خواندنِ همهٔ سطرهای یک جدول یا index |
SEARCH |
سرچ | رفتنِ مستقیم به سطرهای موردِ نظر، بدونِ خواندنِ بقیه |
| covering index | کاورینگ ایندکس | indexی که همهٔ ستونهای لازمِ پرسش را دارد، پس جدول لازم نمیشود |
| temp b-tree | تمپ بیتری | ساختارِ موقتی که برای ORDER BY یا GROUP BY ساخته و بعد دور ریخته میشود |
| autoindex | اتوایندکس | indexی که SQLite خودش برای اجرای یک قید ساخته است |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
حالا میدانی SCAN یعنی چه و SEARCH یعنی چه، و دیدی که یک index — حتی آنکه SQLite خودش ساخته بود — میتواند SCAN را به SEARCH تبدیل کند.
فصلِ بعد همان کار را عمداً میکند: یک CREATE INDEX روی events(happened_at)، و همان پرسشِ ماهِ سومِ فصلِ ۱ که SCAN events میگرفت، SEARCH میشود. دو عددِ زمانیاش را کنارِ هم میگذاریم، و بعد سراغِ چیزی میرویم که کمتر کسی میپرسد: این index چقدر فضا گرفت و نوشتن را چقدر کند کرد؟
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.