Что такое составной (комбинированный) индекс и как правильно его проектировать?

Составной индекс — это индекс, созданный на несколько столбцов таблицы. Он оптимизирует запросы, которые используют эти столбцы в условиях WHERE, JOIN или ORDER BY.

Ключевой принцип: правило левого префикса (leftmost prefix). Индекс (col1, col2, col3) может быть эффективно использован для запросов с:

• col1
• col1, col2
• col1, col2, col3

Но НЕ будет эффективен для запросов только с col2 или col3, так как эти столбцы не являются левым префиксом индекса.

Пример создания в SQL:

-- Индекс для ускорения поиска по фамилии и фильтра по возрасту
CREATE INDEX idx_users_lastname_age ON users(last_name, age);

Эффективные запросы для этого индекса:

SELECT * FROM users WHERE last_name = 'Ivanov'; -- Использует префикс индекса
SELECT * FROM users WHERE last_name = 'Ivanov' AND age = 30; -- Использует оба столбца
SELECT * FROM users WHERE last_name = 'Ivanov' ORDER BY age; -- Использует для сортировки

Неэффективный запрос:

SELECT * FROM users WHERE age = 30; -- Полное сканирование, т.к. age не левый префикс

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

1. Порядок столбцов критичен. Первым ставьте столбец с наибольшей селективностью (уникальностью) или тот, который чаще всего используется в условиях равенства (=).
2. В JPA/Hibernate индекс можно задать аннотацией:

   @Entity
   @Table(indexes = @Index(name = "idx_lastname_age", columnList = "lastName, age"))
   public class User {
       private String lastName;
       private Integer age;
   }

3. Составные индексы также могут обслуживать запросы только на сортировку (ORDER BY) или группировку (GROUP BY), если порядок столбцов в этих операциях совпадает с порядком в индексе.