یک زنجیره داروخانه در اهواز با سه شعبه: دویست هزار بیمار، چهارصد هزار نسخه. یک پرسش، دو بار، در سوم سپتامبر ۲۰۲۶:
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. اولی یعنی هر چهارصد هزار سطر خوانده شد تا آن چند سطری که میخوردند پیدا شوند. دومی یعنی مستقیم رفت سراغش.
همان یک کلمه بهدردبخورترین چیز این زبان است برای هرکسی که مانده چرا یک پرسش کند است، چون مستقیم جواب میدهد نه با حدس. ساختن ایندکس ۰٫۱۱۶ ثانیه طول کشید و یک بار پرداخت میشود، و ایندکس خواندن را سریعتر و نوشتن را کندتر میکند، چون باید مرتب نگه داشته شود. تمام معامله همین است، و دلیل اینکه یک جدول روی هر ستونی ایندکس نمیخورد، در چرا یک پرسش کند است، و ایندکس چه خرجی دارد.
پیوند زدن، و سطری که ناپدید میشود
نسخهها در یک جدولاند و بیماران در یکی دیگر، و پرسشی درباره هر دو باید روی ستون مشترکشان پیوندشان بزند. شمردن نسخههای تحویلنشده بهازای هر شعبه یک دستور است:
چهار پیوند
دو پرسشی که نام هر پیوندی را میگویند
این تصویر دایرههای روی هم نیست. تنها چیزی که این چهارتا را از هم جدا میکند این است که با سطر بیتطبیق چه میکنند.
سطرهای بیتطبیق جدول راست را بینداز
- صفر سطر برای آن بیمار
- یک سطر برای آن بیمار
نگهشان دار
- قرینه خانه بالایی
- کمیاب، و معمولاً نشانه آشتی دادن دو سامانه
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(*) یک عدد نیست.
- فیلتر سطرها با WHEREسطرهای تکی را پیش از دستهبندی خط میزند؛ مثل کنار گذاشتن نسخههای تحویلشده.پیش از گروه
- دستهبندی با GROUP BYسطرها را روی کلید گروه میشکند؛ هر ستون دیگر در SELECT باید تجمیع شود.جمع کردن
- فیلتر گروهها با HAVINGشعبههایی با کمتر از صد نسخه باز را بعد از شمارش حذف میکند.پس از گروه
- شمارش با COUNT(*) در برابر COUNT(ستون)ستاره همه سطرهای گروه را میشمارد، اما نام ستون ردیفهای تهی را جا میاندازد.خروجی
SQLite اجازه میدهد ستون غیرگروهشده را یادتان برود؛ بیشتر موتورهای سختگیرتر همان لحظه خطا میدهند.
دو قاعده دیگر بیشتر اشتباههای گزارشگیری را پوشش میدهند. WHERE سطرها را پیش از گروه شدن فیلتر میکند و HAVING گروهها را بعدش، پس کنار گذاشتن نسخههای تحویلشده یک WHERE است و کنار گذاشتن شعبههایی با کمتر از صد نسخه باز یک HAVING، و گذاشتن هرکدام جای دیگری بهجای خطا یک عدد غلط میدهد. و COUNT ستاره سطرها را میشمارد و COUNT روی یک ستون، تهیها را جا میاندازد، که تفاوت تعداد نسخهها با تعداد نسخههایی است که تاریخ ثبتشده دارند. اینها شمردن، جمع زدن، و گروه کردن هستند، روی انتخاب سطرها و ستونها. پرسشی داخل پرسش هم گاهی روشنترین راه نوشتن یکی از اینهاست و گاهی پیوندی است در لباس مبدل، که یک پرسش داخل یک پرسش، و کی نباید است.
ابزار، و سالی که این حرف درست است
EXPLAIN، با هر املایی که موتور شما دارد، ابزار این صفحه است. رایگان است، از قبل آنجاست، و جای حدس زدن اینکه کدام بخش پرسش کند بوده را گرفت، که آدمها بهطور مداوم در آن بدند: بخش کند تقریباً هیچوقت بخشی نیست که پیچیده به نظر میرسد.
یک فایل SQLite محلی ارزانترین جای تمرین است، رایگان و داخل کتابخانه استاندارد پایتون بدون هیچ نصبی، و همان چهارصد هزار سطر بالا در چند ثانیه داخلش ساخته شد. جای این را گرفت که پیش از نوشتن اولین SELECT به یک سرور در حال اجرا نیاز داشته باشید، که در ایران یعنی این تنها بخش این موضوع است که هیچ نصب و هیچ اتصالی نمیخواهد. یک ابزار مهاجرت هم سومی است، رایگان در هر اکوسیستمی، و جای عوض کردن دستی یک جدول روی یک ماشین و یادمان رفتنش روی ماشین دیگر را گرفت؛ جایش اینجاست نه در فهرست استقرار، چون یک پرسش در برابر شکلی نوشته میشود که باید همهجا یکی باشد، و آن تصمیم درباره جدولها، پیش از پر کردنشان است.
اشتباه: همه چیز را از پایگاه داده خواستن و کار را در کد تمام کردن
گیر، نحو نیست. این است که آدمها SELECT را یاد میگیرند، میایستند، و باقی را با زبانی که از قبل بلدند انجام میدهند: نسخهها را بگیر، بیماران را بگیر، در یک حلقه جفتشان کن، در یک دیکشنری بشمارشان. روی داده آزمایشی کار میکند و دقیقاً شکل اشتباهی است.
ضدالگو
وقتی پس از SELECT کار را به حلقه برنامه میسپارید
کشیدن کل جدول روی اتصال و جفت کردن در دیکشنری روی داده آزمایشی بیضرر به نظر میرسد، اما با رشد جدول میشکند.
- پرسش ساده بدون فیلتر و گروهفقط SELECT نوشته میشود و بقیه کار در زبان برنامهای که از قبل بلدید انجام میشود.
- حلقه و دیکشنری آشنا در کدروی چند ده رکورد آزمایشی سریع تمام میشود و هیچ خطایی نمیدهد.
- چهارصد هزار سطر از روی اتصال رد میشوندبه جای انتقال جواب نهایی، کل جدول از شبکه و حافظه عبور میکند.
- کند شدن خطی با هر سطر تازهزمان اجرای حلقه در برنامه مدام بالا میرود در حالی که نسخه SQL تقریباً تکان نمیخورد.
- دوبارهسازی کاری که موتور بلداش بودفیلتر، پیوند، گروه و ترتیب بیرون از پایگاه داده و بدون ایندکس تکرار میشوند.
نشانه هشدار حلقهای در برنامه است که بدنهاش فقط چیزها را جمع میزند: پرسش را بفرستید، نه جدول را.
چهارصد هزار سطر که از یک اتصال رد شوند تا در یک حلقه شمرده شوند، از همان شمارشی که همانجا که داده هست انجام شود کندتر است، و با هر سطر اضافه کندتر میشود در حالی که نسخه SQL تقریباً تکان نمیخورد. نشانهاش حلقهای در برنامه است که بدنهاش فقط چیزها را جمع میزند، یا پرسشی بدون هیچ WHEREای روی جدولی که بزرگ میشود.
قاعدهای که درستش میکند: پرسش را بفرستید، نه داده را. فیلتر و پیوند و گروه و ترتیب همه مال خود دستورند، و جوابی که برمیگردد باید به اندازه جواب باشد نه به اندازه جدول.
چیزی که این صفحه به آن دست نزد این است که چرا نسخهها و بیماران اصلاً در دو جدولاند، که تصمیمی است پیش از همه اینها و بعدش سخت برگشتپذیر است، زیر طراحی پایگاه داده. موتورها هم دقیقاً در جاهایی فرق میکنند که بعداً گاز میگیرد، در موتورهای پایگاه داده، و آن یک کاری که هرگز نباید با یک رشته پرسش کرد ساختنش با چسباندن متن است، که امنیت وب است. همه اینها زیر پایگاه داده نشستهاند.