Junior: питання на співбесіді з теми «InnoDB і транзакції»

MySQL зберігає дані через рушії зберігання (storage engines). Один сервер може мати таблиці на різних рушіях. Від MySQL 5.5 рушій за замовчуванням - InnoDB, а MyISAM лишився спадщиною.

Що дає InnoDB:

• Транзакції (ACID). COMMIT, ROLLBACK, відновлення після збою. У MyISAM транзакцій немає взагалі: DB::transaction() у Laravel на MyISAM-таблиці нічого не відкотить.
• Блокування рядків. Два запити, що оновлюють різні рядки однієї таблиці, не заважають один одному. MyISAM блокує всю таблицю на кожен запис - під навантаженням на запис усі запити стають у чергу.
• Узгоджене читання (MVCC). Читачі не чекають на записувачів: SELECT бачить знімок даних, а не блокується через чужий UPDATE.
• Зовнішні ключі. MyISAM синтаксис FOREIGN KEY приймає, але ігнорує.
• Стійкість до збоїв. Після аварійного вимкнення InnoDB відновлюється за журналом повтору (redo log). MyISAM-таблиці після збою часто доводиться «лагодити» (REPAIR TABLE) з ризиком втратити дані.
• Кластерний індекс. Рядки фізично впорядковані за первинним ключем, тож пошук за ним дуже дешевий.

Чим MyISAM колись приваблював і чому це вже неактуально:

• точний COUNT(*) без умов миттєво (лічильник у метаданих) - InnoDB рахує рядки, бо через MVCC різні транзакції бачать різну кількість;
• повнотекстовий пошук - InnoDB має його від 5.6;
• менший розмір на диску - різниця не варта втрати транзакцій.

Як перевірити й перевести:

SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = DATABASE() AND engine <> 'InnoDB';

ALTER TABLE legacy_logs ENGINE = InnoDB;

ALTER ... ENGINE перебудовує таблицю повністю, тож на великих таблицях це варто планувати.

Інші рушії мають вузьке застосування: MEMORY для тимчасових даних (зникають при перезапуску), ARCHIVE для рідко читаних логів, CSV для обміну. Для таблиць застосунку відповідь майже завжди одна - InnoDB.

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

InnoDB за замовчуванням працює на рівні REPEATABLE READ. У PostgreSQL за замовчуванням - READ COMMITTED. Той самий код Laravel на двох базах може поводитися по-різному.

REPEATABLE READ у MySQL означає: звичайні SELECT у межах транзакції бачать один і той самий знімок даних. Знімок фіксується під час першого читання в транзакції:

-- сесія A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;   -- 100, знімок зафіксовано

-- сесія B (автокоміт)
UPDATE accounts SET balance = 50 WHERE id = 1;

-- сесія A
SELECT balance FROM accounts WHERE id = 1;   -- все ще 100
COMMIT;
SELECT balance FROM accounts WHERE id = 1;   -- 50

На READ COMMITTED (PostgreSQL за замовчуванням) другий SELECT у сесії A вже показав би 50: кожен оператор бачить свіжі закомічені дані.

Важливий нюанс MySQL: знімок діє лише для звичайних читань. UPDATE, DELETE і SELECT ... FOR UPDATE працюють з останньою закоміченою версією рядка. Тож у транзакції можна прочитати 100, а UPDATE ... SET balance = balance - 10 порахує від 50. Звідси правило: якщо значення читають, щоб потім на його основі писати, - читати треба з FOR UPDATE.

Як подивитися й змінити рівень:

SELECT @@transaction_isolation;               -- REPEATABLE-READ

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- лише для наступної транзакції

У Laravel рівень можна задати в конфігурації підключення ('isolation_level' => 'READ COMMITTED' для MySQL).

Чотири рівні стандарту SQL: READ UNCOMMITTED (бачить незакомічене), READ COMMITTED, REPEATABLE READ, SERIALIZABLE (у InnoDB звичайні SELECT неявно стають FOR SHARE, якщо вимкнено автокоміт).

Що варто пам'ятати: REPEATABLE READ у InnoDB завдяки блокуванням проміжків (gap locks) захищає від фантомів при блокувальних читаннях - але ціною додаткових блокувань і частіших взаємоблокувань.

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

В InnoDB таблиця і є індексом: рядки зберігаються в B-дереві, впорядкованому за первинним ключем. Це дерево називають кластерним індексом. Окремої «купи» рядків, як у PostgreSQL, немає.

Як InnoDB обирає кластерний індекс:

1. первинний ключ (PRIMARY KEY);
2. якщо його немає - перший UNIQUE-індекс, у якого всі колонки NOT NULL;
3. якщо немає й такого - прихований індекс GEN_CLUST_INDEX за внутрішнім 6-байтовим ідентифікатором рядка, невидимим для запитів.

Вторинні індекси (усі інші) зберігають не адресу рядка, а значення первинного ключа:

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    INDEX (user_id)          -- фактично зберігає пари (user_id, id)
);

SELECT total FROM orders WHERE user_id = 7;
-- 1) пошук у індексі user_id -> отримано id
-- 2) пошук у кластерному індексі за id -> отримано total

Наслідки для практики:

• Пошук за первинним ключем найдешевший - одне проходження дерева, і рядок уже на місці.
• Довгий первинний ключ роздуває всі індекси, бо його копія є в кожному вторинному. BIGINT (8 байтів) проти CHAR(36) для UUID (36 байтів) - відчутна різниця на великій таблиці.
• Випадковий первинний ключ шкодить вставці. Автоінкремент дописує рядки в кінець дерева. Випадкові UUIDv4 вставляють у довільні сторінки, викликають їх розщеплення й фрагментацію.
• Вторинний індекс «безкоштовно» покриває первинний ключ. SELECT id FROM orders WHERE user_id = 7 читає лише індекс, без другого кроку.
• Діапазон за первинним ключем дешевий: WHERE id BETWEEN 1000 AND 2000 читає сусідні сторінки.

Тому в Laravel $table->id() (BIGINT UNSIGNED AUTO_INCREMENT) - вдалий вибір за замовчуванням для InnoDB, а для UUID краще впорядковані за часом значення (UUIDv7, HasUuids).

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

У MySQL оператори визначення схеми (DDL) виконують неявний коміт: перед виконанням вони комітять поточну транзакцію, і самі в транзакцію не потрапляють.

START TRANSACTION;
INSERT INTO settings (name) VALUES ('a');
ALTER TABLE settings ADD COLUMN value TEXT;   -- INSERT уже закомічено
ROLLBACK;                                     -- відкочувати нічого

Що викликає неявний коміт (основне):

• CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE, створення й видалення індексів, подань, процедур;
• LOCK TABLES і UNLOCK TABLES (якщо таблиці були заблоковані);
• START TRANSACTION / BEGIN - комітять попередню незавершену транзакцію;
• адміністративні: ANALYZE TABLE, OPTIMIZE TABLE, CREATE USER, GRANT тощо.

CREATE TEMPORARY TABLE неявного коміту не робить.

Що це означає для Laravel-міграцій:

public function up(): void
{
    Schema::table('users', function (Blueprint $table) {
        $table->string('phone')->nullable();
    });

    Schema::table('users', function (Blueprint $table) {
        $table->string('phone')->unique()->change();   // впаде на дублікатах
    });
}

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

Атомарний DDL у MySQL 8 означає інше: окремий оператор DDL або виконується повністю, або не виконується (не лишає «напівстворених» таблиць після збою). Але кілька операторів у транзакцію все одно не об'єднати.

PostgreSQL тут відрізняється: DDL у ньому транзакційний, і Laravel обгортає міграцію в транзакцію ($withinTransaction = true), тож упала - відкотилася повністю.

Практичні висновки для MySQL:

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

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

Пропуски в AUTO_INCREMENT - нормальна поведінка, а не помилка. Лічильник гарантує унікальність і зростання, але не неперервність.

Звідки беруться пропуски:

• Відкат транзакції. Значення видається під час вставки й не повертається при ROLLBACK:

START TRANSACTION;
INSERT INTO orders (total) VALUES (100);   -- отримав id = 41
ROLLBACK;
INSERT INTO orders (total) VALUES (200);   -- id = 42, 41 пропущено назавжди

• Невдала вставка. Порушення унікальності чи іншого обмеження після того, як номер уже виділено.
• INSERT ... ON DUPLICATE KEY UPDATE та INSERT IGNORE. Номер може бути виділений, навіть коли рядок оновлено чи пропущено. Таблиця з частими upsert-ами може «з'їдати» номери значно швидше, ніж росте кількість рядків.
• Масові вставки. Для INSERT ... SELECT і LOAD DATA, коли кількість рядків заздалегідь невідома, InnoDB виділяє номери блоками (1, 2, 4, 8...), і залишок блоку пропадає.
• Видалення рядків - звісно, теж.

Режим блокування лічильника innodb_autoinc_lock_mode. У MySQL 8 за замовчуванням 2 (interleaved): паралельні вставки отримують номери без табличного блокування, тож номери з різних транзакцій можуть перемежовуватися. Це швидко, але безпечно лише з рядковим форматом бінарного журналу (за замовчуванням ROW).

Після перезапуску сервера: від MySQL 8.0 лічильник зберігається (записується в журнал повтору). У 5.7 і раніше після перезапуску він ставав MAX(id) + 1, тож видалені «хвостові» номери могли видатися повторно.

Чого не робити:

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

Про переповнення: INT UNSIGNED закінчується на ~4,29 млрд. Таблиця з частими upsert-ами може дістатися ліміту раніше, ніж здається. Тому в Laravel id() створює BIGINT UNSIGNED.

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