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

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

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

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 завжди вважається найменшим значенням, і окремого керування його позицією в індексі немає.

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