MySQL: питання на співбесіді рівня Senior

CTE (Common Table Expression) - іменований підзапит у блоці WITH, до якого основний запит звертається як до таблиці. Головна користь - читабельність: складний запит розбивається на кроки з іменами.

WITH paid AS (
    SELECT user_id, SUM(total) AS spent
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
)
SELECT u.name, p.spent
FROM users u
JOIN paid p ON p.user_id = u.id
WHERE p.spent > 10000;

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

WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM categories WHERE id = 42          -- початок
    UNION ALL
    SELECT c.id, c.parent_id, c.name, t.depth + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id      -- крок
)
SELECT * FROM tree;

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

• У PostgreSQL до версії 12 CTE завжди матеріалізувався й був «бар'єром оптимізації». З 12-ї простий CTE вбудовується в запит, а поведінку можна задати явно: AS MATERIALIZED / AS NOT MATERIALIZED.
• У рекурсії потрібна умова зупинки, інакше цикли в даних дадуть нескінченний запит. Захист - лічильник глибини чи масив пройдених вузлів.
• MySQL підтримує CTE з версії 8.0.

Докладніше в документації: Запити WITH (CTE)

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

SELECT
    created_at::date AS day,
    total,
    SUM(total) OVER (ORDER BY created_at) AS running_total,
    RANK()     OVER (ORDER BY total DESC) AS place,
    total - LAG(total) OVER (ORDER BY created_at) AS diff_from_previous
FROM orders;

Основні функції:

• ROW_NUMBER() - порядковий номер без повторів.
• RANK() - однакові значення ділять місце, далі пропуск (1, 2, 2, 4). DENSE_RANK() - без пропусків (1, 2, 2, 3).
• LAG() / LEAD() - значення з попереднього чи наступного рядка.
• Будь-який агрегат (SUM, AVG, COUNT) з OVER.

PARTITION BY ділить вікно на групи - наприклад, рейтинг усередині кожної категорії: RANK() OVER (PARTITION BY category_id ORDER BY sales DESC).

Рамка вікна - пастка для старших: з ORDER BY агрегат за замовчуванням рахує від початку до поточного рядка з урахуванням рівних значень (RANGE). Рядки з однаковим created_at отримають однаковий накопичений підсумок. Для «рядок за рядком» потрібно явно: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Так само рахують і ковзне середнє: AVG(total) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

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

JSON у реляційній базі доречний, коли дані:

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

Окрема таблиця краща, коли:

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

PostgreSQL: json чи jsonb. jsonb зберігається в розібраному бінарному вигляді: трохи повільніший запис, зате швидкі оператори (->, ->>, @>, ?) і підтримка GIN-індексів. json зберігає текст як є (з пробілами й порядком ключів) і майже ніколи не потрібен.

CREATE INDEX products_attrs_gin ON products USING gin (attributes);
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

MySQL: тип JSON з валідацією; для індексу за полем створюють згенеровану колонку (GENERATED ALWAYS AS (attributes->>'$.color')) або функціональний індекс (8.0.13+).

Червоні прапорці: JSON-колонку, з якої постійно витягують одне поле в WHERE, часто варто перетворити на звичайну колонку. А зберігання в JSON списку ID інших записів - це втрачений зовнішній ключ.

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

Collation - правила, за якими база порівнює й сортує рядки: чи «а» дорівнює «А», чи «е» дорівнює «є», де в абетці «ґ». Він впливає на ORDER BY, =, LIKE, GROUP BY і унікальні індекси.

MySQL:

• Кодування має бути utf8mb4. Старий utf8 (тепер utf8mb3) зберігає лише до 3 байтів на символ і не вміщує емодзі.
• Collation за замовчуванням у MySQL 8 - utf8mb4_0900_ai_ci: нечутливий до регістру й діакритики (ai - accent-insensitive, ci - case-insensitive). Звідси сюрпризи: WHERE email = 'IVAN@x.com' знаходить ivan@x.com, а унікальний індекс не пустить і «Ivan», і «ivan».
• Для точних порівнянь (токени, хеші) - _bin або utf8mb4_0900_as_cs.

PostgreSQL:

• Порівняння за замовчуванням чутливе до регістру. Для пошуку без урахування регістру - ILIKE, lower(email) з функціональним індексом, розширення citext або недетерміністичний ICU-collation.
• Collation бази залежить від локалі ОС (glibc). Оновлення glibc може змінити порядок сортування і зіпсувати наявні індекси за текстовими колонками - їх доводиться перебудовувати (REINDEX). Тому все частіше використовують ICU-collation з контрольованою версією.

Українська абетка: щоб «ґ» стояла після «г», а «і», «ї» - на своїх місцях, потрібен collation з українською локаллю (наприклад, ICU uk-x-icu у PostgreSQL). Якщо база такого не має, сортують у застосунку через Collator з розширення intl.

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

Проблема: групування за датою повертає лише дні, у які були дані. Для графіка «замовлень по днях» дні без замовлень просто зникають, і лінія графіка бреше.

SELECT DATE(created_at) AS day, COUNT(*) FROM orders GROUP BY day;
-- 2026-10-01: 15, 2026-10-03: 9   (2 жовтня пропущено, хоча там 0)

Рішення - згенерувати повний календар і приєднати до нього дані:

WITH RECURSIVE calendar AS (
    SELECT DATE('2026-10-01') AS day
    UNION ALL
    SELECT day + INTERVAL 1 DAY
    FROM calendar
    WHERE day < '2026-10-31'
)
SELECT c.day, COUNT(o.id) AS orders
FROM calendar c
LEFT JOIN orders o
    ON o.created_at >= c.day
   AND o.created_at < c.day + INTERVAL 1 DAY
GROUP BY c.day
ORDER BY c.day;

Ключові деталі:

• LEFT JOIN від календаря, а не від даних - щоб дні без замовлень лишилися.
• COUNT(o.id), а не COUNT(*) - COUNT(*) порахує рядок календаря й дасть 1 замість 0.
• Умова з'єднання діапазоном, а не DATE(o.created_at) = c.day - так працює індекс на created_at.
• Ліміт рекурсії: cte_max_recursion_depth за замовчуванням 1000. Для календаря на кілька років його збільшують для сесії.

Часові пояси: «день» залежить від поясу. Якщо created_at зберігається в UTC, а звіт - за київським часом, межі днів треба зсунути (CONVERT_TZ або обчислені межі), інакше замовлення о 01:00 за Києвом потраплять у попередній день.

Альтернативи:

• Постійна таблиця-календар з датами на роки вперед - корисна, якщо звітів багато, і в неї можна додати атрибути (вихідні, свята, номер тижня).
• Заповнення пропусків у застосунку - згенерувати діапазон дат у PHP (CarbonPeriod) і злити з результатом запиту.

У PostgreSQL для цього є generate_series().

Докладніше в документації: WITH (CTE)

Рамка вікна визначає, які рядки враховує агрегат для поточного рядка. Є два способи її задати, і для часових даних різниця принципова.

ROWS - за кількістю рядків:

SELECT day, revenue,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_rows
FROM daily_revenue;

Бере 7 рядків. Якщо в даних є пропущені дні, «7 рядків» охоплять більше ніж 7 днів - і середнє буде неправильним.

RANGE з інтервалом - за значенням колонки сортування:

SELECT day, revenue,
       AVG(revenue) OVER (
           ORDER BY day
           RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
       ) AS avg_7_days
FROM daily_revenue;

Бере всі рядки, у яких day в межах 6 днів до поточного - незалежно від того, скільки рядків туди потрапило. Пропущені дні не «розтягують» вікно. MySQL підтримує RANGE з INTERVAL для дат і часу.

Що ще враховувати:

• Пропущені дні в самих даних: RANGE правильно визначить межі, але AVG порахує середнє лише по наявних днях. Якщо день без продажів має рахуватися як 0 - спершу доповнити дані календарем (рекурсивний CTE).
• Початок ряду: для перших днів у вікні менше 7 значень. Якщо це спотворює графік, - показувати середнє лише з 7-го дня (COUNT(*) OVER (...) для перевірки кількості).
• Однакові значення сортування: з RANGE рядки з однаковим day потрапляють у рамку разом (вони «рівні»). З ROWS - ні. Для щоденних агрегатів це не проблема, для сирих подій з однаковими мітками часу - важливо.
• Іменовані вікна зменшують повтори, коли кілька функцій мають однакове вікно:

SELECT day,
       AVG(revenue) OVER w AS avg_7,
       SUM(revenue) OVER w AS sum_7
FROM daily_revenue
WINDOW w AS (ORDER BY day RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW);

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

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

У старих версіях MySQL підзапит у WHERE ... IN (SELECT ...) часто виконувався як корельований - для кожного рядка зовнішньої таблиці. Звідси міф «підзапити в MySQL повільні, завжди пишіть JOIN». Сучасний оптимізатор перетворює їх на ефективні плани.

Semi-join - для IN і EXISTS: «рядки зовнішньої таблиці, для яких є хоч один збіг». На відміну від звичайного JOIN, не розмножує рядки при кількох збігах.

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Стратегії semi-join, з яких оптимізатор обирає за вартістю:

• Table pullout - підзапит перетворюється на звичайний JOIN, якщо збіг гарантовано один (наприклад, за первинним ключем).
• FirstMatch - при обході зупинитися на першому збігу.
• LooseScan - пройти індекс підзапиту, беручи лише перше значення кожної групи.
• Materialization - обчислити підзапит один раз у тимчасову таблицю з індексом і шукати в ній.
• Duplicate Weedout - виконати як звичайний JOIN, а дублікати прибрати потім.

Antijoin (MySQL 8.0.17+) - для NOT IN, NOT EXISTS: «рядки, для яких збігів немає». Раніше такі запити часто виконувалися значно гірше.

Як побачити, що сталося:

EXPLAIN FORMAT=TREE
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

У плані видно Semi-join, Materialize чи Nested loop semijoin. Після EXPLAIN (звичайного) SHOW WARNINGS показує, на який запит оптимізатор переписав оригінал.

Коли оптимізація не спрацьовує:

• підзапит з LIMIT, UNION, агрегатами в певних формах;
• підзапит у UPDATE/DELETE з тієї ж таблиці;
• NOT IN з колонкою, що може бути NULL (семантика NULL заважає antijoin - і результат може бути порожнім).

Керування - підказки оптимізатору (/*+ SEMIJOIN(FIRSTMATCH) */, NO_SEMIJOIN) і optimizer_switch. Але це крайній захід: спершу перевірити індекси й статистику (ANALYZE TABLE).

Докладніше в документації: Semi-join і antijoin

У MySQL (як і в PostgreSQL) немає оператора PIVOT. Рядки перетворюють на колонки умовною агрегацією: агрегат по групі, де враховуються лише рядки, що відповідають умові колонки.

-- продажі: рядок на кожну пару (товар, місяць)
SELECT product_id,
       SUM(CASE WHEN MONTH(created_at) = 1 THEN total ELSE 0 END) AS jan,
       SUM(CASE WHEN MONTH(created_at) = 2 THEN total ELSE 0 END) AS feb,
       SUM(CASE WHEN MONTH(created_at) = 3 THEN total ELSE 0 END) AS mar
FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-04-01'
GROUP BY product_id;

Короткий запис для підрахунків - у MySQL логічний вираз дорівнює 1 чи 0:

SELECT DATE(created_at) AS day,
       SUM(status = 'paid') AS paid,
       SUM(status = 'cancelled') AS cancelled,
       SUM(status = 'refunded') AS refunded
FROM orders
GROUP BY day;

У PostgreSQL для цього є COUNT(*) FILTER (WHERE status = 'paid').

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

Варіанти для динамічних колонок:

• Побудувати запит у застосунку: отримати список значень, згенерувати вирази SUM(CASE ...) і виконати.
• Підготовлений запит у збереженій процедурі (PREPARE / EXECUTE зі зібраного рядка) - працює, але важко підтримувати й небезпечно, якщо значення приходять від користувача.
• Повернути «довгий» формат (рядок на кожну пару) і розвернути в таблицю на клієнті - часто найпростіше й найгнучкіше: бібліотеки графіків і таблиць саме цього й очікують.

Зворотна операція (unpivot) - колонки в рядки - робиться через UNION ALL по колонках або JSON_TABLE з масиву значень.

Обережно з NULL: ELSE 0 дає нуль там, де даних немає; без ELSE вийде NULL. Для звіту важливо вирішити, що з цього означає «нуль продажів», а що - «немає даних».

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

Невидимі колонки (MySQL 8.0.23+) не потрапляють у SELECT * і не потребують значення в INSERT без списку колонок. Явно названі - працюють як звичайні.

ALTER TABLE orders ADD COLUMN internal_note TEXT INVISIBLE;

SELECT * FROM orders;                 -- internal_note немає
SELECT id, internal_note FROM orders; -- є

Навіщо: додати колонку до таблиці, не зламавши старий код, що робить SELECT * чи INSERT INTO t VALUES (...) без переліку колонок. Колонку можна поступово впровадити, а потім зробити видимою.

Згенерований невидимий первинний ключ (GIPK) (MySQL 8.0.30+). Якщо увімкнено sql_generate_invisible_primary_key, таблиця, створена без первинного ключа, автоматично отримує невидиму колонку:

my_row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY

Чому таблиця без первинного ключа - проблема в MySQL:

• InnoDB однаково потрібен кластерний ключ. Без первинного ключа він бере перший унікальний NOT NULL індекс, а якщо такого немає - створює прихований 6-байтовий ідентифікатор. Але цей ідентифікатор спільний для всіх таких таблиць сервера, і його лічильник - точка конкуренції.
• Реплікація на основі рядків: щоб застосувати UPDATE чи DELETE на репліці, потрібно знайти рядок. Без ключа репліка шукає повним переглядом таблиці для кожного рядка - велике оновлення на primary перетворюється на години затримки реплікації.
• Group Replication і InnoDB Cluster взагалі вимагають первинного ключа на кожній таблиці.
• Багато інструментів (онлайн-зміна схеми, деякі CDC-конектори) не працюють з таблицями без ключа.

GIPK вирішує це автоматично, не змінюючи видимої структури таблиці для застосунку.

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

Докладніше в документації: Згенеровані невидимі первинні ключі

Колонку JSON у MySQL неможливо проіндексувати напряму. Індексують значення, витягнуті з документа.

1. Скалярне поле - функціональний індекс чи згенерована колонка:

-- функціональний індекс (MySQL 8.0.13+)
CREATE INDEX products_brand_idx ON products ((CAST(attributes ->> '$.brand' AS CHAR(100)) COLLATE utf8mb4_bin));

-- або віртуальна колонка з індексом - простіше в запитах
ALTER TABLE products
    ADD COLUMN brand VARCHAR(100) AS (attributes ->> '$.brand') VIRTUAL,
    ADD INDEX (brand);

->> повертає текст з типом LONGTEXT, тож у функціональному індексі потрібне явне CAST до типу з обмеженою довжиною. А вираз у запиті має дослівно збігатися з виразом індексу - тому віртуальна колонка надійніша: у запиті пишуть просто WHERE brand = ?.

2. Масив - багатозначний індекс (multi-valued index, MySQL 8.0.17+). Один рядок дає кілька записів в індексі - по одному на кожен елемент масиву:

-- products.attributes = {"tags": ["laravel", "php", "api"]}
CREATE INDEX products_tags_idx ON products ((CAST(attributes -> '$.tags' AS CHAR(50) ARRAY)));

SELECT * FROM products WHERE 'php' MEMBER OF (attributes -> '$.tags');
SELECT * FROM products WHERE JSON_CONTAINS(attributes -> '$.tags', '["php", "api"]');
SELECT * FROM products WHERE JSON_OVERLAPS(attributes -> '$.tags', '["vue", "react"]');

Індекс використовується лише цими трьома конструкціями: MEMBER OF, JSON_CONTAINS, JSON_OVERLAPS.

Обмеження багатозначних індексів:

• один багатозначний компонент на індекс;
• лише для простих масивів скалярних значень;
• не підтримуються ORDER BY за індексом і деякі типи;
• оновлення масиву оновлює всі його записи в індексі.

Порівняння з PostgreSQL: там GIN-індекс на весь jsonb прискорює пошук за будь-якими ключами без попереднього вибору полів. У MySQL треба заздалегідь знати, за якими полями шукатимуть.

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

Докладніше в документації: CREATE INDEX: багатозначні індекси

UUID. Текстове подання - 36 символів (CHAR(36)), тобто 36 байтів на значення, а в utf8mb4-колонці ще й з накладними витратами порівняння за collation. Бінарне - 16 байтів:

CREATE TABLE documents (
    id BINARY(16) PRIMARY KEY,
    title VARCHAR(255) NOT NULL
);

INSERT INTO documents (id, title) VALUES (UUID_TO_BIN(UUID(), 1), 'Договір');
SELECT BIN_TO_UUID(id, 1) AS id, title FROM documents;

Чому розмір первинного ключа в InnoDB важливий подвійно: кожен вторинний індекс містить копію первинного ключа. 36 байтів замість 16 (чи 8 для BIGINT) множаться на всі індекси таблиці.

Прапорець 1 (swap) у UUID_TO_BIN. MySQL-функція UUID() генерує UUID версії 1, де мітка часу розкидана по рядку так, що значення не зростають. Перестановка частин робить їх впорядкованими за часом - нові рядки дописуються в кінець кластерного індексу, а не в випадкові місця. Для UUIDv4 (повністю випадкових) прапорець не допомагає - для них краще генерувати впорядковані UUIDv7 у застосунку.

Незручність бінарних UUID: у консолі вони нечитабельні, і в кожному запиті потрібні перетворення. Laravel з HasUuids за замовчуванням використовує CHAR(36); бінарне зберігання потребує власного касту.

IP-адреси:

CREATE TABLE logins (
    ip VARBINARY(16) NOT NULL,     -- IPv4 (4 байти) і IPv6 (16 байтів)
    created_at DATETIME NOT NULL
);

INSERT INTO logins (ip, created_at) VALUES (INET6_ATON('2001:db8::1'), NOW());
SELECT INET6_NTOA(ip) FROM logins;

-- пошук у підмережі - діапазоном по бінарному значенню
SELECT * FROM logins
WHERE ip BETWEEN INET6_ATON('192.168.1.0') AND INET6_ATON('192.168.1.255');

INET6_ATON працює і з IPv4, і з IPv6. Старі INET_ATON / UNSIGNED INT - лише для IPv4.

PostgreSQL має для цього рідні типи: uuid (16 байтів, з UUIDv7 у PG18) і inet/cidr з операторами підмереж - перетворення там не потрібні.

Докладніше в документації: Інші функції: UUID_TO_BIN, INET6_ATON

InnoDB змінює сторінки даних у буферному пулі, а на диск скидає їх пізніше. Щоб закомічені зміни не пропали при збої, працює журнал повтору (redo log): перед комітом опис змін послідовно записується в журнал. Послідовний запис невеликих записів значно дешевший за запис випадкових сторінок по 16 КБ.

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

innodb_flush_log_at_trx_commit визначає, що відбувається під час COMMIT:

fsync - найдорожча частина коміту, тож 2 і 0 дають помітний виграш на дрібних транзакціях. Але це свідома відмова від стійкості. Прийнятно для реплік, що легко перестворити, чи для тимчасового масового імпорту - не для основної бази з грошима.

Друга половина стійкості - sync_binlog (за замовчуванням 1): fsync бінарного журналу на кожен коміт. Без нього після збою репліки можуть отримати транзакції, яких немає на джерелі, чи навпаки.

Подвійний запис (doublewrite buffer) захищає від «розірваних» сторінок: якщо живлення зникло посеред запису 16-КБ сторінки, на диску лишиться напівзаписана сторінка, яку журнал повтору не виправить. Тому InnoDB спершу пише сторінки в окрему область doublewrite, а потім на місце. Після збою пошкоджена сторінка відновлюється з копії.

Розмір журналу повтору (innodb_redo_log_capacity з MySQL 8.0.30, за замовчуванням 100 МБ). Замалий журнал змушує часто робити контрольні точки й агресивно скидати сторінки - запис стає ривками. Завеликий подовжує відновлення після збою. Орієнтир - щоб журнал вміщав щонайменше годину запису під піковим навантаженням.

Що варто знати: групування комітів (group commit) дає змогу кільком транзакціям поділити один fsync, тож налаштування 1 під паралельним навантаженням коштує менше, ніж здається на синтетичному тесті з одним клієнтом.

Докладніше в документації: Журнал повтору (redo log)

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

Purge - фонова очистка: коли жодна активна транзакція вже не може побачити стару версію, purge-потоки видаляють її з undo і остаточно прибирають рядки, позначені як видалені (DELETE в InnoDB лише позначає рядок).

Довжина історії (history list length) - кількість ще не очищених записів undo:

SHOW ENGINE INNODB STATUS\G
-- TRANSACTIONS: History list length 1234567

SELECT count FROM information_schema.innodb_metrics
WHERE name = 'trx_rseg_history_len';

Як одна транзакція шкодить усім. Purge не може видалити жодну версію, новішу за найстаріший відкритий знімок. Якщо транзакцію відкрито годину тому (навіть тільки для читання на REPEATABLE READ), за годину нагромаджуються всі старі версії всіх змінених рядків:

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

Типові винуватці:

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

Як знайти:

SELECT trx_id, trx_mysql_thread_id, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;

Що робити:

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

Розмір undo на диску: undo-табличні простори автоматично усікаються (innodb_undo_log_truncate = ON), коли перевищують innodb_max_undo_log_size (1 ГБ) і purge їх звільнив. Але доки довга транзакція жива, усікання неможливе.

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

ALTER TABLE у InnoDB може виконуватися трьома алгоритмами, і від алгоритму залежить, чи буде простій.

ALGORITHM=INSTANT - змінюються лише метадані, дані не чіпаються. Мілісекунди незалежно від розміру таблиці:

• додавання колонки (з MySQL 8.0.29 - у будь-яку позицію, раніше - лише в кінець);
• видалення колонки (8.0.29+);
• зміна значення за замовчуванням, перейменування колонки;
• розширення списку ENUM/SET у кінці.

ALGORITHM=INPLACE - таблицю перебудовують «на місці», без копіювання через рівень SQL. З LOCK=NONE паралельні читання й записи дозволені, зміни за час операції накопичуються в журналі й застосовуються наприкінці:

• створення вторинного індексу (без перебудови таблиці);
• OPTIMIZE TABLE, зміна ROW_FORMAT;
• додавання NOT NULL до колонки (з перебудовою).

ALGORITHM=COPY - нова таблиця, копіювання всіх рядків, перемикання. Записи заблоковано на весь час копіювання. Сюди потрапляє, зокрема, зміна типу колонки (INT → BIGINT, зміна кодування).

Головне правило - вказувати алгоритм явно:

ALTER TABLE orders ADD COLUMN note TEXT, ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX orders_status_idx (status), ALGORITHM=INPLACE, LOCK=NONE;

Якщо операцію не можна виконати так, MySQL поверне помилку замість того, щоб тихо перейти на COPY і заблокувати таблицю на годину. У Laravel-міграції таке пишуть через DB::statement().

Підводні камені навіть онлайн-операцій:

• блокування метаданих - на початку й наприкінці будь-який ALTER бере виключне MDL і може застрягти за довгою транзакцією, зупинивши трафік до таблиці. Короткий lock_wait_timeout у сесії міграції обов'язковий;
• журнал змін при INPLACE обмежений innodb_online_alter_log_max_size; при інтенсивному записі операція може впасти наприкінці;
• репліки виконують той самий ALTER після джерела і (за однопотокового застосування) відстають на весь його час;
• INSTANT має ліміт: кожне миттєве додавання чи видалення колонки створює нову версію рядка, а в MySQL 8.4 їх допускається 64. Далі - помилка, і таблицю доведеться перебудувати через INPLACE чи COPY.

Для найважчих випадків - зовнішні інструменти gh-ost чи pt-online-schema-change: вони створюють тіньову копію таблиці, поступово копіюють дані, наздоганяють зміни (через бінарний журнал чи тригери) і атомарно підміняють таблицю. Копіювання можна пригальмувати під навантаженням.

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

Коли транзакція стає в чергу за блокуванням, 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 і скидати пакетами;
• коротші транзакції, що тримають гарячий рядок мілісекунди;
• черга, яка серіалізує оновлення одного ресурсу.

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