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

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

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

107 питань

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.

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

Питання з реальних технічних співбесід - 107 питань у 5 темах, розібраних із відповідями. Нижче - розбивка за рівнями та темами, якщо хочете звузити підготовку.

Рівні
Junior 32 Middle 44 Senior 31

Готуєтесь до співбесіди не просто так: зараз на сайті 90 відкритих вакансій Laravel і PHP. Переглянути вакансії