Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

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

Питання з реальних співбесід з відповідями: Laravel і PHP, бази даних, JavaScript і фронтенд, Git, Docker, API, безпека й архітектура. Тими самими темами, що й тести.

21 питань

EXPLAIN показує план виконання запиту, не виконуючи його. Кожен рядок виводу - одна таблиця в запиті, у порядку, в якому MySQL їх читає.

EXPLAIN SELECT * FROM orders WHERE user_id = 7 AND status = 'paid';

Найважливіші колонки:

  • type - спосіб доступу. Від найкращого до найгіршого:
    • const / system - щонайбільше один рядок за первинним чи унікальним ключем;
    • eq_ref - у з'єднанні один рядок цієї таблиці на кожен рядок попередньої (за унікальним ключем);
    • ref - пошук за неунікальним індексом з рівністю;
    • range - діапазон індексу (BETWEEN, >, IN, LIKE 'abc%');
    • index - повне сканування індексу (дешевше за таблицю, але все одно все);
    • ALL - повне сканування таблиці. На великій таблиці - головний сигнал проблеми.
  • possible_keys - індекси, які теоретично підходять; key - обраний. NULL у key - індекс не використано.
  • key_len - скільки байтів індексу використано. Для складеного індексу показує, скільки колонок з нього реально задіяно.
  • rows - оцінка кількості рядків, які доведеться прочитати (за статистикою, не точна).
  • filtered - який відсоток прочитаних рядків пройде решту умов. rows × filtered / 100 - скільки рядків піде далі.
  • Extra - додаткові подробиці:
    • Using index - запит обслуговано лише з індексу (покривний індекс), чудово;
    • Using where - умову перевірено після читання рядка;
    • Using index condition - частину умови перевірено ще в індексі (Index Condition Pushdown);
    • Using filesort - потрібне окреме сортування;
    • Using temporary - потрібна тимчасова таблиця (часто GROUP BY чи DISTINCT).

Що шукати першим:

  1. type = ALL на великій таблиці;
  2. великі rows при маленькому результаті - індекс неселективний чи відсутній;
  3. Using filesort / Using temporary на запитах, що мають бути швидкими.

Обмеження: EXPLAIN показує план і оцінки, а не фактичний час. Щоб побачити, скільки рядків прочитано насправді й де витрачено час, потрібен EXPLAIN ANALYZE.

У Laravel план зручно отримати прямо з будівника: User::where('email', $email)->explain()->dd();.

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

Складений індекс (a, b, c) - це B-дерево, впорядковане спершу за a, всередині однакових a - за b, всередині однакових (a, b) - за c. Як телефонний довідник: прізвище, потім ім'я.

Правило лівого префікса: індекс можна використати для пошуку лише за початковими колонками:

CREATE INDEX orders_idx ON orders (user_id, status, created_at);

-- індекс працює
WHERE user_id = 7
WHERE user_id = 7 AND status = 'paid'
WHERE user_id = 7 AND status = 'paid' AND created_at > '2026-01-01'
WHERE status = 'paid' AND user_id = 7     -- порядок в умові не важливий

-- індекс для пошуку не працює
WHERE status = 'paid'                     -- пропущено user_id
WHERE created_at > '2026-01-01'

Знайти всіх Іванів у довіднику, впорядкованому за прізвищем, можна лише переглянувши його повністю.

Діапазон «обриває» індекс. Колонки після першої з діапазоном (>, <, BETWEEN, LIKE 'abc%') для пошуку вже не використовуються:

WHERE user_id = 7 AND created_at > '2026-01-01' AND status = 'paid'
-- індекс (user_id, created_at, status): пошук за user_id + діапазон created_at,
-- status перевіряється для кожного запису діапазону
-- індекс (user_id, status, created_at): пошук за всіма трьома - краще

Звідси практичне правило порядку колонок: спершу колонки з рівністю, потім колонка з діапазоном чи сортуванням.

Що ще дає порядок колонок:

  • ORDER BY за колонками, що йдуть одразу після колонок з рівністю, не потребує сортування: WHERE user_id = 7 ORDER BY created_at з індексом (user_id, created_at);
  • окремий індекс (user_id) не потрібен, якщо є (user_id, status) - лівий префікс його заміняє. Це зайвий індекс, що лише сповільнює запис.

Skip scan (MySQL 8.0.13+) частково обходить правило: якщо в першої колонки мало різних значень, а запит покривається індексом, MySQL може «перестрибувати» по значеннях першої колонки. У EXPLAIN видно Using index for skip scan. Розраховувати на це при проєктуванні не варто.

У Laravel: $table->index(['user_id', 'status', 'created_at']); - порядок у масиві і є порядком колонок в індексі.

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

Індекс допомагає лише тоді, коли умова записана так, що MySQL може шукати безпосередньо за значенням колонки. Найчастіші причини, чому це не так:

1. Функція чи вираз над колонкою.

-- індекс на created_at не використовується
WHERE YEAR(created_at) = 2026
WHERE DATE(created_at) = '2026-10-04'
WHERE price * 1.2 > 100

-- переписати на діапазон по «голій» колонці
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
WHERE price > 100 / 1.2

Якщо без функції ніяк, у MySQL 8 є функціональні індекси: CREATE INDEX ... ((LOWER(email))).

2. Неявне приведення типів. Рядкова колонка порівнюється з числом:

-- phone VARCHAR(20), індекс є
WHERE phone = 380501234567     -- кожен рядок перетворюється на число, індекс не працює
WHERE phone = '380501234567'   -- індекс працює

Навпаки (числова колонка, рядкова константа) - не страшно: перетворюється константа. Те саме при з'єднаннях: колонки різних типів чи різних кодувань у JOIN можуть позбавити індексу.

3. LIKE з відсотком на початку.

WHERE name LIKE 'Олек%'    -- діапазон індексу, працює
WHERE name LIKE '%сандр%'  -- немає початку, з якого шукати

Для пошуку всередині тексту - FULLTEXT-індекс чи зовнішній пошуковий рушій.

4. Порушене правило лівого префікса складеного індексу: умова не містить першої колонки.

5. OR між різними колонками. WHERE email = ? OR phone = ? може взагалі не використати індекс або використати злиття індексів. Інколи краще UNION двох запитів.

6. Оптимізатор вирішив, що повне сканування дешевше. Якщо умова підходить під значну частину таблиці (наприклад, status = 'active' для 80% рядків), читати таблицю послідовно швидше, ніж робити тисячі переходів з індексу в кластерний індекс. Це не помилка, а правильне рішення.

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

Як перевіряти: завжди через EXPLAIN на реалістичному обсязі даних. На таблиці зі ста рядків MySQL обиратиме повне сканування, і висновки будуть хибними.

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

В InnoDB первинний ключ - не просто унікальний ідентифікатор, а порядок фізичного зберігання рядків (кластерний індекс) і частина кожного вторинного індексу. Тому вибір ключа впливає на продуктивність усієї таблиці.

Вимоги до хорошого первинного ключа:

  • короткий - він копіюється в кожен вторинний індекс;
  • незмінний - зміна ключа означає переміщення рядка в дереві й оновлення всіх індексів;
  • монотонно зростаючий - нові рядки дописуються в кінець дерева, сторінки заповнюються щільно;
  • завжди заданий (NOT NULL).

Варіант за замовчуванням - автоінкремент:

$table->id();   // BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY

8 байтів, зростає, нічого не коштує. BIGINT замість INT - щоб не впертися в ~4,29 млрд (пропуски в нумерації з'їдають запас швидше, ніж здається).

UUID - коли справді потрібен (генерація id на клієнті, злиття даних з різних баз, неперебірні публічні ідентифікатори):

  • UUIDv4 (випадковий) - найгірший варіант для InnoDB. Кожна вставка йде у випадкову сторінку: розщеплення сторінок, фрагментація, сторінки заповнені наполовину, буферний пул використовується неефективно;
  • UUIDv7 / ULID (впорядковані за часом) - вставляються майже послідовно, проблеми v4 зникають. Laravel HasUuids генерує саме впорядковані UUID;
  • зберігати компактно - BINARY(16), а не CHAR(36).

Компроміс: внутрішній BIGINT-ключ для зв'язків плюс окрема унікальна колонка uuid для зовнішнього світу (URL, API).

Природні ключі (email, номер телефону, код ІПН) - погана ідея: вони змінюються, бувають довгими, а зміна каскадом зачіпає всі зовнішні ключі. Унікальний індекс на них - так; первинний ключ - ні.

Складений первинний ключ доречний у таблицях зв'язку «багато-до-багатьох»:

Schema::create('role_user', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained();
    $table->foreignId('role_id')->constrained();
    $table->primary(['user_id', 'role_id']);
});

Рядки одного користувача лежать поруч, а пошук «ролі користувача» читає сусідні записи.

Таблиця без первинного ключа отримає прихований внутрішній ключ, недоступний у запитах. До того ж Group Replication (InnoDB Cluster) та інструменти онлайн-змін схеми вимагають явного первинного ключа, а керовані хмарні MySQL часто вмикають sql_require_primary_key.

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

Префіксний індекс індексує не всю рядкову колонку, а лише перші N символів:

CREATE INDEX urls_path_idx ON urls (path(50));

Навіщо:

  • ліміт довжини ключа - для формату рядка DYNAMIC це 3072 байти. VARCHAR(1000) в utf8mb4 важить до 4000 байтів і повністю в індекс не поміститься;
  • TEXT і BLOB без префікса індексувати взагалі не можна;
  • розмір індексу - менший індекс краще вміщується в буферний пул.

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

SELECT COUNT(DISTINCT path) / COUNT(*)            AS full_selectivity,
       COUNT(DISTINCT LEFT(path, 20)) / COUNT(*)  AS p20,
       COUNT(DISTINCT LEFT(path, 50)) / COUNT(*)  AS p50,
       COUNT(DISTINCT LEFT(path, 100)) / COUNT(*) AS p100
FROM urls;

Беруть найменшу довжину, селективність якої близька до повної. Для URL з однаковим початком (https://example.com/blog/...) префікс має бути довшим, ніж здається.

Обмеження префіксних індексів:

  • не можуть бути покривними - повне значення все одно читається з таблиці;
  • не допомагають ORDER BY і GROUP BY за колонкою;
  • унікальний префіксний індекс перевіряє унікальність лише префікса: два різні URL з однаковими першими 50 символами не вставляться.

Альтернатива для пошуку за точним збігом довгого рядка - індекс на хеші:

ALTER TABLE urls
    ADD COLUMN path_hash BINARY(16) AS (UNHEX(MD5(path))) STORED,
    ADD UNIQUE INDEX (path_hash);

SELECT * FROM urls WHERE path_hash = UNHEX(MD5(?)) AND path = ?;

Індекс компактний (16 байтів), працює й для унікальності повних значень.

Історична довідка про Laravel. Schema::defaultStringLength(191) з'явився через старий ліміт 767 байтів (формат COMPACT): 191 × 4 = 764. На сучасних MySQL цей ліміт - 3072 байти, і VARCHAR(255) з індексом працює без хитрощів.

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

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 мають точно збігатися з колонками індексу.

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

Режим Що робить
IN NATURAL LANGUAGE MODE (за замовчуванням) ранжує рядки за релевантністю
IN BOOLEAN MODE оператори: +обов'язкове -виключене "точна фраза" префікс*
WITH QUERY EXPANSION другий прохід зі словами з найкращих результатів, часто шумний
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: повнотекстовий пошук

До MySQL 8.0 синтаксис INDEX (a DESC) приймався, але ігнорувався - індекс завжди будувався за зростанням. З 8.0 спадні індекси справжні: значення фізично впорядковані за спаданням.

Коли це потрібно - сортування в різних напрямках:

SELECT * FROM products
WHERE category_id = 3
ORDER BY rating DESC, price ASC
LIMIT 20;
  • індекс (category_id, rating, price): читаючи вперед, отримуємо rating ASC, price ASC; назад - rating DESC, price DESC. Потрібної комбінації DESC, ASC немає - буде Using filesort;
  • індекс (category_id, rating DESC, price ASC) віддає рядки рівно в потрібному порядку, запит читає 20 записів і зупиняється.

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

-- індекс (user_id, created_at)
WHERE user_id = 7 ORDER BY created_at DESC LIMIT 20
-- EXPLAIN: Extra = Backward index scan; Using index condition...

Зворотне сканування працює, але в InnoDB трохи повільніше за пряме: сторінки зв'язані у двонапрямлений список, проте всередині сторінки записи оптимізовані для руху вперед. На гарячих запитах «останні N записів» спадний індекс (user_id, created_at DESC) дає невеликий, але вимірний виграш - і прибирає Backward index scan з плану.

Інші ситуації:

  • MIN()/MAX() за колонкою індексу оптимізуються в обох напрямках;
  • GROUP BY зі спадними частинами індексу підтримується;
  • спадні індекси підтримує лише InnoDB, і не для FULLTEXT, SPATIAL чи хеш-індексів.

У Laravel-міграції напрям колонки в індексі задається через сирий вираз:

$table->rawIndex('category_id, rating DESC, price', 'products_category_rating_price_idx');

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

PostgreSQL має спадні індекси давно, плюс NULLS FIRST/LAST. У MySQL NULL завжди вважається найменшим значенням, і окремого керування його позицією в індексі немає.

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