Импорт большого CSV-файла в MySQL
Я пытаюсь импортировать csv-файл в таблицу mysql, и в настоящее время у меня есть скрипт, который выполняется построчно, потому что мне нужно хэшировать идентификатор в сочетании с другим идентификатором, а также форматировать дату в формате mysql.
В csv-файле БОЛЬШЕ столбцов, чем я сейчас импортирую. Проще ли просто импортировать все столбцы?
Я читал о ФАЙЛЕ ЗАГРУЗКИ ДАННЫХ (http://dev.mysql.com/doc/refman/5.1/en/load-data.html), но мне интересно, как я могу использовать это и хэшировать идентификаторы и отформатируйте дату, не выполняя построчное выполнение. Мой текущий сценарий занимает слишком много времени и вызывает проблемы с производительностью сайта во время работы.
Вот что у меня есть:
$url = 'http://www.example.com/directory/file.csv';
if (($handle = fopen($url, "r")) !== FALSE)
{
fgetcsv($handle, 1000, ",");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$EvID = $data[0];
$Ev = $data[1];
$PerID = $data[2];
$Per = $data[3];
$VName = $data[4];
$VID = $data[5];
$VSA = $data[6];
$DateTime = $data[7];
$PCatID = $data[8];
$PCat = $data[9];
$CCatID = $data[10];
$CCat = $data[11];
$GCatID = $data[12];
$GCat = $data[13];
$City = $data[14];
$State = $data[15];
$StateID = $data[16];
$Country = $data[17];
$CountryID = $data[18];
$Zip = $data[19];
$TYN = $data[20];
$IMAGEURL = $data[21];
$URLLink = $data[22];
$data[7] = strtotime($data[7]);
$data[7] = date("Y-m-d H:i:s",$data[7]);
if((($PCatID == '2') && (($CountryID == '217') or ($CountryID == '38'))) || (($GCatID == '16') or ($GCatID == '19') or ($GCatID == '30') or ($GCatID == '32')))
{
if(!mysql_query("INSERT IGNORE INTO TNDB_CSV2
(id, EvID, Event, PerID, Per, VName,
VID, VSA, DateTime, PCatID, PCat,
CCatID, CCat, GCatID, GCat, City,
State, StateID, Country, CountryID, Zip,
TYN, IMAGEURL) VALUES
('".md5($EventID.$PerformerID)."','".addslashes($data[0])."','".addslashes($data[1])."','".addslashes($data[2])."','".addslashes($data[3])."','".addslashes($data[4])."',
'".addslashes($data[5])."','".addslashes($data[6])."','".addslashes($data[7])."','".addslashes($data[8])."','".addslashes($data[9])."',
'".addslashes($data[10])."','".addslashes($data[11])."','".addslashes($data[12])."','".addslashes($data[13])."','".addslashes($data[14])."',
'".addslashes($data[15])."','".addslashes($data[16])."','".addslashes($data[17])."','".addslashes($data[18])."','".addslashes($data[19])."',
'".addslashes($data[20])."','".addslashes($data[21])."')"))
{
exit("<br>" . mysql_error());
}
}
}
fclose($handle);
}
Любая помощь всегда очень ценится. Заранее спасибо.
3 answers
Сначала попробуйте оптимизировать свои сценарии. Во-первых, никогда не выполняйте одиночные запросы при импорте, если у вас нет другого выбора, сетевые издержки могут быть убийственными.
Попробуйте что-то вроде (очевидно, непроверенное и закодированное в текстовом поле SO, проверьте соответствие скобок и т. д.):
$url = 'http://www.example.com/directory/file.csv';
if (($handle = fopen($url, "r")) !== FALSE)
{
fgetcsv($handle, 1000, ",");
$imports = array();
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$EvID = $data[0];
$Ev = $data[1];
$PerID = $data[2];
$Per = $data[3];
$VName = $data[4];
$VID = $data[5];
$VSA = $data[6];
$DateTime = $data[7];
$PCatID = $data[8];
$PCat = $data[9];
$CCatID = $data[10];
$CCat = $data[11];
$GCatID = $data[12];
$GCat = $data[13];
$City = $data[14];
$State = $data[15];
$StateID = $data[16];
$Country = $data[17];
$CountryID = $data[18];
$Zip = $data[19];
$TYN = $data[20];
$IMAGEURL = $data[21];
$URLLink = $data[22];
$data[7] = strtotime($data[7]);
$data[7] = date("Y-m-d H:i:s",$data[7]);
if((($PCatID == '2') && (($CountryID == '217') or ($CountryID == '38'))) || (($GCatID == '16') or ($GCatID == '19') or ($GCatID == '30') or ($GCatID == '32')))
{
$imports[] = "('".md5($EventID.$PerformerID)."','".addslashes($data[0])."','".addslashes($data[1])."','".addslashes($data[2])."','".addslashes($data[3])."','".addslashes($data[4])."',
'".addslashes($data[5])."','".addslashes($data[6])."','".addslashes($data[7])."','".addslashes($data[8])."','".addslashes($data[9])."',
'".addslashes($data[10])."','".addslashes($data[11])."','".addslashes($data[12])."','".addslashes($data[13])."','".addslashes($data[14])."',
'".addslashes($data[15])."','".addslashes($data[16])."','".addslashes($data[17])."','".addslashes($data[18])."','".addslashes($data[19])."',
'".addslashes($data[20])."','".addslashes($data[21])."')";
}
}
$importarrays = array_chunk($imports, 100);
foreach($importarrays as $arr) {
if(!mysql_query("INSERT IGNORE INTO TNDB_CSV2
(id, EvID, Event, PerID, Per, VName,
VID, VSA, DateTime, PCatID, PCat,
CCatID, CCat, GCatID, GCat, City,
State, StateID, Country, CountryID, Zip,
TYN, IMAGEURL) VALUES ".implode(',', $arr)){
die("error: ".mysql_error());
}
}
fclose($handle);
}
Поиграйте с числом в array_chunk, слишком большим, и это может вызвать проблемы, такие как слишком длинный запрос (да, в my.cnf есть настраиваемый предел), слишком маленький и ненужный накладные расходы.
Вы также можете отказаться от использования присваивания $data[x] переменным, поскольку это пустая трата, учитывая, насколько мал сценарий, просто используйте $data[x] непосредственно в своем запросе, например (не даст значительного улучшения, но в зависимости от размера импорта это может немного сэкономить).
Следующим шагом было бы использовать вставки/обновления с низким приоритетом, ознакомьтесь с этим для получения дополнительной информации об этом, чтобы начать работу: Как придать приоритет определенным запросам?
После всего этого вы могли бы подумайте об оптимизации конфигурации mysql, но это то, что Google действительно должен объяснить, поскольку лучшие настройки различны для всех и их уникальных ситуаций
Редактировать: Еще одна вещь, которую я делал раньше, - если у вас настроено много ключей, которые не требуются для импорта, вы можете временно удалить эти ключи и добавить их обратно, когда сценарий будет завершен. Это также может привести к значительному улучшению времени, но, поскольку вы работаете с живой базой данных, есть подводные камни, которые нужно обойти, если вы пойдете по этому маршруту.
Попробуйте выполнить пакетную вставку с помощью функции implode(). Для дальнейшего объяснения и примера см. Этот поток вставка нескольких строк через массив php в mysql
Я использовал этот запрос
$sql = "
LOAD DATA LOCAL INFILE 'uploads/{$fileName}'
REPLACE INTO TABLE `order`
FIELDS
TERMINATED BY '\t'
LINES
TERMINATED BY '\r\n'
IGNORE 1 LINES
(product_id, `date`, quantity)
";
Это очень быстро