بهینه سازی کوئری

بسم الله الرحمن الرحیم

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

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

برای اینکه بفهمید چطور می توانید یک کد اسکیوال با کارایی مناسب بنویسید نیاز است که درک کنید که کدهای بهینه چطور کار می کنند.

بهینه ساز های کوئری اسکیوال بهینه سازی را بر مبنای هزینه انجام می دهند.

این آنالیز ها یکی از روش های اجرایی انتخاب شده است برای کوئری مشخص، تخمین زدن هزینه هر کدام از این روش ها و انتخاب روشی که کمترین هزینه در بین گزینه های مورد نظر را داشته باشد

درواقع با توجه به اینکه بهینه ساز کوئری نمی تواند هر طرح ممکن را برای هر کوئری در نظر گیرد

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

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

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

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

است به اتمام می رسانیم و نشان می دهیم که چطور Join کردن تیبل ها در یک مرتب اجرایی کارآمدکارایی کوئری ها را افزایش می دهد اما در همان زمان می توان به صورت نمایی برنامه های قابل اجرایی که باید به وسیله بهینه ساز کوئری آنالیز شوند را افزایش داد.

نکته : تمامی مثال ها بر روی نسخه های SQL Server 2008 و SQL Server 2008R2 امتحان شده است و برای اجرای مثال ها از پایگاه داده AdventureWorks پیاده سازی خواهیم کرد.

بهینه ساز کوئری چطور کار می کند

در هسته اسکیوال سرور دو جز اصلی وجود دارد

1- Storage Engine

2- Query Processor

Storage Engine مسئول خواندن اطلاعات بین دیسک و حافظه به شیوه ای که همزمانی به صورت بهینه انجام شود و درحالی که حفظ تمامیت داده ها نیز صورت پذیرد است

Query Processor که موتور رابطه هم نامیده می شود تمامی کوئری های ارسال شده به اسکیوال سرور را دریافت و یک طرح برای اجرای بهینه آنها نهیه می کند و درنتیجه به روش مورد نظر اجرا و نتیجه مورد نیاز بازیابی می شود.

کوئری های ارسال شده به اسکیوال سرور از SQL Language یا همان T-SQL استفاده می کنند (به معنای مایکروسافت اسکیوال سرور به اسکیوال است). از آنجا که SQL یک زبان سطح بالا Declarative Language است به زبان فارسی خودمان در واقع یک زبان سطح بالا اعلانی است، منظور این است که فقط تعیین می کند که چه دیتایی از پایگاه داده دریافت شود نه اینکه چه چیزهایی برای دریافت داده نیاز است چطور و با چه الگوریتمی پردازش درخواست را انجام دهد در یک کلام فقط چه داده ای بازیابی شود همین هیچ کاری به نوع واکشی ندارد در این مبحث نمی خواهیم در مورد آن صحبت کنیم چون خود نیاز به وقت زیادی دارد.

بنابراین برای هر پرس و جویی که دریافت می شود اولین گام Query Processor روشی ایجاد می کند که بیشترین سرعت ممکن را داشته باشد که درواقع بهترین راه ممکن را جهت اجرای پرس و جو گفته شده داشته باشد یا دست کم کارآمد باشد

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

هرکدام از این وظایف به جزئی جدا در Query Processor واگذار شده است.Query Optimizer روش کار را مشخص می کند و بعد آن را به موتور اجرای کوئری ها یا همان Execution Engine انتقال می دهد و در نهایت روش اجرا می شود و خروجی بازیابی می گردد.

 Query Processor برای رسیدن به چیزی که به عوان بهترین روش اجرای کوئری مطرح است، یک سری عملیات جهت انجام فرایند طی می کند که برای درک بهتر تصویری را برای شما قرار داده ام.

توضیحات تصویر

بخش Parsing  و binding

Query در این بخش Pars  و bind شده است. با فرض اسنکه Query ما یک پرس و جو (Query) معتبر است، خروجی این مرحله یک درخت منطقی است، که هر گره (Node)  از این درخت یک عملیات منطقی است که پرس و جو باید این عملیات را انجام دهد، مانند خواندن یک جدول خاص و یا انجام یک inner join. این درخت منطقی پس از این مرحله تقریبا به وسیله دو بخش دیگر برای بهینه سازی Query مورد استفاده قرار می گیرد.
توسط درخت منطقی چندین روش قابل اجرا ایجاد می شود. بهینه ساز پرس و جو چندین روش قابل اجرا برای Query را ابداع می کند. یک Plan قابل اجرا، یک مجموعه از عملیات فیزیکی (مانند جستجو ایندکس، یک حلقه تو در تو join  و یا غیره) می باشد که می تواند به منظور تولید نتیجه مورد نیاز به عنوان درخت منطقی شرح داده شده، انجام شود.
ارزیابی هزینه هر روش (Plan)، در حالی که بهینه ساز پرس و جو هر روش قابل انجام را تولید نمی کند، هزینه زمانی و هزینه استفاده از منابع را برای هر روش قابل استفاده ارزیابی می کند. بهینه ساز پرس و جو روشی را که به عنوان بهترین روش با کمترین هزینه ارزیابی کند، آن را انتخاب می کند. در نهایت روش منتخب به موتور اجرایی انتقال داده می شود.
Query execution، پرس و جو به وسیله این بخش بر اساس روش انتخاب شده اجرا می شود. روش مورد نظر در حافظه در بخش plan cache ذخیره می شود.

Parsing  و  bindingدوعملیات ابتدایی هستند که در هنگام ارسال یک Query به یک نسخه SQL Server اجرا می شوند. Parsing مطمئن می شود که دستور T-SQL ارسالی از لحاظ syntax معتبر باشد. در مرحله بعد SQL query به یک درخت اولیه قابل نمایش ترجمه می شود. به طور خاص یک درخت از عملگرهای منطقی در مرحله ای سطح بالا که مورد نیاز برای اجرا در سوال است، ایجاد شده است. در ابتدا این عملگرهای منطقی به syntax اصلی پرس و جو، مرتبط خواهند شد و عملیات های منطقی ( مانند واکشی اطلاعات از جدول Customer، واکشی اطلاعات از جدول Contact و یا انجام یک inner join و سایر عملیات مشابه) را در بر خواهند گرفت. نمایش درخت های متفاوت از پرس وجو در سراسر فرایند بهینه سازی استفاده خواهد شد و این درخت منطقی نام های متفاوتی را دریافت خواهد کرد تا زمانی که به عنوان آخرین مقداردهی ساختار Memo مورد استفاده قرار می گیرد. در مورد این موضوع بعدا بحث خواهیم کرد.
Binding بیشتر بر روی نام ها دقت دارد. در طول مدت اجرای فرایند binding، SQL Server اطمینان حاصل می کند که تمامی نام های معرفی شد وجود داشته باشند، و نام هر جدول و ستون در درخت Parse (تجزیه) با اشیا مطابق آنها در system catalog متصل می شود. خروجی فرایند دوم که درخت algebrized نامیده می شود و این بخش به Query Optimizer ارسال می شود.
مرحله بعدی در فرایند بهینه سازی، مرحله اصلی فرایند تولید Plane قابل اجرا می باشد که بر اساس Plane های موجود بهترین روش (Plane) بر اساس هزینه های مشخص شده، انتخاب می شود. همانطور که قبلا ذکر شد، SQL Server ازcost-based optimizer (بهینه سازی بر اساس هزینه) استفاده می کند  همچنین از به وسیله یک مدل تعیین هزینه برای مشخص کردن هزینه هر هر روش انتخاب شده استفاده می کند.
در واقع بهینه ساز پرس و جو فرایند mapping دستورات پرس وجو منطقی بیان شده در نمایش درخت دستورات فیزیکی می باشد که می تواند به وسیله موتور اجرا کننده انجام شود. بنابراین این عملا قابلیت موتور اجرایی است که در روش های قابل اجرا توسط Query Optimizer پیاده سازی شده است.این پیاده سازی های موتور اجرایی یکی از الگوریتم های خاص است و این از الگوریتم هایی است که Query Optimizer باید آنها را در هنگام تنطیم کردن روشهای قابل اجرا انتخاب کند. اینکار به وسیله ترجمه کردن عملیات های منطقی اصلی به عملیات های فیزیکی که موتور اجرا کننده قادر به انجام آن است، صورت می گیرد و روش های قابل اجرا دستورات منطقی و فیزیکی را نمایش می دهند. برخی از دستورات منطقی شامل Sort کردن، به دستورات فیزیکی ترجمه می شوند، این درحالی است که سایرد دستورات منطقی ممکن است به چندین دستور فیزیکی map شوند.برای مثال، یک join منطقی می توانید به یک حلقه تو در تور، Merge Join یا دستور فیزیکی Hash Join نگاشت (map) شود.
بنابراین محصول نهایی فرایند query optimization یک روش قابل اجرا است: یک درخت که تعدادی دستور فیزیکی متشکل شده است، که اینها خود شامل الگوریتم هایی برای اجرا شدن توسط موتور اجرایی در خصوص بدست آوردن نتایج مورد نظر از پایگاه داده می باشند.

ادامه دارد

آخرین بروز رسانی 18/08/1393