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

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

نوشته شده توسط: مهدی شیشه بری
تاریخ انتشار: ۱۲ شهریور ۱۳۹۶
آخرین بروزرسانی: ۱۴ مهر ۱۴۰۲
زمان مطالعه: 6 دقیقه
۵
(۲)

مقدمه

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

دوره کوئری نویسی نیک آموز

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

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

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

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

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;

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

CREATE INDEX IX_PersonPerson_FirstLastName
ON Person.Person(FirstLastName);
CREATE INDEX IX_PersonPerson_CalculateValue
ON Person.Person(CalculateValue);

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

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

چه رتبه ای می‌دهید؟

میانگین ۵ / ۵. از مجموع ۲

اولین نفر باش

title sign
دانلود مقاله
قاتلین ایندکس‌ها در SQL Server [بخش سوم]
فرمت PDF
5 صفحه
حجم 1 مگابایت
دانلود مقاله
title sign
معرفی نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

    • با سلام و عرض ارادت
      ممنونم از مقالتون،
      مهندس شیشه بری، چنانچه بعد از مدتی مقادیر یکی از جز فیلدهای مشارکت کننده در ستونهای محاسباتی(که ایندکس براش تعریف شده) تغییر کنه، آیا مقدارش در ایندکسش هم اصلاح میشه؟

      • با سلام و عرض ادب
        همواره این اصل را به‌خاطرداشته باشید که به‌ازای هر یک از اعمال دست‌کاری رکوردهای جداول از قبیل UPDATE، INSERT و DELETE همان اتفاق نیز به‌ازای رکوردهای موجود در ایندکس‌ها خواهد افتاد.
        برای آن‌که این موضوع را بررسی کنید مثلا می‌توانید بلافاصله پس از به‌روزرسانی مقدار رکوردی که دارای ایندکس است از دستور زیر استفاده کرده و محتوای لاگ فایل را بررسی کنید .
        SELECT * FROM fn_dblog (NULL,NULL);
        GO
        در این حالت خواهید دید که علاوه بر ثبت به‌روزرسانی مقدار رکورد در لاگ فایل، به‌روزرسانی مقدار ایندکس متناظر نیز لحاظ خواهد شد.
        پیشنهاد می‌کنم حتما محصول جنون سرعت نیک آموز را تهیه کنید. این محصول علاوه بر قیمت مناسب دارای اطلاعات بسیار ارزشمندی است.

    • با سلام و عرض ارادت
      ممنونم از مقالتون،
      مهندس شیشه بری، چنانچه بعد از مدتی مقادیر یکی از جز فیلدهای مشارکت کننده در ستونهای محاسباتی(که ایندکس براش تعریف شده) تغییر کنه، آیا مقدارش در ایندکسش هم اصلاح میشه؟

      • با سلام و عرض ادب

        همواره این اصل را به‌خاطرداشته باشید که به‌ازای هر یک از اعمال دست‌کاری رکوردهای جداول از قبیل UPDATE، INSERT و DELETE همان اتفاق نیز به‌ازای رکوردهای موجود در ایندکس‌ها خواهد افتاد.
        برای آن‌که این موضوع را بررسی کنید مثلا می‌توانید بلافاصله پس از به‌روزرسانی مقدار رکوردی که دارای ایندکس است از دستور زیر استفاده کرده و محتوای لاگ فایل را بررسی کنید .
        SELECT * FROM fn_dblog (NULL,NULL);
        GO
        در این حالت خواهید دید که علاوه بر ثبت به‌روزرسانی مقدار رکورد در لاگ فایل، به‌روزرسانی مقدار ایندکس متناظر نیز لحاظ خواهد شد.
        پیشنهاد می‌کنم حتما محصول جنون سرعت نیک آموز را تهیه کنید. این محصول علاوه بر قیمت مناسب دارای اطلاعات بسیار ارزشمندی است.

    • با سلام و خسته نباشید خدمت جناب آقای مهندس مهدی شیشه بری
      از این مقاله خوب بسیار ممنونم

    • با سلام و خسته نباشید خدمت جناب آقای مهندس مهدی شیشه بری
      از این مقاله خوب بسیار ممنونم

هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link
ثبت‌نام دوره‌های آنلاین تابستانه نیک‌آموز شروع شد 🏄
مشاهده سرفصل و رزرو رایگان
close-image