Как обрабатывать CSV с 100k + линиями в PHP?

Что-то вроде этого (обратите внимание, что это 100% непроверено, и от моей головы может понадобиться какая-то настройка, чтобы на самом деле работать :))

 //define array may (probably better ways of doing this $stocks = array( 1 => 22, 2 => 23, 3 => 24, 4 => 25, 5 => 26, 6 => 27, 7 => 28, 8 => 29, 9 => 30, 10 => 31 ); $handle = fopen("file.csv", "r")); //open file while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) { //loop through csv $updatesql = "UPDATE t SET `value` = ".$data[2]." WHERE fielddef_id = ".$stocks[$data[0]]." AND product_id = ".$data[1]; echo "$updatesql<br>";//for debug only comment out on live } 

Вам не нужно делать свой первоначальный выбор, так как вы только когда-либо устанавливаете свои данные продукта на 1 в любом случае в своем коде, и из вашего описания видно, что ваш идентификатор вашего продукта всегда корректен именно для вашего столбца fielddef, в котором есть карта.

Также просто для жизни не забудьте поставить свою фактическую команду выполнения mysqli на ваш $ updatesql;

Чтобы дать вам сравнение с фактическим кодом использования (я могу сравнить с этим!) Это код, который я использую для импортера загруженного файла (его не идеально, но он выполняет свою работу)

 if (isset($_POST['action']) && $_POST['action']=="beginimport") { echo "<h4>Starting Import</h4><br />"; // Ignore user abort and expand time limit //ignore_user_abort(true); set_time_limit(60); if (($handle = fopen($_FILES['clientimport']['tmp_name'], "r")) !== FALSE) { $row = 0; //defaults $sitetype = 3; $sitestatus = 1; $startdate = "2013-01-01 00:00:00"; $enddate = "2013-12-31 23:59:59"; $createdby = 1; //loop and insert while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) { // loop through each line of CSV. Returns array of that line each time so we can hard reference it if we want. if ($row>0) { if (strlen($data[1])>0) { $clientshortcode = mysqli_real_escape_string($db->mysqli,trim(stripslashes($data[0]))); $sitename = mysqli_real_escape_string($db->mysqli,trim(stripslashes($data[0]))." ".trim(stripslashes($data[1]))); $address = mysqli_real_escape_string($db->mysqli,trim(stripslashes($data[1])).",".trim(stripslashes($data[2])).",".trim(stripslashes($data[3]))); $postcode = mysqli_real_escape_string($db->mysqli,trim(stripslashes($data[4]))); //look up client ID $client = $db->queryUniqueObject("SELECT ID FROM tblclients WHERE ShortCode='$clientshortcode'",ENABLE_DEBUG); if ($client->ID>0 && is_numeric($client->ID)) { //got client ID so now check if site already exists we can trust the site name here since we only care about double matching against already imported sites. $sitecount = $db->countOf("tblsites","SiteName='$sitename'"); if ($sitecount>0) { //site exists echo "<strong style=\"color:orange;\">SITE $sitename ALREADY EXISTS SKIPPING</strong><br />"; } else { //site doesn't exist so do import $db->execute("INSERT INTO tblsites (SiteName,SiteAddress,SitePostcode,SiteType,SiteStatus,CreatedBy,StartDate,EndDate,CompanyID) VALUES ('$sitename','$address','$postcode',$sitetype,$sitestatus,$createdby,'$startdate','$enddate',".$client->ID.")",ENABLE_DEBUG); echo "IMPORTED - ".$data[0]." - ".$data[1]."<br />"; } } else { echo "<strong style=\"color:red;\">CLIENT $clientshortcode NOT FOUND PLEASE ENTER AND RE-IMPORT</strong><br />"; } fcflush(); set_time_limit(60); // reset timer on loop } } else { $row++; } } echo "<br />COMPLETED<br />"; } fclose($handle); unlink($_FILES['clientimport']['tmp_name']); echo "All Imports finished do not reload this page"; } по if (isset($_POST['action']) && $_POST['action']=="beginimport") { echo "<h4>Starting Import</h4><br />"; // Ignore user abort and expand time limit //ignore_user_abort(true); set_time_limit(60); if (($handle = fopen($_FILES['clientimport']['tmp_name'], "r")) !== FALSE) { $row = 0; //defaults $sitetype = 3; $sitestatus = 1; $startdate = "2013-01-01 00:00:00"; $enddate = "2013-12-31 23:59:59"; $createdby = 1; //loop and insert while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) { // loop through each line of CSV. Returns array of that line each time so we can hard reference it if we want. if ($row>0) { if (strlen($data[1])>0) { $clientshortcode = mysqli_real_escape_string($db->mysqli,trim(stripslashes($data[0]))); $sitename = mysqli_real_escape_string($db->mysqli,trim(stripslashes($data[0]))." ".trim(stripslashes($data[1]))); $address = mysqli_real_escape_string($db->mysqli,trim(stripslashes($data[1])).",".trim(stripslashes($data[2])).",".trim(stripslashes($data[3]))); $postcode = mysqli_real_escape_string($db->mysqli,trim(stripslashes($data[4]))); //look up client ID $client = $db->queryUniqueObject("SELECT ID FROM tblclients WHERE ShortCode='$clientshortcode'",ENABLE_DEBUG); if ($client->ID>0 && is_numeric($client->ID)) { //got client ID so now check if site already exists we can trust the site name here since we only care about double matching against already imported sites. $sitecount = $db->countOf("tblsites","SiteName='$sitename'"); if ($sitecount>0) { //site exists echo "<strong style=\"color:orange;\">SITE $sitename ALREADY EXISTS SKIPPING</strong><br />"; } else { //site doesn't exist so do import $db->execute("INSERT INTO tblsites (SiteName,SiteAddress,SitePostcode,SiteType,SiteStatus,CreatedBy,StartDate,EndDate,CompanyID) VALUES ('$sitename','$address','$postcode',$sitetype,$sitestatus,$createdby,'$startdate','$enddate',".$client->ID.")",ENABLE_DEBUG); echo "IMPORTED - ".$data[0]." - ".$data[1]."<br />"; } } else { echo "<strong style=\"color:red;\">CLIENT $clientshortcode NOT FOUND PLEASE ENTER AND RE-IMPORT</strong><br />"; } fcflush(); set_time_limit(60); // reset timer on loop } } else { $row++; } } echo "<br />COMPLETED<br />"; } fclose($handle); unlink($_FILES['clientimport']['tmp_name']); echo "All Imports finished do not reload this page"; } 

Это импортировало 150 тыс. Строк за 10 секунд

Из-за ответов и комментариев по этому вопросу у меня есть решение. Основанием для этого является @Dave, я только обновил его, чтобы лучше ответить на вопрос.

 <?php require_once 'include.php'; // stock convert table (key is ID in CSV, value ID in database) $stocks = array( 1 => 22, 2 => 23, 3 => 24, 4 => 25, 5 => 26, 6 => 27, 7 => 28, 8 => 29, 9 => 30, 10 => 31 ); // product IDs in CSV (value) and Database (product_id) are different. We need to take both IDs from database and create an array of e-shop products $sql = mysql_query("SELECT product_id, value FROM cms_module_products_fieldvals WHERE fielddef_id = 1") or die(mysql_error()); while ($row = mysql_fetch_assoc($sql)) { $products[$row['value']] = $row['product_id']; } $handle = fopen('import.csv', 'r'); $i = 1; while (($data = fgetcsv($handle, 1000, ';')) !== FALSE) { $p_id = (int)$products[$data[1]]; if ($p_id > 0) { // if product exists in database, continue. Without this condition it works but we do many invalid queries to database (... WHERE product_id = 0 updates nothing, but take a time) if ($i % 300 === 0) { // optional, we'll see what it do with the real traffic sleep(1); } $updatesql = "UPDATE table SET value = " . (int)$data[2] . " WHERE fielddef_id = " . $stocks[$data[0]] . " AND product_id = " . (int)$p_id . " LIMIT 1"; echo "$updatesql<br>";//for debug only comment out on live $i++; } } // cca 1.5sec to import 100.000k+ records fclose($handle); 

Как я уже сказал в комментарии, используйте SPLFileObject для итерации по файлу CSV. Используйте подготовленные инструкции, чтобы снизить накладные расходы на производительность при вызове UPDATE в каждом цикле. Кроме того, объедините два ваших запроса вместе, нет причин потянуть все строки продукта первым и проверить их на CSV. Вы можете использовать JOIN, чтобы убедиться, что будут обновляться только те запасы во второй таблице, которые связаны с продуктом в первом, а это текущая строка CSV:

 /* First the CSV is pulled in */ $export_csv = new SplFileObject('export.csv'); $export_csv->setFlags(SplFileObject::READ_CSV | SplFileObject::DROP_NEW_LINE | SplFileObject::READ_AHEAD); $export_csv->setCsvControl(';'); /* Next you prepare your statement object */ $stmt = $mysqli->prepare(" UPDATE stocks, products SET value = ? WHERE stocks.fielddef_id = ? AND product_id = ? AND products.fielddef_id = 1 LIMIT 1 "); $stmt->bind_param('iis', $amount, $fielddef_id, $product_id); /* Now you can loop through the CSV and set the fields to match the integers bound to the prepared statement and execute the update on each loop. */ foreach ($export_csv as $csv_row) { list($stock_id, $product_id, $amount) = $csv_row; $fielddef_id = $stock_id + 21; if(!empty($stock_id)) { $stmt->execute(); } } $stmt->close(); 

Сделайте запрос больше, т. Е. Используйте цикл для компиляции большего запроса. Возможно, вам придется разбить его на куски (например, процесс 100 за раз), но, конечно же, не делать по одному запросу за раз (применяется для любого вида, вставки, обновления, даже при необходимости выберите). Это должно значительно повысить производительность.

Обычно рекомендуется, чтобы вы не запрашивали в цикле.

Обновление каждой записи каждый раз будет слишком дорого (в основном из-за поиска, но и от написания).

Сначала вы должны TRUNCATE таблицу, а затем снова вставить все записи (при условии, что внешние внешние ключи не будут связаны с этой таблицей).

Чтобы сделать это еще быстрее, вы должны заблокировать стол перед вставкой и разблокировать его потом. Это предотвратит возможность индексирования при каждой вставке.