چرا دیتابیس شما کند است؟ راهنمای بهینه سازی و ایندکس گذاری دیتابیس

سخت افزار گران قیمت خریده اید، کدهای خود را تا آخرین حد ممکن بهینه سازی کرده اید، معماری فرانت اند را به مدرن ترین شکل ممکن چیده اید و همه چیز در سرورهای محلی بی نقص کار می کند. اما درست در لحظه ای که ترافیک کاربران به اوج خود می رسد، ناگهان کابوس شروع می شود: لود صفحات ثانیه ها طول می کشد، درخواست ها تایم اوت می دهند و پردازنده ی سرور دیتابیس روی ۱۰۰٪ قفل می کند.

در این لحظه بحرانی، اولین متهم از نظر بیشتر برنامه نویسان چیست؟ «سرور ضعیف است» یا «کدهای بک اند باگ دارند». اما در ۹۰ درصد مواقع، واقعیت چیز دیگری است. مقصر اصلی، یک قاتل خاموش و نامرئی است که درست در تاریکی دیتابیس شما نشسته است: فقدان، یا بدتر از آن، ساختار کاملا غلط ایندکس ها (Indexing).

بسیاری از توسعه دهندگان تصور می کنند که با نوشتن یک کوئری ساده و سپردن آن به دیتابیس، وظیفه شان تمام شده است. اما حقیقت تلخ این است که یک کوئری بهینه نشده روی جدولی بدون ایندکس، مانند جستجوی خط به خط یک کتاب ۱۰۰۰ صفحه ای برای پیدا کردن یک کلمه است! این کار یعنی به زانو درآوردن هارد دیسک، پر کردن پهنای باند I/O و سوزاندن بی دلیل منابع CPU. ایندکس گذاری یک ویژگی تفننی یا جانبی نیست؛ بلکه مرز باریک بین یک سیستم زنده، روان و پایدار، با یک نرم افزار شکست خورده و قفل شده زیر بار ترافیک است.

در این مقاله تخصصی از ImaniNova، تعاریف تئوریک و دانشگاهی را کنار می گذاریم. مستقیما به قلب موتورهای دیتابیس نفوذ می کنیم تا ببینیم ساختارهای داخلی چطور کار می کنند و چگونه با چند تغییر استراتژیک در ایندکس ها، سرعت کوئری های سنگین خود را به چند میلی ثانیه برسانید.

تایمینگ در مهندسی سیستم؛ چه زمانی باید ایندکس گذاری را شروع کنیم؟

یکی از چالش های بزرگ در بحث توسعه نرم افزار، زمان بندی صحیح برای پیاده سازی ایندکس هاست. افراط و تفریط در این نقطه، به یک اندازه به پلتفرم ضربه می زند؛ زود اقدام کردن باعث «بهینه سازی زودهنگام» (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