Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Як змінювати схему великої таблиці 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

Схожі питання