Як швидко завантажити мільйони рядків у таблицю InnoDB?

Question

Accepted Answer

Наївна вставка по одному рядку в автокоміті - найповільніший варіант: на кожен рядок окремий запит по мережі, окремий коміт і окремий fsync журналу. Мільйон рядків - мільйон fsync.
1. Групувати рядки в один INSERT.
INSERT INTO products (sku, name, price) VALUES
    ('A-1', 'Товар 1', 100.00),
    ('A-2', 'Товар 2', 150.00),
    ...;   -- сотні-тисячі рядків на запит

У Laravel - DB::table('products')->insert($chunk) частинами по 500-2000 рядків. Розмір запиту обмежений max_allowed_packet, а кількість параметрів у PDO - теж не безмежна.
2. Комітити пачками, а не кожен рядок.
foreach (array_chunk($rows, 1000) as $chunk) {
    DB::transaction(fn () => DB::table('products')->insert($chunk));
}

Але й одна гігантська транзакція на мільйони рядків погана: величезний undo-журнал, довгий відкат при помилці, відставання реплік. Транзакції по кілька тисяч рядків - баланс.
3. LOAD DATA - найшвидший спосіб для файлу:
LOAD DATA LOCAL INFILE '/tmp/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
(sku, name, price);

LOCAL вимагає дозволу на обох боках (local_infile), що з міркувань безпеки часто вимкнено.
4. Вставляти в порядку первинного ключа. Тоді рядки дописуються в кінець кластерного індексу без розщеплення сторінок. Відсортований файл вантажиться помітно швидше за перемішаний.
5. Тимчасово вимкнути перевірки в сесії (лише для довірених даних):
SET unique_checks = 0;        -- відкладені перевірки вторинних унікальних індексів
SET foreign_key_checks = 0;   -- без перевірок зовнішніх ключів
-- завантаження
SET unique_checks = 1;
SET foreign_key_checks = 1;

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

innodb_autoinc_lock_mode = 2 (за замовчуванням у MySQL 8) - паралельні вставки не блокують одна одну на автоінкременті;
достатній буферний пул і журнал повтору (innodb_redo_log_capacity);
для тимчасового імпорту на окремому інстансі можна послабити innodb_flush_log_at_trx_commit - але ніколи на основній базі з живими даними;
після завантаження - ANALYZE TABLE, щоб оптимізатор знав про нові дані.

Не забувати про репліки: мільйонний імпорт генерує стільки ж подій у бінарному журналі, і репліки відстануть. Пауза між пачками дає їм наздогнати.
Докладніше в документації: Масове завантаження даних в InnoDB

Як швидко завантажити мільйони рядків у таблицю InnoDB?

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