Почему важен порядок полей в составном (composite) индексе базы данных?

Да, порядок полей в составном индексе критически важен для производительности запросов. Это связано с тем, как базы данных используют такие индексы.

Правило левого префикса (Left-Prefix Rule)

Составной индекс можно сравнить с отсортированным телефонным справочником, где записи упорядочены сначала по Фамилии, а затем по Имени.

• Вы легко найдёте всех людей с фамилией Иванов.
• Вы легко найдёте конкретного Иванова Ивана.
• Но вы не сможете эффективно найти всех людей с именем Иван, не перебирая весь справочник, потому что они разбросаны по разным фамилиям.

Точно так же работает и составной индекс. Он может быть эффективно использован только тогда, когда условия в WHERE соответствуют левому префиксу индекса, то есть первому полю, первым двум полям, первым трём и так далее.

Пример

Допустим, у нас есть таблица users и мы создаём составной индекс по полям city, status и created_at.

CREATE INDEX idx_users_city_status_created ON users (city, status, created_at);

✅ Этот индекс будет эффективно использоваться для запросов:

-- 1. Используется первое поле индекса (city)
SELECT * FROM users WHERE city = 'Москва';

-- 2. Используются первые два поля (city, status)
SELECT * FROM users WHERE city = 'Москва' AND status = 'active';

-- 3. Используются все три поля (city, status, created_at)
SELECT * FROM users WHERE city = 'Москва' AND status = 'active' AND created_at > '2023-01-01';

-- 4. Порядок в WHERE не важен, важен состав полей
SELECT * FROM users WHERE status = 'active' AND city = 'Москва';

❌ Этот индекс будет НЕэффективен или бесполезен для запросов:

-- Пропущено первое поле (city). Индекс не используется для поиска.
SELECT * FROM users WHERE status = 'active';

-- Пропущено второе поле (status). Индекс используется только по `city`.
SELECT * FROM users WHERE city = 'Москва' AND created_at > '2023-01-01';

Как выбирать порядок полей?

1. 
   

   Равенство > Диапазон: Сначала ставьте поля, которые будут использоваться в условиях на точное равенство (=, IN), а затем поля для диапазонных условий (>, <, BETWEEN). Как только планировщик встречает диапазон, использование последующих полей индекса становится неэффективным.

   
   
2. 
   

   Селективность: На первые позиции ставьте поля с высокой селективностью (большим количеством уникальных значений, например, user_id). Поля с низкой селективностью (например, is_deleted с двумя значениями true/false) лучше ставить в конец.

   
   
3. 
   

   Частота запросов: Анализируйте ваши самые частые и медленные запросы и стройте индекс под них.

   
   

Вывод: Правильный порядок полей в составном индексе позволяет базе данных максимально быстро сужать область поиска, что напрямую влияет на скорость выполнения запросов.