بررسی عملگر merge join
ابتدا در management studio از منوی Query، گزینهی Include actual execution plan را انتخاب میکنیم. سپس کوئریهای زیر را اجرا میکنیم:
USE [WideWorldImporters]; GO SET STATISTICS IO ON; GO SELECT [p].[PurchaseOrderID], [pl].[PurchaseOrderLineID] FROM [Purchasing].[PurchaseOrders] [p] JOIN [Purchasing].[PurchaseOrderLines] [pl] ON [p].[PurchaseOrderID] = [pl].[PurchaseOrderID]; GO
در اینجا یک merge join انجام شده، چون اطلاعات رسیدهی به آن، از پیش مرتب شدهاست. از این جهت که جدول PurchaseOrders دارای یک clustered index تعریف شدهی بر روی PurchaseOrderID است:
ALTER TABLE [Purchasing].[PurchaseOrders] ADD CONSTRAINT [PK_Purchasing_PurchaseOrders] PRIMARY KEY CLUSTERED ( [PurchaseOrderID] ASC )
CREATE NONCLUSTERED INDEX [FK_Purchasing_PurchaseOrderLines_PurchaseOrderID] ON [Purchasing].[PurchaseOrderLines] ( [PurchaseOrderID] ASC )
اما بهینه سازی کوئریهای SQL Server، همیشه در یک چنین شرایطی، از merge join استفاده نمیکند. برای مثال کوئری زیر نیز دقیقا از لحاظ تعریف ایندکس بر روی OrderID، وضعیت مشابهی با کوئری قبلی دارد:
SELECT [o].[OrderID], [ol].[OrderLineID] FROM [Sales].[Orders] [o] JOIN [Sales].[OrderLines] [ol] ON [o].[OrderID] = [ol].[OrderID]; GO
اگر به میزان ضخامت پیکانهای این پلن، با پلن قبلی دقت کنید، مشاهده میکنید که ضخامت آنها در اینجا افزایش یافتهاست. این افزایش ضخامت پیکانها، بیانگر افزایش میزان اطلاعات ارسالی به قسمتهای مختلف است (حدود 231 هزار ردیف) به همراه اسکن بالایی بر روی ایندکس [FK_Sales_Orders_SalespersonPersonID] است (بر روی PersonID بجای OrderID) و دومی بر روی [NCCX_Sales_OrderLines]. چون ایندکس OrderID سنگین است و تعداد ردیف زیادی را شامل میشود، بهینه ساز ترجیح دادهاست تا از ایندکس دیگری استفاده کند که I/O کمتری را به همراه دارد. در اینحالت دیگر merger join میسر نبوده و از hash match استفاده کردهاست.
اگر OrderID انتخاب شده را از جدول OrderLines تهیه کنیم، چه اتفاقی رخ میدهد؟ (در کوئری قبلی، OrderID از جدول Orders انتخاب شده بود)
SELECT [ol].[OrderID], [ol].[OrderLineID] FROM [Sales].[Orders] [o] JOIN [Sales].[OrderLines] [ol] ON [o].[OrderID] = [ol].[OrderID];
یک بازنویسی ساده و دریافت دو ستون از یک جدول سبب شدهاست تا بهینه سازی کوئری، join تشکیل شده را غیرضروری دانسته و مستقیم عمل کند.
اهمیت مرتب شده بودن اطلاعات در تشکیل Joinهای بهینه
کوئری زیر را در نظر بگیرید که در آن یک select * را داریم (که یک ضد الگو است):
SELECT * FROM [Sales].[Orders] [o] JOIN [Sales].[OrderLines] [ol] ON [o].[OrderID] = [ol].[OrderID]; GO
جدول OrderLines دارای یک non-clustered index، فقط بر روی ستون OrderID است؛ اما با select * نوشته شده، تمام ستونهای آنرا درخواست کردهایم (و نه فقط OrderID را)؛ به همین جهت اطلاعات آن پیش از ارسال به merge join باید توسط عملگر sort مرتب شود و همانطور که مشاهده میکنید، هزینهی این عملگر در این پلن، 82 درصد کل است.
تاثیر order by بر روی کوئری پلن تشکیل شده
دو کوئری زیر را در نظر بگیرید که تفاوت دومی با اولی، در داشتن یک ORDER BY است:
SELECT TOP 1000 * FROM [Sales].[OrderLines]; GO SELECT TOP 1000 * FROM [Sales].[OrderLines] ORDER BY [Description]; GO
اولی، تمام clustered index را اسکن نمیکند و جائیکه 1000 ردیف را از آن بازگشت میدهد، متوقف میشود.
اما در دومی چون نیاز به مرتب سازی اطلاعات بر اساس یک ستون بودهاست، عملگر sort مشاهده میشود. اسکن آن نیز بر روی کل اطلاعات است (پیکان مرتبط با آن، نسبت به پلن قبلی ضخیمتر است) و سپس آنها را مرتب میکند.
برای بهبود این وضعیت، تعداد ستونهای بازگشت داده شده را محدود کرده و سپس بر اساس آنها، ایندکس صحیحی را طراحی میکنیم:
بنابراین اینبار بجای select *، تعداد مشخصی از ستونها را بازگشت میدهیم:
SELECT [CustomerID], [OrderDate], [ExpectedDeliveryDate] FROM [Sales].[Orders] ORDER BY [CustomerID]; GO
CREATE NONCLUSTERED INDEX [IX_Sales_Orders_CustomerID_Dates] ON [Sales].[Orders]( [CustomerID] ASC ) INCLUDE ( [OrderDate], [ExpectedDeliveryDate] ) ON [USERDATA]; GO