«یک index اضافه کن» رایج‌ترین توصیه برای query کند است، و اتفاقاً یکی از خطرناک‌ترین‌ها. index اشتباه فقط بی‌اثر نیست؛ می‌تواند query را از حالت بدون index هم کندتر کند. در این مقاله با یک آزمایش واقعی روی یک میلیون سفارش نشان می‌دهم ترتیب ستون‌ها در index ترکیبی MySQL چطور زمان یک query رایج Laravel را از حدود یک ثانیه به کمتر از نیم میلی‌ثانیه می‌رساند، و چرا خروجی EXPLAIN گاهی گمراه‌کننده است.

اگر هنوز نمی‌دانید گلوگاه درخواست کندتان کجاست، اول راهنمای رفع کندی Laravel و MySQL را بخوانید. این مقاله فرض می‌کند query مقصر را پیدا کرده‌اید و حالا باید index درست را برایش طراحی کنید.

محیط آزمایش؛ تا عددها قابل تکرار باشند

همه عددهای این مقاله را خودم روی محیط زیر اندازه گرفته‌ام. عدد مطلق روی سرور شما فرق می‌کند؛ چیزی که مهم است نسبت بین گزینه‌هاست.

  • MySQL 8.4.11 داخل Docker روی یک Apple M2 Pro، با innodb_buffer_pool_size برابر یک گیگابایت (داده کامل در حافظه جا می‌شود).
  • جدول orders با ۱٬۰۰۰٬۰۰۰ ردیف و seed ثابت برای تولید داده: حدود ۵۰ هزار مشتری، به‌طور میانگین ۲۰ سفارش برای هر مشتری، و توزیع وضعیت ۷۰٪ paid، ۱۵٪ pending، ۱۰٪ cancelled و ۵٪ refunded.
  • هر حالت بعد از سه اجرای گرم‌کردن، ۵۰ بار اجرا شده و میانه (median) گزارش شده است. پیش از هر حالت همه indexهای غیر از کلید اصلی حذف شده‌اند.
CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  status VARCHAR(16) NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

query مورد بررسی

یک صفحه «سفارش‌های پرداخت‌شده من» در پنل مشتری؛ الگویی که تقریباً در هر فروشگاه و SaaS وجود دارد:

$orders = Order::query()
    ->where('customer_id', $customer->id)
    ->where('status', 'paid')
    ->latest()          // ORDER BY created_at DESC
    ->limit(20)
    ->get(['id', 'total', 'created_at']);

SQL تولیدشده یک شرط برابری روی customer_id، یک شرط برابری روی status و یک مرتب‌سازی روی created_at دارد. همین سه بخش تعیین می‌کنند index خوب چه شکلی باید داشته باشد.

نتیجه اندازه‌گیری هفت گزینه

indexمیانه زمانخلاصه EXPLAIN
بدون index۱۳۷٫۸ mstype=ALL، حدود یک میلیون ردیف، Using filesort
(status)۶۲۸٫۶ mstype=ref، تخمین ۴۹۸ هزار ردیف، Using filesort
(customer_id)۰٫۳۷ ms۲۰ ردیف، Using where; Using filesort
(customer_id, status)۰٫۳۳ ms۱۵ ردیف، Using filesort
(customer_id, status, created_at)۰٫۲۸ ms۱۵ ردیف، Backward index scan، بدون filesort
(status, customer_id, created_at)۰٫۳۵ ms۱۵ ردیف، Backward index scan
(created_at, customer_id, status)۹۶۵٫۱ mstype=index، تخمین فقط ۲۰ ردیف

دو ردیف این جدول باید هر توسعه‌دهنده‌ای را به فکر فرو ببرد.

غافلگیری اول: index روی ستون کم‌تنوع، query را ۴٫۵ برابر کندتر کرد

با index روی status زمان از ۱۳۸ به ۶۲۹ میلی‌ثانیه رسید. ستون status فقط چهار مقدار دارد و ۷۰٪ ردیف‌ها paid هستند. MySQL برای هر ردیفی که از index پیدا می‌کند باید به ردیف اصلی در کلید اصلی سر بزند تا customer_id را بخواند. صدها هزار پرش تصادفی، از یک full scan ترتیبی گران‌تر است. بهینه‌ساز این بار هزینه را درست تخمین نزد و index را انتخاب کرد.

درس عملی: indexهای تک‌ستونی روی ستون‌هایی مثل status، type یا is_active را که «برای احتیاط» در migration اضافه می‌شوند، با یک query واقعی بسنجید. اگر کمکی نمی‌کنند، حذفشان هزینه نوشتن و حافظه را هم کم می‌کند.

غافلگیری دوم: EXPLAIN گفت ۲۰ ردیف، واقعیت یک میلیون ردیف بود

با index (created_at, customer_id, status) خروجی EXPLAIN عدد ۲۰ را برای rows نشان داد؛ ظاهراً عالی است. اما زمان واقعی ۹۶۵ میلی‌ثانیه بود، یعنی هفت برابر بدتر از حالت بدون index. EXPLAIN ANALYZE که query را واقعاً اجرا می‌کند، علت را نشان می‌دهد:

-> Limit: 20 row(s)  (cost=2 rows=0.2) (actual time=220..1111 rows=15 loops=1)
    -> Filter: ((orders.status = 'paid') and (orders.customer_id = 37448))
        -> Index scan on orders using ix_ccs (reverse)
           (cost=2 rows=20) (actual time=0.204..1051 rows=1e+6 loops=1)

بهینه‌ساز فکر کرد چون index از قبل بر اساس created_at مرتب است، با پیمایش معکوس خیلی زود ۲۰ ردیف مطابق پیدا می‌کند. اما این مشتری فقط ۱۵ سفارش پرداخت‌شده داشت، پس MySQL کل یک میلیون ورودی index را پیمود تا مطمئن شود ردیف دیگری نیست. همان query با index درست:

-> Limit: 20 row(s)  (cost=5.25 rows=15) (actual time=0.0403..0.0688 rows=15 loops=1)
    -> Index lookup on orders using ix_csc (customer_id=37448, status='paid') (reverse)

درس عملی: برای تصمیم درباره index به EXPLAIN تنها اکتفا نکنید. در MySQL 8 از EXPLAIN ANALYZE استفاده کنید و ستون actual rows را با rows تخمینی مقایسه کنید. اختلاف بزرگ یعنی بهینه‌ساز اشتباه کرده یا آمار جدول قدیمی است.

قاعده ترتیب ستون‌ها؛ برابری، بعد بازه یا مرتب‌سازی

InnoDB یک index ترکیبی را مثل دفترچه تلفنی می‌خواند که اول بر اساس ستون اول، بعد ستون دوم و به همین ترتیب مرتب شده است. از این واقعیت یک قاعده ساده بیرون می‌آید:

  1. اول ستون‌هایی که با برابری (= یا IN) فیلتر می‌شوند. ترتیب بین خودشان معمولاً اهمیت کمی دارد؛ در جدول بالا (customer_id, status, …) و (status, customer_id, …) تقریباً یکسان بودند.
  2. بعد ستون مرتب‌سازی یا ستونی که روی آن بازه می‌گیرید (>، <، BETWEEN). وقتی ستون‌های برابری ثابت شده‌اند، ردیف‌های باقی‌مانده از قبل بر اساس این ستون مرتب‌اند و filesort حذف می‌شود.
  3. بعد از اولین ستون بازه، ستون‌های بعدی برای جستجو کمکی نمی‌کنند؛ فقط ممکن است برای «covering index» مفید باشند.

ستون با بیشترین تنوع (selectivity) را اول گذاشتن، توصیه رایج اما ناقصی است. چیزی که تعیین‌کننده است نوع شرط است، نه فقط تعداد مقادیر یکتا.

وقتی مرتب‌سازی مهم‌تر از فیلتر است

در query مشتری، تفاوت (customer_id) و index کامل فقط چند صدم میلی‌ثانیه بود، چون هر مشتری حدود ۲۰ سفارش دارد و مرتب‌کردن ۲۰ ردیف ارزان است. اما در فید ادمین که سفارش‌های پرداخت‌شده کل فروشگاه را نشان می‌دهد، ماجرا فرق می‌کند:

SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
indexمیانه زمان
بدون index۱۵۶٫۸ ms
(status)۴۱۱٫۷ ms
(status, created_at)۱٫۵۶ ms

اینجا ستون مرتب‌سازی در index حدود صد برابر تفاوت ایجاد کرد، و index تک‌ستونی status دوباره از بدون index بدتر بود. وقتی فیلتر بخش بزرگی از جدول را برمی‌گرداند، کار اصلی دیتابیس مرتب‌سازی است و index باید همان را حذف کند.

چک‌لیست طراحی index ترکیبی برای یک query لاراول

  1. SQL واقعی را با bindingها بگیرید؛ مثلاً با DB::enableQueryLog() در محیط توسعه یا slow query log در production.
  2. شرط‌ها را دسته‌بندی کنید: برابری، بازه، مرتب‌سازی و ستون‌های SELECT.
  3. index پیشنهادی را با ترتیب «برابری‌ها، سپس بازه یا مرتب‌سازی» بنویسید.
  4. روی داده‌ای با حجم و توزیع شبیه production تست کنید. توزیع مهم است؛ index روی status با ۷۰٪ paid با همان index روی ستونی با توزیع یکنواخت رفتار یکسانی ندارد.
  5. زمان را چند بار اندازه بگیرید و میانه را مقایسه کنید، نه یک اجرای تصادفی.
  6. با EXPLAIN ANALYZE مطمئن شوید actual rows با انتظار شما هم‌خوان است و Using filesort در جاهایی که انتظار ندارید نیست.
  7. indexهای قدیمی را که با index جدید پوشش داده می‌شوند پیدا و حذف کنید. index (customer_id, status, created_at) نیاز به (customer_id) را از بین می‌برد.
  8. اثر روی نوشتن را فراموش نکنید. هر index هر INSERT و UPDATE مرتبط را کمی گران‌تر می‌کند؛ در جدول‌های پرنوشتن، تعداد index مهم است.

تعریف index در migration لاراول

در Laravel ترتیب آرایه همان ترتیب ستون‌ها در index است. یک نام صریح بدهید تا حذف یا بررسی بعدی ساده باشد:

Schema::table('orders', function (Blueprint $table) {
    $table->index(['customer_id', 'status', 'created_at'], 'orders_customer_status_created_idx');
    $table->dropIndex('orders_customer_id_index'); // index قدیمی که حالا اضافه است
});

روی جدول بزرگ production، ساخت index می‌تواند زمان‌بر باشد. InnoDB در MySQL 8 ساخت index ثانویه را به‌صورت online انجام می‌دهد، اما فشار I/O و فضای دیسک را در نظر بگیرید و migration را در زمان کم‌ترافیک اجرا کنید.

اشتباه‌های رایجی که در پروژه‌ها می‌بینم

  • index جداگانه برای هر ستون. MySQL معمولاً برای یک جدول در یک query فقط از یک index استفاده می‌کند. سه index تک‌ستونی جای یک index ترکیبی درست را نمی‌گیرند.
  • تابع روی ستون index‌شده. شرطی مثل whereDate('created_at', $day) به DATE(created_at) = ? تبدیل می‌شود و استفاده از index را برای آن ستون محدود می‌کند. بازه صریح بنویسید: whereBetween('created_at', [$start, $end]).
  • مرتب‌سازی در جهت‌های متفاوت. اگر query بر اساس created_at DESC, id ASC مرتب می‌کند، index معمولی هر دو را با یک پیمایش پوشش نمی‌دهد. یا جهت‌ها را یکسان کنید یا از index با ستون نزولی در MySQL 8 استفاده کنید.
  • اعتماد به داده توسعه. با هزار ردیف همه indexها سریع‌اند. تصمیم را با حجم و توزیع واقعی بگیرید.

قدم بعدی

وقتی index درست شد، معمولاً گلوگاه بعدی صفحه‌بندی صفحه‌های عمیق است. در مقاله pagination مبتنی بر cursor یا offset در Laravel با همین داده نشان داده‌ام چرا OFFSET بزرگ کند می‌شود و چه زمانی cursorPaginate انتخاب بهتری است.

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

پرسش‌های متداول

آیا برای هر ستونی که در where می‌آید باید index جدا ساخت؟

نه. معمولاً یک index ترکیبی هماهنگ با شرط‌ها و مرتب‌سازی query بهتر از چند index تک‌ستونی است و هزینه نوشتن کمتری هم دارد.

بعد از ساخت index، از کجا بفهمم واقعاً استفاده می‌شود؟

با EXPLAIN ANALYZE در MySQL 8 تعداد ردیف واقعی خوانده‌شده را ببینید و زمان query را چند بار قبل و بعد اندازه بگیرید.