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

فصل ۵ از ۸

پیشرفت ترم
۰٪

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

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

و این بار EXPLAIN QUERY PLAN هیچ کمکی نمی‌کند: نقشهٔ اجرای یک INSERT کاملاً خالی است. پس این فصل نشان می‌دهد وقتی ذره‌بینِ دوره ساکت است، سراغِ چه چیزِ دیگری باید رفت.

دو مسیرِ کالسکه‌ای که یکی بعد از هر جعبه دروازه‌ای را می‌بندد و باز می‌کند و دیگری همهٔ جعبه‌ها را می‌برد و یک بار دروازه را می‌بندد

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

  • با UPDATE و DELETE بنویسی و عددِ سطرهای تغییرکرده را بخوانی
  • با BEGIN و ROLLBACK نشان بدهی «همه یا هیچ» یعنی چه
  • تفاوتِ هزار commit با یک تراکنش را با دو عدد نشان بدهی
  • PRAGMA journal_mode را عوض کنی و اثرش را اندازه بگیری
  • خطای database is locked را بسازی، بخوانی، و رفعش کنی

قبل از شروع#

از ترمِ ۱: INSERT و executemany و پارامترِ ?. از ترمِ ۴: commit بعد از هر نوشتن، و اینکه executescript تراکنشِ باز را قطعی می‌کند.

دانه‌بندی: یک سطر از events یک رویداد. جدولِ queue را همین فصل می‌سازد و یک سطرش یک یادداشتِ ساختگی است.

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

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

۱. UPDATE، و عددی که برمی‌گرداند#

هر UPDATE و DELETE یک عددِ مهم برمی‌گرداند که خیلی‌ها نادیده‌اش می‌گیرند: چند سطر واقعاً عوض شد. این عدد در cursor.rowcount است و اولین شمارشِ کنترلیِ هر نوشتنی همین است.

print("جمع days در ابتدا:", int(q("SELECT SUM(days) AS s FROM events").iloc[0, 0]))

cursor = con.execute("UPDATE events SET days = days + 1 WHERE kind = ?", ("in",))
con.commit()
print("سطرهایی که عوض شدند:", cursor.rowcount)
print("جمع days بعد از UPDATE:", int(q("SELECT SUM(days) AS s FROM events").iloc[0, 0]))

back = con.execute("UPDATE events SET days = days - 1 WHERE kind = ?", ("in",))
con.commit()
print("سطرهایی که برگشتند:", back.rowcount)
print("جمع days دوباره:", int(q("SELECT SUM(days) AS s FROM events").iloc[0, 0]))
جمع days در ابتدا: 8200275
سطرهایی که عوض شدند: 192169
جمع days بعد از UPDATE: 8392444
سطرهایی که برگشتند: 192169
جمع days دوباره: 8200275

۸٬۳۹۲٬۴۴۴ منهای ۸٬۲۰۰٬۲۷۵ می‌شود دقیقاً ۱۹۲٬۱۶۹ — یعنی همان تعداد سطری که rowcount گفت، هرکدام یک واحد. دو عدد که مستقل از هم به‌دست آمدند و همدیگر را تأیید کردند.

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

show_plan("UPDATE events SET days = days + 1 WHERE kind = ?", ("in",))
 id  parent      detail
  4       0 SCAN events

✅ چک کن: «جمع days دوباره» باید دقیقاً برابرِ «جمع days در ابتدا» باشد. اگر نبود، یکی از دو UPDATE سطرهای متفاوتی گرفته — و آن‌وقت داده‌ات را خراب کرده‌ای بدونِ اینکه خطایی گرفته باشی.

۲. BEGIN و ROLLBACK: «همه یا هیچ» یعنی چه#

یک تراکنش یعنی چند تغییر که یا همه‌شان اتفاق می‌افتند یا هیچ‌کدام. اینجا با DELETE امتحانش می‌کنیم، چون خطرناک‌ترین دستورِ ممکن بهترین جا برای دیدنِ این خاصیت است:

con.execute("BEGIN")
removed = con.execute("DELETE FROM events WHERE kind = ?", ("in",))
print("سطرهایی که DELETE برداشت:", removed.rowcount)
print("شمارش داخلِ تراکنش      :", con.execute("SELECT COUNT(*) FROM events").fetchone()[0])

con.rollback()
print("شمارش بعد از ROLLBACK   :", con.execute("SELECT COUNT(*) FROM events").fetchone()[0])
سطرهایی که DELETE برداشت: 192169
شمارش داخلِ تراکنش      : 207831
شمارش بعد از ROLLBACK   : 400000

داخلِ تراکنش، جدول واقعاً ۲۰۷٬۸۳۱ سطر داشت. بعد از ROLLBACK، انگار هیچ‌وقت چیزی حذف نشده بود.

سه کلمه که کلِ ماجرا هستند:

  1. BEGIN — از اینجا به بعد، تغییرها موقتی‌اند.
  2. COMMIT — همه‌شان با هم قطعی می‌شوند.
  3. ROLLBACK — همه‌شان با هم دور ریخته می‌شوند.

و این خاصیت فقط برای امنیت نیست؛ کلیدِ سرعت هم هست — که بخشِ بعد نشانش می‌دهد.

۳. چرا هزار commit گران است#

جدولِ queue را می‌سازیم و هزار سطر را دو جور می‌نویسیم: یک بار با یک commit بعد از هر سطر، و یک بار با یک تراکنش برای همه.

con.execute("CREATE TABLE queue (id INTEGER PRIMARY KEY, body TEXT NOT NULL)")
con.commit()
rows = [(number, f"یادداشت {number}") for number in range(1, 1001)]

start = time.perf_counter()
for row in rows:
    con.execute("INSERT INTO queue VALUES (?, ?)", row)
    con.commit()
one_by_one_ms = (time.perf_counter() - start) * 1000
print("سطرهای نوشته‌شده، راه اول:", int(q("SELECT COUNT(*) AS n FROM queue").iloc[0, 0]))

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

start = time.perf_counter()
con.executemany("INSERT INTO queue VALUES (?, ?)", rows)
con.commit()
one_transaction_ms = (time.perf_counter() - start) * 1000
print("سطرهای نوشته‌شده، راه دوم:", int(q("SELECT COUNT(*) AS n FROM queue").iloc[0, 0]))
print("تعداد commit در راه اول :", len(rows))
print("تعداد commit در راه دوم :", 1)
سطرهای نوشته‌شده، راه اول: 1000
سطرهای نوشته‌شده، راه دوم: 1000
تعداد commit در راه اول : 1000
تعداد commit در راه دوم : 1
print("هزار commit :", round(one_by_one_ms, 1), "میلی‌ثانیه")
print("یک تراکنش   :", round(one_transaction_ms, 2), "میلی‌ثانیه")
print("نسبت        :", round(one_by_one_ms / one_transaction_ms), "برابر")
هزار commit : 3131.8 میلی‌ثانیه
یک تراکنش   : 4.57 میلی‌ثانیه
نسبت        : 685 برابر

همان هزار سطر، صدها برابر اختلاف.

حالا ذره‌بینِ دوره را روی همان INSERT می‌گیریم:

steps = plan("INSERT INTO queue VALUES (?, ?)", (2000, "آزمایش"))
print("تعداد سطرهای نقشهٔ اجرای این INSERT:", len(steps))
تعداد سطرهای نقشهٔ اجرای این INSERT: 0

نقشه خالی است، و این خودش جواب است.

EXPLAIN QUERY PLAN می‌گوید پایگاه داده چطور سطرها را پیدا می‌کند. این INSERT با VALUES چیزی را پیدا نمی‌کند، پس نقشه‌ای هم ندارد. هزینه جای دیگری است: هر commit باید مطمئن شود که تغییر روی دیسک نشسته، و این یعنی صبر کردن برای سخت‌افزار. هزار بار صبر کردن، هزار برابرِ یک بار صبر کردن است — مهم نیست هر بار یک سطر بود یا هزار سطر.

و قاعده‌ای که از این درمی‌آید تا آخرِ عمرِ کاری‌ات به دردت می‌خورد: commit را از داخلِ حلقه بیاور بیرون.

۴. PRAGMA journal_mode: همان کار، با دفترِ ثبتِ دیگر#

SQLite برای اینکه بتواند ROLLBACK کند، قبل از هر تغییر نسخهٔ قدیمی را جایی می‌نویسد. حالتِ پیش‌فرض یک فایلِ جانبی می‌سازد و بعد از هر commit پاکش می‌کند. حالتِ WAL به‌جایش تغییرها را به یک فایلِ پیوسته اضافه می‌کند و آن رفت‌وبرگشت را حذف می‌کند.

con.execute("DELETE FROM queue")
con.commit()
print("حالت فعلی:", con.execute("PRAGMA journal_mode").fetchone()[0])
print("حالت جدید:", con.execute("PRAGMA journal_mode = WAL").fetchone()[0])

start = time.perf_counter()
for row in rows:
    con.execute("INSERT INTO queue VALUES (?, ?)", row)
    con.commit()
wal_ms = (time.perf_counter() - start) * 1000

print("سطرهای نوشته‌شده:", int(q("SELECT COUNT(*) AS n FROM queue").iloc[0, 0]))
con.execute("DELETE FROM queue")
con.commit()
print("برگشت به حالت اول:", con.execute("PRAGMA journal_mode = DELETE").fetchone()[0])
حالت فعلی: delete
حالت جدید: wal
سطرهای نوشته‌شده: 1000
برگشت به حالت اول: delete
print("هزار commit در حالت پیش‌فرض:", round(one_by_one_ms, 1), "میلی‌ثانیه")
print("هزار commit در حالت WAL   :", round(wal_ms, 1), "میلی‌ثانیه")
print("نسبت                      :", round(one_by_one_ms / wal_ms, 1), "برابر")
هزار commit در حالت پیش‌فرض: 3131.8 میلی‌ثانیه
هزار commit در حالت WAL   : 694.6 میلی‌ثانیه
نسبت                      : 4.5 برابر

همان هزار commit، چند برابر ارزان‌تر — ولی هنوز بیش از صد برابر گران‌تر از یک تراکنش.

این ترتیب مهم است و اغلب برعکسش را می‌شنوی: اول commitها را کم کن، بعد سراغِ تنظیمات برو. WAL یک بهبودِ واقعی است، ولی جای طراحیِ درست را نمی‌گیرد.

۵. یک نویسنده، و خطایی که از آن می‌آید#

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

🔧 اگر کار نکرد: این همان خطایی است که در هر برنامهٔ واقعی با SQLite بالاخره یک بار می‌بینی:

import sqlite3

other = sqlite3.connect(DB_PATH, timeout=0.5)

con.execute("BEGIN IMMEDIATE")
con.execute("INSERT INTO queue VALUES (?, ?)", (1, "از اتصال اول"))

try:
    other.execute("INSERT INTO queue VALUES (?, ?)", (2, "از اتصال دوم"))
    other.commit()
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
    other.rollback()

print("خواندن از اتصال دوم، همان لحظه:",
      other.execute("SELECT COUNT(*) FROM events").fetchone()[0])
sqlite3.OperationalError
database is locked
خواندن از اتصال دوم، همان لحظه: 400000

نوشتن رد شد، خواندن کار کرد. این دقیقاً همان قانون است: یک نویسنده، هر تعداد خواننده.

و حالا رفعش، که چهار تکه دارد — و تکهٔ اولش همان other.rollback() بالاست:

con.commit()
other.execute("INSERT INTO queue VALUES (?, ?)", (2, "از اتصال دوم"))
other.commit()
print("سطرهای queue بعد از هر دو نوشتن:",
      int(q("SELECT COUNT(*) AS n FROM queue").iloc[0, 0]))
other.close()
سطرهای queue بعد از هر دو نوشتن: 2
  1. بعد از خطا، تراکنشِ ناتمام را ببند. اتصالِ دوم موقعِ تلاش برای نوشتن یک تراکنش باز کرد و آن تراکنش با شکستِ دستور بسته نشد. این تراکنش تا وقتی فقط باز است مزاحمِ کسی نیست، ولی اگر اتصالِ دوم در همان حال چیزی بخواند — مثلِ همان SELECT COUNT(*) بالا، اگر rollback را جا بیندازی — قفلِ خواندن را نگه می‌دارد و حتی commit اتصالِ اول هم شکست می‌خورد. یعنی خطا از قربانی به مقصر سرایت می‌کند.
  2. تراکنش را باز نگه ندار. آن BEGIN IMMEDIATE تا لحظهٔ commit قفل را در دست داشت؛ هر ثانیه‌ای که یک تراکنش باز می‌ماند، ثانیه‌ای است که بقیه منتظرند.
  3. به اتصالت مهلت بده. ما عمداً timeout=0.5 گذاشتیم تا خطا را ببینی؛ مقدارِ پیش‌فرض چند ثانیه است و بیشترِ برخوردهای کوتاه را خودش حل می‌کند.
  4. در حالتِ WAL خواننده‌ها اصلاً منتظرِ نویسنده نمی‌مانند — ولی دو نویسندهٔ هم‌زمان همچنان همان خطا را می‌گیرند.

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

۶. جدولِ قبل و بعدِ این فصل#

کاری که کردیم قبل بعد ابزارِ تشخیص
هزار commit ← یک تراکنش ~۳٬۱۰۰ میلی‌ثانیه ~۵ میلی‌ثانیه شمارشِ commitها (نقشهٔ اجرا خالی است)
هزار commit ← هزار commit در WAL ~۳٬۱۰۰ میلی‌ثانیه ~۷۰۰ میلی‌ثانیه PRAGMA journal_mode

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

📏 اندازه بگیر: یک سطر یعنی چه؟ در events یک رویداد، در queue یک یادداشتِ ساختگی. با کدام شمارشِ دوم سنجیدی؟ با سه‌تا: اختلافِ جمعِ days دقیقاً برابرِ rowcount شد (۱۹۲٬۱۶۹)؛ شمارشِ events بعد از ROLLBACK به ۴۰۰٬۰۰۰ برگشت؛ و هر دو راهِ نوشتن دقیقاً ۱٬۰۰۰ سطر ساختند. چه کسی از قلم افتاد؟ در بخشِ ۵، سطری که اتصالِ دوم می‌خواست بنویسد — و اگر برنامه‌ات آن خطا را بگیرد و بی‌صدا رد شود، آن سطر برای همیشه گم می‌شود بدونِ اینکه کسی بفهمد.

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

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

کلمه تلفظ به حروف فارسی یعنی چه
transaction ترنزاکشن تراکنش؛ چند تغییر که یا همه اتفاق می‌افتند یا هیچ‌کدام
atomicity اتمیسیتی اتمی بودن؛ همان «همه یا هیچ»
rowcount رو‌کانت تعدادِ سطرهایی که آخرین دستور واقعاً عوض کرد
journal ژورنال دفترِ ثبتِ تغییرها، که ROLLBACK را ممکن می‌کند
WAL وال حالتی که تغییرها را به یک فایلِ پیوسته اضافه می‌کند
lock لاک قفل؛ حقِ انحصاریِ نوشتن که در هر لحظه دستِ یک اتصال است

تمرین‌ها

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

در فصل بعد#

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

چهار بازنویسی را امتحان می‌کنیم و نتیجه‌ها با هم فرق دارند. یکی پرسش را نزدیک به دو برابر سریع می‌کند و بعد، به‌محضِ ساختنِ یک index، تبدیل به کندترِ دو تا می‌شود؛ دو تا هیچ فرقی نمی‌کنند و همان را هم گزارش می‌کنیم؛ و یکی جوابِ غلط می‌دهد بدونِ اینکه خطایی بگیرد — پرسشی که جوابش صفر است در حالی که جوابِ درست ۹٬۱۶۰ است.

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

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