Для каких задач использовал составной индекс

Назначение и применение составных индексов в SQL

Составной индекс (composite или compound index) — это индекс, построенный на двух и более колонках таблицы. Я использую его для решения конкретных задач оптимизации запросов, когда условия фильтрации, сортировки или соединения затрагивают несколько полей одновременно. Вот ключевые сценарии, где составные индексы незаменимы:

1. Оптимизация запросов с условиями по нескольким полям

Если в WHERE указаны несколько условий через AND, составной индекс позволяет найти данные за один проход, избегая обработки отдельных индексов или полного сканирования таблицы.

-- Пример таблицы orders
CREATE INDEX idx_status_created ON orders (status, created_at);

-- Запрос использует обе колонки индекса
SELECT * FROM orders 
WHERE status = 'shipped' 
  AND created_at >= '2024-01-01';

Индекс idx_status_created эффективен здесь, так как сначала фильтрует по status, затем по created_at.

2. Поддержка сортировки (ORDER BY)

Составной индекс позволяет избежать дорогостоящих операций сортировки в памяти, если порядок колонок в индексе соответствует порядку в ORDER BY.

-- Индекс покрывает сортировку
CREATE INDEX idx_department_salary ON employees (department_id, salary DESC);

-- Запрос использует индекс для сортировки
SELECT * FROM employees 
WHERE department_id = 5 
ORDER BY salary DESC;

Важно: индекс не поможет при ORDER BY salary DESC, department_id, если колонки в индексе переставлены.

3. Покрывающие индексы (Covering Indexes)

Если индекс содержит все колонки, запрашиваемые в SELECT, база данных может выполнить запрос, обращаясь только к индексу, без чтения основной таблицы (heap). Это значительно ускоряет выполнение.

-- Индекс покрывает все нужные колонки
CREATE INDEX idx_user_date_amount ON transactions (user_id, transaction_date, amount);

-- Запрос извлекает данные только из индекса
SELECT user_id, transaction_date, amount 
FROM transactions 
WHERE user_id = 123 
  AND transaction_date BETWEEN '2024-01-01' AND '2024-01-31';

4. Ускорение JOIN операций

При соединении таблиц по нескольким колонкам составной индекс на этих колонках улучшает производительность.

-- Индекс для FK из нескольких колонок
CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

-- JOIN с условием по двум полям
SELECT * FROM customers c
JOIN orders o ON c.id = o.customer_id 
  AND o.order_date > '2024-01-01';

5. Обеспечение уникальности комбинаций полей

Составной уникальный индекс гарантирует уникальность сочетаний значений в нескольких колонках.

-- Уникальная пара user_id + product_id
CREATE UNIQUE INDEX idx_unique_user_product ON cart_items (user_id, product_id);

Это предотвращает дублирование записей, например, в корзине пользователя.

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

• Порядок колонок критически важен. Индекс работает эффективно, если запрос использует префикс индекса (левый принцип, leftmost prefix rule). Например, индекс (A, B, C) может использоваться для условий с A, A+B или A+B+C, но не для B или C по отдельности.
• Селективность: чаще всего первыми следует ставить колонки с высокой селективностью (большим количеством уникальных значений), чтобы индекс отфильтровывал больше строк на раннем этапе.
• Баланс между чтением и записью: каждый дополнительный индекс замедляет INSERT/UPDATE/DELETE, так как индексы требуют обновления.

Пример реального кейса из моей практики

В системе аналитики у нас была таблица событий (events) с миллиардами строк. Частые запросы фильтровали по tenant_id, event_type и диапазону дат. Составной индекс (tenant_id, event_type, created_at) позволил:

• Резко сократить время выполнения запросов (с 2-3 секунд до 50-100 мс).
• Убрать сортировку файлов на диске для ORDER BY created_at.
• Уменьшить нагрузку на диск за счёт покрывающих индексов для агрегирующих запросов.

Итог: составные индексы — мощный инструмент для оптимизации сложных запросов, но требуют глубокого анализа паттернов доступа к данным. Неправильный порядок колонок может сделать индекс бесполезным. Всегда анализируйте планы выполнения (EXPLAIN) и тестируйте под реальной нагрузкой.