Питання на співбесіді з MySQL

sql_mode визначає, наскільки суворо MySQL поводиться з некоректними даними. Ключовий прапорець - STRICT_TRANS_TABLES (строгий режим), увімкнений за замовчуванням з MySQL 5.7.

Без строгого режиму MySQL «виправляє» дані мовчки:

-- name VARCHAR(10), age TINYINT UNSIGNED, created_at DATE
INSERT INTO users (name, age, created_at) VALUES ('Олександра Іваненко', 300, '2026-02-30');
-- вставлено з попередженнями:
-- name = 'Олександра', age = 255, created_at = '0000-00-00'

• рядок обрізано до довжини колонки;
• число поза діапазоном замінено на межу;
• неіснуюча дата - на нульову;
• значення не того типу - на 0 чи порожній рядок.

Застосунок отримує «успіх», а в базі - зіпсовані дані, які виявляють через місяці.

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

Інші важливі прапорці режиму за замовчуванням у MySQL 8:

• ONLY_FULL_GROUP_BY - заборона неоднозначних GROUP BY;
• NO_ZERO_DATE, NO_ZERO_IN_DATE - заборона «нульових» дат 0000-00-00;
• ERROR_FOR_DIVISION_BY_ZERO - ділення на нуль при записі - помилка, а не NULL;
• NO_ENGINE_SUBSTITUTION - помилка замість тихої заміни рушія таблиці.

Де режим вимикають (і не варто):

• 'strict' => false у config/database.php Laravel - встановлює м'який режим для з'єднання застосунку. Інколи так «лікують» помилки старого коду при оновленні MySQL - і повертають тихе псування даних.
• Старі дампи з 0000-00-00 не імпортуються в строгому режимі - їх треба виправити, а не вимикати режим глобально.

Як перевірити:

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;
SHOW WARNINGS;   -- після операції, якщо режим м'який

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

Докладніше в документації: Режими SQL сервера

Автоматичні мітки часу - для TIMESTAMP і DATETIME:

CREATE TABLE posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

• DEFAULT CURRENT_TIMESTAMP - значення при вставці, якщо колонку не передали.
• ON UPDATE CURRENT_TIMESTAMP - автоматичне оновлення при будь-якій зміні рядка.
• Дробові секунди - з точністю: DATETIME(6) ... DEFAULT CURRENT_TIMESTAMP(6).

Нюанси ON UPDATE:

• Спрацьовує, лише якщо значення якоїсь колонки справді змінилося. UPDATE posts SET title = title мітку не оновить.
• Спрацьовує й на технічні зміни (лічильник переглядів, службовий прапорець) - «оновлено» перестає означати «змінено автором». Тоді краще керувати міткою в застосунку.

Значення за замовчуванням виразом (MySQL 8.0.13+) - вираз у дужках:

CREATE TABLE api_tokens (
    id BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY,
    expires_at DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL 30 DAY),
    settings JSON NOT NULL DEFAULT (JSON_OBJECT()),
    notes TEXT DEFAULT ('')
);

До 8.0.13 дозволялися лише літерали й CURRENT_TIMESTAMP; TEXT, BLOB і JSON не могли мати значення за замовчуванням узагалі.

Обмеження виразів: без підзапитів, змінних, збережених функцій і посилань на AUTO_INCREMENT-колонку.

Laravel і мітки часу: Eloquent сам заповнює created_at / updated_at у застосунку (з часовим поясом застосунку) і не покладається на ON UPDATE. Але масові оновлення через Query Builder (DB::table()->update()) Eloquent не бачить - там updated_at не зміниться, якщо в базі немає ON UPDATE. $table->timestamp('updated_at')->useCurrentOnUpdate() додає його в міграції.

Часові пояси: CURRENT_TIMESTAMP обчислюється в поясі сесії MySQL. Якщо він не збігається з поясом застосунку, мітки, поставлені базою й застосунком, розійдуться на кілька годин.

Докладніше в документації: Ініціалізація TIMESTAMP і DATETIME

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

Тому читачі не чекають на записувачів, а записувачі - на читачів.

Коли фіксується знімок:

• REPEATABLE READ (за замовчуванням): під час першого читання в транзакції, а не на START TRANSACTION. Усі наступні звичайні SELECT транзакції бачать той самий знімок.
• READ COMMITTED: кожен оператор бере новий знімок.

START TRANSACTION;          -- знімка ще немає
-- тут інша сесія комітить зміну
SELECT * FROM t;            -- знімок фіксується зараз: зміну видно

START TRANSACTION WITH CONSISTENT SNAPSHOT;  -- знімок фіксується одразу

WITH CONSISTENT SNAPSHOT використовує, наприклад, mysqldump --single-transaction: дамп усіх таблиць узгоджений на одну мить.

Знімок не діє на зміни даних. Це найчастіша пастка:

START TRANSACTION;
SELECT COUNT(*) FROM jobs WHERE status = 'new';     -- 0 (знімок)
-- інша сесія вставляє і комітить 5 рядків 'new'
UPDATE jobs SET status = 'taken' WHERE status = 'new';  -- оновить 5 рядків!
SELECT COUNT(*) FROM jobs WHERE status = 'taken';   -- 5: свої зміни видно
COMMIT;

UPDATE і DELETE працюють з останніми закоміченими версіями. Після цього транзакція бачить рядки, які вона змінила, - навіть якщо «за знімком» їх не існувало.

Блокувальні читання (SELECT ... FOR UPDATE / FOR SHARE) теж читають останню версію, а не знімок, і ставлять блокування.

Практичні наслідки:

• шаблон «прочитати, перевірити в PHP, записати» без FOR UPDATE дає гонитву: рішення приймається на основі знімка, а запис - поверх свіжих даних;
• довга транзакція тримає старий знімок, і InnoDB не може очистити старі версії рядків (див. purge), тож база росте й сповільнюється;
• DDL на кшталт ALTER TABLE інколи не узгоджується зі знімком: якщо таблицю перестворено після фіксації знімка, транзакція отримає помилку «Table definition has changed».

Докладніше в документації: Узгоджене неблокувальне читання

Блокувальні читання читають останню закомічену версію рядка (а не знімок) і ставлять на нього блокування до кінця транзакції. Поза транзакцією (в автокоміті) вони майже марні: блокування знімається одразу.

FOR SHARE (раніше LOCK IN SHARE MODE) - спільне блокування. Інші теж можуть читати з FOR SHARE, але змінити рядок чи взяти FOR UPDATE не можуть до коміту.

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

START TRANSACTION;
SELECT id FROM projects WHERE id = 5 FOR SHARE;
INSERT INTO tasks (project_id, title) VALUES (5, 'Нове завдання');
COMMIT;

FOR UPDATE - виключне блокування: так, ніби рядок уже оновлюють. Інші FOR UPDATE, FOR SHARE і зміни чекають. Звичайні SELECT без блокувань читають далі зі знімка.

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- перевірка в застосунку
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

У Laravel: ->sharedLock() і ->lockForUpdate() всередині DB::transaction().

NOWAIT - не чекати: якщо рядок заблоковано, одразу помилка (ER_LOCK_NOWAIT). Зручно для інтерфейсу «запис редагує інший користувач».

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

START TRANSACTION;
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- позначити взяті й закомітити

Саме так драйвер черг database у Laravel вибирає завдання на MySQL 8+.

Нюанси InnoDB:

• блокується не «рядок за умовою», а записи індексу, які пройшов пошук. Без індексу на колонці з умови FOR UPDATE заблокує всі прочитані рядки таблиці;
• на REPEATABLE READ додаються блокування проміжків (gap locks), тож може блокуватися й вставка в діапазон;
• SKIP LOCKED повертає неузгоджену картину даних - для черг це нормально, для звітів ні;
• FOR UPDATE OF orders у запиті з JOIN блокує лише рядки вказаної таблиці.

Докладніше в документації: Блокувальні читання

InnoDB блокує не лише існуючі рядки, а й проміжки між записами індексу. Так на рівні REPEATABLE READ він захищається від фантомів - рядків, що «з'являються» між двома блокувальними читаннями однієї транзакції.

Види блокувань:

• record lock - блокування одного запису індексу;
• gap lock - блокування проміжку між записами (без самих записів). Забороняє лише вставку в цей проміжок;
• next-key lock - запис плюс проміжок перед ним. Саме так InnoDB блокує при пошуку й скануванні індексу на REPEATABLE READ.

Приклад, що дивує:

-- у таблиці є рядки з age = 20 і age = 30, індекс на age
-- сесія A
START TRANSACTION;
SELECT * FROM users WHERE age BETWEEN 21 AND 29 FOR UPDATE;  -- 0 рядків

-- сесія B
INSERT INTO users (age) VALUES (25);    -- чекає!
INSERT INTO users (age) VALUES (35);    -- може теж чекати: next-key lock
                                        -- на запис 30 разом із проміжком

Сесія A не знайшла жодного рядка, але заблокувала проміжок, щоб повторний запит дав той самий результат.

Коли блокування ширше, ніж очікуєш:

• немає індексу на колонці з умови - сканується вся таблиця, і блокуються всі записи з проміжками. UPDATE ... WHERE email = ? без індексу на email фактично блокує таблицю для вставок;
• неунікальний індекс - блокуються проміжки з обох боків знайдених записів;
• пошук за унікальним індексом з рівністю блокує лише сам запис, без проміжку.

Gap locks не конфліктують між собою - дві транзакції можуть тримати блокування одного проміжку. Звідси класичне взаємоблокування: обидві роблять SELECT ... FOR UPDATE за неіснуючим ключем, обидві отримують gap lock, обидві намагаються вставити - deadlock.

READ COMMITTED вимикає gap locks для пошуку й сканування (лишаються лише для перевірки зовнішніх ключів і дублікатів). Блокувань і взаємоблокувань менше, але фантоми можливі.

Як побачити блокування:

SELECT engine_transaction_id, index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks;

lock_mode вигляду X,GAP, X,REC_NOT_GAP чи просто X (next-key) показує, що саме тримає транзакція.

Докладніше в документації: Блокування next-key і фантомні рядки

Взаємоблокування (deadlock) - дві транзакції чекають одна на одну: A тримає рядок 1 і хоче рядок 2, B тримає рядок 2 і хоче рядок 1. InnoDB помічає цикл і відкочує одну з транзакцій (зазвичай ту, що змінила менше рядків). Вона отримує помилку 1213, друга продовжує роботу.

Перше правило: deadlock - не аварія. У системі з паралельними записами вони трапляються. Застосунок має бути готовим повторити транзакцію повністю:

DB::transaction(function () use ($from, $to, $amount) {
    // ...
}, attempts: 3);

Laravel повторює замикання при взаємоблокуванні. Важливо: повторюється вся транзакція, тож побічні ефекти (листи, HTTP-запити) - лише після коміту (DB::afterCommit() чи afterCommit у подіях і джобах).

Друге - з'ясувати причину:

SHOW ENGINE INNODB STATUS\G
-- розділ LATEST DETECTED DEADLOCK: обидві транзакції,
-- їхні запити, які блокування тримають і чого чекають

Там видно лише останній deadlock. Щоб писати всі в журнал помилок:

SET PERSIST innodb_print_all_deadlocks = ON;

Типові причини й ліки:

• Різний порядок захоплення рядків. Переказ з A на B і з B на A одночасно. Ліки - завжди блокувати в одному порядку (наприклад, за зростанням id):

$accounts = Account::whereIn('id', [$from, $to])->orderBy('id')->lockForUpdate()->get();

• Відсутній індекс. UPDATE ... WHERE без індексу блокує все проскановане - ймовірність перетину різко зростає.
• Блокування проміжків. Два SELECT ... FOR UPDATE за неіснуючим ключем, а потім два INSERT. Ліки - INSERT ... ON DUPLICATE KEY UPDATE замість «перевірити, потім вставити», або READ COMMITTED.
• Довгі транзакції. Чим довше тримаються блокування, тим більше перетинів. Важкі обчислення й зовнішні виклики - поза транзакцією.
• Масові оновлення. Великий UPDATE по частинах з однаковим порядком.

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

Докладніше в документації: Взаємоблокування в InnoDB

Буферний пул (buffer pool) - головна область пам'яті InnoDB. Там кешуються сторінки таблиць і індексів (по 16 КБ). Запит, якому вистачило даних у пам'яті, не йде на диск. Зміни теж спершу пишуться в сторінки буферного пулу («брудні» сторінки), а на диск скидаються пізніше у фоні.

Розмір - найважливіше налаштування MySQL. Параметр innodb_buffer_pool_size, за замовчуванням лише 128 МБ - для продакшену майже завжди замало.

Як вибирати:

• на виділеному під MySQL сервері - зазвичай 50-75% оперативної пам'яті. Решта потрібна ОС, з'єднанням (буфери сортування, з'єднань на кожну сесію), тимчасовим таблицям;
• якщо на тому ж сервері працюють PHP, Redis, черги - менше, залежно від їхніх потреб;
• ідеал - щоб робочий набір (часто читані таблиці й індекси) поміщався в пул повністю. Вся база в пам'ять не обов'язкова.

Для виділеного сервера є ще варіант innodb_dedicated_server = ON: InnoDB сам визначає розмір пулу й журналу повтору з обсягу пам'яті.

Як перевірити, чи вистачає:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests - логічні читання
-- Innodb_buffer_pool_reads         - довелося йти на диск

SELECT * FROM sys.innodb_buffer_stats_by_table LIMIT 10;  -- хто займає пул

Частка reads / read_requests має бути дуже малою (соті частки відсотка). Помітне зростання - робочий набір не вміщується.

Як влаштоване витіснення. Пул використовує LRU з «точкою вставки посередині»: нові сторінки потрапляють не на початок списку, а в «старшу» частину (останні 3/8). Так одноразове повне сканування великої таблиці (наприклад, дамп) не витісняє з пам'яті гарячі дані.

Корисне:

• розмір можна змінювати на льоту (SET PERSIST innodb_buffer_pool_size = ...), зміна відбувається поступово блоками;
• під час зупинки MySQL зберігає список гарячих сторінок, а під час старту завантажує їх (innodb_buffer_pool_dump_at_shutdown / load_at_startup, увімкнено за замовчуванням), тож після перезапуску кеш «прогрівається» швидше;
• на великих пулах він ділиться на кілька екземплярів (innodb_buffer_pool_instances), щоб зменшити конкуренцію.

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

Блокування метаданих (metadata lock, MDL) захищає структуру таблиці: поки хтось працює з таблицею в транзакції, змінити її схему не можна. Будь-який запит до таблиці, навіть звичайний SELECT, бере спільне MDL і тримає його до кінця транзакції. ALTER TABLE потребує виключного MDL.

Класичний сценарій аварії:

-- сесія 1: хтось у консолі
START TRANSACTION;
SELECT * FROM orders LIMIT 1;   -- MDL взято, транзакція не закрита
-- ... пішов на обід

-- сесія 2: деплой
ALTER TABLE orders ADD COLUMN note TEXT;   -- чекає на MDL

-- сесії 3..N: звичайний трафік
SELECT * FROM orders WHERE id = 5;         -- теж чекають!

Найнеприємніше - останній крок: ALTER стоїть у черзі за виключним блокуванням, і всі нові запити до таблиці стають у чергу за ним. Таблиця фактично недоступна, хоча сам ALTER навіть не почався. Пул з'єднань застосунку швидко вичерпується.

Звідки беруться довгі транзакції:

• відкрита транзакція в консолі чи GUI-клієнті з вимкненим автокомітом;
• воркер черги, що відкрив транзакцію й чекає на зовнішній API;
• довгий звіт чи дамп без --single-transaction.

Як знайти винуватця:

SELECT * FROM sys.schema_table_lock_waits\G
-- хто чекає, хто блокує, і готова команда KILL для блокувальника

SELECT trx_mysql_thread_id, trx_started, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;

Як захистити деплой:

• задати короткий тайм-аут очікування для сесії міграцій. За замовчуванням lock_wait_timeout - рік:

SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD COLUMN note TEXT;   -- не дочекався за 5 с - помилка, а не простій

• повторювати міграцію кілька разів з паузою;
• перед міграцією перевіряти innodb_trx на довгі транзакції;
• обмежувати довжину транзакцій у застосунку й тайм-аут неактивних сесій (wait_timeout).

Онлайн DDL не рятує: навіть ALGORITHM=INSTANT бере виключне MDL на коротку мить на початку й наприкінці - і так само може застрягти за довгою транзакцією.

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

За замовчуванням (innodb_file_per_table = ON) кожна таблиця InnoDB живе у власному файлі .ibd. Коли рядки видаляють, InnoDB позначає місце вільним усередині файлу й використовує його для нових рядків цієї ж таблиці. Операційній системі місце не повертається.

-- таблиця логів займає 40 ГБ
DELETE FROM activity_log WHERE created_at < NOW() - INTERVAL 90 DAY;
-- файл activity_log.ibd усе ще 40 ГБ, але всередині багато вільних сторінок

Як оцінити вільне місце:

SELECT table_name,
       ROUND(data_length / 1024 / 1024) AS data_mb,
       ROUND(index_length / 1024 / 1024) AS index_mb,
       ROUND(data_free / 1024 / 1024) AS free_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_free DESC;

Цифри приблизні (оцінка за статистикою), але порядок величин видно.

Як повернути місце ОС:

• OPTIMIZE TABLE - для InnoDB це перебудова таблиці (ALTER TABLE ... FORCE): створюється нова компактна копія, стара видаляється. Виконується онлайн (паралельні зміни дозволені), але потребує вільного диска на розмір таблиці й створює навантаження;
• TRUNCATE TABLE - якщо треба очистити все: файл перестворюється порожнім, миттєво;
• секціонування за датою й ALTER TABLE ... DROP PARTITION - найкращий варіант для логів: стара частина видаляється разом із файлом, без DELETE мільйонів рядків.

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

Системний табличний простір ibdata1 не зменшується ніколи. Якщо таблиці колись створили з innodb_file_per_table = OFF, їхні дані лежать там, і звільнити місце можна лише перенесенням даних у новий інстанс. Тому вимикати file-per-table немає причин.

Масове видалення варто робити частинами (DELETE ... LIMIT 10000 у циклі): один гігантський DELETE - це довга транзакція, величезний undo-журнал і затримка реплік.

Докладніше в документації: Табличні простори file-per-table

Формат рядка визначає, як InnoDB розкладає колонки рядка по сторінках. Є чотири: REDUNDANT, COMPACT, DYNAMIC і COMPRESSED. За замовчуванням - DYNAMIC, і його варто лишати.

Головна відмінність - довгі колонки (TEXT, BLOB, JSON, довгі VARCHAR):

• DYNAMIC: якщо значення не вміщується в сторінку, воно цілком переноситься на окремі сторінки переповнення, а в рядку лишається 20-байтовий вказівник;
• COMPACT: у рядку лишаються перші 768 байтів значення, решта - на сторінках переповнення. Широка таблиця з багатьма TEXT швидко впирається в ліміт.

Звідки «Row size too large» - насправді два різні ліміти:

1. 65 535 байтів на рядок - ліміт MySQL для визначення таблиці. Рахується максимальний розмір усіх колонок. VARCHAR(255) в utf8mb4 важить до 1020 байтів, тож двісті таких колонок уже не вмістяться. TEXT/BLOB додають лише 9-12 байтів, бо зберігаються окремо.
2. Майже половина сторінки (~8 КБ при 16-КБ сторінках) - ліміт InnoDB для частини рядка, що лишається в сторінці. Помилка може з'явитися не при створенні таблиці, а при вставці, коли реальні дані не вмістилися.

-- рішення для 1-го ліміту: довгі VARCHAR перевести в TEXT
ALTER TABLE products MODIFY description TEXT;

-- перевірити формат
SELECT name, row_format FROM information_schema.innodb_tables WHERE name LIKE 'app/%';

Другий наслідок формату - довжина ключа індексу:

• DYNAMIC/COMPRESSED: до 3072 байтів на індекс;
• COMPACT/REDUNDANT: лише 767 байтів.

Звідси історичний Schema::defaultStringLength(191) у Laravel: 191 × 4 байти (utf8mb4) = 764 < 767. На MySQL 5.7.7+ і 8.x з DYNAMIC це обмеження вже не потрібне - VARCHAR(255) під унікальним індексом важить 1020 байтів, що вміщується в 3072.

COMPRESSED стискає сторінки, економлячи диск ціною процесора й складнішої роботи буферного пулу. Сьогодні частіше вибирають стиснення на рівні файлової системи чи сховища.

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

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

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.

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