a

ABLY مقالات و مطالب مجموعه

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

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

 

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

 

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

 

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

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

 

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

 

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

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

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

 

 

 

 

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

 

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

 

برای دوستان عزیز که در تیم طراحی پایگاه داده 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 برای رسیدن به چیزی که به عوان بهترین روش اجرای کوئری مطرح است، یک سری عملیات جهت انجام فرایند طی می کند که برای درک بهتر تصویری را برای شما قرار داده ام.

 

 

 

SQL - Query Processing

 

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


بخش 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

نظرات

  • Hannah Martinez
    asadi1873
    دو شنبه 11 دی 1278 0:00

    سلام آقای شیدایی من اسدی هستم.

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

  • Hannah Martinez
    Rosariarosariaseptimus@gmail.com
    شنبه 21 فروردین 1400 14:49

    Hi there! This is kind of off topic but I need some advice from an established blog. Is it very hard to set up your own blog? I'm not very techincal but I can figure things out pretty quick. I'm thinking about making my own but I'm not sure where to start. Do you have any tips or suggestions? With thanks 0mniartist asmr https://0mniartist.tumblr.com

  • Hannah Martinez
    Margaritamargaritagage@yahoo.com
    شنبه 21 فروردین 1400 19:32

    Hello there! This post couldn't be written any better! Reading through this post reminds me of my good old room mate! He always kept talking about this. I will forward this article to him. Fairly certain he will have a good read. Thanks for sharing! asmr https://0mniartist.tumblr.com 0mniartist

  • Hannah Martinez
    Yukikoyukikoruyle@inbox.com
    یک شنبه 22 فروردین 1400 5:09

    Hey there! This is my 1st comment here so I just wanted to give a quick shout out and tell you I genuinely enjoy reading through your blog posts. Can you suggest any other blogs/websites/forums that go over the same topics? Thanks! 0mniartist asmr https://0mniartist.tumblr.com

  • Hannah Martinez
    Darreldarrel_plante@gmail.com
    یک شنبه 22 فروردین 1400 19:20

    Hi there, after reading this awesome piece of writing i am as well glad to share my experience here with friends. asmr https://0mniartist.tumblr.com 0mniartist

  • Hannah Martinez
    Malindamalindasmart@web.de
    دو شنبه 23 فروردین 1400 0:17

    If some one needs to be updated with most up-to-date technologies then he must be pay a visit this web page and be up to date daily. asmr 0mniartist

  • Hannah Martinez
    Noranoradiehl@gmail.com
    چهار شنبه 25 فروردین 1400 20:31

    Please let me know if you're looking for a article author for your weblog. You have some really good posts and I feel I would be a good asset. If you ever want to take some of the load off, I'd absolutely love to write some material for your blog in exchange for a link back to mine. Please blast me an e-mail if interested. Regards! 0mniartist asmr (bit.ly)

  • Hannah Martinez
    Lavernelaverne_elia@gmail.com
    پنج شنبه 26 فروردین 1400 10:35

    This is the perfect site for anybody who wishes to find out about this topic. You realize so much its almost hard to argue with you (not that I personally would want to…HaHa). You definitely put a brand new spin on a topic that has been discussed for ages. Wonderful stuff, just excellent! 0mniartist asmr (http://j.mp/3g9BKdG)

  • Hannah Martinez
    Veronicaveronicalynton@zoho.com
    پنج شنبه 26 فروردین 1400 16:56

    I am now not sure where you are getting your information, however great topic. I needs to spend some time studying much more or understanding more. Thanks for excellent information I used to be searching for this info for my mission. asmr (j.mp) 0mniartist

  • Hannah Martinez
    Bennybenny.shumate@freenet.de
    جمعه 27 فروردین 1400 1:03

    Magnificent beat ! I wish to apprentice at the same time as you amend your web site, how can i subscribe for a weblog web site? The account aided me a acceptable deal. I were tiny bit acquainted of this your broadcast provided brilliant clear concept asmr (http://j.mp/3to3ZZU) 0mniartist

  • Hannah Martinez
    Raymondraymondgrover@postinbox.com
    جمعه 27 فروردین 1400 2:50

    This is a topic that is close to my heart... Many thanks! Where are your contact details though? asmr (bit.ly) 0mniartist

نظرات یا سوالات خودرا با ما درمیان بگذارید