ایندکس در SQL چیست و چه زمانی باید ساخته شود

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

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

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

طبق مستندات رسمی PostgreSQL، ایندکس ساختار داده‌ای مجزا از جدول اصلی است که به موتور پایگاه داده امکان می‌دهد ردیف‌های مشخص را بسیار سریع‌تر از حالت پیمایش کامل جدول بیابد. بدون ایندکس، سیستم مجبور است از ابتدای جدول تا انتها تمام سطرها را بخواند؛ فرایندی که به آن اسکن ترتیبی یا Sequential Scan می‌گویند.

نوع پیش‌فرض و رایج‌ترین شکل ایندکس، ساختار درخت متوازن یا B-Tree است. در این ساختار، مقادیر ستون به صورت مرتب نگهداری می‌شوند و کنار هر مقدار، نشانگری به محل فیزیکی رکورد در دیسک قرار دارد. هنگام جست‌وجو، پایگاه داده به جای خواندن میلیون‌ها رکورد، با تعداد انگشت‌شماری مقایسه در درخت به نشانی رکورد می‌رسد.

ساخت ایندکس با یک نمونه واقعی

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

sql-- ایجاد جدول کاربران
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- درج 100 هزار ردیف نمونه
INSERT INTO users (email)
SELECT 'user_' || seq || '@example.com'
FROM generate_series(1, 100000) AS seq;

-- بررسی برنامه اجرای جست‌وجو بدون ایندکس
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

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

textSeq Scan on users  (cost=0.00..1887.00 rows=1 width=37) (actual time=11.230..11.231 rows=1 loops=1)
  Filter: ((email)::text = '[email protected]'::text)
  Rows Removed by Filter: 99999
Execution Time: 11.265 ms

اکنون برای ستون ایمیل یک ایندکس استاندارد می‌سازیم و دوباره همان کوئری را آزمایش می‌کنیم:

sql-- ساخت ایندکس روی ستون ایمیل
CREATE INDEX idx_users_email ON users(email);

-- اجرای مجدد بررسی برنامه
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

خروجی پس از ایجاد ایندکس تغییر چشمگیری می‌کند:

textIndex Scan using idx_users_email on users  (cost=0.29..8.31 rows=1 width=37) (actual time=0.042..0.043 rows=1 loops=1)
  Index Cond: ((email)::text = '[email protected]'::text)
Execution Time: 0.068 ms

زمان اجرا از ۱۱ میلی‌ثانیه به کمتر از ۰.۰۷ میلی‌ثانیه رسید و هزینه محاسباتی از ۱۸۸۷ به ۸ واحد کاهش یافت.

چه زمانی باید ایندکس بسازیم

ایجاد ایندکس باید بر اساس الگوی واقعی پرس‌وجوهای سامانه انجام شود. در شرایط زیر ساخت ایندکس تصمیم درستی است:

• ستون‌هایی که مدام در شرط WHERE استفاده می‌شوند؛ مانند شناسه‌ها، شماره همراه یا وضعیت سفارش.
• کلیدهای خارجی یا ستون‌هایی که در اتصال جدول‌ها (JOIN) دخالت دارند.
• ستون‌هایی که بر اساس آن‌ها مرتب‌سازی (ORDER BY) سنگین یا گروه‌بندی (GROUP BY) انجام می‌دهید.
• ستون‌هایی با تنوع داده‌ای بالا (مانند کدهای رهگیری)؛ در مقابل ستون‌هایی که فقط دو یا سه حالت دارند معمولاً از ایندکس B-Tree سود چندانی نمی‌برند.

اشتباه رایج در استفاده از ایندکس

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

۱. اشغال حافظه دیسک و رم: هر ایندکس فضای جداگانه‌ای می‌گیرد و برای کارایی بهتر باید در حافظه موقت (RAM) قرار بگیرد.
۲. کاهش سرعت عملیات نوشتن: هنگام اجرای INSERT، UPDATE یا DELETE، پایگاه داده علاوه بر تغییر داده اصلی، باید تمام درخت‌های ایندکس مربوط به آن ستون‌ها را هم به‌روز کند.

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

گام بعدی برای تسلط بر پرس‌وجوها

برای درک عمیق‌تر نحوه تصمیم‌گیری بهینه‌ساز دیتابیس، تحلیل خروجی دستور EXPLAIN و طراحی اصولی پایگاه داده، می‌توانید سرفصل‌های دوره پایگاه داده و SQL را دنبال کنید و معماری جداول خود را از پایه استاندارد پیاده‌سازی کنید.

سؤالات پرتکرار

ایندکس در SQL چیست؟
ساختاری جدا از جدول که جست‌وجوی ردیف‌ها را بدون اسکن کامل جدول سریع‌تر می‌کند؛ رایج‌ترین نوع آن B-Tree است.

چه زمانی ایندکس نسازیم؟
روی ستون‌هایی با تنوع خیلی کم، یا وقتی نرخ نوشتن از خواندن خیلی بالاتر است و هزینه به‌روزرسانی ایندکس بیشتر از سود خواندن است.