‫پروژه Profiler برای مدیریت SQL Server

بهینه‌ ساز پرس‌ و جو از  اهمیت زیادی برای پایگاه داده ارتباطی برخوردار است، مخصوصا برای اجرای دستورات پیچیده SQL . یک بهینه ساز پرس‌وجو بهترین استراتژی بر اجرای هر پرس‌وجو را تعیین می‌کند.

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

این تصمیم تاثیری بسیار زیادی بر روی کارآیی SQL دارد، و بهینه‌ سازی پرس‌وجو یک تکنولوژی کلیدی بر هر کاربردی است، از  سیستم‌های قابل استفاده (Operatianal system) تا انباره‌ های داده‌ ای (Data warehause) و سیستم‌ های تحلیل (analysis systems) تا سیستم‌ های مدیریت محتویات  (canternt – management) .

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

برای مطمئن شدن به ایجاد یک کارآیی خوب. برای مثال بهینه سازهای دستورات SQL را تغییر شکل می‌دهد، به دلیل این که این دستورات می‌توانند به معادل‌هایی تبدیل شوند اما با کارآیی بالاتر.

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

بهینه‌سازی پرس وجو

بهبود کارآیی پرس وجو به صورت خودکار

بهبود به معنی تضمین بهینه بودن نیست

مراحل فرآیند بهینه سازی

انتخاب یک نمایش داخلی (internal representation)

اعمال تغییرات لازم جهت بهبود کارآیی

انتخاب رویه‌های دسترسی سطح پایین به داده‌ها

تولید طرحهای اجرایی پرس وجو و تخصیص هزینه به آنها

انتخاب یک طرح اجرایی با کمترین هزینه

درختهای پرس‌وجو

نمایش درخت عبارت جبر رابطه‌ای با شرایط:

پیمایش میانوندی درخت عبارت اصلی را تولید کند.

عملگرهای دوتایی موجود – 0 U,X می‌باشند.

عملگرهای یکتایی موجود  می‌باشند.

همه برگ‌ها دردرخت رابط‌های پایه ای می‌باشند.

تبدیلات (Tranformations)

طراحی دستکاریهای جبری و معنایی جهت دوری از  انجام اعمال هزینه بری باشد.

دستکاریهای جبری

عبارت رابطه‌ای E3,E2,E1 را در نظر بگیرید.

قوانین تبدیل زیر برای حاصلضرب نمایش داده شده‌اند اما می‌توان آنها را جهت انواع دیگری از  عملیات الحاق به کار برد:

1 قانون جابه‌جایی

2 قانون شرکت‌پذیری

3 آبشار تصاویر (Cascade of projection)

4- آبشار انتخاب‌ها (Cascade of selections)

5 تبدیل عملگر انتخاب و عملگر تصویر (project)

اگر شرط F تنها با صفات ضرب درگیر باشد آنگاه

6 تبدیل عملگرهای انتخاب به عملگر ضرب متقابل (Cross product)

اگر شرط F تنها با صفات E1 درگیر باشد آنگاه

اگر F برابر باشد با حاصل   البته به شرط این که F1 به E1 وابسته باشد و F2 به E2 واسته باشد آنگاه

7 تبدیل عملگر انتخاب به عملگر اجتماع (Union)

8 تبدیل عملگر انتخاب به عملگر تفاضل (Difference)

9 تبدیل عملگر تصویر به عملگر ضرب متقابل

10 تبدیل عملگر تصویر به عملگر اجتماع

نکته: عملگر تصویر و عملگر تفاضل دارای خاصیت جابه جایی نیستند.

الگوریتم بهینه سازی پرس‌و‌جو

تجزیه کردن انتخاب‌ها به آبشار انتخاب‌ها

انتقال هرانتخاب به پایین ترین سطح ممکن در درخت پرس‌وجو

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

ترکیب آبشار انتخابها به یک انتخاب منفرد

ترکیب آبشار تصاویر به یک تصویر منفرد

انتخاب رویه‌‌های سطح پایین

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

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

این فرآیند انتخاب مسیر دسترسی نامیده می‌شود.

تولید طرح‌های پرس و جو و انتخاب یکی از  آنها

بهینه ساز یک مجموعه از  طرح‌های پرس و جو را به وسیله ترکیب روال‌های سطح پایین کاندید تولید می‌کند.

چندین تابع اکتشافی (Heurisic) جهت محدود کردن تعداد طرح‌های پرس‌وجوی تولید شده استفاده می‌شود یک هزینه (از  نظر میزان I/O دیسک) به هر طرح اختصاص داده می‌شود.

کم‌هزینه‌ترین طرح انتخاب می‌شود.

(تخمین هزینه دقیق مشکل است زیرا بعضی از  پرس و جوها به تولید نتایج میانی نیاز دارند و اندازه این نتایج وابستگی زیادی به مقادیر داده‌ها واقعی دارد.)

روش‌های بهینه‌سازی پرس‌و‌جو

تبدیل پرس‌و‌جو (Transformation Query)

هر گاه یک زبان دستکاری داده (DML) نظیر SQL جهت ارایه یک پرس‌و‌جو به سیستم مدیریت پایگاه داده رابطه‌ای (RDBMS) مورد استفاده قرار می‌گیرد، گامهای فرآیندی مستقلی جهت تبدیل پرس‌و‌جو اصلی مورد نیاز است.

هر یک از  این گامها باید قبل از  این که RDBMS  پرس‌و‌جو را پردازش کند، انجام شود.

فرآیند تجزیه (The parsing process)

فرآیند تجزیه شامل دو عملکرد زیر است:

کنترل کردن پرس‌و‌جو ورودی ازنظر نحوی (Syntax)

شکستن پرس‌و‌جو به قسمتهای مولفه‌ای که می‌تواند به وسیله 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) باقیمانده افزایش پیدا کند.

به طوری که کمترین سربار را به سیستم تحمیل می‌کنند بهینه‌ساز اکشانی. بیش از  جهت تجزیه پرس‌وجو کاری انجام نمی‌دهد.

بهینه‌سازی نحوی (Syntactical optimizer)

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

بهینه‌ساز سعی در بهبود کارآیی این الحاق‌ها دارد به وسیله شاخص‌هایی (Index) که برای بازیابی داده‌ها نیز هستند.

این نوع از  بهینه‌ساز هنگام دستیابی به داده‌ها در یک محیط نسبتا ایستا می‌تواند کارایی بسیار زیادی داشته باشد. بااستفاده از  بهینه‌ساز نحوی هنگامی رخ می دهد که ساختار زیربنایی داده به صورت خوبی پویا داده‌ها را تغییر می دهند اغلب نیاز پیاده می‌کنند که مجدد کامپایل شوند تا کارایی دسترسی آنها به داده‌ها بیشتر شود. بهینه‌سازی بر مبنای ارزش (Cost – based optimizotion) جهت حل این قبیل مسائل مطرح می شود.

بهینه‌سازی بر مبنای هزینه (Cost – based optimization)

جهت اجرای بهینه‌سازی بر مبنای هزینه، بهینه‌ساز اطلاعات مشخصی در مورد داده‌های ذخیره شده نیاز دارد.

این اطلاعات بسیار زیاد به سیستم وابسته است و می‌تواند شامل اطلاعاتی نظیر اندازه فایل، نوع ساختار فایل ، شاخص‌های اولیه وثانویه (Primany and secondary Index) موجود، و صفات انتخابی (attribute selectivity)  (درصد تابل هایی که انتظار می رود در یکانتخاب برابر بازیابی شوند).

از  آنجا که هدف هر مرحله بهینه‌سازی بازیابی اطلاعات درخواست شده به صورت کالا کارا است، یک بهینه‌ساز بر مبنای ارزش از  دانش خود در مورد داده‌های زیربنایی (underying data) و ساختار ذخیره‌سازی؟

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

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

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

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

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

بهینه‌ ساز معنایی (Semantic optimization)

اگر چه هنوز تکنیک بهینه سازی پیاده‌سازی شده‌ای وجود ندارد اما تحقیقات قابل توجهی درمورد بهینه‌سازی معنایی در حال انجام است. عملکرد بهینه‌ساز معنایی بر مبنای این فرض است که بهینه‌ساز یک درک مقدماتی از  شمای پایگاه داده واقعی دارد.

هنگامی یک پرس‌وجو ارائه می شود، بهینه‌ساز از  دانش خود درمورد محدودیت‌های سیستمی جهت ساده کردن یا صرف نظر کردن از  پرس‌وجو خاصی (البته اگر ضمانت شود که یک مجموعه تهی را بر می گرداند) استفاده می‌کند.