Import großer Datenmengen mit LOAD DATA und Bulk Inserts

1. Multi-Row INSERT: Optimale Batch-Größen

Der einfachste Weg um Inserts zu beschleunigen ist das Zusammenfassen vieler Zeilen in einem einzigen INSERT-Statement. Statt für jede Zeile eine eigene Query zu schicken, enthält eine Query tausende Zeilen. Der Overhead für Netzwerkkommunikation, Parser und Commitment wird auf viele Zeilen verteilt.

Die optimale Batch-Größe liegt in der Praxis zwischen 1.000 und 5.000 Zeilen pro INSERT. Zu kleine Batches verschwenden den Optimization-Overhead. Zu große Batches führen zu langen Transaktionen, großen Redo-Log-Einträgen und erhöhtem Speicherverbrauch. Für 10 MB Zeilen pro Batch ist das ein guter Ausgangspunkt.

-- Single-row INSERT: slow for bulk operations
INSERT INTO product_staging (sku, name, price, qty) VALUES ('SKU-001', 'Product 1', 29.99, 100);
INSERT INTO product_staging (sku, name, price, qty) VALUES ('SKU-002', 'Product 2', 49.99, 50);
-- ...repeated 100,000 times = 100,000 round trips

-- Multi-row INSERT: much faster — 1 round trip for 1000 rows
INSERT INTO product_staging (sku, name, price, qty) VALUES
    ('SKU-001', 'Product 1',  29.99, 100),
    ('SKU-002', 'Product 2',  49.99,  50),
    ('SKU-003', 'Product 3',  19.99, 200),
    ('SKU-004', 'Product 4',  99.99,  25),
    -- ... up to ~1000-5000 rows per statement
    ('SKU-999', 'Product 999', 15.99, 75);

-- INSERT ... SELECT for server-side bulk inserts (no network round trip)
-- Transforms data from staging into final table in one operation
INSERT INTO catalog_product_entity (sku, type_id, attribute_set_id, created_at)
SELECT
    ps.sku,
    'simple' AS type_id,
    4        AS attribute_set_id,  -- Default attribute set
    NOW()
FROM product_staging ps
WHERE ps.sku NOT IN (SELECT sku FROM catalog_product_entity)
ORDER BY ps.sku;

2. LOAD DATA INFILE: Der schnellste CSV-Import

LOAD DATA INFILE ist die schnellste Methode um CSV-Daten in MySQL zu importieren — typischerweise 5- bis 20-mal schneller als Multi-Row INSERTs, weil MySQL die Datei direkt liest und den Parser-Overhead minimiert. Die Datei muss auf dem Server-Filesystem liegen (oder bei LOAD DATA LOCAL INFILE auf dem Client).

-- Basic LOAD DATA INFILE for product import
LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE product_staging
FIELDS TERMINATED BY ','
       ENCLOSED BY '"'
       ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES  -- skip header row
(sku, name, description, price, special_price, qty, weight, status);

-- With column transformations and default values
LOAD DATA INFILE '/var/lib/mysql-files/customers.csv'
INTO TABLE customer_import_staging
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ';'       -- semicolon-separated
       OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'     -- Windows line endings
IGNORE 1 LINES
(
    @email,
    @firstname,
    @lastname,
    @dob,
    @group_id
)
SET
    email      = LOWER(TRIM(@email)),         -- normalize email
    firstname  = TRIM(@firstname),
    lastname   = TRIM(@lastname),
    dob        = IF(@dob = '', NULL, STR_TO_DATE(@dob, '%d.%m.%Y')),
    group_id   = IF(@group_id = '', 1, @group_id),
    created_at = NOW(),
    import_batch = 'batch_2025_01_17';

-- LOAD DATA LOCAL INFILE: file is on the client machine (not server)
-- Requires --local-infile=1 in MySQL client config
LOAD DATA LOCAL INFILE '/home/user/export/orders.csv'
INTO TABLE order_import_staging
FIELDS TERMINATED BY ','
       ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Wichtig: Der Pfad in LOAD DATA INFILE muss im secure_file_priv-Verzeichnis liegen (standardmäßig /var/lib/mysql-files/). Das kann mit SHOW VARIABLES LIKE 'secure_file_priv' geprüft werden.

3. Transaction Batching: Redo-Log unter Kontrolle

Eine einzige riesige Transaktion für einen Import mit Millionen von Zeilen ist keine gute Idee. Das Redo-Log wächst ins Unermessliche, ein Fehler in Zeile 999.999 rollt alles zurück, und der Speicherverbrauch kann die Datenbank zum Absturz bringen. Transaction Batching teilt den Import in handhabbare Einheiten auf.

-- Transaction batching pattern: commit every N rows
-- This example processes rows from a staging table in batches

SET @batch_size = 2000;
SET @offset = 0;
SET @total = (SELECT COUNT(*) FROM product_staging WHERE processed = 0);

-- Batch loop (pseudocode — implement in application or stored procedure)
WHILE @offset < @total DO
    START TRANSACTION;

    INSERT INTO catalog_product_entity (sku, type_id, attribute_set_id, created_at)
    SELECT sku, type_id, attribute_set_id, NOW()
    FROM product_staging
    WHERE processed = 0
    ORDER BY id
    LIMIT @batch_size OFFSET @offset;

    -- Mark batch as processed
    UPDATE product_staging
    SET processed = 1, processed_at = NOW()
    WHERE processed = 0
    ORDER BY id
    LIMIT @batch_size;

    COMMIT;

    SET @offset = @offset + @batch_size;
END WHILE;

-- Alternative: Use the ID range approach for large tables
-- Process IDs 1-2000, then 2001-4000, etc.
START TRANSACTION;
INSERT INTO target_table SELECT * FROM staging WHERE id BETWEEN 1 AND 2000;
COMMIT;

START TRANSACTION;
INSERT INTO target_table SELECT * FROM staging WHERE id BETWEEN 2001 AND 4000;
COMMIT;

4. Keys deaktivieren während des Imports

Für MyISAM-Tabellen kann mit ALTER TABLE t DISABLE KEYS das Schreiben in nicht-eindeutige Sekundärindizes während des Imports ausgesetzt werden. Das ist bei großen Bulk-Loads erheblich schneller, weil die Indexes nach dem Import in einem Durchgang gebaut werden statt bei jedem INSERT aktualisiert zu werden.

Für InnoDB gibt es diesen Mechanismus nicht direkt, aber äquivalente Optionen:

-- MyISAM: disable/enable non-unique indexes
ALTER TABLE myisam_table DISABLE KEYS;
LOAD DATA INFILE '/path/to/data.csv' INTO TABLE myisam_table ...;
ALTER TABLE myisam_table ENABLE KEYS;

-- InnoDB equivalent: disable unique checks (dangerous if data has duplicates!)
SET unique_checks = 0;
SET foreign_key_checks = 0;  -- see next section

LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE product_staging
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

SET unique_checks = 1;
SET foreign_key_checks = 1;

-- WARNING: unique_checks=0 does NOT check for duplicate key violations
-- MySQL will still detect duplicates on the PRIMARY KEY (clustered index)
-- but UNIQUE secondary indexes are not enforced during the insert
-- Only use this if you are 100% certain the data has no duplicates!

-- Safer alternative: sort the import data by primary key before loading
-- InnoDB inserts are faster when data is in primary key order
-- This allows sequential page writes instead of random I/O
LOAD DATA INFILE '/var/lib/mysql-files/sorted_products.csv'
INTO TABLE product_staging
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

5. foreign_key_checks=0: Chancen und Risiken

Das Deaktivieren von Foreign Key Checks (SET foreign_key_checks = 0) kann Imports signifikant beschleunigen, da MySQL nicht für jede eingefügte Zeile in der referenzierten Tabelle nachschlagen muss. Es ist aber ein zweischneidiges Schwert: Wenn die Daten ungültige Referenzen enthalten, werden diese still importiert — ohne Fehlermeldung.

-- The risk of foreign_key_checks=0
-- Without FK checks, this will succeed even if store_id 999 doesn't exist:
SET foreign_key_checks = 0;
INSERT INTO sales_order (store_id, customer_id, grand_total, created_at)
VALUES (999, 12345, 150.00, NOW());  -- store_id 999 may not exist!
SET foreign_key_checks = 1;

-- After re-enabling FK checks, the orphaned row remains — inconsistent DB!

-- Safe pattern: validate referential integrity BEFORE disabling FK checks
-- Check that all store_ids in import exist in store table
SELECT DISTINCT si.store_id
FROM staging_import si
LEFT JOIN store s ON s.store_id = si.store_id
WHERE s.store_id IS NULL;
-- If this returns rows, there are invalid store_ids in the import!

-- Only proceed if the above check returns zero rows
SET foreign_key_checks = 0;
INSERT INTO target_table SELECT * FROM staging_import;
SET foreign_key_checks = 1;

-- Verify no orphans were created after import
SELECT COUNT(*) AS orphaned_orders
FROM sales_order so
LEFT JOIN store s ON s.store_id = so.store_id
WHERE s.store_id IS NULL;

6. innodb_autoinc_lock_mode für parallele Inserts

Wenn mehrere parallele Prozesse gleichzeitig in eine AUTO_INCREMENT-Tabelle schreiben, kann der Standard-Auto-Increment-Locking-Mode zum Bottleneck werden. innodb_autoinc_lock_mode=2 (interleaved mode) erlaubt parallelen Inserts, ohne dass sie aufeinander warten müssen — was die Gesamtperformance bei Bulk-Imports erheblich verbessert.

-- Check current auto_increment lock mode
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
/*
0 = traditional: full table lock for all INSERT statements
1 = consecutive (default before MySQL 8): lock for bulk inserts, interlocked for simple
2 = interleaved: no lock, fastest for concurrent inserts
*/

-- Set in my.cnf for persistent configuration:
-- [mysqld]
-- innodb_autoinc_lock_mode = 2

-- innodb_autoinc_lock_mode=2 is safe when:
-- - INSERT ... SELECT or LOAD DATA with auto-increment is used
-- - Multiple threads insert concurrently
-- Note: with mode=2, IDs may not be consecutive — gaps are possible
-- This is fine for most applications but breaks assumptions about sequential IDs

-- Monitor auto-increment usage during bulk import
SHOW TABLE STATUS WHERE Name = 'product_staging'\G
-- Look at Auto_increment value to see current counter

7. Import-Fortschritt monitoren

Bei langen Imports ist es wichtig zu wissen, wie weit man ist — und ob der Import noch läuft oder hängt. MySQL bietet mehrere Wege zur Beobachtung laufender Operationen:

-- Monitor running imports via information_schema.PROCESSLIST
SELECT
    ID,
    USER,
    DB,
    COMMAND,
    TIME,
    STATE,
    LEFT(INFO, 100) AS query_preview
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
  AND TIME > 5
ORDER BY TIME DESC;

-- For LOAD DATA INFILE: check progress via performance_schema
SELECT
    st.PROCESSLIST_ID,
    st.PROCESSLIST_INFO,
    st.PROCESSLIST_STATE,
    st.PROCESSLIST_TIME
FROM performance_schema.threads st
WHERE st.PROCESSLIST_COMMAND != 'Sleep'
  AND st.PROCESSLIST_TIME > 5;

-- Monitor InnoDB row insert rate
SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted';
-- Run twice with a 10-second pause to calculate rows/second

-- Check how many rows are in the target table so far
SELECT COUNT(*) AS imported_rows FROM product_staging;
-- Compare to total expected rows in the CSV file

8. Magento ImportExport vs direktes SQL

Magento bietet ein eigenes ImportExport-Framework (app/code/Magento/ImportExport), das Produkt- und Kundendaten validiert, transformiert und über definierte Prozesse importiert. Es ist der sichere Weg für Produktions-Imports, weil es Indexer, Caches und Event-System korrekt behandelt.

Direktes SQL ist schneller, aber umgeht alle Magento-seitigen Checks: Indexer werden nicht automatisch aktualisiert, URL-Rewrites werden nicht generiert, Produktbilder werden nicht verarbeitet. Direktes SQL eignet sich für Daten-Migrationen und Staging-Setups, nicht für Produktions-Katalog-Updates.

-- Direct SQL bulk insert into Magento catalog (only for migrations/staging)
-- After this, full reindex is required!

-- Step 1: Disable indexes to speed up EAV inserts
SET foreign_key_checks = 0;
SET unique_checks = 0;

-- Step 2: Insert base product records
INSERT INTO catalog_product_entity
    (entity_id, attribute_set_id, type_id, sku, created_at, updated_at)
SELECT
    NULL,  -- auto-increment
    4,     -- default attribute set
    'simple',
    ps.sku,
    NOW(),
    NOW()
FROM product_staging ps
WHERE ps.sku NOT IN (SELECT sku FROM catalog_product_entity)
LIMIT 5000;  -- batch of 5000

-- Step 3: Re-enable checks
SET foreign_key_checks = 1;
SET unique_checks = 1;

-- Step 4: After ALL batches completed, run full reindex
-- (via CLI, not SQL)
-- bin/magento indexer:reindex catalog_product_flat
-- bin/magento indexer:reindex catalog_product_price
-- bin/magento indexer:reindex catalogsearch_fulltext
-- bin/magento cache:flush

-- Magento's built-in import (recommended for production):
-- bin/magento import:run --entity=catalog_product \
--   --behavior=append \
--   --validation-strategy=validation-stop-on-errors \
--   products_import.csv

FAQ: MySQL Bulk Import mit LOAD DATA und Bulk Inserts