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

Ответ

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

Когда его стоит использовать:

  • Фильтрация по нескольким полям: Когда в WHERE часто используются условия на несколько столбцов одновременно (AND).
    • SELECT * FROM users WHERE last_name = 'Иванов' AND first_name = 'Иван';
  • Сортировка по нескольким полям: Когда в ORDER BY указано несколько столбцов.
    • SELECT * FROM messages ORDER BY chat_id, created_at DESC;
  • Покрывающие запросы (Covering Index): Когда все столбцы, запрашиваемые в SELECT, уже содержатся в самом индексе. В этом случае СУБД не нужно обращаться к таблице, что значительно ускоряет запрос.

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

-- Индекс для быстрого поиска пользователей по фамилии и имени
CREATE INDEX idx_users_lastname_firstname ON users(last_name, first_name);

Ключевые особенности и нюансы:


  1. Правило левого префикса: Порядок столбцов в индексе критически важен. Индекс (col1, col2, col3) может быть эффективно использован для запросов, фильтрующих по (col1), (col1, col2) и (col1, col2, col3). Однако он будет бесполезен для запроса, фильтрующего только по col2 или col3.



  2. Избыточность: Если у вас уже есть индекс (A, B), то создавать отдельный индекс на (A) не нужно — он уже покрывается первым.



  3. Запросы с диапазонами: Индекс перестаёт эффективно использоваться после первого условия с диапазоном (>, <, BETWEEN, LIKE '...'). В запросе WHERE last_name = 'Иванов' AND age > 30 индекс по (last_name, age) будет использован для поиска по last_name, а затем для фильтрации по age среди найденных записей.