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

نحوه اثرگذاری ترتیب ستون‌ها: قانون پیشوند چپ

در ایندکس‌های B-Tree چندستونه، داده‌ها بر اساس ترتیب تعریف‌شده از چپ به راست مرتب می‌شوند. موتور می‌تواند از هر پیشوند چپ ایندکس برای جستجو استفاده کند؛ مثلا برای ایندکس (a,b,c) این پیشوندها قابل استفاده‌اند: (a)، (a,b) و (a,b,c). اگر شرط شما روی a مساوی باشد و روی b بازه‌ای، موتور می‌تواند بسیار سریع همه رکوردهای a=x و سپس محدوده b را پیدا کند. اما اگر ترتیب ایندکس (b,a) باشد، همان شرط به استفاده کامل از ایندکس منجر نمی‌شود و هزینه اسکن بالا می‌رود.

چک لیست عملی انتخاب ترتیب ستون‌ها

  • ستون‌هایی که در WHERE یا JOIN با مساوی (= یا IN) می‌آیند و گزینش پذیری (selectivity) بالاتری دارند را در ابتدای ایندکس بگذارید.
  • ستون‌های JOIN برابری‌محور معمولا باید جلوتر از بقیه باشند؛ چون دامنه داده را سریع کوچک می‌کنند.
  • پس از مساوی‌ها، ستون(های) بازه‌ای (BETWEEN، >، <) را قرار دهید. بعد از اولین شرط بازه‌ای، ستون‌های بعدی برای فیلتر عمیق معمولا بی‌اثر می‌شوند.
  • اگر ORDER BY یا GROUP BY دارید و جهت مرتب‌سازی با ایندکس سازگار است، ستون‌های آن را بلافاصله پس از آخرین شرط مساوی قرار دهید تا مرتب‌سازی اضافی حذف شود.
  • در صورت وجود چند الگوی پرکاربرد و متضاد، یک ایندکس بهینه برای هر الگو بسازید؛ تلاش برای پوشش همه سناریوها با یک ترتیب معمولا به مصالحه پرهزینه منجر می‌شود.
  • هزینه نگهداری را فراموش نکنید: هرچه ایندکس بزرگ‌تر و تعدادشان بیشتر باشد، INSERT/UPDATE/DELETE کندتر می‌شود.

مثال عملی: سفارش‌ها و تاثیر جابه‌جایی ترتیب

جدول سفارش‌ها را در نظر بگیرید:

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  total_amount NUMERIC(12,2) NOT NULL
);

الگوی پرتکرار کوئری:

-- آخرین سفارش‌های یک مشتری در بازه زمانی با مرتب‌سازی زمانی
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = :cid
  AND created_at BETWEEN :from AND :to
ORDER BY created_at DESC
LIMIT 50;

دو گزینه ایندکس داریم:

-- گزینه 1: مساوی جلو، سپس بازه، سازگار با ORDER BY
CREATE INDEX idx_orders_cust_created ON orders (customer_id, created_at DESC);

-- گزینه 2: ابتدا زمان، سپس مشتری
CREATE INDEX idx_orders_created_cust ON orders (created_at DESC, customer_id);

در گزینه 1، موتور با مساوی روی customer_id پیشوند کامل را شروع می‌کند، سپس بازه زمانی را محدود می‌کند و چون جهت مرتب‌سازی نیز منطبق است، معمولا نیازی به مرتب‌سازی اضافی نیست. در گزینه 2، فیلتر اصلی روی customer_id از پیشوند چپ استفاده نمی‌کند؛ موتور باید محدوده زمانی بزرگ‌تری را اسکن کند و سپس روی customer_id فیلتر بزند و احتمالا مرتب‌سازی مجدد انجام دهد.

آزمون سریع با EXPLAIN

برای سنجش، از EXPLAIN/EXPLAIN ANALYZE استفاده کنید:

EXPLAIN
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 123
  AND created_at BETWEEN '2026-01-01' AND '2026-12-31'
ORDER BY created_at DESC
LIMIT 50;

در خروجی به این موارد نگاه کنید: نوع دسترسی (Index Scan/Range Scan)، استفاده از ایندکس موردنظر، حذف یا وجود عملیات Sort، و تعداد ردیف‌های تخمینی/واقعی. اگر Sort حذف شده و تعداد ردیف‌های اسکن‌شده کم باشد، ترتیب ایندکس مناسب است.

تاثیر مساوی، بازه، مرتب‌سازی و گروه‌بندی

  • مساوی (=، IN): بهترین دوست ایندکس چندستونه است. چند مساوی پشت سر هم (a=…, b IN (…), c=…) به استفاده از پیشوندهای عمیق‌تر کمک می‌کند.
  • بازه (BETWEEN، >، <): اولین ستون بازه‌ای معمولا نقطه توقف بهره‌گیری از ستون‌های بعدی برای فیلتر است. بنابراین آن را پس از مساوی‌ها قرار دهید.
  • ORDER BY: اگر ترتیب ستون‌ها و جهت‌ها با ایندکس همخوان باشند، مرتب‌سازی حذف می‌شود. برای برخی موتورها، ایندکس نزولی (DESC) نیز قابل تعریف است. اگر جهت‌ها ناسازگار باشند، Sort اجرا می‌شود.
  • GROUP BY: مشابه ORDER BY است. اگر گروه‌بندی با پیشوند ایندکس هم‌راستا باشد، هزینه کاهش می‌یابد.

سناریوهای مقایسه‌ای کوتاه

-- کوئری 1: فیلتر برابری + بازه + مرتب‌سازی
WHERE status = 'PAID' AND created_at BETWEEN :f AND :t
ORDER BY created_at

-- ایندکس پیشنهادی
CREATE INDEX idx1 ON orders (status, created_at);

-- کوئری 2: فقط بازه زمانی و مرتب‌سازی زمانی
WHERE created_at BETWEEN :f AND :t
ORDER BY created_at

-- ایندکس پیشنهادی (ساده‌تر)
CREATE INDEX idx2 ON orders (created_at);

اگر عمده بار کاری شما شبیه کوئری 1 است، idx1 بهتر است. اگر اغلب بدون فیلتر status جستجو می‌کنید، idx2 به صرفه‌تر است. تلاش برای پوشش توامان هر دو با یک ترتیب، اغلب هیچ‌کدام را عالی نمی‌کند.

پوشش ایندکس (Covering) و جایگاه ستون‌ها

وقتی همه ستون‌های لازم کوئری از خود ایندکس تامین شوند، موتور می‌تواند بدون مراجعه به جدول نتیجه بگیرد (Index-Only Scan). ترتیب ستون‌ها برای پوشش بودن ایندکس شرط نیست؛ اما برای حذف Sort و محدود کردن اسکن اهمیت دارد. اگر موتور شما امکان افزودن ستون‌های شامل‌شونده (INCLUDE) را می‌دهد، آن‌ها را پس از کلیدهای جستجو اضافه کنید تا هم پوشش حفظ شود و هم کلیدها کوچک و موثر بمانند.

اشتباهات رایج و محدودیت‌ها

  • شروع ایندکس با ستونی با گزینش پذیری بسیار پایین (مثل status با چند مقدار ثابت)، وقتی ستون گزینش‌پذیرتر در ادامه است. این کار دامنه اسکن را به اندازه کافی کوچک نمی‌کند.
  • قرار دادن ستون بازه‌ای در ابتدا؛ ستون‌های بعدی عملا برای فیلتر عمیق استفاده نمی‌شوند.
  • استفاده از تابع روی ستون اول در WHERE (مثل date(created_at) = …). این کار معمولا جستجوی ایندکسی را غیرقابل استفاده می‌کند. شرط را بازنویسی کنید تا قابل شاخص‌گذاری (sargable) بماند: created_at BETWEEN ts_start AND ts_end.
  • افزودن ستون‌های زیاد به ایندکس با این امید که «همه چیز» را پوشش دهد؛ هزینه به‌روزرسانی و اندازه ایندکس بالا می‌رود و مزیت آن برای بیشتر کوئری‌ها ناچیز است.
  • نادیده گرفتن جهت مرتب‌سازی. اگر نتایج را نزولی می‌خواهید، جهت ستون مرتب‌سازی را در ایندکس مطابق نیاز تعریف کنید، مشروط به پشتیبانی موتور شما.
  • یک ایندکس برای الگوهای کاملا متفاوت. برای سناریوهای متضاد، دو ایندکس باریک معمولا بهتر از یک ایندکس پهن و سازش‌کار است.

چگونه انتخاب خود را بسنجیم؟

  1. ۳ تا ۵ کوئری پرهزینه را از لاگ کند یا ابزارهای پروفایلینگ دیتابیس استخراج کنید.
  2. برای هر کوئری، ستون‌های مساوی، بازه‌ای، مرتب‌سازی و گروه‌بندی را مشخص کنید.
  3. دو یا سه ترتیب نامزد بر اساس چک لیست بسازید و ایندکس‌های آزمایشی ایجاد کنید.
  4. با EXPLAIN (و در صورت امکان ANALYZE) بررسی کنید: نوع دسترسی، وجود/حذف Sort، ردیف‌های اسکن‌شده و زمان تقریبی.
  5. در داده واقعی و با LIMITهای منطقی تست کنید؛ سپس بهترین را نگه دارید و باقی را حذف کنید.

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

اگر دو الگوی پرتکرار شما پیشوندهای متفاوتی می‌خواهند (مثلا یکی با customer_id شروع می‌شود و دیگری با status)، ساخت دو ایندکس باریک‌تر معقول است. همچنین اگر یک ایندکس برای حذف Sort طراحی شده اما کوئری دیگری فقط به فیلتر سریع نیاز دارد، ایندکس دوم مجرد و کوچک روی ستون فیلتر می‌تواند ارزان و موثر باشد.

نتیجه نهایی

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