ایندکس گذاری دیتابیس(راهنمای جامع)

«نسخه کامل و جامع این مقاله به همراه جزئیات بیشتر، درImaniNova.ir منتشر شده است. در ادامه قطعه ای از این مقاله را مشاهده می کنید.»

یکی از چالش های بزرگ در بحث توسعه نرم افزار، زمان بندی صحیح برای پیاده سازی ایندکس هاست. افراط و تفریط در این نقطه، به یک اندازه به پلتفرم ضربه می زند؛ زود اقدام کردن باعث «بهینه سازی زودهنگام» (Premature Optimization) و پیچیدگی بی مورد کد می شود، و دیر اقدام کردن سیستم را در پروداکشن به زانو درمی آورد.

نقشه راه مهندسی برای شروع ایندکس گذاری، سه ایستگاه مشخص دارد:

۱. لایه فونداسیون: در زمان طراحی دیتابیس (Design Time)

در همان روزهای اول که در حال نوشتن اسکریپت های Migration یا طراحی دیتابیس روی کاغذ هستید، کار ایندکس گذاری شروع می شود؛ اما فقط و فقط برای ستون های کلیدی:

• کلیدهای اصلی (Primary Keys): که دیتابیس آن ها را به عنوان ایندکس خوشه ای (Clustered) در نظر می گیرد.
• کلیدهای خارجی (Foreign Keys): ستون هایی که جداول را به هم متصل می کنند (مثل UserID در جدول Orders). از آنجا که تقریبا تمام عملیات های JOIN روی این ستون ها رخ می دهند، باید از همان روز اول ایندکس گذاری شوند.
• ستون های منحصر به فرد فیزیکی (Unique Constraints): ستون هایی مثل کدملی، شماره تلفن یا ایمیل که دیتابیس به طور خودکار برای تضمین عدم تکرار، روی آن ها ایندکس می سازد.

۲. لایه توسعه: در زمان نوشتن کوئری های فرکانس بالا (Development Time)

وقتی معماری سیستم شکل گرفته و در حال نوشتن بخش های اصلی بک اند هستید، به محض پیاده سازی کوئری هایی که «مدام و با فرکانس بالا» توسط کاربران صدا زده می شوند، باید ایندکس های غیرخوشه ای (Non-Clustered) مرتبط را بسازید.

• مثال: اگر در حال توسعه پلتفرم فروشگاهی هستید و می دانید بخش سرچ محصولات بر اساس ستون IsAvailable = 1 و Price فیلتر می شود و این صفحه در ثانیه هزاران بار لود خواهد شد، منتظر پروداکشن نمانید؛ ایندکس ترکیبی یا فیلترشده ی آن را در همین مرحله بسازید.

۳. لایه پروداکشن: مانیتورینگ و پاسخ به رفتار واقعی کاربران (Production Time)

حقیقت این است که شما هرگز نمی توانید ۱۰۰٪ رفتار کاربران را پیش بینی کنید. ایستگاه اصلی ایندکس گذاری دقیقا پس از زیر بار رفتن سیستم و بر اساس داده های واقعی سرور آغاز می شود. در این مرحله شما باید بر اساس نشانه های زیر اقدام کنید:

• وقتی حجم داده ها از مرز بحرانی عبور می کند: همان طور که گفتیم، جداول کوچک نیازی به ایندکس ندارند. اما به محض اینکه رکوردهای یک جدول شروع به رشد حرکتی کردند (مثلا عبور از ۱۰ الی ۵۰ هزار رکورد)، باید رفتار کوئری ها روی آن جدول زیر ذره بین برود.
• وقتی کندی (Latency) در پروفایلرها دیده می شود: به محض اینکه ابزارهای مانیتورینگ (مثل APMها یا لاگ کوئری های کند دیتابیس) نشان دادند اجرای یک کوئری بیش از حد استاندارد (مثلا بالای ۱۰۰ میلی ثانیه) طول می کشد، زمان جراحی دیتابیس و اضافه کردن ایندکس جدید فرا رسیده است.

کالبدشکافی مکانیسم داخلی: دیتابیس بدون ایندکس چگونه فکر می کند؟

وقتی در دیتابیس دستوری مانند زیر را اجرا می کنید، چه اتفاقی رخ می دهد؟
SELECT * FROM Users WHERE Email = 'info@imaninova.ir';

اگر ستون Email ایندکس نداشته باشد، موتور دیتابیس چاره ای جز انجام عملیات Table Scan یا Full Table Scan ندارد. این یعنی موتور دیتابیس باید تمام صفحات داده (Data Pages) را یک به یک از روی هارد یا دیسک سخت حافظه بخواند، درون RAM لود کند و خط به خط بررسی کند تا رکورد مورد نظر را بیابد.

اگر جدول شما ۱۰ هزار رکورد داشته باشد، این فرآیند شاید چند میلی ثانیه طول بکشد؛ اما اگر با جدولی با بیش از ۱۰ میلیون رکورد سروکار داشته باشید، یک Table Scan ساده یعنی خواندن گیگابایت ها دیتا از روی دیسک، ایجاد گلوگاه در پهنای باند I/O حافظه و به چالش کشیدن جدی CPU دیتابیس.

ایندکس ها با تغییر این فرآیند خطی به یک ساختار درختی هوشمند (عمدتا بر پایه ساختار داده B-Tree یا +B-Tree)، پیچیدگی زمانی جستجو را از حالت خطی $O(N)$ به حالت لگاریتمی $O(\log N)$ کاهش می دهند. این یعنی برای پیدا کردن یک رکورد در میان میلیون ها داده، دیتابیس به جای میلیون ها بررسی، تنها با کمتر از ۱۰ الی ۱۵ گام مقایسه ای مستقیم به هدف می رسد.

ادامه مقاله در

https://imaninova.ir/ArticleView?advanced-database-indexing-guide