ایندکس دیتابیس: چرا یک کوئری ساده گاهی چند ثانیه طول میکشه

فرض کن یک جدول داری با یک میلیون ردیف، و میخوای کاربری با یک ایمیل مشخص رو پیدا کنی. بدون هیچ کمکی، دیتابیس مجبوره ردیف به ردیف کل جدول رو بگرده تا برسه به همونی که دنبالشی — به این کار میگن full table scan. روی یک میلیون ردیف، این یعنی دیتابیس تا آخرین لحظه هم نمیدونه جواب رو پیدا کرده یا نه، و باید همه‌چیز رو ببینه. حالا اگه همین جدول یک ایندکس روی ستون ایمیل داشته باشه، دیتابیس دیگه لازم نیست همه‌جا رو بگرده؛ مستقیم میره سراغ جایی که احتمال جواب هست. تفاوتش میتونه از چند ثانیه به چند میلی‌ثانیه برسه.

ایندکس چیه واقعاً

ساده‌ترین راه برای فهمیدن ایندکس، مقایسه‌اش با فهرست آخر یک کتابه. اگه بخوای بفهمی کلمه‌ی «recursion» توی کدوم صفحه‌ی کتاب اومده، دو راه داری: یا کتاب رو صفحه به صفحه بخونی تا پیداش کنی (همون full scan)، یا بری سراغ فهرست آخر کتاب که مرتب‌شده‌ست و سریع بگی صفحه‌ی ۲۴۳. ایندکس دیتابیس دقیقاً همین نقشو بازی میکنه: یک ساختار داده‌ی جدا و مرتب‌شده که به ازای هر مقدار توی یک ستون، میگه دقیقاً کجا (کدوم ردیف فیزیکی) باید بری دنبالش.

اکثر دیتابیس‌های رابطه‌ای مثل PostgreSQL و MySQL از یک ساختار داده به اسم B-tree برای ایندکس‌ها استفاده میکنن. B-tree یک درخت متوازنه که جستجو، درج، و حذف توش با پیچیدگی زمانی لگاریتمی انجام میشه — یعنی حتی روی یک جدول با میلیون‌ها ردیف، پیدا کردن یک مقدار فقط چند مرحله طول میکشه، نه میلیون‌ها مقایسه.

چرا رایگان نیست

اگه ایندکس این‌قدر خوبه، چرا همه‌ی ستون‌ها رو ایندکس نمیکنیم؟ چون هزینه داره، و این هزینه دو جا خودش رو نشون میده.

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

دوم و مهم‌تر، سرعت نوشتن. هر بار که یک ردیف جدید insert میکنی یا یک ردیف موجود رو update میکنی، دیتابیس باید همزمان با آپدیت کردن جدول اصلی، تمام ایندکس‌های مرتبط با اون ستون‌ها رو هم آپدیت کنه. اگه یک جدول پنج تا ایندکس داشته باشه، هر insert عملاً شش تا عملیات نوشتن رو تریگر میکنه، نه یکی. برای همین توصیه‌ی رایج اینه که فقط روی ستون‌هایی ایندکس بذاری که واقعاً توی WHERE، JOIN، یا ORDER BY زیاد ازشون استفاده میکنی — نه هر ستونی که «شاید یک روز» لازم بشه.

clustered در مقابل non-clustered

یک تمایز مهم که خیلی وقت‌ها گیج‌کننده‌ست، فرق ایندکس clustered و non-clustered است. توی ایندکس clustered، ترتیب فیزیکی داده روی دیسک دقیقاً همون ترتیب ایندکسه — یعنی خود جدول، به شکل ایندکس مرتب شده. هر جدول فقط میتونه یک ایندکس clustered داشته باشه، چون داده فقط یک ترتیب فیزیکی روی دیسک میتونه داشته باشه. توی MySQL با InnoDB، این معمولاً همون primary key است.

ایندکس non-clustered یک ساختار جداست که فقط اشاره‌گر به ردیف اصلی نگه میداره، نه خود داده رو. میتونی تعداد دلخواهی از این نوع ایندکس روی یک جدول داشته باشی. تفاوتش توی عمل اینه که خوندن از ایندکس clustered یک مرحله کمتر داره (چون داده همونجاست)، ولی non-clustered برای ستون‌های غیر primary key انعطاف بیشتری میده.

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

وقتی کوئری‌هات معمولاً چند شرط با هم دارن — مثلاً WHERE user_id = ? AND status = 'active' — میتونی یک composite index روی هر دو ستون بسازی. نکته‌ی مهمی که خیلی از دولوپرها نادیده میگیرن اینه که ترتیب ستون‌ها توی ایندکس ترکیبی مهمه. یک ایندکس روی (user_id, status) برای کوئری‌هایی که با user_id فیلتر میکنن (با یا بدون status) مفیده، ولی برای کوئری‌هایی که فقط روی status فیلتر میکنن و اصلاً کاری با user_id ندارن، عملاً بی‌فایده‌ست — چون درست مثل فهرست کتابیه که بر اساس فصل مرتب شده، نه بر اساس موضوع؛ اگه فصل رو ندونی، فهرست کمکت نمیکنه.

CREATE INDEX idx_user_status ON orders (user_id, status);

-- این کوئری از ایندکس بالا خوب استفاده میکنه
SELECT * FROM orders WHERE user_id = 42 AND status = 'active';

-- این یکی عملاً از این ایندکس استفاده نمیکنه
SELECT * FROM orders WHERE status = 'active';

چطور بفهمیم دیتابیس واقعاً از ایندکس استفاده میکنه

خیلی وقت‌ها فرض میکنیم چون ایندکس ساختیم، کوئری هم ازش استفاده میکنه، درحالی‌که همیشه این‌طور نیست. ابزار اصلی برای فهمیدن این موضوع دستور EXPLAIN (یا EXPLAIN ANALYZE توی PostgreSQL) است که نشون میده دیتابیس دقیقاً چه plan ای برای اجرای کوئری انتخاب کرده — آیا از ایندکس استفاده کرده یا رفته سراغ full table scan.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

اگه توی خروجی این دستور عبارتی مثل Seq Scan ببینی به‌جای Index Scan، یعنی دیتابیس تصمیم گرفته ایندکس رو نادیده بگیره — که میتونه چند دلیل داشته باشه: شاید جدول اونقدر کوچیکه که دیتابیس تشخیص داده full scan سریع‌تره، شاید آمار (statistics) دیتابیس قدیمی شده، یا شاید شرط کوئری طوری نوشته شده که ایندکس اصلاً قابل استفاده نیست (مثلاً استفاده از تابع روی ستون ایندکس‌شده، مثل WHERE LOWER(email) = ...).

وقتی ایندکس ضرر میزنه

جالبه که بدونی ایندکس همیشه سریع‌تر نیست. روی جدول‌های خیلی کوچیک، یا وقتی یک شرط WHERE درصد بزرگی از جدول رو برمیگردونه (مثلاً بیش از ۲۰-۳۰ درصد ردیف‌ها)، دیتابیس معمولاً ترجیح میده مستقیم برگرده به full scan، چون رفتن سراغ ایندکس و بعد پرش بین ردیف‌های پراکنده روی دیسک، از یک خوندن پیوسته و ساده کندتره. این یکی از نکاتی‌یه که خیلی از مهندس‌های تازه‌کار تعجب میکنن وقتی برای اولین بار میبینن دیتابیس با وجود ایندکس، ازش استفاده نکرده — درحالی‌که این تصمیم دیتابیس اتفاقاً درسته، نه باگ.

جمع‌بندی

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