مقالات › EF Core و دیتابیس

آموزش ایندکس در SQL Server؛ Clustered، Non-Clustered و Covering Index

یاد بگیرید ایندکس‌های SQL Server پشت صحنه چطور کار می‌کنند، چطور ستون مناسب را انتخاب کنید، Execution Plan بخوانید و از اشتباهاتی که دیتابیس را کند می‌کنند دوری کنید.

ایندکس مؤثرترین ابزار برای سریع کردن دیتابیس است. کوئری‌ای که ده میلیون ردیف را در چهار ثانیه پیمایش می‌کند، با ایندکس مناسب می‌تواند در چهار میلی‌ثانیه جواب بدهد. اما ایندکس رایگان نیست: هر ایندکس عملیات نوشتن را کندتر می‌کند و فضا مصرف می‌کند. در این آموزش می‌بینیم ایندکس‌های SQL Server چطور کار می‌کنند، چطور طراحی‌شان کنیم و چطور مطمئن شویم واقعاً استفاده می‌شوند.

مثال دفترچه تلفن

یک دفترچه تلفن را تصور کنید که بر اساس نام خانوادگی مرتب شده است. پیدا کردن «محمودی» سریع است: مستقیم به بخش «م» می‌روید. اما پیدا کردن همه‌ی کسانی که در یک خیابان خاص زندگی می‌کنند کند است: باید همه‌ی صفحات را بخوانید. ایندکس دقیقاً همین است — یک ساختار مرتب‌شده که به دیتابیس اجازه می‌دهد به‌جای خواندن همه‌چیز، مستقیم به داده برسد.

ایندکس‌ها چطور ذخیره می‌شوند: B-Tree

SQL Server ایندکس‌ها را به‌شکل درخت B+ ذخیره می‌کند:

  • سطح ریشه و سطوح میانی بازه‌های کلید را نگه می‌دارند و به سطوح پایین‌تر اشاره می‌کنند.
  • سطح برگ (Leaf) خود ورودی‌های ایندکس را به ترتیب کلید نگه می‌دارد.

پیدا کردن یک ردیف یعنی حرکت از ریشه تا برگ؛ معمولاً ۳ یا ۴ بار خواندن صفحه، حتی برای میلیون‌ها ردیف. این عملیات Index Seek است. خواندن کل سطح برگ Index Scan نام دارد.

ایندکس Clustered

ایندکس Clustered خودِ جدول است: سطح برگ آن ردیف‌های کامل را نگه می‌دارد که به‌طور فیزیکی بر اساس کلید مرتب شده‌اند. به همین دلیل هر جدول فقط یک ایندکس Clustered می‌تواند داشته باشد.

CREATE TABLE Orders
(
    Id          INT IDENTITY PRIMARY KEY,   -- به‌طور پیش‌فرض Clustered
    CustomerId  INT           NOT NULL,
    Status      TINYINT       NOT NULL,
    Total       DECIMAL(18,2) NOT NULL,
    CreatedAt   DATETIME2     NOT NULL
);

یک کلید Clustered خوب این ویژگی‌ها را دارد:

  • باریک — چون در همه‌ی ایندکس‌های Non-Clustered کپی می‌شود.
  • یکتا — وگرنه SQL Server یک «Uniquifier» مخفی اضافه می‌کند.
  • ثابت — تغییر آن یعنی جابه‌جایی ردیف.
  • همیشه صعودی — INT IDENTITY یا GUID ترتیبی از Page Split هنگام درج جلوگیری می‌کند.

هشدار: استفاده از GUIDهای تصادفی NEWID() به‌عنوان کلید Clustered باعث Fragmentation شدید و Page Split می‌شود. اگر به GUID نیاز دارید، از NEWSEQUENTIALID() استفاده کنید یا GUID را Non-Clustered نگه دارید و Clustered را روی یک عدد صحیح بگذارید.

ایندکس Non-Clustered

ایندکس Non-Clustered یک ساختار جداگانه است که کلید ایندکس و یک اشاره‌گر ردیف (کلید Clustered) را نگه می‌دارد. هر جدول می‌تواند تعداد زیادی از آن داشته باشد.

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
    ON Orders (CustomerId);

SELECT Id, Total, CreatedAt
FROM Orders
WHERE CustomerId = 42;

SQL Server با Seek روی ایندکس، Idهای منطبق را پیدا می‌کند و سپس برای Total و CreatedAt یک Key Lookup روی ایندکس Clustered انجام می‌دهد. برای چند ردیف مشکلی نیست؛ اما برای هزاران ردیف این Lookupها گران می‌شوند و بهینه‌ساز ممکن است تصمیم بگیرد Scan کامل ارزان‌تر است.

Covering Index با INCLUDE

Covering Index همه‌ی ستون‌هایی را که کوئری لازم دارد شامل می‌شود و دیگر نیازی به Lookup نیست:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Covering
    ON Orders (CustomerId)
    INCLUDE (Total, CreatedAt);

ستون‌های INCLUDE فقط در سطح برگ ذخیره می‌شوند؛ روی ترتیب مرتب‌سازی اثری ندارند و سطوح بالایی را کوچک نگه می‌دارند. از آن‌ها برای ستون‌هایی استفاده کنید که در SELECT هستند اما در WHERE، JOIN یا ORDER BY نیستند.

ایندکس ترکیبی و ترتیب ستون‌ها

در ایندکس‌های چندستونه ترتیب اهمیت دارد. ایندکس روی (CustomerId, CreatedAt) اول بر اساس CustomerId و سپس داخل هر مشتری بر اساس CreatedAt مرتب است:

CREATE INDEX IX_Orders_Customer_Created ON Orders (CustomerId, CreatedAt DESC);

این ایندکس به‌خوبی از این کوئری‌ها پشتیبانی می‌کند:

WHERE CustomerId = 42                                 -- ✅ Seek
WHERE CustomerId = 42 AND CreatedAt >= '2026-01-01'   -- ✅ Seek روی هر دو
WHERE CustomerId = 42 ORDER BY CreatedAt DESC         -- ✅ Seek و بدون Sort

اما نه از این یکی:

WHERE CreatedAt >= '2026-01-01'                        -- ❌ ستون اول وجود ندارد ← Scan

قواعد سرانگشتی:

  1. ستون‌های تساوی را اول و ستون‌های بازه‌ای (>، BETWEEN، LIKE 'abc%') را آخر بگذارید.
  2. ستون‌هایی که بیشتر فیلتر می‌شوند را جلوتر قرار دهید.
  3. ترتیب ایندکس را با ORDER BYهای رایج هماهنگ کنید تا عملیات Sort حذف شود.

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

وقتی کوئری‌ها روی زیرمجموعه‌ی کوچکی از ردیف‌ها هستند، فقط همان را ایندکس کنید:

CREATE INDEX IX_Orders_Pending
    ON Orders (CreatedAt)
    WHERE Status = 0;   -- فقط سفارش‌های در انتظار

ایندکس‌های فیلترشده کوچک، کم‌هزینه و بسیار سریع‌اند؛ ایده‌آل برای پرچم‌هایی مثل «فعال»، «خوانده‌نشده» یا «حذف‌نشده».

کوئری‌هایی که نمی‌توانند از ایندکس استفاده کنند (Non-SARGable)

عبارتی SARGable است که SQL Server بتواند با آن روی ایندکس Seek انجام دهد. قرار دادن ستون داخل یک تابع معمولاً این قابلیت را از بین می‌برد:

❌ Non-SARGable ✅ SARGable
WHERE YEAR(CreatedAt) = 2026 WHERE CreatedAt >= '2026-01-01' AND CreatedAt < '2027-01-01'
WHERE LEFT(Name, 3) = 'Ali' WHERE Name LIKE 'Ali%'
WHERE Name LIKE '%ali%' Full-Text Search یا طراحی متفاوت
WHERE Total * 1.09 > 1000 WHERE Total > 1000 / 1.09
WHERE CAST(PhoneNumber AS INT) = 912 مقایسه با نوع خود ستون

تبدیل نوع ضمنی (Implicit Conversion) نسخه‌ی پنهان همین مشکل است؛ مثلاً مقایسه‌ی پارامتر NVARCHAR با ستون VARCHAR می‌تواند Scan ایجاد کند.

خواندن Execution Plan

در SQL Server Management Studio کلید Ctrl+M (Include Actual Execution Plan) را بزنید و کوئری را اجرا کنید. به این موارد دقت کنید:

  • Index Seek — خوب: دسترسی هدفمند.
  • Index/Clustered Index Scan — همه‌چیز را می‌خواند؛ برای جدول کوچک قابل قبول و برای جدول بزرگ مشکوک است.
  • Key Lookup — اگر زیاد اجرا می‌شود، Covering Index را در نظر بگیرید.
  • Sort پرهزینه — ایندکسی هم‌راستا با ORDER BY می‌تواند حذفش کند.
  • پیشنهاد Missing Index (متن سبز) — سرنخ مفیدی است اما بررسی‌اش کنید؛ کل بار کاری شما را در نظر نمی‌گیرد.

می‌توانید I/O را هم اندازه بگیرید:

SET STATISTICS IO, TIME ON;
SELECT ... ;   -- عدد logical reads را قبل و بعد از ساخت ایندکس مقایسه کنید

ایندکس در EF Core

ایندکس‌ها را در مدل تعریف کنید تا Migrationها آن‌ها را بسازند:

modelBuilder.Entity<Order>(e =>
{
    e.HasIndex(o => new { o.CustomerId, o.CreatedAt })
     .IsDescending(false, true)
     .IncludeProperties(o => new { o.Total });   // مخصوص SQL Server

    e.HasIndex(o => o.CreatedAt)
     .HasFilter("[Status] = 0")
     .HasDatabaseName("IX_Orders_Pending");

    e.HasIndex(o => o.Email).IsUnique();
});

یادتان باشد SQL Server به‌طور خودکار روی کلیدهای خارجی ایندکس نمی‌سازد؛ هرچند EF Core طبق قرارداد برایشان ایندکس ایجاد می‌کند. اگر جدول‌ها را دستی می‌سازید، کلیدهای خارجی را ایندکس کنید؛ Joinها و حذف‌های آبشاری به آن‌ها وابسته‌اند.

هزینه‌ی ایندکس‌ها

هر INSERT، UPDATE و DELETE باید همه‌ی ایندکس‌های مرتبط را به‌روزرسانی کند. ایندکس‌های زیادی یعنی:

  • نوشتن کندتر و قفل‌گذاری بیشتر؛
  • فضای ذخیره‌سازی و فشار حافظه‌ی بیشتر؛
  • زمان نگهداری طولانی‌تر.

استفاده از ایندکس‌ها را دوره‌ای بررسی کنید و ایندکس‌هایی که هرگز خوانده نمی‌شوند را حذف کنید:

SELECT OBJECT_NAME(s.object_id) AS TableName, i.name AS IndexName,
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID()
ORDER BY s.user_updates DESC;

ایندکسی با به‌روزرسانی زیاد و صفر Seek، فقط سربار است.

جمع‌بندی

  • ایندکس Clustered خود جدول است؛ کلیدی باریک، یکتا، ثابت و صعودی انتخاب کنید.
  • ایندکس‌های Non-Clustered فیلترها و Joinها را سریع می‌کنند و INCLUDE آن‌ها را Covering می‌کند.
  • ترتیب ستون‌ها در ایندکس ترکیبی حیاتی است: تساوی اول، بازه آخر.
  • شرط‌های SARGable بنویسید و از تابع روی ستون‌های ایندکس‌شده پرهیز کنید.
  • با Execution Plan و STATISTICS IO بررسی کنید و ایندکس‌های بی‌استفاده را حذف کنید.

پرسش‌های متداول

یک جدول چند ایندکس باید داشته باشد؟

عدد جادویی وجود ندارد. جدول‌های OLTP با نوشتن زیاد معمولاً با چند ایندکس خوب‌انتخاب‌شده بهترین عملکرد را دارند؛ جدول‌های گزارش‌گیری که بیشتر خوانده می‌شوند می‌توانند ایندکس‌های بیشتری داشته باشند.

آیا هر ستونی که در WHERE می‌آید را ایندکس کنم؟

نه. ستون‌های کوئری‌های پرتکرار و مهم را ایندکس کنید و چند ایندکس ترکیبی/Covering را به ایندکس‌های تک‌ستونه‌ی زیاد ترجیح دهید.

آیا باید ایندکس‌ها را Rebuild کنم؟

روی SSDهای امروزی Fragmentation اهمیت کمتری دارد، اما به‌روز نگه داشتن Statistics بسیار مهم است. از یک راهکار نگهداری استفاده کنید که آمار را منظم به‌روز و ایندکس‌های خیلی پراکنده را بازسازی کند.