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

Коли транзакція стає в чергу за блокуванням, InnoDB будує граф очікування (хто кого чекає) і перевіряє, чи не утворився цикл. Знайшовши цикл, він обирає «жертву» - транзакцію, відкіт якої найдешевший (за кількістю змінених і заблокованих рядків), - і відкочує її з помилкою 1213. Решта продовжує.

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

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

Вимкнення:

SET PERSIST innodb_deadlock_detect = OFF;
SET PERSIST innodb_lock_wait_timeout = 3;   -- за замовчуванням 50 с

Без виявлення справжні взаємоблокування розриваються лише тайм-аутом очікування (помилка 1205). Тому тайм-аут треба різко зменшити, інакше учасники циклу висітимуть по 50 секунд.

Важлива різниця між помилками:

• 1213 (deadlock) - транзакцію відкочено повністю;
• 1205 (lock wait timeout) - за замовчуванням відкочується лише останній оператор, транзакція лишається відкритою (innodb_rollback_on_timeout = OFF). Застосунок має сам зробити ROLLBACK і повторити, інакше закомітить частину змін.

DB::transaction(..., attempts: 3) у Laravel повторює транзакцію в обох випадках і на будь-якому винятку сам робить ROLLBACK. Ризик закомітити частину змін лишається при ручному керуванні транзакціями (beginTransaction() / commit()) з перехопленням винятків.

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

Краще лікування - прибрати гарячий рядок:

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

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

Багато великих інсталяцій MySQL працюють на READ COMMITTED замість типового REPEATABLE READ. Причина - менше блокувань, а не інша видимість даних.

Що змінюється в блокуваннях:

• немає блокувань проміжків (gap locks) при пошуку й скануванні - лише блокування самих записів. Вставки в діапазони, які читали інші, більше не чекають. Gap locks лишаються тільки для перевірки зовнішніх ключів і дублікатів;
• блокування невідповідних рядків знімаються одразу. UPDATE ... WHERE status = 'new' без індексу на status на REPEATABLE READ тримає блокування на всіх проглянутих рядках до коміту. На READ COMMITTED рядки, що не підійшли під умову, розблоковуються після перевірки;
• напівузгоджене читання для UPDATE: якщо рядок заблоковано, InnoDB читає його останню закомічену версію, щоб перевірити умову WHERE, і чекає лише якщо рядок справді підходить.

Разом це помітно зменшує кількість очікувань і взаємоблокувань при паралельних записах.

Що змінюється в читанні:

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

Обов'язкова умова - рядковий бінарний журнал. На READ COMMITTED реплікація за операторами (binlog_format=STATEMENT) небезпечна, і MySQL відмовиться писати такі зміни в журнал. З ROW (за замовчуванням) проблем немає.

Як перейти:

SET PERSIST transaction_isolation = 'READ-COMMITTED';

Або точково - для підключення в config/database.php ('isolation_level' => 'READ COMMITTED') чи для окремої транзакції.

Що перевірити в коді перед переходом:

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

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

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

До MySQL 8.0 синтаксис INDEX (a DESC) приймався, але ігнорувався - індекс завжди будувався за зростанням. З 8.0 спадні індекси справжні: значення фізично впорядковані за спаданням.

Коли це потрібно - сортування в різних напрямках:

SELECT * FROM products
WHERE category_id = 3
ORDER BY rating DESC, price ASC
LIMIT 20;

• індекс (category_id, rating, price): читаючи вперед, отримуємо rating ASC, price ASC; назад - rating DESC, price DESC. Потрібної комбінації DESC, ASC немає - буде Using filesort;
• індекс (category_id, rating DESC, price ASC) віддає рядки рівно в потрібному порядку, запит читає 20 записів і зупиняється.

Коли спадний індекс НЕ потрібен. Сортування в одному напрямку будь-яким індексом обслуговується і вперед, і назад:

-- індекс (user_id, created_at)
WHERE user_id = 7 ORDER BY created_at DESC LIMIT 20
-- EXPLAIN: Extra = Backward index scan; Using index condition...

Зворотне сканування працює, але в InnoDB трохи повільніше за пряме: сторінки зв'язані у двонапрямлений список, проте всередині сторінки записи оптимізовані для руху вперед. На гарячих запитах «останні N записів» спадний індекс (user_id, created_at DESC) дає невеликий, але вимірний виграш - і прибирає Backward index scan з плану.

Інші ситуації:

• MIN()/MAX() за колонкою індексу оптимізуються в обох напрямках;
• GROUP BY зі спадними частинами індексу підтримується;
• спадні індекси підтримує лише InnoDB, і не для FULLTEXT, SPATIAL чи хеш-індексів.

У Laravel-міграції напрям колонки в індексі задається через сирий вираз:

$table->rawIndex('category_id, rating DESC, price', 'products_category_rating_price_idx');

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

PostgreSQL має спадні індекси давно, плюс NULLS FIRST/LAST. У MySQL NULL завжди вважається найменшим значенням, і окремого керування його позицією в індексі немає.

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

Оптимізаторні підказки - коментарі спеціального вигляду одразу після ключового слова SELECT, UPDATE, DELETE чи INSERT. Вони керують оптимізатором точніше, ніж старі FORCE INDEX, і можуть стосуватися окремого запиту, таблиці чи індексу.

SELECT /*+ MAX_EXECUTION_TIME(2000) */ * FROM reports WHERE ...;

Основні групи:

• індекси: INDEX(t idx), NO_INDEX(t idx), точніше - JOIN_INDEX, ORDER_INDEX, GROUP_INDEX; також INDEX_MERGE / NO_INDEX_MERGE, SKIP_SCAN, NO_ICP, NO_RANGE_OPTIMIZATION;
• порядок з'єднань: JOIN_ORDER(a, b), JOIN_PREFIX(a), JOIN_SUFFIX(b), JOIN_FIXED_ORDER();
• стратегії з'єднань і підзапитів: BNL / NO_BNL (керують і хеш-з'єднанням), BKA, SEMIJOIN(FIRSTMATCH), SUBQUERY(MATERIALIZATION), MERGE / NO_MERGE для похідних таблиць;
• виконання: MAX_EXECUTION_TIME(мс) (лише для SELECT), SET_VAR(змінна=значення) - змінна сесії лише на час запиту, RESOURCE_GROUP(...).

Найкорисніші на практиці:

-- обмежити час важкого звіту, щоб не тримав сервер
SELECT /*+ MAX_EXECUTION_TIME(5000) */ ...;

-- більший буфер сортування лише для цього запиту
SELECT /*+ SET_VAR(sort_buffer_size = 16777216) */ ... ORDER BY ...;

-- не матеріалізувати похідну таблицю, а злити з зовнішнім запитом
SELECT /*+ MERGE(t) */ * FROM (SELECT ...) AS t WHERE ...;

Переваги над FORCE INDEX:

• неправильна підказка не ламає запит - MySQL видає попередження й ігнорує її (а FORCE INDEX з неіснуючим індексом дає помилку);
• для інших СУБД це просто коментар - менше зав'язки на синтаксис MySQL;
• точкове керування без зміни змінних сесії.

У Laravel підказка має стояти одразу після SELECT. Найпростіше - через selectRaw першим виразом:

Report::query()
    ->selectRaw('/*+ MAX_EXECUTION_TIME(5000) */ *')
    ->where('year', 2026)
    ->get();

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

Як перевірити, що підказку застосовано: EXPLAIN і SHOW WARNINGS після нього - там видно переписаний запит з підказками, а нерозпізнані підказки дають попередження.

Застереження те саме, що й для будь-яких підказок: вони заморожують рішення. MAX_EXECUTION_TIME і SET_VAR - безпечні запобіжники; підказки індексів і порядку з'єднань - крайній засіб із коментарем, чому вони там.

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

Довгий час MySQL виконував з'єднання лише вкладеними циклами (nested loop): для кожного рядка першої таблиці шукаємо відповідні рядки в другій. З індексом на колонці з'єднання це дуже ефективно. Без індексу - катастрофа: повне сканування другої таблиці на кожен рядок першої (частково пом'якшене буфером block nested loop).

Hash join (MySQL 8.0.18+) працює інакше:

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

Кожна таблиця читається один раз - замість N × M порівнянь виходить N + M.

EXPLAIN FORMAT=TREE
SELECT * FROM orders o JOIN import_rows r ON r.external_id = o.external_id;

-> Inner hash join (r.external_id = o.external_id)
    -> Table scan on r
    -> Hash
        -> Table scan on o

Коли MySQL обирає hash join:

• з'єднання, для якого немає придатного індексу (з 8.0.20 hash join повністю замінив block nested loop);
• зазвичай за рівністю, але з 8.0.20 і для нерівностей, зовнішніх з'єднань, напівз'єднань (EXISTS, IN) і антиз'єднань (NOT EXISTS);
• коли є індекс, оптимізатор найчастіше лишається на вкладених циклах з пошуком в індексі.

Пам'ять: хеш-таблиця будується в межах join_buffer_size. Якщо не вміщується - дані розбиваються на частини у тимчасових файлах на диску, і з'єднання сповільнюється (але все одно зазвичай краще за повний перебір). Для великих аналітичних з'єднань має сенс збільшити join_buffer_size для запиту через /*+ SET_VAR(join_buffer_size = ...) */.

Керування: окремі підказки HASH_JOIN / NO_HASH_JOIN у сучасних версіях не діють. Використовуються BNL / NO_BNL (історично про block nested loop, тепер про hash join), або optimizer_switch: block_nested_loop=off.

Що це означає на практиці:

• hash join не заміна індексам для OLTP. Запит «замовлення користувача з товарами» має йти індексами: читати всю таблицю товарів заради 5 рядків - погано навіть одним проходом;
• для разових і аналітичних запитів (звірка імпорту з існуючими даними, звіти на репліці) hash join робить прийнятними запити без спеціальних індексів;
• побачивши hash join у плані частого запиту, варто перевірити, чи не бракує індексу на колонці з'єднання. Часта причина - різні типи чи кодування колонок, через які індекс непридатний.

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

Для деяких запитів MySQL створює внутрішню тимчасову таблицю - проміжне сховище результату. В EXPLAIN це Using temporary в Extra, у TREE-форматі - вузли на кшталт Aggregate using temporary table чи Materialize.

Коли вона з'являється:

• GROUP BY, який не можна виконати по порядку індексу;
• GROUP BY і ORDER BY за різними колонками (згрупувати, потім пересортувати);
• DISTINCT разом із ORDER BY;
• UNION (з дедуплікацією; UNION ALL у більшості випадків обходиться без неї);
• матеріалізація похідних таблиць, CTE, підзапитів;
• віконні функції;
• багатотабличний UPDATE, що читає з таблиці, яку змінює.

Де вона живе. За замовчуванням внутрішні тимчасові таблиці в пам'яті створює рушій TempTable. Ліміт - temptable_max_ram (у MySQL 8.4 за замовчуванням 3% оперативної пам'яті в межах 1-4 ГБ) на весь сервер, а не на запит. Коли він вичерпаний, дані йдуть на диск - у внутрішні таблиці InnoDB.

Також на окрему таблицю діє tmp_table_size - перевищення переводить на диск саме її.

Як побачити, що тимчасові таблиці йдуть на диск:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_tables       - усього внутрішніх тимчасових таблиць
-- Created_tmp_disk_tables  - з них на диску

SELECT * FROM sys.statements_with_temp_tables
ORDER BY disk_tmp_tables DESC LIMIT 10;   -- які запити винні

Зростання частки дискових таблиць - сигнал розібратися з конкретними запитами.

Як позбутися тимчасової таблиці (краще, ніж збільшувати ліміти):

• індекс під GROUP BY: якщо групування йде за колонками індексу після рівностей з WHERE, MySQL групує на льоту в порядку індексу;
• однакові GROUP BY і ORDER BY: від MySQL 8.0 GROUP BY не сортує неявно, тож ORDER BY за тими самими колонками дешевий;
• менше колонок у проміжному результаті: SELECT * з TEXT-полями роздуває тимчасову таблицю;
• UNION ALL замість UNION, коли дублікати неможливі чи не важливі;
• агрегувати до з'єднання: спершу згрупувати велику таблицю в похідній таблиці, потім з'єднати з довідниками.

Коли збільшувати ліміти: якщо аналітичні запити на репліці регулярно йдуть на диск і переписати їх не вдається. Підвищувати варто разом із контролем загальної пам'яті: ліміт TempTable глобальний, а tmp_table_size може застосовуватися до кожної з таблиць одночасно в багатьох сесіях.

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

EXPLAIN показує, який план обрано. Optimizer trace показує, чому: які способи доступу розглядалися, у скільки їх оцінено, які перетворення запиту застосовано й чому переміг саме цей варіант.

Як отримати трасування:

SET SESSION optimizer_trace = 'enabled=on';
SET SESSION optimizer_trace_max_mem_size = 1048576;

SELECT * FROM orders WHERE user_id = 7 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

SELECT trace, missing_bytes_beyond_max_mem_size
FROM information_schema.optimizer_trace\G

SET SESSION optimizer_trace = 'enabled=off';

Трасування - великий JSON. Якщо missing_bytes_beyond_max_mem_size більше нуля, вивід обрізано, і ліміт пам'яті треба збільшити.

Що в ньому шукати:

• join_preparation - як запит переписано: розкриття подань, перетворення підзапитів у напівз'єднання;
• condition_processing - спрощення умов, підстановка констант;
• rows_estimation → range_analysis - для кожного індексу: чи придатний ("usable": false з причиною), оцінка рядків (rows), вартість (cost), чи робилися index dives;
• considered_execution_plans - порядки з'єднань і їхня вартість;
• reconsidering_access_paths_for_index_ordering - чи вирішив оптимізатор перейти на інший індекс заради ORDER BY ... LIMIT. Це часте джерело «дивних» планів: запит з LIMIT раптом сканує індекс сортування замість селективного індексу з WHERE.

Типові висновки з трасування:

• оцінка рядків хибна (індекс оцінено в 50 рядків, а реально їх 500 000) - проблема статистики: ANALYZE TABLE, більше сторінок для вибірки, гістограми;
• індекс непридатний через тип колонки, кодування чи функцію в умові - видно прямо в range_analysis;
• вартість двох планів майже однакова - план «стрибає» між ними при зміні даних, і варто дати оптимізатору однозначно кращий індекс.

Обмеження й обережність:

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

Порядок діагностики повільного запиту: EXPLAIN → EXPLAIN ANALYZE (де план розходиться з реальністю) → optimizer trace (чому оптимізатор помилився) → виправлення статистики, індексу чи запиту.

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

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

Статистика індексів InnoDB:

• постійна (innodb_stats_persistent = ON, за замовчуванням) - зберігається в mysql.innodb_table_stats і mysql.innodb_index_stats, переживає перезапуск, тож плани стабільні;
• збирається вибірково: читається innodb_stats_persistent_sample_pages (20) випадкових сторінок індексу, а не весь індекс. Для великих таблиць з нерівномірними даними цього може бути замало;
• автоматично перераховується (innodb_stats_auto_recalc), коли змінилося понад 10% рядків, - у фоні, з невеликою затримкою.

Коли статистика підводить:

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

Що робити:

ANALYZE TABLE orders;   -- перерахувати статистику зараз

-- для конкретної великої таблиці - більше сторінок вибірки
ALTER TABLE orders STATS_SAMPLE_PAGES = 200;

ANALYZE TABLE для InnoDB швидкий (та сама вибірка сторінок), але викликає неявний коміт, тож усередині транзакції застосунку його не запускають.

Гістограми (MySQL 8.0+) описують розподіл значень колонки:

ANALYZE TABLE orders UPDATE HISTOGRAM ON status, country WITH 64 BUCKETS AUTO UPDATE;

SELECT column_name, JSON_EXTRACT(histogram, '$."histogram-type"')
FROM information_schema.column_statistics WHERE table_name = 'orders';

Навіщо вони, якщо є статистика індексів. Для колонок з індексом оптимізатор оцінює рядки через index dives. Для колонок без індексу він знає лише кількість рядків і бере грубі припущення. Гістограма каже, що status = 'refunded' - це 0,1% таблиці, а status = 'paid' - 90%. Це впливає на порядок з'єднань і вибір способу доступу до інших таблиць.

Особливості гістограм:

• корисні для колонок без індексу, з нерівномірним розподілом, що з'являються в умовах і з'єднаннях;
• не створюються для колонок з одноколонковим унікальним індексом;
• за замовчуванням оновлюються лише вручну. У MySQL 8.4 з'явилася опція AUTO UPDATE - тоді гістограму оновлює і ANALYZE TABLE, і фоновий перерахунок статистики;
• побудова гістограми читає таблицю (з вибіркою в межах histogram_generation_max_mem_size), тож на великих таблицях її варто запускати поза піком.

Добра практика: після масового імпорту чи міграції даних запускати ANALYZE TABLE для зачеплених таблиць - це дешево і прибирає цілий клас «раптом повільних» запитів.

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

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

1. Групувати рядки в один INSERT.

INSERT INTO products (sku, name, price) VALUES
    ('A-1', 'Товар 1', 100.00),
    ('A-2', 'Товар 2', 150.00),
    ...;   -- сотні-тисячі рядків на запит

У Laravel - DB::table('products')->insert($chunk) частинами по 500-2000 рядків. Розмір запиту обмежений max_allowed_packet, а кількість параметрів у PDO - теж не безмежна.

2. Комітити пачками, а не кожен рядок.

foreach (array_chunk($rows, 1000) as $chunk) {
    DB::transaction(fn () => DB::table('products')->insert($chunk));
}

Але й одна гігантська транзакція на мільйони рядків погана: величезний undo-журнал, довгий відкат при помилці, відставання реплік. Транзакції по кілька тисяч рядків - баланс.

3. LOAD DATA - найшвидший спосіб для файлу:

LOAD DATA LOCAL INFILE '/tmp/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
(sku, name, price);

LOCAL вимагає дозволу на обох боках (local_infile), що з міркувань безпеки часто вимкнено.

4. Вставляти в порядку первинного ключа. Тоді рядки дописуються в кінець кластерного індексу без розщеплення сторінок. Відсортований файл вантажиться помітно швидше за перемішаний.

5. Тимчасово вимкнути перевірки в сесії (лише для довірених даних):

SET unique_checks = 0;        -- відкладені перевірки вторинних унікальних індексів
SET foreign_key_checks = 0;   -- без перевірок зовнішніх ключів
-- завантаження
SET unique_checks = 1;
SET foreign_key_checks = 1;

Відповідальність за коректність даних переходить на вас: дублікати чи «осиротілі» зовнішні ключі після цього ніхто не впіймає.

6. Вторинні індекси - після завантаження. Для порожньої таблиці часто швидше завантажити дані лише з первинним ключем, а вторинні індекси створити потім: InnoDB будує індекс сортуванням, що ефективніше за мільйони окремих вставок у дерево.

7. Що ще впливає:

• innodb_autoinc_lock_mode = 2 (за замовчуванням у MySQL 8) - паралельні вставки не блокують одна одну на автоінкременті;
• достатній буферний пул і журнал повтору (innodb_redo_log_capacity);
• для тимчасового імпорту на окремому інстансі можна послабити innodb_flush_log_at_trx_commit - але ніколи на основній базі з живими даними;
• після завантаження - ANALYZE TABLE, щоб оптимізатор знав про нові дані.

Не забувати про репліки: мільйонний імпорт генерує стільки ж подій у бінарному журналі, і репліки відстануть. Пауза між пачками дає їм наздогнати.

Докладніше в документації: Масове завантаження даних в InnoDB

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

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

-- на джерелі
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET PERSIST rpl_semi_sync_source_enabled = ON;
SET PERSIST rpl_semi_sync_source_timeout = 1000;   -- мс

-- на репліці
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET PERSIST rpl_semi_sync_replica_enabled = ON;

Точка очікування rpl_semi_sync_source_wait_point:

• AFTER_SYNC (за замовчуванням, «без втрат») - джерело записує транзакцію в бінарний журнал, чекає підтвердження репліки, і лише потім комітить у рушії. Інші клієнти не побачать транзакцію на джерелі раніше, ніж вона буде на репліці;
• AFTER_COMMIT - коміт у рушії, потім очікування. Інші сесії можуть побачити дані, які потім пропадуть при перемиканні.

Головне застереження - тайм-аут. Якщо репліки не відповідають довше за rpl_semi_sync_source_timeout (за замовчуванням 10 секунд), джерело тихо переходить в асинхронний режим і продовжує працювати. Гарантія зникає саме тоді, коли щось пішло не так. Це треба моніторити:

SHOW GLOBAL STATUS LIKE 'Rpl_semi_sync_source_status';   -- ON / OFF
SHOW GLOBAL STATUS LIKE 'Rpl_semi_sync_source_no_tx';    -- коміти без підтвердження

Ціна: кожен коміт чекає на мережевий обмін з реплікою. У межах одного дата-центру - долі мілісекунди; між регіонами - десятки мілісекунд на кожну транзакцію.

Налаштування для надійності:

• щонайменше дві напівсинхронні репліки, щоб відмова однієї не перемикала джерело в асинхронний режим;
• rpl_semi_sync_source_wait_for_replica_count - скільки підтверджень чекати;
• автоматичне перемикання (Orchestrator, MySQL Router з InnoDB Cluster) має обирати як нове джерело саме репліку, що має всі підтверджені транзакції.

Від чого не захищає: від логічних помилок (DELETE без WHERE теж реплікується), і не робить читання з репліки свіжим - транзакцію отримано, але ще не обов'язково застосовано. Для справді синхронної реплікації з консенсусом - Group Replication.

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

Звичайна репліка застосовує зміни майже миттєво - разом із помилками. DROP TABLE orders чи UPDATE users SET email = ... без WHERE за мілісекунди стає катастрофою і на всіх репліках.

Відкладена репліка навмисно застосовує зміни із затримкою:

STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600;   -- секунди
START REPLICA;

Репліка отримує події одразу (вони зберігаються в її журналі ретрансляції), але застосовує кожну транзакцію не раніше ніж через годину після коміту на джерелі.

Сценарій порятунку. О 14:32 хтось виконав DROP TABLE orders. Відкладена репліка застосувала дані лише до 13:32.

1. Негайно зупинити застосування на відкладеній репліці, поки вона не дійшла до 14:32:

STOP REPLICA SQL_THREAD;

2. Знайти в бінарному журналі позицію чи GTID шкідливої транзакції.
3. Довести репліку до моменту перед аварією:

START REPLICA SQL_THREAD UNTIL SQL_BEFORE_GTIDS = '3e11fa47-...:98765';
-- або UNTIL SOURCE_LOG_FILE = 'binlog.000057', SOURCE_LOG_POS = 48211337

4. Тепер репліка містить стан бази за мить до DROP. Далі:
   • вивантажити звідти таблицю orders і повернути на джерело (а зміни, що відбулися після 14:32, у видаленій таблиці не існують - тож втрачено лише нові замовлення за час аварії);
   • або, якщо пошкоджено багато, підвищити відкладену репліку до джерела, пропустивши шкідливу транзакцію.

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

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

Що треба знати:

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

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

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

Clone plugin (MySQL 8.0.17+) копіює фізичні файли даних InnoDB з працюючого сервера-донора на сервер-одержувач прямо через протокол MySQL, без зупинки донора.

Підготовка:

-- на обох серверах
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

-- на донорі: користувач для клонування
CREATE USER 'clone_user'@'10.0.0.%' IDENTIFIED BY '...';
GRANT BACKUP_ADMIN ON *.* TO 'clone_user'@'10.0.0.%';

-- на одержувачі: хто клонує і звідки можна
GRANT CLONE_ADMIN ON *.* TO 'admin'@'localhost';
SET GLOBAL clone_valid_donor_list = '10.0.0.10:3306';

Клонування (на одержувачі):

CLONE INSTANCE FROM 'clone_user'@'10.0.0.10':3306 IDENTIFIED BY '...';

Що відбувається:

1. дані одержувача видаляються - він стає точною копією донора;
2. файли передаються потоком, паралельно;
3. зміни, що відбуваються на донорі під час копіювання, теж переносяться - результат узгоджений;
4. одержувач автоматично перезапускається (якщо ним керує systemd чи інший супервізор).

Прогрес видно в performance_schema.clone_progress, підсумок - у clone_status.

Налаштування реплікації після клонування: клон зберігає координати - позицію бінарного журналу й множину GTID. З GTID досить:

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

Обмеження:

• однакова версія донора й одержувача (у межах однієї серії допустимі різні патч-версії), та сама платформа й операційна система;
• копіюються лише дані InnoDB; таблиці інших рушіїв створюються порожніми;
• клонування навантажує мережу й диск донора - є налаштування обмеження швидкості (clone_max_data_bandwidth, clone_max_network_bandwidth);
• конфігураційні файли (my.cnf) не переносяться;
• DDL на донорі під час клонування за замовчуванням дозволено (clone_block_ddl = OFF), але чи потрапить його результат у клон, залежить від того, чи завершився він до знімка. Для передбачуваності на час клонування схему краще не змінювати.

Також використовується: InnoDB Cluster і Group Replication самі застосовують clone для додавання нового вузла, коли журналів недостатньо, щоб «наздогнати» кластер.

Для бекапу clone теж придатний (клонування в локальний каталог: CLONE LOCAL DATA DIRECTORY = '/backups/2026-10-04'), але без інкрементних бекапів і з тими ж обмеженнями.

Докладніше в документації: Плагін clone

MySQL 8.4 - перша LTS-версія в новій моделі випусків: LTS отримують лише виправлення протягом довгого часу, а нові можливості виходять у проміжних Innovation-версіях (9.x). Оновлення 8.0 → 8.4 підтримується напряму. Відкат до 8.0 на тих самих файлах не підтримується - лише з бекапу.

Що ламається найчастіше:

1. mysql_native_password вимкнено за замовчуванням. Облікові записи зі старим плагіном після оновлення не увійдуть. Перед оновленням:

SELECT user, host FROM mysql.user WHERE plugin = 'mysql_native_password';
ALTER USER 'app'@'%' IDENTIFIED WITH caching_sha2_password BY '...';

Тимчасово плагін можна ввімкнути (mysql_native_password = ON), але в MySQL 9 його видалено.

2. Видалено старий синтаксис реплікації. CHANGE MASTER TO, START SLAVE, SHOW SLAVE STATUS, SHOW MASTER STATUS, RESET MASTER і опції MASTER_* більше не існують. Треба оновити скрипти, системи моніторингу, Ansible-ролі, інструменти перемикання:

3. Видалено утиліту mysqlpump - замінити на mysqldump чи утиліти MySQL Shell.

4. Змінено значення за замовчуванням InnoDB - продуктивність може змінитися навіть без змін конфігурації:

• innodb_adaptive_hash_index: ON → OFF;
• innodb_change_buffering: all → none;
• innodb_io_capacity: 200 → 10000;
• innodb_log_buffer_size: 16 МБ → 64 МБ;
• temptable_max_ram: 1 ГБ → 3% пам'яті (у межах 1-4 ГБ).

Ці зміни відображають сучасні SSD, але для навантажень, що виграють від адаптивного хеш-індексу, варто порівняти продуктивність до і після.

5. Застарілі й видалені змінні в my.cnf. Сервер не стартує з невідомою змінною. Перевірити конфігурацію на тестовому інстансі.

Процес оновлення:

1. перевірка сумісності - util.checkForServerUpgrade() з MySQL Shell: знаходить застарілі плагіни, синтаксис, проблеми схеми;
2. оновлення на копії продакшену, прогін тестів застосунку і порівняння планів найважливіших запитів;
3. на продакшені - спершу репліки (новіша репліка може реплікувати зі старішого джерела, навпаки - ні), потім перемикання джерела на оновлену репліку;
4. бекап безпосередньо перед оновленням і план відкату (повернення на стару репліку).

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

Докладніше в документації: Що нового в MySQL 8.4

Значення MySQL за замовчуванням розраховані на те, що сервер ділить машину з іншими програмами: буферний пул 128 МБ, журнал повтору 100 МБ. Для виділеного сервера бази це в рази менше, ніж треба.

innodb_dedicated_server = ON (у файлі конфігурації, не на льоту) - InnoDB сам визначає ключові параметри за ресурсами машини. У MySQL 8.4 це два параметри:

innodb_buffer_pool_size:

innodb_redo_log_capacity = (кількість логічних процесорів / 2) ГБ, максимум 16 ГБ.

У MySQL 8.0 ця опція ще й встановлювала innodb_flush_method, у 8.4 - вже ні. Якщо якийсь із параметрів явно задано в конфігурації, явне значення має пріоритет.

Пастка контейнерів. Опція бачить пам'ять, яку повідомляє система. У контейнері з лімітом пам'яті (Docker, Kubernetes) MySQL може побачити пам'ять хоста, і 75% від неї перевищить ліміт контейнера - OOM-kill. У контейнерах параметри краще задавати явно.

Що ще варто переглянути на виділеному сервері:

• max_connections - за реальною кількістю процесів застосунку з запасом, не «про всяк випадок» тисячі;
• innodb_io_capacity / innodb_io_capacity_max - під можливості дисків (у 8.4 типове значення вже розраховане на SSD);
• innodb_flush_log_at_trx_commit = 1 і sync_binlog = 1 - лишити такими на основному сервері: це гарантія стійкості;
• binlog_expire_logs_seconds - щоб покривав інтервал між бекапами, але не з'їв диск;
• long_query_time і slow_query_log - увімкнути з порогом, що має сенс для застосунку;
• max_execution_time - запобіжник для SELECT, якщо застосунок не ставить власних тайм-аутів;
• table_open_cache, table_definition_cache - якщо таблиць тисячі (багатоорендні схеми);
• пам'ять на з'єднання (sort_buffer_size, join_buffer_size) - не збільшувати глобально без потреби: вони множаться на кількість активних з'єднань.

Як перевіряти ефект. Змінювати по одному параметру й вимірювати на реалістичному навантаженні. Популярні «оптимізовані конфіги» з інтернету часто містять параметри для старих версій, які на сучасному MySQL шкодять або вже видалені.

Залишок пам'яті після буферного пулу потрібен ОС (файловий кеш для бінарних журналів), з'єднанням, тимчасовим таблицям (TempTable), Performance Schema. Якщо на тому ж сервері ще й PHP чи Redis, innodb_dedicated_server вмикати не можна - він вважає, що вся пам'ять належить MySQL.

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

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

Сторона сервера. MySQL 8 при першому старті сам генерує самопідписані сертифікати й вмикає TLS за замовчуванням: клієнт, що підтримує TLS, використає його. Але:

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

Заборонити незашифровані підключення:

SET PERSIST require_secure_transport = ON;   -- для всіх, крім сокета

-- або для окремого облікового запису
ALTER USER 'app'@'%' REQUIRE SSL;
ALTER USER 'app'@'%' REQUIRE X509;           -- ще й клієнтський сертифікат

Сторона Laravel. Підключення налаштовується через опції PDO в config/database.php:

'options' => extension_loaded('pdo_mysql') ? array_filter([
    (PHP_VERSION_ID >= 80500 ? Pdo\Mysql::ATTR_SSL_CA : PDO::MYSQL_ATTR_SSL_CA) => env('MYSQL_ATTR_SSL_CA'),
    (PHP_VERSION_ID >= 80500 ? Pdo\Mysql::ATTR_SSL_VERIFY_SERVER_CERT : PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT) => true,
]) : [],

• ATTR_SSL_CA - сертифікат центру сертифікації, яким підписано сертифікат сервера. У хмарних провайдерів (RDS, Cloud SQL, PlanetScale) це їхній публічний CA-бандл;
• ATTR_SSL_VERIFY_SERVER_CERT = true - перевіряти сертифікат сервера. Без цього TLS є, але підключитися можна й до підставного сервера;
• для взаємної автентифікації - ATTR_SSL_CERT і ATTR_SSL_KEY (клієнтські сертифікат і ключ).

У PHP 8.5 константи PDO::MYSQL_ATTR_* оголошено застарілими на користь Pdo\Mysql::ATTR_* - звідси перевірка версії в типовому конфігу Laravel.

Як перевірити, що з'єднання справді зашифроване:

SHOW SESSION STATUS LIKE 'Ssl_cipher';   -- непорожнє значення = TLS
SHOW SESSION STATUS LIKE 'Ssl_version';  -- TLSv1.3 / TLSv1.2

З Laravel - тим самим запитом: DB::select("SHOW SESSION STATUS LIKE 'Ssl_cipher'"). А які з усіх поточних підключень ідуть без шифрування, показує performance_schema.status_by_thread зі змінною Ssl_cipher для кожного потоку.

Для консольного клієнта аналогічний рівень дає --ssl-mode=VERIFY_IDENTITY (перевіряє і ланцюжок, і що ім'я хоста збігається з сертифікатом), а --ssl-mode=REQUIRED лише шифрує без перевірки.

Ротація сертифікатів у MySQL 8 можлива без перезапуску: замінити файли й виконати ALTER INSTANCE RELOAD TLS.

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