شاخصهاي يك جدول مي‌توانند به شما در دسترسي به يك يا چند سطر از داده‌ها كمك كنند. داشتن يك شاخص خوب براي پرس و جوي (query) شما يكي از بهترين راههاي بهبود بخشيدن به عملكرد است. هنگامي كه پرس و جوي شما سعي دريافتن تنها تعداد محدودي سطر از يك جدول بزرگ دارد،وجود يا عدم وجود يك شاخص خوب و مفيد مي‌تواند تاثير چشمگيري در تفاوت عملكرد داشته باشد. هنگام تنظيم پرس و جوهايي كه جداول گوناگوني دارند (مانند پيوند)، شاخصها مي‌توانند بطور موثري به سرويس‌دهنده SQL دريافتن سطرهاي موردنظر از بين جدولها كمك كنند.
بهينه ساز پرس و جوي سرويس دهنده SQL مي‌تواند براي اجراي پيوندها از ميان 3 استراتژي يكي را انتخاب كند: پيوند حلقه‌اي تودرتو (nested-loop)، پيوند ادغامي (merge) و پيوند hash، در اين مقاله به شرح دو استراتژي اول يعني حلقه‌اي تودرتو و ادغامي مي‌پردازيم. در هر روش، جدولهايي را كه مي‌خواهيد پيوند دهيد (يا زير مجموعه‌هايي از آنها كه با شرط WHERE محدود كرده‌ايد) وروديهاي پيوند هستند. اگر پرس و جوي شما علاوه بر پيوند، شرط WHERE را نيز شامل شود، سرويس دهنده SQL ممكن است قبل از يافتن سطرهاي موردنظر در دومين جدول شرط WHERE را بكار ببرد. بعنوان مثال پرس و جوي 1 از بانك اطلاعاتي Northwind را در نظر بگيريد.
-- Query 1:
SELECT LastName, FirstName, OrderID, OrderDate
FROM Employees e JOIN Orders o
ON e.EmployeeID = o.EmployeeID
اين پيوند تمام سفارشات را از جدول سفارشها باز مي‌گرداند و همچنين براي هر يك از آنها، FirstName، LastName كارمندي را كه به آن OrderID و OrderDate مربوط مي‌شود را نيز باز مي‌گرداند.
در پرس و جوي 1 سرويس دهنده SQL تمام سطرها را در جدول كارمندان و سفارشات امتحان مي‌كند و وروديهاي پيوند تمام سطرهاي جدولها هستند. بهرحال، طبق آنچه كه پرس و جوي 2 نشان مي‌دهد، اگر شما عبارت SELECT را با دو شرط WHERE تعريف كنيد، وروديهاي پيوند ديگر تمام سطرهاي جدولها نيستند.
-- Query 2:
SELECT LastName, FirstName, OrderID, OrderDate
FROM Employees e JOIN Orders o
ON e.EmployeeID = o.EmployeeID
WHERE OrderDate < '1996-12-01' AND LastName < 'D'
وروديهاي پيوند در پرس و جوي 2، بسيار كوچكتر از پرس و جوي 1 هستند. به جاي پيوند 9 سطر از جدول كارمندان با 830 سطر از جدول سفارشات، سرويس دهنده SQL بايد تنها 2 سطر از جدول كارمندان را با 121 سطر از جدول سفارشات پيوند دهد. بهينه ساز پرس و جو با وجود تعداد محدودي از سطرهاي ورودي غالباً استراتژي پيوند متفاوتي را در مقايسه با زماني كه وروديهاي پيوند بيشتر هستند برمي‌گزيند و همچنين ممكن است جدولها را به سبك متفاوتي پيوند دهد. به همان اندازه كه تصميمات استراتژي بهينه ساز مهم هستند اندازه جدولها نيز حائز اهميت است.
حلقه‌هاي تودرتو
حتي اگر پرس وجوي شما بيش از 2 جدول را پيوند دهد، سرويس دهنده SQL عمل پيوند را از طريق پيوند تنها دو ورودي در يك زمان اجرا مي‌كند و هر پيوند در يك پرس و جو ممكن است از استراتژي پيوند متفاوتي استفاده كند. آسانترين نوع پيوند – و نوعي از پيوند كه اكثر افراد هنگام عمليات پيوند بدان فكر مي‌كنند – پيوند حلقه تودرتوست مي‌توانيد تصور كنيد كه سرويس دهنده SQL روي دو ورودي عمل مي‌كند حتي اگر آنها آرايه‌هايي در يك زبان پيشرفته مانند C يا Basic باشند. سرويس دهنده SQL هر سطر يك ورودي را با تمام سطرهاي ورودي ديگر مقايسه مي‌كند تا تطابق بين سطرها را بيابد.
پرس و جوي 1 سعي دريافتن سطرهايي دارد كه با ستون EmployeeID تطبيق داشته باشد. بنابراين با استراتژي پيوند حلقه تودرتو، سرويس دهنده SQL بايد تمام مقادير EmployeeID در يك جدول را با تمام مقادير EmployeeID در جدول ديگر مقايسه كند.
بدترين قسمت سناريو براي يك پيوند حلقه تودرتو زماني است كه هيچ شاخصي نتواند به سرويس دهنده SQL دريافتن سطرهاي منطبق در بين وروديها و همچنين يافتن سطرهايي كه در هر شرط WHERE صدق مي‌كند، كمك نمايد. در ين حالت، وروديها كل سطرهاي جدولها هستند. بهينه ساز پرس و جو جدولي را بعنوان جدول خارجي انتخاب مي‌كند و در ابتدا به سطرهاي آن دستيابي مي‌يابد. بياييد فرض كنيم كه جدول خارجي داراي P1 صفحه و R1 سطر باشد. دومين جدول كه جدول داخلي است P2 صفحه دارد. سرويس دهنده SQL بايد تمام صفحات را از جدول خارجي بخواند؛ و براي هر سطر تعريف شده در هر صفحه بايد تمام صفحات را از جدول داخلي بخواند. براي يافتن تعداد صفحاتي كه سرويس دهنده SQL براي خواندن و ارائه نتيجه نياز دارد، مي‌توانيد از فرمول زير استفاده كنيد:
P1 + R1 * P2
حتي اگر جدولها نسبتاً كوچك باشند، عدد حاصله از صفحات خوانده شده به سرعت بزرگ مي‌شود. در مورد يك جدول خارجي با تنها 200 صفحه و 4000 سطر (براي مثال 20 سطر براي هر صفحه) و يك جدول داخلي با 100 صفحه، نتيجه رقمي كاملاً بزرگ است. جدولهايي با 100 يا 200 صفحه جدولهايي نيستند كه بطور غيرمعمول بزرگ باشند، اما براي پردازش پيوند در صورتي كه جدولها شاخصهاي مفيدي نداشته باشند، سرويس دهنده SQL نياز به دستيابي به بيش از 400,000 صفحه خواهد داشت.
شاخصها مي‌توانند در بهبود عملكرد يك پيوند حلقه تودرتو به طرق مختلف نقش داشته باشند. بزرگترين حسن اين شاخصها اغلب زماني است كه شما يك شاخص كلاستري روي ستون پيوند يكي از جدولها داشته باشيد. وجود يك شاخص كلاستري روي ستون پيوند غالباً مشخص مي‌كند كه سرويس دهنده SQL چه جدولي را بعنوان جدول داخلي انتخاب مي‌كند. اگر جدول داخلي داراي شاخص كلاستري باشد، سرويس دهنده SQL نياز به جستجو در ميان كل سطرهاي آن جدول را ندارد. شاخص كلاستري، سرويس دهنده SQL را مستقيماً به سوي سطرهايي در جدول داخلي هدايت مي‌كند كه داراي مقدار ستون پيوند بوده كه سطرهاي جاري در جدول خارجي را تطبيق مي‌دهد. بنابراين در آن فرمول، به جاي عبارت R1 ´ P2 كه نشان مي‌دهد سرويس دهنده SQL به تمام P2 صفحه دستيابي پيدا مي‌كند، مي‌توانيد P2 را با دستيابي 2 يا 3 صفحه‌اي جايگزين كنيد بسته به اينكه شاخص كلاستري چند Level دارد. بنابراين در مورد مثالي با 200 صفحه و 400 سطر در جدول خارجي و 100 صفحه در جدول داخلي نتيجه 3*4000+200 يا 200،12 صفحه خوانده شده است – يك پيشرفت بزرگ بالاي 400,000 صفحه مي‌باشد.
هنوز هم آن 4000 سطر در محاسبه نتيجه را بزرگتر از حد انتظار خواهد كرد. در اين حالت، تمامي 4000 سطر در جدول خارجي بخشي از نتيجه هستند كه موجب 4000 بار رجوع به جدول داخلي مي‌شود. يك راه ديگر براي كاهش تعداد صفحات بدست آمده كاهش اندازه وروديهاي خارجي است. علاوه بر كنترل شاخص كلاستري روي ستون پيوند، ابتدا بهينه ساز سعي مي‌كند جدولها را با وروديهاي كوچكتر پيوند دهد. در پرس و جوي 1، جدول كارمندان داراي يك شاخص كلاستري روي ستون پيوند يعني EmployeeID هستند اما اين جدول نيز بطور نمايشي كوچكتر از جدول سفارشات است. جدول كارمندان تنها 9 سطر دارد و جدول سفارشات 830 سطر. در پرس و جوي 1، اگر بهينه ساز يك پيوند حلقه تودرتو را انتخاب كند، از جدول كوچكتر كارمندان به عنوان ورودي خارجي استفاده مي‌كند بگونه‌اي كه تنها 9 بار به جدول سفارشات رجوع خواهد داشت.
اگر شما داراي شرط WHERE باشيد كه جدول خارجي را شامل مي‌شود، تعداد سطرهاي تعريف شده پايين مي‌آيد و سرويس دهنده SQL كمتر نياز به مراجعه به جدول داخلي را خواهد داشت. اگر پرس و جوي 1 را طوري تغيير دهيد كه شامل يك شرط WHERE در جدول سفارشات باشد، همانگونه كه در پرس و جوي 3 نشان داده شده، طرح پرس و جو تغيير مي‌كند.
-- Query 3:
SELECT LastName, FirstName, OrderID, OrderDate
FROM Employees e JOIN Orders o
ON e.EmployeeID = o.EmployeeID
WHERE OrderDate < '1996-12-01'
حالا، تنها 121 سطر در جدول سفارشات بخشي از نتيجه هستند، آن جدول كوچكتر كه با شاخص كلاستري روي ستون پيوند جدول كارمندان تركيب شده اين مفهوم را مي‌رساند كه بهينه ساز حالا جدول كارمندان را به عنوان جدول داخلي انتخاب مي‌كند. سرويس دهنده SQL از پيوند حلقه تودرتو استفاده خواهد كرد چرا كه شاخص كلاستري باعث مي‌شود سرويس دهنده SQL سريعاً سطرهاي منطبق شده را در جدول داخلي بيابد.
FIGURE 1: Query plan for Query 3
|..Nested Loops(Inner Join, OUTER REFERENCES:([o].[EmployeeID]))
|..Clustered Index Scan(OBJECT:([northwind].[dbo].[Orders].[PK_Orders]
AS [o]),
WHERE:([o].[OrderDate] < 'Dec 1 1996 12:00AM'))
|..Clusterd Index
Seek(OBJECT:([northwind].[dbo].[Employees].[PK_Employees] AS [e]),
SEEK:([e].[EmployeeID]=[o].[EmployeeID]) ORDERED FORWARD)
شكل 1 طرح پرس و جو را در مورد پرس و جوي 3 نشان مي‌دهد. اولين خط اين طرح نوع پيوند (حلقه تودرتو) را نشان مي‌دهد و مشخص مي‌كند كه جدول خارجي ستون EmployeeID را ارجاع خواهد داد.
اسكن شاخص كلاستري در جدول خارجي شبيه اسكن يك جدول است زيرا هيچيك از شاخصهاي موجود نمي‌تواند دستيابي به جدول خارجي را سرعت ببخشد. شرط WHERE در ستون OrdrerDate تعداد سطرهاي برگردانده شده و تعداد دفعاتي كه سرويس دهنده SQL بايد به جدول داخلي دستيابي داشته باشد تا تعيين كند كداميك مقدار
OrderDate قابل قبولي دارند. در نهايت، طرح پرس و جو نشان مي‌دهد كه سرويس دهنده SQL از يك شاخص كلاستري براي جستجوي جدول داخلي استفاده مي‌كند زيرا اين شاخص روي ستوني است كه سرويس دهنده SQL براي يافتن سطرهاي منطبق استفاده مي‌كند.
چنانچه قبلاً ذكر شد شاخص در ستون OrderDate چيز خوبي است اما عملكرد پرس و جو را تقريباً به يك شاخص كلاستري روي ستون پيوند بهبود نخواهد بخشيد. يك شاخص مفيد در پارامتر جستجو در جدول خارجي بدين معناست كه سرويس دهنده SQL نبايد به تمام صفحات جدول خارجي رجوع نمايد، بنابراين، مقدار P1 كاهش مي‌يابد. باتوجه به اينكه مقدار P1 نسبت به مقدار دومين عبارت، P2 R1، كوچكتر است، بنابراين كاهش مقدار P1 فقط موجب بهبودي كمتر عملكرد مي‌گردد. شاخص جدول خارجي تعداد دفعاتي كه سرويس دهنده SQL بايد به جدول داخلي رجوع كند را كاهش نمي‌دهد زيرا سرويس دهنده SQL هنوز بايد بازاي هر سطر تعريف شده در جدول خارجي به جدول داخلي رجوع كند. شما مي‌توانيد انتخاب بهينه‌ساز از پيوند حلقه تودرتو را اينگونه تعميم دهيد: در صورتي كه يكي از وروديهاي پيوند بسيار كوچكتر از ديگري و ورودي بزرگتر داراي يك شاخص كلاستري روي ستون پيوند باشد، بهينه ساز اغلب پيوند حلقه تودرتو را برمي‌گزيند.
ادغام
در پيوند حلقه تودرتو، شاخص ستون پيوند در مورد جدول خارجي بي‌فايده است. بهرحال، زماني كه شما پرس و جوها و جداول را تنظيم مي‌كنيد، ممكن است هميشه ندانيد كه كدام جدول داخلي و كدام خارجي است، بنابراين بايد در هر دو جدول ورودي شاخصهاي كلاستري را روي ستونهاي پيوند ايجاد كرد. زماني كه هر دو وروديهاي پيوند روي ستون پيوند مرتب سازي مي‌شوند، سرويس دهنده SQL مي‌تواند از پيوند ادغامي استفاده كند، درست مانند موردي كه هر دو جدول داراي شاخصهاي كلاستري روي ستون پيوند باشند.پيوند ادغامي را مي‌توان همچون تركيب دويست مرتب سازي شده از مقادير تصور كرد. فرض كنيد داراي دو سري از اطلاعات پيمانكاري هستيد. يك سري شامل قراردادهاي مهم مي‌باشد كه هر پيمانكاري آن را امضا كرده است و دومين سري توصيف هريك از پروژه‌هايي است كه پيمانكار بر روي آن كار مي‌كند بنابراين، شما اساساً نياز به يك پل ارتباطي ميان اين دو سري اطلاعات داريد.پرس و جوي 1 را در نظر بگيريد: اگر جدول كارمندان و جدول سفارشات در ستون EmployeeID شاخصهاي كلاستري داشته باشند، سرويس دهنده SQL مي‌تواند پيوند ادغامي را اجرا كند. شبه كد مربوطه در مورد اجراي ادغامي سرويس دهنده SQL چيزي شبيه اين عبارات خواهد بود:
GET one Orders row and one Employees row
DO (until one input is empty);
IF EmployeeID values are equal
Return values from both rows
GET next Orders row
ELSE IF Orders.EmployeeID <> Employees.EmployeeID
GET next Employees row
ELSE GET next Orders row
بهينه ساز پرس و جو معمولاً استراتژي پيوند ادغامي را زماني انتخاب مي كند كه هر دو وروديها قبلاً در ستون پيوند مرتب شده باشند. اگر هر دو ورودي قبلاً مرتب شده باشند، در صورتي كه پيوند يك به چند باشد استفاده از I/O كمتري براي پردازش پيوند ادغامي ضروريست. پيوند ادغامي چند به چند (M:N) به جاي كنار گذاشتن سطرها كه معمولاً انجام مي‌دهد، آنها را در يك جدول موقتي ذخيره مي‌كند. اگر داده‌ها شامل مقادير تكراري از هر دو ورودي باشند، هنگامي كه سرويس دهنده SQL هر مقدار تكراري را از اولين ورودي پردازش مي‌كند، دومين ورودي بايد به ابتداي مقادير تكراري در جدول موقت بازگردد. بهرحال، در اكثر موارد، سرويس دهنده SQL از پيوند ادغامي استفاده نخواهد كرد مگر اينكه حداقل يكي از ستونهاي پيوند Unique باشد.در اينجا مثالي از پيوند دو جدول يكي با شاخص و ديگري بدون شاخص Unique آورده شده است.
LISTING 1: Joins Two Tables With and Without Unique Index
-- Copy the two tables
SELECT * INTO o1 FROM orders
SELECT * INTO od1 FROM [order details]
-- Create the indexes.
CREATE CLUSTERED INDEX orders_index
ON o1(orderID)
CREATE CLUSTERED INDEX OD_index
ON od1(orderID)
-- Look at the query plan for the query.
SELECT * FROM o1 JOIN od1
ON o1.orderID = od1.orderID
-- Now recreate the clustered index on o1 as unique.
CREATE UNIQUE CLUSTERED INDEX orders_index ON o1(orderID)
WITH DROP_EXISTING
-- Now Look at the query plan for the query.
SELECT * FROM o1 JOIN od1 ON o1.orderID = od1.orderID
LISTING 2: Modified Listing 1
SELECT * INTO o2 FROM orders
SELECT * INTO od2 FROM [order details]
CREATE UNIQUE CLUSTERED INDEX orders_index ON o2(orderID)
CREATE INDEX OD_index ON od2(orderID)
-- Check the query plan to see the sort operation before
-- the merge join:
SELECT * FROM o2 JOIN od2
ON o2.orderID = od2.orderID
ليست 1 كپي‌هايي از جدول سفارشات و جدول جزئيات سفارشات در بانك اطلاعاتي Northwind ايجاد مي‌كند و يك شاخص كلاستري در OrderID هر دو جدول مي‌سازد. زماني كه شما ابتداً اين جدولها را پيوند مي‌دهيد و طرح پرس و جو را به نمايش مي‌گذاريد، خواهيد ديد كه سرويس دهنده SQL پيوند حلقه تودرتو را انتخاب مي‌كند. اگرچه ستون OrderID در جدول سفارشات Unique است، اما در صورتي كه Unique بودن را در تعريف شاخص مشخص نكنيد، بهينه ساز متوجه نخواهد شد كه مقادير كليدي Unique هستند. بنابراين هنگامي كه مجدداً شاخص كلاستري را در جدول سفارشات مي‌سازيد و مشخص كنيد كه آن شاخص بايد Unique باشد، طرح پرس و جوي اصلاح شده نشان مي‌دهد كه سرويس دهنده SQL از يك پيوند ادغامي استفاده مي‌كند. در برخي از حالتها، بهينه ساز سرويس دهنده SQL ممكن است به دليل به صرفه بودن تصميم بگيرد يكي از وروديها را قبل از پيوند مرتب كند و بعد پيوند ادغامي را اجرا نمايد. اگر ليست 1 را كمي تغيير دهيد به گونه‌اي كه شاخص اوليه كه در جدول جزئيات سفارشات ساخته مي‌شود كلاستري نباشد، طبق آنچه كه ليست 2 نمايش مي‌دهد، طرح پرس و جو عمل مرتب سازي را قبل از پيوند ادغامي نشان مي‌دهد.
جدولهايتان را بشناسيد
معمولاً، بهينه ساز پرس و جو تعيين مي‌كند كه سرويس دهنده SQL چه نوع پيوندي را استفاده خواهد كرد. شما مي‌توانيد پرس و جوي پيوند خود را نوشته و انتخاب استراتژي را به عهده SQL بگذاريد. بهرحال، دانستن نحوه اجراي پيوندها در سرويس دهنده SQL مي‌تواند انتخاب شما در مورد شاخصهاي مفيدتر كمك كند. بهينه ساز معمولاً در صورتي كه يكي از وروديهاي پيوند در مقايسه با ورودي ديگري كوچكتر و ورودي بزرگتر داراي يك شاخص كلاستري روي ستون پيوند باشد، از پيوند حلقه تودرتو استفاده مي‌كند. اگر هر دو ورودي در ستون پيوند مرتب شده باشند و بخصوص يكي از وروديها داراي شاخص كلاستري unique باشد نوع پيوند به احتمال زياد ادغامي خواهد بود.
هر دو پيوند ادغامي و حلقه تودرتو نياز به شاخصهاي مناسب در جدولها دارند. بهرحال، اگر برنامه كاربردي شما باعث شود كه كاربرها پرس و جوهاي ويژه بسازند، ممكن است در ابتدا ندانيد كه بهترين ستون براي شاخصها كدام است. سومين نوع پيوند، پيوند hash است كه باعث مي‌شود سرويس دهنده SQL به يك عملكرد پيوند بسيار خوب دست بيابد حتي زماني كه جداول شما از شاخصهاي مفيدي برخوردار نباشند.
جواب بصورت نقل قول
.gif ":دی")
