بهینه سازی پرس و جو ها در SQL Server با Profiler
بهینه ساز پرسوجو چیست؟
بهینهساز پرسوجو از اهمیت زیادی برای پایگاه داده ارتباطی برخوردار است، مخصوصا برای اجرای دستورات پیچیده SQL . یک بهینه ساز پرسوجو بهترین استراتژی بر اجرای هر پرسوجو را تعیین میکند.
بهینهساز پرس و جو به عنوان مثال انتخاب میکند آیا از شاخص برای یک پرسوجو مشخص استفاده کند یا نه، وکدام تکنیک الحاق هنگامی که جداول با هم الحاق میشوند استفاده شود.
این تصمیم تاثیری بسیار زیادی بر روی کارآیی SQL دارد، و بهینهسازی پرسوجو یک تکنولوژی کلیدی بر هر کاربردی است، از سیستمهای قابل استفاده (Operatianal system) تا انبارههای دادهای (Data warehause) و سیستمهای تحلیل (analysis systems) تا سیستمهای مدیریت محتویات (canternt – management) .
بهینهساز پرسوجو برای برنامههای کاربردی و کاربران نهایی کاملا ناپیدا است . از آنجا که برنامههای کاربردی ممکن است هر SQL پیچیدهای راتولید کنند، بهینه سازها پرس و جو باید فوقالعاده سطح بالا و قدرتمند باشد.
برای مطمئن شدن به ایجاد یک کارآیی خوب. برای مثال بهینه سازهای دستورات SQL را تغییر شکل میدهد، به دلیل این که این دستورات میتوانند به معادلهایی تبدیل شوند اما با کارآیی بالاتر.
بهینهسازهای جستجو معمولا بر مبنای هزینه میباشند. در یک استراتژی بهینه سازی بر مبنای هزینه، طرحهای اجرایی چندگانهای برای یک پرس و جو شخص تولید میشود، و آنگاه یک هزینه تخمینی برای هر طرح محاسبه میشود. بهینه ساز پرسوجو طرحی که دارای کمترین هزینة تخمینی است را انتخاب میکند.
بهینهسازی پرس وجو
• بهبود کارآیی پرس وجو به صورت خودکار
• بهبود به معنی تضمین بهینه بودن نیست
مراحل فرآیند بهینه سازی
• انتخاب یک نمایش داخلی (internal representation)
• اعمال تغییرات لازم جهت بهبود کارآیی
• انتخاب رویههای دسترسی سطح پایین به دادهها
• تولید طرحهای اجرایی پرس وجو و تخصیص هزینه به آنها
• انتخاب یک طرح اجرایی با کمترین هزینه
درختهای پرسوجو
نمایش درخت عبارت جبر رابطهای با شرایط:
1. پیمایش میانوندی درخت عبارت اصلی را تولید کند.
2. عملگرهای دوتایی موجود – 0 U,X میباشند.
الگوریتم بهینه سازی پرسوجو
• تجزیه کردن انتخابها به آبشار انتخابها
• انتقال هرانتخاب به پایین ترین سطح ممکن در درخت پرسوجو
• برای هر تصویر ـ آیا این عملگر حذف شود یا این که این عملگر به پایین ترین سطح ممکن در درخت انتقال یابد.
• ترکیب آبشار انتخابها به یک انتخاب منفرد
• ترکیب آبشار تصاویر به یک تصویر منفرد
• انتخاب رویههای سطح پایین
• درخت پرسوجو تبدیل شده یک سری از عملیات سطح پایین را نمایش میدهد بهینهساز یک مجموعه زوال پیادهسازی سطح پایین از پیش تعریف شده بر هر عملگر دارد.
• بهینهساز از اطلاعات کاتالوگ سیستم (شاخصها، کاردینالیتی و غیره) جهت تعیین هزینه هر روال کاندید استفاده میکنند.
• این فرآیند انتخاب مسیر دسترسی نامیده میشود.
• تولید طرحهای پرس و جو و انتخاب یکی از آنها
• بهینه ساز یک مجموعه از طرحهای پرس و جو را به وسیله ترکیب روالهای سطح پایین کاندید تولید میکند.
• چندین تابع اکتشافی (Heurisic) جهت محدود کردن تعداد طرحهای پرسوجوی تولید شده استفاده میشود یک هزینه (از نظر میزان I/O دیسک) به هر طرح اختصاص داده میشود.
• کمهزینهترین طرح انتخاب میشود.
(تخمین هزینه دقیق مشکل است زیرا بعضی از پرس و جوها به تولید نتایج میانی نیاز دارند و اندازه این نتایج وابستگی زیادی به مقادیر دادهها واقعی دارد.)
روشهای بهینهسازی پرسوجو
تبدیل پرسوجو (Transformation Query)
هر گاه یک زبان دستکاری داده (DML) نظیر SQL جهت ارایه یک پرسوجو به سیستم مدیریت پایگاه داده رابطهای (RDBMS) مورد استفاده قرار میگیرد، گامهای فرآیندی مستقلی جهت تبدیل پرسوجو اصلی مورد نیاز است.
هر یک از این گامها باید قبل از این که RDBMS پرسوجو را پردازش کند، انجام شود.
فرآیند تجزیه (The parsing process)
فرآیند تجزیه شامل دو عملکرد زیر است:
1. کنترل کردن پرسوجو ورودی ازنظر نحوی (Syntax)
2. شکستن پرسوجو به قسمتهای مولفهای که میتواند به وسیله RDBMS ارزیابی شود.
قسمتهای مولفهای در یک ساختارداخلی ذخیره میشوند این ساختار میتواند صورت گراف یا معمولا به صورت یک درخت پرسوجو باشد. یک درخت پرسوجو در حقیقت نمایش داخلی قسمتهای مولفهای یک پرسوجو باشد که به راحتی می تواند به وسیله RDBMS دستکاری شود. بعد ازتولید این درخت مرحله فرآیند تجزیه کامل میشود.
فرآیند طبقهبندی (The standardization process)
برخلاف سیستمهای سلسله مراتبی محض (Strictly hierarchical systerm) ، یکی از مزایای بزرگ یک ROBMS توانایی پذیرفتن پرسوجو پویای سطح بالا از کاربر است، در حالی که کاربر هیچ دانشی از بستر ساختار دادهای ندارد.
هدف فرآیند طبقهبندی تبدیل پرسوجو به یک قالب مفید برای بهینهسازی است. فرآیند طبقهبندی مجموعهای از احکام (Rule) را برای دستکاری درخت پرسوجوی تولید شده به وسیله فرآیند تجزیه، به کارمیبرد.
از آنجا که این احکام مستقل از مقادیر دادهها میباشند برای تماس اعمال میتوانند مورد استفاده قرار گیرند. در مدت انجام این فرآیند، RDBMS درخت پرسوجوی را باز چینی میکند به شکلی که طبقهبندی بیشتری شده باشد در بسیاری از موارد، قسمتهای نحوی اضافه به طور کامل حذف می شود.
این طبقهبندی درخت پرسوجوی، ساختاری را تولید میکند که میتواند به وسیله بهینهساز پرسوجوی RDBMS مورد استفاده قرار گیرد.
بهینه ساز پرسوجو (The Query optimizer)
هدف بهینهساز پرسوجوی توید یک طرح اجرایی کارآمد برای پردازش پرسوجوی ارائه شده به وسیله درخت پرسوجوی طبقهبندی شده است.
بنابراین یک بهینهساز میتواند ازنظر تئوری یک طرح اجرایی بهینه را برای هر درخت پرسوجوی پیدا کند، یک بهینه ساز واقعا یک طرح اجرایی کارآمد ومورد قبول را تولید می کند.
هنگامی که یک پرسوجوی پیچده می شود تعداد جداولی که ممکن است لازم باشد الحاق شوند افزایش مییابد.
بدون استفاده از تکنیکهای هرس کردن (pruning) یا روشهای اکتشافی (heuristical) دیگر جهت کاهش تعداد ترکیبات دادهایمورد نیاز، زمان مورد نیاز بهینهساز پرسوجو جهت ارائه یک طرح اجرایی کارآمد برای یک پرسوجوی پیچیده به راحتی میتواند بیشتر از زمان مورد نیاز یک طرح اجرایی با کارآمد کمتر شود.
بهینهسازی اکتشافی (Hevristic Optimization)
بهینهسازی اکتشافی یک روش قانونمند است که میتواند یک طرح اجرایی کارا برای اجرای پرسوجوی را توید کند.
از آنجا که خروجی مرحله طبقهبندی یک صورت یک درخت پرسوجو ارائه میشود، هر نود از این درخت به صورت مستقیم به یک عبارت جبری رابطهای نگاشت میشود.
عملکرد بهینهساز پرسوجوی اکشانی به این صورت است که قوانین جبری رابطهای هم ارز با این درخت عبارت را به کار میبرد و این عبارات را به نمایشی کاراتر تبدیل می کند.
با استفاده از قوانین هم اند جبر رابطهای که اطلاعات غیر ضروری در هنگام تبدیل این درخت حذف میشوند.
گامهای اجرایی در بهینه سازی اکتشافی صورت زیر میباشند:
1ـ شکستن انتخابهای ربطی (Canjuctive seleot) به انتخابهای آبشاری (Cacadin select)
2ـ انتقال انتخابها به پایین درخت پرسوجوی جهت کاهش تعداد تاپلهای (Tuple) خروجی پرسوجوی
3ـ انتقال Proyect به پایین درخت پرسوجوی جهت حذف صفات غیر ضروری
4ـ ترکیب عملگر ضرب کارتزین که به دنبال یک عملگر انتخاب آمده است به یک عملگر الحاق ساده .
هنگامی که این گامها انجام شود، میزان کارآیی یک پرسوجو میتواند به وسیله باز چینی (rearranging) انتخابها (Select) و الحاقهای (Join) باقیمانده افزایش پیدا کند.
به طوری که کمترین سربار را به سیستم تحمیل میکنند بهینهساز اکشانی. بیش از جهت تجزیه پرسوجو کاری انجام نمیدهد.