پرش به مطلب اصلی

Index

ایندکس‌ها درDB برای افزایش سرعت جستجوها استفاده می‌شوند. مثل فهرست یک کتاب عمل می‌کنند که دسترسی سریع به داده‌ها را ممکن می‌سازد.

۱. ایندکس‌ها چجوری ذخیره میشن؟​

ساختارهای داده‌ای معمول برای ذخیره ایندکس‌ها​

ایندکس‌ها معمولاً از یکی از این ساختارهای داده‌ای استفاده می‌کنند:

الف) B-Tree (Balanced Tree)​

  • پراستفاده‌ترین ساختار در دیتابیس‌های رابطه‌ای مانند PostgreSQL، MySQL و SQL Server.
  • داده‌ها به‌صورت مرتب شده در یک درخت متوازن ذخیره می‌شوند.
  • هر نود می‌تواند چندین کلید و اشاره‌گر به فرزندانش داشته باشد.
  • عملیات **جستجو (O(log n)، درج (O(log n و حذف (O(log n.

🔹 نحوه ذخیره در دیسک:

  • هر نود در یک page از دیسک ذخیره می‌شود (مثلاً در PostgreSQL سایز صفحه ۸KB است).
  • دسترسی به نودهای والد و فرزند به کمک اشاره‌گرها در صفحه‌های مختلف صورت می‌گیرد.
B-tree

ب) Hash Index​

  • بر اساس مقدار هش کلیدها کار می‌کند.
  • دسترسی سریع O(1) دارد، ولی برای رنج کوئری‌ها مناسب نیست.
  • اPostgreSQL از آن پشتیبانی می‌کند اما در مقایسه با B-Tree کمتر استفاده می‌شود.

ج) GiST (Generalized Search Tree)​

  • برای ایندکس‌های خاص مثل جستجوی متنی، داده‌های فضایی (PostGIS) و داده‌های چندبعدی.

د) GIN (Generalized Inverted Index)​

  • مخصوص جستجوی متن کامل (Full-Text Search) و آرایه‌ها.
  • در PostgreSQL برای فیلدهایی که مقدارشان شامل چند مقدار ممکن است (مثلاً tags که شامل چندین تگ است).

۲. Heap Fetch چیه؟​

اPage چیست؟​

  • دیتابیس‌ها داده‌ها را در بلاک‌های ثابت (Pages) ذخیره می‌کنند.
  • مثلاً در PostgreSQL اندازه هر Page ۸KB است.
  • هر Page شامل چندین سطر (Tuple) است.

اHeap Fetch چیه؟​

وقتی یک ایندکس اسکن انجام میشه، نتیجه‌ی اون آدرس فیزیکی سطرها در heap tableه. دیتابیس باید سطرهای واقعی را از heap بخونه و اون را برگردونه. اسم این کار heap fetchه.

🔹 چرا Heap Fetch مشکل‌ساز است؟

  • باعث IO اضافه می‌شود چون دیتابیس باید دو بار به دیسک مراجعه کند (یک‌بار ایندکس و یک‌بار داده واقعی).
  • بهینه‌سازی: Covering Index (ایندکسی که تمام داده‌های لازم را شامل شود) این مشکل را کاهش می‌دهد.

۳. آیا کوئری‌های ما همیشه از ایندکس استفاده می‌کنند؟​

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

الف) Index Scan​

  • دیتابیس مستقیماً ایندکس را جستجو می‌کند.
  • اگر سطرهای کمی موردنیاز باشد، کارآمد است.
  • در صورتی که دیتای لازم در ایندکس باشد، Heap Fetch ممکن است حذف شود (Covering Index).

ب) Bitmap Index Scan​

  • زمانی که بخش بزرگی از جدول نیاز است ولی کل جدول نه. میره و اون page های که دیتا توش هست رو لیست میکنه که یه بار io بزنه برای خوندن
  • اگه دوتا ایندکس باشه، ایندکس‌ها را ترکیب می‌کند و بعد page هاشون رو با هم مرج میکنه و اشتراکات رو نگه میداره.

ج) Sequential Scan (Full Table Scan)​

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

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

  • در PostgreSQL از EXPLAIN ANALYZE استفاده کنید.

۴. ری‌ ایندکس چیه؟ کی باید این کارو بکنیم؟​

اReindex چیست؟​

  • ایندکس‌ها ممکن است به مرور زمان متورم (Bloat) شوند، یعنی شامل فضاهای خالی یا بلااستفاده شوند.
  • با REINDEX دیتابیس ایندکس را از نو می‌سازد.

چه زمانی REINDEX کنیم؟​

🔹 ایندکس خیلی بزرگ شده و باعث کندی شده است.
🔹 اVacuum ایندکس را بهینه نکرده است.
🔹 خرابی (corruption) در ایندکس رخ داده است.
🔹 بعد از bulk delete حجم زیادی از داده حذف شده است.

روی دیتابیس پروداکشن چجوری ایندکس بسازیم؟​

به‌صورت دیفالت (CREATE INDEX عادی)، جدول برای عملیات نوشتن (INSERT, UPDATE, DELETE) قفل می‌شه (LOCK). یعنی تا وقتی که ایندکس ساخته نشده، هیچ تغییری روی جدول انجام نمی‌شه.

ولی میتونیم به صورت کانکارنت هم ایندکس بسازیم: درسته که CREATE INDEX CONCURRENTLY جدول رو برای عملیات نوشتن (INSERT, UPDATE, DELETE) قفل نمی‌کنه، اما در مرحله آخر یه قفل خیلی کوتاه و سبک (Short Lock) برای سینک کردن تغییرات نیاز داره. تا چیزی هم از دست نده

📌 پس فرآیند دقیق چیه؟​

1️⃣ اسکن اول: کل جدول خونده می‌شه و ایندکس ساخته می‌شه.
2️⃣ اسکن دوم: تغییرات جدید اعمال می‌شن.
3️⃣ یک قفل خیلی کوتاه (Short Lock) روی جدول اعمال می‌شه تا مطمئن بشیم همه رکوردهای جدید هم در ایندکس قرار دارن.

پس آیا همچنان احتمال از دست رفتن دیتا وجود داره؟​

خیر، PostgreSQL به‌صورت داخلی همه تغییرات رو مدیریت می‌کنه. 🚀
دلیلش اینه که وقتی CREATE INDEX CONCURRENTLY اجرا می‌شه، تغییرات جدید همچنان در WAL (Write-Ahead Log) ثبت می‌شن و در نهایت همه در ایندکس اعمال می‌شن. [[ACID#Durability]]

🔎 تفاوت قفل در CREATE INDEX معمولی vs CONCURRENTLY​

روشآیا جدول قفل می‌شود؟آیا چند اسکن انجام می‌دهد؟آیا امکان از دست رفتن دیتا وجود دارد؟
CREATE INDEX (عادی)✅ بله، تا پایان کار قفل می‌شود❌ فقط یک بار اسکن می‌کند❌ خیر، چون جدول قفل است
CREATE INDEX CONCURRENTLY🔄 فقط یک قفل خیلی کوتاه برای متادیتا✅ دو بار اسکن می‌کند❌ خیر، چون تغییرات جدید هم در ایندکس ثبت می‌شوند

نکات برای ایندکس​

  • ایندکس اضافه نکنید مگر لازم باشد! هر ایندکس هنگام نوشتن باعث هزینه اضافی می‌شود.
  • ایندکس روی ستون‌های زیاد ایجاد نکنید! ایندکس‌های چندستونه فقط در شرایط خاص مفیدند.
  • از EXPLAIN ANALYZE برای بررسی کارایی استفاده کنید.
  • ایندکس‌هایی که استفاده نمی‌شوند را حذف کنید! (pg_stat_user_indexes برای پیدا کردن ایندکس‌های بلااستفاده).
  • ترتیب نداشتن و توزیع تصادفی UUID → باعث Fragmentation و کندی درج می‌شه

مشکل:

  • اUUIDها به‌صورت تصادفی تولید می‌شن و ترتیب خاصی ندارن. وقتی یه UUID جدید اضافه می‌کنی، ممکنه وسط درخت قرار بگیره، نه انتها.
  • این یعنی در [[B+Tree]] یا [[B-Tree]]، مرتباً نودهای میانی دچار تغییر می‌شن، تقسیم گره (Node Splitting) اتفاق می‌افته و درخت از تعادل خارج می‌شه.
  • نتیجه: درج‌ها کندتر می‌شن، چون باید ساختار درخت رو مرتباً بازسازی کنیم.

مقایسه:
✅ اگه یه ایندکس عددی یا زمان‌بندی‌شده (مثلاً AUTO_INCREMENT در MySQL) استفاده بشه، داده‌ها به‌صورت ترتیبی اضافه می‌شن و درخت رشد یکنواختی داره.
❌ ولی UUID تصادفی باعث می‌شه که هر درج، نیاز به جابجایی زیادی داشته باشه و این کارایی رو کاهش می‌ده.

حجم بالای UUID → مصرف زیاد حافظه و کاهش کارایی کش (Cache Misses)​

مشکل:

  • اUUID 16 بایت (128 بیت) هست، در حالی که یک عدد BIGINT فقط 8 بایت (64 بیت) فضا می‌گیره.
  • وقتی ایندکس روی UUID باشه، اندازه نودهای درخت بزرگ‌تر می‌شه و این باعث می‌شه که داده‌های کمتری توی هر صفحه حافظه جا بشن. و جامپ هامون بیشتر شه و io بیشتری داشته باشیم.
  • این یعنی در پایگاه داده، وقتی می‌خوایم داده‌ای رو جستجو کنیم، به تعداد بیشتری صفحه دیسک (Disk Pages) نیاز داریم و نرخ کش شدن داده‌ها (Cache Hit Rate) کم می‌شه.
  • نتیجه: سرعت جستجو کاهش پیدا می‌کنه، چون دسترسی به دیسک بیشتر از RAM طول می‌کشه.

مقایسه:
✅ اگر به‌جای UUID از یه کلید عددی (BIGINT یا AUTO_INCREMENT) استفاده کنیم، اندازه ایندکس خیلی کوچیک‌تر می‌شه، داده‌های بیشتری توی RAM کش می‌شن و دسترسی‌ها سریع‌تره.
❌ ولی UUIDها باعث می‌شن که حافظه بیشتری مصرف بشه و نرخ کش شدن داده‌ها پایین بیاد.

میتونیم از [[Twitter Snowflake]] برای uuid هامون استفاده کنیم تا هم ترتیبی باشه هم کوتاه تر و هم ایدی دیتابیس رو به یوزر ندیم

برچسب‌ها:
آخرین به روز رسانی در تاریخ توسط mahdi momeni