Как порядок колонок в составном индексе влияет на производительность запроса?

Составной индекс в реляционных СУБД (PostgreSQL, MySQL, MSSQL) — это структура B-tree, где ключи сортируются сначала по первой колонке, затем внутри одинаковых значений — по второй, и так далее. Это означает, что индекс можно эффективно использовать только для запросов, которые фильтруют по непрерывному левому префиксу колонок индекса. Это правило часто называют leftmost prefix rule.

Как это работает на практике

Представим индекс (last_name, first_name). Он отсортирован по фамилии, а внутри одинаковых фамилий — по имени. Запрос WHERE last_name = 'Ivanov' использует индекс. Запрос WHERE last_name = 'Ivanov' AND first_name = 'Petr' тоже использует индекс полностью. А вот WHERE first_name = 'Petr' индекс не использует, потому что без фамилии невозможно найти нужный диапазон — имена разбросаны по всему дереву.

Селективность и порядок колонок

Классическая рекомендация — ставить более селективные (уникальные) колонки первыми. Однако на практике важнее, чтобы первая колонка использовалась в запросах с равенством. Если первая колонка имеет низкую селективность (например, status с 3 значениями), но всегда участвует в фильтрации, индекс всё равно будет полезен. Если же первая колонка редко фильтруется, индекс может оказаться бесполезным.

-- Индекс (status, created_at)
SELECT * FROM orders
WHERE status = 'paid' AND created_at > '2024-01-01';
-- Использует индекс эффективно

SELECT * FROM orders
WHERE created_at > '2024-01-01';
-- Индекс НЕ используется (нет левого префикса)

Покрывающий индекс

Если индекс содержит все колонки, нужные запросу (в ключе или в INCLUDE), СУБД может выполнить index-only scan без обращения к таблице. Порядок колонок в ключе при этом всё ещё важен для фильтрации, но дополнительные колонки можно добавить в конец или в INCLUDE-часть.

Практические рекомендации

• Ставьте первой колонку, которая чаще всего используется в равенствах в WHERE.
• Колонки с диапазонными условиями (>, <, BETWEEN) ставьте последними — после них индекс перестаёт использоваться для фильтрации.
• Учитывайте порядок в ORDER BY: индекс может убрать сортировку, если порядок колонок совпадает.
• Проверяйте план запроса через EXPLAIN — планировщик сам решает, использовать ли индекс.

Итог: порядок колонок в составном индексе определяет, какие запросы смогут его использовать. Проектируйте индекс под конкретные запросы, ставя первой колонку с равенством и высокой частотой использования, а диапазонные условия — в конец.