Jak poprawić wydajność INSERT na bardzo dużej tabeli MySQL
Pracuję nad dużą bazą danych MySQL i muszę poprawić wydajność INSERT na konkretnej tabeli. Ten zawiera około 200 milionów wierszy, a jego struktura jest następująca:
(mała przesłanka: nie jestem ekspertem od baz danych, więc napisany przeze mnie kod może opierać się na błędnych podstawach. Pomóż mi zrozumieć moje błędy :))
CREATE TABLE IF NOT EXISTS items (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
key VARCHAR(10) NOT NULL,
busy TINYINT(1) NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY (id, name),
UNIQUE KEY name_key_unique_key (name, key),
INDEX name_index (name)
) ENGINE=MyISAM
PARTITION BY LINEAR KEY(name)
PARTITIONS 25;
Każdego dnia otrzymuję wiele plików csv, w których każda linia składa się z pary „nazwa; klucz”, więc muszę analizować te pliki (dodając wartości created_at i updated_at dla każdego wiersza) i wstawiać wartości do mojej tabeli. W tym przypadku kombinacja „nazwa” i „klucz” MUSI być UNIQUE, więc zaimplementowałem procedurę wstawiania w następujący sposób:
CREATE TEMPORARY TABLE temp_items (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
key VARCHAR(10) NOT NULL,
busy TINYINT(1) NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY (id)
)
ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'file_to_process.csv'
INTO TABLE temp_items
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
(name, key, created_at, updated_at);
INSERT INTO items (name, key, busy, created_at, updated_at)
(
SELECT temp_items.name, temp_items.key, temp_items.busy, temp_items.created_at, temp_items.updated_at
FROM temp_items
)
ON DUPLICATE KEY UPDATE busy=1, updated_at=NOW();
DROP TEMPORARY TABLE temp_items;
Kod, który właśnie pokazałem, pozwala mi osiągnąć mój cel, ale aby ukończyć wykonanie, wykorzystuje około 48 godzin, a to jest problem. Myślę, że ta słaba wydajność jest spowodowana faktem, że skrypt musi sprawdzać na bardzo dużym stole (200 milionów wierszy), a dla każdej wstawki para „nazwa; klucz” jest unikalna.
Jak mogę poprawić wydajność mojego skryptu?
Dziękujemy wszystkim z góry.