ایندکس ترکیبی، قانون leftmost prefix، Covering و Invisible Index

ترتیب ستون‌ها همه‌چیز است

ایندکس ترکیبی (status, customer_id, created_at) مثل دفترچه تلفنی است که اول بر اساس نام خانوادگی، بعد نام و بعد شهر مرتب شده. پیدا کردن «همه‌ی محمدی‌ها» سریع است؛ پیدا کردن «همه‌ی علی‌ها» بدون دانستن نام خانوادگی نه. این قانون leftmost prefix است.

ALTER TABLE orders ADD INDEX ix_s_c_d (status, customer_id, created_at);

شرط	استفاده از ایندکس
`status = 'paid'`	بله، ستون اول
`status = 'paid' AND customer_id = 42`	بله، دو ستون
`status = 'paid' AND customer_id = 42 AND created_at >= '2025-01-01'`	بله، هر سه ستون
`customer_id = 42`	معمولاً نه (ستون اول نیامده)
`status = 'paid' AND created_at >= '2025-01-01'`	فقط ستون اول؛ created_at با فیلتر روی ردیف‌ها بررسی می‌شود
`status IN ('paid','shipped') AND customer_id = 42`	بله؛ IN مثل چند برابری عمل می‌کند

قاعده‌ی طلایی: برابری اول، بازه آخر

بعد از اولین ستونی که با بازه (>، BETWEEN، LIKE 'x%') فیلتر شود، ستون‌های بعدی ایندکس برای جست‌وجو استفاده نمی‌شوند. پس ستون‌هایی که با = فیلتر می‌شوند جلو، و ستون بازه یا مرتب‌سازی آخر.

-- کوئری پرتکرار پنل: سفارش‌های پرداخت‌شده‌ی یک مشتری، جدیدترین اول
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

-- ایندکس مناسب: دو برابری، سپس ستون مرتب‌سازی
ALTER TABLE orders ADD INDEX ix_cust_status_created (customer_id, status, created_at);

Covering Index: بدون مراجعه به جدول

اگر همه‌ی ستون‌های مورد نیاز کوئری در خود ایندکس باشند، مرحله‌ی lookup به clustered index حذف می‌شود و در EXPLAIN عبارت Using index می‌آید. با افزودن total_amount به انتهای ایندکس بالا، کوئری کاملاً از ایندکس پاسخ داده می‌شود (id که کلید اصلی است خودش در ایندکس هست).

ابزارهای MySQL 8

-- ایندکس نزولی واقعی (قبل از 8.0 کلمه‌ی DESC نادیده گرفته می‌شد)
ALTER TABLE orders ADD INDEX ix_created_desc (created_at DESC);

-- ایندکس تابعی (8.0.13): پرانتز دوتایی الزامی است
ALTER TABLE orders ADD INDEX ix_order_day ((DATE(created_at)));

-- ایندکس نامرئی: آزمایش حذف بدون حذف واقعی
ALTER TABLE orders ALTER INDEX ix_customer INVISIBLE;
-- چند روز پایش؛ اگر چیزی کند نشد:
ALTER TABLE orders DROP INDEX ix_customer;
-- اگر کند شد، فوری و بدون بازسازی:
ALTER TABLE orders ALTER INDEX ix_customer VISIBLE;

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

نکته‌هایی که کمتر کسی می‌داند

از 8.0.13 «Index Skip Scan» گاهی ایندکس (status, created_at) را برای شرط فقط روی created_at هم به کار می‌گیرد، به شرط کم‌تنوع بودن ستون اول؛ در EXPLAIN با Using index for skip scan دیده می‌شود.
ایندکس پیشوندی INDEX (title(20)) برای ستون‌های متنی بلند فضا را کم می‌کند، اما هرگز covering نیست و برای ORDER BY کامل هم به کار نمی‌آید.
ایندکس UNIQUE ترکیبی با ستون NULLپذیر، ردیف‌های تکراری را که یک جزء NULL دارند رد نمی‌کند؛ (order_id, coupon_code) با coupon_code NULL چند بار ثبت می‌شود.
با SET SESSION optimizer_switch = 'use_invisible_indexes=on'; می‌توانید اثر یک ایندکس نامرئی را فقط در نشست خودتان آزمایش کنید.
sys.schema_unused_indexes ایندکس‌هایی را نشان می‌دهد که از آخرین راه‌اندازی سرور استفاده نشده‌اند؛ اما گزارش ماهانه‌ای که هنوز اجرا نشده هم آنجاست — قبل از حذف، نامرئی کنید.

برای ذخیره‌ی پیشرفت و شرکت در آزمون، وارد شوید — رایگان است.