PHP imports large amounts of data into mysql performance optimization tips, mysql performance optimization_PHP tutorial

PHP import large amounts of data into mysql performance optimization tips, mysql performance optimization

The example in this article describes performance optimization techniques for importing large amounts of data from PHP to MySQL. Share it with everyone for your reference. The specific analysis is as follows:

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

There were a few articles before, and it was said 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 making these calls. Phone numbers sell 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, it needs to be updated 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 aside this reason first, because the title of this article is to optimize the writing speed, so when will the program Will the record be written? From the above logic, we know that when matching the database, a write to the database operation will occur when no record is found (of course update is also counted, but only insert is discussed here), then convert the above logic into code, which is almost as follows:

Copy code The code is as follows:

//$array is the array exploded from the txt file, each one is a phone number, $str is the type
for($i=0; $i {
$tmpstr = "'". $array[$i] ."','". $str ."'";
          $sql="INSERT INTO ".$usertable." (tel,type) VALUES (".$tmpstr.")";
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 tens of thousands of Accumulating records, this execution time cannot be ignored. Tiandi simply tested inserting 150 million records, which took almost 5 minutes. If you add in the previous logical judgment and other processes, then half an hour is really not too long. , this is not possible, the number of writes to the database must be reduced, so the above code is changed to the following:

Copy code The code is as follows:

$sql2="INSERT INTO ".$usertable." (tel,type,updatetime) VALUES";
for($i=0; $i {
$tmpstr = "'". $array[$i] ."','". $str ."'";
$sql2 .= "(".$tmpstr."),";
}
$sql2 = substr($sql2,0,-1); //Remove the last comma
mysql_query($sql2);

In this way, the entire write operation is only once, which greatly shortens the execution time. It takes almost 10 seconds to complete 15,000 records. Well, this article ends here. If you also encounter the problem of long time writing a large amount of data to mysql If so, why not try the optimization methods in this article.

I hope this article will be helpful to everyone’s PHP programming design.

http://www.bkjia.com/PHPjc/934937.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/934937.htmlTechArticlePHP import large amounts of data into mysql performance optimization tips, mysql performance optimization This article describes the performance of php importing large amounts of data into mysql Optimization tips. Share it with everyone for your reference. Detailed analysis...