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

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

Пример создания в SQL:
Предположим, у нас часто ищут пользователей по фамилии и городу.

CREATE INDEX idx_lastname_city ON users (last_name, city);

Пример использования в Go с GORM:

// Определение индекса на уровне модели
type User struct {
    gorm.Model
    LastName string `gorm:"index:idx_lastname_city"`
    City     string `gorm:"index:idx_lastname_city"`
    Age      int
}

// или программное создание
db.Model(&User{}).AddIndex("idx_lastname_city", "last_name", "city")

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

• Порядок колонок имеет значение. Индекс (last_name, city) будет эффективно использоваться для запросов с WHERE last_name = '...' AND city = '...' или только WHERE last_name = '...'. Однако он будет бесполезен для запроса, который ищет только по city.
• Правило левого префикса. Индекс может использоваться, если в запросе участвуют колонки, составляющие левую часть индекса.
• Покрытие запроса (Covering Index). Если все запрашиваемые в SELECT поля есть в индексе, база данных может вообще не обращаться к таблице, что дает максимальное ускорение.
• Влияние на запись. Составные индексы ускоряют чтение (SELECT), но замедляют операции записи (INSERT, UPDATE, DELETE), так как при каждом изменении данных СУБД нужно обновлять и сам индекс.