Составные индексы и порядок столбцов

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

#Leftmost Prefix Rule

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

-- Составной индекс из трёх столбцов
CREATE INDEX idx_comp ON users(country, city, age);

Этот индекс работает для следующих запросов:

-- ✅ Использует (country) — левый префикс длины 1
SELECT * FROM users WHERE country = 'Russia';

-- ✅ Использует (country, city) — левый префикс длины 2
SELECT * FROM users WHERE country = 'Russia' AND city = 'Moscow';

-- ✅ Использует (country, city, age) — все три столбца
SELECT * FROM users WHERE country = 'Russia' AND city = 'Moscow' AND age = 25;

-- ❌ НЕ использует индекс — пропущен первый столбец
SELECT * FROM users WHERE city = 'Moscow';

-- ❌ НЕ использует индекс — пропущены первые два столбца
SELECT * FROM users WHERE age = 25;

Индекс (A, B, C) — это как телефонная книга: отсортированная по фамилии, затем по имени, затем по возрасту. Вы не можете быстро найти всех людей возраста 25, не зная фамилию.

#Порядок столбцов: равенства перед диапазонами

Общее правило проектирования составного индекса:

Столбцы с точным равенством (=, IN) — первыми
Столбцы с диапазонным условием (>, <, BETWEEN, LIKE 'prefix%') — затем
Столбцы для ORDER BY — последними

-- Запрос
SELECT * FROM orders
WHERE customer_id = 42          -- равенство
  AND status = 'pending'        -- равенство
  AND created_at > '2024-01-01' -- диапазон
ORDER BY created_at DESC;       -- сортировка

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

Почему такой порядок? customer_id и status фильтруют через точное равенство — они сужают набор. created_at используется и для диапазонного условия, и для сортировки — он идёт последним. После первого диапазонного столбца MySQL не может использовать оставшиеся столбцы индекса для поиска (но может для сортировки).

#Почему порядок имеет значение

Рассмотрим два индекса для одного запроса:

-- Запрос: WHERE status = 'active' AND user_id = 5
CREATE INDEX idx_a ON tasks(status, user_id);   -- Индекс A
CREATE INDEX idx_b ON tasks(user_id, status);   -- Индекс B

Оба индекса технически работают, но эффективность разная. Если user_id более селективен (уникальнее), чем status, то idx_b лучше: он быстрее сужает поиск до 1-2 строк по user_id, а затем проверяет status. Индекс idx_a сначала отфильтрует все 'active' (тысячи строк), затем найдёт user_id = 5.

Оптимизатор MySQL может переставлять столбцы равенства внутри индекса — для него (status, user_id) и (user_id, status) при условиях = равнозначны. Но как только появляется диапазон, порядок фиксируется.

#Диапазонные условия и сортировка

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

-- Индекс: (status, created_at, priority)

-- ✅ created_at — диапазон, но это последний используемый столбец
SELECT * FROM tasks
WHERE status = 'open' AND created_at > '2024-01-01';
-- Использует: status (ref) + created_at (range)

-- ⚠️ created_at — диапазон, priority НЕ используется для поиска
SELECT * FROM tasks
WHERE status = 'open' AND created_at > '2024-01-01' AND priority = 'high';
-- Использует: status (ref) + created_at (range); priority фильтруется после

Особый случай: IN-список. IN с несколькими значениями ведёт себя как диапазон — после него MySQL не может использовать следующие столбцы для поиска:

-- IN с несколькими значениями = диапазон
SELECT * FROM tasks
WHERE status IN ('open', 'pending') ORDER BY created_at;
-- created_at НЕ используется для сортировки, будет filesort

Если IN содержит одно значение, он эквивалентен =, и сортировка работает.

#Составной индекс vs отдельные индексы

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

-- Составной индекс — когда запросы часто используют комбинацию
CREATE INDEX idx_comp ON orders(customer_id, status);
-- Эффективен для WHERE customer_id = ? AND status = ?

-- Отдельные индексы — когда каждый столбец часто используется сам по себе
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_customer ON orders(customer_id);
-- MySQL может использовать Index Merge, но это обычно медленнее

Составной индекс предпочтительнее, потому что:

Один поиск по составному индексу быстрее, чем Index Merge двух отдельных
Составной индекс может стать покрывающим (covering index)
Занимает меньше места, чем два отдельных индекса

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

#Практические правила

Правило 1: Начинайте с запроса, а не с индекса. Спроектируйте индекс под конкретный запрос.

Правило 2: Самый селективный столбец равенства — первым. Если оба столбца — равенство, начните с более селективного.

Правило 3: Диапазон — после равенств. Только один диапазонный столбец в индексе эффективен для поиска.

Правило 4: Один индекс может обслуживать несколько запросов. Индекс (A, B, C) работает для (A), (A, B), (A, B, C) — не создавайте (A) отдельно, если уже есть (A, B, C).