حل هزینه Lookup با استفاده از مفهوم Cover Index

حل هزینه Lookup با استفاده از مفهوم Cover Index

نوشته شده توسط: میلاد فیروزی
۱۶ دی ۱۳۹۴
زمان مطالعه: 7 دقیقه
۴
(۱)

مقدمه

 
اگر تا به حال به مسائل Tuning و Performance در زمینه ی Queryها پرداخته باشید احتمالا با Execution Plan سر و کله زده اید تا ببینید کدام قسمت از Queryتان مشکل دارد و باید آن قسمت را اصلاح کنید و یا دنبال راه حل Indexی باشید.
همان طور که می بینید در این Query تقریبا چیزی حدود ۵۰ درصد هزینه صرف Key Lookup شده است ، حال می خواهیم ابتدا ضمن بررسی کردن دلیل به وجود آمدن Lookup راه حل برطرف نمودن آن را نیز ارائه دهیم.

Lookup چیست؟

خوب Lookup به معنای گشتن دنبال چیزی می باشد ، در Lookup اتفاقی که می افتد این است که SQL Server برای پیدا کردن یک یا چند فیلد نمی تواند از Index تعریف شده استفاده کند و باید برای یافتن آن فیلد و یا فیلدها دوباره در خود جدول بگردد.همانطور که می دانید دو نوع Index داریم ، Clustered  و Non Clustered. چون مبحث امروز در مورد Indexها نیست سعی می کنم خیلی خلاصه مفهوم این دو Index را بررسی کنم. در نوع Clustered اتفاقی که می افتد این است که SQL چینش فیزیکی رکوردها را مرتب می کند و چون بحث مرتب سازی می باشد رکوردها تنها می توانند بر اساس یک فیلد مرتب شوند ، در نتیجه Clustered Index روی همان فیلد تعریف می شود ، پس از تعریف Primary Key هم یک Clustered Index تعریف می شود که رکوردها را بر اساس آن Key مرتب می کند.
در نو Non Clustered روی یک یا چند فیلد می توانیم Index بگذاریم و اتفاقی که می افتد این است که این فیلد ها توسط SQL در مکانی دیگر کپی می شوند و با توجه به ساختاری به نام B-Tree مرتب می شوند. خوب Non Clustered index می تواند روی جداول Heap (جدولی که هیچ نوعی از Index روی آن تعریف نشده است) و یا جداول Clustered تعریف شود. در هر دوی آن ها ما تعداد محدودی از فیلدها را در Index داریم و اگر در شرط SELECT QUERY به فیلدهای بیشتری نیاز باشد SQL مجبور می شود برای یافتن آن فیلدها به خود جدول مراجعه کند و به دنبال آن فیلدها “بگردد” که به این کار به اصطلاح Key Lookup می گویند.
 
اگر این گشتن روی یک Clustered Table باشد چون به دنبال کلید Indexی که خود جزو Non Clustered نیز هست می گردد به آن Key Lookup گفته می شود و حال اگر این Non Clustered روی Heap Table تعریف شده باشد SQL مجبور است تمامی جدول را برای یافتن فیلد اضافی بگردد که به این عمل هم RID Lookup گفته می شود.

چگونه می توان Lookup را برطرف کرد؟

ما می توانیم به Non Clustered بگوییم که به جز آن چند فیلدی که در جایی دیگر کپی شده اند یک یا چند فیلد دیگر را نیز در کنار آن ها نگهداری کند به نحوی که این فیلدها در نحوه مرتب سازی تاثیری ندارند و فقط به عنوان فیلدهای اضافی به مابقی فیلدها چسبیده اند ، با این روش SQL وقتی شرط SELECT را چک می کند دیگر نیازی ندارد به خود جدول سر بزند و هرآنچه نیاز دارد در کنار بقیه فیلدها پیدا می کند و هزینه صرف شده جهت Lookup صرفه جویی می شود به این نوع از Indexها Covered Index گفته می شود.

حال به مثال زیر دقت کنید

در این مثال از دیتابیس AdventureWorks2012 استفاده می کنیم ، می خواهیم دستور زیر را اجرا کنیم ، قبل آن STATISTICS IO  را فعال می کنیم تا تعداد صفحات خوانده شده را بتوانیم بررسی کنیم ،
SET STATISTICS IO ON;
SELECT CustomerID, OrderDate
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]
WHERE CustomerID = 11000;

خوب حالا نتایج STATISTICS IO را ببینیم ،

Table ‘SalesOrderHeader’. Scan count 1, logical reads 11, physical reads 0, read-ahead reads

برای این واکشی ۱۱ صفحه خوانده شده است. حال نتیجه Execution Plan را با هم ببینیم ،

مشاهده می شود که ۶۸ درصد هزینه صرف Key Lookup شده است و با اینکه Non Clustered Index تعریف کرده بودیم هنوز هزینه ی زیادی بابت Key Lookup پرداخت می شود. حال Index قبلی را Drop کرده و Index جدیدی با استفاده از مفهوم Covered Index ایجاد می کنیم ،

حال دوباره نتیجه STATISTICS IO و Execution Plan  را ببینیم ،

Table ‘SalesOrderHeader’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads

DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_CustomerID;
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID_OrderDate
ON Sales.SalesOrderHeader(CustomerID, OrderDate);

 مشاهده می شود که علاوه بر حذف شدن هزینه Key Lookup تعداد صفحات خوانده شده نیز به عدد ۲ کاهش یافت.

چه رتبه ای می‌دهید؟

میانگین ۴ / ۵. از مجموع ۱

اولین نفر باش

title sign
معرفی نویسنده
میلاد فیروزی
مقالات
8 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
میلاد فیروزی
پروفایل نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link
جشنواره عیدآموز نیک آموز، سال جدید رو با قدرت شروع کن
مشاهده تخفیف ها
close-image