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

فصل ۲ از ۸

پیشرفت ترم
۰٪

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

فصلِ قبل یک پرسش را زمان گرفت و جواب داد «سی‌وچند میلی‌ثانیه». این فصل همان پرسش را می‌پرسد و جوابِ کاملاً دیگری می‌گیرد: 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 یعنی «از سطرِ اول تا سطرِ آخرِ این جدول را می‌خوانم». پایگاه داده هیچ راهِ میان‌بری به تاریخ ندارد، پس هر ۴۰۰٬۰۰۰ سطر را برداشت، شرط را روی هرکدام سنجید، و ۱۱٬۴۲۱ تا را نگه داشت. آن سی‌وچند میلی‌ثانیهٔ فصلِ قبل، هزینهٔ خواندنِ ۳۸۸٬۵۷۹ سطری بود که به کارمان نیامدند.

حالا همان کتاب را دو جور صدا می‌زنیم. هر دو پرسش دقیقاً یک عنوان برمی‌گردانند:

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 چقدر فضا گرفت و نوشتن را چقدر کند کرد؟

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

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