PHP imports large amounts of data into mysql performance optimization_PHP tutorial

In mysql, we combine php to import some files into mysql. Now I will share my analysis and optimization of 15,000 records when importing. Friends can refer to it.

There were a few articles in the past that mentioned that Tiandi was recently helping a friend to do a small project for counting phone numbers. Each time, packaged phone numbers were randomly generated from the database according to the needs, and then people were constantly calling these phone numbers for sales. Products (a little contempt for such behavior). But if a friend asks for help, we have to help, right? The program was completed two weeks ago and was handed over after testing. A few days ago, a friend called me and said that it takes longer and longer to import phone numbers every day. Sometimes it takes more than half an hour to import 10,000 records. I want to see if I can find a way to improve the speed.

I thought about it for a while. The database structure is very simple. It can be considered that there are only two fields. One field stores the phone number, and the other field stores the category. The categories are c, d, e, etc., which represent that the call has been made. Phone, this phone has not been dialed, this phone has not been dialed, etc., and the entire program logic is like this:

■Get a txt file with phone numbers stored in it
■Import txt files into mysql through the program
■When importing, check whether the phone number in txt is repeated with the one in mysql. If it is not repeated, insert a new record directly. If it is repeated, you need to update it according to the category to which the phone number belongs.
Since each phone number in txt needs to be compared when it is imported, the program will definitely take some time. Let’s put this reason aside for now, because the title of this article is to optimize the writing speed, so when will the program write Where are the records? From the above logic, we know that when matching the database, a write operation to the database will occur when no record is found (of course, update also counts, but only insert is discussed here). Then convert the above logic into code, which is almost as follows:

$tmpstr = "'". $array[$i] ."','". $str ."'";           $sql="INSERT INTO ".$usertable." (tel,type) VALUES (".$tmpstr.")";

The code is as follows

代码如下	复制代码
//$array为txt文件explode出来的数组，每一个为一个电话号码, $str为类型 for($i=0; $i     {         $tmpstr = "'". $array[$i] ."','". $str ."'";         $sql="INSERT INTO ".$usertable." (tel,type) VALUES (".$tmpstr.")";         mysql_query($sql);     }

Copy code

代码如下	复制代码
$sql2="INSERT INTO ".$usertable." (tel,type,updatetime) VALUES"; for($i=0; $i     {         $tmpstr = "'". $array[$i] ."','". $str ."'";  $sql2 .= "(".$tmpstr."),";     } $sql2 = substr($sql2,0,-1);   //去除最后的逗号 mysql_query($sql2);

//$array is the array exploded from the txt file, each one is a phone number, $str is the type

for($i=0; $i {

mysql_query($sql); } The above code is completely correct, but it is inefficient. When the txt file contains tens of thousands of phone numbers, there will be tens of thousands of database insertion operations. Although each database write operation is very fast, But when tens of thousands of items are accumulated, the execution time cannot be ignored. Tiandi briefly tested inserting 150 million records, which took about 5 minutes. If you add in the previous logical judgment and other processes, then half an hour is really not that long. This is not possible. The number of writes to the database must be reduced, so the above code is changed to the following:

The code is as follows	Copy code
$sql2="INSERT INTO ".$usertable." (tel,type,updatetime) VALUES"; for($i=0; $i http://www.bkjia.com/PHPjc/630676.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/630676.htmlTechArticleIn mysql, we combine php to import some files into mysql. Now I will share my analysis of 15,000 records. Analysis and optimization during import, friends can refer to it. There were a few articles before, saying...