وبلاگ

در اینجا یک سوال رایج وجود دارد: یافتن پزشکان فعال در سال گذشته؟ فرض کنید یک جدول “پزشکان” وجود دارد که اطلاعات پزشکان را ثبت می کند و یک جدول “پذیرش بیماران” که موارد بیماران بستری شده توسط پزشکان را ثبت می کند. هدف فیلتر کردن آن دسته از پزشکانی است که در سال گذشته حداقل یک بار ملاقات با بیمار داشته‌اند (این می‌تواند یک دوره زمانی پویا در خطوط لوله یادگیری ماشین یا داشبوردهای تعاملی باشد).

در عمل، سه راه متداول برای نوشتن این کوئری وجود دارد: EXIST، IN، و JOIN. ما آنها را تجزیه و تحلیل خواهیم کرد و آزمایش هایی را با Bigquery و PostgreSQL انجام خواهیم داد تا تحلیل خود را تأیید کنیم.

رویکرد اول: IN

برای کاربران پایتون، عملگر IN ممکن است بصری ترین رویکرد باشد. این شامل ابتدا فیلتر کردن سوابق پذیرش در سال گذشته و سپس بررسی اینکه آیا پزشکان در آن سوابق ذکر شده اند یا خیر. ما همچنین آزمایش خواهیم کرد که آیا افزودن DISTINCT باعث افزایش عملکرد می شود یا خیر.

SELECT d.*
FROM `tool-for-analyst.richard_workspace.doctors` d
WHERE d.doctor_id IN (
SELECT doctor_id
FROM `tool-for-analyst.richard_workspace.patient_admissions` admissions
WHERE admissions.Admission_Date BETWEEN '2023–01–01' AND '2023–12–31'
);

رویکرد دوم وجود دارد:

روش دیگر شامل استفاده از عملگر EXISTS است که نتایج را فیلتر می کند تا فقط آنهایی را که پرس و جوی فرعی حداقل یک رکورد برای آنها برمی گرداند را شامل شود. EXISTS بر اساس مفهوم “Semi JOIN” عمل می کند، به این معنی که در واقع یک اتصال سمت راست را انجام نمی دهد. در عوض، آن را به سادگی بررسی می کند که آیا پیوستن نتایج را برای یک تاپل معین برمی گرداند یا خیر. وقتی یکی را پیدا کرد متوقف می شود. این می تواند برخی از مزایای عملکرد را ارائه دهد.

SELECT d.*
FROM `tool-for-analyst.richard_workspace.doctors` d
WHERE EXISTS (
SELECT 1
FROM `tool-for-analyst.richard_workspace.patient_admissions` pa
WHERE pa.doctor_id = d.doctor_id
AND pa.Admission_Date BETWEEN '2023–01–01' AND '2023–12–31'
)

رویکرد سوم:

رویکرد سوم شامل استفاده از JOIN است که کلاسیک ترین روش در فلسفه پایگاه داده رابطه ای است. بحث‌های مکرری در انجمن‌ها درباره زمان فیلتر کردن و استفاده از زیرپرس و جو یا عبارت جدول رایج (CTE) وجود دارد. ما این ملاحظات را در آزمایش خود نیز لحاظ کردیم.

پس از فیلتر در subquery بپیوندید

SELECT d.doctor_id, name, Hospital, Age, Gender
FROM `tool-for-analyst.richard_workspace.doctors` d
INNER JOIN (
SELECT DISTINCT doctor_id
FROM `tool-for-analyst.richard_workspace.patient_admissions`
WHERE Admission_Date BETWEEN '2023–01–01' AND '2023–12–31'
) admissions 
ON d.doctor_id = admissions.doctor_id;

پس از JOIN فیلتر و گروه بندی کنید

SELECT d.doctor_id, d.name, d.Hospital, d.Age, d.Gender
FROM `tool-for-analyst.richard_workspace.doctors` d
INNER JOIN `tool-for-analyst.richard_workspace.patient_admissions` pa
ON d.doctor_id = pa.doctor_id
WHERE pa.Admission_Date BETWEEN '2023–01–01' AND '2023–12–31'
GROUP BY d.doctor_id, d.name, d.Hospital, d.Age, d.Gender;

فیلتر CTE قبل از JOIN

WITH filtered_admissions AS(
SELECT DISTINCT doctor_id
FROM `tool-for-analyst.richard_workspace.patient_admissions` admissions
WHERE admissions.Admission_Date 
BETWEEN '2023–01–01' AND '2023–12–31'
)
SELECT d.*
FROM `tool-for-analyst.richard_workspace.doctors` d
JOIN filtered_admissions
ON d.doctor_id = filtered_admissions.doctor_id;

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

Q1: IN
Q2: IN با DISTINCT در پرس و جو فرعی
Q3: وجود دارد
Q4: با یک پرسش فرعی که محدوده زمانی را فیلتر می کند، بپیوندید
Q5: قبل از هر فیلتری JOIN کنید، سپس از GROUP BY برای حذف مجدد استفاده کنید
Q6: یک JOIN با CTE که محدوده زمانی را فیلتر می کند

ما هر پرس و جو را 10 بار بر روی یک مجموعه داده آزمایشی ایجاد شده اجرا کردیم و محدوده زمانی را 1 روز برای هر آزمایش تغییر دادیم. با استفاده از جزئیات اجرای BigQuery و دستور EXPLAIN ANALYZE در PostgreSQL، اطلاعات دقیقی در مورد زمان اجرا و برنامه ها به دست آوردیم. نتایج آزمایش مشخص است. اگر این یک مورد استفاده در دنیای واقعی است، می‌توانیم کارآمدترین گزینه را انتخاب کرده و ادامه دهیم. در این وبلاگ، با این حال، ما قصد داریم کمی عمیق تر بکنیم و بپرسیم: چرا؟

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

سوال بزرگ:

برنامه های اجرایی برای Q1 “IN” و Q3 “EXISTS” دقیقاً یکسان هستند. اجرای دو مرحله ای ابتدا در پرس و جوی فرعی فیلتر می شود، سپس از یک SEMI JOIN برای شناسایی پزشکان با حداقل یک قرار ملاقات با بیمار استفاده می کند. این یک مثال کامل از آنچه قبلاً ذکر کردیم بود: SQL یک زبان اعلامی است که آنچه شما نیاز دارید را توصیف می کند و BigQuery نحوه اجرای آن را مشخص می کند. حتی اگر منطق SQL در رویکرد خود به مشکل متفاوت بود، BigQuery فهمید که آنها به همان نتیجه نیاز دارند و تصمیم گرفت از همان رویکرد اجرایی برای بهینه سازی آنها استفاده کند.

افزودن DISTINCT به پرس و جو فرعی IN منجر به عملکرد بسیار بدتری شد. مشاهده این نکته بسیار جالب بود که افزودن یک DISTINCT می‌تواند تأثیر مهمی بر سرعت اجرای پرس‌وجو داشته باشد. وقتی به طرح اجرای پرس و جو نگاه کردیم، می‌توانیم ببینیم که یک DISTINCT باعث دو مرحله اضافی در اجرای پرس و جو می‌شود. این باعث شد که جداول موقت بیشتری در فرآیند نگهداری شوند و در نتیجه زمان اجرای به طور قابل توجهی کندتر شود.

در بین سه روش JOIN، تعجب آور بود که Q5 ‘JOIN قبل از فیلتر’ بهترین عملکرد را نشان داد، در حالی که دو رویکرد دیگر که سعی در بهینه سازی فیلتر و دنباله JOIN داشتند، Q4 ‘JOIN with subquery’ و Q6 ‘JOIN with CTE’ انجام دادند. بهره وری ضعیف با نگاهی به زمان‌بندی، به نظر می‌رسد که BigQuery در واقع تشخیص داده است که انجام فیلتر قبل از JOIN می‌تواند عملکرد را بهینه کند. با این حال، زمانی که سعی کردیم با وادار کردن فیلتر قبل از JOIN ظاهر شود، دنباله را به صورت دستی کنترل کنیم، این منجر به مراحل بیشتر در برنامه اجرا و زمان اجرای به طور قابل توجهی کندتر شد. جالب اینجاست که رویکردهای subquery و CTE دقیقاً همان طرح اجرایی را داشتند که همچنین بسیار شبیه به طرح Q2 “IN with DISTINCT” است. تنها تفاوتش این بود که در مرحله آخر از JOIN داخلی به جای SEMI JOIN استفاده کرد.

PostgreSQL:

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

تجزیه و تحلیل ما بر اساس نتایج EXPLAIN ANALYSE است. این ابزار برای درک ویژگی های عملکرد پرس و جو PostgreSQL بسیار ارزشمند است. ‘EXPLAIN’ طرح اجرایی را ارائه می دهد که زمانبندی پرس و جو PostgreSQL برای یک عبارت داده شده ایجاد می کند، در حالی که گزینه ‘ANALYZE’ در واقع عبارت را اجرا می کند و امکان ارزیابی عملکرد دقیق تری را فراهم می کند.

Q1 ‘IN’ و Q3 ‘EXISTS’ برنامه اجرایی مشابهی با کمترین هزینه داشتند. مانند BigQuery، PostgreSQL نیز تشخیص داد که این دو کوئری به داده های یکسانی نیاز دارند و برای آنها بهینه شده است.

Q2، Q4 و Q6 دقیقاً همان برنامه عملکردی با برچسب قیمت کمی بالاتر دارند. اگرچه پرس‌و‌جوها از نظر منطق یا نحو متفاوت هستند، زمان‌بند Postgres تصمیم می‌گیرد همان اجرا را انجام دهد: Filter -> Group by (DISTINCT) -> JOIN,

Q5 “پیوستن قبل از فیلتر” بالاترین طرح اجرای هزینه را داشت. اگرچه زمان‌بندی PostgreSQL هنوز هم می‌توانست فیلتر پیش از JOIN را اعمال کند، فرآیند deduplication در جدول بزرگ‌تر اعمال شد که منجر به هزینه بالاتر شد.

در آزمایش ما، رویکردهایی مانند اجبار کردن فیلتر قبل از JOIN یا افزودن گزینه DISTINCT به عملگر IN، عملکرد پرس و جو ما را افزایش نداد. در عوض آنها آن را کندتر کردند. با مقایسه BigQuery با Postgres، واضح است که هر کدام نقاط قوت و موقعیت خاص خود را دارند. برنامه ریزان آنها نیز با استفاده از رویکردهای مختلف برای اهداف مختلف بهینه شده اند.

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

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