Low Selectivity در ایندکس‌ها چه بلایی سر Performance می‌آورد؟

در بسیاری از سازمان‌ها، وقتی Performance دیتابیس افت می‌کند، اولین واکنش این است:
«روی این ستون ایندکس داریم، پس مشکل نباید از دیتابیس باشد.»
اما وقتی Execution Plan بررسی می‌شود، واقعیت چیز دیگری است.
به جای Index Seek، انواع Scan دیده می‌شود، مصرف CPU بالا می‌رود، IO افزایش پیدا می‌کند و Query Store پر از Planهای ناپایدار می‌شود.
در ظاهر ایندکس وجود دارد، اما در عمل SQL Server تمایلی به استفاده از آن ندارد.
ریشه این تناقض در بسیاری از موارد یک مفهوم کلیدی است: Low Selectivity
ایندکسی که از نظر فیزیکی ساخته شده، اما از نظر منطقی برای Optimizer ارزش تصمیم‌گیری ندارد.

در این مقاله به‌صورت عمیق و عملی بررسی می‌کنیم Low Selectivity چیست، چگونه باعث بی‌اثر شدن ایندکس‌ها می‌شود، چه نشانه‌هایی در سیستم ایجاد می‌کند و DBA حرفه‌ای چگونه با آن برخورد می‌کند.

Selectivity دقیقاً چیست؟

Selectivity معیاری است برای سنجش قدرت یک ستون در محدود کردن تعداد رکوردهای بازگشتی.

به زبان ساده:

• اگر یک شرط فقط تعداد کمی رکورد را برگرداند، Selectivity بالاست
• اگر همان شرط بخش بزرگی از جدول را برگرداند، Selectivity پایین است

مثال ساده:

نکته مهم اینجاست که Optimizer به وجود ایندکس اهمیت نمی‌دهد، به صرفه بودن استفاده از آن اهمیت می‌دهد.
اگر استفاده از ایندکس کاهش چشمگیری در حجم داده خوانده‌شده ایجاد نکند، SQL Server آن را کنار می‌گذارد.

چرا Low Selectivity ایندکس را بی‌ارزش می‌کند؟

وقتی Selectivity پایین است، استفاده از ایندکس معمولاً به این معنی است که:

• تعداد زیادی رکورد همچنان باید خوانده شود
• Random IO افزایش می‌یابد
• هزینه Seek به‌علاوه Lookup از یک Scan ساده بیشتر می‌شود

در نتیجه Optimizer تصمیم می‌گیرد به جای Index Seek از Table Scan یا Index Scan استفاده کند، چون از نظر هزینه کلی به‌صرفه‌تر است.

در این حالت ایندکس وجود دارد، اما از دید موتور پایگاه داده ارزش اقتصادی استفاده ندارد.

مثال از فاجعه Low Selectivity

جدولی با ۵۰ میلیون رکورد در نظر بگیرید و ستونی به نام IsActive که فقط دو مقدار دارد:

• ۱ برای رکوردهای فعال
• ۰ برای رکوردهای غیرفعال

۹۵ درصد رکوردها فعال هستند.

ایندکس:

CREATE INDEX IX_Orders_IsActive ON Orders(IsActive);

کوئری:

SELECT *
FROM Orders
WHERE IsActive = 1;

از دید Optimizer:

• باید تقریباً کل جدول خوانده شود
• ایندکس فقط یک مسیر اضافی ایجاد می‌کند
• Scan ساده‌تر و قابل پیش‌بینی‌تر است

نتیجه: ایندکس نادیده گرفته می‌شود و Scan انجام می‌شود.
این تصمیم اشتباه نیست، طراحی ایندکس اشتباه بوده است.

اشتباه رایج سازمانی: روی هر WHERE ایندکس بساز

یکی از مخرب‌ترین باورهای رایج همین است.
ستونی در WHERE دیده می‌شود، بلافاصله ایندکس ساخته می‌شود، بدون بررسی Selectivity و الگوی واقعی Query.

نتیجه:

• رشد شدید تعداد ایندکس‌ها
• کند شدن عملیات Insert و Update
• افزایش Fragmentation
• مصرف بیشتر فضا و زمان نگهداری
• بدون بهبود واقعی در Performance

ایندکس زیاد نشانه بلوغ نیست، اغلب نشانه نبود تحلیل است.

Low Selectivity فقط مخصوص ستون‌های Boolean نیست

خیلی از DBAها فقط به ستون‌های True/False مشکوک می‌شوند، در حالی که ستون‌های زیر هم معمولاً Selectivity پایینی دارند:

• Status با مقادیر محدود
• Type یا Category
• Gender
• Flagهای کنترلی
• ستون‌های گروه‌بندی سطح بالا

اگر این ستون‌ها به‌تنهایی ایندکس شوند، معمولاً بهبود محسوسی ایجاد نمی‌کنند.

نقش Statistics در تشدید مشکل

Statistics به Optimizer می‌گویند:

• توزیع داده چگونه است
• هر مقدار چند بار تکرار شده
• تخمین Cardinality چگونه انجام شود

اگر Statistics قدیمی یا غیرنماینده باشند، Optimizer ممکن است گاهی Seek و گاهی Scan انتخاب کند، بدون اینکه Query تغییر کرده باشد. این ناپایداری اغلب در کنار Low Selectivity دیده می‌شود.

ترکیب خطرناک:
Low Selectivity + Statistics ضعیف = Performance غیرقابل پیش‌بینی

Composite Index راهکار حرفه‌ای

ستون با Selectivity پایین اگر به‌تنهایی استفاده شود ضعیف است، اما در ترکیب با ستون Selective می‌تواند مؤثر باشد.

به‌جای:

CREATE INDEX IX_Orders_Status ON Orders(Status);

استفاده از:

CREATE INDEX IX_Orders_Status_OrderDate
ON Orders(Status, OrderDate);

در اینجا:

• Status دامنه اولیه را محدود می‌کند
• OrderDate Selectivity واقعی ایجاد می‌کند
• Optimizer دلیل منطقی برای Seek پیدا می‌کند

ترتیب ستون‌ها در Composite Index کاملاً تعیین‌کننده است و باید بر اساس الگوی واقعی Query انتخاب شود.

Filtered Index راه‌حل هوشمندانه برای داده‌های پرتکرار

اگر فقط بخشی از داده‌ها برای سیستم حیاتی هستند، Filtered Index می‌تواند بسیار مؤثر باشد.

CREATE INDEX IX_Orders_Active
ON Orders(OrderDate)
WHERE Status = 'Active';

مزایا:

• حجم ایندکس کوچک می‌ماند
• Selectivity واقعی ایجاد می‌شود
• هزینه Write کاهش می‌یابد
• Queryهای هدفمند سریع‌تر می‌شوند

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

Low Selectivity و Key Lookupهای انفجاری

گاهی ایندکس استفاده می‌شود، اما Selectivity پایین باعث می‌شود تعداد زیادی Key Lookup انجام شود.

سناریو:

• Index Seek انجام می‌شود
• هزاران Lookup برای ستون‌های دیگر اجرا می‌شود
• IO و CPU به‌شدت بالا می‌رود

در ظاهر Seek داریم، اما نتیجه از Scan هم بدتر است.
راهکار در اینجا طراحی Covering Index یا بازنگری در Projection کوئری است.

نشانه‌های واضح وجود مشکل Low Selectivity

اگر این علائم را می‌بینید، احتمالاً با Low Selectivity درگیر هستید:

• Scanهای مکرر روی جدول‌های بزرگ با وجود ایندکس
• ایندکس‌هایی با Usage بسیار پایین
• Planهای ناپایدار در Query Store
• CPU بالا بدون Query پیچیده
• اختلاف شدید Performance بین محیط تست و پروداکشن

این‌ها معمولاً مشکل موتور SQL نیست، مشکل طراحی ایندکس است.

تأثیر در سیستم‌های OLTP و تحلیلی

در OLTP:

• افزایش Lock و Block
• کاهش Throughput
• افزایش Latency

بر BI و گزارش‌گیری:

• Scanهای سنگین
• فشار روی IO و TempDB
• کند شدن Refresh گزارش‌ها

هر دو حالت، ریشه مشترک می‌تواند ایندکس بدون Selectivity باشد.

رویکرد DBA حرفه‌ای به Selectivity

یک DBA بالغ قبل از ساخت ایندکس این سؤالات را می‌پرسد:

• این ایندکس دقیقاً کدام Query را بهبود می‌دهد
• Selectivity واقعی این ستون چقدر است
• آیا Composite منطقی‌تر است
• آیا Filtered Index مناسب‌تر است
• هزینه Write این ایندکس چقدر است

ایندکس ابزار است، نه هدف.

چک‌لیست عملی تشخیص Low Selectivity

• بررسی Histogram در Statistics
• مقایسه Distinct Values با تعداد کل رکوردها
• تحلیل Execution Plan واقعی
• بررسی sys.dm_db_index_usage_stats
• شناسایی Key Lookupهای پرتعداد
• مقایسه هزینه Seek + Lookup با Scan

بدون این تحلیل‌ها، ایندکس‌سازی بیشتر شبیه حدس زدن است تا مهندسی.

جمع‌بندی

Low Selectivity دشمن خاموش Performance است.
ایندکس‌هایی که بدون درک توزیع داده ساخته می‌شوند، نه‌تنها کمکی نمی‌کنند، بلکه هزینه سیستم را بالا می‌برند و تصمیم‌گیری Optimizer را پیچیده‌تر می‌کنند.

Performance پایدار زمانی ایجاد می‌شود که:

• Selectivity به‌درستی تحلیل شود
• ایندکس هدفمند و مبتنی بر Query طراحی شود
• داده، Query و ایندکس به‌صورت یک سیستم دیده شوند

ایندکس زیاد نشانه بلوغ نیست،
ایندکس مؤثر نشانه درک عمیق از رفتار داده است.

سوالات متداول FAQ

۱. Low Selectivity دقیقاً چه تأثیری روی استفاده از ایندکس‌ها دارد؟
وقتی Selectivity پایین است، SQL Server اغلب از ایندکس استفاده نمی‌کند زیرا هزینه Seek + Lookup از یک Scan ساده بیشتر می‌شود و بهینه‌ساز ترجیح می‌دهد کل جدول یا ایندکس را Scan کند.

۲. ستون‌های Boolean همیشه مشکل Low Selectivity ایجاد می‌کنند؟
نه، ستون‌های Boolean فقط نمونه‌ای از Low Selectivity هستند. ستون‌هایی مانند Status با مقادیر محدود، Type یا Category، Flagهای کنترلی و ستون‌های گروه‌بندی سطح بالا نیز می‌توانند Selectivity پایینی داشته باشند و ایندکس آنها بدون ترکیب با ستون‌های Selective مؤثر نباشد.

۳. نقش Statistics در ناپایداری Execution Plan چیست؟
Statistics اطلاعاتی درباره توزیع داده و تعداد مقادیر تکراری ارائه می‌دهند. اگر Statistics قدیمی یا ناقص باشد، Optimizer گاهی تصمیم به Seek و گاهی به Scan می‌گیرد و نتیجه اجرای Query ناپایدار خواهد بود.

۴. Composite Index چگونه مشکل Low Selectivity را حل می‌کند؟
ستون با Selectivity پایین به تنهایی ضعیف است، اما وقتی با ستون‌های Selective دیگر ترکیب شود، Optimizer دلیل منطقی برای Index Seek پیدا می‌کند. ترتیب ستون‌ها در Composite Index باید بر اساس الگوی واقعی Query انتخاب شود.

۵. Filtered Index چه مزایایی دارد؟

Filtered Index فقط روی داده‌های مورد نیاز ایجاد می‌شود، حجم ایندکس کوچک می‌ماند، Selectivity واقعی ایجاد می‌شود، هزینه Write کاهش پیدا می‌کند و Queryهای هدفمند سریع‌تر اجرا می‌شوند.

۶. چرا داشتن تعداد زیاد ایندکس همیشه نشانه بلوغ نیست؟
ایندکس زیاد می‌تواند عملکرد Insert و Update را کاهش دهد، Fragmentation ایجاد کند، فضای بیشتری اشغال کند و بدون تحلیل درست، بهبود واقعی در Performance ایجاد نکند.

۷. Key Lookupهای پرتعداد چگونه رخ می‌دهند؟
گاهی ایندکس استفاده می‌شود، اما Selectivity پایین باعث می‌شود هزاران Lookup برای ستون‌های دیگر اجرا شود. این حالت می‌تواند IO و CPU را به شدت افزایش دهد، حتی بدتر از یک Scan ساده.

۸. چگونه یک DBA حرفه‌ای قبل از ساخت ایندکس تصمیم می‌گیرد؟
DBA قبل از ساخت ایندکس بررسی می‌کند کدام Query بهبود می‌یابد، Selectivity واقعی ستون‌ها چقدر است، آیا Composite یا Filtered Index مناسب‌تر است و هزینه Write چه میزان است.

۹. چه علائمی نشان‌دهنده وجود مشکل Low Selectivity در سیستم است؟
Scanهای مکرر روی جداول بزرگ با وجود ایندکس، ایندکس‌های کم‌استفاده، Planهای ناپایدار در Query Store، مصرف CPU بالا و اختلاف شدید Performance بین محیط تست و پروداکشن از نشانه‌های واضح هستند.

۱۰. Low Selectivity بیشتر در سیستم‌های OLTP یا تحلیلی مشکل‌ساز است؟
در هر دو سیستم تأثیر دارد: در OLTP باعث Lock و Block و کاهش Throughput می‌شود و در BI و گزارش‌گیری Scanهای سنگین و فشار روی IO و TempDB ایجاد می‌کند.

از Tuning حدسی عبور کنید و به معماری مبتنی بر تحلیل داده برسید

اگر در دیتابیس شما ایندکس‌های زیادی وجود دارد اما همچنان Scanهای سنگین، مصرف بالای CPU و ناپایداری Execution Plan مشاهده می‌شود، احتمال زیادی وجود دارد که Low Selectivity یکی از عوامل پنهان مشکل باشد.

تیم تخصصی لاندا با تحلیل Distribution داده‌ها، بررسی Statistics، ارزیابی Execution Planهای واقعی و بازطراحی هدفمند ایندکس‌ها کمک می‌کند ساختار ایندکس از حالت تجربی و حدسی خارج شده و به یک معماری مهندسی‌شده و پایدار تبدیل شود.

اگر می‌خواهید بدانید کدام ایندکس‌ها واقعاً مفیدند، کدام فقط هزینه Write تولید می‌کنند و Performance شما دقیقاً کجا قربانی Selectivity پایین شده است، اکنون زمان یک ارزیابی تخصصی است.

برای دریافت مشاوره و شروع فرآیند Tuning حرفه‌ای SQL Server با کارشناسان لاندا تماس ✆  بگیرید.