ایندکس دیتابیس چیست؟ B-tree، ایندکس مرکب و بهینهسازی Query
ایندکس دیتابیس ساختاری کمکی است که پیدا کردن رکوردها را سریعتر میکند؛ شبیه فهرست کتاب که اجازه میدهد بدون خواندن همه صفحهها به بخش موردنظر برسید. اما هر ایندکس رایگان نیست: فضای ذخیرهسازی میگیرد و insert، update و delete را پرهزینهتر میکند. سؤال درست «برای همه ستونها ایندکس بسازیم؟» نیست؛ سؤال این است که کدام query و با چه الگویی ارزش بهینهسازی دارد.
عبارتهایی مثل «ایندکس دیتابیس چیست»، «ایندکس در SQL»، «B-tree چیست»، «چرا query کند است»، «ایندکس مرکب» و «چه زمانی index بسازیم» نیت آموزشی و فنی دارند. این راهنما از تعریف و انتخاب ستون تا composite index، query plan و خطاهای رایج را توضیح میدهد. موضوع با توسعه نرمافزار اختصاصی ارتباط دارد، اما با «API چیست» یا «طراحی پنل» یکی نیست؛ برای قرارداد ارتباط سرویسها، راهنمای API را جدا بخوانید.
The goal of database indexing is to reduce the amount of data that has to be read to satisfy a query.
ایندکس دیتابیس چیست؟
وقتی query بدون ایندکس اجرا میشود، موتور ممکن است مجبور شود ردیفهای زیادی را بررسی کند؛ این کار برای جدول کوچک محسوس نیست اما با رشد داده و همزمانی کاربران گران میشود. ایندکس یک مسیر مرتب یا ساختار جستوجو فراهم میکند تا موتور سریعتر کاندیداها را پیدا کند و سپس نتیجه را از جدول بخواند.
ایندکس همیشه query را سریع نمیکند. اگر شرط انتخابپذیری کمی داشته باشد، بیشتر ردیفها را شامل شود یا خروجی بزرگی برگرداند، اسکن ترتیبی ممکن است بهتر باشد. تصمیم باید با execution plan، اندازه داده و الگوی واقعی مصرف گرفته شود؛ نه با این فرض که هر query باید از index scan استفاده کند.
| اثر مثبت | هزینه | سؤال تشخیصی |
|---|---|---|
| خواندن سریعتر رکوردهای منتخب | فضای دیسک | این query پرتکرار یا حساس است؟ |
| کم شدن I/O و زمان پاسخ | نوشتن کندتر | نرخ insert/update چقدر است؟ |
| کمک به sort و join | نگهداری و migration | شرایط و ترتیب ستونها درست است؟ |
B-tree و انتخاب نوع ایندکس
B-tree برای بسیاری از مقایسهها، بازهها و sortها انتخاب پیشفرض مناسبی است. اگر دنبال برابری دقیق، بازه تاریخ یا مرتبسازی هستید، معمولاً از همین خانواده شروع میکنید. اما نوع مناسب به موتور دیتابیس و داده بستگی دارد؛ جستوجوی متن، داده JSON، مکان جغرافیایی یا آرایه ممکن است ساختار دیگری بخواهد.
قبل از ساخت index، محدودیت دیتابیس، collation، null و نوع مقایسه را بشناسید. ایندکس روی expression یا شرط partial میتواند برای الگوی خاص مفید باشد، اما باید با query واقعی همخوان باشد. استفاده از یک نسخه کپیشده از توصیه اینترنتی بدون اندازهگیری ممکن است فقط migration و هزینه نگهداری بسازد.
ایندکس تکی یا مرکب؟
فرض کنید query همیشه سفارشهای یک فروشگاه را بر اساس وضعیت و تاریخ میخواهد. دو ایندکس جدا روی status و created_at همیشه به خوبی یک ایندکس مرکب مناسب عمل نمیکنند. ترتیب ستونها اهمیت دارد؛ ستون فیلترکننده، ترتیبدهنده و الگوی استفاده را با هم بررسی کنید. تعداد ستونها را هم بیدلیل زیاد نکنید.
قاعده «ستون با cardinality بیشتر را اول بگذار» همیشه کافی نیست. leftmost prefix، queryهای واقعی و نوع join تصمیم را شکل میدهند. اگر گاهی فقط status و گاهی tenant_id بهعلاوه status فیلتر میشود، شاید دو query متفاوت به راهکار متفاوت نیاز داشته باشند. plan را برای نمونههای واقعی و نه فقط یک request آزمایشی ببینید.
Query plan را چطور بخوانیم؟
ابزارهایی مثل EXPLAIN و EXPLAIN ANALYZE نشان میدهند موتور چه مسیری برای اجرای query انتخاب کرده است. به نوع scan، تعداد ردیف تخمینی و واقعی، هزینه، sort، join و زمان توجه کنید. اختلاف زیاد بین estimate و actual میتواند از آمار قدیمی یا توزیع غیرعادی داده بیاید؛ در این حالت ساختن ایندکس اولین پاسخ نیست.
plan را روی دادهای نزدیک production بررسی کنید. جدول دههزار ردیفی ممکن است با جدول دهمیلیونی رفتاری متفاوت داشته باشد. همچنین query را جدا از application pool نسنجید؛ timeout، lock، network و serialization میتوانند زمان نهایی را تغییر دهند. هدف، کم کردن زمان و فشار واقعی مسیر کاربر است، نه زیبا شدن یک plan.
چه ستونهایی معمولاً کاندید ایندکس هستند؟
ستونهای join، foreign key، فیلترهای پرتکرار، unique constraint و مسیرهای sort میتوانند کاندید باشند. اما وجود foreign key بهتنهایی در همه موتورهای دیتابیس index مناسب ایجاد نمیکند؛ رفتار موتور را بررسی کنید. ستونهایی که با queryهای واقعی بهندرت استفاده میشوند، فقط به خاطر اسم مهمشان index نمیخواهند.
در سیستم چندمستاجری، tenant_id در بسیاری از queryها بخشی از شرط امنیتی و عملکردی است. در جدول رویداد یا سفارش، زمان و وضعیت هم مهماند. این مثالها نسخه قطعی نیستند؛ الگوی مصرف و توزیع داده تعیینکننده است. اگر طراحی schema هنوز تغییر میکند، پیش از انباشتن ایندکسها قرارداد داده و مسیرهای اصلی محصول را روشن کنید.
Unique و partial index
Unique index علاوه بر سرعت، یک قاعده صحت داده را enforce میکند. برای مثال، جلوگیری از تکرار ایمیل باید با نرمالسازی و تعریف درست مقایسه همراه باشد؛ فقط index روی رشته خام همه نیازهای کسبوکار را حل نمیکند. اگر uniqueness فقط برای رکوردهای فعال لازم است، partial index میتواند مدل دقیقتری باشد.
این تصمیمها بخشی از منطق دامنهاند. migration باید قابل بازگشت و قابل اجرا روی داده موجود باشد. ساخت index بزرگ روی production ممکن است lock یا مصرف منابع ایجاد کند؛ روش online/concurrent متناسب با دیتابیس و پنجره نگهداری را بررسی کنید. backup و plan rollback قبل از اجرا فراموش نشود.
چرا ایندکس زیاد خطرناک است؟
هر write باید ایندکسهای مرتبط را نیز بهروزرسانی کند. ایندکسهای تکراری یا بلااستفاده فضای دیسک و زمان migration میگیرند و بررسی آنها را سخت میکنند. در پروژههای واقعی، فهرست indexها را با query log و آمار مصرف دورهای بازبینی کنید؛ حذف index هم مثل ساخت آن باید با اندازهگیری و برنامه rollback انجام شود.
ایندکس را برای حل مشکل lock یا query بد بهصورت کورکورانه اضافه نکنید. شاید مشکل از N+1 در application، انتخاب ستونهای زیاد، join نادرست یا pagination با offset بزرگ باشد. یکپارچهسازی سیستمها و تصمیم معماری مونولیت و میکروسرویس را هم در کنار مسئله دیتابیس ببینید؛ عملکرد فقط ویژگی یک index نیست.
ایندکس و pagination
Offset pagination برای صفحههای اول ساده است، اما با offset بزرگ ممکن است ردیفهای زیادی را رد کند. در فهرستهای بزرگ، keyset یا cursor pagination میتواند مناسبتر باشد؛ شرط و order باید با index هماهنگ شوند و ترتیب پایدار داشته باشند. این انتخاب را با نیاز کاربر، امکان رفتن به صفحه خاص و حجم داده بسنجید.
چکلیست بررسی ایندکس دیتابیس
- query کند، frequency و مسیر کاربر ثبت شده است.
- داده نزدیک production و توزیع واقعی بررسی شده است.
- EXPLAIN یا plan قبل و بعد مقایسه شده است.
- ترتیب ستونهای composite index با query همخوان است.
- هزینه write، فضا و migration در نظر گرفته شده است.
- ایندکس تکراری یا بلااستفاده وجود ندارد.
- روش اجرا، backup و rollback در production مشخص است.
اشتباهات رایج
- ساخت ایندکس روی همه ستونها بدون query واقعی
- اعتماد به cardinality بدون دیدن execution plan
- نادیده گرفتن ترتیب ستون در composite index
- حل N+1 یا query بد با ایندکس بیشتر
- تست فقط روی دیتابیس کوچک توسعه
- ساخت index سنگین در زمان پرترافیک بدون برنامه migration
جمعبندی
ایندکس دیتابیس ابزار سرعت است، نه تزئین schema. از query و مسیر واقعی کاربر شروع کنید، plan را ببینید، اثر read را با هزینه write بسنجید و بعد migration قابل بازگشت بسازید. یک ایندکس خوب مسئله مشخصی را حل میکند؛ چند ایندکس حدسی فقط هزینه را پخش میکنند.
برای طراحی schema، API، پنل و نرمافزار متناسب با فرآیند کسبوکار، خدمات برنامهنویسی اختصاصی روبینش و صفحه مشاوره را ببینید.
سؤالات متداول
ایندکس دیتابیس چیست؟
ایندکس ساختاری کمکی است که پیدا کردن رکوردها را سریعتر میکند، اما فضا میگیرد و هزینه عملیات نوشتن را افزایش میدهد.
آیا برای همه ستونها باید ایندکس بسازیم؟
خیر. ایندکس باید بر اساس queryهای واقعی، فراوانی استفاده، انتخابپذیری داده و نتیجه execution plan ساخته شود.
ایندکس مرکب چیست؟
ایندکسی است که چند ستون را با ترتیب مشخص پوشش میدهد و برای queryهایی که چند شرط یا sort ثابت دارند میتواند مناسب باشد.
چرا ایندکس زیاد بد است؟
هر ایندکس فضا میگیرد و هنگام insert، update و delete باید بهروزرسانی شود؛ ایندکس بلااستفاده فقط هزینه و پیچیدگی میسازد.