PHP Import a lot of data to MySQL performance optimization tips, MySQL performance optimization _php tutorial

PHP imports a lot of data to MySQL performance optimization tips, MySQL performance optimization

The example in this article describes how PHP imports a lot of data to MySQL performance optimization techniques. Share to everyone for your reference. The specific analysis is as follows:

In MySQL we combine PHP to import some files into MySQL, here to share my 15,000 records in the Import Analysis and optimization, the need for friends can refer to.

Before a few articles, said the recent tiandi to help a friend to do a small project, for the statistical telephone number, each time according to demand from the database randomly generated packaged phone numbers, and then keep people to call these phone numbers to promote products (small despise such behavior). But friends ask for help, we can not help Ah, right. The procedure was completed two weeks ago and the completion of the test. A few days ago friends Call said, the daily import phone numbers more and more time, sometimes 10,000 records will be more than half an hour, see if you can find ways to improve this speed.

I took a look at the idea, the database structure is very simple, you can think of two fields, a word Gencun phone number, another word Gencun category, the categories are c,d,e and so on, respectively, has dialed through this phone, not dialed through this phone, did not dial this phone and so on, and the whole program logic is such.

Get a TXT file with a phone number in it

Import txt file into MySQL via program

When importing, the detection of the TXT phone number and MySQL in the duplicate, if not repeat, insert new records directly, if repeated, you need to determine the number of phone numbers belong to the category to be updated.

Since each TXT phone number in the import, you need to do a comparison, so the program will certainly take some time, here we put aside this reason, because the title of this article is to optimize the write speed, then when the program will write records? By the logic above, when the database is matched, no record is found when there is a write to the database operation (of course, the update is also counted, only the insert is discussed here), then the above logic into code, almost as follows:
Copy CodeThe code is as follows://$array for TXT file explode out of the array, each for a phone number, $str for 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 inefficient, when the TXT file contains tens of thousands of phone numbers, there will be tens of thousands of insert database operations, although every time the database write operations are very fast, but tens of thousands of accumulated, this execution time can not be ignored, Tiandi simple test to insert 150 million records, time-consuming almost 5 minutes, if coupled with the previous logic judgment and so on, then half an hour is really not very small, this can not, you must reduce the database library write times, then the above code changed to the following:
Copy CodeThe code is as follows: $sql 2= "INSERT into". $usertable. " (Tel,type,updatetime) VALUES ";
for ($i =0; $i
{
$tmpstr = "'". $array [$i]. "', '". $str. "'";
$sql 2. = "(". $tmpstr. "),";
}
$sql 2 = substr ($sql 2,0,-1); Remove the last comma
mysql_query ($sql 2);
In this way, the entire write operation only 1 times, greatly shorten the execution time, almost 10 seconds to complete 15,000 records, well, this article to the end, if you also encounter a large amount of data to MySQL time-consuming problem, try this article optimization method.

I hope this article is helpful to everyone's PHP programming.

http://www.bkjia.com/PHPjc/934937.html www.bkjia.com true http://www.bkjia.com/PHPjc/934937.html techarticle PHP Import a lot of data to MySQL performance optimization techniques, MySQL performance optimization This article describes the PHP import a lot of data to MySQL performance optimization techniques. Share to everyone for your reference. Specific analysis ...

