Использование нескольких индексов в одном запросе, алгоритмы объединения
Index Merge — это стратегия оптимизатора, при которой MySQL использует несколько индексов одновременно для одного запроса и объединяет их результаты. Это альтернатива составному индексу, когда его нет.
Представьте таблицу с двумя отдельными индексами:
CREATE TABLE tasks (
id BIGINT PRIMARY KEY,
status VARCHAR(20),
priority VARCHAR(20),
assignee_id INT,
INDEX idx_status (status),
INDEX idx_priority (priority)
);Запрос с двумя условиями по разным столбцам:
EXPLAIN SELECT * FROM tasks
WHERE status = 'open' AND priority = 'high';Без составного индекса (status, priority) оптимизатор может использовать Index Merge:
type: index_merge
key: idx_status,idx_priority
Extra: Using intersect(idx_status,idx_priority)
Используется для запросов с AND:
-- Оба условия должны выполняться
SELECT * FROM tasks WHERE status = 'open' AND priority = 'high';Алгоритм:
idx_status, получает набор rowid для 'open'idx_priority, получает набор rowid для 'high'Это эффективнее full table scan, если оба условия селективны. Но составной индекс (status, priority) почти всегда быстрее — один поиск вместо двух + пересечение.
Используется для запросов с OR:
-- Любое из условий
SELECT * FROM tasks WHERE status = 'open' OR priority = 'high';Алгоритм:
idx_status, получает rowid для 'open'idx_priority, получает rowid для 'high'Union полезен, когда каждое условие по отдельности селективно, но ни одно не покрывает весь запрос.
Используется для OR с диапазонными условиями:
-- Диапазонные условия с OR
SELECT * FROM tasks
WHERE created_at > '2024-01-01' OR updated_at > '2024-06-01';Sort-Union отличается от обычного Union тем, что сортирует rowid перед объединением. Это необходимо, потому что диапазонные условия не позволяют напрямую объединить rowid — их нужно сначала упорядочить. Сортировка добавляет owerhead, но обеспечивает корректность.
Index Merge — это 'план Б'. Вот почему составной индекс почти всегда лучше:
-- Index Merge: два отдельных индекса
-- 2 сканирования индексов + пересечение rowid + lookup
CREATE INDEX idx_status ON tasks(status);
CREATE INDEX idx_priority ON tasks(priority);
-- Составной индекс: один поиск
-- 1 сканирование + lookup
CREATE INDEX idx_comp ON tasks(status, priority);Сравнение:
| Метрика | Index Merge | Составной индекс |
|---|---|---|
| Чтений индексов | 2 | 1 |
| Операций пересечения | Да | Нет |
| Rowid для lookup | Больше (менее точный) | Меньше (точный) |
| Covering index | Нет (обычно) | Возможен |
Index Merge имеет смысл, когда:
-- Index Merge может быть выбран вместо составного индекса,
-- если есть МНОГО разных комбинаций запросов
-- и создание всех составных индексов невозможноИногда Index Merge дороже альтернатив. Его можно отключить:
-- Для конкретного запроса — игнорировать индексы
SELECT * FROM tasks
IGNORE INDEX (idx_status, idx_priority)
WHERE status = 'open' AND priority = 'high';
-- Глобально (не рекомендуется)
SET SESSION optimizer_switch = 'index_merge=off';Зачем отключать: если один из индексов значительно лучше другого, лучше использовать только его + фильтрация. Index Merge может добавить owerhead пересечения без существенного сокращения строк.
В EXPLAIN Index Merge легко обнаружить:
EXPLAIN SELECT * FROM tasks WHERE status = 'open' AND priority = 'high';type: index_merge
key: idx_status,idx_priority
key_len: 62,62
ref: NULL
rows: 150
Extra: Using intersect(idx_status,idx_priority)
Если вы видите index_merge — подумайте, не будет ли составной индекс эффективнее. Если составной индекс невозможен — убедитесь, что rows для Index Merge разумно мал.
Далее: Оптимизация JOIN запросов