Премиум

Составные индексы в базах данных: когда и как их следует использовать? Опишите ключевые принципы.

Тип вопроса: Базы данных Вероятность: 10% Категория: Golang Разработчик

Ответ

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

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

  1. Порядок колонок имеет решающее значение (Принцип левого префикса)

    • Индекс, созданный для колонок (col_A, col_B, col_C), может быть эффективно использован для запросов, фильтрующих по:
      • col_A
      • col_A и col_B
      • col_A, col_B и col_C
    • Однако он будет бесполезен или малоэффективен для запросов, фильтрующих только по col_B или col_C.

  2. Селективность колонок

    • Селективность — это количество уникальных значений в колонке по отношению к общему числу строк. Чем выше селективность, тем эффективнее индекс.
    • При создании составного индекса первыми следует ставить наиболее селективные колонки. Например, в таблице пользователей user_id (уникален для каждой строки) гораздо селективнее, чем status (может иметь всего несколько значений: 'active', 'inactive', 'banned').
    • Правильно: CREATE INDEX ON users (user_id, status);
    • Неправильно: CREATE INDEX ON users (status, user_id);

  3. Покрытие запроса индексом (Index-Only Scan)

    • Это самая эффективная ситуация. Если все колонки, запрашиваемые в SELECT, WHERE и ORDER BY, присутствуют в индексе, базе данных не нужно обращаться к самой таблице. Она берет все данные прямо из индекса.
    • Пример: Для запроса SELECT department, created_at FROM users WHERE company_id = ?; идеальным будет индекс (company_id, department, created_at).

  4. Влияние на сортировку (ORDER BY)

    • Составной индекс может избавить БД от необходимости выполнять операцию сортировки, если порядок колонок в ORDER BY совпадает с порядком колонок в индексе.
    • Индекс (company_id, created_at DESC) ускорит ... WHERE company_id = ? ORDER BY created_at DESC;.

  5. Учитывайте компромиссы

    • Индексы — это не бесплатно. Они занимают место на диске и, что важнее, замедляют операции записи (INSERT, UPDATE, DELETE), так как при каждом изменении данных БД должна обновить и индекс.
    • Не создавайте индексы бездумно. Каждый индекс должен быть оправдан частыми и медленными SELECT-запросами.

Пример для PostgreSQL:

-- Таблица заказов
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    status VARCHAR(20),
    created_at TIMESTAMPTZ
);

-- Мы часто ищем активные заказы конкретного клиента, отсортированные по дате
-- SELECT order_id, created_at FROM orders 
-- WHERE customer_id = 123 AND status = 'active' 
-- ORDER BY created_at DESC;

-- Создаем оптимальный составной индекс:
-- 1. customer_id - наиболее селективная колонка для фильтрации
-- 2. status - вторая колонка для фильтрации
-- 3. created_at - для покрытия сортировки и, возможно, выборки
CREATE INDEX idx_orders_customer_status_created ON orders(customer_id, status, created_at DESC);

Ответ 18+ 🔞

А, составные индексы, блядь! Ну это же классика, ёпта! Сейчас расскажу, как не наступить на грабли, а то потом будешь сидеть и думать, почему запрос тормозит, как черепаха в патоках.

Представь, что индекс — это алфавитный указатель в книге. А составной — это когда ищешь не просто по фамилии, а по фамилии и имени вместе. Логично же, да? Но тут, сука, есть свои подводные камни, о которые все разбиваются.

Первый и главный принцип, который надо вбить себе в башку, — порядок колонок. Это, блядь, всё решает!

Создал ты индекс на три колонки: (фамилия, имя, отчество). Ну, думаешь, красава, теперь всё летает. Ан нет, мудак! Этот индекс сработает на ура, только если ты ищешь:

  • По фамилии.
  • По фамилии и имени.
  • По фамилии, имени и отчеству.

А если ты пришёл и спрашиваешь: «А дай-ка мне всех Ивановых»? А индекс-то начинается с фамилии! Он нихуя не знает, где у него в этом своём отсортированном списке одни имена лежат. Он тупо посмотрит на тебя и пойдёт читать всю таблицу целиком, потому что для такого запроса он — просто кусок мёртвого веса. Вот это и есть принцип левого префикса, ёпта. Запомни, как «Отче наш».

Второй момент — селективность. Это, блядь, важно, как соль в супе. Ставь первыми в индексе те колонки, которые отсекают больше всего мусора. Например, у тебя таблица пользователей. Колонка пол имеет два значения («М», «Ж») — селективность хуёвая. А user_id — уникальный. Так какой индекс создашь?

  • (пол, user_id) — пиздец, не делай так. Это как искать иголку в стоге сена, начав с сортировки стогов по цвету.
  • (user_id, пол) — вот это уже дело. Сначала находишь уникального юзера по айди (мгновенно), а потом смотришь его пол, если надо.

Третий кайф — покрывающий индекс (Index-Only Scan). Это когда ты такой хитрожопый и засунул в индекс ВСЕ колонки, которые нужны для запроса. База данных ахуевает от счастья: ей не надо лазить в саму таблицу, все данные уже лежат аккуратненько в индексе. Быстро, чисто, эффективно. Мечта, а не работа.

Четвёртое — сортировка. Хочешь, чтобы ORDER BY не тормозил? Распиши колонки в индексе в том же порядке, в каком сортируешь. Хочешь ORDER BY created_at DESC? Так и пиши в индексе ... created_at DESC. База скажет тебе спасибо.

И главное, чувак, не нахуйяривай индексов, как горячих пирожков! Каждый индекс — это доп. работа при записи. Каждый INSERT, UPDATE или DELETE будет бегать и обновлять все эти твои индексы. Создал лишний — сам себе злобный буратино, который пилит производительность на ровном месте.

Пример, чтобы было совсем понятно:

-- Допустим, есть таблица заказов. Весь офис постоянно долбит запрос:
-- «Найди все активные заказы клиента 123, отсортированные от новых к старым»
-- SELECT * FROM orders WHERE customer_id = 123 AND status = 'active' ORDER BY created_at DESC;

-- Индекс-уродец (status, customer_id):
-- Начинается с status, у которого значений 3 штуки ('active', 'done', 'cancelled').
-- Он сначала найдёт ВСЕ активные заказы (миллион, например), а потом в этой куче будет искать customer_id = 123.
-- Пиздец и медленно.

-- Индекс-красавец (customer_id, status, created_at DESC):
-- 1. customer_id — сразу находим все заказы клиента 123 (штук 100).
-- 2. status — в этих 100 отфильтровываем только 'active' (остаётся 50).
-- 3. created_at DESC — они УЖЕ отсортированы в индексе так, как нам надо! Просто читаем готовенькое.
-- Волшебство, скорость, все довольны.

CREATE INDEX idx_orders_optimal ON orders(customer_id, status, created_at DESC);

Вот и вся магия, блядь. Думай головой, когда создаёшь, а не тыкай пальцем в небо. И проверяй EXPLAIN ANALYZE, этот твой лучший друг и суровый судья в одном флаконе.

© ХакСобесов, 2026
support@hacksobesov.com