composite index) — это индекс сразу по нескольким столбцам таблицы (например, (user_id, created_at)). Он эффективен, когда запросы часто используют эти столбцы вместе в условиях WHERE/ORDER BY, и может быть лучше нескольких одиночных индексов.
Определение составного индекса
Составной индекс — это индекс, который создаётся по нескольким столбцам одновременно. Например:
CREATE INDEX idx_orders_user_created_at
ON orders(user_id, created_at);Такой индекс хранит отсортированные пары значений (user_id, created_at) и может ускорять запросы, которые фильтруют и/или сортируют по этим полям.
Как работает порядок колонок в индексе
Важно, в каком порядке указаны столбцы в индексе:
индекс по (user_id, created_at) хорошо подходит для запросов вида WHERE user_id = ... и дополнительно фильтров/сортировки по created_at;
такой индекс, как правило, не будет полноценно использоваться для запроса WHERE created_at = ... без фильтра по user_id, потому что первый столбец в индексе — user_id;
планировщик может использовать префикс индекса: индекс по (a, b, c) может обслуживать запросы по a или по (a, b), но не только по b.
Когда составной индекс полезнее нескольких одиночных
когда большинство важных запросов используют несколько колонок одновременно в WHERE и/или ORDER BY (например, WHERE user_id = ? AND created_at > ? ORDER BY created_at DESC);
когда одиночные индексы по отдельным колонкам приводят к тому, что планировщик либо делает intersection (дороже), либо вообще выбирает сканирование таблицы;
когда нужно «покрывающий» индекс, который содержит в себе все колонки, необходимые для запроса (тогда часть запросов можно обслужить только по индексу, не читая саму таблицу).
Чего избегать
создавать составные индексы «на всякий случай» без анализа запросов — это увеличивает размер БД и замедляет запись;
делать составные индексы с большим числом колонок, если реальные запросы используют только часть из них;
игнорировать порядок колонок: сначала должны идти наиболее селективные и часто используемые в точном равенстве поля =, затем — те, которые идут в диапазонах >/>=/</<= или сортировке.
Формулировка уровня middle
Составной индекс — это индекс по нескольким столбцам. Я проектирую их, исходя из реальных запросов: если важные запросы фильтруют и сортируют по (user_id, created_at), имеет смысл сделать индекс в таком порядке, чтобы база могла использовать его и для фильтрации, и для сортировки, и, по возможности, как покрывающий. При этом помню, что каждый лишний индекс удорожает INSERT/UPDATE/DELETE, поэтому сначала смотрю планы выполнения запросов (EXPLAIN) и уже по ним решаю, нужен ли составной индекс.
Оцени свой прогресс