Faragiri

همهٔ زبان‌ها

آموزش SQL پایگاه داده

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

۴بخش
۵دقیقه خواندن
۳عمق

یک زنجیره داروخانه در اهواز با سه شعبه: دویست هزار بیمار، چهارصد هزار نسخه. یک پرسش، دو بار، در سوم سپتامبر ۲۰۲۶:

  • SELECT id, daroo FROM noskhe WHERE bimar_id = ?
  • پیش از هر ایندکسی: ۰٫۰۱۴ ثانیه
  • پس از CREATE INDEX noskhe_bimar ON noskhe(bimar_id): ۰٫۰۰۰۰۲ ثانیه

همان جست‌وجو بدون ایندکس ۰٫۰۱۴ ثانیه و با ایندکس ۰٫۰۰۰۰۲ ثانیه طول کشید، و پایگاه داده در یک سطر می‌گوید چرا. EXPLAIN QUERY PLAN را جلوی پرسش بگذارید و جواب را بخوانید: پیش از ایندکس گفت SCAN noskhe و بعدش گفت SEARCH noskhe USING INDEX. اولی یعنی هر چهارصد هزار سطر خوانده شد تا آن چند سطری که می‌خوردند پیدا شوند. دومی یعنی مستقیم رفت سراغش.

همان یک کلمه به‌دردبخورترین چیز این زبان است برای هرکسی که مانده چرا یک پرسش کند است، چون مستقیم جواب می‌دهد نه با حدس. ساختن ایندکس ۰٫۱۱۶ ثانیه طول کشید و یک بار پرداخت می‌شود، و ایندکس خواندن را سریع‌تر و نوشتن را کندتر می‌کند، چون باید مرتب نگه داشته شود. تمام معامله همین است، و دلیل اینکه یک جدول روی هر ستونی ایندکس نمی‌خورد، در چرا یک پرسش کند است، و ایندکس چه خرجی دارد.

پیوند زدن، و سطری که ناپدید می‌شود

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

چهار پیوند

دو پرسشی که نام هر پیوندی را میگویند

این تصویر دایرههای روی هم نیست. تنها چیزی که این چهارتا را از هم جدا میکند این است که با سطر بیتطبیق چه میکنند.

سطرهای بیتطبیق جدول چپ را بیندازنگهشان دار

سطرهای بیتطبیق جدول راست را بینداز

INNER JOINفقط سطرهایی که تطبیق کردند. بیمار بدون نسخه ناپدید میشود.
  • صفر سطر برای آن بیمار
LEFT JOINهر بیمار، با تهی جایی که نسخهای نبوده.
  • یک سطر برای آن بیمار

نگهشان دار

RIGHT JOINهر نسخه، از جمله آنهایی که بیمارشان پاک شده.
  • قرینه خانه بالایی
FULL OUTER JOINهمه چیز از هر دو طرف، تطبیقشده جایی که ممکن بود.
  • کمیاب، و معمولاً نشانه آشتی دادن دو سامانه

MySQL اصلاً FULL OUTER JOIN ندارد و بهشکل دو پیوند ترکیبشده نوشته میشود.

  • SELECT b.shobe, COUNT(*) FROM noskhe n JOIN bimaran b ON b.id = n.bimar_id WHERE n.tahvil = 0 GROUP BY b.shobe
  • جواب می‌دهد کیانپارس ۶۶۷۹۷، زیتون ۶۶۷۱۰، گلستان ۶۶۴۹۳

کلمه‌ای که همه چیز یک پیوند را تعیین می‌کند این است که وقتی چیزی برای تطبیق نیست کدام سطرها زنده می‌مانند. پیوند درونی سطرهای بی‌تطبیق را می‌اندازد و پیوند چپ نگهشان می‌دارد. بیماری که در شعبه چهارم ثبت شده و هرگز نسخه‌ای نداشته دقیقاً همین را نشان می‌دهد: بیمار بدون نسخه از پیوند درونی صفر سطر داد و از پیوند چپ یک سطر.

این یک جزئیات نیست، رایج‌ترین جواب غلط در گزارش‌گیری است. بیماران هر شعبه را با پیوند درونی بشمارید و هر بیماری که هرگز نسخه‌ای نگرفته بی‌صدا غایب است، پس شعبه‌ای که بیشترین بیمار تازه را دارد کوچک‌ترین به نظر می‌رسد. چهار نوعش و اینکه هرکدام چه نگه می‌دارد ترکیب کردن جدول‌ها، و چهار نوعش است.

جمع کردن سطرها، و اینکه فیلتر کجا می‌رود

GROUP BY سطرها را جمع می‌کند و هر ستون انتخاب‌شده باید یا گروه شده باشد یا تجمیع، و SQLite همان است که اجازه می‌دهد فراموشش کنید، که دانستنش پیش از اجرای پرسشی روی چیزی سخت‌گیرتر می‌ارزد.

ترتیب اجرا

فیلتر پیش از گروه و فیلتر پس از گروه چه فرقی دارند

جابه‌جا گذاشتن WHERE و HAVING به جای خطا عدد غلط برمیگرداند، و COUNT روی ستون با COUNT(*) یک عدد نیست.

  1. فیلتر سطرها با WHEREسطرهای تکی را پیش از دستهبندی خط میزند؛ مثل کنار گذاشتن نسخههای تحویلشده.پیش از گروه
  2. دستهبندی با GROUP BYسطرها را روی کلید گروه میشکند؛ هر ستون دیگر در SELECT باید تجمیع شود.جمع کردن
  3. فیلتر گروهها با HAVINGشعبههایی با کمتر از صد نسخه باز را بعد از شمارش حذف میکند.پس از گروه
  4. شمارش با COUNT(*) در برابر COUNT(ستون)ستاره همه سطرهای گروه را میشمارد، اما نام ستون ردیفهای تهی را جا میاندازد.خروجی

SQLite اجازه میدهد ستون غیرگروهشده را یادتان برود؛ بیشتر موتورهای سختگیرتر همان لحظه خطا میدهند.

دو قاعده دیگر بیشتر اشتباه‌های گزارش‌گیری را پوشش می‌دهند. WHERE سطرها را پیش از گروه شدن فیلتر می‌کند و HAVING گروه‌ها را بعدش، پس کنار گذاشتن نسخه‌های تحویل‌شده یک WHERE است و کنار گذاشتن شعبه‌هایی با کمتر از صد نسخه باز یک HAVING، و گذاشتن هرکدام جای دیگری به‌جای خطا یک عدد غلط می‌دهد. و COUNT ستاره سطرها را می‌شمارد و COUNT روی یک ستون، تهی‌ها را جا می‌اندازد، که تفاوت تعداد نسخه‌ها با تعداد نسخه‌هایی است که تاریخ ثبت‌شده دارند. این‌ها شمردن، جمع زدن، و گروه کردن هستند، روی انتخاب سطرها و ستون‌ها. پرسشی داخل پرسش هم گاهی روشن‌ترین راه نوشتن یکی از این‌هاست و گاهی پیوندی است در لباس مبدل، که یک پرسش داخل یک پرسش، و کی نباید است.

ابزار، و سالی که این حرف درست است

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

اندازهگیریشده

یک جستوجو روی چهارصد هزار نسخه

همان دستور، همان داده، و یک CREATE INDEX میان دو اجرا.

ثانیه برای یک جستوجو، کمتر بهتر است. SQLite در حافظه، روی همان چهارصد هزار نسخه.

  1. بدون ایندکسنقشه میگوید SCAN noskhe، و هر سطر خوانده میشود۰٫۰۱۴ ثانیه
  2. با ایندکس روی bimar_idنقشه میگوید SEARCH noskhe USING INDEX۰٫۰۰۰۰۲ ثانیه

ساختن ایندکس یک بار ۰٫۱۱۶ ثانیه خرج داشت، و هر درج تازه در جدول از این به بعد کمی گرانتر است.

یک فایل SQLite محلی ارزان‌ترین جای تمرین است، رایگان و داخل کتابخانه استاندارد پایتون بدون هیچ نصبی، و همان چهارصد هزار سطر بالا در چند ثانیه داخلش ساخته شد. جای این را گرفت که پیش از نوشتن اولین SELECT به یک سرور در حال اجرا نیاز داشته باشید، که در ایران یعنی این تنها بخش این موضوع است که هیچ نصب و هیچ اتصالی نمی‌خواهد. یک ابزار مهاجرت هم سومی است، رایگان در هر اکوسیستمی، و جای عوض کردن دستی یک جدول روی یک ماشین و یادمان رفتنش روی ماشین دیگر را گرفت؛ جایش اینجاست نه در فهرست استقرار، چون یک پرسش در برابر شکلی نوشته می‌شود که باید همه‌جا یکی باشد، و آن تصمیم درباره جدول‌ها، پیش از پر کردنشان است.

اشتباه: همه چیز را از پایگاه داده خواستن و کار را در کد تمام کردن

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

ضدالگو

وقتی پس از SELECT کار را به حلقه برنامه میسپارید

کشیدن کل جدول روی اتصال و جفت کردن در دیکشنری روی داده آزمایشی بیضرر به نظر میرسد، اما با رشد جدول میشکند.

  • پرسش ساده بدون فیلتر و گروهفقط SELECT نوشته میشود و بقیه کار در زبان برنامهای که از قبل بلدید انجام میشود.
  • حلقه و دیکشنری آشنا در کدروی چند ده رکورد آزمایشی سریع تمام میشود و هیچ خطایی نمیدهد.
آنچه روی داده آزمایشی دیده میشود
  • چهارصد هزار سطر از روی اتصال رد میشوندبه جای انتقال جواب نهایی، کل جدول از شبکه و حافظه عبور میکند.
  • کند شدن خطی با هر سطر تازهزمان اجرای حلقه در برنامه مدام بالا میرود در حالی که نسخه SQL تقریباً تکان نمیخورد.
  • دوبارهسازی کاری که موتور بلداش بودفیلتر، پیوند، گروه و ترتیب بیرون از پایگاه داده و بدون ایندکس تکرار میشوند.

نشانه هشدار حلقهای در برنامه است که بدنهاش فقط چیزها را جمع میزند: پرسش را بفرستید، نه جدول را.

چهارصد هزار سطر که از یک اتصال رد شوند تا در یک حلقه شمرده شوند، از همان شمارشی که همان‌جا که داده هست انجام شود کندتر است، و با هر سطر اضافه کندتر می‌شود در حالی که نسخه SQL تقریباً تکان نمی‌خورد. نشانه‌اش حلقه‌ای در برنامه است که بدنه‌اش فقط چیزها را جمع می‌زند، یا پرسشی بدون هیچ WHEREای روی جدولی که بزرگ می‌شود.

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

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

کنار این

بالای این