Для чего нужны составные индексы?

Для чего нужны составные индексы в MySQL

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

Основные цели использования составных индексов

1. Ускорение запросов с несколькими условиями в WHERE

    Если запрос содержит фильтрацию по нескольким колонкам, составный индекс позволяет найти данные за один проход по индексу, вместо последовательного применения нескольких отдельных индексов или полного сканирования таблицы.

```sql
-- Пример таблицы
CREATE TABLE orders (
    user_id INT,
    status VARCHAR(20),
    created_at DATE,
    amount DECIMAL(10,2)
);

-- Составный индекс
CREATE INDEX idx_user_status ON orders(user_id, status);

-- Оптимизированный запрос
SELECT * FROM orders WHERE user_id = 100 AND status = 'completed';
```

    Индекс `idx_user_status` позволит быстро найти все завершенные заказы конкретного пользователя.

1. Эффективная поддержка операций ORDER BY и GROUP BY

    Когда сортировка или группировка выполняется по нескольким колонкам, составный индекс, созданный в той же последовательности, может полностью удовлетворить потребность в данных без дополнительной сортировки в памяти (используется **filesort**).

```sql
CREATE INDEX idx_user_date ON orders(user_id, created_at);

-- Запрос будет использовать индекс для сортировки
SELECT user_id, SUM(amount) FROM orders 
WHERE user_id = 100 
GROUP BY user_id, created_at 
ORDER BY created_at DESC;
```

3. Реализация покрывающих индексов (Covering Indexes)

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

```sql
CREATE INDEX idx_covering ON orders(user_id, status, created_at, amount);

-- Покрывающий индекс: все нужные колонки уже в индексе
SELECT user_id, status, created_at FROM orders 
WHERE user_id = 100 AND status = 'pending';
```

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

• Принцип левого префикса (Leftmost Prefix Principle): MySQL может использовать составный индекс, только если запрос использует колонки с самого начала индекса, в том же порядке. Например, для индекса (A, B, C) он будет работать для:

    *   `WHERE A = ...`
    *   `WHERE A = ... AND B = ...`
    *   `WHERE A = ... AND B = ... AND C = ...`
    Но **НЕ будет работать** для:
    *   `WHERE B = ...` (пропущена колонка `A`)
    *   `WHERE A = ... AND C = ...` (пропущена колонка `B` между ними)

• Оптимальный порядок колонок:

    *   Первой должна стоять колонка с **высокой селективностью** (много уникальных значений), которая часто используется в условиях.
    *   Затем — колонки, используемые в условиях равенства (`=`).
    *   Колонки для диапазонных условий (`>`, `<`, `BETWEEN`) или сортировки (`ORDER BY`) лучше ставить в конце индекса.

Пример сравнения производительности

Без составного индекса запрос WHERE user_id = 5 AND status = 'active' может:

1. Использовать индекс на user_id, найти все заказы пользователя, затем фильтровать их по status в памяти.
2. Использовать индекс на status, найти все активные заказы, затем фильтровать по user_id (обычно менее эффективно).
3. Выполнить полное сканирование таблицы (Full Table Scan) если нет подходящих индексов.

С составным индексом (user_id, status):

• MySQL сразу найдет точную комбинацию значений в структуре индекса (обычно B-дереве).
• Доступ к данным происходит по прямому указателю из индекса.
• Время выполнения сокращается с O(n) до O(log n).

Ограничения и рекомендации

• Не создавайте избыточные индексы. Индекс (A, B) часто покрывает потребности для запросов с колонкой A, поэтому отдельный индекс на A может быть не нужен.
• Составные индексы увеличивают объем данных и могут замедлить операции INSERT, UPDATE, DELETE, так как индекс необходимо поддерживать.
• Порядок колонок в индексе должен соответствовать порядку в наиболее частых и критичных запросах.

Таким образом, составные индексы — мощный инструмент оптимизации сложных запросов. Их правильное проектирование, основанное на анализе частых паттернов доступа к данным в приложении, является одной из ключевых задач при настройке производительности базы данных.