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

فصل ۵ از ۹

پیشرفت ترم
۰٪

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

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

پرسشِ «پرامانت‌ترین کتاب‌ها کدام‌اند؟» روی مدلِ نرمال‌شده باید هر بار ۲۶ هزار سطرِ رویداد را بگردد. اگر همان عدد را در یک ستونِ کنارِ خودِ کتاب نگه داریم، خواندنش تقریباً رایگان می‌شود. هر دو زمان را اندازه می‌گیریم.

و بعد هر دو هزینه‌اش را هم اندازه می‌گیریم. هزینهٔ اول کوچک است: نوشتن حدودِ یک‌ونیم برابر کند می‌شود. هزینهٔ دوم آن چیزی است که کارِ واقعی را خراب می‌کند: بعد از ۵۰۰ امانتِ تازه که فقط در یک جا ثبت شوند، آن ستون دقیقاً ۵۰۰ واحد دروغ می‌گوید — و هیچ خطایی نمی‌دهد.

و در راه، یک عددِ غیرمنتظره پیدا می‌کنیم که خودش یک درسِ کامل است.

یک قفسهٔ ابزار با نسخهٔ دومِ یک ابزار که نزدیک دست گذاشته شده و نسخهٔ اصلی در انتهای قفسه مانده

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

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

قبل از شروع#

از فصلِ ۴: نرمال‌سازی و وابستگیِ تابعی. از ترمِ ۳ فصلِ ۳: تجمیع قبل از پیوند. از فصلِ ۳ همین ترم: آن ۳۶ رویدادِ یتیم — که در بخشِ ۲ از یک راهِ کاملاً تازه دوباره پیدایشان می‌کنیم.

تعریف، در یک جمله: غیرنرمال‌سازی یعنی عمداً یک واقعیت را دو جا نگه داری تا خواندنش ارزان شود. کلمهٔ مهم «عمداً» است — همان کاری که فصلِ ۴ به‌عنوان اشتباه نشانش داد، اینجا با چشمِ باز و با عدد انجام می‌شود.

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

جدول تعدادِ سطر زمانِ ساخت
catalog_raw ۱٬۲۰۰ کمتر از یک ثانیه
members_raw ۳۰۰ آنی
loan_events ۲۶٬۱۱۹ حدودِ یک ثانیه

۱. پرسشی که هر بار ۲۶ هزار سطر را می‌گردد#

SLOW = """
    SELECT c.title, COUNT(e.id) AS loans
    FROM catalog_raw AS c
    LEFT JOIN loan_events AS e ON e.catalog_id = c.id AND e.kind = 'out'
    GROUP BY c.id, c.title
    ORDER BY loans DESC, c.id
    LIMIT 3
"""
print(q(SLOW))
              title  loans
0        پنجره و پل     44
1  در جست‌وجوی چراغ     44
2      چراغ پشت برف     44

جواب درست است و پرسش هم درست نوشته شده. این تجمیع بعد از پیوند است، ولی fan-out خطری ندارد: فقط یک طرفِ پیوند چندتایی است و COUNT(e.id) خودِ همان سطرها را می‌شمارد. LEFT JOIN هم کتابِ بی‌امانت را نگه می‌دارد. حالا زمانش:

print("زمان اجرا:", timed(SLOW), "میلی‌ثانیه")
print()
print(plan(SLOW))
زمان اجرا: 10.42 میلی‌ثانیه
       id  parent  notused                                             detail
0       9       0        0                                             SCAN c
1      13       0        0        BLOOM FILTER ON e (catalog_id=? AND kind=?)
2      26       0        0  SEARCH e USING AUTOMATIC PARTIAL COVERING INDEX
3      74       0        0                       USE TEMP B-TREE FOR ORDER BY

عددِ دقیقِ زمان روی ماشینِ تو فرق می‌کند — به همین دلیل در بلوکِ جدا آمده و ادعای قطعی نیست.

و آن تابعِ plan تابعِ سومِ سلولِ راه‌اندازی است که از ترمِ ۱ آنجا بوده و تا امروز به آن کاری نداشتیم: نقشهٔ اجرا را می‌دهد، یعنی کاری که پایگاه داده واقعاً می‌کند، نه کاری که فکر می‌کنی می‌کند. فعلاً همین‌قدر بخوانش که کلِ جدولِ کتاب‌ها پیمایش می‌شود و برای هر کتاب سراغِ رویدادها می‌رود، و آخرش یک درختِ موقت برای مرتب‌سازی ساخته می‌شود. متنِ این نقشه به نسخهٔ SQLite بند است، پس ادعای قطعی نیست و در بلوکِ جدا آمده؛ ترمِ ۵ فصلِ ۲ کلمه‌به‌کلمه بازش می‌کند.

و مهم‌تر از عدد: این کار با بزرگ شدنِ جدولِ رویدادها بزرگ‌تر می‌شود. امروز ۲۶ هزار سطر است؛ سالِ بعد صدهزار.

۲. ستونِ محاسبه‌شده — و عددی که انتظارش را نداشتیم#

راهِ‌حل ساده است: همان شمارش را یک بار حساب کن و کنارِ خودِ کتاب بگذار.

con.executescript("""
    DROP TABLE IF EXISTS books_hot;
    CREATE TABLE books_hot (
        id         INTEGER PRIMARY KEY,
        title      TEXT    NOT NULL,
        branch     TEXT    NOT NULL,
        loan_count INTEGER NOT NULL
    );
    INSERT INTO books_hot (id, title, branch, loan_count)
    SELECT c.id, c.title, c.branch,
           (SELECT COUNT(*) FROM loan_events AS e
            WHERE e.catalog_id = c.id AND e.kind = 'out')
    FROM catalog_raw AS c;
""")
con.commit()

FAST = """
    SELECT title, loan_count AS loans
    FROM books_hot
    ORDER BY loans DESC, id
    LIMIT 3
"""
print(q(FAST))
print()
print("جمع ستون loan_count      :",
      int(q("SELECT SUM(loan_count) AS s FROM books_hot").iloc[0, 0]))
print("شمارش مستقیم رویدادهای out:",
      int(q("SELECT COUNT(*) AS n FROM loan_events WHERE kind = ?", ("out",)).iloc[0, 0]))
              title  loans
0        پنجره و پل     44
1  در جست‌وجوی چراغ     44
2      چراغ پشت برف     44

جمع ستون loan_count      : 13422
شمارش مستقیم رویدادهای out: 13458

سه سطرِ اول دقیقاً همان سه سطرِ بخشِ ۱ است. ولی دو عددِ پایین یکی نیستند — و اختلافشان ۳۶ است.

سی‌وشش. همان ۳۶ رویدادِ یتیمِ فصلِ ۳. ستونِ loan_count از catalog_raw ساخته شد و آن شش کتاب در catalog_raw وجود ندارند، پس رویدادهایشان هیچ خانه‌ای برای نشستن نداشتند و بی‌صدا افتادند بیرون.

این سومین مسیرِ کاملاً مستقلی است که به همان عدد رسیده‌ایم — یک بار با NOT EXISTS، یک بار با PRAGMA foreign_key_check، و حالا یک بار از راهِ اختلافِ دو جمع. و درسش فراتر از این عدد است: یک جدولِ خلاصه، وضعیتِ خرابیِ امروزِ داده را در خودش منجمد می‌کند. هر کسی که فردا فقط books_hot را بخواند، دیگر هیچ نشانی از آن ۳۶ رویداد نمی‌بیند.

حالا زمانِ خواندن:

print("خواندن از مدل نرمال‌شده:", timed(SLOW), "میلی‌ثانیه")
print("خواندن از ستون آماده  :", timed(FAST), "میلی‌ثانیه")
print()
print(plan(FAST))
خواندن از مدل نرمال‌شده: 10.42 میلی‌ثانیه
خواندن از ستون آماده  : 0.15 میلی‌ثانیه
   id  parent  notused                        detail
0   4       0        0                SCAN books_hot
1  17       0        0  USE TEMP B-TREE FOR ORDER BY

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

✅ چک کن: سه سطرِ اولِ FAST باید دقیقاً همان سه سطرِ SLOW باشند، با همان عددها. اگر نبودند، شرطِ kind = 'out' را در ساختنِ ستون جا انداخته‌ای و داری رویدادها را می‌شماری نه امانت‌ها — همان اشتباهِ فصلِ ۱، این بار منجمدشده در یک ستون.

۳. حالا هزینهٔ نوشتن را بشمار#

ادعای «سریع‌تر شد» بدونِ عددِ طرفِ دیگر نصفِ ادعاست. هر امانتِ تازه حالا باید دو جا ثبت شود.

دو چیزِ تازه در کدِ زیر هست که فعلاً فقط به‌شکلِ مکانیکی به‌کارشان می‌بریم. اولی BEGIN است: یعنی «از اینجا تا commit بعدی، همهٔ نوشتن‌ها یک واحدند». دومی این است که به‌جای timed تابعِ خودمان را نوشتیم، چون timed برای پرسش‌های خواندنی است و ما داریم نوشتن را می‌سنجیم — ولی همان قاعده را نگه داشتیم: میانهٔ چند اجرا، نه یک اجرا. ترمِ ۵ فصلِ ۵ تراکنش را کامل باز می‌کند و فصلِ ۱ همان ترم اندازه‌گیریِ زمان را.

import time

BATCH = [(i % 1200 + 1, 7, "2025-06-30 12:00:00", "out") for i in range(2000)]


def plain():
    con.execute("BEGIN")
    for row in BATCH:
        con.execute("""
            INSERT INTO loan_events (catalog_id, member_id, happened_at, kind)
            VALUES (?, ?, ?, ?)
        """, row)
    con.commit()


def maintained():
    con.execute("BEGIN")
    for row in BATCH:
        con.execute("""
            INSERT INTO loan_events (catalog_id, member_id, happened_at, kind)
            VALUES (?, ?, ?, ?)
        """, row)
        con.execute("UPDATE books_hot SET loan_count = loan_count + 1 WHERE id = ?",
                    (row[0],))
    con.commit()


def median_ms(job, repeat=5):
    times = []
    for _ in range(repeat):
        start = time.perf_counter()
        job()
        times.append((time.perf_counter() - start) * 1000)
    return round(sorted(times)[len(times) // 2], 2)


print("۲۰۰۰ امانت، فقط در دفتر رویداد :", median_ms(plain), "میلی‌ثانیه")
print("۲۰۰۰ امانت، در دفتر و در ستون  :", median_ms(maintained), "میلی‌ثانیه")
۲۰۰۰ امانت، فقط در دفتر رویداد : 8.27 میلی‌ثانیه
۲۰۰۰ امانت، در دفتر و در ستون  : 13.02 میلی‌ثانیه

حدودِ یک‌ونیم برابر کندتر — و این عددِ صادقانه‌ای است که باید همان‌طور گزارش شود، نه بزرگ‌تر و نه کوچک‌تر.

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

ولی این هنوز هزینهٔ اصلی نیست.

۴. هزینهٔ واقعی: ناسازگاری#

جدولِ ما بعد از آزمایشِ بالا به‌هم ریخته — پنج بار plain اجرا شد و ستون را به‌روز نکرد. اول یک بار از نو حسابش می‌کنیم و زمانِ همین ترمیم را هم می‌شماریم، چون آن هم بخشی از هزینه است.

start = time.perf_counter()
con.execute("""
    UPDATE books_hot
    SET loan_count = (SELECT COUNT(*) FROM loan_events AS e
                      WHERE e.catalog_id = books_hot.id AND e.kind = 'out')
""")
con.commit()
repair_ms = round((time.perf_counter() - start) * 1000, 2)

events = int(q("SELECT COUNT(*) AS n FROM loan_events WHERE kind = ?", ("out",)).iloc[0, 0])
column = int(q("SELECT SUM(loan_count) AS s FROM books_hot").iloc[0, 0])
print("رویدادهای out    :", events)
print("جمع ستون خلاصه   :", column)
print("اختلاف            :", events - column, "(همان ۳۶ یتیم)")
رویدادهای out    : 33458
جمع ستون خلاصه   : 33422
اختلاف            : 36 (همان ۳۶ یتیم)

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

con.execute("BEGIN")
for i in range(500):
    con.execute("""
        INSERT INTO loan_events (catalog_id, member_id, happened_at, kind)
        VALUES (?, ?, ?, ?)
    """, (i % 1200 + 1, 9, "2025-06-30 13:00:00", "out"))
con.commit()

drift = q("""
    SELECT COUNT(*) AS wrong_books, SUM(true_count - loan_count) AS total_drift
    FROM (
        SELECT h.id, h.loan_count,
               (SELECT COUNT(*) FROM loan_events AS e
                WHERE e.catalog_id = h.id AND e.kind = 'out') AS true_count
        FROM books_hot AS h
    )
    WHERE true_count <> loan_count
""")
print(drift)
print()
print("خطایی گرفتیم؟ نه. پرسش‌های بالا همه بدون اعتراض اجرا شدند.")
   wrong_books  total_drift
0          500          500

خطایی گرفتیم؟ نه. پرسش‌های بالا همه بدون اعتراض اجرا شدند.

پانصد واحد دروغ، پخش‌شده روی ۵۰۰ کتاب — و هیچ‌کس چیزی نگفت.

این خطا از هر خطای دیگری در این ترم بدتر است، و دلیلش سه چیز است:

  1. پیام ندارد. نه INSERT اعتراض کرد و نه SELECT. گزارشی که از books_hot بخوانی کاملاً سالم به‌نظر می‌رسد.
  2. رشد می‌کند. هر امانتِ بعدیِ آن اسکریپت یک واحد به فاصله اضافه می‌کند. امروز ۵۰۰، ماهِ بعد ۵۰٬۰۰۰.
  3. مقصرش پیدا نیست. آن اسکریپت هیچ کارِ اشتباهی نکرد — سطرش را درست وارد کرد. کاری که نکرد، کاری بود که هیچ‌جا نوشته نشده بود.

و ترمیمش هم رایگان نیست. زمانِ همان UPDATEی که بالای همین بخش زدیم:

print("ترمیم کامل ستون:", repair_ms, "میلی‌ثانیه")
ترمیم کامل ستون: 2085.66 میلی‌ثانیه

دو ثانیه — دویست برابرِ همان پرسشی که غیرنرمال‌سازی می‌خواست سریع‌ترش کند. یعنی ترمیم کاری نیست که بشود در هر درخواست انجامش داد؛ کاری است که شبانه انجام می‌شود، و بینِ دو ترمیم داده‌ات کهنه است.

و درسِ مهندسی‌اش این است: هر جا یک واقعیت را دو جا نگه می‌داری، باید یک راهِ خودکار برای سازگار نگه داشتنشان باشد — یک trigger در خودِ پایگاه داده، یا یک کارِ زمان‌بندی‌شده که هر شب از نو حساب کند. اگر راهش «همه یادشان باشد» است، آن اصلاً راه نیست.

و آزمونِ سازگاری را همیشه بنویس، حتی وقتی مطمئنی. همان پرسشِ بالا — «چند کتاب عددِ غلط دارند؟» — باید هر شب اجرا شود و جوابش صفر باشد. اگر نبود، همان‌جا معلوم می‌شود، نه سه ماه بعد در یک گزارشِ مدیریتی.

۵. TRIGGER: نگهبانی که یادش نمی‌رود#

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

سه راهِ نوشتن داریم و برای هر سه یکی لازم است. داخلِ TRIGGER دو نامِ مخصوص در دسترس‌اند: NEW سطر را بعد از نوشتن نشان می‌دهد و OLD قبلش را. درج فقط NEW دارد، حذف فقط OLD، و به‌روزرسانی هر دو.

اول ستون را از نو حساب می‌کنیم تا از نقطهٔ درستی شروع کنیم، بعد نگهبان‌ها را می‌گذاریم:

con.execute("""
    UPDATE books_hot
    SET loan_count = (SELECT COUNT(*) FROM loan_events AS e
                      WHERE e.catalog_id = books_hot.id AND e.kind = 'out')
""")
con.commit()

con.executescript("""
    DROP TRIGGER IF EXISTS hot_after_insert;
    DROP TRIGGER IF EXISTS hot_after_delete;
    DROP TRIGGER IF EXISTS hot_after_update;

    CREATE TRIGGER hot_after_insert AFTER INSERT ON loan_events
    WHEN NEW.kind = 'out'
    BEGIN
        UPDATE books_hot SET loan_count = loan_count + 1 WHERE id = NEW.catalog_id;
    END;

    CREATE TRIGGER hot_after_delete AFTER DELETE ON loan_events
    WHEN OLD.kind = 'out'
    BEGIN
        UPDATE books_hot SET loan_count = loan_count - 1 WHERE id = OLD.catalog_id;
    END;

    CREATE TRIGGER hot_after_update AFTER UPDATE ON loan_events
    BEGIN
        UPDATE books_hot SET loan_count = loan_count - 1
         WHERE id = OLD.catalog_id AND OLD.kind = 'out';
        UPDATE books_hot SET loan_count = loan_count + 1
         WHERE id = NEW.catalog_id AND NEW.kind = 'out';
    END;
""")

print(q("""
    SELECT name, tbl_name FROM sqlite_master
    WHERE type = 'trigger' ORDER BY name
""").to_string(index=False))
            name    tbl_name
hot_after_delete loan_events
hot_after_insert loan_events
hot_after_update loan_events

سه قاعده، و هر سه به loan_events چسبیده‌اند نه به books_hot — چون کاری که باید کنترل شود، نوشتن در دفترِ رویدادهاست.

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

con.execute("BEGIN")
for i in range(500):
    con.execute("""
        INSERT INTO loan_events (catalog_id, member_id, happened_at, kind)
        VALUES (?, ?, ?, ?)
    """, (i % 1200 + 1, 9, "2025-06-30 14:00:00", "out"))
con.commit()

CONSISTENCY = """
    SELECT COUNT(*) AS wrong_books, SUM(true_count - loan_count) AS total_drift
    FROM (
        SELECT h.id, h.loan_count,
               (SELECT COUNT(*) FROM loan_events AS e
                WHERE e.catalog_id = h.id AND e.kind = 'out') AS true_count
        FROM books_hot AS h
    )
    WHERE true_count <> loan_count
"""
print(q(CONSISTENCY))
   wrong_books total_drift
0            0        None

صفر کتاب، در برابرِ ۵۰۰ کتابِ بخشِ ۴ — با کدِ درجِ کاملاً یکسان. تفاوت در کدِ ما نیست؛ در این است که این بار کسی سرِ جدول ایستاده بود. و آن None یادآوریِ رایگانِ ترمِ ۲ فصلِ ۱ است: SUM روی صفر سطر NULL می‌دهد، نه صفر.

حالا دو راهِ دیگر. یک رویداد را از کتابی به کتابِ دیگر منتقل می‌کنیم و یک رویداد را حذف:

moved_id = int(q("SELECT MAX(id) AS id FROM loan_events").iloc[0, 0])
con.execute("UPDATE loan_events SET catalog_id = ? WHERE id = ?", (7, moved_id))
con.execute("DELETE FROM loan_events WHERE id = ?", (moved_id - 1,))
con.commit()

print(q("SELECT id, loan_count FROM books_hot WHERE id IN (7, 500) ORDER BY id"))
print()
print(q(CONSISTENCY))
    id  loan_count
0    7          23
1  500          30

   wrong_books total_drift
0            0        None

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

و حالا قیمتش. همان تابعِ plain بخشِ ۳ را دو بار می‌سنجیم، یک بار با نگهبان‌ها و یک بار بدونشان. کدِ پایتون در هر دو حالت حرف‌به‌حرف یکی است.

with_trigger = median_ms(plain)

con.executescript("""
    DROP TRIGGER hot_after_insert;
    DROP TRIGGER hot_after_delete;
    DROP TRIGGER hot_after_update;
""")
without_trigger = median_ms(plain)

con.executescript("""
    CREATE TRIGGER hot_after_insert AFTER INSERT ON loan_events
    WHEN NEW.kind = 'out'
    BEGIN
        UPDATE books_hot SET loan_count = loan_count + 1 WHERE id = NEW.catalog_id;
    END;
""")
con.execute("""
    UPDATE books_hot
    SET loan_count = (SELECT COUNT(*) FROM loan_events AS e
                      WHERE e.catalog_id = books_hot.id AND e.kind = 'out')
""")
con.commit()

print("۲۰۰۰ امانت، بدون نگهبان:", without_trigger, "میلی‌ثانیه")
print("۲۰۰۰ امانت، با trigger  :", with_trigger, "میلی‌ثانیه")
۲۰۰۰ امانت، بدون نگهبان: 9.04 میلی‌ثانیه
۲۰۰۰ امانت، با trigger  : 14.0 میلی‌ثانیه

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

⚠️ مواظب باش: TRIGGER فراموش‌شدنی نیست، ولی نامرئی است. کسی که فردا یک INSERT می‌زند و می‌بیند ستونِ جدولِ دیگری هم عوض شد، هیچ ردی از دلیلش در کدِ خودش نمی‌بیند. دو عادت این را کم‌خطر می‌کند: نامِ trigger بگوید چه‌کار می‌کند (hot_after_insert، نه trg1)، و SELECT name, tbl_name FROM sqlite_master WHERE type = 'trigger' جزوِ اولین کارهایت روی هر پایگاه دادهٔ ناآشنا باشد.

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

۶. جدولِ خلاصه: همان معامله، در دانه‌بندیِ درشت‌تر#

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

BY_BRANCH = """
    SELECT c.branch, COUNT(DISTINCT c.id) AS books, COUNT(e.id) AS loans
    FROM catalog_raw AS c
    LEFT JOIN loan_events AS e ON e.catalog_id = c.id AND e.kind = 'out'
    GROUP BY c.branch
    ORDER BY c.branch
"""
con.execute("DROP TABLE IF EXISTS branch_stats")
con.execute("""
    CREATE TABLE branch_stats (
        branch TEXT PRIMARY KEY,
        books  INTEGER NOT NULL,
        loans  INTEGER NOT NULL
    )
""")
con.executemany("INSERT INTO branch_stats VALUES (?, ?, ?)",
                [tuple(r) for r in q(BY_BRANCH).itertuples(index=False)])
con.commit()

print(q("SELECT * FROM branch_stats ORDER BY branch"))
print()
print("جمع ستون books:", int(q("SELECT SUM(books) AS s FROM branch_stats").iloc[0, 0]),
      "| کتاب‌های فهرست: 1200")
print("جمع ستون loans:", int(q("SELECT SUM(loans) AS s FROM branch_stats").iloc[0, 0]),
      "| جمع ستون loan_count:",
      int(q("SELECT SUM(loan_count) AS s FROM books_hot").iloc[0, 0]))
  branch  books  loans
0   بهار    214   9310
1   دانش    187   8657
2  مرکزی    180   9621
3   نسیم    224  11001
4   پویا    195   7591
5   کوشا    200   8241

جمع ستون books: 1200 | کتاب‌های فهرست: 1200
جمع ستون loans: 54421 | جمع ستون loan_count: 54421

شش سطر به‌جای ۱٬۲۰۰، و این بار هر دو شمارشِ کنترلی می‌خوانند.

جمعِ ستونِ books دقیقاً ۱٬۲۰۰ است — هیچ کتابی دو بار شمرده نشده و هیچ‌کدام جا نمانده. و جمعِ ستونِ loans با جمعِ ستونِ loan_count هم برابر است، چون نگهبانِ بخشِ ۵ سرِ جایش نشسته.

حالا پانصد امانتِ دیگر — و همان دو عدد را دوباره می‌گیریم.

con.execute("BEGIN")
for i in range(500):
    con.execute("""
        INSERT INTO loan_events (catalog_id, member_id, happened_at, kind)
        VALUES (?, ?, ?, ?)
    """, (i % 1200 + 1, 11, "2025-06-30 15:00:00", "out"))
con.commit()

snapshot = int(q("SELECT SUM(loans) AS s FROM branch_stats").iloc[0, 0])
column = int(q("SELECT SUM(loan_count) AS s FROM books_hot").iloc[0, 0])
print("جمع جدول خلاصه branch_stats:", snapshot)
print("جمع ستون نگهبان‌دار         :", column)
print("اختلاف                      :", column - snapshot)
جمع جدول خلاصه branch_stats: 54421
جمع ستون نگهبان‌دار         : 54921
اختلاف                      : 500

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

و این خطرِ مخصوصِ جدولِ خلاصه است: یک عکسِ لحظه‌ای که هیچ‌چیزی رویش ننوشته مالِ کدام لحظه است. ستونِ محاسبه‌شده دستِ‌کم کنارِ سطرِ زنده‌اش می‌نشیند و حالا با آن جلو هم می‌آید؛ یک جدولِ خلاصهٔ جدا می‌تواند ماه‌ها کهنه بماند و کسی متوجه نشود. پس هر جدولِ خلاصه یا باید نگهبانِ خودش را داشته باشد، یا دستِ‌کم یک ستونِ «آخرین به‌روزرسانی» — و اینکه آن ستون را چطور ذخیره کنی، موضوعِ کلِ فصلِ ۷ است.

🔧 اگر کار نکرد: در این فصل تراکنش را دستی باز کردیم. اگر یکی از commitها را جا بیندازی و دوباره BEGIN بزنی، SQLite صریح اعتراض می‌کند:

con.commit()
con.execute("BEGIN")
try:
    con.execute("BEGIN")
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
con.rollback()
sqlite3.OperationalError
cannot start a transaction within a transaction

ولی خطای واقعیِ این فصل همان ۵۰۰ بود که هیچ پیامی نداشت.

۷. معیارِ تصمیم: دو عدد، نه سلیقه#

غیرنرمال‌سازی نه گناه است و نه راهِ‌حل. یک معامله است، و معامله بدونِ دو عدد قمار است. فهرستِ کارِ کوتاهِ این فصل:

  1. زمانِ خواندن را قبل و بعد اندازه بگیر. اگر تفاوت در محدودهٔ نوسان بود، همان را بنویس و کار را همین‌جا تمام کن — یک بهینه‌سازیِ بی‌اثر که پیچیدگی اضافه می‌کند، بدترین حالتِ ممکن است.
  2. زمانِ نوشتن را هم اندازه بگیر. اینجا یک‌ونیم برابر شد؛ در جدولی با پنج ستونِ خلاصه می‌تواند خیلی بیشتر شود.
  3. نسبتِ خواندن به نوشتن را بدان. هفتاد برابر سریع‌تر شدنِ کاری که ماهی یک بار انجام می‌شود، ارزشِ آن را ندارد که کاری که ثانیه‌ای یک بار انجام می‌شود یک‌ونیم برابر کند شود.
  4. راهِ خودکارِ سازگار ماندن را از قبل بنویس. بخشِ ۵ ساختش: سه TRIGGER روی هر سه راهِ نوشتن. اگر چنین راهی نداری، معامله را نکن.
  5. آزمونِ سازگاری را بنویس و زمان‌بندی‌اش کن. جوابش باید همیشه صفر باشد.

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

📏 اندازه بگیر: یک سطر یعنی چه؟ در books_hot یک سطر یک کتاب است و ستونش تعدادِ امانت‌هایش؛ در branch_stats یک سطر یک شعبه است. با کدام شمارشِ دوم سنجیدی؟ با سه‌تا: سه سطرِ اولِ پرسشِ سریع در برابرِ پرسشِ کند که یکی درآمدند، جمعِ ستونِ books که ۱٬۲۰۰ شد، و جمعِ ستونِ loans در برابرِ جمعِ loan_count که اول خواند و بعد از پانصد امانتِ تازه ۵۰۰ واحد از آن عقب افتاد. چه کسی از قلم افتاد؟ ۳۶ رویدادِ یتیم که در هیچ‌کدام از دو جدولِ خلاصه نیستند — و از این به بعد هر کسی که فقط این دو جدول را بخواند، حتی نمی‌داند که چیزی از قلم افتاده.

🤖 از دستیارت بپرس: «غیرنرمال‌سازی چیست و چه زمانی توجیه دارد؟» بعد این را بپرس: «اگر یک ستونِ محاسبه‌شده بگذارم، چطور مطمئن شوم هیچ‌وقت با مدلِ اصلی ناسازگار نمی‌شود؟» — جوابِ درست دستِ‌کم یکی از این دو را نام می‌برد: trigger در خودِ پایگاه داده — که بخشِ ۵ ساختش — یا محاسبهٔ دوبارهٔ زمان‌بندی‌شده به‌علاوهٔ یک آزمونِ سازگاری. اگر جواب فقط گفت «حواست باشد به‌روزش کنی»، با بخشِ ۴ ردش کن.

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

کلمه تلفظ به حروف فارسی یعنی چه
denormalization دی‌نرمالایزیشن غیرنرمال‌سازی؛ تکرارِ عمدیِ یک واقعیت برای خواندنِ ارزان‌تر
computed column کامپیوتد کالمن ستونی که مقدارش از جای دیگری حساب شده و ذخیره شده
summary table سامری تیبل جدولِ خلاصه؛ جدولی در دانه‌بندیِ درشت‌تر که از داده‌ی اصلی ساخته می‌شود
read-write trade-off ریدرایت ترید‌آف تبادلِ خواندن و نوشتن
stale data استیل دیتا دادهٔ کهنه؛ نسخه‌ای که دیگر با اصل نمی‌خواند
trigger تریگر قاعده‌ای در پایگاه داده که با هر نوشتن خودکار اجرا می‌شود

تمرین‌ها

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

در فصل بعد#

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

و با سه عیبی شروع می‌کند که ترمِ ۱ در همین داده کاشت و از آن روز تا حالا دست‌نخورده مانده‌اند: ۲۳۱ سطر بدونِ امتیاز، ۳۷ مقدارِ متنی در ستونی که INTEGER اعلام شده، و ۱۹ سطر که نامِ نویسنده‌شان با نویسهٔ عربی وارد شده. برای هر سه یک قید می‌نویسیم و می‌شماریم که هرکدام دقیقاً چند سطر از دادهٔ موجود را رد می‌کند.

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

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