ایندکس در پشت صحنه چطور باعث سریع تر شدن کوئری میشه؟

معنی اینکه هر ایندکس یک ساختار داده جدا تشکیل میده چیه؟

در این پست، به بررسی موارد زیر می پردازم:

✅ چگونگی عملکرد ایندکسها

✅ ساختار داده‌های آنها (درخت B+)

✅ نحوه به‌روزرسانی هنگام درج، حذف یا تغییر داده

🏗️ ایندکس چیست و چگونه ساخته میشود؟

وقتی یک ستون را ایندکس میکنید، MySQL یک ساختار داده جداگانه (معمولاً یک درخت B+) ایجاد میکند که شامل مقدارهای آن ستون و آدرس (Pointer) ردیفهای متناظر در جدول اصلی است.

مزیت اصلی ایندکس این است که به‌جای جستجوی خطی در کل جدول، MySQL میتواند داده‌های موردنظر را بسرعت از طریق این ساختار پیدا کند.

🎯 مثال

جدولی با اطلاعات کاربران را در نظر بگیرید:

با اجرای دستور زیر، یک ایندکس روی ستون name ایجاد میکنیم:

CREATE INDEX idx_name ON users(name);

در این حالت، MySQL یک درخت B+ برای مقادیر ستون name ساخته و از آن برای جستجوی سریع استفاده میکند.

🔍 ساختار درخت B+ و نحوه جستجو در آن

درخت B+ یک ساختار داده متوازن است که سه نوع گره دارد:

• گره ریشه (Root Node): نقطه شروع جستجو
• گره‌های میانی (Internal Nodes): مسیر مناسب را مشخص میکنند
• گره‌های برگ (Leaf Nodes): شامل مقادیر ایندکس شده و اشاره‌گر به ردیف‌های اصلی

🎯 مثال تصویری

فرض کنید ایندکس ما به این صورت ذخیره شده باشد:

اگر بخواهیم مقدار "ژاکان" را جستجو کنیم:

1️⃣ از گره ریشه [روژان, هیوا] شروع میکنیم.

2️⃣ چون "ژاکان" از "روژان" بزرگتر ولی از "هیوا" کوچکتر است، به گره میانی مربوطه میرویم.

3️⃣ مقدار "ژاکان" را در گره برگ پیدا کرده و به ردیف اصلی جدول (ردیف با id=3) مراجعه میکنیم.

مزیت این روش: ✅ بجای جستجوی خطی در تمام ردیف‌ها، فقط چند مقایسه انجام میشود. ✅ پیچیدگی جستجو O(log N) است که بسیار سریعتر از O(N) در جستجوی خطی است.

🔄 به‌روزرسانی ایندکس‌ها هنگام تغییر داده

1️⃣ درج داده جدید

هنگامی که یک مقدار جدید به جدول اضافه شود، مقدار مربوط به ستون ایندکس شده نیز در درخت B+ درج میشود.

مثال:

INSERT INTO users (id, name, email, age)

VALUES (6, 'متین', 'matin@example.com', 29);

🔹 مقدار "متین" در محل مناسب درج میشود. 🔹 اگر یک گره پر شود، عملیات تقسیم (Split) انجام می‌شود تا درخت متعادل بماند.

2️⃣ حذف داده

مثلاً اگر داده زیر را حذف کنیم:

DELETE FROM users WHERE name = 'روژان';

🔸 مقدار "روژان" از درخت B+ حذف می‌شود. 🔸 درخت به‌صورت خودکار مجدداً سازماندهی شده تا تعادل خود را حفظ کند.

3️⃣ به‌روزرسانی داده

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

مثلاً تغییر "هیوا" به "هستی":

UPDATE users SET name = 'هستی' WHERE id = 4;

✅ ابتدا مقدار "هیوا" از ایندکس حذف میشود.

✅ سپس مقدار جدید "هستی" در مکان مناسب درج میشود.

🔹 نتیجه: این فرآیند ممکن است کمی سربار ایجاد کند، به همین دلیل باید ایندکس‌ها را هوشمندانه انتخاب کنیم.

⚡ چرا استفاده از ایندکس مهم است؟

🔻 بدون ایندکس: MySQL مجبور است تمام رکوردها را بررسی کند (Full Table Scan) که برای جداول بزرگ، بسیار کند است.

✅ با ایندکس: MySQL از درخت B+ برای پیدا کردن مقدار استفاده می‌کند، که زمان جستجو را از O(N) به O(log N) کاهش میدهد.

🔸 مزایا: ✔ افزایش سرعت جستجوها ✔ کاهش تعداد مقایسه‌ها

🔹 معایب: ❌ ایندکسها در هنگام درج/حذف/بهروزرسانی، سربار دارند. ❌ استفاده بیش از حد از ایندکس‌ها میتواند کارایی عملیات نوشتن را کاهش دهد.

📌 نتیجه‌گیری ✔ ایندکسها ساختار دادهای جداگانه (مثل درخت B+) هستند که مقادیر یک ستون و آدرس ردیفهای متناظر را ذخیره میکنند. ✔ استفاده از ایندکس باعث بهبود سرعت جستجو میشود، اما هزینه‌هایی برای عملیات درج، حذف و به‌روزرسانی دارد. ✔ باید از ایندکسها بهینه و هوشمندانه استفاده کنیم تا تأثیر مثبتی بر عملکرد پایگاه داده داشته باشند.

💬 نظر شما چیست؟ اگر شما هم از چالشهای خودتون در این زمینه بنویسید برای همه ارزشمند است.

منبع اصلی این پست ها دوره های Aaron Francis است.با تشکر از ارون عزیز!