Як MySQL оптимізує діапазони й довгі списки IN і що таке index dive?

Доступ range читає один чи кілька відрізків індексу: BETWEEN, >, <, LIKE 'abc%', IN (...), OR за однією колонкою. Список IN (1, 5, 9) - це три «відрізки» з одного значення.

Як оптимізатор оцінює кількість рядків. Для кожного відрізка він може зробити index dive - спуститися в B-дерево до початку й кінця відрізка й точно оцінити, скільки записів між ними. Точно, але для довгого списку дорого: тисяча значень у IN - тисяча спусків ще до виконання запиту.

eq_range_index_dive_limit (за замовчуванням 200): якщо в рівностях більше значень, MySQL замість спусків використовує середню статистику індексу (скільки рядків припадає на одне значення). Це швидко, але неточно для нерівномірних даних - звідси раптова зміна плану, коли список у whereIn переростає 200 елементів.

Ліміт пам'яті оптимізатора діапазонів range_optimizer_max_mem_size (8 МБ за замовчуванням). Дуже довгі IN чи складні OR можуть його перевищити - тоді MySQL відмовляється від діапазонного доступу, видає попередження й може обрати повне сканування.

Складені індекси й діапазони:

-- індекс (status, created_at)
WHERE status IN ('new', 'paid') AND created_at > '2026-09-01'
-- два відрізки: ('new', > дата) і ('paid', > дата), обидві колонки працюють

WHERE status > 'a' AND created_at > '2026-09-01'
-- діапазон по першій колонці - created_at для пошуку вже не використовується

IN у першій колонці поводиться як кілька рівностей, тому наступні колонки індексу лишаються корисними. Діапазон - ні.

Кортежі в IN теж оптимізуються діапазоном:

SELECT * FROM prices WHERE (product_id, currency) IN ((1, 'UAH'), (2, 'USD'));

Практичні поради для Laravel:

• whereIn з десятками тисяч id - запит стає величезним, розбір і оптимізація дорогі, легко впертися в max_allowed_packet. Краще обробляти частинами (chunkById, lazyById) або вставити id у тимчасову таблицю й з'єднати;
• Eloquent при with() генерує whereIn за всіма ключами батьківських моделей: жадібне завантаження для 50 000 моделей - це IN з 50 000 значень. Ще одна причина обробляти великі вибірки частинами;
• whereIntegerInRaw() уникає зв'язування тисяч параметрів для цілих чисел.

Діагностика: в EXPLAIN - type = range і rows; у виводі оптимізатора (optimizer trace) видно, чи робилися index dives і чому обрано план.

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