آیا آماده هستید تا عمیقاً در دنیای پایگاه داده ها غوطه ور شوید و عملکرد درونی پردازش پرس و جو را کشف کنید؟ بیایید به دنیای پایگاه‌های داده سر بزنیم و دنیای هیجان‌انگیز برآورد هزینه را در جستارهای تک جدولی کشف کنیم!
در این پست وبلاگ، نحوه عملکرد بهینه سازی پرس و جو PostgreSQL بر اساس هزینه را بررسی خواهیم کرد. اما نگران نباشید، ما مفاهیم پیچیده پایگاه داده را با استفاده از قیاس های سرگرم کننده تجزیه می کنیم. هزینه را به عنوان برچسب قیمت یک محصول در نظر بگیرید، این یک شاخص مطلق عملکرد نیست، بلکه ابزاری برای مقایسه ارزش نسبی عملیات است.
هزینه ها با توابع تعریف شده در costize.c تخمین زده می شوند و تمام عملیات اجرا شده توسط مجری دارای توابع هزینه مربوطه هستند. به عنوان مثال، هزینه های اسکن های متوالی و اسکن های شاخص به ترتیب توسط cost_seqscan() و cost_index() برآورد می شوند. در PostgreSQL سه نوع هزینه وجود دارد: راه اندازی، اجرا و کل، که هزینه کل مجموع هزینه های راه اندازی و اجرا است.
هزینه راه اندازی هزینه ای است که قبل از واکشی اولین تاپل صرف شده است. آن را مانند هزینه لباس پوشیدن و آماده شدن قبل از ترک خانه خود تصور کنید. از سوی دیگر، هزینه اجرا، هزینه واکشی تمام تاپل ها یا کار واقعی انجام شده برای رسیدن به نتیجه است. این مانند انرژی است که پس از آماده شدن برای سفر به مقصدی صرف می کنید.
دستور EXPLAIN هر دو هزینه راه اندازی و کل هزینه ها را در هر عملیات نشان می دهد. مثلا وقتی می دوید EXPLAIN SELECT * FROM tbl;، بخش هزینه دو مقدار 0.00 و 145.00 را نمایش می دهد. در اینجا هزینه راه اندازی و کل هزینه ها به ترتیب 0.00 و 145.00 است.
اکنون، بیایید نحوه تخمین عملیات اسکن متوالی، اسکن فهرست و مرتب‌سازی را با جزئیات بررسی کنیم. ما از یک جدول خاص و یک شاخص برای نشان دادن نحوه عملکرد این عملیات استفاده خواهیم کرد.
به عنوان مثال، هزینه اسکن متوالی توسط تابع cost_seqscan() تخمین زده می شود. بیایید نحوه تخمین هزینه اسکن متوالی درخواست زیر را بررسی کنیم: SELECT * FROM tbl WHERE id < 8000;.
در اسکن متوالی، هزینه راه اندازی برابر با 0 است و هزینه اجرا با معادله تعریف می شود:
هزینه اجرا = (cpu_tuple_cost + cpu_operator_cost) × Ntuple + seq_page_cost × Npage,
جایی که seq_page_cost، cpu_tuple_cost، و cpu_operator_cost در تنظیم شده اند postgresql.conf فایل، و مقادیر پیش فرض به ترتیب 1.0، 0.01 و 0.0025 هستند. Ntuple و Npage به ترتیب تعداد تاپل ها و صفحات جدول را نشان می دهد.
ما می توانیم مقادیر را بدست آوریم Ntuple و Npage با استفاده از پرس و جو زیر: SELECT relpages, reltuples FROM pg_class WHERE relname="tbl";. برای جدول مثال ما، خروجی این است:
| تکرارها | دوباره چند نفره |
|---|---|
| 45 | 10000 |
بنابراین هزینه اجرا را می توان به صورت زیر محاسبه کرد:
هزینه اجرا = (0.01 + 0.0025) × 10000 + 1.0 × 45 = 170.0.
و هزینه کل مجموع هزینه های راه اندازی و اجرا است:
هزینه کل = 0.0 + 170.0 = 170.
اگر شما اجرا کنید EXPLAIN دستور برای کوئری بالا، خروجی زیر را خواهید دید:
QUERY PLAN
Seq Scan در tbl (هزینه = 0.00.. 170.00 ردیف = 8000 عرض = 8)
فیلتر: (id < 8000)
(2 ردیف)
حالا بیایید به تخمین هزینه اسکن شاخص برویم. اسکن فهرست کارآمدتر از اسکن متوالی است زیرا به جای اسکن کل جدول، فقط به صفحات داده مورد نیاز دسترسی دارد. هزینه اسکن شاخص توسط تابع cost_index() تخمین زده می شود. بیایید از همان جدول استفاده کنیم tbl و یک شاخص در id ستونی برای نشان دادن چگونگی برآورد هزینه اسکن شاخص برای پرس و جو SELECT * FROM tbl WHERE id < 8000;.
هزینه شروع یک اسکن شاخص با هزینه اسکن شاخص برای یافتن نقطه شروع تعریف می شود. این هزینه با هزینه دسترسی به یک صفحه فهرست واحد تخمین زده می شود که توسط آن تعیین می شود random_page_cost تنظیم در postgresql.conf فایل. هزینه اجرای اسکن نمایه، هزینه واکشی تمام تاپل های منطبق است، که مشابه هزینه اسکن متوالی است، اما در ضریب انتخاب شاخص ضرب می شود، که کسری از ردیف های جدول است که شرایط پرس و جو را برآورده می کند. .
ضریب انتخاب یک شاخص توسط برنامه ریز بر اساس آمار جمع آوری شده توسط سازمان برآورد می شود ANALYZE فرمان برای پرس و جوی مثال ما، بیایید فرض کنیم که ضریب انتخاب 0.8 است، به این معنی که 80٪ از ردیف ها شرط را برآورده می کنند.
هزینه اسکن شاخص را می توان به صورت زیر محاسبه کرد:
هزینه راه اندازی = random_page_cost × 1 = 4.0 (با فرض تصادفی_page_cost = 4.0)
هزینه اجرا = (cpu_tuple_cost + cpu_operator_cost) × Ntuple × انتخابی + index_page_cost × Nindex
= (0.01 + 0.0025) × 10000 × 0.8 + 0.2 × 45
= 82.0
هزینه کل = هزینه راه اندازی + هزینه اجرا = 86.0
اگر اجرا کنید EXPLAIN دستور برای کوئری بالا، خروجی زیر را خواهید دید:
QUERY PLAN
اسکن فهرست با استفاده از idx_tbl_id در tbl (هزینه=4.00..86.00 ردیف=8000 عرض=8)
وضعیت شاخص: (id < 8000)
(2 ردیف)
می بینید که هزینه اسکن شاخص کمتر از اسکن متوالی است، که نشان می دهد اسکن شاخص برای این پرس و جو خاص کارآمدتر است.
در نهایت، اجازه دهید به طور خلاصه در مورد چگونگی برآورد هزینه عملیات مرتب سازی بحث کنیم. مرتب سازی یک عملیات گران قیمت است که به CPU و منابع I/O زیادی نیاز دارد. هزینه عملیات مرتب سازی توسط تابع cost_sort() تخمین زده می شود که اندازه داده هایی که باید مرتب شوند، حافظه موجود و هزینه نوشتن و خواندن داده ها به دیسک و از دیسک را در نظر می گیرد.
در نتیجه، برآورد هزینه عملیات پرس و جو یک جنبه حیاتی از بهینه سازی پرس و جو است. درک چگونگی برآورد هزینه ها می تواند به شما در نوشتن پرس و جوهای کارآمدتر و بهبود عملکرد پایگاه داده کمک کند. امیدواریم این پست وبلاگ به شما درک بهتری از نحوه عملکرد بهینه ساز مبتنی بر هزینه PostgreSQL داده باشد. پرس و جو مبارک!
