Як змінювати схему великої таблиці MySQL без простою: INSTANT, INPLACE і COPY?

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