انواع ایندکس‌ها در SQL Server

۱. ایندکس خوشه‌ای (Clustered Index)

ویژگی‌ها

• هر جدول تنها می‌تواند یک ایندکس خوشه‌ای داشته باشد، زیرا داده‌ها فقط به یک ترتیب فیزیکی ذخیره می‌شوند.
• معمولاً روی ستون کلید اصلی (Primary Key) به‌صورت پیش‌فرض ایجاد می‌شود.
• به دلیل ذخیره مستقیم داده‌ها در ساختار ایندکس، جستجو با استفاده از آن بسیار سریع است و نیازی به عملیات اضافه (مانند Lookup) ندارد.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(۵۰),
    LastName NVARCHAR(۵۰)
);

مزایا

• عملکرد عالی در کوئری‌های مبتنی بر محدوده (Range Queries) مثل WHERE EmployeeID BETWEEN 50 AND 100.
• مناسب برای جداولی که مرتب‌سازی یا فیلتر بر اساس یک ستون خاص رایج است.

معایب

• درج (INSERT) یا به‌روزرسانی (UPDATE) داده‌ها ممکن است کندتر باشد، زیرا مرتب‌سازی فیزیکی باید حفظ شود.

کاربرد

۲. ایندکس غیرخوشه‌ای (Non-Clustered Index)

ویژگی‌ها

• یک جدول می‌تواند چندین ایندکس غیرخوشه‌ای داشته باشد (حداکثر ۹۹۹ در SQL Server).
• برای هر ردیف، اشاره‌گری به داده اصلی (معمولاً آدرس فیزیکی یا کلید خوشه‌ای) ذخیره می‌شود.
• اگر داده‌های مورد نظر در ایندکس موجود نباشند، عملیات اضافی به نام Key Lookup انجام می‌شود.

CREATE NONCLUSTERED INDEX IX_Employees_LastName
ON Employees (LastName);

مزایا

• انعطاف‌پذیری بالا به دلیل امکان ایجاد چندین ایندکس روی ستون‌های مختلف.
• بهبود عملکرد کوئری‌های جستجو و مرتب‌سازی.

معایب

• فضای ذخیره‌سازی بیشتری نیاز دارد، زیرا داده‌ها در دو مکان (جدول و ایندکس) نگهداری می‌شوند.
• درج و به‌روزرسانی کندتر است، زیرا ایندکس باید همگام‌سازی شود.

کاربرد

۳. ایندکس منحصربه‌فرد (Unique Index)

ویژگی‌ها

• به‌طور خودکار هنگام تعریف قید UNIQUE یا کلید اصلی ایجاد می‌شود.
• از ورود داده‌های تکراری جلوگیری می‌کند.

CREATE UNIQUE NONCLUSTERED INDEX IX_Employees_Email
ON Employees (Email);

مزایا

• حفظ یکپارچگی داده‌ها.
• افزایش سرعت جستجو در ستون‌های منحصربه‌فرد.

معایب

• محدودیت در ورود داده‌ها ممکن است برای برخی سناریوها نامناسب باشد.

کاربرد

۴. ایندکس فیلترشده (Filtered Index)

ویژگی‌ها

• فقط ردیف‌هایی که شرط WHERE را برآورده می‌کنند، در ایندکس قرار می‌گیرند.
• فضای کمتری نسبت به ایندکس‌های معمولی اشغال می‌کند.

CREATE NONCLUSTERED INDEX IX_Employees_Active
ON Employees (DepartmentID)
WHERE IsActive = ۱;

مزایا

• کاهش هزینه نگهداری و فضای ذخیره‌سازی.
• بهبود عملکرد کوئری‌های خاص.

معایب

• محدود به سناریوهای خاص با شرط‌های ثابت.

کاربرد

۵. ایندکس ستونی (Columnstore Index)

ویژگی‌ها

• داده‌ها به‌صورت فشرده ذخیره می‌شوند و عملیات‌هایی مثل جمع (SUM) یا میانگین (AVG) را تسریع می‌کنند.
• می‌تواند خوشه‌ای یا غیرخوشه‌ای باشد.

CREATE COLUMNSTORE INDEX IX_Sales_Columnstore
ON Sales (SaleDate, Amount);

مزایا

• عملکرد فوق‌العاده در تحلیل داده‌ها.
• کاهش حجم ذخیره‌سازی با فشرده‌سازی.

معایب

• برای جداول با عملیات نوشتن زیاد (INSERT/UPDATE) مناسب نیست.

CREATE SPATIAL INDEX IX_Locations_Spatial
ON Locations (GeoLocation);

کاربرد

۷. ایندکس XML

CREATE PRIMARY XML INDEX IX_Employees_XmlData
ON Employees (XmlData);

کاربرد

۸. ایندکس Full-Text

CREATE FULLTEXT INDEX ON Employees (Resume)
KEY INDEX PK_Employees;

کاربرد

۹. ایندکس مبتنی بر دیسک (Disk-Based Index)

ویژگی‌ها

• داده‌ها به‌صورت دائمی روی دیسک (هارد دیسک یا SSD) ذخیره می‌شوند.
• برای اکثر سناریوهای پایگاه داده که پایداری داده‌ها مهم است، استفاده می‌شود.
• سرعت دسترسی به داده‌ها به عملکرد دیسک وابسته است.

CREATE NONCLUSTERED INDEX IX_Employees_Disk
ON Employees (FirstName);

مزایا

• پایداری بالا به دلیل ذخیره‌سازی دائمی.
• مناسب برای جداول بزرگ که در حافظه جا نمی‌شوند.

معایب

• کندتر از ایندکس‌های مبتنی بر حافظه به دلیل تأخیر I/O دیسک.
• وابستگی به سرعت سخت‌افزار ذخیره‌سازی.

کاربرد

۱۰. ایندکس مبتنی بر حافظه (In-Memory Index)

ویژگی‌ها

• از ساختارهای بهینه‌شده مثل Hash Index یا Non-Clustered Bw-Tree استفاده می‌کند.
• داده‌ها در حافظه نگهداری می‌شوند و نیازی به دسترسی به دیسک ندارند.
• معمولاً با جداول In-Memory (Memory-Optimized Tables) استفاده می‌شود.

CREATE TABLE InMemoryEmployees (
    EmployeeID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = ۱۰۰۰۰),
    FirstName NVARCHAR(۵۰),
    LastName NVARCHAR(۵۰)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

مزایا

• سرعت بسیار بالا به دلیل حذف تأخیر I/O دیسک.
• مناسب برای عملیات تراکنشی با حجم بالا (High Throughput).

معایب

• نیاز به حافظه زیاد دارد و برای جداول بزرگ ممکن است هزینه‌بر باشد.
• در صورت قطع برق، داده‌ها ممکن است از دست بروند، مگر اینکه گزینه پایداری (Durability) فعال باشد.

کاربرد

مدیریت ایندکس‌ها

• تحلیل عملکرد: از Execution Plan در SSMS برای بررسی استفاده از ایندکس‌ها استفاده کنید.
• حذف ایندکس‌های بلااستفاده: با دستور DROP INDEX فضای اضافی را آزاد کنید.
• تعادل بین خواندن و نوشتن: ایندکس‌های زیاد می‌توانند عملیات نوشتن را کند کنند.
• انتخاب بین دیسک و حافظه: برای سیستم‌های با نیاز به سرعت بالا، In-Memory و برای پایداری، Disk-Based را انتخاب کنید.

نتیجه‌گیری