ایندکس در SQL چیست و چگونه کار می کند؟

"ایندکس باعث می شود کوئری ها سریع شود" این ساده ترین تعریف از ایندکس ها هست.

با استفاده از عبارت ‍`create index‍` میتوانیم ایندکس بسازیم. با ساخت ایندکس یک کپی از داده ها در جای دیگر ساخته می شود که مانند فهرست یک کتاب به داده ها در داخل جدول اصلی اشاره دارند. توجه داشته باشید که هر ایندکس فضای ذخیره سازی مخصوص خودش را لازم دارد.

جست و جو با کمک ایندکس مانند پیدا کردن شماره ای در دفتر تلفن است که با توجه به مرتب بودن شماره ها خیلی سریع شماره ها رو پیدا میکنیم اما ایندکس ها در دیتابیس پیچیده تر از یک دفترچه تلفن هستند زیرا به طور مداوم داده ها در حال تغییر (update, delete, insert) هستند و با این حال دیتابیس باید ایندکس ها رو مرتب نگه دارد.

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

• doubly linked list
• The Search Tree (B-Tree)

این دو ساختار ، بیشتر ویژگیهای عملکرد دیتابیس را توضیح می دهند.

هدف اولیه هر ایندکس فراهم آوردن یک نمایش منظم از داده‌های ایندکس است. ذخیره داده ها به صورت متوالی امکان پذیر نیست زیرا یک عبارت درج باید داده ها را جابجا کند تا فضای جدیدی را فراهم کند. انتقال مقادیر زیادی از داده بسیار زمان بر است بنابراین کوئری insert بسیار کند می شود.

راه حل مسئله ایجاد نظمی منطقی است که مستقل از نظم فیزیکی در حافظه باشد.

نظم منطقی از طریق یک لیست مضاعف (doubly linked list) مرتبط برقرار می شود. هر گره (node) به دو ورودی جدول پیوند دارد ، شبیه یک زنجیر. گره های جدید با به روز کردن پیوندهای آنها بین دو گره موجود وارد می شوند تا به گره جدید مراجعه کنند.

تصویر بالا نشان دهنده نحوه ذخیره ایندکسی است که روی ستون "column 2" گذاشته شده است اگر دقت کنید در ایندکس دیتای ستون "column 2" را داریم و در شناسه ی ردیف در جدول اصلی اشاره دارد.

اما هنوزم دیتابیس نمیتواند به سرعت مقدار را پیدا کند و ردیف را برگرداند زیرا در این حالت دیتابیس باید بین تمام مقادیر ستون "column 2" جست و جو کند تا ردیف خواسته شده را پیدا کند. برای حل این مشکل دیتابیس ها از درخت جست و جو (‌B-tree) استفاده میکنند.

در روش بالا دیتابیس با استفاده از قسمت بندی داده های ایندکس سریع تر به آن ها می رسند مانند یک درخت که یک تنه اصلی دارد و بعد شاخه های اصلی و بعد شاخه های ریز تر به این صورت به راحتی می توانیم به شاخه های کوچک تر برسیم. با استفاده از این روش هم تغییرات در ایندکس سریع تر انجام میشود و هم جست و جوی داده سریعتر به نتیجه میرسد.

در مثال بالا ما به دنبال عدد 57 میگردیم دیتابیس از گره های اول شروع میکند و به جلو میرود. در گره اول دیتابیس با سه عدد (39, 83, 98) مواجه میشود باید داخل گره ای شود که از 57 بزرگتر است پس 83 را انتخاب میکند در مرحله بعد با اعداد (46, 53, 57, 83) مواجه می شود داخل گره 57 می شود که حاوی اطلاعات 57 هم هست.

مزیت ایندکس:

• افزایش قابل توجه سرعت کوئری های select.

معایب ایندکس:

• کاهش سرعت کوئری های insert و update.
• اشغال بیشتر فضای حافظه.

انواع ایندکس:

• ایندکس index (برای افزایش سرعت در جستجوی مقدار)
• ایندکس unique (علاوه بر افزایش سرعت در جستجوی مقدار، یکتا بودن مقدار ستون رو هم چک می کند)
• ایندکس full-text (برای افزایش سرعت در جستجوی مقدارهایی از جنس متون نسبتا طولانی)
• ایندکس spatial (برای افزایش سرعت در جستجوی مقدارهایی از جنس موقعیت های مکانی)
• ایندکس primary (کلید اصلی جدول هست و علاوه بر جلوگیری از ورود مقدار تکراری از ورود null ها هم جلوگیری میکند)

خب تا اینجا متوجه شدیم ایندکس چی هست و چطوری کار میکند و انواع مختلفش چی هست در مطلب بعدی بررسی می کنیم کوئری هامون رو چگونه بنویسیم تا بیشترین بهره وری رو از ایندکس هامون داشته باشیم.