Що таке злиття індексів (index merge) і чому воно часто сигналізує про відсутній складений індекс?

Зазвичай MySQL використовує один індекс на таблицю в запиті. Index merge - виняток: кілька діапазонних сканувань різних індексів однієї таблиці, результати яких об'єднуються.

Три варіанти (видно в Extra при type = index_merge):

• Using union(a_idx, b_idx) - для OR:

SELECT * FROM users WHERE email = 'a@b.ua' OR phone = '380501234567';
-- окремо шукає за індексом email, окремо за phone, об'єднує id

• Using intersect(a_idx, b_idx) - для AND за колонками з різних індексів: перетин множин первинних ключів;
• Using sort_union(...) - як union, але для діапазонів: id треба спершу відсортувати.

Для OR між різними колонками злиття - добрий результат: без нього був би повний перебір таблиці. Альтернатива, яку інколи варто написати явно, - UNION двох запитів, кожен з яких використовує свій індекс:

SELECT * FROM users WHERE email = ?
UNION
SELECT * FROM users WHERE phone = ?;

А ось intersect - майже завжди сигнал проблеми:

-- окремі індекси (user_id) і (status)
SELECT * FROM orders WHERE user_id = 7 AND status = 'paid';
-- Using intersect(orders_user_idx, orders_status_idx)

MySQL читає всі замовлення користувача, всі оплачені замовлення (а їх можуть бути мільйони) і перетинає. Один складений індекс (user_id, status) знайшов би потрібні рядки одним проходом. Окремі індекси на кожну колонку «про всяк випадок» - типова помилка, що й призводить до злиття.

Обмеження index merge:

• не працює з повнотекстовими індексами;
• складні вкладені AND/OR можуть не розпізнатися - оптимізатор не завжди переписує умову в зручну форму;
• оцінка вартості буває хибною, і злиття обирається там, де одного індексу вистачило б.

Керування:

-- вимкнути для запиту
SELECT /*+ NO_INDEX_MERGE(orders) */ * FROM orders WHERE ...;

-- глобально окремі алгоритми через optimizer_switch:
-- index_merge, index_merge_union, index_merge_intersection, index_merge_sort_union

Практичний висновок: побачивши index_merge у плані частого запиту, перше питання - чи не потрібен тут складений індекс. Для intersect відповідь майже завжди «так».

Докладніше в документації: Оптимізація злиттям індексів