آوند — داده: SQL، مدل‌سازی و تحلیل برای تصمیم

فصل ۴ از ۸

پیشرفت ترم
۰٪

در این فصل چه یاد می‌گیری#

فصلِ قبل نشان داد یک 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.

به آخر این فصل رسیدی!

اگر ساختی و جواب داد، این دکمه مال توست.