درک مفهوم join ها در SQL Server

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

برای تعریف queryهای مربوط به ارتباط میان جداول و درک این ارتباط ها از عملگر های منطقی کمک میگیریم.(گام اول شناخت نیازمندی های موجود می باشد.)

SQL این عملگر های منطقی را با سه عملگر فیزیکی متفاوت پیاده سازی می کنیم که از آنها برای اجرای عملیاتی که توسط عملگر های منطقی انجام می شود،استفاده می نماییم.(گام دوم چگونگی برطرف کردن این نیازها می باشد.)  اگرچه ما انواع  مختلف از عملگر های منطقی برای بیان  این join های منطقی  داریم ،تنها با سه نوع عملگر joinفیزیکی میتوان آن هارا پیاده سازی نمود.

join از نوع حلقه های تو در تو(Nested loops join)

قبل ار این که وارد جزییات بیستری شویم  این نوع از join را تعریف می کنیم.در واقع nested loops join یک ساختار منطقی که در آن هر حلقه یا iteration در داخل iteration دیگری قرار داد که به ازای هر بار فراخوانی iteration بیرونی تمامی iteration های داخلی  اجرا  می شوند. Nested loops join هم به این شکل کار می کند.یکی از جداول به عنوان جدول بیرونی و جدول دیگر به عنوان جدول درونی در نظر گرفته می شود. به هزای هر سطر از جدول بیرونی تمامی سطر های جدول دورنی بررسی می شود و در صورت منطبق بودن این سطرها با سطرجدو ل بیرونی ، خروجی اضافه می شوند در غیر این صورت در نظر گرفته نمی شوند.به همین ترتیب چنین مراحلی برای سطرهای بعدی جدول بیرونی انجام می گیرد.

SQL Server optimizer از این نوع join در مواقعی استفاده می نماید که یکی از جداول join جدول کوچکی باشد وبه عنوان جدول بیرونی در نظر گرفته می شود و جدول دیگر این join   جدول بزرگتر است که به عنوان جداول درونی که شامل ستونی از join که حاوی index می باشد و به کمترین ورودی و خروجی و مقایسه نیازمند است.

Optimizer برای استفاده از این join  که شامل سه نوع زیر می باشد، کمک می گیرد:

  • Naive nested loops join یا  به عبارتی join ازنوع حلقه های وتودرتو ساده : در این مدل جدول و یا index به طور کامل بررسی می شود.
  • Index nested loops join یا join از نوع حلقه های تودرتو حاوی index: در این مورد فرآیند جستجو و بررسی با استفاده از این  index ها انجام می گیرد.
  • Temporary index nested loops join به عبارتی join از نوع حلقه های تودرتو با index موقت: یک index به طور موقت برای یک query تعریف می شود و پس از اجرای کامل query از بین می رود.

Join با حلقه های تو درتو حاوی شاخص (index) در صورتی که حاوی مجموعه ی کوچکی از سطر ها باشد، عملکرد بهتری نسبت به نوع ترکیبی و مخلوط آن دارد. از این رو هنگامی که تعداد سطرها زیاد باشد انتخاب بهینه ای نیست. هم چنین این join همه ی انواع join را به غیر از پیوند خارجی راست و کامل (right and full outer join)، نیم پیوند راست (right semi-join)، پیوند کامل راست (right anti-semi join) را شامل می شود.

در مثال زیر می خواهیم دو جدول order, order details را با هم دیگر join بزنیم و تنها مشتریانی با id=10 را در خروجی داشته باشیم.۱۲ سطر به عنوان خروجی برگردانده می شود که به دلیل اینکه تعداد سطر ها کم است جدول order به عنوان جدول بیرونی (outer table) در نظر گرفته می شود. در شکل زیر در قسمت بالایی execution plan آن را مشاهده می نماییم. به ازای هر سطر از این ۱۲ سطر جدول بیرونی تمای سطرهای جدول درونی باید به لحاظ تطبیق بررسی شوند. در واقع هر سطر از جدول داخلی ۱۲ بار و در هر بار با کمک این index جستجو و یا پارامتر مرتبط با جدول بیرونی جستجو می شود. همان طور که در تصویر زیر می بینید، ۳۱۲ سطر به عنوان خروجی بر گردانده می شود. در query ما از set statistics profile on استفاده می نماییم تا اطلاعات مربوط به اجرای این query را در کنار خروجی داشته باشیم.

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

 join ترکیبی (Merge join)

اولین نکته ای که باید در رابطه با ترکیب جدول در نظر بگیریم، این است که جدول ادغام شده نیازمند هر دو ورودی برای ذخیره سازی در ستون های جدول ترکیبی می باشد ویا هر دو جدول وروردی باید حاوی clustered index بر روی ستون مربوط به join بوده و هم چنین حداقل به یک معادل سازی احتیاج داریم.

چون تمامی سطرها از پیش ذخیره شده اند، از این رو merge join خیلی سریع فرآیند تطبیق را شروع میکند. یک سطر از جدول اول را می خواند و با یک سطر از جدول دوم مقایسه می نماید. اگر با هم منطبق بودند، این سطر در خروجی آورده می شود و این روال به همین شکل برای همه ی سطر ها تکرار می شود تا زمانی که تمامی سطر ها بررسی شوند.

در واقع merge join برای جداول ورودی بزرگتر که از پیش ذخیره شوند و یا حاویindex از پیش تعیین شده باشندعملکرد بهتری دارد و هزینه ای برار با مجموع سطرها های هر دو جدول ورودی دارد این در حالی است که join از نوع حلقه های تو در تو به شکل حاصل ضرب سطرهای هر دو جدول می باشد. هنگامی که optimizer بخواهد برای جداول ذخیره نشده از چنین راهکاری استفاده کند، می تواند از یک عملگر فیزیکی مرتب سازی صریح استفاده کند تا نقش index را برای آن ایفا نماید با این تفاوت که با سرعتی آهسته تر از index اجرا می شود.

در اینجا هم می توان از مثال بالا با کمی تغغیر استفاده کرد. با کمک where می خواهیم تمام مشتریان بزرگتر از ۸۵ را بیابیم. در اینجا به دلیل زیاد بودن تعداد سطر ها و اینکه از پیش ذخیره شده اند merge join را به کار می بریم. توجه کنیم که هر دو جدول ورودی تنها یک بار جستجو می شوند در حالی که در حلقه های تو در تو ۱۲ بار این اتفاق می افتاد.

هنگامی که داده ی ذخیره شده از طریق index ها به دست آید،merge join بسیار مطلوب و سریع است. هم چنین انواع join ها را نیز شامل می شود البته تا زمانی که حداقل یک شرط تساوی join بر قرار باشد این اتفاق می افتد. هم چنین می توانیم چندین عبارت تساوی برای این join داشته باشیم که باید تمامی این شرط ها بر قرار باشند و ترتیب مشخص خود را داشته باشند.

پیوند مخلوطی (Hash join)

زمانی که جداول ورودی کاملا بزرگ باشند و شاخص کافی برای آنها وجود نداشته باشد از hash join یا پیوند مخلوط استفاده می نماییم که در ۲ فاز مطرح می شود: فاز ساخت و فاز جستجو بنابراین این join شامل ۲ ورودی ساختن وجستجو می باشد. داده های کوچکتر به عنوان ورودی اول و یا ورودی ساختن در نظر گرفته می شوند تا حافظه مورد نیاز برای ذخیره ی جدول حاصل از مخلوط این دو جدول به کمترین مقدار خود برسد و واضح است که ورودی دوم به عنوان ورودی جستجو در نظر گرفته می شود.

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

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

تابع hash مربوطه به مقدار مشخصی از حافظه برای تولید جدول خروجی و داده های این جدول نیازمند است. اگر به لحاظ حافظه محدودیت داشته باشیم. می توانیم قسمت هایی از این جدول خروجی را با tempdb جایگزین نماییم. درصورتی که بخواهیم بهتریتن عملکرد را داشته باشیم می توانیم از hash join به صورت موازی استاده کنیم نا نتیجه ای بهتر از دو روش قبل داشته باشیم.

در ادمه سه نوع از این پیوند را می بینیم:

  • join مخلوطی با حافظه: در این مدل فضای کافی برای ذخیری سازی داریم
  • Grace join : در این مدل با محدودیت حافظه مواجه هستیم و نیاز داریم تا برخی از قسمت های جدول را با tempdb تعویض نماییم.
  • join مخلوطی بازگشتی: در این مدل به دلیل بزرگ بودن جدول خروجی باید از چندین مرحله merge join  استفاده نماییم.

در مثال زیر دو جدول بزرگ بدون index داریم که می‌خواهیم با کمک hash join خروجی را به دست آوریم که بر خلاف حلقه های تو در تو نیاز به بررسی چند باره ی ورودی ها نداریم.

حال که با این نوع از join ها آشنا شدیم می توانیم در شرایط مختلف آن ها را به کار گیریم. البته این به این معنا نیست که همواره با دستور option می توانیم SQL Server را مجبور کنیم تا از نوع خاصی از join ها استفاده نماید. راهکار بهتر می تواند بازنویسی و یا بروز رسانی query مربوطه باشد.

0 پاسخ

دیدگاه خود را ثبت کنید

تمایل دارید در گفتگوها شرکت کنید؟
در گفتگو ها شرکت کنید.

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *