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

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

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

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

1. Для запросов с условиями по нескольким столбцам (WHERE и AND) Когда ваши SELECT, UPDATE или DELETE запросы часто используют фильтрацию по нескольким колонкам одновременно через AND, составной индекс может радикально ускорить поиск.

-- Пример таблицы `orders`
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL
);

-- Частый запрос: найти заказы определенного клиента в определенном статусе
SELECT * FROM orders WHERE customer_id = 100 AND status = 'shipped';

-- Оптимальный составной индекс:
CREATE INDEX idx_customer_status ON orders(customer_id, status);

Индекс будет использован для быстрого поиска по обеим колонкам, что гораздо эффективнее двух отдельных индексов или полного сканирования таблицы.

2. Для поддержки запросов с сортировкой (ORDER BY) Если данные часто извлекаются в определенном порядке, составной индекс, колонки которого соответствуют порядку сортировки, позволяет избежать дорогостоящей операции filesort.

-- Частый запрос: получить заказы, отсортированные по дате для конкретного клиента
SELECT * FROM orders WHERE customer_id = 100 ORDER BY created_at DESC;

-- Эффективный индекс:
CREATE INDEX idx_customer_created ON orders(customer_id, created_at DESC);

Такой индекс позволяет сразу читать данные в нужном порядке после поиска по customer_id.

3. Для покрывающих индексов (Covering Index) Это один из самых эффективных приемов. Если индекс содержит ВСЕ колонки, требуемые запросом (и в SELECT, и в WHERE), то движку БД вообще не нужно обращаться к самим данным таблицы (к heap). Все необходимое он находит прямо в индексе.

-- Запрос использует только колонки из индекса
SELECT customer_id, status, created_at FROM orders WHERE customer_id = 100 AND status = 'pending';

-- Покрывающий индекс:
CREATE INDEX idx_customer_status_created ON orders(customer_id, status, created_at);

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

4. Для запросов с условием LIKE на префикс, когда есть другая колонка в равенстве Составные индексы могут помочь и с LIKE, но только если поиск по префиксу (LIKE 'abc%') идет НЕ по первой колонке индекса.

-- Пример таблицы `users`
CREATE TABLE users (
    id INT PRIMARY KEY,
    last_name VARCHAR(100) NOT NULL,
    first_name VARCHAR(100) NOT NULL
);

-- Запрос: найти всех Ивановых с именем на "Ал"
SELECT * FROM users WHERE last_name = 'Иванов' AND first_name LIKE 'Ал%';

-- Эффективный индекс:
CREATE INDEX idx_last_first ON users(last_name, first_name);

Индекс сможет использовать равенство по last_name и затем префиксный поиск по first_name.

Критические правила и предостережения

• Порядок колонок ВАЖЕН. Индекс (col_a, col_b) НЕ то же самое, что (col_b, col_a). Индекс работает слева направо. Он может быть использован для поиска по (col_a), по (col_a, col_b), но НЕ для поиска только по col_b (если это не LIKE-префикс по первой колонке).
• Кардинальность. Располагайте колонки с высокой кардинальностью (большим количеством уникальных значений) левее в индексе, если это не противоречит логике запросов. Это позволит эффективнее отфильтровывать данные на раннем этапе.
• Цена поддержки. Каждый новый индекс замедляет операции INSERT, UPDATE, DELETE, так как индексные структуры也需要 обновляться. Кроме того, индексы занимают дополнительное место на диске.
• Избирательность запросов. Создавайте индексы под конкретные, самые частые и самые медленные запросы вашего приложения. Анализируйте EXPLAIN для этих запросов.

Вывод

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

1. Фильтруют данные по нескольким колонкам одновременно.
2. Требуют сортировки по нескольким полям.
3. Могут быть полностью обслужены самим индексом (покрывающие индексы).

Решение должно приниматься на основе анализа реальных рабочих нагрузок (slow query log) и понимания принципов работы B-деревьев, лежащих в основе большинства индексов. Слепое добавление составных индексов «на всякий случай» так же вредно, как и их полное отсутствие.