Что такое составной индекс в базах данных и в каких случаях его следует использовать?

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

Ключевые сценарии использования:

1. Фильтрация по нескольким полям: Наиболее частый случай. Если запросы часто содержат условия по нескольким колонкам в WHERE, составной индекс позволяет базе данных быстро найти нужные строки.

   -- Запрос для поиска активных пользователей из определенного города
   SELECT user_id, name FROM users 
   WHERE city = 'Moscow' AND status = 'active';
   
   -- Оптимальный индекс для этого запроса
   CREATE INDEX idx_users_city_status ON users(city, status);

2. Оптимизация сортировки (ORDER BY): Если запрос требует сортировки по нескольким полям, индекс, созданный по этим же полям в том же порядке, может избавить БД от дорогостоящей операции сортировки.

3. Создание покрывающих индексов (Covering Index): Это ситуация, когда все поля, запрашиваемые в SELECT, содержатся в самом индексе. В этом случае базе данных не нужно обращаться к таблице для получения данных, что дает максимальный прирост производительности.

   -- Для запроса ниже, индекс idx_users_city_status_name будет покрывающим
   SELECT name FROM users WHERE city = 'Moscow' AND status = 'active';
   CREATE INDEX idx_users_city_status_name ON users(city, status, name);

4. Обеспечение уникальности комбинации полей: Создание уникального составного индекса гарантирует, что комбинация значений в этих колонках не будет повторяться в таблице.

Важный аспект — порядок колонок в индексе. Он имеет решающее значение. В начало следует ставить колонки, которые чаще всего используются в WHERE с оператором равенства (=) и имеют высокую селективность (большое количество уникальных значений).