در این فصل چه یاد میگیری#
فصلِ قبل نشان داد یک index چه میکند. این فصل شش حالت را نشان میدهد که در هر شش، index ساخته شده، روی ستونِ درست است، و هیچ کاری نمیکند — و یکی از آن شش بدتر از بقیه است.
بدترینش این است: پرسشی که نقشهاش SEARCH ... USING INDEX است، در حدودِ یکدهمِ میلیثانیه جواب میدهد، و جوابش یک جدولِ خالی است در حالی که سطرِ موردِ نظر همانجا در جدول نشسته. نه خطا، نه هشدار، نه کندی — فقط جوابِ غلط.

آخر این فصل میتوانی:
- تشخیص بدهی کِی یک فیلتر
indexرا از کار میاندازد - بگویی چرا
indexروی ستونی با دو مقدار معمولاً ضرر است - تلهٔ عدمِ تطابقِ نوع را بگیری، قبل از اینکه گزارشت را خراب کند
- با
ANALYZEآماری بسازی که تصمیمِ پایگاه داده را عوض میکند
قبل از شروع#
از فصلِ ۳: CREATE INDEX، index چندستونی، هزینهٔ حجم. از فصلِ ۲: SCAN و SEARCH.
دانهبندی: یک سطر از events یک رویداد؛ یک سطر از members یک عضو.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
events |
۴۰۰٬۰۰۰ | چند ثانیه |
books |
۴۰٬۰۰۰ | کمتر از یک ثانیه |
members |
۱۲٬۰۰۰ | آنی |
branches |
۸ | آنی |
۱. تابعی که دورِ ستون پیچیده میشود#
index روی happened_at میسازیم و همان یک روز را دو جور میپرسیم:
con.execute("CREATE INDEX idx_events_time ON events(happened_at)")
con.commit()
DAY_FUNC = "SELECT COUNT(*) AS n FROM events WHERE substr(happened_at, 1, 10) = ?"
DAY_RANGE = "SELECT COUNT(*) AS n FROM events WHERE happened_at >= ? AND happened_at < ?"
print("با تابع:", int(q(DAY_FUNC, ("2022-03-15",)).iloc[0, 0]))
print("با بازه:", int(q(DAY_RANGE, ("2022-03-15", "2022-03-16")).iloc[0, 0]))
با تابع: 378
با بازه: 378
show_plan(DAY_FUNC, ("2022-03-15",))
print()
show_plan(DAY_RANGE, ("2022-03-15", "2022-03-16"))
print()
print("با تابع:", timed(DAY_FUNC, ("2022-03-15",)), "میلیثانیه")
print("با بازه:", timed(DAY_RANGE, ("2022-03-15", "2022-03-16")), "میلیثانیه")
id parent detail
3 0 SCAN events USING COVERING INDEX idx_events_time
id parent detail
3 0 SEARCH events USING COVERING INDEX idx_events_time (happened_at>? AND happened_at<?)
با تابع: 34.71 میلیثانیه
با بازه: 0.07 میلیثانیه
همان ۳۷۸، و صدها برابر اختلاف.
index مرتبشده بر اساسِ happened_at است، نه بر اساسِ substr(happened_at, 1, 10). پایگاه داده هیچ راهی ندارد بداند آن تابع ترتیب را حفظ میکند، پس مجبور است همهٔ ۴۰۰٬۰۰۰ مقدار را از index بخواند، تابع را روی هرکدام اجرا کند، و بسنجد — همان SCAN، فقط این بار روی index بهجای جدول.
قاعده: ستونِ فیلتر باید لخت بماند. هر تابعی، هر محاسبهای، هر CASTی که دورش بپیچد، index را از کار میاندازد. راهِ درست همان بازه است — یا اگر واقعاً به آن تابع نیاز داری، index عبارتیِ فصلِ قبل.
۲. LIKEی که هیچ indexی نجاتش نمیدهد#
con.execute("CREATE INDEX idx_books_title ON books(title)")
con.commit()
LIKE_TITLE = "SELECT COUNT(*) AS n FROM books WHERE title LIKE ?"
print("عنوانهایی که «باران» در آنهاست:", int(q(LIKE_TITLE, ("%باران%",)).iloc[0, 0]))
print("عنوانهایی که با «در جست» شروع میشوند:", int(q(LIKE_TITLE, ("در جست%",)).iloc[0, 0]))
عنوانهایی که «باران» در آنهاست: 8908
عنوانهایی که با «در جست» شروع میشوند: 6746
show_plan(LIKE_TITLE, ("%باران%",))
id parent detail
3 0 SCAN books USING COVERING INDEX idx_books_title
SCAN، با index روی همان ستون.
دلیلش با یک تصویر روشن میشود: دفترچهٔ تلفن بر اساسِ حرفِ اول مرتب است. «همهٔ کسانی که نامشان با «کا» شروع میشود» یک ورق زدن است؛ «همهٔ کسانی که در نامشان «را» هست» یعنی خواندنِ کلِ دفترچه. index هم دقیقاً همین است.
پس LIKE '%…' — با % در ابتدا — هیچوقت از یک index معمولی استفاده نمیکند، و این محدودیتِ طراحی است نه ضعفِ پیادهسازی.
ولی پیشوند هم در SQLite خودبهخود سریع نیست. تصویرِ دفترچهٔ تلفن میگوید «در جست%» باید SEARCH بگیرد، و این را باید اندازه گرفت نه فرض کرد:
print("LIKE با پیشوند:")
show_plan(LIKE_TITLE, ("در جست%",))
print()
PREFIX_RANGE = "SELECT COUNT(*) AS n FROM books WHERE title >= ? AND title < ?"
print("همان پیشوند با دو مرز:", int(q(PREFIX_RANGE, ("در جست", "در جسث")).iloc[0, 0]))
show_plan(PREFIX_RANGE, ("در جست", "در جسث"))
print()
print("زمان LIKE:", timed(LIKE_TITLE, ("در جست%",), repeat=9), "میلیثانیه")
print("زمان بازه:", timed(PREFIX_RANGE, ("در جست", "در جسث"), repeat=9), "میلیثانیه")
LIKE با پیشوند:
id parent detail
3 0 SCAN books USING COVERING INDEX idx_books_title
همان پیشوند با دو مرز: 6746
id parent detail
3 0 SEARCH books USING COVERING INDEX idx_books_title (title>? AND title<?)
زمان LIKE: 3.16 میلیثانیه
زمان بازه: 0.43 میلیثانیه
LIKE در SQLite بهطورِ پیشفرض به بزرگی و کوچکیِ حروف حساس نیست، و index معمولی با ترتیبِ دقیقِ بایتها مرتب شده، پس حتی با پیشوند هم SCAN میگیرد. همان پرسش با دو مرزِ >= و < همان ۶٬۷۴۶ سطر را میدهد و SEARCH میگیرد. قاعدهٔ «ستونِ فیلتر لخت بماند» اینجا هم برقرار است و شکلِ درستِ نوشتنِ شرط باز همان بازه است.
۳. indexی که پرسش را کندتر کرد#
ستونِ kind فقط دو مقدار دارد. index رویش میسازیم و اندازه میگیریم:
BY_KIND = "SELECT SUM(days) AS total FROM events WHERE kind = ?"
print("پاسخ:", int(q(BY_KIND, ("out",)).iloc[0, 0]))
print("سطرهایی که شرط را دارند:",
int(q("SELECT COUNT(*) AS n FROM events WHERE kind = ?", ("out",)).iloc[0, 0]),
"از", int(q("SELECT COUNT(*) AS n FROM events").iloc[0, 0]))
kind_before = timed(BY_KIND, ("out",))
pages_before = con.execute("PRAGMA page_count").fetchone()[0]
con.execute("CREATE INDEX idx_events_kind ON events(kind)")
con.commit()
kind_after = timed(BY_KIND, ("out",))
pages_after = con.execute("PRAGMA page_count").fetchone()[0]
print("پاسخ بعد از index:", int(q(BY_KIND, ("out",)).iloc[0, 0]))
print("صفحههایی که این index گرفت:", pages_after - pages_before)
پاسخ: 4259225
سطرهایی که شرط را دارند: 207831 از 400000
پاسخ بعد از index: 4259225
صفحههایی که این index گرفت: 1125
show_plan(BY_KIND, ("out",))
print()
print("قبل از index:", kind_before, "میلیثانیه")
print("بعد از index:", kind_after, "میلیثانیه")
id parent detail
4 0 SEARCH events USING INDEX idx_events_kind (kind=?)
قبل از index: 41.93 میلیثانیه
بعد از index: 53.74 میلیثانیه
نقشه SEARCH میگوید و پرسش کندتر شده است.
این تناقض نیست. SEARCH یعنی «از index استفاده کردم»، نه «سریع بود». آن index بیش از دویست هزار ورودی برگرداند، و پایگاه داده برای هرکدام باید یک بار هم به خودِ جدول سر بزند تا days را بردارد. خواندنِ نصفِ جدول از راهِ index، گرانتر از خواندنِ کلِ جدول بهترتیب است.
اسمِ این خاصیت گزینشگری است: یک index وقتی میارزد که فیلترش کسرِ کوچکی از سطرها را نگه دارد. اینجا کسرِ ما بیش از نصف است.
و ۱٬۱۲۵ صفحه هم گرفت. یعنی این index دقیقاً مصداقِ چیزی است که باید از آن ترسید: حجم میگیرد، هر INSERT را کند میکند، و هیچ پرسشی را سریع نمیکند.
۴. نقشهٔ بیعیب، جوابِ صفر#
ستونِ card_code متن است و مقدارهایش با صفر شروع میشوند. index رویش میسازیم و یک عضو را صدا میزنیم — دو بار، با دو نوعِ متفاوت از همان مقدار:
con.execute("CREATE INDEX idx_members_card ON members(card_code)")
con.commit()
BY_CARD = "SELECT id, name, city FROM members WHERE card_code = ?"
print("سه نمونه از این ستون:",
list(q("SELECT card_code FROM members ORDER BY id LIMIT 3")["card_code"]))
print()
print("با رشته:")
print(q(BY_CARD, ("0700123",)))
print()
print("با عدد:")
print(q(BY_CARD, (700123,)))
سه نمونه از این ستون: ['0700001', '0700002', '0700003']
با رشته:
id name city
0 123 مریم یزدانی اصفهان
با عدد:
Empty DataFrame
Columns: [id, name, city]
Index: []
show_plan(BY_CARD, (700123,))
print()
print("با رشته:", timed(BY_CARD, ("0700123",)), "میلیثانیه")
print("با عدد :", timed(BY_CARD, (700123,)), "میلیثانیه")
id parent detail
3 0 SEARCH members USING INDEX idx_members_card (card_code=?)
با رشته: 0.07 میلیثانیه
با عدد : 0.10 میلیثانیه
نقشه بیعیب است، زمان بیعیب است، و جواب خالی است.
SQLite عددِ 700123 را با قاعدهٔ نوعِ همان ستون به متن تبدیل کرد و شد '700123' — که با '0700123' برابر نیست. آن صفرِ اول کلِ ماجراست. و چون مقایسه فقط «نامساوی» درآمد، نه خطا، نه هشدار: پایگاه داده کارِ درستی کرد و جوابِ درستِ یک پرسشِ دیگر را داد.
این خطرناکترین حالتِ کلِ این ترم است، چون هیچ نشانهای ندارد. یک index کُند دیر یا زود کسی را عصبانی میکند؛ یک گزارشِ خالی فقط به این نتیجه میرسد که «آن عضو کارتی ندارد».
✅ چک کن: هر جا فیلتری روی یک ستون صفر سطر برگرداند، قبل از هر نتیجهگیری
typeofهمان ستون را بگیر و نوعِ مقداری را که فرستادی با آن بسنج. صفر سطر یک جواب است، نه لزوماً جوابِ درست.
۵. OR روی دو ستون#
con.execute("CREATE INDEX idx_events_book ON events(book_id)")
con.commit()
EITHER = "SELECT COUNT(*) AS n FROM events WHERE book_id = ? OR member_id = ?"
print("پاسخ:", int(q(EITHER, (17, 55)).iloc[0, 0]))
one_index = timed(EITHER, (17, 55))
show_plan(EITHER, (17, 55))
con.execute("CREATE INDEX idx_events_member ON events(member_id)")
con.commit()
two_indexes = timed(EITHER, (17, 55))
print()
print("پاسخ بعد از index دوم:", int(q(EITHER, (17, 55)).iloc[0, 0]))
show_plan(EITHER, (17, 55))
print()
print("با یک index :", one_index, "میلیثانیه")
print("با دو index :", two_indexes, "میلیثانیه")
پاسخ: 44
id parent detail
3 0 SCAN events
پاسخ بعد از index دوم: 44
id parent detail
5 0 MULTI-INDEX OR
6 5 INDEX 1
14 6 SEARCH events USING INDEX idx_events_book (book_id=?)
19 5 INDEX 2
27 19 SEARCH events USING INDEX idx_events_member (member_id=?)
با یک index : 29.23 میلیثانیه
با دو index : 0.07 میلیثانیه
index روی book_id بود، شرط هم روی book_id بود، و نقشه SCAN گفت.
دلیلش منطقی است: OR یعنی سطر ممکن است بهخاطرِ شرطِ دوم هم قبول شود، پس حتی اگر index تمامِ سطرهای شرطِ اول را بدهد، بقیهٔ جدول همچنان باید بررسی شود. یک index برای یک OR کافی نیست؛ برای هر دو طرف لازم است.
با index دوم، نقشه MULTI-INDEX OR میشود: هر طرف جداگانه با index خودش پیدا میشود و نتیجهها با هم یکی میشوند.
۶. ANALYZE: نه پرسش عوض شد، نه اسکیما#
آخرین حالت از همه غافلگیرکنندهتر است. حالا هم index روی kind داریم و هم روی happened_at، و پرسشی مینویسیم که هر دو را میتواند استفاده کند:
FIRST_OUT = """
SELECT id, happened_at
FROM events
WHERE kind = ?
ORDER BY happened_at, id
LIMIT 5
"""
print(q(FIRST_OUT, ("out",)))
id happened_at
0 1 2022-01-01 00:02:34
1 3 2022-01-01 00:04:44
2 8 2022-01-01 00:09:34
3 11 2022-01-01 00:29:51
4 12 2022-01-01 00:30:54
before_analyze = timed(FIRST_OUT, ("out",))
show_plan(FIRST_OUT, ("out",))
print()
print("قبل از ANALYZE:", before_analyze, "میلیثانیه")
id parent detail
5 0 SEARCH events USING INDEX idx_events_kind (kind=?)
22 0 USE TEMP B-TREE FOR ORDER BY
قبل از ANALYZE: 54.65 میلیثانیه
حالا فقط یک دستور اجرا میکنیم — ANALYZE — و همان پرسش را دوباره میپرسیم:
con.execute("ANALYZE")
con.commit()
print(q(FIRST_OUT, ("out",)))
id happened_at
0 1 2022-01-01 00:02:34
1 3 2022-01-01 00:04:44
2 8 2022-01-01 00:09:34
3 11 2022-01-01 00:29:51
4 12 2022-01-01 00:30:54
after_analyze = timed(FIRST_OUT, ("out",))
show_plan(FIRST_OUT, ("out",))
print()
print("بعد از ANALYZE:", after_analyze, "میلیثانیه")
id parent detail
5 0 SCAN events USING INDEX idx_events_time
بعد از ANALYZE: 0.07 میلیثانیه
نه یک حرف از پرسش عوض شد، نه یک index ساخته یا حذف شد. تنها کاری که کردیم این بود که SQLite رفت و شمرد.
قبل از ANALYZE نقشه SEARCH events USING INDEX idx_events_kind بود بهعلاوهٔ USE TEMP B-TREE FOR ORDER BY، همانطور که بالا چاپ شد: پایگاه داده حدس زد که فیلترِ kind گزینشگر است، دویست هزار سطر گرفت، و بعد مجبور شد همهشان را مرتب کند. بعد از ANALYZE فهمید که kind گزینشگر نیست و بهجایش از index تاریخ استفاده کرد، که خودش از قبل مرتب است — پس پنج سطرِ اول را برداشت و ایستاد.
جایی که این دانش ذخیره میشود یک جدول است:
print(q("""
SELECT idx, stat FROM sqlite_stat1
WHERE tbl = ? ORDER BY idx
""", ("events",)))
idx stat
0 idx_events_book 400000 11
1 idx_events_kind 400000 200000
2 idx_events_member 400000 34
3 idx_events_time 400000 1
عددِ دوم میگوید بهازای هر مقدار، بهطورِ متوسط چند سطر برمیگردد: برای kind دویست هزار، برای happened_at یک. تمامِ تصمیمِ بالا از همین دو عدد آمد.
پس ANALYZE را بعد از هر تغییرِ بزرگ در حجم یا توزیعِ داده اجرا کن — و قبل از اینکه به این نتیجه برسی که «پایگاه داده احمق است»، اول ببین اصلاً آماری دارد یا نه.
🔧 اگر کار نکرد: موقعِ آزمایشِ
indexها زیاد پیش میآید که بخواهی یکی را حذف کنی و اسمش را غلط بنویسی:
try:
con.execute("DROP INDEX idx_events_tmie")
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
sqlite3.OperationalError
no such index: idx_events_tmie
رفعش DROP INDEX IF EXISTS است، و اسمِ درست را همیشه از sqlite_master بگیر نه از حافظهات.
📏 اندازه بگیر: یک سطر یعنی چه؟ یک رویداد در بخشهای ۱ و ۳ و ۵، یک کتاب در بخشِ ۲، و یک عضو در بخشِ ۴. با کدام شمارشِ دوم سنجیدی؟ با سهتا: دو راهِ پرسیدنِ همان روز هر دو ۳۷۸ دادند؛
SUM(days)قبل و بعد ازindexهمان ۴٬۲۵۹٬۲۲۵ ماند؛ و پنج سطرِ بخشِ ۶ قبل و بعد ازANALYZEعیناً یکی بودند. چه کسی از قلم افتاد؟ در بخشِ ۴، عضو شمارهٔ ۱۲۳ — که وجود دارد، در جدول است، و پرسشِ ما ندیدش. هر گزارشی که روی آن پرسش بنا میشد، دربارهٔ آدمی حرف میزد که از قلم افتاده بود بیآنکه کسی بفهمد.
🤖 از دستیارت بپرس: «چرا یک
indexروی ستونی که فقط دو مقدار دارد معمولاً بیفایده است؟» بعد این را بپرس: «اگر پرسشی روی یک ستونِTEXTکه عدد در آن ذخیره شده فیلتر بزنم و مقدار را بهشکلِ عدد بفرستم، چه اتفاقی میافتد؟» — جوابِ درست میگوید مقایسه انجام میشود ولی ممکن است هیچ سطری را نگیرد، و هیچ خطایی هم نمیدهد. اگر جواب گفت «خطا میگیری»، با بخشِ ۴ ردش کن.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| selectivity | سلکتیویتی | گزینشگری؛ اینکه یک فیلتر چه کسری از سطرها را نگه میدارد |
| sargable | سارجبل | شرطی که index میتواند رویش کار کند — یعنی ستونش لخت است |
| type affinity | تایپ افینیتی | قاعدهٔ SQLite برای تبدیلِ مقدار به نوعِ ستون هنگامِ مقایسه |
ANALYZE |
آنالایز | دستوری که آمارِ توزیعِ ستونها را میسازد و در sqlite_stat1 میگذارد |
MULTI-INDEX OR |
مولتی ایندکس اور | استفاده از دو index جدا برای دو طرفِ یک OR |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
چهار فصل دربارهٔ خواندن بود. فصلِ بعد سراغِ نوشتن میرود، و با آزمایشی شروع میشود که نسبتِ چاپشدهاش از هر نسبتِ چاپشدهٔ این ترم بزرگتر است: هزار سطر، یک بار با یک commit بعد از هر سطر و یک بار با یک commit در آخرِ کار.
و همانجا با خطایی آشنا میشویم که تا حالا ندیدهایم چون فقط یک اتصال داشتیم: database is locked.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.