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

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

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

EXPLAIN SELECT * FROM orders WHERE user_id = 7 AND status = 'paid';

Найважливіші колонки:

• type - спосіб доступу. Від найкращого до найгіршого:
  • const / system - щонайбільше один рядок за первинним чи унікальним ключем;
  • eq_ref - у з'єднанні один рядок цієї таблиці на кожен рядок попередньої (за унікальним ключем);
  • ref - пошук за неунікальним індексом з рівністю;
  • range - діапазон індексу (BETWEEN, >, IN, LIKE 'abc%');
  • index - повне сканування індексу (дешевше за таблицю, але все одно все);
  • ALL - повне сканування таблиці. На великій таблиці - головний сигнал проблеми.
• possible_keys - індекси, які теоретично підходять; key - обраний. NULL у key - індекс не використано.
• key_len - скільки байтів індексу використано. Для складеного індексу показує, скільки колонок з нього реально задіяно.
• rows - оцінка кількості рядків, які доведеться прочитати (за статистикою, не точна).
• filtered - який відсоток прочитаних рядків пройде решту умов. rows × filtered / 100 - скільки рядків піде далі.
• Extra - додаткові подробиці:
  • Using index - запит обслуговано лише з індексу (покривний індекс), чудово;
  • Using where - умову перевірено після читання рядка;
  • Using index condition - частину умови перевірено ще в індексі (Index Condition Pushdown);
  • Using filesort - потрібне окреме сортування;
  • Using temporary - потрібна тимчасова таблиця (часто GROUP BY чи DISTINCT).

Що шукати першим:

1. type = ALL на великій таблиці;
2. великі rows при маленькому результаті - індекс неселективний чи відсутній;
3. Using filesort / Using temporary на запитах, що мають бути швидкими.

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

У Laravel план зручно отримати прямо з будівника: User::where('email', $email)->explain()->dd();.

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

Складений індекс (a, b, c) - це B-дерево, впорядковане спершу за a, всередині однакових a - за b, всередині однакових (a, b) - за c. Як телефонний довідник: прізвище, потім ім'я.

Правило лівого префікса: індекс можна використати для пошуку лише за початковими колонками:

CREATE INDEX orders_idx ON orders (user_id, status, created_at);

-- індекс працює
WHERE user_id = 7
WHERE user_id = 7 AND status = 'paid'
WHERE user_id = 7 AND status = 'paid' AND created_at > '2026-01-01'
WHERE status = 'paid' AND user_id = 7     -- порядок в умові не важливий

-- індекс для пошуку не працює
WHERE status = 'paid'                     -- пропущено user_id
WHERE created_at > '2026-01-01'

Знайти всіх Іванів у довіднику, впорядкованому за прізвищем, можна лише переглянувши його повністю.

Діапазон «обриває» індекс. Колонки після першої з діапазоном (>, <, BETWEEN, LIKE 'abc%') для пошуку вже не використовуються:

WHERE user_id = 7 AND created_at > '2026-01-01' AND status = 'paid'
-- індекс (user_id, created_at, status): пошук за user_id + діапазон created_at,
-- status перевіряється для кожного запису діапазону
-- індекс (user_id, status, created_at): пошук за всіма трьома - краще

Звідси практичне правило порядку колонок: спершу колонки з рівністю, потім колонка з діапазоном чи сортуванням.

Що ще дає порядок колонок:

• ORDER BY за колонками, що йдуть одразу після колонок з рівністю, не потребує сортування: WHERE user_id = 7 ORDER BY created_at з індексом (user_id, created_at);
• окремий індекс (user_id) не потрібен, якщо є (user_id, status) - лівий префікс його заміняє. Це зайвий індекс, що лише сповільнює запис.

Skip scan (MySQL 8.0.13+) частково обходить правило: якщо в першої колонки мало різних значень, а запит покривається індексом, MySQL може «перестрибувати» по значеннях першої колонки. У EXPLAIN видно Using index for skip scan. Розраховувати на це при проєктуванні не варто.

У Laravel: $table->index(['user_id', 'status', 'created_at']); - порядок у масиві і є порядком колонок в індексі.

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

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

1. Функція чи вираз над колонкою.

-- індекс на created_at не використовується
WHERE YEAR(created_at) = 2026
WHERE DATE(created_at) = '2026-10-04'
WHERE price * 1.2 > 100

-- переписати на діапазон по «голій» колонці
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
WHERE price > 100 / 1.2

Якщо без функції ніяк, у MySQL 8 є функціональні індекси: CREATE INDEX ... ((LOWER(email))).

2. Неявне приведення типів. Рядкова колонка порівнюється з числом:

-- phone VARCHAR(20), індекс є
WHERE phone = 380501234567     -- кожен рядок перетворюється на число, індекс не працює
WHERE phone = '380501234567'   -- індекс працює

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

3. LIKE з відсотком на початку.

WHERE name LIKE 'Олек%'    -- діапазон індексу, працює
WHERE name LIKE '%сандр%'  -- немає початку, з якого шукати

Для пошуку всередині тексту - FULLTEXT-індекс чи зовнішній пошуковий рушій.

4. Порушене правило лівого префікса складеного індексу: умова не містить першої колонки.

5. OR між різними колонками. WHERE email = ? OR phone = ? може взагалі не використати індекс або використати злиття індексів. Інколи краще UNION двох запитів.

6. Оптимізатор вирішив, що повне сканування дешевше. Якщо умова підходить під значну частину таблиці (наприклад, status = 'active' для 80% рядків), читати таблицю послідовно швидше, ніж робити тисячі переходів з індексу в кластерний індекс. Це не помилка, а правильне рішення.

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

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

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

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

Вимоги до хорошого первинного ключа:

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

Варіант за замовчуванням - автоінкремент:

$table->id();   // BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY

8 байтів, зростає, нічого не коштує. BIGINT замість INT - щоб не впертися в ~4,29 млрд (пропуски в нумерації з'їдають запас швидше, ніж здається).

UUID - коли справді потрібен (генерація id на клієнті, злиття даних з різних баз, неперебірні публічні ідентифікатори):

• UUIDv4 (випадковий) - найгірший варіант для InnoDB. Кожна вставка йде у випадкову сторінку: розщеплення сторінок, фрагментація, сторінки заповнені наполовину, буферний пул використовується неефективно;
• UUIDv7 / ULID (впорядковані за часом) - вставляються майже послідовно, проблеми v4 зникають. Laravel HasUuids генерує саме впорядковані UUID;
• зберігати компактно - BINARY(16), а не CHAR(36).

Компроміс: внутрішній BIGINT-ключ для зв'язків плюс окрема унікальна колонка uuid для зовнішнього світу (URL, API).

Природні ключі (email, номер телефону, код ІПН) - погана ідея: вони змінюються, бувають довгими, а зміна каскадом зачіпає всі зовнішні ключі. Унікальний індекс на них - так; первинний ключ - ні.

Складений первинний ключ доречний у таблицях зв'язку «багато-до-багатьох»:

Schema::create('role_user', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained();
    $table->foreignId('role_id')->constrained();
    $table->primary(['user_id', 'role_id']);
});

Рядки одного користувача лежать поруч, а пошук «ролі користувача» читає сусідні записи.

Таблиця без первинного ключа отримає прихований внутрішній ключ, недоступний у запитах. До того ж Group Replication (InnoDB Cluster) та інструменти онлайн-змін схеми вимагають явного первинного ключа, а керовані хмарні MySQL часто вмикають sql_require_primary_key.

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

Префіксний індекс індексує не всю рядкову колонку, а лише перші N символів:

CREATE INDEX urls_path_idx ON urls (path(50));

Навіщо:

• ліміт довжини ключа - для формату рядка DYNAMIC це 3072 байти. VARCHAR(1000) в utf8mb4 важить до 4000 байтів і повністю в індекс не поміститься;
• TEXT і BLOB без префікса індексувати взагалі не можна;
• розмір індексу - менший індекс краще вміщується в буферний пул.

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

SELECT COUNT(DISTINCT path) / COUNT(*)            AS full_selectivity,
       COUNT(DISTINCT LEFT(path, 20)) / COUNT(*)  AS p20,
       COUNT(DISTINCT LEFT(path, 50)) / COUNT(*)  AS p50,
       COUNT(DISTINCT LEFT(path, 100)) / COUNT(*) AS p100
FROM urls;

Беруть найменшу довжину, селективність якої близька до повної. Для URL з однаковим початком (https://example.com/blog/...) префікс має бути довшим, ніж здається.

Обмеження префіксних індексів:

• не можуть бути покривними - повне значення все одно читається з таблиці;
• не допомагають ORDER BY і GROUP BY за колонкою;
• унікальний префіксний індекс перевіряє унікальність лише префікса: два різні URL з однаковими першими 50 символами не вставляться.

Альтернатива для пошуку за точним збігом довгого рядка - індекс на хеші:

ALTER TABLE urls
    ADD COLUMN path_hash BINARY(16) AS (UNHEX(MD5(path))) STORED,
    ADD UNIQUE INDEX (path_hash);

SELECT * FROM urls WHERE path_hash = UNHEX(MD5(?)) AND path = ?;

Індекс компактний (16 байтів), працює й для унікальності повних значень.

Історична довідка про Laravel. Schema::defaultStringLength(191) з'явився через старий ліміт 767 байтів (формат COMPACT): 191 × 4 = 764. На сучасних MySQL цей ліміт - 3072 байти, і VARCHAR(255) з індексом працює без хитрощів.

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

mysqldump робить логічний бекап: SQL-файл з CREATE TABLE і INSERT, з якого базу можна відтворити на будь-якому сервері MySQL.

mysqldump --single-transaction --routines --events \
    --user=backup --password app > app-2026-10-04.sql

# відновлення
mysql --user=root --password app < app-2026-10-04.sql

Чому --single-transaction обов'язковий для InnoDB. Без нього дамп читає таблиці по черзі в різні моменти часу. Якщо між читанням orders і order_items хтось створив замовлення, у дампі будуть позиції без замовлення - неузгоджений бекап. Тому за замовчуванням mysqldump блокує таблиці (--lock-tables), і застосунок на час дампу не може писати.

З --single-transaction дамп відкриває транзакцію з узгодженим знімком (REPEATABLE READ) і читає всі таблиці на одну мить, нікого не блокуючи. Працює лише для InnoDB: MyISAM-таблиці знімка не підтримують.

Корисні параметри:

• --routines, --events - збережені процедури й події (тригери включено за замовчуванням);
• --no-data - лише схема; --no-create-info - лише дані;
• --source-data=2 - записати в дамп коментар з позицією бінарного журналу (потрібно для відновлення на момент у часі чи налаштування репліки);
• --quick (увімкнено за замовчуванням) - читати рядки потоком, а не завантажувати таблицю в пам'ять;
• стиснення на льоту: mysqldump ... | gzip > app.sql.gz.

Обмеження й застереження:

• під час дампу не можна змінювати схему (ALTER, TRUNCATE, RENAME) - вони порушують узгодженість знімка, а дамп може впасти чи отримати неповні дані;
• довга транзакція дампу заважає очищенню старих версій рядків (purge) - на великій навантаженій базі дамп краще знімати з репліки;
• відновлення повільне: SQL виконується рядок за рядком і перебудовує індекси. Для бази на сотні гігабайтів це години. Для великих баз використовують фізичні бекапи (Percona XtraBackup, MySQL Enterprise Backup) або паралельні утиліти MySQL Shell (util.dumpInstance()).

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

Безпека: пароль у командному рядку видно в списку процесів. Краще використовувати файл опцій (--defaults-extra-file) чи mysql_config_editor, а бекапи шифрувати й зберігати поза сервером бази.

Докладніше в документації: Бекапи за допомогою mysqldump

Журнал повільних запитів записує запити, що виконувалися довше заданого порогу. Це найпростіший спосіб дізнатися, що саме гальмує базу на продакшені.

Увімкнення без перезапуску:

SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 1;      -- секунди, можна дробові: 0.5
SET PERSIST slow_query_log_file = '/var/log/mysql/slow.log';

За замовчуванням long_query_time - 10 секунд, а для вебзастосунку повільним є вже запит на 200-500 мс.

Додаткові налаштування:

• log_queries_not_using_indexes = ON - записувати й запити без індексів, навіть швидкі. Корисно на розробці; на продакшені шумно (обмежує log_throttle_queries_not_using_indexes);
• min_examined_row_limit - не писати запити, що прочитали менше N рядків;
• log_slow_admin_statements - включити ALTER TABLE, OPTIMIZE TABLE тощо;
• log_output = 'TABLE' - писати в таблицю mysql.slow_log замість файлу (зручно для керованих хмарних баз).

Що є в записі:

# Query_time: 2.415  Lock_time: 0.000  Rows_sent: 20  Rows_examined: 1850234
SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;

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

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

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log   # топ-10 за сумарним часом
pt-query-digest /var/log/mysql/slow.log            # детальніший звіт (Percona Toolkit)

Часто винуватцем виявляється не найповільніший запит, а запит на 50 мс, що виконується 100 000 разів на годину.

Альтернатива без журналу - Performance Schema вже збирає статистику за нормалізованими запитами:

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

У Laravel допоміжно: DB::whenQueryingForLongerThan() для сповіщень про сумарний час запитів у межах запиту, Telescope чи Pulse (картка повільних запитів) на рівні застосунку. Але журнал MySQL бачить і запити від черг, крон-задач і сторонніх клієнтів.

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

SHOW PROCESSLIST показує всі з'єднання з сервером і що кожне робить зараз:

SHOW FULL PROCESSLIST;

Без привілею PROCESS користувач бачить лише власні з'єднання.

Зручніше фільтрувати через таблиці:

SELECT id, user, host, time, state, LEFT(info, 120) AS query
FROM performance_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;

-- з додатковою інформацією про транзакції й блокування
SELECT * FROM sys.session WHERE command <> 'Sleep' ORDER BY time DESC;

Зупинити запит:

KILL QUERY 12345;   -- перервати лише поточний запит, з'єднання лишається
KILL 12345;         -- закрити з'єднання повністю

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

• відкіт може тривати довго. Якщо вбити UPDATE, що змінював рядки 20 хвилин, InnoDB відкочуватиме зміни приблизно стільки ж. Стан Killed у процес-листі - це відкіт, і повторний KILL його не прискорить. Перезапуск сервера теж не допоможе: відкіт продовжиться після старту;
• з'єднання в стані Sleep з великим Time - це не завислі запити, а простоюючі з'єднання (пул, постійні з'єднання). Небезпечні вони лише якщо тримають відкриту транзакцію - це видно в information_schema.innodb_trx;
• убитий запит застосунок отримає як помилку - Laravel-джоба впаде і, можливо, буде повторена.

Запобіжники, щоб не доводилося вбивати вручну:

• max_execution_time (мілісекунди) - ліміт часу для SELECT, глобально чи на сесію, або підказкою /*+ MAX_EXECUTION_TIME(5000) */ в конкретному запиті;
• wait_timeout - закривати неактивні з'єднання;
• тайм-аути в застосунку й важкі звіти на репліці.

Типова картина аварії: десятки запитів у стані Waiting for table metadata lock - шукати не їх, а довгу транзакцію чи ALTER TABLE, що стоїть у черзі першим.

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

Застосунок не повинен ходити в базу під root. Якщо через SQL-ін'єкцію чи вкрадений .env хтось отримає доступ, права користувача визначають, скільки шкоди він зробить.

Користувач для застосунку:

CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'довгий-випадковий-пароль';

GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'10.0.0.%';

Частина @'host' - невід'ємна частина облікового запису. 'app'@'10.0.0.%' - підключення лише з внутрішньої мережі; 'app'@'%' - звідусіль. 'app'@'localhost' і 'app'@'%' - два різні облікові записи з різними паролями й правами, що часто плутає.

Окремий користувач для міграцій:

CREATE USER 'migrator'@'10.0.0.%' IDENTIFIED BY '...';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, REFERENCES
    ON app.* TO 'migrator'@'10.0.0.%';

Застосунок працює з обмеженими правами, а при деплої міграції запускаються з окремими обліковими даними. Тоді ін'єкція не зможе виконати DROP TABLE.

Інші облікові записи за призначенням:

• backup - SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS (для дампів);
• readonly для аналітики й BI - лише SELECT, бажано на репліці;
• моніторинг - PROCESS, REPLICATION CLIENT і SELECT на performance_schema.

Чого не давати застосунку:

• ALL PRIVILEGES чи права на *.*;
• SUPER та адміністративні динамічні привілеї;
• FILE - дає змогу читати й писати файли на сервері (LOAD DATA INFILE, SELECT ... INTO OUTFILE);
• GRANT OPTION.

Перевірити права:

SHOW GRANTS FOR 'app'@'10.0.0.%';

Що змінилося в MySQL 8:

• GRANT більше не створює користувача неявно - спершу CREATE USER;
• зміна пароля - ALTER USER 'app'@'10.0.0.%' IDENTIFIED BY '...';
• плагін автентифікації за замовчуванням - caching_sha2_password;
• для груп прав є ролі (CREATE ROLE).

Пароль застосунку зберігається в .env (DB_USERNAME, DB_PASSWORD), а не в коді чи репозиторії, і змінюється, якщо .env міг потрапити в чужі руки.

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

Бінарний журнал (binary log, binlog) - послідовний запис усіх змін даних і схеми: INSERT, UPDATE, DELETE, CREATE, ALTER тощо. Запити SELECT туди не потрапляють. Записується лише закомічене, у порядку комітів.

Від MySQL 8.0 журнал увімкнено за замовчуванням.

Навіщо він:

1. Реплікація. Репліки читають бінарний журнал джерела й застосовують ті самі зміни у себе.
2. Відновлення на момент у часі (PITR). Відновлюємо нічний бекап, потім «програємо» бінарний журнал до хвилини перед аварією (наприклад, до випадкового DELETE без WHERE).
3. Аудит і захоплення змін (CDC). Інструменти на кшталт Debezium читають журнал і передають зміни в Kafka, пошукові індекси, сховища даних.

Не плутати з журналом повтору InnoDB (redo log). Redo log - внутрішній механізм InnoDB для відновлення після збою, у фізичних термінах сторінок. Бінарний журнал - логічний, на рівні сервера, для всіх рушіїв, і його читають зовнішні споживачі.

Основні команди:

SHOW BINARY LOGS;                 -- список файлів журналу і їхні розміри
SHOW BINARY LOG STATUS;           -- поточний файл і позиція (раніше SHOW MASTER STATUS)
SHOW BINLOG EVENTS IN 'binlog.000042' LIMIT 10;

Вміст у читабельному вигляді:

mysqlbinlog --base64-output=DECODE-ROWS --verbose binlog.000042 | less

Скільки зберігати: binlog_expire_logs_seconds - за замовчуванням 30 днів. Журнал займає місце пропорційно інтенсивності запису, і на навантаженій базі може зайняти більше, ніж самі дані. Але термін має покривати проміжок між бекапами, інакше відновлення на момент у часі неможливе. Видалити старі файли вручну:

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;

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

Вплив на продуктивність: запис журналу й sync_binlog = 1 (fsync на кожен коміт) - помітна, але необхідна ціна стійкості. Групування комітів зменшує її під паралельним навантаженням.

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

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

          записи                 читання
застосунок ──────► джерело ───► репліка 1 ◄──┐
                       │                      ├── застосунок
                       └──────► репліка 2 ◄──┘

Налаштування в Laravel (config/database.php):

'mysql' => [
    'driver' => 'mysql',
    'read' => [
        'host' => ['10.0.0.11', '10.0.0.12'],   // випадкова репліка на запит
    ],
    'write' => [
        'host' => ['10.0.0.10'],
    ],
    'sticky' => true,
    'database' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    // ...
],

SELECT ідуть на репліки, усе інше - на джерело. Транзакції й lockForUpdate() теж виконуються на джерелі.

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

$post = Post::create($data);          // запис на джерело
return redirect()->route('posts.show', $post);
// наступний запит читає з репліки - поста там ще немає, 404

Як Laravel це пом'якшує: 'sticky' => true - якщо в поточному запиті вже був запис, наступні читання цього ж запиту йдуть на джерело. Але наступний HTTP-запит (після редиректу) знову піде на репліку.

Інші способи:

• явно читати з джерела: Post::query()->useWritePdo()->find($id), DB::connection('mysql')->...;
• для критичних сторінок після запису (профіль після редагування, замовлення після оплати) - читати з джерела кілька секунд після запису (через сесію чи кеш);
• черги: джоба, поставлена в черзі одразу після запису, може не знайти рядок на репліці - afterCommit не допомагає від затримки репліки, лише від незакоміченої транзакції.

Що ще дають репліки:

• важкі звіти й аналітика без навантаження на джерело;
• бекапи з репліки;
• резерв на випадок відмови джерела (але перемикання - окрема задача).

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

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