Как ускорить импорт в mySQL из CSV большого объема данных?

Доброго времени суток.
Имеется скрипт на PHP для импорта данных из CSV в mySQL.
Импорт делается через временную таблицу:
<?php
$files = glob('*.csv');

foreach($files as $file){
	$mysqli = new mysqli("localhost", "user", "pass", "db");
		
	$query1 = "CREATE TEMPORARY TABLE table_temp LIKE table";
	$query2 = "LOAD DATA LOCAL INFILE '".$file."' INTO TABLE table_temp FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' (column1, column2, column3)";
	$query3 = "INSERT IGNORE INTO table(column1, column2, column3) (SELECT column1, column2, column3 FROM table_temp)";
	$query4 = "DROP TABLE table_temp";

	if (!$mysqli->multi_query($query1)) {
		printf("Сообщение ошибки: %s\n", $mysqli->error);
	};
	
	if (!$mysqli->multi_query($query2)) {
		printf("Сообщение ошибки: %s\n", $mysqli->error);
	};
	
	if (!$mysqli->multi_query($query3)) {
		printf("Сообщение ошибки: %s\n", $mysqli->error);
	};
	
	if (!$mysqli->multi_query($query4)) {
		printf("Сообщение ошибки: %s\n", $mysqli->error);
	};

	$mysqli->close();
	
	//unlink($file);
	rename($file, '/done/'.$file);
}
?>

На старте заливки все было неплохо, но когда количество строк перевалило за 80 млн., проверка на уникальность column1 стала проходить в разы больше, причем сервер не загружен вообще, т.е. затык явно в единичном запросе. Можно ли ускорить заливку в базу, к примеру, распараллелить запросы или сделать многопоточность?
База данных InnoDB, Индексы UNIQUE id, UNIQUE column1.

Спасибо за ответы.
  • Вопрос задан
  • 1399 просмотров
Пригласить эксперта
Ответы на вопрос 4
Создание $mysqli надо вынести из цикла. Достаточно, а точнее, даже необходимо, создать один раз перед циклом. Впрочем, $query1, 2 и 4 тоже должны быть перед циклом, т.к. они не меняютса.
Не нужно делать DROP TABLE, достаточно TRUNCATE.
DROP TABLE - 1 раз по окончании цикла.
Я предполагаю, что создать $mysqli надо где-то еще раньше, там, где начинает использоваться соединение с базой данных, чтобы соединение не повторялось.
Ответ написан
2ord
@2ord
продвинутый чайник
А на каком этапе создаются индексы? В коде отсутствует их создание.

Александр
я так полагаю в момент INSERT mySQL делает это автоматически.

Поскольку индексы нужны только для SELECT, а при вставке тратится время их записи на диск, то лучше включить индексы только по окончанию импорта.

Добавлено
Помимо вышесказанного, советую также обратить внимание на Percona XtraDB - обратно-совместимый форк InnoDB.
Ответ написан
BojackHorseman
@BojackHorseman Куратор тега PHP
...в творческом отпуске...
1. нужно попробовать отказаться от строковых индексов, если есть возможность построить числовой хэш в момент попадания данных в источник и индексировать по числу. сравнение целых гораздо быстрее сравнения строк.

2. индекс становится раздутым и чтение его требует большого количества дисковых операций и отсюда тормоза. если не удается уменьшить размер индекса, то можно попробовать снизить количество его чтений. например сделать таблицу с неуникальным индексом, а затем выбрать из нее distinct'ом. этот вариант должен сработать, если в источнике много повторений.
Ответ написан
На днях выгружали 40 миллионов строк - просто INSERT обещал сделать это за 3 дня, а INSERT внутри транзакции справился за 20 минут.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
20 июн. 2019, в 00:13
1000 руб./за проект
20 июн. 2019, в 00:01
2500 руб./за проект
19 июн. 2019, в 23:38
10000 руб./за проект