SQL و Index

مشکل: جدول بزرگ، کوئری کند

جدول سفارش‌های فروشگاه ما ۵۰ میلیون ردیف دارد. در صفحه «سفارش‌های من»، مشتری سفارش‌هایش را می‌بیند، جدیدترین اول:

SELECT TOP (20) Id, CreatedAt, Total, Status
FROM Orders
WHERE CustomerId = @customerId
ORDER BY CreatedAt DESC;

وقتی جدول کوچک بود، این کوئری سریع بود. حالا چند ثانیه طول می‌کشد. چرا؟ چون دیتابیس راهی ندارد که سفارش‌های مشتری ۴۲ را مستقیم پیدا کند. پس همه ۵۰ میلیون ردیف را یکی‌یکی می‌خواند. به این Scan می‌گویند.

ایندکس چیست؟

فهرست آخر یک کتاب را در نظر بگیر. کلمه‌ها مرتب‌اند و کنار هر کلمه شماره صفحه آمده است. لازم نیست کل کتاب را بخوانی.

ایندکس دیتابیس هم همین است. یک درخت مرتب (B-Tree) از مقدار ستون‌ها. هر برگ درخت به ردیف اصلی اشاره می‌کند.

دو کلمه که در Query Plan زیاد می‌بینی:

1. سیک (Seek). دیتابیس از ریشه درخت مستقیم به جای درست می‌رود. فقط چند صفحه می‌خواند.
2. اسکن (Scan). دیتابیس همه ردیف‌ها را از اول تا آخر می‌خواند. روی جدول بزرگ کند است.

برای کوئری بالا، این ایندکس کافی است:

CREATE INDEX IX_Orders_CustomerId_CreatedAt
ON Orders (CustomerId, CreatedAt);

ترتیب ستون‌ها مهم است

ایندکس روی دو ستون مثل دفترچه تلفن است: اول بر اساس نام خانوادگی مرتب است، بعد بر اساس نام. پس:

1. جستجو با نام خانوادگی سریع است. ستون اول ایندکس است.
2. جستجو با نام خانوادگی و نام هم سریع است. هر دو ستون به ترتیب.
3. جستجو فقط با نام کند است. آدم‌های با نام «علی» در همه صفحه‌ها پخش هستند.

برای ایندکس سفارش‌ها هم همین است. ایندکس روی شماره مشتری و تاریخ، برای «سفارش‌های مشتری ۴۲، مرتب بر اساس تاریخ» عالی است. ولی برای «همه سفارش‌های دیروز» کمکی نمی‌کند، چون تاریخ ستون دوم است.

پرش به جدول اصلی و ایندکس پوششی

در SQL Server، هر جدول معمولاً یک Clustered Index دارد. یعنی خود جدول بر اساس کلید اصلی مرتب ذخیره شده است. ایندکس‌های دیگر (Nonclustered) جدا هستند. در هر برگ فقط ستون‌های ایندکس و کلید اصلی ردیف را دارند.

حالا کوئری ما ستون‌های Total و Status را هم می‌خواهد. این ستون‌ها در ایندکس نیستند. پس دیتابیس برای هر ردیف یک پرش جدا به جدول اصلی می‌زند. به این Key Lookup می‌گویند.

راه حل، ایندکس پوششی (Covering Index) است. ستون‌های لازم را با INCLUDE به برگ‌های ایندکس اضافه کن:

CREATE INDEX IX_Orders_CustomerId_CreatedAt
ON Orders (CustomerId, CreatedAt)
INCLUDE (Total, Status);

ستون‌های INCLUDE در مرتب‌سازی درخت نقشی ندارند. فقط کنار هر برگ ذخیره می‌شوند تا پرش لازم نباشد. در PostgreSQL هم از نسخه ۱۱، همین گزینه INCLUDE وجود دارد.

در EF Core همین ایندکس را این‌طور تعریف می‌کنی:

protected override void OnModelCreating(ModelBuilder model)
{
    model.Entity<Order>()
        .HasIndex(o => new { o.CustomerId, o.CreatedAt })
        .IncludeProperties(o => new { o.Total, o.Status });
}

شرطی که ایندکس را بی‌اثر می‌کند

ایندکس روی مقدار خام ستون ساخته شده است. اگر روی ستون یک تابع بزنی، دیتابیس نمی‌تواند در درخت جستجو کند. باید برای هر ردیف تابع را حساب کند. پس اسکن می‌شود.

-- Bad: the function on the column hides it from the index
SELECT Id FROM Orders WHERE YEAR(CreatedAt) = 2026;

-- Good: a range on the raw column can use the index
SELECT Id FROM Orders
WHERE CreatedAt >= '2026-01-01' AND CreatedAt < '2027-01-01';

موارد مشابه که ایندکس را بی‌اثر می‌کنند:

1. جستجوی متن با درصد در اول. شرط «نام شبیه درصد علی» نمی‌داند از کجای درخت شروع کند.
2. تبدیل نوع پنهان. مثلاً مقایسه یک ستون varchar با یک پارامتر nvarchar در SQL Server. دیتابیس ممکن است مجبور شود ستون را برای هر ردیف تبدیل کند.
3. محاسبه روی ستون. مثلاً «قیمت ضرب در ۲ بزرگ‌تر از ۱۰۰». به جای آن بنویس «قیمت بزرگ‌تر از ۵۰».

نقشه اجرا (Query Plan) را بخوان

حدس نزن. از خود دیتابیس بپرس چه کرده است:

1. در SQL Server، نقشه اجرای واقعی (Actual Execution Plan) را روشن کن. دستور SET STATISTICS IO ON هم تعداد صفحه‌های خوانده‌شده را نشان می‌دهد.
2. در PostgreSQL، دستور EXPLAIN ANALYZE را جلوی کوئری بنویس.
3. دنبال این‌ها بگرد. اسکن روی جدول بزرگ. Key Lookup با تعداد ردیف زیاد. فاصله زیاد بین تعداد ردیف تخمینی و واقعی.
4. قبل و بعد را اندازه بگیر. هر تغییر ایندکس را با عدد ثابت کن، نه با حس.

ایندکس مجانی نیست

هر ایندکس یک کپی مرتب از چند ستون است. پس:

1. نوشتن کندتر می‌شود. هر INSERT، UPDATE و DELETE باید همه ایندکس‌های جدول را هم به‌روز کند.
2. فضا می‌گیرد. روی دیسک و در حافظه دیتابیس.
3. ایندکس تکراری هدر است. ایندکس روی شماره مشتری تنها، وقتی ایندکس روی شماره مشتری و تاریخ داری، معمولاً لازم نیست.

پس برای هر کوئری یک ایندکس نساز. برای کوئری‌های پرتکرار و مهم ایندکس بساز و بقیه را با Query Plan بررسی کن.

صفحه‌بندی روی جدول بزرگ

لیست سفارش‌های پنل مدیریت با Skip و Take صفحه‌بندی می‌شود (در SQL همان OFFSET و FETCH). صفحه‌های اول سریع‌اند، ولی صفحه ۵۰ هزارم چند ثانیه طول می‌کشد. چرا؟

1. صفحه ۵۰ هزارم یعنی «۹۹۹٬۹۸۰ ردیف را رد کن، بعد ۲۰ تا بده».
2. دیتابیس نمی‌تواند مستقیم به ردیف ۹۹۹٬۹۸۰ بپرد. حتی با ایندکس، باید از اول بشمارد.
3. پس حدود یک میلیون ردیف را می‌خواند و دور می‌ریزد.

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

راه حل Keyset Pagination است. به جای «صفحه چندم؟» می‌گوییم «بعد از آخرین ردیفی که دیدم، ۲۰ تای بعدی را بده»:

var page = await db.Orders
    .Where(o => o.CreatedAt < lastCreatedAt
             || (o.CreatedAt == lastCreatedAt && o.Id < lastId))
    .OrderByDescending(o => o.CreatedAt).ThenByDescending(o => o.Id)
    .Take(20)
    .ToListAsync(ct);

1. با ایندکس روی تاریخ ثبت و شماره سفارش، دیتابیس مستقیم به جای درست می‌پرد. صفحه ۱ و صفحه ۵۰ هزارم یک سرعت دارند.
2. شماره سفارش لازم است، چون دو سفارش ممکن است تاریخ ثبت یکسان داشته باشند. بدون آن ترتیب یکتا نیست.
3. سفارش جدید بالای لیست، نقطه شروع ما را جابجا نمی‌کند. پس تکرار پیش نمی‌آید.
4. هزینه‌اش این است که پرش به «صفحه ۴۷» ممکن نیست. فقط صفحه بعد و قبل داریم. برای اسکرول بی‌پایان و API عالی است.

کپی فقط‌خواندنی (Read Replica) و تأخیر

وقتی بار خواندن زیاد است، یک کپی فقط‌خواندنی (Read Replica) اضافه می‌کنیم. نوشتن به دیتابیس اصلی می‌رود و خواندن به کپی. ولی تغییرها معمولاً با کمی تأخیر به کپی می‌رسند. به آن Replication Lag می‌گویند.

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

1. مدتی بعد از نوشتن، از اصلی بخوان. مثلاً تا چند ثانیه بعد از ذخیره، خواندن‌های همان کاربر به دیتابیس اصلی برود.
2. تصمیم‌های مهم را از کپی نخوان. مثلاً چک موجودی قبل از کم کردن آن.
3. تأخیر را مانیتور کن و برایش هشدار بگذار.

اشتباه‌های رایج