INDEX (اندیس ها) و کاربرد آن ها در پایگاه داده

اندیس چیست؟

اندیس‌ها به پایگاه داده این امکان را می‌دهند که داده‌ها را بدون نیاز به جستجوی کل جدول، سریع‌تر پیدا و بازیابی کند. به عبارت دیگر، اندیس‌ها سرعت اجرای پرس‌وجوها (Query) و جستجوی اطلاعات در جداول را افزایش داده و فرایند پیدا کردن داده‌ها را بهینه‌سازی می‌کنند. از این رو توصیه می‌شود برای ستون‌هایی که به طور متداول در عبارات WHERE استفاده می‌شوند، اندیس تعریف کنید. اندیس‌ها زمانی بسیار موثر واقع می‌شوند که تنها بخشی از داده‌ها (مثلاً ۱۵٪ از کل داده‌های یک جدول) مورد جستجو قرار گیرد.

---

انواع مختلف اندیس‌ها در پایگاه داده

در پایگاه داده‌ها، انواع مختلفی از اندیس‌ها وجود دارند که بسته به نوع داده‌ها و نیازهای جستجو، می‌توانند عملکرد کوئری‌ها را بهینه کنند. در ادامه، پرکاربردترین انواع ایندکس‌ها را بررسی می‌کنیم.

1. B-Tree Index (اندیس B-Tree)

• رایج‌ترین نوع ایندکس در پایگاه داده‌های رابطه‌ای است.
• از ساختار درختی برای ذخیره داده‌ها استفاده می‌کند که باعث جستجوی سریع‌تر مقادیر می‌شود.
• برای جستجوهای شرطی مانند WHERE salary BETWEEN 5000 AND 10000 بسیار کارآمد است.
• برای داده‌هایی که مقدار یکتای زیادی دارند، مناسب است.

CREATE INDEX emp_btree_index ON employees (salary);
                

2. Bitmap Index (اندیس بیت‌مپ)

• مناسب برای ستون‌هایی که تعداد مقادیر یکتای کمی دارند اما داده‌های زیادی را شامل می‌شوند.
• به جای ذخیره‌سازی اشاره‌گرها، مقادیر را به صورت بیت‌مپ ذخیره می‌کند که باعث کاهش فضای اشغالی می‌شود.
• برای کوئری‌هایی که ترکیب چند شرط منطقی دارند (مانند WHERE gender='Male' AND status='Active') بسیار مناسب است.
• در جداولی که تغییرات زیادی دارند (مانند INSERT و UPDATE)، کارایی مناسبی ندارد.

CREATE BITMAP INDEX emp_bitmap_index ON employees (gender);
                

3. Unique Index (اندیس یکتا)

این نوع ایندکس از ورود مقادیر تکراری در یک ستون جلوگیری می‌کند. معمولاً برای فیلدهایی مانند شناسه کاربری، ایمیل، شماره ملی و سایر مقادیری که باید یکتا باشند، استفاده می‌شود.

CREATE UNIQUE INDEX email_unique ON users (email);
            

4. Partial Index (اندیس جزئی)

این نوع اندیس فقط روی مجموعه‌ای خاص از داده‌ها اعمال می‌شود. مثلاً اگر بخواهید اندیسی را فقط روی کاربرانی که وضعیت آن‌ها فعال است ایجاد کنید، می‌توانید از اندیس جزئی استفاده کنید.

CREATE INDEX active_users_index ON users (email) WHERE status = 'active';
            

5. اندیس خوشه‌ای (Clustered Index) در برابر اندیس غیرخوشه‌ای (Non-Clustered Index)

در پایگاه داده‌های رابطه‌ای، دو نوع اصلی از اندیس‌ها وجود دارند: اندیس‌های خوشه‌ای (Clustered Index) و اندیس‌های غیرخوشه‌ای (Non-Clustered Index).

• اندیس خوشه‌ای (Clustered Index)
• در این نوع ایندکس، داده‌ها به صورت فیزیکی در دیسک به ترتیب مقدار اندیس ذخیره می‌شوند.
• هر جدول فقط می‌تواند یک اندیس خوشه‌ای داشته باشد.
• برای جستجوهایی که از مقدار اندیس به عنوان کلید اصلی استفاده می‌کنند، بسیار سریع عمل می‌کند.
• افزودن، حذف و بروزرسانی داده‌ها در این نوع اندیس می‌تواند کندتر باشد، زیرا نیاز به تغییر در ساختار فیزیکی داده‌ها دارد.

CREATE CLUSTERED INDEX emp_id_index ON employees (id);
    

• اندیس غیرخوشه‌ای (Non-Clustered Index)
• این نوع ایندکس داده‌های جدول را تغییر نمی‌دهد، بلکه ساختاری جداگانه ایجاد می‌کند که حاوی اشاره‌گرهایی به داده‌های ذخیره‌شده در جدول است.
• امکان تعریف چندین اندیس غیرخوشه‌ای روی یک جدول وجود دارد.
• برای جستجوهایی که نیاز به بازیابی سریع داده‌ها دارند، مناسب است اما سرعت آن از اندیس خوشه‌ای کمتر است.
• این اندیس معمولاً به ازای هر سطر، یک اشاره‌گر به مکان فیزیکی داده در جدول نگه می‌دارد.

CREATE INDEX last_name_index ON employees (last_name);
    

---

نحوه‌ی ایجاد INDEX

برای ایجاد اندیس، کافی است از دستور CREATE INDEX استفاده کنید.

CREATE INDEX empno_ind ON emp (empno);
            

---

ایندکس ترکیبی (Composite Index)

اگر دو ستون را به طور مکرر در شرط‌های WHERE استفاده می‌کنید، می‌توانید یک ایندکس ترکیبی روی آن‌ها ایجاد کنید.

CREATE INDEX empdept_ind ON emp (empno, deptno);
            

---

اندیس‌های BITMAP

این نوع اندیس برای ستون‌هایی مناسب است که دارای مقادیر تکراری زیاد هستند و جدول دارای حجم زیادی از داده است.

CREATE BITMAP INDEX city_ind ON emp (city);
            

---

اندیس‌های Function-Based

این نوع اندیس مقدار یک عبارت شامل یک یا چند ستون را محاسبه کرده و ذخیره می‌کند. اگر مرتباً از عبارتی مانند SAL + COMM در شرط‌های WHERE استفاده می‌کنید، می‌توانید یک اندیس Function-Based ایجاد کنید.

CREATE INDEX salcomm_ind ON emp (sal + comm);
            

---

نحوه‌ی حذف INDEX

برای حذف یک اندیس از دستور DROP INDEX استفاده می‌کنیم.

DROP INDEX salcomm_ind;
            

---

بازیابی اطلاعات اندیس‌هاh

برای مشاهده‌ی اطلاعات مربوط به اندیس‌ها و تعداد آن‌ها در Schema خود، دستور زیر را اجرا کنید:

SELECT * FROM user_indexes;
            

---

خوشه‌ها (Cluster) و مزایای آن‌ها

اگر دو یا چند جدول به طور مکرر روی یک ستون با هم JOIN شوند، ایجاد یک خوشه باعث بهینه‌سازی عملیات و کاهش Disk I/O می‌شود.

CREATE CLUSTER emp_dept (deptno NUMBER(2));
            

---

حذف Cluster

برای حذف خوشه، از دستور DROP CLUSTER استفاده کنید.

DROP CLUSTER emp_dept INCLUDING TABLES;
            

---

بررسی جداول موجود در یک Cluster

برای اینکه بدانید کدام جداول در یک Cluster قرار دارند، از دستور زیر استفاده کنید:

SELECT * FROM tab;
            

---

نتیجه‌گیری

استفاده‌ی بهینه از اندیس‌ها در پایگاه داده نقش بسیار مهمی در افزایش سرعت پرس‌وجوها و بهبود عملکرد دارد. با این حال، تعریف بیش از حد اندیس‌ها ممکن است باعث کاهش سرعت عملیات DML (INSERT, UPDATE, DELETE) شود، بنابراین باید با دقت طراحی شوند.