Middle: питання на співбесіді з теми «Індекси й оптимізація запитів»

EXPLAIN показує план і оцінки оптимізатора. Запит не виконується.

EXPLAIN ANALYZE (MySQL 8.0.18+) виконує запит і показує план разом з фактичними вимірами: скільки рядків пройшло через кожен вузол, скільки часу він зайняв, скільки разів виконувався. Вивід завжди у форматі TREE.

EXPLAIN ANALYZE
SELECT u.name, COUNT(*) FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2026-09-01'
GROUP BY u.id;

-> Table scan on <temporary>  (actual time=45.2..45.9 rows=812 loops=1)
    -> Aggregate using temporary table  (actual time=45.1..45.1 rows=812 loops=1)
        -> Nested loop inner join  (cost=2410 rows=5230) (actual time=0.09..38.7 rows=5104 loops=1)
            -> Index range scan on o using orders_created_at_idx ...
                   (cost=580 rows=5230) (actual time=0.05..9.1 rows=5104 loops=1)
            -> Single-row index lookup on u using PRIMARY (id=o.user_id)
                   (cost=0.25 rows=1) (actual time=0.005..0.005 rows=1 loops=5104)

Як читати:

• дерево читається зсередини назовні: найглибші вузли виконуються першими й передають рядки батьківським;
• cost, rows у перших дужках - оцінки оптимізатора;
• actual time=A..B - мілісекунди до першого рядка і до останнього, на одне виконання;
• rows у других дужках - фактична кількість рядків на одне виконання;
• loops - скільки разів вузол виконувався. Повний час вузла ≈ B × loops. У прикладі пошук користувача виконано 5104 рази.

На що дивитися:

• розбіжність оцінки й факту (rows=10 в оцінці, rows=200000 насправді) - оптимізатор помилився через статистику й міг обрати поганий план. Лікування - ANALYZE TABLE, гістограми, переписаний запит;
• вузол, де зростає час - різниця між часом вузла і його дочірніх показує, де він сам витрачає час;
• великі loops у вкладеному циклі - тисячі пошуків там, де міг би бути один прохід з'єднання.

EXPLAIN FORMAT=TREE (без ANALYZE) показує те саме дерево лише з оцінками - він інформативніший за табличний формат, бо видно порядок і тип з'єднань. Формат за замовчуванням можна змінити змінною explain_format.

Обережно: EXPLAIN ANALYZE справді виконує запит. Крім SELECT, він підтримує багатотабличні UPDATE і DELETE - і теж виконує їх по-справжньому, тож на продакшені аналізувати модифікації варто лише в транзакції з відкотом або на копії. Однотабличний UPDATE для аналізу зручно переписати на SELECT з тією самою умовою.

Докладніше в документації: Оператор EXPLAIN

Покривний індекс містить усі колонки, які потрібні запиту, - і в умові, і в SELECT, і в сортуванні. Тоді MySQL читає лише індекс і не йде в саму таблицю. У EXPLAIN це видно як Using index в колонці Extra.

Чому це так вигідно в InnoDB. Звичайний пошук за вторинним індексом - це два проходи по деревах:

1. у вторинному індексі знаходимо значення первинного ключа;
2. у кластерному індексі за первинним ключем знаходимо рядок.

Якщо запит повертає тисячу рядків, другий крок - це тисяча окремих пошуків у різних місцях таблиці. Покривний індекс прибирає його повністю.

Вторинний індекс неявно містить первинний ключ - саме ним він посилається на рядок:

CREATE INDEX orders_user_idx ON orders (user_id);   -- фактично (user_id, id)

SELECT id FROM orders WHERE user_id = 7;            -- Using index: id уже в індексі
SELECT id, total FROM orders WHERE user_id = 7;     -- потрібен рядок: total немає в індексі

Як зробити запит покривним:

CREATE INDEX orders_user_status_total_idx ON orders (user_id, status, total);

SELECT status, SUM(total) FROM orders WHERE user_id = 7 GROUP BY status;
-- усе в індексі: умова, групування, агрегат

Типові застосування:

• лічильники й агрегати за умовою (COUNT(*) WHERE ...);
• пагінація з відкладеним з'єднанням (deferred join): спершу знайти id через покривний індекс, потім підтягнути рядки:

SELECT o.* FROM orders o
JOIN (SELECT id FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20 OFFSET 10000) AS page
  ON page.id = o.id;

Глибоке зміщення проходиться по компактному індексу, а повні рядки читаються лише для 20 записів.

• перевірки існування (EXISTS) і whereIn за зовнішнім ключем.

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

Пастка Eloquent: ->get() за замовчуванням вибирає *, що майже ніколи не покривається індексом. Для гарячих запитів варто явно обмежувати колонки: ->select(['id', 'status']) чи ->pluck('id').

Відмінність від PostgreSQL: там вторинний індекс не містить первинного ключа, а index-only scan залежить ще й від карти видимості (visibility map).

Докладніше в документації: Формат виводу EXPLAIN: Using index

Index Condition Pushdown (ICP) - оптимізація, при якій частина умови WHERE перевіряється ще на рівні індексу, до того як читати повний рядок з таблиці.

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

З ICP сервер передає рушію і ті частини умови, які стосуються колонок індексу, але не можуть бути використані для пошуку. Рушій перевіряє їх на записі індексу й читає рядок лише якщо умова виконується.

Приклад:

CREATE INDEX people_idx ON people (zipcode, lastname, firstname);

SELECT * FROM people
WHERE zipcode = '01001'
  AND lastname LIKE '%енко%'
  AND address LIKE '%Хрещатик%';

• zipcode = '01001' - пошук в індексі;
• lastname LIKE '%енко%' - для пошуку непридатна (відсоток на початку), але lastname є в індексі. З ICP її перевіряють на записах індексу, і рядки з невідповідним прізвищем не читаються з таблиці;
• address в індексі немає - перевіряється вже після читання рядка.

Якщо під zipcode 10 000 записів, а прізвище підходить у 200, ICP заощаджує 9 800 переходів у кластерний індекс.

В EXPLAIN це видно як Using index condition в Extra.

Коли ICP застосовується:

• для типів доступу range, ref, eq_ref, ref_or_null;
• для InnoDB - лише для вторинних індексів (для кластерного індексу рядок уже прочитано разом із записом);
• умова не може містити підзапитів і збережених функцій.

Практичне значення:

• складений індекс корисний навіть тоді, коли не всі його колонки використовуються для пошуку: «хвостові» колонки працюють як фільтр;
• типовий випадок - діапазон у середині індексу: (user_id, created_at, status) при WHERE user_id = ? AND created_at > ? AND status = ? - status після діапазону не бере участі в пошуку, але завдяки ICP фільтрується в індексі;
• проте ICP - не заміна правильного порядку колонок: пошук за всіма колонками все одно ефективніший за фільтрацію.

ICP увімкнено за замовчуванням (optimizer_switch: index_condition_pushdown=on). Вимикати його доводиться хіба що для діагностики.

Докладніше в документації: Index Condition Pushdown

MySQL отримує відсортований результат двома способами:

1. читає індекс по порядку - рядки вже йдуть відсортованими, а з LIMIT можна зупинитися після N рядків;
2. filesort - читає всі відповідні рядки й сортує їх окремо. Попри назву, це не обов'язково диск: сортування йде в пам'яті (sort_buffer_size), і лише якщо дані не поміщаються - у тимчасових файлах.

Типовий запит стрічки:

SELECT * FROM posts
WHERE author_id = 5
ORDER BY published_at DESC
LIMIT 20;

• з індексом (author_id): знаходимо всі 50 000 постів автора, сортуємо їх усі (Using filesort), віддаємо 20;
• з індексом (author_id, published_at): йдемо по індексу в потрібному порядку, читаємо 20 записів і зупиняємося. Різниця може бути в тисячі разів.

Коли індекс може дати порядок:

• колонки ORDER BY - це продовження колонок з рівністю в WHERE: WHERE a = ? ORDER BY b, c з індексом (a, b, c);
• або ORDER BY за лівим префіксом індексу без WHERE на інших колонках;
• напрямки сортування узгоджені: або всі однакові (індекс читається вперед чи назад), або збігаються з напрямками в індексі (DESC-індекси).

Коли filesort неминучий:

WHERE a > 10 ORDER BY b            -- діапазон по a, сортування по b
WHERE a IN (1, 2) ORDER BY b       -- кілька значень a - кілька відсортованих шматків
ORDER BY a, b DESC                 -- різні напрямки при індексі (a, b) з однаковими
ORDER BY LOWER(name)               -- вираз
ORDER BY t1.a, t2.b                -- колонки з різних таблиць

Як зробити filesort дешевшим, якщо його не уникнути:

• вибирати менше колонок: MySQL сортує кортежі з потрібними колонками, і SELECT * з TEXT-полями роздуває буфер;
• LIMIT з filesort використовує пріоритетну чергу - зберігає лише N найкращих рядків, а не сортує все;
• для великих сортувань - збільшити sort_buffer_size для сесії, а не глобально (він виділяється на кожне з'єднання).

Діагностика: Using filesort в EXPLAIN і лічильники Sort_merge_passes (злиття тимчасових файлів - сортування не вмістилося в пам'ять) та Sort_scan / Sort_range у SHOW GLOBAL STATUS.

Пагінація через OFFSET навіть з індексом читає й відкидає всі пропущені рядки. Для глибоких сторінок краще курсорна пагінація (WHERE published_at < ? ORDER BY published_at DESC LIMIT 20, у Laravel - cursorPaginate()).

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

Підказки індексів дають оптимізатору вказівки, які індекси розглядати:

SELECT * FROM orders USE INDEX (orders_user_idx) WHERE user_id = 7 AND status = 'paid';
SELECT * FROM orders FORCE INDEX (orders_created_idx) WHERE created_at > '2026-09-01';
SELECT * FROM orders IGNORE INDEX (orders_status_idx) WHERE status = 'paid';

• USE INDEX - розглядати лише вказані індекси (але повне сканування лишається можливим);
• FORCE INDEX - як USE INDEX, але повне сканування вважається дуже дорогим: індекс буде використано, якщо це взагалі можливо;
• IGNORE INDEX - не розглядати вказані індекси.

Можна уточнити призначення: FOR JOIN, FOR ORDER BY, FOR GROUP BY.

У Laravel для цього є методи будівника:

Order::query()->forceIndex('orders_created_idx')->where('created_at', '>', $date)->get();
Order::query()->useIndex('orders_user_idx')->...;
Order::query()->ignoreIndex('orders_status_idx')->...;

Чому це крайній засіб:

• підказка заморожує рішення, ухвалене на сьогоднішніх даних. Через рік розподіл зміниться, а запит і далі змушений використовувати індекс, який уже невигідний;
• перейменування чи видалення індексу ламає запит: на неіснуючий індекс у підказці MySQL поверне помилку;
• підказка маскує справжню причину: застарілу статистику, неселективний індекс, невдало записану умову;
• запит прив'язується до MySQL - на PostgreSQL чи SQLite (у тестах) такий синтаксис не працює. Laravel-методи на інших драйверах ігноруються або генерують свої варіанти, тож поведінка розходиться.

Що спробувати перед підказкою:

1. ANALYZE TABLE - оновити статистику;
2. гістограма на колонці з нерівномірним розподілом;
3. кращий складений індекс, під який запит природно підходить;
4. переписати умову (прибрати функцію з колонки, розбити OR на UNION);
5. прибрати зайві схожі індекси, між якими оптимізатор «вагається».

Майбутнє синтаксису. Документація MySQL 8.4 попереджає, що USE INDEX, FORCE INDEX і IGNORE INDEX планують оголосити застарілими на користь оптимізаторних підказок у коментарях: /*+ INDEX(orders orders_created_idx) */ і /*+ NO_INDEX(...) */, плюс точніші JOIN_INDEX, ORDER_INDEX, GROUP_INDEX. Новий код краще писати одразу з ними.

Коли підказка виправдана: відомий патологічний запит, де оптимізатор стабільно помиляється, і всі інші варіанти перевірено. Тоді варто лишити коментар, чому вона тут, - і переглядати після оновлень MySQL.

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

Видаляти індекси страшно: якщо він таки був потрібен якомусь рідкісному, але важливому запиту, після DROP INDEX цей запит перейде на повне сканування. А відновлення індексу на великій таблиці займає хвилини чи години.

Невидимий індекс (MySQL 8.0+) продовжує існувати й оновлюватися при кожному записі, але оптимізатор його не бачить:

ALTER TABLE orders ALTER INDEX orders_status_idx INVISIBLE;
-- спостерігаємо день-тиждень: чи не з'явилися повільні запити
ALTER TABLE orders ALTER INDEX orders_status_idx VISIBLE;  -- миттєвий відкат
-- або, якщо все спокійно
ALTER TABLE orders DROP INDEX orders_status_idx;

Зміна видимості - операція з метаданими, вона виконується миттєво. Повернення індексу теж миттєве, бо його дані весь час підтримувалися в актуальному стані.

Як знайти кандидатів на видалення:

-- індекси, які не використовувалися з моменту запуску сервера
SELECT * FROM sys.schema_unused_indexes;

-- індекси, що дублюють інші (лівий префікс іншого індексу)
SELECT * FROM sys.schema_redundant_indexes;

Статистика використання береться з Performance Schema й скидається при перезапуску сервера. Якщо сервер перезапускали вчора, «невикористаний» індекс міг бути потрібен щомісячному звіту. Тому період спостереження має охоплювати всі регулярні задачі.

Перевірити запит з невидимими індексами в поточній сесії:

SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN SELECT ...;   -- чи обрав би оптимізатор цей індекс

Так само можна підготувати новий індекс: створити його невидимим, перевірити плани в сесії й лише потім відкрити для всіх.

Обмеження:

• первинний ключ не може бути невидимим, як і унікальний індекс, що неявно виконує роль первинного ключа;
• невидимий індекс продовжує сповільнювати запис і займати місце. Це інструмент перевірки, а не постійний стан;
• унікальний невидимий індекс і далі перевіряє унікальність;
• підказка FORCE INDEX на невидимий індекс поверне помилку - корисний спосіб помітити, що десь у коді на нього посилаються.

Чому взагалі видаляти індекси: кожен індекс додає вартість кожному INSERT, UPDATE і DELETE, займає буферний пул і місце на диску. Зайві індекси - тихий податок на запис.

Докладніше в документації: Невидимі індекси

Доступ 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 і чому обрано план.

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

Зазвичай 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 відповідь майже завжди «так».

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

LIKE '%слово%' не використовує індекс і читає всю таблицю. FULLTEXT-індекс розбиває текст на слова й будує інвертований індекс «слово → рядки».

ALTER TABLE posts ADD FULLTEXT INDEX ft_posts (title, body);

SELECT id, title, MATCH(title, body) AGAINST ('черги laravel') AS score
FROM posts
WHERE MATCH(title, body) AGAINST ('черги laravel')
ORDER BY score DESC;

Колонки в MATCH мають точно збігатися з колонками індексу.

Режими пошуку:

WHERE MATCH(title, body) AGAINST ('+laravel -symfony черг*' IN BOOLEAN MODE)

Чому щось «не знаходиться»:

• мінімальна довжина слова: для InnoDB innodb_ft_min_token_size = 3 - слова з двох літер («ІТ», «UI») не індексуються. Зміна потребує перезапуску сервера й перебудови індексів;
• стоп-слова: InnoDB має короткий вбудований англійський список; власний задають через innodb_ft_server_stopword_table;
• поріг 50% (слово в половині рядків вважається шумом) стосується лише MyISAM у природному режимі - через нього на маленьких тестових таблицях MyISAM пошук «нічого не знаходить».

Кирилиця й морфологія. Стандартний парсер розбиває текст за пробілами й розділовими знаками, тож кирилиця індексується, але без морфології: «черга», «черги», «чергу» - різні слова. Допомагає пошук за префіксом у булевому режимі (черг*), але відмінки з чергуванням літер він не покриває.

Парсер ngram (WITH PARSER ngram) розбиває текст на послідовності з N символів (ngram_token_size, за замовчуванням 2). Створений для китайської, японської й корейської, де немає пробілів; для української дає частковий збіг, але індекс великий і результати менш релевантні.

У Laravel:

$table->fullText(['title', 'body']);                              // міграція
Post::whereFullText(['title', 'body'], 'черги laravel')->get();
Post::whereFullText(['title', 'body'], '+laravel черг*', ['mode' => 'boolean'])->get();

Коли FULLTEXT достатньо: простий пошук по сайту, адмінка, невеликі обсяги. Коли потрібні морфологія, стійкість до опечаток, фасети й підсвічування - окремий рушій (Meilisearch, Typesense, Elasticsearch) через Laravel Scout.

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