Правила создания составных индексов, выбор порядка столбцов, 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, не зная фамилию.
Общее правило проектирования составного индекса:
-- Запрос
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 содержит одно значение, он эквивалентен =, и сортировка работает.
Когда создавать один составной индекс, а когда отдельные?
-- Составной индекс — когда запросы часто используют комбинацию
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, но это обычно медленнееСоставной индекс предпочтительнее, потому что:
Отдельные индексы имеют смысл, если каждый столбец часто используется в разных запросах без другого столбца.
Правило 1: Начинайте с запроса, а не с индекса. Спроектируйте индекс под конкретный запрос.
Правило 2: Самый селективный столбец равенства — первым. Если оба столбца — равенство, начните с более селективного.
Правило 3: Диапазон — после равенств. Только один диапазонный столбец в индексе эффективен для поиска.
Правило 4: Один индекс может обслуживать несколько запросов. Индекс (A, B, C) работает для (A), (A, B), (A, B, C) — не создавайте (A) отдельно, если уже есть (A, B, C).
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.