Cause Analysis and Solution for slow mysql update speed

I increased the database content to million records yesterday, and found that the database was quite slow to update the database. I used the command to view more than N waiting information in mysql status, next I will introduce how to solve the slow update speed of mysql for large tables. If you encounter such problems, refer.

Let's take a look at the update syntax.

UPDATE

UPDATE is used to UPDATE data in a table. This syntax is similar to the second INSERT syntax. The table name and SET expression must be provided. You can add the WHERE clause to limit the range of updated records.

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 whose id is 123 in the users table to 24.

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

Similarly, you can use UPDATE to UPDATE values of multiple fields.

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

The above UPDATE statement uses WHERE to specify a condition. Otherwise, UPDATE updates the values of all records in the table.

There should be no problem with mysql for millions of data.

The problem with your SQL statement is that it is equivalent to modifying the AGE information of all records in the ONE table. The modification process is to query and modify the records in each ONE in TWO. In addition, there may be locks and other things.

First, this SQL statement should not appear in the business logic, but in the background job.

If you must do this, you can try the opposite method. If there are not many different records, find the records in the ONE table that are different from those in the TWO table, and then modify them, for example, it is like the following (the syntax may not be correct ):

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 set the data to, the update will fail. Let's analyze the cause.

Instance: Calculate the 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 filled:

The Code is as follows:	Copy code
Create table 'tmp _ open_ip '( 'Email 'varchar (60) not null default '', 'Address' varchar (50) not null default '', 'IP' int (10) unsigned not null default '0 ', KEY 'email '('email '), KEY 'IP' ('IP ') ) ENGINE = MyISAM default charset = utf8

Another IP address database table (0.44 million records)

 

The Code is as follows:	Copy code
Create table 'IP '( 'S 'int (10) unsigned not null default '0' comment' start ip ', 'E' int (10) unsigned not null default '0' comment' end ip ', 'A' varchar (50) not null default '', KEY's '('s '), KEY 'E' ('E ') ) ENGINE = MyISAM default charset = utf8

You need to query the corresponding geographic address in the ip address database table based on the ip field in the user log table tmp_open_ip, and fill the address field in the address field.

Run 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.

I ran very slowly on my computer, and it took more than an hour (4500 s) to complete the execution, and I don't know how long it would take.

I can't see it, so I thought it would be faster to use insert, So I re-imported a table tmp_open_log, which is exactly the same as tmp_open_log.

Create a table tmp_open_address, which is the target table of insert. to speed up, no index is created:

The Code is as follows:	Copy code
Create table 'tmp _ open_address '( 'Email 'varchar (60) not null default '', 'Address' varchar (50) not null default '', 'IP' int (10) unsigned not null default '0' ) ENGINE = MyISAM default charset = utf8

Execute the 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 .*/

Less than 17 s! I wanted to pour a cup of water and take a break. The result has been completed.

By the time this article was completed, the previous update statement had been executed for 5000 s, and the end was still far from being completed.

Therefore, when executing an update statement on a large amount of data, you can use an insert statement to implement the update statement. This may be troublesome, but the benefits brought by high speed are far greater than the troubles!

Postscript:

Directly killed the update process and checked how much update was executed: Run

SELECT * FROM 'tmp _ open_ip 'where address! =''

The result is only 11,373. At this speed, it takes N days to run ....