اشتباهات ایندکس در SQL Server و تأثیر آن بر عملکرد کوئری‌ها

در این مقاله از نیک آموز به سراغ اشتباهات ایندکس در SQL Server خواهیم رفت. توجه داشته باشید که موارد ذکر‌شده در این مقاله، تنها بخشی از معضلاتی هستند که می‌توانند موجب عدم استفاده از قابلیت‌های ایندکس در SQL Server در حین اجرای کوئری‌ها شوند. 
نکته مهم: تمامی کوئری‌های این مجموعه مقالات، بر روی دیتابیس AdventureWorks2016 و در محیط SQL Server Management Studio 2016 و ۲۰۱۷ اجرا شده است. 

اشتباهات در ایندکس در SQL Server شماره ۵ – (Data Conversion) 

یکی از مواردی‌که می‌تواند تاثیری منفی در عملکرد ایندکس در SQL Server داشته باشد. زمانی است که نوع‌داده یا Data Type ستون‌ها در حین عملیات‌های JOIN و یا در بخش WHERE مورد تغییر واقع شود. در این شرایط SQL Server مجبور است تا با تبدیل انواع‌ داده، انطباق و تشابه میان نوع‌ داده‌ی ستون‌های شرکت‌ کننده در کوئری‌ها را برقرار سازد.
اگر چنین تبدیلاتی به‌صورت صریح (Explicit) در متن کوئری قید نشده باشد. آنگاه SQL Server مجبور است تا چنین تبدیلاتی را در پس‌زمینه‌ی فرایند اجرای کوئری و به‌صورت ضمنی (Implicit) انجام دهد. این تبدیلات (منظور، انواع تبدیلات مجاز در SQL Server است) بر اساس اولویت انواع‌داده انجام خواهد شد. شما می‌توانید این موضوع را در لینک زیر دنبال کنید:

https://technet.microsoft.com/en-us/library/ms190309(v=sql.110).aspx

اما این عملیات چگونه تاثیری منفی در روند اجرای یک کوئری خواهد داشت؟ به‌عنوان مثال اگر قرار باشد ستونی را که دارای نوع‌داده VARCHAR بوده و از قبل بر روی آن ایندکسی پیاده‌سازی شده، در حین اجرای کوئری به نوع NVARCHAR تبدیل کنیم آن‌گاه در هنگام ساخت Plan کوئری به‌هیچ عنوان اطلاعات و آمار مربوط به ایندکسِ مقادیر آن ستون، در تصمیم‌گیری و تعیین میزان فراوانی و موقعیت مقادیر آن ستون، مفید واقع نخواهد شد. برای نشان دادن این موضوع، سناریو زیر را برای‌تان تشریح خواهم کرد:
ابتدا به‌کمک دستور Make Table Query، برخی از ستون‌های جدول Person را در جدولی با عنوان PersonPerson کپی کنید.

USE AdventureWorks2014
GO
SELECT
BusinessEntityID
,CAST(FirstName as varchar(50)) as FirstName
,CAST(MiddleName as varchar(50)) as MiddleName
,CAST(LastName as varchar(50)) as LastName
INTO PersonPerson
FROM Person.Person;
GO

 سپس در جدول PersonPerson یک CLUSTEREDINDEX بر روی فیلد BusinessEntityID و یک Non-CLUSTEREDINDEX بر روی فیلد FirstName تعریف کنید.

CREATE CLUSTERED INDEX IX_PersonPerson_ContactID ON PersonPerson (BusinessEntityID);
CREATE INDEX IX_PersonContact_FirstName ON PersonPerson(FirstName);
GO

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

SET STATISTICS IO ON;
-- Query1
DECLARE @FirstName NVARCHAR(100)
SET @FirstName = 'Gail';
SELECT
FirstName, LastName FROM PersonPerson
WHERE FirstName = @FirstName
OPTION (RECOMPILE);
GO
-- Query2
DECLARE @FirstName VARCHAR(100)
SET @FirstName = 'Gail';
SELECT
FirstName, LastName FROM PersonPerson
WHERE FirstName = @FirstName
OPTION (RECOMPILE);
GO

در کوئری اول، متغیری از نوع‌داده NVARCHAR تعریف شده است. از طرفی نوع‌داده فیلد FirstName در جدول PersonPerson برابر با VARCHAR است. بنابراین این دو نوع‌داده همانند یکدیگر نبوده و می‌بایست این فیلد در حین پردازش کوئری از VARCHAR به NVARCHAR تبدیل شود. (این اتفاق با توجه به قواعدی انجام می‌شود که لینک آن را در ابتدای این مقاله برای‌تان گذاشته‌ام. در حقیقت اولویت نوع‌داده NVARCHAR بالاتر بوده؛ بنابراین در بخش WHERE تبدیل ضمنی صورت گرفته و فیلد FirstName به NVARCHAR تغییر می‌یابد) شما می‌توانید این موضوع را در Plan اجرایی این کوئری مشاهده کنید که چگونه برای بازیابی رکوردها، عملیات تبدیلِ نوع‌داده به‌صورت ضمنی انجام شده است. یشنهاد میکنیم برای درک بهتر مفاهیم کوئری نویسی را مطالعه کنید.

مجددا به Plan اجرایی کوئری اول نگاه کنید. در بخش SELECT این Plan، هشداری با این مضمون که تبدیل نوع‌داده از VARCHAR به NVARCHAR می‌تواند بر روی انتخاب اجرای کوئری از طریق عملیات Index seek تاثیرگذار باشد، به‌چشم می‌خورد.

اما در کوئری دوم، انطباق نوع‌داده میان متغیر تعریف‌شده و فیلد FirstName از جدول PersonPerson برقرار است و همین موضوع باعث می‌شود تا در هنگام ساخت Plan کوئری، از ایندکسِ تعریف‌شده (منظور IX_PersonContact_FirstName) بر روی فیلد FirstName استفاده شود. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

در شکل زیر، مقایسه تعداد Page های خوانده‌شده توسط این دو کوئری را مشاهده می‌کنید. کوئری اول صرفا به دلیل عدم تطابق میان نوع‌داده فیلد FirstName و متغیر تعریف‌شده، نتوانسته است از قابلیت‌های ایندکس استفاده کند و در نتیجه برای اجرای کوئری، تعداد Pageهای بیشتری را خوانده است.

سخن پایانی

ایندکس در SQL Server در این مجموعه مقاله، برداشت آزادی بود از فصل دوازدهم کتاب Expert Performance Indexing in SQL Server که انتشارات Apress آن را ارائه کرده است. پیشنهاد می‌کنیم حتما این کتاب جذاب و فوق‌العاده را مطالعه کنید. امیدواریم این مجموعه مقاله مورد استفاده شما قرار گرفته شده باشد. به‌زودی با مجموعه مقالات دیگری در حوزه ایندکس‌ها در خدمت‌تان خواهم بود. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.