آشنایی با Lookup در SQL Server

مقدمه

در مقاله بررسی جداول Heap و Clustered در SQL Server با نحوه کار Index‌ها آشنا شدیم و یاد گرفتیم که SQL Server چگونه از Index‌ها استفاده می‌کند. در این مقاله به دنبال آن هستیم تا با مفهوم Lookup در SQL Server آشنا شویم. پیش از آنکه با این مفهوم آشنا شویم لازم است تا به‌صورت مختصر با دو ایندکس Non-cluster index  و Cluster index آشنا شویم.

Cluster Index

این نوع از ایندکس ترتیب ذخیره‌سازی رکوردها را در Page‌ها با استفاده از کلید ایندکس انجام می‌دهد. همانند صفحات یک کتاب که از ترتیب خاصی برخوردار هستند. نکته‌ای که می‌توان به آن اشاره نمود آن است که یک جدول می‌تواند تنها یک ایندکس Cluster داشته باشد. بنابراین اگر در جدول شما Primary key داشته باشید  SQL Server به‌طور پیش‌فرض بر روی کلید Primary یک Cluster Index ایجاد می‌کند اما می‌توان پیش‌فرض SQL Server را تغییر داد و از یک Non-Cluster Index برای Primary Key استفاده نمود.

Non-Cluster Index

این نوع از ایندکس درواقع شاخص گذاری روی کلید ایندکس را انجام‌ می‌دهد و به ترتیب ذخیره‌سازی داده‌ها توجهی ندارد. درواقع همانند ایندکس پایان کتاب عمل می‌کند و اعلام می‌کند کلید ایندکس انتخابی به ترتیب در کدام رکوردها و به همین صورت در کدام Page ها قرار دارد.

مفهوم LookUp

واژه Lookup  به معنای جستجو است. SQL Server زمانی از LookUp استفاده می‌کند که نتواند پاسخ کوئری موردنظر را با استفاده از ایندکس‌های تعریف شده روی آن جدول بدهد. در واقع زمانی که در یک کوئری ستون‌هایی از جدول را که برای واکشی انتخاب شده‌اند در ایندکس تعریف شده موجود نباشند، نیاز است تا با استفاده از LookUp ستون موردنظر واکشی شود. وجود LookUp به‌خودی‌خود باعث کاهش کارایی می‌شود بنابراین باید دقت نمود از به وجود آمدن LookUp تا حد امکان اجتناب نمود.
دو نوع LookUp در Sql Server وجود دارد که با نام‌های RID LookUp و Key LookUp شناخته می‌شوند. اگر یک جدول به‌صورت Cluster Table باشد (جدولی که یک Cluster Index داشته باشد را Cluster Table گویند) SQL Server از Key LookUp و اگر جدول به‌صورت Heap باشد SQL Server از  RID LookUp  استفاده می‌کند.
جدول زیر را در نظر بگیرید:

CREATE TABLE [dbo].[ResellerSales](
  [ProductKey] [int] NOT NULL,
  [OrderDateKey] [int] NOT NULL,
  [DueDateKey] [int] NOT NULL,
  [ShipDateKey] [int] NOT NULL,
  [ResellerKey] [int] NOT NULL,
  [EmployeeKey] [int] NOT NULL,
)
CREATE NONCLUSTERED INDEX [NonClusteredIndex-EmployeeKey] ON [dbo].[ResellerSales]
(
  [EmployeeKey] ASC
)

حال کوئری زیر را در نظر بگیرید.

SELECT   [EmployeeKey],count(*)  FROM [dbo].[ResellerSales]
where [EmployeeKey]=285

در این کوئری SQL Server بدون نیاز به LookUp و تنها با استفاده از کلید ایندکس اقدام به یافتن نتیجه جستجو می‌کند. حال به کوئری زیر توجه نمایید:

SELECT  [OrderDateKey],[EmployeeKey]  FROM [ResellerSales]
      where [EmployeeKey]=285

اما در کوئری دوم ازآنجایی‌که به‌جز کلید ایندکس یک یا چند ستون اضافی نیز نیاز به واکشی دارد بنابراین ستون اضافی در کلید ایندکس وجود نداشته و SQL Server مجبور می‌شود از LookUp استفاده نماید. ازآنجایی‌که جدول ResellerSales یک جدول Heap می‌باشد، بنابراین RID LookUp برای یافتن نتیجه جستجو استفاده شده است.
حال یک ایندکس Cluster را روی فیلد OrderDateKey در نظر بگیرید.

CREATE CLUSTERED INDEX [ClusteredIndex-OrderDateKey] ON [dbo].[ResellerSales]
(
  [OrderDateKey] ASC
)

اگر کوئری ۲ را مجدد اجرا نماییم، این بار SQL Server بجای استفاده از RID LookUp از Key LookUP استفاده می‌کند.این بدان معنا می‌باشد که SQL از کلید NonCluster برای انتخاب رکوردهای موردنظر طبق شرط Where استفاده می‌کند و در ادامه به‌منظور واکشی کلیه اقلام اطلاعاتی لازم (جلوی دستور Select) از RID lookup استفاده کرده و اقلام اطلاعاتی را از Leaf Level درخت Cluster Index واکشی می‌کند. (برای دریافت اطلاعات بیشتر در رابطه با درخت B-Tree می‌توانید از مقاله  نحوه تشکیل درخت B-Tree در SQL Server استفاده نمایید.)

حل مشکل LookUp

روش‌های زیر می‌تواند به ما در حل مسئله LookUp کمک کند اما با توجه به بیزینس موجود لازم است تا بهترین روش انتخاب گردد. لازم به ذکر است گاهی نمی‌توان مسئله LookUp را به‌طورقطع حل نمود اما با توجه به بیزینس موجود می‌توان تعداد LookUp‌ها را کاهش داد.

۱- استفاده از Covering Index

فرض کنید برای جدول هیپ ResellerSales یک Non-Cluster بر روی دو فیلد  [OrderDateKey],[EmployeeKey] ایجاد کنید. آنگاه اگر کوئری ۲ را مجدد اجرا نمایید می‌بینید که SQL Server بدون نیاز به استفاده از RID LookUp مستقیم تمامی فیلدهای موردنیاز خود را از داخل کلید ایندکس به دست می‌آورد. در واقع اگر می‌بینید در کنار کلید ایندکس اغلب از فیلدهای دیگری نیز استفاده می‌شود (تعداد فیلدها برای اضافه کردم در ایندکس استاندارد خاصی ندارد اما باید دقت نمود با اضافه کردن هر فیلد به کلید ایندکس سربار ناشی از ذخیره‌سازی و تشکیل درخت جستجو افزایش می‌یابد) می‌توان به‌صورت صریح فیلدهای موردنظر را به کلید ایندکس اضافه نمود.

۲- استفاده از Included Columns

پس‌ از آنکه با کوئری‌های بیزینس مربوطه آشنا شدید می‌توانید تصمیم مهمی برای ایجاد ایندکس‌های جداول آن بگیرید. فرض کنید پس از مانیتورینگ کوئری های اجرا شده در دیتابیس موردنظر متوجه می‌شوید از جدولی مانند جدول ResellerSales به تعداد زیادی استفاده می‌شود و همچنین در این بین درمی‌یابید که برخی از  فیلدهای این جدول در اکثر کوئری ها در حال استفاده است (در بخش Select). در چنین حالتی می‌توانید از Included Columns در ایندکس جدول مربوطه استفاده نمایید. در واقع برخلاف حالت  Covering Index ، این فیلدها در کلید ایندکس به‌صورت صریح استفاده نمی‌شوند اما در کنار کلید ایندکس قرار می‌گیرند تا زمانی که کوئری های موردنظر نیاز به واکشی این اطلاعات داشتند لازم نباشد تا Sql با استفاده از LookUp فیلدهای موردنظر را از جدول واکشی نماید.

CREATE NONCLUSTERED INDEX [NonClusteredIndex-EmployeeKey] ON [dbo].[ResellerSales]
(
  )[EmployeeKey](
INCLUDE ([OrderDateKey],ResellerKey)
)

نکته: باید دقت داشت هر یک از موارد ۱و۲ سربار ناشی از نگهداری فیلدهای اضافی را دارند بنابراین نباید به‌صورت حریصانه از این روش‌ها استفاده نمود. حتی می‌توان گفت گاهی لازم است تا در مسائلی، سربار ناشی از LookUp را پذیرفت و به سراغ روش‌های ذکر شده نرفت.