قاتلین ایندکس‌ها در SQL Server [بخش سوم]

مقدمه

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

نکته مهم: تمامی کوئری‌های این مجموعه مقالات، بر روی دیتابیس AdventureWorks2016 و در محیط SQL Server Management Studio 2016 و 2017 اجرا شده است.

قاتل شماره 3 – ستون‌های محاسباتی (Computed Columns)

همان‌طور که می‌دانید در برخی از موارد مقادیر یک یا چندین ستون از جدولی به‌صورت عبارت یا Expression تعریف می‌شوند. چنین مقادیری ممکن است در حین اجرای یک کوئری و برای جلوگیری از محاسباتِ زمانِ اجرا مورد استفاده قرار گیرند. از طرفی چنین ستون‌هایی می‌توانند با ستون‌های دیگری وابستگی داشته باشند تا به‌محض هرگونه تغییری در سایر ستون‌ها، تغییرات بر روی آن‌ها نیز اِعمال شود. به چنین ستون‌هایی Computed Columns گفته می‌شود. زمانی که شما مجبور هستید تا نتیجه اجرای یک تابع یا محاسبات مبتنی بر چندین ستون را در جدول‌تان نگهداری کنید، استفاده از ستون‌های محاسباتی می‌تواند برای شما مفید واقع شود.
همواره به این نکته مهم توجه داشته باشید که قابلیت استفاده از ایندکس‌های موجود بر روی ستون‌هایی که بر اساس آن‌ها ستون‌های محاسباتی شکل گرفته است، امکان پذیر نخواهد بود! برای بررسی این موضوع، ابتدا دو ستون محاسباتی جدید را برای جدول Person درنظر می‌گیریم. اولین ستون محاسباتی شامل ترکیبی از فیلدهای نام و نام‌خانوادگی است اما دومین ستون محاسباتی معنای خاصی نداشته و صرفا جهت بررسی یک سناریو تعریف می‌شود.

[sql]

USE AdventureWorks2016
GO
ALTER TABLE Person.Person
ADD FirstLastName AS (FirstName + ‘ ‘ + LastName)
,CalculateValue AS (BusinessEntityID * EmailPromotion);

[/sql]

اکنون کوئری‌های زیر را اجرا می‌کنیم.

[sql]

SET STATISTICS IO ON

SELECT
BusinessEntityID, FirstName, LastName, FirstLastName
FROM Person.Person
WHERE FirstLastName = ‘Gustavo Achong’;

SELECT
BusinessEntityID, CalculateValue
FROM Person.Person
WHERE CalculateValue = 198;

[/sql]

با مراجعه به Plan اجرایی این کوئری‌ها خواهیم دید که جهت بازیابی رکوردها از عملیات Scan استفاده شده است. در قسمت‌های قبلی این مجموعه مقالات به شما تذکر دادم که این عملیات با افزایش حجم دیتابیس، رکوردهای جدول و تعداد کاربران هم‌زمان می‌تواند احتمال بروز پدیده Blocking و استفاده از I/O های بیشتر برای درخواست‌ها را فراهم کند. این موضوع در بخش آمار و اطلاعات I/O نیز قابل مشاهده است که چگونه برای بازیابی نتایج موردنظر، تمامی رکوردها مورد ارزیابی قرار گرفته شده است.

با توجه به عدم استفاده از ایندکس ستون‌های مبداء در ستون‌های محاسباتی، اکنون برای این دو ستونِ جدید ایندکس‌هایی از نوع NONCLUSTERED تعریف می‌کنیم.

[sql]

CREATE INDEX IX_PersonPerson_FirstLastName
ON Person.Person(FirstLastName);

CREATE INDEX IX_PersonPerson_CalculateValue
ON Person.Person(CalculateValue);

[/sql]

با اجرای مجدد کوئری‌ها خواهیم دید که بازیابی رکوردها از طریق عملیات Index Seek انجام شده است. مقایسه آمار و اطلاعات I/O نیز نشان از کاهش چشمگیر تعداد Page های خوانده‌شده در این عملیات خواهد داشت.

حتما این نکته مهم را به‌خاطر بسپارید که ایندکس‌گذاری بر روی ستون‌های محاسباتی مستلزم آن است که عبارات موجود در آن‌ها به‌صورت قطعی یا Deterministic باشد؛ یعنی در هر زمان از اجرای آن عبارت، نتایج مشابهی حاصل شود. به‌عنوان مثال استفاده از تابع GETDATE() موجب عدم استفاده از قابلیت ایندکس‌گذاری بر روی چنین ستون‌هایی خواهد شد و SQL Server به شما اجازه تعریف ایندکس را نخواهد داد.

در قسمت بعدی با یکی دیگر از قاتلین ایندکس‌ها آشنا خواهید شد.

[download id=”28796″]