Как оптимизировать этот PHP код вставки?
У меня есть база данных mysql, и я хочу вставить в нее около 40 000 строк из кода PHP, но мой код занимает более 15 минут, чтобы вставить строки, есть ли шансы его оптимизировать? где моя проблема (PHP код / дизайн базы данных)?
вот подробности:
— данные строки в текстовом файле utf-8 хранятся значения, разделенные символом табуляции «\ t», и каждая строка устанавливается в одной строке файла, как это
строковое представление:
"value1\tvalue2\tvalue3\value4\value5\r\nvalue1\tvalue2\tvalue3\value4\value5\r\nvalue1\tvalue2\tvalue3\value4\value5\r\nvalue1\tvalue2\tvalue3\value4\value5\r\n"просмотр текста:
value1 value2 value3 value4 value5
value1 value2 value3 value4 value5
value1 value2 value3 value4 value5
value1 value2 value3 value4 value5
-база данных имеет 3 таблицы как это:
table1 countries fields(1) (NAME varchar -primarykey-)
table2 products fields(2) (HS varchar - primarykey-, NAME varchar)
table3 imports fields (6) (product_hs varchar -foreignkey->products(HS),
counteryname varchar - foreignkey->countries (NAME),
year year,
units int,
weight int,
value int)
— PHP-код было так
$conn = new mysqli($hn,$un,$pw,$db);
if($conn->connect_error) {die($conn->connect_error);}
$x = 0; // row counter
ini_set('max_execution_time', 3000);
while(!feof($filehandle)){
$x++;
echo $x . ": ";
$fileline = fgets($filehandle);
$fields = explode("\t", $fileline);
$query = "INSERT INTO imports(product_hs,counteryname,year,units,weight,value) VALUES(" . "'" . $fields[0] ."','". $fields[1] . "','2014','". $fields[2] . "','" . $fields[3] . "','" . $fields[4] . "');";
$result = $conn->query($query);
if(!$result) {
echo $conn->error . "</br>";
}else{
echo $result . "</br>";
}
};
Сначала я подумал, что это проблема с индексами, которая замедляет вставку, поэтому я удалил все индексы из таблицы «import», но она не пошла быстрее !!
проблема в дизайне базы данных или в моем php-коде?
также обратите внимание, что браузер уведомляет «ожидание ответа от сервера» в течение первых 5 минут, затем большая часть оставшегося времени уведомляет «передачу данных с сервера», это потому, что html-ответ содержит более 40 000 строк для счетчик строк1:1 </br> 2:1 </br> .....(объявлено в коде php)?
пожалуйста, учтите, что я очень новичок, спасибо.
Решение
большое спасибо вам mr.tadman а также мистер Ханлет Эсканьо а также mr.Uueerdo а также Джули Пеллетье а также mr.Solarflare
за помощь мне в комментариях.
Я сделал 3 разных изменения в своем php-коде, используя подходы, которые вы предложили в комментариях, затем я проверил результаты и вот результаты тестов.
заключение 3-х тестов: как предположил г-н Тадман, ключ находится в НАГРУЗКА ДАННЫХ ИНФИЛЬ . это значительно сократило время выполнения до менее чем 7 секунд, и это 3 теста.
ОРИГИНАЛЬНЫЙ КОД: ~ 26 минут
ТЕСТ 1: ~ 34 минуты
(как г-н.Uueerdo предложил я удалил echo операторы и счетчик строк из цикла)
while(!feof($filehandle)){
// $x++; // commented out
//echo $x . ": "; // commented out
$fileline = fgets($filehandle);
$fields = explode("\t", $fileline);
$query = "INSERT INTO products(hs,arabicname,englishname) VALUES(" . "'" . str_replace("'", ".", $fields[0]) ."'," . "'". str_replace("'", ".", $fields[1]) . "'," . "'". str_replace("'", ".", $fields[2]) . "');";
$result = $conn->query($query);
/* // commented out
if(!$result) {echo $conn->error . "</br>";}
}else{echo $result . "</br>";}
*/};
ТЕСТ 2: ~ 7 секунд
(как г-н.Тадман сказал, что я искал НАГРУЗКА ДАННЫХ ИНФИЛЬ и это было супер мощным
//replace the entire loop with this simple query
$query = "LOAD DATA LOCAL INFILE'" .
addslashes("C:\\xampp\\htdocs\\bots\\impandexp\\imports.txt")
. "' INTO TABLE imports FIELDS TERMINATED BY '\t' LINES TERMINATED BY
'\r\n'(product_hs,counteryname,units,weight,value) SET year = '2014';";
$result = $conn->query($query);
ТЕСТ 3: ~ 5 секунд
это было то же самое, что и тест 2, за исключением того, что я нашел полезные советы на той же странице, которые дает mr.tadman, которые помогают максимизировать скорость для Массовая загрузка данных для таблиц InnoDB
// turning off index checks that might slows down bulk data insertion
$query = "SET foreign_key_checks=0;";
$conn->query($query);
$query = "SET unique_checks=0;";
$conn->query($query);
$query ="SET autocommit=0;";
$conn->query($query);
$query = "LOAD DATA LOCAL INFILE'" . addslashes("C:\\xampp\\htdocs\\bots\\impandexp\\imports.txt") . "' INTO TABLE imports FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'(product_hs,counteryname,units,weight,value) SET year = '2014';";
$result = $conn->query($query);
echo $result . "</br>";
// turning them on again
$query = "SET foreign_key_checks=1;";
$conn->query($query);
$query = "SET unique_checks=1;";
$conn->query($query);
$query ="COMMIT;";
$conn->query($query);
Другие решения
Других решений пока нет …