Как создается составной индекс в базе данных?

Создание составного индекса в базе данных

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

Основные принципы создания

В SQL составной индекс создаётся с помощью команды CREATE INDEX. Общий синтаксис выглядит следующим образом:

CREATE INDEX index_name 
ON table_name (column1, column2, ..., columnN);

Ключевые особенности:

• Порядок столбцов критически важен — индекс эффективен для запросов, которые используют префикс указанных колонок (например, для индекса (col1, col2, col3) будут работать запросы по col1, по col1 и col2, по col1, col2 и col3, но НЕ по col2 или col3 отдельно).
• Максимальное количество колонок зависит от СУБД (обычно 16-32).
• Индекс может включать колонки разных типов данных.

Практический пример в MySQL/PostgreSQL

Рассмотрим таблицу orders:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    total_amount DECIMAL(10,2)
);

Для запросов, которые часто фильтруют по user_id и сортируют по created_at, создадим составной индекс:

-- Индекс для фильтрации и сортировки
CREATE INDEX idx_user_created 
ON orders (user_id, created_at DESC);

-- Индекс для фильтрации по двум условиям
CREATE INDEX idx_user_status 
ON orders (user_id, status);

Когда использовать составные индексы?

Составные индексы наиболее эффективны в следующих сценариях:

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

   -- Использует индекс idx_user_status
   SELECT * FROM orders 
   WHERE user_id = 100 AND status = 'completed';
   
   -- Использует префикс idx_user_created (только user_id)
   SELECT * FROM orders 
   WHERE user_id = 100 AND created_at > '2024-01-01';
   

2. Поддержка операций ORDER BY и GROUP BY

   -- Эффективная сортировка благодаря индексу
   SELECT user_id, SUM(total_amount) 
   FROM orders 
   WHERE user_id BETWEEN 100 AND 200
   GROUP BY user_id 
   ORDER BY user_id;
   

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

    Если индекс содержит ВСЕ колонки, необходимые для запроса, СУБД может выполнить запрос, обращаясь только к индексу, без чтения самой таблицы (значительно быстрее):

```sql
-- Создадим покрывающий индекс
CREATE INDEX idx_covering 
ON orders (user_id, status, created_at, total_amount);

-- Этот запрос может быть выполнен используя только индекс
SELECT user_id, status, created_at 
FROM orders 
WHERE user_id = 100 AND status = 'pending';
```

4. Оптимизация JOIN операций

    Индексы на колонках соединения ускоряют выполнение запросов с объединением таблиц.

Важные ограничения и рекомендации

• Селективность колонок — более селективные колонки (с большим количеством уникальных значений) следует размещать первыми в индексе.
• Торговля между производительностью и стоимостью — каждый добавленный индекс замедляет операции INSERT, UPDATE, DELETE, так как СУБД необходимо обновлять все соответствующие индексы.
• Лимит длины ключа — в некоторых СУБД существует ограничение на максимальную длину ключа индекса (например, 3072 байта в InnoDB MySQL).
• Индексы на NULL значения — поведение зависит от СУБД; некоторые системы не включают полностью NULL строки в индекс.

Пример в Laravel (миграция)

// В миграции Laravel
public function up()
{
    Schema::table('orders', function (Blueprint $table) {
        // Составной индекс для частых запросов
        $table->index(['user_id', 'status'], 'orders_user_status_index');
        
        // Уникальный составной индекс
        $table->unique(['user_id', 'created_at'], 'orders_user_created_unique');
    });
}

Заключение

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

• Фактические запросы в приложении (через лог медленных запросов или EXPLAIN)
• Кардинальность колонок
• Соотношение операций чтения/записи

Правильно спроектированный составной индекс может ускорить запросы в сотни раз, но избыточное индексирование приводит к снижению общей производительности системы. Всегда используйте EXPLAIN (или EXPLAIN ANALYZE) для проверки эффективности индексов перед внедрением в продакшен.