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

فصل ۷ از ۸

پیشرفت ترم
۰٪

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

شش فصل ابزارِ کارایی جمع کردیم و همه‌شان یک فرضِ نگفته داشتند: sqlite3 ابزارِ درستِ این کار است. این فصل همان فرض را با عدد می‌سنجد.

چهار نخ را هم‌زمان به نوشتن می‌گذاریم و کار کندتر تمام می‌شود — بازدهیِ کمتر از یک، نه ۴ برابر. بعد همان چهار نخ را به خواندن می‌گذاریم و نزدیک به سه برابر سریع‌تر می‌شود. یک عدد، دو نتیجهٔ کاملاً متضاد، و همان مرزی که کلِ این فصل دربارهٔ آن است.

یک لولهٔ باریک که چهار خطِ ورودی به آن می‌رسند و فقط یکی در هر لحظه از آن رد می‌شود، در کنارِ چهار لولهٔ موازیِ برداشت

آخر این فصل می‌توانی:

  • با عدد نشان بدهی چرا افزودنِ نویسنده سرعت نمی‌آورد و افزودنِ خواننده می‌آورد
  • سقفِ تعدادِ پارامترهای یک پرسش را پیدا کنی و پیامِ خطایش را بشناسی
  • رشدِ زمانِ اجرا با رشدِ داده را اندازه بگیری به‌جای اینکه فرضش کنی
  • بگویی چه چیزی از این دوره به یک پایگاه دادهٔ سرور منتقل می‌شود و چه چیزی نمی‌شود

قبل از شروع#

از فصلِ ۵: تراکنش، commit، و database is locked. از فصلِ ۱: میانهٔ چند اجرا.

دانه‌بندی: یک سطر از events یک رویداد. جدول‌های bulk و events_small را همین فصل می‌سازد.

📓 نوت‌بوک: نوت‌بوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

جدول تعدادِ سطر زمانِ ساخت
events ۴۰۰٬۰۰۰ چند ثانیه
books ۴۰٬۰۰۰ کمتر از یک ثانیه
bulk (همین فصل می‌سازدش) ۲۰٬۰۰۰ آنی
events_small (همین فصل می‌سازدش) ۴۰٬۰۰۰ آنی

۱. چهار نویسنده، و سرعتی که کم شد#

بیست هزار سطر را دو جور می‌نویسیم: یک بار چهار دستهٔ پنج‌هزارتایی پشتِ سرِ هم، و یک بار همان چهار دسته هم‌زمان روی چهار نخ.

threading را لازم نیست بلد باشی و این فصل هم یادش نمی‌دهد. فقط این‌قدر بدان که Thread یک تابع را می‌گیرد و اجرایش را کنارِ بقیه پیش می‌برد؛ اینجا صرفاً ابزارِ آزمایش است تا بشود چند نویسنده را هم‌زمان کرد.

import sqlite3
import threading

con.execute("CREATE TABLE bulk (id INTEGER PRIMARY KEY, who INTEGER NOT NULL)")
con.commit()


def write_batch(tag, first_id, count):
    """یک اتصال جدا، یک تراکنش، پنج هزار سطر."""
    own = sqlite3.connect(DB_PATH, timeout=30)
    own.executemany("INSERT INTO bulk VALUES (?, ?)",
                    [(first_id + step, tag) for step in range(count)])
    own.commit()
    own.close()


start = time.perf_counter()
for tag in range(4):
    write_batch(tag, 1 + tag * 5000, 5000)
serial_ms = (time.perf_counter() - start) * 1000
print("سطرها بعد از نوشتنِ پشتِ‌سرِهم:", int(q("SELECT COUNT(*) AS n FROM bulk").iloc[0, 0]))

con.execute("DELETE FROM bulk")
con.commit()

threads = [threading.Thread(target=write_batch, args=(tag, 1 + tag * 5000, 5000))
           for tag in range(4)]
start = time.perf_counter()
for thread in threads:
    thread.start()
for thread in threads:
    thread.join()
parallel_ms = (time.perf_counter() - start) * 1000
print("سطرها بعد از نوشتنِ هم‌زمان :", int(q("SELECT COUNT(*) AS n FROM bulk").iloc[0, 0]))
print("دسته‌های متمایز در جدول    :",
      int(q("SELECT COUNT(DISTINCT who) AS n FROM bulk").iloc[0, 0]))
سطرها بعد از نوشتنِ پشتِ‌سرِهم: 20000
سطرها بعد از نوشتنِ هم‌زمان : 20000
دسته‌های متمایز در جدول    : 4
print("پشتِ سرِ هم:", round(serial_ms, 1), "میلی‌ثانیه")
print("هم‌زمان   :", round(parallel_ms, 1), "میلی‌ثانیه")
print("بازدهی    :", round(serial_ms / parallel_ms, 2), "برابر")
پشتِ سرِ هم: 39.4 میلی‌ثانیه
هم‌زمان   : 56.8 میلی‌ثانیه
بازدهی    : 0.69 برابر

بازدهی کمتر از یک است — یعنی هم‌زمانی نه‌تنها کمکی نکرد، ضرر هم زد.

دلیلش همان قانونِ فصلِ ۵ است: در هر لحظه فقط یک اتصال می‌تواند بنویسد. چهار نخ نوبتی نوشتند، و هزینهٔ قفل و انتظار به کارِ اصلی اضافه شد. این یک ضعفِ پیاده‌سازی نیست؛ تصمیمِ طراحیِ SQLite است و با هیچ تنظیمی رفع نمی‌شود.

۲. چهار خواننده، و نتیجهٔ کاملاً برعکس#

حالا همان آزمایش با خواندن:

def read_batch(rounds):
    """یک اتصال جدا، چند بار یک پرسشِ سنگین."""
    own = sqlite3.connect(DB_PATH)
    for _ in range(rounds):
        own.execute("SELECT COUNT(*) FROM events WHERE kind = ?", ("out",)).fetchall()
    own.close()


start = time.perf_counter()
for _ in range(4):
    read_batch(5)
read_serial_ms = (time.perf_counter() - start) * 1000

readers = [threading.Thread(target=read_batch, args=(5,)) for _ in range(4)]
start = time.perf_counter()
for reader in readers:
    reader.start()
for reader in readers:
    reader.join()
read_parallel_ms = (time.perf_counter() - start) * 1000

print("پرسش‌های اجراشده در هر دو حالت:", 4 * 5)
print("پاسخ همان پرسش:",
      int(q("SELECT COUNT(*) AS n FROM events WHERE kind = ?", ("out",)).iloc[0, 0]))
پرسش‌های اجراشده در هر دو حالت: 20
پاسخ همان پرسش: 207831
print("پشتِ سرِ هم:", round(read_serial_ms, 1), "میلی‌ثانیه")
print("هم‌زمان   :", round(read_parallel_ms, 1), "میلی‌ثانیه")
print("بازدهی    :", round(read_serial_ms / read_parallel_ms, 2), "برابر")
پشتِ سرِ هم: 713.6 میلی‌ثانیه
هم‌زمان   : 257.3 میلی‌ثانیه
بازدهی    : 2.77 برابر

همان چهار نخ، همان فایل، و این بار نزدیک به سه برابر سریع‌تر.

پس جملهٔ درست دربارهٔ SQLite این نیست که «هم‌زمانی ندارد». این است: هر تعداد خواننده، دقیقاً یک نویسنده.

و همین دو عدد، مرزِ تصمیم را می‌سازند: اگر بارِ کارت خواندن است، این محدودیت شاید هرگز به تو نخورد. اگر نوشتنِ هم‌زمان است، از روزِ اول به آن خواهی خورد.

✅ چک کن: در آزمایشِ بخشِ ۱ هر دو راه باید دقیقاً ۲۰٬۰۰۰ سطر از ۴ دستهٔ متمایز ساخته باشند. اگر شمارشِ حالتِ هم‌زمان کمتر درآمد، یکی از نخ‌ها خطای قفل گرفته و بی‌صدا رد شده — و آن‌وقت داری زمانِ کارِ ناتمام را با زمانِ کارِ کامل می‌سنجی.

۳. سقفی که پیامِ خطا دارد#

بعضی مرزها را می‌شود دقیقاً پیدا کرد. این یکی از آن‌هاست: حداکثر تعدادِ پارامترِ ? در یک پرسش.

🔧 اگر کار نکرد: این خطا معمولاً وقتی می‌آید که فهرستی از شناسه‌ها را از پایتون به یک IN می‌دهی و فهرست بزرگ می‌شود:

for size in (32766, 32767):
    ids = list(range(1, size + 1))
    sql = "SELECT COUNT(*) AS n FROM books WHERE id IN (%s)" % ",".join("?" * size)
    try:
        print(size, "پارامتر →", con.execute(sql, ids).fetchone()[0], "سطر")
    except Exception as error:
        print(size, "پارامتر →", type(error).__module__ + "." + type(error).__name__)
        print("           ", error)
32766 پارامتر → 32766 سطر
32767 پارامتر → sqlite3.OperationalError
            too many SQL variables

۳۲٬۷۶۶ قبول، ۳۲٬۷۶۷ رد. یک مرزِ تیز، با یک پیامِ روشن. این عدد سقفِ پیش‌فرضِ خودِ SQLite است و سازندهٔ هر نسخه می‌تواند عوضش کند، پس روی ماشینِ تو شاید جای دیگری باشد؛ con.getlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER) مقدارِ همان ماشین را می‌گوید.

رفعش این نیست که فهرست را تکه‌تکه کنی — آن راهِ‌حلِ درجه‌دو است. راهِ درست این است که آن فهرست را اصلاً از پایگاه داده بیرون نیاوری: اگر شناسه‌ها خودشان از یک پرسشِ دیگر آمده‌اند، همان پرسش را به‌شکلِ زیرپرسش داخلِ IN بگذار و هیچ پارامتری لازم نمی‌شود.

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

۴. ده برابر داده، چند برابر زمان؟#

یک نسخهٔ ده‌درصدی از events می‌سازیم — چهل هزار رویدادِ اولِ دوره — و همان پرسش را روی هر دو می‌گیریم:

con.executescript("""
    CREATE TABLE events_small (
        id          INTEGER PRIMARY KEY,
        book_id     INTEGER NOT NULL,
        member_id   INTEGER NOT NULL,
        happened_at TEXT    NOT NULL,
        kind        TEXT    NOT NULL,
        days        INTEGER NOT NULL
    );
    INSERT INTO events_small SELECT * FROM events WHERE id <= 40000;
""")
con.commit()

SMALL = "SELECT COUNT(*) AS n, SUM(days) AS s FROM events_small"
FULL = "SELECT COUNT(*) AS n, SUM(days) AS s FROM events"
print(q(SMALL))
print(q(FULL))
print(q("SELECT MIN(happened_at) AS first, MAX(happened_at) AS last FROM events_small"))
       n       s
0  40000  819976
        n        s
0  400000  8200275
                 first                 last
0  2022-01-01 00:02:34  2022-04-20 02:08:42
small_ms = timed(SMALL)
full_ms = timed(FULL)
print("۴۰ هزار سطر :", small_ms, "میلی‌ثانیه")
print("۴۰۰ هزار سطر:", full_ms, "میلی‌ثانیه")
print("نسبتِ داده   : 10 برابر")
print("نسبتِ زمان   :", round(full_ms / small_ms, 1), "برابر")
۴۰ هزار سطر : 1.75 میلی‌ثانیه
۴۰۰ هزار سطر: 28.23 میلی‌ثانیه
نسبتِ داده   : 10 برابر
نسبتِ زمان   : 16.1 برابر

ده برابر داده، و بیش از ده برابر زمان.

نسبتِ زمان بزرگ‌تر از نسبتِ داده درآمد و باید همان‌طور که هست نوشته شود: «خطی رشد می‌کند» یک فرض است، نه یک قانون. جدولِ کوچک‌تر تقریباً کامل در حافظه جا می‌شود و جدولِ بزرگ نه، و همین تفاوت خودش را در عدد نشان می‌دهد.

و این تنها راهِ درستِ پاسخ به پرسشِ «داده‌ام ده برابر شود چه می‌شود؟» است: یک نسخهٔ کوچک‌تر بساز، هر دو را اندازه بگیر، و نسبت را بنویس. برون‌یابی از یک عدد، حدس است.

۵. نوعی که قید نیست#

یک تفاوتِ بنیادی با پایگاه دادهٔ سرور، که هیچ ربطی به سرعت ندارد ولی روزی گرانت تمام می‌شود:

con.executescript("""
    CREATE TABLE loose (n INTEGER);
    CREATE TABLE tight (n INTEGER) STRICT;
""")
con.commit()

con.execute("INSERT INTO loose VALUES (?)", ("سلام",))
con.commit()
print(q("SELECT n, typeof(n) AS t FROM loose"))
print()
try:
    con.execute("INSERT INTO tight VALUES (?)", ("سلام",))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
      n     t
0  سلام  text

sqlite3.IntegrityError
cannot store TEXT value in INTEGER column tight.n

ستون INTEGER اعلام شده بود و یک رشتهٔ فارسی را بی‌صدا قبول کرد.

در SQLite نوعِ ستون یک تمایل است نه یک قید: اگر مقدار قابلِ تبدیل باشد تبدیل می‌شود، و اگر نباشد همان‌طور که هست ذخیره می‌شود. در PostgreSQL و MySQL همین INSERT از روزِ اول خطا می‌دهد.

راهِ‌حلش از نسخه‌های جدیدِ SQLite هست و اسمش STRICT است — همان‌طور که جدولِ دوم نشان داد. اگر جدولی می‌سازی که قرار است سال‌ها بماند، STRICT بنویس.

و همین‌جا چند تفاوتِ گویشِ دیگر، که فقط برای خواندن‌اند و نیازی به اجرا ندارند:

-- کلیدِ خودافزا
INTEGER PRIMARY KEY               -- SQLite
GENERATED ALWAYS AS IDENTITY      -- PostgreSQL
AUTO_INCREMENT                    -- MySQL

-- بریدنِ تاریخ به ماه
strftime('%Y-%m', happened_at)    -- SQLite
date_trunc('month', happened_at)  -- PostgreSQL
DATE_FORMAT(happened_at, '%Y-%m') -- MySQL

-- چسباندنِ دو رشته
first_name || ' ' || last_name    -- SQLite و PostgreSQL
CONCAT(first_name, ' ', last_name)-- MySQL

۶. چه چیزی منتقل می‌شود و چه چیزی نمی‌شود#

چیزی که یاد گرفتی به پایگاه دادهٔ سرور منتقل می‌شود؟
SELECT، WHERE، GROUP BY، JOIN، تابعِ پنجره، CTE بله، تقریباً کلمه‌به‌کلمه
NULL و منطقِ سه‌ارزشی، تلهٔ NOT IN بله، عیناً — این‌ها از استانداردند
دانه‌بندی، شمارشِ کنترلی، اعلامِ اینکه چه کسی از قلم افتاد بله، و مهم‌ترین چیزی است که می‌بری
ایدهٔ index، گزینشگری، covering index بله، ولی نامِ دستورها و جزئیاتِ نقشه فرق می‌کند
EXPLAIN QUERY PLAN نه با همین نام — در PostgreSQL می‌شود EXPLAIN ANALYZE و خروجی‌اش کاملاً متفاوت است
PRAGMA و journal_mode و user_version نه؛ مخصوصِ SQLite است
نوعِ ستون به‌عنوان تمایل نه؛ در بقیه یک قیدِ واقعی است
«یک نویسنده» نه؛ پایگاه دادهٔ سرور برای نویسندهٔ هم‌زمان ساخته شده

سطرِ سوم مهم‌ترین سطرِ این جدول است. نحوِ SQL را در چند روز می‌شود دوباره یاد گرفت؛ عادتِ اینکه هر عدد را با یک شمارشِ دوم بسنجی، چیزی است که کلِ این دوره برای ساختنش نوشته شده.

و کِی باید کوچ کرد؟ سه نشانه، هر سه اندازه‌گرفتنی:

  1. چند نویسندهٔ هم‌زمان داری و آزمایشِ بخشِ ۱ روی بارِ واقعی‌ات بازدهیِ کمتر از یک می‌دهد.
  2. داده‌ات آن‌قدر بزرگ است که آزمایشِ بخشِ ۴ نشان می‌دهد زمان از بودجه‌ات بیرون زده.
  3. چند برنامهٔ جدا روی چند ماشین باید به همان داده وصل شوند — که SQLite اصلاً برایش ساخته نشده، چون یک فایل است نه یک سرویس.

و اگر هیچ‌کدام از این سه نشانه را نداری، کوچ نکن. یک فایل که پشتیبان‌گیری‌اش کپی کردنِ همان فایل است، سادگیِ کمی نیست.

📏 اندازه بگیر: یک سطر یعنی چه؟ در bulk یک سطرِ ساختگی از یک نخ، در events_small یک رویداد از هر ده رویداد. با کدام شمارشِ دوم سنجیدی؟ با سه‌تا: هر دو راهِ نوشتن دقیقاً ۲۰٬۰۰۰ سطر از ۴ دستهٔ متمایز ساختند؛ هر دو حالتِ خواندن دقیقاً ۲۰ پرسش اجرا کردند؛ و جمعِ days در نسخهٔ کوچک ۸۱۹٬۹۷۶ شد که تقریباً یک‌دهمِ ۸٬۲۰۰٬۲۷۵ است. چه کسی از قلم افتاد؟ نسخهٔ کوچک فقط چهل هزار رویدادِ اولِ دوره را دارد — یعنی حدودِ سه ماه و نیمِ اول، نه یک نمونهٔ تصادفی از سه سال. برای اندازه‌گیریِ زمان بی‌خطر است، چون فقط تعدادِ سطر مهم است؛ برای هر ادعای آماری دربارهٔ خودِ داده نه.

🤖 از دستیارت بپرس: «SQLite برای چه نوع کاری مناسب است و برای چه نوعی نیست؟» بعد این را بپرس: «آیا با روشن کردنِ WAL می‌توانم چند نویسندهٔ هم‌زمان داشته باشم؟» — جوابِ درست می‌گوید نه: WAL خواننده‌ها را از نویسنده جدا می‌کند، ولی نویسنده همچنان یکی است. اگر جواب گفت بله، با بازدهیِ بخشِ ۱ ردش کن.

واژه‌های تازهٔ این فصل#

کلمه تلفظ به حروف فارسی یعنی چه
concurrency کانکارنسی هم‌زمانی؛ چند کار که در یک بازه پیش می‌روند
throughput تروپوت بازدهی؛ چند کار در واحدِ زمان تمام می‌شود
contention کانتنشن رقابت بر سرِ یک منبعِ مشترک، مثلِ حقِ نوشتن
STRICT table استریکت تیبل جدولی که نوعِ ستون‌هایش قیدِ واقعی است
dialect دایلکت گویش؛ تفاوت‌های SQL بینِ پایگاه داده‌های مختلف
client-server کلاینت-سرور معماری‌ای که در آن پایگاه داده یک سرویسِ جداست، نه یک فایل

تمرین‌ها

اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.

در فصل بعد#

هفت فصل ابزار جمع کردیم. فصلِ آخر هیچ ابزارِ تازه‌ای ندارد و به‌جایش کارِ کامل را انجام می‌دهد: یک پرسشِ گزارشیِ واقعی و کند، از اندازه‌گیری تا نتیجه.

نقشهٔ اجرا را می‌خوانیم، یک فرضیه می‌نویسیم، یک تغییر می‌دهیم، و یک باندِ زمانی چاپ می‌کنیم. یکی از فرضیه‌ها پرسش را کندتر می‌کند و یکی تفاوتی کوچک‌تر از نوسان می‌سازد، و هر دو را دقیقاً با همان جزئیاتِ فرضیهٔ موفق گزارش می‌کنیم — چون گزارشی که فقط موفقیت‌ها را دارد، گزارش نیست.

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

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