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

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

Невидимий індекс (MySQL 8.0+) продовжує існувати й оновлюватися при кожному записі, але оптимізатор його не бачить:

ALTER TABLE orders ALTER INDEX orders_status_idx INVISIBLE;
-- спостерігаємо день-тиждень: чи не з'явилися повільні запити
ALTER TABLE orders ALTER INDEX orders_status_idx VISIBLE;  -- миттєвий відкат
-- або, якщо все спокійно
ALTER TABLE orders DROP INDEX orders_status_idx;

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

Як знайти кандидатів на видалення:

-- індекси, які не використовувалися з моменту запуску сервера
SELECT * FROM sys.schema_unused_indexes;

-- індекси, що дублюють інші (лівий префікс іншого індексу)
SELECT * FROM sys.schema_redundant_indexes;

Статистика використання береться з Performance Schema й скидається при перезапуску сервера. Якщо сервер перезапускали вчора, «невикористаний» індекс міг бути потрібен щомісячному звіту. Тому період спостереження має охоплювати всі регулярні задачі.

Перевірити запит з невидимими індексами в поточній сесії:

SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN SELECT ...;   -- чи обрав би оптимізатор цей індекс

Так само можна підготувати новий індекс: створити його невидимим, перевірити плани в сесії й лише потім відкрити для всіх.

Обмеження:

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

Чому взагалі видаляти індекси: кожен індекс додає вартість кожному INSERT, UPDATE і DELETE, займає буферний пул і місце на диску. Зайві індекси - тихий податок на запис.

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

Доступ range читає один чи кілька відрізків індексу: BETWEEN, >, <, LIKE 'abc%', IN (...), OR за однією колонкою. Список IN (1, 5, 9) - це три «відрізки» з одного значення.

Як оптимізатор оцінює кількість рядків. Для кожного відрізка він може зробити index dive - спуститися в B-дерево до початку й кінця відрізка й точно оцінити, скільки записів між ними. Точно, але для довгого списку дорого: тисяча значень у IN - тисяча спусків ще до виконання запиту.

eq_range_index_dive_limit (за замовчуванням 200): якщо в рівностях більше значень, MySQL замість спусків використовує середню статистику індексу (скільки рядків припадає на одне значення). Це швидко, але неточно для нерівномірних даних - звідси раптова зміна плану, коли список у whereIn переростає 200 елементів.

Ліміт пам'яті оптимізатора діапазонів range_optimizer_max_mem_size (8 МБ за замовчуванням). Дуже довгі IN чи складні OR можуть його перевищити - тоді MySQL відмовляється від діапазонного доступу, видає попередження й може обрати повне сканування.

Складені індекси й діапазони:

-- індекс (status, created_at)
WHERE status IN ('new', 'paid') AND created_at > '2026-09-01'
-- два відрізки: ('new', > дата) і ('paid', > дата), обидві колонки працюють

WHERE status > 'a' AND created_at > '2026-09-01'
-- діапазон по першій колонці - created_at для пошуку вже не використовується

IN у першій колонці поводиться як кілька рівностей, тому наступні колонки індексу лишаються корисними. Діапазон - ні.

Кортежі в IN теж оптимізуються діапазоном:

SELECT * FROM prices WHERE (product_id, currency) IN ((1, 'UAH'), (2, 'USD'));

Практичні поради для Laravel:

• whereIn з десятками тисяч id - запит стає величезним, розбір і оптимізація дорогі, легко впертися в max_allowed_packet. Краще обробляти частинами (chunkById, lazyById) або вставити id у тимчасову таблицю й з'єднати;
• Eloquent при with() генерує whereIn за всіма ключами батьківських моделей: жадібне завантаження для 50 000 моделей - це IN з 50 000 значень. Ще одна причина обробляти великі вибірки частинами;
• whereIntegerInRaw() уникає зв'язування тисяч параметрів для цілих чисел.

Діагностика: в EXPLAIN - type = range і rows; у виводі оптимізатора (optimizer trace) видно, чи робилися index dives і чому обрано план.

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

Зазвичай MySQL використовує один індекс на таблицю в запиті. Index merge - виняток: кілька діапазонних сканувань різних індексів однієї таблиці, результати яких об'єднуються.

Три варіанти (видно в Extra при type = index_merge):

• Using union(a_idx, b_idx) - для OR:

SELECT * FROM users WHERE email = 'a@b.ua' OR phone = '380501234567';
-- окремо шукає за індексом email, окремо за phone, об'єднує id

• Using intersect(a_idx, b_idx) - для AND за колонками з різних індексів: перетин множин первинних ключів;
• Using sort_union(...) - як union, але для діапазонів: id треба спершу відсортувати.

Для OR між різними колонками злиття - добрий результат: без нього був би повний перебір таблиці. Альтернатива, яку інколи варто написати явно, - UNION двох запитів, кожен з яких використовує свій індекс:

SELECT * FROM users WHERE email = ?
UNION
SELECT * FROM users WHERE phone = ?;

А ось intersect - майже завжди сигнал проблеми:

-- окремі індекси (user_id) і (status)
SELECT * FROM orders WHERE user_id = 7 AND status = 'paid';
-- Using intersect(orders_user_idx, orders_status_idx)

MySQL читає всі замовлення користувача, всі оплачені замовлення (а їх можуть бути мільйони) і перетинає. Один складений індекс (user_id, status) знайшов би потрібні рядки одним проходом. Окремі індекси на кожну колонку «про всяк випадок» - типова помилка, що й призводить до злиття.

Обмеження index merge:

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

Керування:

-- вимкнути для запиту
SELECT /*+ NO_INDEX_MERGE(orders) */ * FROM orders WHERE ...;

-- глобально окремі алгоритми через optimizer_switch:
-- index_merge, index_merge_union, index_merge_intersection, index_merge_sort_union

Практичний висновок: побачивши index_merge у плані частого запиту, перше питання - чи не потрібен тут складений індекс. Для intersect відповідь майже завжди «так».

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

Бінарний журнал може записувати зміни трьома способами, що задається 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;

Різниця важлива в порівняннях: 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:

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

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

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