آموزش ایندکس SQL و EXPLAIN با یک مثال کاربردی

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

SELECT id, total, created_at
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

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

CREATE INDEX idx_orders_user_status_date
  ON orders (user_id, status, created_at);

EXPLAIN SELECT id, total, created_at
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

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

• جدول کوچک است و پیمایش کامل ارزان‌تر است.
• شرط بخش بزرگی از جدول را برمی‌گرداند.
• آمار با دادهٔ فعلی سازگار نیست.
• ترتیب ستون‌ها با الگوی جست‌وجو همخوانی ندارد.
• تابع یا تبدیل نوع روی ستون استفاده از ایندکس معمولی را دشوار کرده است.

بهینه‌ساز موظف نیست هر ایندکس را انتخاب کند. اجبار به استفاده از ایندکس بدون دادهٔ نماینده می‌تواند نتیجه را بدتر کند.

EXPLAIN ANALYZE در موتورهای پشتیبان می‌تواند واقعاً کوئری را اجرا کند؛ رفتار نسخهٔ خود را بخوانید، مخصوصاً برای دستورهای تغییر داده. با SELECT محدود در دیتابیس آزمایشی شروع کنید.

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

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