То, что я делаю прямо сейчас, – это чтение содержимого текстового файла и сохранение его в переменной. После прочтения всего содержимого я запустил цикл для данных chunk и там вызываю функцию, которая будет считывать каждую строку данных chunk и передает каждую строку другой функции, обрабатывающей обработку каждого столбца данных и вставляя его в база данных по пакету. Партия – это весь кусок.
Слишком длинный код для каждого файла размером более 500 КБ. Моя проблема заключается в том, что в текстовом файле нет уникального идентификатора, который я могу использовать, чтобы применить «LOAD DATA INFILE», который поставил меня в эту ситуацию обработки текстового файла кусками.
700K занял почти целый день в обработке, но все же это зависит от характеристик машины. Код работает в CentOS. После обработки первого текстового файла следующий текстовый файл размером 800 КБ ++ занял почти неделю, чтобы обработать. В них идет с другим текстовым файлом размером более 800 КБ, занимает почти или больше недели, чтобы обрабатывать особенно файл размером 1 МБ.
Может кто-нибудь сказать мне, что я делаю неправильно, и каковы параметры, которые мне нужны, чтобы мой код работал эффективно.
/* ==================================================================================== RECORDS FETCH ==================================================================================== Needs path and filename with extension. The page do an iteration of records in the file by line. After by line, it iterates again per delimiter ",".. It concatenates the part of the records for bulk insert process. PID address is incremental, every three PID correspond to one Chamber and the reading in each Chamber is CO2 for first PID address, RH for the second PID address, TEMP for the third PID address. ==================================================================================== */ $path = ""; $filename = ""; error_reporting(0); include_once ("connect.php"); $p_results = mysql_query("SELECT PATH, FILENAME FROM tbl_path"); if(mysql_num_rows($p_results) > 0 ){ while ( $rows = mysql_fetch_assoc($p_results) ) { $path = $rows['PATH']; $filename = $rows['FILENAME']; } } else { mysql_query("INSERT INTO tbl_log (LOG_DATE, DETAILS) VALUES ( NOW(), 'There is no path and filename to be accessed. Please provide.' )"); } $path = str_replace('\\','/',$path); //holds the path..NOTE: Change backslash (\) to forward slash (/) //$path = "E:/"; //holds the filename.. NOTE: Include the file extension //$filename = "sample2.txt"; //"chamber_1_con.txt"; if ($path <> "" && $filename <> "") is_connected($path, $filename); echo ('<script language="javascript">window.location = "chambers_monitoring.php" </script>'); //function for DB writing in table data function InsertData($rec, &$errorDataCnt, &$sql, $y, $i, $x, &$dCnt) { $dDate = (!isset($rec[0]) ? 0 : (trim($rec[0]) == "" ? 0 : trim($rec[0]))); $dTime = (!isset($rec[1]) ? 0 : (trim($rec[1]) == "" ? 0 : trim($rec[1]))); $address = (!isset($rec[2]) ? 0 : (trim($rec[2]) == "" ? 0 : trim($rec[2]))); $co2SV = (!isset($rec[3]) ? 0 : (trim($rec[3]) == "" ? 0 : trim($rec[3]))); $co2PV = (!isset($rec[4]) ? 0 : (trim($rec[4]) == "" ? 0 : trim($rec[4]))); $tempSV = (!isset($rec[5]) ? 0 : (trim($rec[5]) == "" ? 0 : trim($rec[5]))); $tempPV = (!isset($rec[6]) ? 0 : (trim($rec[6]) == "" ? 0 : trim($rec[6]))); $rhSV = (!isset($rec[7]) ? 0 : (trim($rec[7]) == "" ? 0 : trim($rec[7]))); $rhPV = (!isset($rec[8]) ? 0 : (trim($rec[8]) == "" ? 0 : trim($rec[8]))); /* include('connect.php'); */ set_time_limit(36000); ini_set('max_execution_time','43200'); $e_results = mysql_query("SELECT ID FROM tbl_reading WHERE (READING_DATE = '".date("Ymd",strtotime($dDate))."' AND READING_TIME = '".date("H:i:s",strtotime($dTime))."') AND READING_ADDRESS = $address LIMIT 1"); if(mysql_num_rows($e_results) <= 0 ){ if (!($dDate == 0 || $dTime == 0 || $address == 0) ) { if ($y == 0){ $sql = "INSERT INTO tbl_reading (READING_DATE, READING_TIME, READING_ADDRESS, CO2_SET_VALUE, CO2_PROCESS_VALUE, TEMP_SET_VALUE, TEMP_PROCESS_VALUE, RH_SET_VALUE, RH_PROCESS_VALUE) VALUES ('".date("Y/m/d",strtotime($dDate))."','".date("H:i:s",strtotime($dTime))."', ". mysql_real_escape_string($address).",". mysql_real_escape_string($co2SV).",". mysql_real_escape_string($co2PV).",". mysql_real_escape_string($tempSV).",". mysql_real_escape_string($tempPV).",". mysql_real_escape_string($rhSV).",". mysql_real_escape_string($rhPV).")"; } else { $sql .= ", ('".date("Y/m/d",strtotime($dDate))."','".date("H:i:s",strtotime($dTime))."', ". mysql_real_escape_string($address).",". mysql_real_escape_string($co2SV).",". mysql_real_escape_string($co2PV).",". mysql_real_escape_string($tempSV).",". mysql_real_escape_string($tempPV).",". mysql_real_escape_string($rhSV).",". mysql_real_escape_string($rhPV).")"; } } } if(($x + 1) == $i){ //echo ($x + 1)." = ".$i."<br>"; if (substr($sql, 0, 1) == ",") $sql = "INSERT INTO tbl_reading (READING_DATE, READING_TIME, READING_ADDRESS, CO2_SET_VALUE, CO2_PROCESS_VALUE, TEMP_SET_VALUE, TEMP_PROCESS_VALUE, RH_SET_VALUE, RH_PROCESS_VALUE) VALUES".substr($sql, 1); //echo $sql."<br>"; set_time_limit(36000); try { $result = mysql_query($sql) ; $dCnt = mysql_affected_rows(); if( $dCnt == 0) { $errorDataCnt = $errorDataCnt + 1; } } catch (Exception $e) { mysql_query("INSERT INTO tbl_log (LOG_DATE, DETAILS) VALUES ( NOW(), '".$e->getMessage()."' )"); } //mysql_free_result($result); } unset($dDate); unset($dTime); unset($address); unset($co2SV); unset($co2PV); unset($tempSV); unset($tempPV); unset($rhSV); unset($rhPV); } //function for looping into the records per line function loop($data) { $errorDataCnt = 0; $sql = ""; $exist = 0; $i = count( $data); $x = 0; $y = 0; $tmpAdd = ""; $cnt = 0; $t = 0; $dCnt = 0; ini_set('max_execution_time','43200'); while($x < $i) { $rec = explode(",", $data[$x]); InsertData($rec, $errorDataCnt, $sql, $y, $i, $x, $dCnt); $x++; $y++; unset($rec); } $errFetch = ($i - $dCnt); if($errorDataCnt > 0) mysql_query("INSERT INTO tbl_log (LOG_DATE, DETAILS) VALUES ( NOW(), 'Error inserting $errFetch records. Check if there is a NULL or empty value or if it is the correct data type.' )"); if($dCnt > 0) mysql_query("INSERT INTO tbl_log (LOG_DATE, DETAILS) VALUES ( NOW(), 'Saved $dCnt of $i records into the database. Total $exist records already existing in the database.' )"); } // functions in looping records and passing into $contents variable function DataLoop($file) { ini_set("auto_detect_line_endings", true); set_time_limit(36000); ini_set('max_execution_time','43200'); $contents = ''; $j = 0; if ($handle = fopen($file,"rb")){ while (!feof($handle)) { $rdata = fgets($handle, 3359232);//filesize($file)); //$rdata = fread($handle, filesize($file)); if(trim($rdata) != "" || $rdata === FALSE){ if (feof($handle)) break; else { $contents .= $rdata; $j = $j + 1; }} } fclose($handle); $data = explode("\n", $contents); unset($contents); unset($rdata); } /* echo count($contents)." ".count($data); */ /* $query = "SELECT MAX(`ID`) AS `max` FROM `tbl_reading`"; $result = mysql_query($query) or die(mysql_error()); $row = mysql_fetch_assoc($result); $max = $row['max']; */ /* $res = mysql_fetch_assoc(mysql_query("SELECT COUNT(*) as total FROM tbl_reading")) or die(mysql_error()); echo "<script>alert('".$res['total']."')</script>"; */ $p = 0; ini_set('memory_limit','512M'); if($j != 0) { foreach(array_chunk($data, ceil(count($data)/200)) as $rec_data){ loop($rec_data); $p++; } } } //function to test if filename exists function IsExist($file) { if ($con = fopen($file, "r"))// file_exists($file)) { fclose($con); DataLoop($file); } else mysql_query("INSERT INTO tbl_log (LOG_DATE, DETAILS) VALUES ( NOW(), '$filename is not existing in $path. Check if the filename or the path is correct.' )"); } //function to test connection to where the file is. function is_connected($path, $filename) { //check to see if the local machine is connected to the network $errno = ""; $errstr = ""; if (substr(trim($path), -1) == '/') $file = $path.$filename; else $file = $path."/".$filename; IsExist($file); } Из вашего кода кажется, что ваш «уникальный идентификатор» (для целей этой вставки, по крайней мере) является составным (READING_DATE, READING_TIME, READING_ADDRESS) .
Если вы определяете такой ключ UNIQUE в своей базе данных, то LOAD DATA с ключевым словом IGNORE должны выполнять именно то, что вам нужно:
ALTER TABLE tbl_reading ADD UNIQUE KEY (READING_DATE, READING_TIME, READING_ADDRESS) ; LOAD DATA INFILE '/path/to/csv' IGNORE INTO TABLE tbl_reading FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '' LINES TERMINATED BY '\r\n' (@rec_0, @rec_1, @rec_2, @rec_3, @rec_4, @rec_5, @rec_6, @rec_7, @rec_8) SET READING_DATE = DATE_FORMAT(STR_TO_DATE(TRIM(@rec_0), '???'), '%Y/%m/%d'), READING_TIME = DATE_FORMAT(STR_TO_DATE(TRIM(@rec_1), '???'), '%H:%i:%s'), READING_ADDRESS = TRIM(@rec_2), CO2_SET_VALUE = TRIM(@rec_3), CO2_PROCESS_VALUE = TRIM(@rec_4), TEMP_SET_VALUE = TRIM(@rec_5), TEMP_PROCESS_VALUE = TRIM(@rec_6), RH_SET_VALUE = TRIM(@rec_7), RH_PROCESS_VALUE = TRIM(@rec_8) ;
(Где '???' заменены строками, которые представляют формат даты и времени в вашем CSV).
Обратите внимание, что вы действительно должны хранить READING_DATE и READING_TIME вместе в одном столбце DATETIME или TIMESTAMP :
ALTER TABLE tbl_reading ADD COLUMN READING_DATETIME DATETIME AFTER READING_TIME, ADD UNIQUE KEY (READING_DATETIME, READING_ADDRESS) ; UPDATE tbl_reading SET READING_DATETIME = STR_TO_DATE( CONCAT(READING_DATE, ' ', READING_TIME), '%Y/%m/%d %H:%i:%s' ); ALTER TABLE tbl_reading DROP COLUMN READING_DATE, DROP COLUMN READING_TIME ;
В этом случае предложение SET команды LOAD DATA будет включать в себя:
READING_DATETIME = STR_TO_DATE(CONCAT(TRIM(@rec_0), ' ', TRIM(@rec_1)), '???')
Чтение строки в 1 Мб по строке занимает менее секунды. Даже конкатенация, а затем снова расщепление всех строк не занимает времени.
С помощью простого теста вставка 100 000 строк заняла около 90 секунд.
Но , делая запрос выбора перед вставкой, увеличивается время, требуемое более чем на порядок.
Урок, который можно извлечь из этого, заключается в том, что если вам нужно вставить большие объемы данных, сделайте это в больших кусках (см. LOAD DATA INFILE ). Если вы не можете сделать это по каким-либо причинам, сделайте только вставки и вставки.
Обновление :
Как уже указывал @eggyal, добавьте уникальный ключ в определение таблицы. В моем небольшом, одном столбце теста я добавил уникальный ключ и изменил insert чтобы insert ignore . Настенное время увеличилось на 15% -30% (~ 100-110 секунд), что намного лучше, чем увеличение до 38 минут (25 раз!) С отдельной вставкой +.
Итак, как вывод, (кража из eggyal) добавить
ALTER TABLE tbl_reading ADD UNIQUE KEY (READING_DATE, READING_TIME, READING_ADDRESS)
в таблицу, удалите select в InsertData() и insert чтобы insert ignore .
Перед запуском ваших вставок вам нужно сделать некоторые приготовления, потому что двигатель InnoDB делает вставки слишком медленными с настройками по умолчанию.
либо установите этот параметр перед вставкой
innodb_flush_log_at_trx_commit=0
или сделать все ваши вставки в одну транзакцию.
И он будет быстро вспыхивать, независимо от того, какой синтаксис или драйвер вы выберете.