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

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

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

107 питань

Видаляти індекси страшно: якщо він таки був потрібен якомусь рідкісному, але важливому запиту, після 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 відповідь майже завжди «так».

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

Бінарний журнал може записувати зміни трьома способами, що задається binlog_format.

STATEMENT - записується текст запиту:

UPDATE products SET price = price * 1.1 WHERE category_id = 5;
-- у журналі - один цей рядок, хоч змінилося 100 000 рядків
  • компактно для масових змін;
  • небезпечно для недетермінованих запитів: UUID(), NOW() в певних контекстах, RAND(), LIMIT без ORDER BY, UPDATE ... ORDER BY з різним порядком на репліці, тригери й функції. Репліка може отримати інші дані, і це ніхто не помітить одразу;
  • несумісний з READ COMMITTED для InnoDB.

ROW (за замовчуванням з MySQL 5.7.7) - записуються самі змінені рядки: значення до і після:

  • детерміновано: репліка отримує рівно ті самі дані, незалежно від того, як їх обчислено;
  • безпечно з будь-яким рівнем ізоляції й будь-якими функціями;
  • великий обсяг для масових змін: UPDATE 100 000 рядків - 100 000 записів у журналі;
  • зручний для захоплення змін (CDC): Debezium та подібні інструменти вимагають саме ROW.

MIXED - за замовчуванням STATEMENT, але для небезпечних запитів автоматично перемикається на ROW. Компроміс, що втрачає популярність: складно передбачити, що саме потрапить у журнал.

Налаштування ROW-формату:

  • binlog_row_image:
    • FULL (за замовчуванням) - усі колонки до і після;
    • MINIMAL - лише колонки, що змінилися, плюс ті, що ідентифікують рядок. Помітно зменшує журнал для широких таблиць із TEXT/JSON, але дехто зі споживачів CDC вимагає FULL;
    • NOBLOB - усі, крім незмінених BLOB/TEXT.
  • binlog_row_value_options = PARTIAL_JSON - для JSON_SET/JSON_REPLACE писати лише змінену частину документа.

Важливо для реплікації в ROW: на репліці рядки знаходяться за первинним ключем. Таблиця без первинного ключа змушує репліку для кожного зміненого рядка сканувати всю таблицю - класична причина багатогодинного відставання після одного масового UPDATE.

Як подивитися ROW-події:

mysqlbinlog --base64-output=DECODE-ROWS --verbose binlog.000042
# ### UPDATE `app`.`products`
# ### WHERE
# ###   @1=17 ...
# ### SET
# ###   @1=17 ...

Практична відповідь на співбесіді: використовувати ROW (типове значення), за потреби з binlog_row_image = MINIMAL, і мати первинні ключі в усіх таблицях.

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

GTID (global transaction identifier) - глобально унікальний ідентифікатор кожної закоміченої транзакції:

3e11fa47-71ca-11e1-9e33-c80aa9429562:23
└──────── server_uuid джерела ───────┘ └ номер транзакції

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

З GTID кожен сервер знає множину транзакцій, які він уже застосував (gtid_executed). Перемикання зводиться до «підключись до нового джерела й забери все, чого в тебе немає»:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '10.0.0.11',
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = '...',
    SOURCE_AUTO_POSITION = 1;
START REPLICA;

Увімкнення (на всіх серверах, послідовно, можна без простою через проміжні режими gtid_mode):

gtid_mode = ON
enforce_gtid_consistency = ON

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

Що ще дає GTID:

  • перевірка стану репліки: SHOW REPLICA STATUS показує Retrieved_Gtid_Set і Executed_Gtid_Set, легко порівняти з джерелом;
  • очікування реплікації конкретної транзакції: SELECT WAIT_FOR_EXECUTED_GTID_SET('...', 5) - корисно для читання власних записів з репліки;
  • ідемпотентність: транзакція з уже застосованим GTID пропускається, тож повторне застосування журналу не задвоює дані;
  • основа для Group Replication і InnoDB Cluster.

Типова проблема - «чужі» транзакції на репліці (errant transactions). Хтось виконав INSERT прямо на репліці - у неї з'явився GTID з її власним server_uuid, якого немає на джерелі. При перемиканні, коли ця репліка стане джерелом, інші репліки отримають цю транзакцію. Тому репліки варто тримати в super_read_only = ON.

Відновлення з дампу в GTID-середовищі вимагає уваги до --set-gtid-purged у mysqldump: він записує в дамп множину вже виконаних транзакцій, щоб нова репліка не намагалася отримати їх повторно.

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

Відновлення на момент у часі (point-in-time recovery, PITR) = останній повний бекап + бінарний журнал від моменту бекапу до потрібної точки.

Що потрібно мати заздалегідь:

  • регулярний повний бекап з відомою позицією бінарного журналу (mysqldump --single-transaction --source-data=2 записує її в дамп коментарем; фізичні інструменти - у свої метадані);
  • бінарні журнали, що зберігаються довше за інтервал між бекапами, і бажано копіюються на інший сервер (якщо загине диск, разом із ним загинуть і журнали).

Сценарій: о 14:32 хтось виконав DELETE FROM orders без WHERE. Бекап зроблено о 03:00.

1. Зупинити запис у базу (режим обслуговування), щоб не накопичувати зміни поверх аварії. Скопіювати поточні бінарні журнали в безпечне місце.

2. Знайти точну позицію помилки:

mysqlbinlog --base64-output=DECODE-ROWS --verbose \
    --start-datetime="2026-10-04 14:30:00" --stop-datetime="2026-10-04 14:35:00" \
    binlog.000057 | grep -n -B5 "DELETE FROM \`app\`.\`orders\`"
# знаходимо "# at 48211337" перед подією

3. Відновити бекап на окремому сервері (не поверх продакшену):

mysql app < backup-03-00.sql

4. Застосувати журнал від позиції бекапу до позиції перед DELETE:

# позиція з дампу: -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000055', SOURCE_LOG_POS=157;
mysqlbinlog --start-position=157 binlog.000055 binlog.000056 > /tmp/redo.sql
mysqlbinlog --stop-position=48211337 binlog.000057 >> /tmp/redo.sql
mysql app < /tmp/redo.sql

З GTID зручніше: --exclude-gtids для транзакції з DELETE і --skip-gtids залежно від сценарію.

5. Що робити з даними після аварії. Події після DELETE (нові замовлення з 14:32 до зупинки) теж є в журналі. Можна:

  • застосувати журнал після помилкової транзакції (--start-position на наступну подію), пропустивши лише її;
  • або відновити на окремому сервері лише постраждалу таблицю й перенести видалені рядки на продакшен, не відкочуючи всю базу.

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

Нюанси:

  • --start-datetime/--stop-datetime неточні (секундна точність, в одну секунду - багато подій) - для точки зупинки краще позиція чи GTID;
  • застосовувати журнали кількох файлів треба одним потоком mysqlbinlog чи по черзі без пропусків;
  • відновлення великої бази з логічного дампу займає години - це час простою, який треба знати заздалегідь (RTO).

Захист від таких аварій до бекапів: відкладена репліка (SOURCE_DELAY), обмежені права застосунку й sql_safe_updates у консольних сесіях.

Докладніше в документації: Відновлення на момент у часі через бінарний журнал

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

CREATE ROLE 'app_read', 'app_write', 'app_ddl';

GRANT SELECT ON app.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app.* TO 'app_write';
GRANT CREATE, ALTER, DROP, INDEX, REFERENCES ON app.* TO 'app_ddl';

CREATE USER 'analyst'@'%' IDENTIFIED BY '...';
GRANT 'app_read' TO 'analyst'@'%';

CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY '...';
GRANT 'app_read', 'app_write' TO 'app'@'10.0.0.%';

Головна пастка - ролі неактивні за замовчуванням. Після GRANT 'app_read' TO 'analyst' і підключення:

SELECT * FROM app.orders;
-- ERROR 1142: SELECT command denied to user 'analyst'@...

SELECT CURRENT_ROLE();   -- NONE

Роль призначена, але не активована в сесії. Варіанти:

-- активувати в поточній сесії
SET ROLE 'app_read';
SET ROLE ALL;

-- зробити ролі активними за замовчуванням при вході
SET DEFAULT ROLE ALL TO 'analyst'@'%';

-- або глобально: активувати всі призначені ролі при вході кожного користувача
SET PERSIST activate_all_roles_on_login = ON;

Для облікового запису застосунку зручно SET DEFAULT ROLE ALL, інакше Laravel отримає помилки прав одразу після деплою.

Перевірити ефективні права:

SHOW GRANTS FOR 'analyst'@'%';                     -- видно лише призначені ролі
SHOW GRANTS FOR 'analyst'@'%' USING 'app_read';    -- права з урахуванням ролі

Корисні можливості:

  • обов'язкові ролі (mandatory_roles) - автоматично призначаються всім користувачам;
  • роль - фактично заблокований обліковий запис, тож ролі можна вкладати одну в одну (GRANT 'app_read' TO 'app_write');
  • REVOKE 'app_write' FROM 'app'@'10.0.0.%' - забрати роль, не перебираючи окремі права.

Як це використовувати в проєкті: ролі під призначення (читання, запис, міграції, бекап, моніторинг) і облікові записи під конкретні сервіси чи людей. Коли співробітник іде - видаляється лише його обліковий запис, а не переписуються права. Аудит прав зводиться до перегляду кількох ролей.

У керованих хмарних MySQL набір доступних привілеїв обмежений провайдером, але ролі працюють так само.

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

caching_sha2_password - плагін автентифікації за замовчуванням з MySQL 8.0. Він замінив mysql_native_password, що спирався на SHA-1 без солі: однакові паролі давали однакові хеші, а вкрадена таблиця mysql.user легко перебиралася.

Як працює новий плагін:

  • пароль зберігається з сіллю і багатьма раундами SHA-256;
  • перша автентифікація користувача після старту сервера - «повна»: пароль треба передати безпечно, тобто через TLS або з шифруванням публічним RSA-ключем сервера;
  • після успішного входу сервер кешує хеш у пам'яті, і наступні підключення проходять швидко без повного обміну.

Звідки беруться помилки підключення:

  1. Старий клієнт не знає плагіна. Повідомлення на кшталт The server requested authentication method unknown to the client - типова історія старих версій PHP. Драйвер mysqlnd підтримує caching_sha2_password з PHP 7.4, тож на сучасному PHP проблеми немає. Але старі GUI-клієнти, бібліотеки інших мов і бінарники в Docker-образах можуть її мати.

  2. Підключення без TLS і без RSA-ключа. Помилка на зразок Authentication requires secure connection. Варіанти: увімкнути TLS (правильно), дозволити клієнту запитати ключ сервера (--get-server-public-key / GET_SOURCE_PUBLIC_KEY=1 для реплікації) чи передати файл ключа.

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

MySQL 8.4: mysql_native_password вимкнено за замовчуванням. Облікові записи, створені зі старим плагіном (наприклад, перенесені з 5.7), після оновлення не зможуть увійти, доки плагін не ввімкнуть:

[mysqld]
mysql_native_password = ON

Це тимчасовий милиць: у MySQL 9 плагін видалено зовсім. Правильний шлях - перевести облікові записи:

SELECT user, host, plugin FROM mysql.user WHERE plugin = 'mysql_native_password';

ALTER USER 'app'@'10.0.0.%' IDENTIFIED WITH caching_sha2_password BY 'пароль';

Практичні висновки:

  • перед оновленням до 8.4 знайти всі облікові записи зі старим плагіном і перевести їх;
  • підключення до бази за межами одного хоста - через TLS, що одночасно знімає й питання першої автентифікації;
  • оновити клієнтські бібліотеки й інструменти, а не повертати старий плагін.

Докладніше в документації: Автентифікація caching_sha2_password

MySQL обслуговує кожне клієнтське з'єднання окремим потоком. Кількість одночасних з'єднань обмежена max_connections - за замовчуванням 151. Коли ліміт вичерпано, нові підключення отримують ERROR 1040: Too many connections.

Резерв для адміністратора. MySQL дозволяє одне додаткове з'єднання понад ліміт для користувача з привілеєм CONNECTION_ADMIN, щоб у момент аварії можна було зайти й розібратися. Якщо застосунок підключається під root, він з'їсть і цей резерв. Ще надійніше - окремий адміністративний інтерфейс (admin_address, admin_port) зі своїм лімітом.

Як порахувати потребу. У PHP кожен процес, що обробляє запит, тримає власне з'єднання:

PHP-FPM: pm.max_children = 50 на сервер × 4 вебсервери     = 200
воркери черг (Horizon): 30 процесів × 2 сервери            = 60
планувальник, Pulse, разові команди                        ~ 10
                                                            ─────
                                                             270 + запас

Для Octane/FrankenPHP у режимі воркерів - кількість воркерів на всіх інстансах: з'єднання живуть довго й тримаються постійно.

Чому не можна просто поставити 5000:

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

Корисні дані - не скільки з'єднань відкрито, а скільки одночасно виконують запити:

SHOW GLOBAL STATUS WHERE variable_name IN
    ('Threads_connected', 'Threads_running', 'Max_used_connections',
     'Max_used_connections_time', 'Aborted_connects', 'Connection_errors_max_connections');

Типові причини раптового вичерпання:

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

Рішення на рівні архітектури:

  • пул з'єднань перед MySQL - ProxySQL чи MySQL Router: тисячі з'єднань від застосунку мультиплексуються в десятки до бази;
  • пул потоків (thread pool) - у MySQL Enterprise, Percona Server і MariaDB: обмежує кількість одночасно виконуваних запитів незалежно від кількості з'єднань;
  • wait_timeout (за замовчуванням 8 годин) зменшити, щоб забуті неактивні з'єднання закривалися;
  • окремі ліміти на користувача: ALTER USER ... WITH MAX_USER_CONNECTIONS 50 - щоб аналітик чи воркер не забрав усі з'єднання в застосунку.

Докладніше в документації: Керування з'єднаннями

Performance Schema - вбудований механізм інструментування MySQL: сервер збирає статистику про запити, очікування, блокування, використання пам'яті, файловий ввід-вивід. Увімкнено за замовчуванням. Дані лежать у таблицях performance_schema.*, але сирі й незручні.

sys schema - набір подань і процедур поверх Performance Schema та information_schema, які перетворюють сирі дані на зрозумілі звіти (з людськими одиницями часу й розміру).

Найкорисніші подання:

1. Які запити сумарно найдорожчі:

SELECT query, db, exec_count, total_latency, avg_latency,
       rows_examined_avg, rows_sent_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

Запити згруповано за дайджестом - нормалізованим текстом без конкретних значень (WHERE id = ?), тож тисячі викликів з різними id - один рядок.

2. Повні сканування і сортування на диску:

SELECT * FROM sys.statements_with_full_table_scans ORDER BY total_latency DESC LIMIT 10;
SELECT * FROM sys.statements_with_sorting ORDER BY sort_merge_passes DESC LIMIT 10;
SELECT * FROM sys.statements_with_temp_tables ORDER BY disk_tmp_tables DESC LIMIT 10;

3. Індекси:

SELECT * FROM sys.schema_unused_indexes;        -- не використовувались з моменту старту
SELECT * FROM sys.schema_redundant_indexes;     -- дублюють інші
SELECT * FROM sys.schema_index_statistics ORDER BY rows_selected DESC LIMIT 10;

4. Блокування зараз:

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

5. Таблиці з найбільшим вводом-виводом і пам'ять:

SELECT * FROM sys.schema_table_statistics ORDER BY total_latency DESC LIMIT 10;
SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10;

Що варто знати:

  • статистика накопичується з моменту старту сервера (чи з останнього скидання). Порівнювати варто зміни за період: зберегти знімок, почекати, порівняти. Процедура sys.diagnostics() формує звіт саме так;
  • скинути лічильники запитів: CALL sys.ps_truncate_all_tables(FALSE); - зручно перед вимірюванням ефекту оптимізації;
  • таблиця дайджестів має обмежений розмір (performance_schema_digests_size), рідкісні запити можуть витіснятися;
  • накладні витрати Performance Schema невеликі (кілька відсотків), але додаткові інструменти (наприклад, історія всіх очікувань) варто вмикати лише на час діагностики.

Практичний ритуал при скаргах «база повільна»: sys.statement_analysis за сумарним часом → EXPLAIN ANALYZE для лідера → sys.innodb_lock_waits, якщо проблема в очікуваннях, а не в самих запитах.

Докладніше в документації: Схема sys

Секціонування ділить одну логічну таблицю на кілька фізичних частин за значенням колонки. Для запитів таблиця одна, а на диску - окремі файли на кожну секцію.

CREATE TABLE activity_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    created_at DATETIME NOT NULL,
    payload JSON,
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE COLUMNS (created_at) (
    PARTITION p2026_08 VALUES LESS THAN ('2026-09-01'),
    PARTITION p2026_09 VALUES LESS THAN ('2026-10-01'),
    PARTITION p2026_10 VALUES LESS THAN ('2026-11-01'),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

Типи: RANGE (діапазони, найчастіше дати), LIST (списки значень), HASH і KEY (рівномірний розподіл).

Де секціонування справді допомагає:

  • видалення старих даних. Замість DELETE мільйонів рядків (довга транзакція, роздутий undo, затримка реплік, місце на диску не звільняється):
ALTER TABLE activity_log DROP PARTITION p2026_08;   -- миттєво, з файлом
  • відсікання секцій (partition pruning): запит з умовою на ключ секціонування читає лише потрібні секції. В EXPLAIN - колонка partitions;
  • обслуговування частинами: перебудова чи аналіз окремої секції.

Обмеження, про які часто дізнаються запізно:

  • кожен унікальний ключ, включно з первинним, мусить містити колонку секціонування. Тому в прикладі первинний ключ (id, created_at). Унікальність email у секціонованій за датою таблиці забезпечити неможливо;
  • зовнішні ключі не підтримуються для секціонованих таблиць InnoDB - ні з неї, ні на неї;
  • запити без умови на ключ секціонування перебирають усі секції - це повільніше, ніж одна несекціонована таблиця з хорошим індексом;
  • глобальних індексів немає: індекс існує окремо в кожній секції;
  • до 8192 секцій, але сотні секцій уже уповільнюють відкриття таблиці й планування.

Коли секціонування шкодить: як «прискорювач» звичайної OLTP-таблиці. Таблиця orders на 50 мільйонів рядків з правильними індексами працює чудово - B-дерево на 50 млн рядків має лише 3-4 рівні. Секціонування за user_id не дасть нічого, крім обмежень.

Найкращий кандидат: журнали, події, метрики, історія - дані, що додаються за часом і видаляються за часом, а запити майже завжди мають умову на дату.

Супровід: секції на майбутнє треба створювати заздалегідь (крон-задача раз на місяць: REORGANIZE PARTITION pmax INTO (...)). Laravel-міграції секціонування не підтримують - лише DB::statement().

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

Логічний бекап - дані у вигляді SQL чи CSV (mysqldump, утиліти MySQL Shell):

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

Фізичний бекап - копія файлів даних (Percona XtraBackup, MySQL Enterprise Backup, знімки диска):

  • швидке відновлення - файли просто повертаються на місце;
  • прив'язаний до версії MySQL і конфігурації;
  • копія займає стільки ж, скільки дані, включно з фрагментацією.

Гарячий, теплий, холодний:

  • гарячий - база працює, читання й запис дозволені (mysqldump --single-transaction для InnoDB, XtraBackup);
  • теплий - читати можна, писати ні (дамп з блокуванням таблиць);
  • холодний - сервер зупинено, копіюються файли. Найпростіший, але з простоєм.

Повний та інкрементний: інкрементний містить лише зміни з попереднього бекапу. У MySQL роль інкрементних бекапів часто виконують бінарні журнали: повний бекап раз на добу + безперервне копіювання журналів = відновлення на будь-яку секунду.

Знімки файлової системи (LVM, ZFS, хмарні диски) - миттєвий фізичний бекап. Щоб знімок був узгодженим, InnoDB має бути готовий: знімок робиться при FLUSH TABLES WITH READ LOCK або покладається на відновлення після збою (InnoDB відновиться, як після раптового вимкнення).

Як обрати стратегію - від вимог, а не від інструментів:

  • RPO (recovery point objective) - скільки даних можна втратити. «Нічого» - потрібні бінарні журнали, що безперервно копіюються за межі сервера;
  • RTO (recovery time objective) - скільки триває відновлення. «15 хвилин» для бази на 500 ГБ - фізичний бекап чи готова репліка, але не mysqldump.

Типова схема для середнього проєкту:

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

Репліка - не бекап. Випадковий DROP TABLE миттєво реплікується на всі репліки. Від помилок людей захищають бекапи й відкладені репліки.

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

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

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

DELIMITER //

CREATE PROCEDURE close_month(IN p_month DATE)
BEGIN
    START TRANSACTION;
    INSERT INTO monthly_totals (month, total)
    SELECT p_month, SUM(amount) FROM payments
    WHERE created_at >= p_month AND created_at < p_month + INTERVAL 1 MONTH;
    UPDATE payments SET closed = 1
    WHERE created_at >= p_month AND created_at < p_month + INTERVAL 1 MONTH;
    COMMIT;
END //

CREATE FUNCTION vat(amount DECIMAL(12,2)) RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL
RETURN amount * 0.2 //

DELIMITER ;

CALL close_month('2026-09-01');
SELECT id, vat(total) FROM orders;

DELIMITER - команда клієнта mysql, а не сервера: тіло процедури містить ;, тож клієнту потрібен інший роздільник. Через PDO (і DB::unprepared() у Laravel) він не потрібен - запит надсилається цілком.

Характеристики функції - DETERMINISTIC, NO SQL, READS SQL DATA. Якщо ввімкнено бінарний журнал, MySQL відмовиться створити функцію без цих позначок (або без log_bin_trust_function_creators): недетермінована функція могла б дати різні результати на джерелі й репліці. Позначка - обіцянка: неправдиве DETERMINISTIC MySQL не перевіряє.

Коли процедури доречні:

  • обробка великих обсягів даних без передачі їх у застосунок;
  • спільна логіка для кількох застосунків на різних мовах, що працюють з однією базою;
  • адміністративні операції, які запускає DBA.

Чому в застосунках на Laravel їх зазвичай уникають:

  • тестування й налагодження складніші: немає Pest, покриття, нормального налагоджувача;
  • деплой і версіонування: код живе в міграціях, рев'ю й відкат незручні, легко розійтися між середовищами;
  • масштабування: база - найважче для масштабування місце, а логіка в ній додає навантаження;
  • обмеження: функції й тригери не можуть змінювати таблицю, яку вже читає оператор, що їх викликав; рекурсивні функції заборонені;
  • знання в команді: критична логіка на мові, якою команда пише рідко.

Виклик з Laravel: DB::select('CALL report_for(?)', [$month]) для процедури з результатом, DB::statement('CALL close_month(?)', [...]) - без нього. Помилка з SIGNAL SQLSTATE '45000' приходить як QueryException.

Докладніше в документації: MySQL: CREATE PROCEDURE і CREATE FUNCTION

Витягти значення з JSON-колонки:

SELECT id,
       settings->'$.theme'   AS theme_json,   -- "dark" (JSON-значення з лапками)
       settings->>'$.theme'  AS theme         -- dark (звичайний рядок)
FROM users;
Оператор Еквівалент Результат
col->'$.path' JSON_EXTRACT(col, '$.path') JSON-значення
col->>'$.path' JSON_UNQUOTE(JSON_EXTRACT(col, '$.path')) рядок без лапок

Різниця важлива в порівняннях: settings->'$.theme' = 'dark' порівнює JSON з рядком і може дати не той результат, якого очікуєте; для умов і сортування зазвичай потрібен ->>.

Шляхи: $.address.city, елемент масиву $.tags[0], усі елементи $.tags[*].

JSON_TABLE - перетворити JSON-масив на рядки й колонки, з якими працює звичайний SQL:

SELECT o.id, items.sku, items.qty
FROM orders o,
     JSON_TABLE(o.payload, '$.items[*]' COLUMNS (
         sku VARCHAR(32) PATH '$.sku',
         qty INT         PATH '$.qty' DEFAULT '1' ON EMPTY
     )) AS items
WHERE items.sku = 'A-1';

Корисно, щоб розібрати масив позицій із зовнішнього API, порахувати агрегати по елементах масиву чи перенести дані з JSON у нормальні таблиці під час міграції.

Зміна JSON без перезапису всього документа:

UPDATE users SET settings = JSON_SET(settings, '$.theme', 'light') WHERE id = 7;

Для JSON_SET, JSON_REPLACE, JSON_REMOVE InnoDB може оновити документ частково, і в бінарний журнал (з binlog_row_value_options=PARTIAL_JSON) потрапить лише зміна.

У Laravel:

User::where('settings->theme', 'dark')->get();          // json_unquote(json_extract(...)), тобто те саме, що ->>
User::whereJsonContains('settings->tags', 'php')->get();
$user->update(['settings->theme' => 'light']);          // JSON_SET

Обмеження: умова по settings->>'$.theme' не використовує звичайних індексів. Для частих фільтрів потрібна згенерована колонка з індексом або функціональний індекс; для масивів - багатозначний індекс (MEMBER OF). Якщо поле фільтрують у кожному запиті, це сигнал винести його в окрему колонку.

Докладніше в документації: MySQL: функції JSON_TABLE

Тригер - SQL-код, який MySQL виконує автоматично до чи після INSERT, UPDATE або DELETE для кожного рядка.

DELIMITER //
CREATE TRIGGER orders_audit
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF NOT (OLD.status <=> NEW.status) THEN
        INSERT INTO order_status_log (order_id, old_status, new_status, changed_at)
        VALUES (NEW.id, OLD.status, NEW.status, NOW());
    END IF;
END //
DELIMITER ;
  • OLD - рядок до зміни (у UPDATE і DELETE), NEW - після (у INSERT і UPDATE);
  • у тригері BEFORE можна змінити значення: SET NEW.slug = LOWER(NEW.slug);
  • SIGNAL SQLSTATE '45000' у BEFORE-тригері скасовує операцію з помилкою.

Обмеження MySQL, які варто знати:

  • лише рядкові тригери (FOR EACH ROW) - тригерів рівня оператора, як у PostgreSQL, немає. Масовий UPDATE мільйона рядків - мільйон викликів;
  • не можна змінювати ту саму таблицю, на яку спрацював тригер (і будь-яку таблицю, яку вже читає оператор, що його викликав) - помилка Can't update table ... in stored function/trigger;
  • тригери не спрацьовують на дії зовнішніх ключів: ON DELETE CASCADE видалить дочірні рядки без їхніх тригерів;
  • кілька тригерів на одну подію дозволені, порядок задають FOLLOWS / PRECEDES;
  • реплікація: з рядковим форматом бінарного журналу (за замовчуванням) на репліці тригери не виконуються - туди приходять уже готові зміни рядків, включно з тими, що зробив тригер на джерелі. Зі STATEMENT-форматом тригери виконуються на репліці знову.

Тригер чи подія Eloquent:

Тригер Подія моделі Eloquent
спрацьовує при масовому update(), сирому SQL, зміні з іншого сервісу так ні
видно в коді застосунку ні так
тестування на справжній базі звичайними тестами
може надіслати лист, поставити завдання в чергу ні так

Підводні камені: невидима логіка (розробник не знає, що UPDATE змінює ще одну таблицю), складність налагодження, додаткові блокування в довгих транзакціях. Тригери мають бути задокументовані, мати зрозумілі назви й створюватися в міграціях (DB::unprepared()), а тести - запускатися на MySQL, а не SQLite.

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

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

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

Рівні
Junior 32 Middle 44 Senior 31

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