如何优化这个sql插入php代码? [英] how to optimize this sql insertion php code?

问题描述

我有一个 mysql 数据库，我想从 PHP 代码中插入大约 40'000 行，但我的代码需要超过 15 分钟才能插入行，有没有机会优化它? 我的问题在哪里(PHP 代码/数据库设计)?

详情如下:

-行数据存储在一个utf-8 txt文件中，值以\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值1 值2 值3 值4 值5值1 值2 值3 值4 值5值1 值2 值3 值4 值5

-数据库有 3 个表，如下所示:

table1 countries fields(1) (NAME varchar -primarykey-)table2 products fields(2) (HS varchar - primarykey-, NAME varchar)table3 导入字段 (6) (product_hs varchar -foreignkey->products(HS),counteryname varchar -外键->国家(名称)，年年，单位整数，重量整数，值整数)

- php代码就是这样

$conn = new mysqli($hn,$un,$pw,$db);if($conn->connect_error) {die($conn->connect_error);}$x = 0;//行计数器ini_set('max_execution_time', 3000);while(!feof($filehandle)){$x++;回声 $x .:"；$fileline = fgets($filehandle);$fields = expand("\t", $fileline);$query = "INSERT INTO import(product_hs,counteryname,year,units,weight,value) VALUES(" . "'" . $fields[0] ."','". $fields[1] . "','2014','". $fields[2] . "','" . $fields[3] . "','" . $fields[4] . "');";$result = $conn->query($query);如果(！$结果){回声 $conn-> 错误."</br>";}别的{回声 $result ."</br>";}};

首先我认为是索引问题导致插入速度变慢，所以我从导入"表中删除了所有索引，但速度并没有变快！！是数据库设计的问题还是我的php代码的问题?

还要注意，浏览器在前 5 分钟通知等待服务器的响应"，然后大部分剩余时间通知从服务器传输数据"，这是因为响应 html 有超过40'000 行用于行计数器1:1 </br>2:1 </br>.....(在php代码中声明)?

请认为我是新手，谢谢.

解决方案

非常感谢

<小时>

测试 1: ~ 34 分钟

(如先生(正如 它与测试 2 相同，只是我在 mr.tadman 提供的同一页面上找到了有用的提示，这有助于最大限度地提高 InnoDB 表的批量数据加载

//关闭可能减慢批量数据插入速度的索引检查$query = "SET foreign_key_checks=0;";$conn->查询($查询)；$query = "SET unique_checks=0;";$conn->查询($查询)；$query ="SET autocommit=0;";$conn->查询($查询)；$query = "LOAD DATA LOCAL INFILE'" .addlashes("C:\\xampp\\htdocs\\bots\\impandexp\\imports.txt") ."' INTO TABLE 导入 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'(product_hs,counteryname,units,weight,value) SET year = '2014';";$result = $conn->query($query);回声 $result ."</br>";//再次打开它们$query = "SET foreign_key_checks=1;";$conn->查询($查询)；$query = "SET unique_checks=1;";$conn->查询($查询)；$query ="提交;";$conn->查询($查询)；

I have mysql database and I want to insert about 40'000 rows into it from PHP code , but my code takes more than 15 minutes to insert the rows, is there any chances to optimize it? where is my problem(PHP code / database design) ?

here are the details:

- the row data are stored in a utf-8 txt file the values separated by "\t" tab character and every row sets in one line of the file, like this

string view:

"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"

text reader view:

value1     value2     value3     value4     value5
value1     value2     value3     value4     value5
value1     value2     value3     value4     value5
value1     value2     value3     value4     value5

-the data base has 3 tables as this:

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 code was like this

$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>";
}
};

first I thought it is an index problem that slows down the insertion , so I removed all the indexes from "imports" table , but it didn't go faster!! is the problem from the database design or from my php code?

also note that the browser is notifying "waiting for response from the server" for the first 5 minutes then most of the remaining time is notifying "transferring data from server", is this because the response html has more than 40'000 line for the row counter1:1 </br> 2:1 </br> .....(declared in the php code)?

please consider I'm very newbie, thanks.

解决方案

thank you very much mr.tadman and mr.Hanlet Escaño and mr.Uueerdo and mr.Julie Pelletier and mr.Solarflare for helping me in the comments.

I did 3 different changes in my php code using the approaches you suggested in the comments , then I tested the results and here are the tests results.

the conclusion of the 3 tests: as mr.tadman suggested , the key is in LOAD DATA INFILE . it dramatically reduced the execution time to less than 7 seconds, and these are the 3 tests.

---

ORIGINAL CODE: ~ 26 minutes

---

TEST 1 : ~ 34 minutes

(as mr.Uueerdo suggested I removed the echo statements and the rows counter from the loop)

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>";}
*/};

---

TEST 2 : ~ 7 seconds

(as mr.tadman said I searched for LOAD DATA INFILE and it was super powerful

//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);

---

TEST 3 : ~ 5 seconds it was the same as test 2 except that I found useful tips on the same page that mr.tadman give , that help in maximizing the speed for Bulk Data Loading for InnoDB Tables

// 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);

这篇关于如何优化这个sql插入php代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！