مقدمه
در قسمتهای اول و دوم دیدید که چگونه با استفادهی نامناسب از عملگر 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 به شما اجازه تعریف ایندکس را نخواهد داد.
در قسمت بعدی با یکی دیگر از قاتلین ایندکسها آشنا خواهید شد.
3 دیدگاه
حسن ضرابی
با سلام و خسته نباشید خدمت جناب آقای مهندس مهدی شیشه بری
از این مقاله خوب بسیار ممنونم
jml.maryam
با سلام و عرض ارادت
ممنونم از مقالتون،
مهندس شیشه بری، چنانچه بعد از مدتی مقادیر یکی از جز فیلدهای مشارکت کننده در ستونهای محاسباتی(که ایندکس براش تعریف شده) تغییر کنه، آیا مقدارش در ایندکسش هم اصلاح میشه؟
آرزو محمدزاده
با سلام و عرض ادب
همواره این اصل را بهخاطرداشته باشید که بهازای هر یک از اعمال دستکاری رکوردهای جداول از قبیل UPDATE، INSERT و DELETE همان اتفاق نیز بهازای رکوردهای موجود در ایندکسها خواهد افتاد.
برای آنکه این موضوع را بررسی کنید مثلا میتوانید بلافاصله پس از بهروزرسانی مقدار رکوردی که دارای ایندکس است از دستور زیر استفاده کرده و محتوای لاگ فایل را بررسی کنید .
SELECT * FROM fn_dblog (NULL,NULL);
GO
در این حالت خواهید دید که علاوه بر ثبت بهروزرسانی مقدار رکورد در لاگ فایل، بهروزرسانی مقدار ایندکس متناظر نیز لحاظ خواهد شد.
پیشنهاد میکنم حتما محصول جنون سرعت نیک آموز را تهیه کنید. این محصول علاوه بر قیمت مناسب دارای اطلاعات بسیار ارزشمندی است.