MySQL update speed slow cause analysis and solution

Let's take a look at the update syntax first.

UPDATE

The update feature is updating the data in the table. This syntax is similar to the second use of insert. You must provide a table name and a set expression, where you can add a where to limit the updated record range.

The code is as follows	Copy Code
UPDATE table_anem SET column_name1 = value1, column_name2 = value2, where;

The following statement changes the age of the record with ID equals 123 in the Users table to 24

The code is as follows	Copy Code
UPDATE users SET age = the WHERE id = 123;

Similarly, you can use update to update the values of multiple fields

The code is as follows	Copy Code
UPDATE users SET age =, name = ' Mike ' WHERE id = 123;

The above UPDATE statement specifies a condition through the where, otherwise, update updates the values of all records in the table

Millions other data, there should be no problem with MySQL.

The problem with your SQL is that it's equivalent to changing the age information for all the records in one table, and the process of modifying it is to two the inside of each record, and then modify it. Also, there is a good chance that there will be locks and things like that.

First of all, this SQL should not appear in the business logic, but should be in the background job.

If you have to do this, try the opposite way, if the different records are not particularly numerous, then find a record of the age record in one table that is not the same as the two table, and then modify it, for example, probably like the following (probably not the right syntax):

The code is as follows	Copy Code
Update One,two Set one. Age=two. Age where One.id=two.id and one. Age!= two. Age

, when I transferred the data to 1000W will not be updated, I will analyze the reasons below.

Instance: needs to compute its geographic address based on the IP address of the user log

Table structure:

User Log table (2 million records), where address is the field to be populated:

The code is as follows	Copy Code
CREATE TABLE ' Tmp_open_ip ' ( ' Email ' varchar not NULL DEFAULT ', ' Address ' varchar ' is not NULL DEFAULT ', ' IP ' int unsigned not NULL DEFAULT ' 0 ', KEY ' email ' (' email '), KEY ' IP ' (' IP ') ) Engine=myisam DEFAULT Charset=utf8

Another IP Address database table (440,000 records)

The code is as follows	Copy Code
CREATE TABLE ' IP ' ( ' s ' int unsigned not NULL DEFAULT ' 0 ' COMMENT ' Start IP ', ' e ' int unsigned not NULL DEFAULT ' 0 ' COMMENT ' End IP ', ' A ' varchar not NULL DEFAULT ', KEY ' s ' (' s '), KEY ' E ' (' E ') ) Engine=myisam DEFAULT Charset=utf8

According to the User Log table tmp_open_ip IP field in the IP address database to query the corresponding geographical address, fill the Address field.

Execute using the following UPDATE statement:

The code is as follows	Copy Code
UPDATE tmp_open_ip as U INNER JOIN IP On U.ip BETWEEN Ip.s and IP.E SET u.address = Ip.a

In the author's computer run very slow, one hours (4500s) did not finish, also do not know how long.

Can not see the past, so think of the use of inserts will be faster, and then a table tmp_open_log and tmp_open_log exactly the same.

Create a table tmp_open_address, is the target table for inserts, for faster, no indexing:

The code is as follows	Copy Code
CREATE TABLE ' tmp_open_address ' ( ' Email ' varchar not NULL DEFAULT ', ' Address ' varchar ' is not NULL DEFAULT ', ' IP ' int (a) unsigned not NULL DEFAULT ' 0 ' ) Engine=myisam DEFAULT Charset=utf8

Execute INSERT statement

The code is as follows	Copy Code
Insert into tmp_open_address (EMAIL,ADDRESS,IP) Select L.email,ip.a,l.ip From Tmp_open_log as l inner join IP on L.ip between IP.S and IP.E; /* Affected rows:2,543,124 Found rows:0 warnings:0 Duration for 3 queries:16.922 sec. * *

Not to 17s! Originally also want to go to pour a cup of water, take a break, the result has been completed.

By the time this article was finished, the previous UPDATE statement had been executed 5000s, and the end was still elusive.

So, for large amounts of data to execute update, you can consider using the INSERT statement to achieve, may be troublesome, but the high speed of the benefits of more than trouble!

Postscript:

Directly kills the update process, to see how much update executed: Run

SELECT * from ' tmp_open_ip ' where address!= '

The result is only 11,373, according to this speed, to run n days ....