قصد داریم الگوهای مختلف ایندکس گذاری و استراتژی Non-Clustered Indexes را در Sql Server، بررسی کنیم.
مزایای ایجاد ایندکس‌های صحیح بر اساس نیازهای واقعی کاری:
- سریعتر شدن اجرای کوئری‌های جستجو در تعداد رکوردهای بالا
- مرتب سازی سریعتر نتایج (sorting)
- کوئری‌هایی که بر اساس عبارت GROUP BY ایجاد شده‌اند، سریعتر اجرا خواهند شد
Non-Clustered Indexes
تقریبا در تمام دیتابیس‌ها به راه‌های دیگری برای دسترسی به داده‌های جداول نیاز خواهد شد که لزوما این داده‌ها براساس ترتیب هنگام ذخیره سازی، مرتب نیستند. در چنین شرایطی ایندکس‌های غیر خوشه‌ای بر سر کار خواهند آمد.
در ادامه الگوهای مختلف ایندکس گذاری مرتبط با ایندکس‌های غیر خوشه‌ای را بررسی کرده و برای هر کدام از آنها مثالی را بررسی خواهیم کرد. خواهیم دید هر ایندکسی که از جانب ما ایجاد می‌شود، نمیتوان مطمئن شد که توسط Sql Server مورد استفاده قرار می‌گیرد!
این الگو‌ها در تعیین زمان و مکان ساخت ایندکس‌های غیر خوشه‌ای، به ما کمک خواهند کرد که به شرح زیر می‌باشند:
- Search Columns
- Index Intersection
- Multiple Columns
- Covering Indexes
- Included Columns
- Filterd Indexes
- Foreign Keys
Search Columns
یکی از الگوهای اولیه، ساخت ایندکس‌های غیر خوشه‌ای براساس الگوهای جستجوی تعریف شده یا مورد انتظار می‌باشد. این الگو با اینکه خیلی شناخته شده است ولی گاهی اوقات به راحتی از کنار آن گذشته و از آن چشم پوشی میکنیم.
برای مثال اگر قرار است در جدول Contacts جستجویی براساس نام آنها داشته باشید، بهتر است یک ایندکس غیر خوشه‌ای بر روی فیلد نام ایجاد کنید. هدف اصلی از این الگو، کاهش هزینه‌ی Scan کردن دوباره‌ی ایندکس خوشه دار و انتقال این عملیات به ایندکس غیر خوشه داری که مسیر دسترسی مستقیم به دیتا را مهیا می‌کند. به مثال زیر توجه بفرمایید:
USE AdventureWorks2012;
GO
CREATE TABLE dbo.Contacts (
ContactID INT IDENTITY (1, 1),
FirstName NVARCHAR (50),
LastName NVARCHAR (50),
IsActive BIT ,
EmailAddress NVARCHAR (50),
CertificationDate DATETIME ,
FillerData CHAR (1000) ,
CONSTRAINT PK_Contacts PRIMARY KEY CLUSTERED (ContactID)
);
INSERT INTO dbo.Contacts (FirstName, LastName, IsActive, EmailAddress, CertificationDate)
SELECT pp.FirstName,
pp.LastName,
IIF (pp.BusinessEntityID / 10 = 1, 1, 0),
pea.EmailAddress,
IIF (pp.BusinessEntityID / 10 = 1, pp.ModifiedDate, NULL)
FROM Person.Person AS pp
INNER JOIN
Person.EmailAddress AS pea
ON pp.BusinessEntityID = pea.BusinessEntityID; ابتدا قصد داریم از جدول Contacts بدون استفاده از هیچ ایندکس غیر خوشه‌ای، کوئری بگیریم. نتیجه‌های نشان داده شده‌ی در کوئری حاصل از کد T-SQL زیر به شرح زیر است:
SET STATISTICS IO ON;
SELECT ContactID,
FirstName
FROM dbo.Contacts
WHERE FirstName = 'Catherine';
SET STATISTICS IO OFF;
22 رکورد را واکشی کرده است؛ ولی با خواندن 2866 page ! که این تعداد، تمام صفحات موجود در جدول می‌باشد. بنابراین واکشی این تعداد رکورد از کل رکوردهای موجود در جدول (19000) نیاز به چک کردن همه‌ی صفحات را خواهد داشت که واقعا روش بهینه‌ای نمی‌باشد.
همانطور که در تصویر پلن کوئری بالا هم مشخص است، کل ایندکس خوشه دار ما Scan شده است که هزینه‌ی بالایی خواهد داشت.
حال با کد T-SQL زیر یک ایندکس غیر خوشه دار را بر روی فیلد FirstName ایجاد خواهیم کرد:
CREATE INDEX IX_Contacts_FirstName ON dbo.Contacts(FirstName);
اگر دوباره کوئری قبلی را اجرا کنیم، به نتایج خیلی بهتری خواهیم رسید و تعداد صفحات خوانده شده به 2 کاهش یافته است!
Sql Server این بار به جای اسکن دوباره‌ی ایندکس خوشه دار، با استفاده از Index Seek و بهره بردن از ایندکس ایجاد شده‌ی توسط ما، یک پلن قابل قبول را برای ما
ارائه داده است.
Index Intersection
در برخی از سناریوها لازم است یکسری ستون دیگر هم علاوه بر ستونی که ایندکس را بر روی آن تعریف کرده‌ایم، در بخش شرط یا خروجی select استفاده شوند. یکی از راه‌حل‌ها، ایجاد یک ایندکس غیر خوشه‌ای که سایر ستون‌ها را نیز Include می‌کند، می‌باشد. با وجود ایندکس‌هایی که هر کدام از آنها می‌توانند برای ادا کردن بخشی از شروط، نقش ایفا کنند، Sql Server هم با به کار بردن آنها می‌تواند رکوردهایی که در فصل مشترک حاصل از جسجتوی این ایندکس‌ها بدست آمده را به عنوان خروجی کوئری ما بازگشت دهد. این عملیات Index Intersection نام دارد. به مثال زیر توجه کنید:
SET STATISTICS IO ON;
SELECT ContactID,
FirstName,
LastName
FROM dbo.Contacts
WHERE FirstName = 'Catherine'
AND LastName = 'Cox';
SET STATISTICS IO OFF; در کوئری بالا علاوه بر FirstName که یک ایندکس غیر خوشه دار را بر روی آن ایجاد کرده‌ایم، فیلد LastName را هم در بخش Select و شرط، مطرح کرده‌ایم. حالا اگر آن را اجرا کنیم، به آمار و پلن زیر دست خواهیم یافت:
بله تعداد Pageهای خوانده شده این بار به 68 افزایش یافته است که نسبت به حالت بدون LastName که 2 Page خوانده شده بود، زیاد است. همانطور که در پلن زیر مشخص است، به دلیل ایندکسی که برروی FirstName ایجاد کرده‌ایم، نمی‌تواند تمام داده‌های مورد نیاز کوئری را مهیا کند. عملیات Key Lookup و nested loop هم این بار اضافه شده‌اند. Sql Server همچنان استفاده از ایندکس موجود را در کنار Key Lookup از ایندکس خوشه دار، ارزان‌تر از اسکن ایندکس خوشه دار، تشخیص داده است.
مشکل
زمانی گریبان گیر ما خواهد شد که به ازای هر مطابقتی در ایندکس غیر خوشه دار، یک
بار به ایندکس خوشه دار برای بررسی شرط بعدی و واکشی دیتا، رجوع خواهد شد. باید
دقت کرد که Key
Lookup همیشه به عنوان مشکل مطرح نمی‌شود. ولی باعث افزایش
غیرضروری هزینه‌های CPU و I/O برای کوئری خواهد شد.
برای
استفاده از الگوی Index Intersection، یک ایندکس غیر خوشه دار
برروی ستون LastName ایجاد خواهیم کرد:
CREATE INDEX IX_Contacts_LastName ON dbo.Contacts(LastName);
اگر این بار کوئری قبل
را اجرا کنیم، به آمار و پلن زیر خواهیم رسید:
بله تعداد Pageهای خوانده شده به 5 کاهش یافته و این بار به جای استفاده از Key Lookup، از دو index seek استفاده کرده است که هزینه‌ای کمتر را نسبت به حالت قبل خواهد داشت. به دلیل اینکه این دو ایندکس تمام دیتای لازم را می‌توانند مهیا کنند، دیگر نیازی به رجوع به ایندکس خوشه دار نخواهد بود. تصویر زیر در درک پلن بالا و این الگو می‌تواند مفید باشد:
Multiple Columns
در دو
الگوی قبل، بیشتر به ایجاد ایندکس، بر روی یک ستون متمرکز شده بودیم. اگر تعدادی از
ستون‌ها در بخش شروط مربوط به کوئری مطرح شوند، بهتر است آنها
را در قالب یک ایندکس نگهداری کنیم. برای نشان دادن تأثیر این مورد، یک
ایندکس غیر خوشه دار را بر روی دو ستون ایجاد میکنیم:
CREATE INDEX IX_Contacts_FirstNameLastName
ON dbo.Contacts(FirstName, LastName);
SET STATISTICS IO ON;
SELECT ContactID,
FirstName,
LastName
FROM dbo.Contacts
WHERE FirstName = 'Catherine'
AND LastName = 'Cox';
SET STATISTICS IO OFF; با اجرای کوئری بالا به
آمار و پلن زیر خواهیم رسید:
باید توجه داشت هر زمان که نیاز است یکسری فیلد، در قسمت شرطی خیلی از کوئری‌ها تکرار شوند، ایجاد کردن یک ایندکس برروی آنها به صورت یکجا، ایده‌ی خوبی خواهد بود.
الگوی Multiple Columns هم به مانند الگوی Search Columns باید هنگام ایندکس گذاری دیتابیس در نظر گرفته شود و از اهمیت بالایی برخوردار است. باید توجه داشت اگر فیلدهایی که در قسمت شرطی کوئری مطرح می‌شوند، متغییر باشد، استفاده از الگوی Index Intersection مفید خواهد. ولی برای مواقعی که نیاز است یکسری فیلد به صورت یکجا در بخش شرطی کوئری مطرح شوند، الگوی Multiple Columns کارآیی بهتری خواهد داشت. از این دو الگوی مطرح شده که در تناقض باهم قرار دارند، می‌توان به نحوی استفاده برد تا هزینه‌ی کلی را کاهش داد.
Covering Index
الگوی بعدی، ایندکس پوشش دهنده نام گرفته است. همانند نامی که دارد، هدف آن نگهداری یکسری ستون در ستون‌های ایندکس تولیدی که اتفاقا این ستون‌ها در قسمت شرطی کوئری قرار ندارند، ولی قرار است به عنوان خروجی Select برگردانده شوند، می‌باشد.
این الگو به عنوان یک روش استاندارد ایندکس گذاری در Sql Server مطرح بوده است. البته در ادامه و با بروز شدن روش‌هایی که می‌توان ایندکس‌ها را ایجاد کرد، این الگو نسبت به قبل کمتر مفید است! از آن جهت که یک روش شناخته شده می‌باشد، در این قسمت این مورد را هم مطرح کردیم. به مثال زیر توجه کنید:
SET STATISTICS IO ON;
SELECT ContactID,
FirstName,
LastName,
IsActive
FROM dbo.Contacts
WHERE FirstName = 'Catherine'
AND LastName = 'Cox';
SET STATISTICS IO OFF; در کوئری بالا این بار قصد داریم خصوصیت IsActive را که در ایندکس IX_Contacts_FirstNameLastName نگهداری نمی‌شود و همچنین در قسمت شرطی هم مطرح نشده و نیازی به آن نبوده، هم واکشی کنیم. با توجه به نتایج بدست آمده که در آمار و پلن زیر مشخص است، باز هم تعداد Pageهای خوانده شده به 5 افزایش یافته و بار دیگر، Key Lookup و Nested Loop را در کنار یک Index Seek، برروی ایندکسی که با الگوی Multiple Columns ایجاد کرده‌ایم، خواهیم داشت.
الگوی index covering پیشنهاد می‌کند ستونی را هم که در قسمت شرطی مطرح نمی‌شود، به عنوان ستونی اصلی در ایندکس، نگهداری کنیم؛ به شکل زیر:
CREATE INDEX IX_Contacts_FirstNameLastNameIsActive ON dbo.Contacts(FirstName, LastName,IsActive)
ایندکس غیر خوشه دار بالا، 3 فیلدی را که قرار است در بخش شرطی مطرح شوند، یا به عنوان خروجی Select برگردانده شوند، در بر می‌گیرد. سپس کوئری قبلی را دوباره اجرا میکنیم. به نتایج زیر خواهیم رسید:
باز هم هزینه‌ی Key Lookup حذف شده و این بار از ایندکس جدید ما استفاده شده و تعداد Pageهای خوانده شده هم به 2 کاهش یافته است.
این الگو در بیشتر سناریوها کاملا مفید بوده و پتانسیل افزایش کارآیی را در بیشتر سناریوها دارد. اما در سال‌های اخیر از زمانیکه امکانات جدیدی در Sql Server 2005 به بعد ایجاد شد، از استفاده‌ی آن کاسته شده است. با وجود این امکانات جدید که در الگوی بعد به آن خواهیم پرداخت، می‌توان ستون‌های اضافی را در ایندکس‌ها، Include کنیم و نیازی نیست که جزء ستون‌های اصلی ایندکس باشند.
Included Columns
الگوی Included Columns درواقعا پسر عموی الگوی Covering Index می‌باشد. در این الگو از عبارت INCLUDE در ایجاد یا تغییر ایندکس استفاده می‌شود و از این طریق امکان این را مهیا می‌کند تا یکسری ستون که جز ستون‌های اصلی ایندکس نیستند هم در ایندکس غیر خوشه دار ما افزوده شوند و حتی در قسمت شرطی هم مطرح شوند. این عمل خیلی شبیه به نگهداری دیتا‌های غیر کلیدی در یک ایندکس خوشه دار می‌باشد و این همان تفاوت اصلی بین دو الگو مطرح شده است.
اگر کوئری زیر را اجرا کنیم:
SET STATISTICS IO ON;
SELECT ContactID,
FirstName,
LastName,
EmailAddress
FROM dbo.Contacts
WHERE FirstName = 'Catherine';
SET STATISTICS IO OFF; 68 Page خوانده شده خواهیم داشت که حاصل یک Index Seek بر روی ایندکس IX_Contacts_FirstName می‌باشد و برای واکشی بقیه ستون‌ها هم یک Key Lookup بر روی ایندکس خوشه دار در پلن مشخص خواهد بود.
علاوه بر ایندکس‌های ایجاد شده‌ی در مراحل قبل، حال یک ایندکس غیر خوشه‌ای را با استفاده از الگوی INC ایجاد میکنیم:
CREATE INDEX IX_Contacts_FirstNameINC ON dbo.Contacts(FirstName) INCLUDE (LastName, IsActive, EmailAddress);
دوباره کوئری قبلی را اگر اجرا کنیم، نتایج به دست آمده، به شرح زیر خواهد بود:
این بار از ایندکس جدید ایجاد شده استفاده شده و تعداد Pageهای خوانده شده، به 3 کاهش یافته است. با توجه به انعطاف پذیری این الگو می‌توان از اندک افزایشی که در تعداد Pageهای خوانده شده نسبت به الگوی ایندکس پوشش دهنده وجود دارد، چشم پوشی کرد.
در مثال‌های قبل چندین ایندکس بر روی جدول Contacts ایجاد کرده‌ایم که 4 مورد از آنها به صورت اختصاصی بر روی فیلد FirstName بوده است. باید توجه کرد این ایندکس‌ها نیاز به فضا و نگهداری در مواقع ویرایش رکوردهای جدول خواهند داشت. لذا این هزینه‌ها اثر منفی برروی تمام عملیاتی خواهند داشت که روی جدول انجام می‌شود.
الگوی INC می‌تواند این مشکل را برطرف کند. برای مثال با استفاده از آن می‌توان ایندکس‌های تولید شده‌ی در مراحل قبل را بر روی FirstName، توسط یک ایندکس نیز پوشش داد. لذا ایندکس‌های قبلی را حذف کرده و با یکسری کوئری، مشخص خواهیم کرد که گفته‌ی ما صحت دارد:
IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.Contacts')
AND name = 'IX_Contacts_FirstNameLastName')
DROP INDEX IX_Contacts_FirstNameLastName ON dbo.Contacts
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.Contacts')
AND name = 'IX_Contacts_FirstNameLastNameIsActive')
DROP INDEX IX_Contacts_FirstNameLastNameIsActive ON dbo.Contacts
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.Contacts')
AND name = 'IX_Contacts_FirstName')
DROP INDEX IX_Contacts_FirstName ON dbo.Contacts
GO با کدهای بالا ایندکس‌هایی را که بر روی FirstName ایجاد شده بودند، حذف کرده و این بار تمام کوئری‌های مطرح شده‌ی در مراحل قبل را یکبار دیگر اجرا میکنیم:
SET STATISTICS IO ON;
SELECT ContactID,
FirstName
FROM dbo.Contacts
WHERE FirstName = 'Catherine';
SELECT ContactID,
FirstName,
LastName
FROM dbo.Contacts
WHERE FirstName = 'Catherine'
AND LastName = 'Cox';
SELECT ContactID,
FirstName,
LastName,
IsActive
FROM dbo.Contacts
WHERE FirstName = 'Catherine'
AND LastName = 'Cox';
SET STATISTICS IO OFF; دو نکته‌ای که باید به آنها توجه کرد:
- کوئری‌ها بالا در مقایسه با الگوهای قبلی به چه شکلی اجرا خواهند شد؟
- توجه کردن به تعداد Pageهای خوانده شده
در جواب مورد دوم، با اینکه حدود 50% افزایش در تعداد Pageهای خوانده شده نسبت به حالتی که به صورت جدا از هم برای هر کوئری خاص یک ایندکس در نظر گرفته بودیم، داشته‌ایم ولی این تغییر کارآیی نمی‌تواند ساخت 4 ایندکس را به جای 1 ایندکس که تمام آنها را پوشش می‌دهد، توجیه کند! در حالیکه ما به کارآیی مورد نظر خود دست یافته‌ایم.
در نتیجه الگوی INC هنگام ساخت ایندکس‌های غیر خوشه دار خیلی مهم است و باید به آن توجه زیادی کرد. بیشتر در مواقعی‌که نیاز است عملیات Lookup را حذف کنید و سرعت خواندن و کارآیی اجرای کوئری را افزایش دهید، این الگو مناسب خواهد بود. همچنین با کاهش تعداد ایندکس‌ها برای پوشش دادن ایندکس‌های لازم برای کوئری‌ها مشابه، باید توجه کرد که باز هم نسبت به حالتی که هیچ ایندکس غیر خوشه داری ایجاد نشده، کارآیی افزایش می‌یابد.
Filtered Indexes
ممکن است در برخی از جداول دیتابیس، یکسری رکوردهایی با مقدار‌هایی که به ندرت یا هرگز از آنها در یک برنامه‌ی کاربردی استفاده نخواهد شد، ذخیره شده باشند. در این مواقع، حذف آنها از نتیجه‌ی خروجی کوئری‌ها می‌تواند خیلی مفید باشد. یا در مواقعی می‌توان از این مورد برای مشخص کردن یک زیر مجموعه‌ی از داده‌های جدول، برای ایجاد ایندکس استفاده کرد. همچنین می‌توان به جای کوئری زدن بر روی میلیون‌ها رکورد موجود در جدول، ایندکس‌ها را طوری ایجاد کرد که پوشش دهنده‌ی بخشی از دیتای چند میلیونی باشند.
SET STATISTICS IO ON;
SELECT ContactID,
FirstName,
LastName,
CertificationDate
FROM dbo.Contacts
WHERE CertificationDate IS NOT NULL
ORDER BY CertificationDate;
SELECT ContactID,
FirstName,
LastName,
CertificationDate
FROM dbo.Contacts
WHERE CertificationDate BETWEEN '20050101' AND '20050201'
ORDER BY CertificationDate;
SET STATISTICS IO OFF; 
زمانیکه مقدار آن نال باشد، استفاده نخواهد شد. آیا عقل سلیم قبول می‌کند که این مقادیر نال را در ایندکس نگهداری و رکوردهایی با مقادیر نال داشته باشیم؟ برای پیاده سازی این الگو باید از عبارت Where به هنگام ساخت ایندکس‌های غیر خوشه‌ای استفاده کنیم.
توجه کنید که امکان استفاده از مقادیر متغیر در بخش Where، وجود ندارد.
نکته‌ی بعدی این است که نمیتوان مقایسه‌های پیچیده را در این مورد استفاده کرد. برای مثال استفاده از LIKE و BETWEEN امکان پذیر نیست.
این بار با استفاده از الگوی Filtered Indexes یک ایندکس غیر خوشه‌ای را بر روی ستون CertificationDate ایجاد میکنیم:
CREATE INDEX IX_Contacts_CertificationDate ON dbo.Contacts(CertificationDate) INCLUDE (FirstName, LastName) WHERE CertificationDate IS NOT NULL;
حال دوباره دو کوئری قبلی را اجرا میکنیم. آمار و پلن زیر نشان می‌دهند که این بار فقط 2 عدد Page خوانده شده است و عملیات به Index Seek بر روی ایندکس جدید تغییر کرده است.
یکسری از مزایای نگهداری فقط زیر مجموعه‌ای از رکوردهای جدول در ایندکس، به شرح زیر است:
- کم شدن تعداد رکوردهای ایندکس‌ها موجب کاهش تعداد Pageهای مورد نیاز برای ذخیره سازی آنها و در نتیجه کاهش حجم مورد نیاز برای ذخیره سازی خواهد شد.
- با توجه به مورد اول، اگر تعداد Pageهای برای نگهداری ایندکس کم باشند، لذا فرصت Fragmentation برای ایندکس کم خواهد بود و در نتیجه، هزینه و تلاش کمی برای نگهداری آن لازم است.
- زمانیکه تعداد مقادیر نگهداری شده‌ی در ایندکس محدود هستند، تعداد Page هایی که برای پیمایش نیاز است، کم خواهند بود و اینجاست که حتی Index Scan هم بروری آن خیلی بهینه‌تر از Index Scan بر روی ایندکس خوشه دار می‌باشد.
- اگر لازم است بر روی یک ستون که به‌صورت نال‌پذیر است، ایندکس ایجاد کنید(دلایل آن پیشتر گفته شد).
- اگر لازم است برروی Sparse Column، یک ایندکس یکتا ایجاد کنید.
- مورد بعدی همان بحث کاهش تعداد رکوردهایی می‌باشد که در ایندکس ذخیره می‌شوند.
- اعتبارسنجی بر روی جدول ParentTable
- اعتبارسنجی بر روی جدول ChildTable
در مورد نوع اول، هر وقت که رکوردهای جدول ChildTable تغییر کند، در این صورت مقدار ParentID موجود جدول ChildTable با یک جستجو در جدول ParentTable اعتبارسنجی خواهد شد. از آنجایی که این کلید خارجی در جدول ParentTable یک کلید اصلی بوده، یک ایندکس خوشه دار بر روی آن ایجاد شده است و تأثیری در کاهش کارآیی نخواهد داشت.
در مورد نوع دوم، هروقت تغییراتی بر روی ParentID موجود در جدول ParentTable داشته باشیم، نیاز است اعتبار سنجی بر روی جدول ChildTable انجام شود. برای مثال با حذف یک رکورد در جدول پدر، لازم است که جدول فرزند بررسی کند که آیا این ParentID در رکوردها موجود استفاده شده است یا خیر؟ در این نوع از اعتبارسنجی، الگوی Foreign Key خود را نشان می‌دهد.
برای نشان دادن استفاده‌ی از این الگو، لازم است جداول مطرح شده‌ی در تصویر بالا را ایجاد کنیم:
USE AdventureWorks2012;
GO
CREATE TABLE dbo.Customer (
CustomerID INT ,
FillterData CHAR (1000),
CONSTRAINT PK_Customer_CustomerID PRIMARY KEY CLUSTERED (CustomerID)
);
CREATE TABLE dbo.SalesOrderHeader (
SalesOrderID INT ,
OrderDate DATETIME ,
DueDate DATETIME ,
CustomerID INT ,
FillterData CHAR (1000),
CONSTRAINT PK_SalesOrderHeader_SalesOrderID PRIMARY KEY CLUSTERED (SalesOrderID),
CONSTRAINT GK_SalesOrderHeader_CustomerID_FROM_Customer FOREIGN KEY (CustomerID) REFERENCES dbo.Customer (CustomerID)
); کد T-SQL بالا دو جدول مشتری و سفارش را ایجاد کرده و یک ارتباط یک به چند مابین آنها را از سمت مشتری به سفارش ایجاد می‌کند. برای انجام آزمایش خود، یکسری دیتای موجود را هم از جداول دیتابیس AdventureWorks2012 در جداول بالا درج میکنیم:
INSERT INTO dbo.Customer (CustomerID)
SELECT CustomerID
FROM Sales.Customer;
INSERT INTO dbo.SalesOrderHeader (SalesOrderID, OrderDate, DueDate, CustomerID)
SELECT SalesOrderID,
OrderDate,
DueDate,
CustomerID
FROM Sales.SalesOrderHeader; در واقع می‌خواهیم نشان دهیم که در زمان تغییر یک رکورد از جدول Customers، چه اتفاقاتی می‌افتد. برای مثال این تغییر می‌تواند حذف یک رکورد باشد که به شکل زیر آن را انجام خواهیم داد:
SET STATISTICS IO ON; DELETE dbo.Customer WHERE CustomerID = 701; SET STATISTICS IO OFF;
آمار و پلن زیر نشان می‌دهد که برای حذف یک رکورد در جدول مشتری، چون از عملیات Index Seek برروی ایندکس خوشه دار موجود برروی ستون CustomerID استفاده شده است، تنها 3 Page خوانده شده‌است؛ ولی برای اعتبارسنجی برروی جدول سفارش، با خواندن 4513 page و انجام عملیات Index Scan برروی ایندکس خوشه دار باعث کاهش کارآیی شده است.
برای پیاده سازی الگوی کلیدخارجی یک ایندکس غیر خوشه‌ای را بر روی CustomerID در جدول سفارشات ایجاد میکنیم:
CREATE INDEX IS_SalesOrderHeader_CustomerID ON dbo.SalesOrderHeader(CustomerID)
اگر دوباره کوئری بالا را با یک CustomerID دیگر انجام دهیم، به نتایج بهتری دست خواهیم یافت. تعداد Pageهای خوانده شده‌ی برای اعتبارسنجی جدول سفارشات، به عدد 2 کاهش یافته است! و از یک عملیات Index Seek بر روی ایندکس ایجاد شده، استفاده شده است.
اگر از EF استفاده می‌کنید، در حال حاضر به غیر از الگوهای Filtered Indexes و Include Indexes، پیاده سازی بقیه الگوهای ذکر شده به صورت توکار پشتیبانی می‌شود. برای دو الگوی مذکور هم می‌توان از نوشتن T-SQL خام استفاده کرد. برای مثال:
public partial class AddIndexes : DbMigration
{
private const string IndexName = "IX_LogSamples";
public override void Up()
{
Sql(String.Format(@"CREATE NONCLUSTERED INDEX [{0}]
ON [dbo].[Logs] ([SampleId],[Date])
INCLUDE ([Value])", IndexName));
}
public override void Down()
{
DropIndex("dbo.Logs", IndexName);
}
} یا حتی خیلی تمیزتر و با ایده گرفتن از این مطلب می‌توان به یک کد Refactoring friendly نیز دست یافت.
پ.ن: این مطلب خلاصه‌ای از فصل 8 کتاب Expert Performance Indexing for SQL Server 2012 می‌باشد.
