Составные индексы и порядок столбцов

Введение

Индекс может охватывать не один, а несколько столбцов одновременно (составной, или многостолбцовый индекс) — в статье разберём, как порядок столбцов в таком индексе критично влияет на то, для каких именно запросов он окажется полезен, и как выбрать правильный порядок для конкретных, реальных паттернов запросов.

Концепция

Составной индекс упорядочивает строки сначала по первому указанному столбцу, и только в пределах одинакового значения первого столбца — по второму, и так далее — это означает, что составной индекс эффективен для запросов, фильтрующих по префиксу этого порядка столбцов (только по первому столбцу, или по первому и второму вместе), но обычно неэффективен (не используется СУБД вообще, или используется неоптимально) для запросов, фильтрующих только по второму или последующему столбцу индекса без учёта первого — аналогично тому, как телефонный справочник, отсортированный по фамилии, затем по имени, удобен для поиска по фамилии, но бесполезен для поиска исключительно по имени без знания фамилии.

Пример кода: Составные индексы и порядок столбцов

-- Составной индекс — порядок столбцов имеет значение
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Эффективно использует индекс — фильтрация по ПЕРВОМУ столбцу (customer_id)
SELECT * FROM orders WHERE customer_id = 5;

-- Также эффективно — фильтрация по ОБОИМ столбцам индекса в правильном порядке
SELECT * FROM orders WHERE customer_id = 5 AND order_date >= '2026-01-01';

-- НЕэффективно — фильтрация только по ВТОРОМУ столбцу без первого
-- индекс idx_orders_customer_date здесь, как правило, НЕ используется эффективно
SELECT * FROM orders WHERE order_date >= '2026-01-01';

-- Если оба паттерна запросов важны — нужны ДВА отдельных индекса,
-- а не один составной с "правильным" порядком для только одного из них
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date); -- для запросов по клиенту
CREATE INDEX idx_orders_date ON orders(order_date);                       -- для запросов только по дате

-- Составной индекс также может работать как "покрывающий" для определённых запросов
-- (подробнее в статье 415) — если все нужные столбцы есть в самом индексе
CREATE INDEX idx_orders_customer_date_amount ON orders(customer_id, order_date, total_amount);

-- Этот запрос может быть выполнен ИСКЛЮЧИТЕЛЬНО через индекс, без обращения к самой таблице
SELECT order_date, total_amount FROM orders WHERE customer_id = 5;

Пояснения к коду

Три запроса в первом примере явно демонстрируют принцип «эффективности по префиксу» — индекс (customer_id, order_date) эффективен для условий, начинающихся с customer_id (либо только по нему, либо по нему и затем по order_date), но не для условия, обращающегося лишь к order_date без customer_id, поскольку строки в индексе физически упорядочены сначала по customer_id, и значения order_date для разных клиентов разбросаны по всему индексу не в общем, едином порядке по дате. Решение через два отдельных индекса (а не попытка одного «универсального» составного индекса для обоих паттернов запроса) — практичный подход, когда оба паттерна действительно важны и часто используются — каждый индекс оптимизирован именно под свой конкретный сценарий использования.

Подводные камни

• Создание составного индекса со столбцами в порядке, не соответствующем реальным, частым паттернам запросов, например, по алфавиту имён столбцов или интуитивно «логичном» порядке без анализа того, как столбцы реально используются в условиях WHERE существующих запросов — порядок столбцов в составном индексе должен определяться исключительно реальными паттернами использования (какие столбцы фильтруются вместе и в каком порядке чаще всего), а не произвольными, не связанными с реальным использованием соображениями.
• Создание избыточных, перекрывающихся составных индексов — индекс (customer_id, order_date) уже эффективно покрывает запросы, фильтрующие только по customer_id (как показано в примере), и отдельный индекс (customer_id) в дополнение к нему обычно избыточен, поскольку не даёт ничего, что уже не покрывалось бы более широким составным индексом для этого конкретного паттерна запроса — избыточные индексы лишь добавляют ненужные накладные расходы на операции записи без реальной пользы для чтения.
• Слишком большое количество столбцов в одном составном индексе «на всякий случай», без понимания, что каждый дополнительный столбец увеличивает физический размер индекса и снижает эффективность кеширования его наиболее часто используемых частей в памяти — составной индекс должен включать именно те столбцы, что реально нужны для конкретного, обоснованного паттерна использования, а не максимально возможное их количество.
• Игнорирование того, что порядок столбцов в ORDER BY запроса также должен соответствовать порядку столбцов составного индекса для эффективного использования индекса именно для целей сортировки (а не только фильтрации) — индекс (customer_id, order_date) эффективен для ORDER BY customer_id, order_date, но не обязательно эффективен для ORDER BY order_date, customer_id (обратный порядок), даже если оба столбца физически присутствуют в одном и том же индексе.