حل هزینه 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
معرفی محصول
مسعود طاهری

دوره آموزشی Performance Tuning در SQL Server (گروه 15)

ثبت نام حضوری8.700.000 تومان
ثبت نام غیرحضوری5.700.000 تومان
title sign
دیدگاه کاربران

    • با سلام
      مقاله تحسین برانگیزی بود
      یه سوال:
      به جای استفاده از الگوی Cover Index بهتر نبود الگوی Included Columns پیشنهاد می شد چون با این الگوی جدید تر می‌توان ستون‌های اضافی را در ایندکس‌ها، Include کنیم و نیازی نیست که جزء ستون‌های اصلی ایندکس باشند.
      برداشت من این بود که به طور ضمنی در اولین کامنتی که استاد عزیز جناب مهندس طاهری هم گذاشته اند به این مطللب اشاه شده بود
      با سپاس

    • با سلام
      مقاله تحسین برانگیزی بود
      یه سوال:
      به جای استفاده از الگوی Cover Index بهتر نبود الگوی Included Columns پیشنهاد می شد چون با این الگوی جدید تر می‌توان ستون‌های اضافی را در ایندکس‌ها، Include کنیم و نیازی نیست که جزء ستون‌های اصلی ایندکس باشند.
      برداشت من این بود که به طور ضمنی در اولین کامنتی که استاد عزیز جناب مهندس طاهری هم گذاشته اند به این مطللب اشاه شده بود
      با سپاس

    •   با سلام و خسته نباشید خدمت آقای میلاد فیروزی

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

      با تشکر از شما

    •   با سلام و خسته نباشید خدمت آقای میلاد فیروزی

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

      با تشکر از شما

    •     با سلام وتشکر از مقاله بسیار و عالی و جذابی که داشتید واقعا بعد از مدت ها که اومدم شگفت زده شدم بابت این همه مطلب عالی

    •     با سلام وتشکر از مقاله بسیار و عالی و جذابی که داشتید واقعا بعد از مدت ها که اومدم شگفت زده شدم بابت این همه مطلب عالی

    • یکی از بزرگترین معایب جداول Heap مسئاله Forwarding Pointer است. 

      یکی از دلیل استفاده از Forwarding Pointer جلوگیری از بروزرسانی ردیف‌های ایندکس‌های Non Clustered بهنگام انتقال row اصلی به data page  دیگر پس از بروزرسانی می‌باشد. 
      اما این مسئاله (Forwarding Pointer) یک ایراد بزرگ به سیستم تحمیل می کند زمانی که SQL Server از IAM Page برای استخراج Pageهای وابسته به یک Heap Table استفاده می کند ممکن است به ازای برخی از Pageها چندین بار عملیات خواندن تکرار شود و این یعنی تحمیل IO اضافی به سیستم.
    • یکی از بزرگترین معایب جداول Heap مسئاله Forwarding Pointer است. 

      یکی از دلیل استفاده از Forwarding Pointer جلوگیری از بروزرسانی ردیف‌های ایندکس‌های Non Clustered بهنگام انتقال row اصلی به data page  دیگر پس از بروزرسانی می‌باشد. 
      اما این مسئاله (Forwarding Pointer) یک ایراد بزرگ به سیستم تحمیل می کند زمانی که SQL Server از IAM Page برای استخراج Pageهای وابسته به یک Heap Table استفاده می کند ممکن است به ازای برخی از Pageها چندین بار عملیات خواندن تکرار شود و این یعنی تحمیل IO اضافی به سیستم.