Record MYSQL update optimization

: This article describes how to record MYSQL update optimization. For more information about PHP tutorials, see. Introduction

Today (August 5, 2015 PM), I made an adjustment to the structure of a table in the database, added several fields, and refreshed the previous data. the refresh content is: for an existing fieldurlAnd then update the newly added fields.typeAndtypeid. Later, I wrote a shell script to refresh the data. after running the shell script, I ran it. why is it so slow ~~~

Scenario reproduction

CREATE TABLE `fuckSpeed` (  `uin` bigint(20) unsigned NOT NULL DEFAULT 0,  `id` int(11) unsigned NOT NULL DEFAULT 0,  `url` varchar(255) NOT NULL DEFAULT '',  `type` int(11) unsigned NOT NULL DEFAULT 0,  `typeid` varchar(64) NOT NULL DEFAULT '',  ......  KEY `uin_id` (`uin`,`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The table structure is like this (many fields are omitted). The table has only one joint index.uin_idIn the update process, the following ideas are provided:

• Obtain a certain amount of data based on an id range.
  Select id, url from funkSpeed where id >=101 and id <=200;
  
• Traverse all data and update each data entry
  # First process the data and obtain the type and typeid matching
  Update fuckSpeed set type = [type], typeid = [typeid] where id = [id]
  After proceeding with the above ideas, we found that the update was particularly slow, with an average of 3 to 3 seconds ~ About five, I was also drunk. I looked at the data to be updated, with a total of more than 32 million records. This would take about 24 h + to update, that is, more than a day, amount ~~ Cry. think about what's wrong.

Problems found

The first thing I think of is whether it is slow because only one process is being updated. I started five processes and segmented the IDs, as shown below:

./update_url.sh 0 10000 &./update_url.sh 10000 20001 &./update_url.sh 20001 30001 &./update_url.sh 30002 40002 &./update_url.sh 40003 50003 &

After the operation, we found that the speed was not improved much, or the speed was Updated three to three times per second ~ Five or so, too. time cannot be spent on the steps before data is inserted (matching, assembling SQL statements ,...), It should be a problem during insertion.

Let's take a look at my SQL statements.select id,url from funkSpeed where id>=101 and id<=200;Here, I tried to execute the command line, and the result is as follows:

mysql> select id,url from funkSpeed where id>=0 and id<=200;Empty set (0.18 sec)

It took 0.18 seconds. at this time, I suddenly realized that I didn't use the union index. the condition for the Union index to take effect is that the field on the left must be available. I used the explain statement to verify that it was like this:

mysql> explain id,url from funkSpeed where id>=0 and id<=200;+-------------+------+---------------+------+---------+------+--------+-------------+| table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |+-------------+------+---------------+------+---------+------+--------+-------------+| funkSpeed  | ALL  | NULL          | NULL | NULL    | NULL | 324746 | Using where |+-------------+------+---------------+------+---------+------+--------+-------------+1 row in set (0.00 sec)

Then use the union index:

mysql> select uin,id from funkSpeed where uin=10023 and id=162;+------------+----------+| uin        |   id     |+------------+----------+| 10023      | 162      |+------------+----------+1 row in set (0.00 sec)mysql> explain select uin,id from funkSpeed where uin=10023 and id=162;+-------------+------+---------------+----------+---------+-------------+------+-------------+| table       | type | possible_keys | key      | key_len | ref         | rows | Extra       |+-------------+------+---------------+----------+---------+-------------+------+-------------+| funkSpeed   | ref  | uin_id        | uin_id   | 12      | const,const |    4 | Using index |+-------------+------+---------------+----------+---------+-------------+------+-------------+1 row in set (0.00 sec)

We can see that it is almost a second query. at this time, we can basically conclude that the problem occurs at the index location.

The number of select operations is relatively small, and the id difference between each two select statements is 10000. Therefore, this can be ignored and cannot be optimized unless an index is added to the id.

The problem occurs inupdate fuckSpeed set type=[type],typeid=[typeid] where id=[id]The query is also used during the update process. my mysql version is 5.5 and cannotexplain updateOtherwise, we can certainly verify what I said. here we need to update more than pieces of data, and each piece of data will be updated. every piece of data is about seconds. this is too scary ~~

Solve the problem

Once the problem is found, it will be much easier to solve it ~~

A field is added to the select statement.uin.select uin,id,url from funkSpeed where id>=101 and id<=200;And then useupdate fuckSpeed set type=[type],typeid=[typeid] where uin=[uin] id=[id]In this way, the index is used.

After the code has been changed, I tried to start a process to see how it worked. Indeed, the improvement was not a little bit, with an average of 30 + times/s, in this way, all the updates can be completed in about three hours.

No.: love_skills

The harder you work, the luckier you are! The luckier you are, the harder you work!

It's not a dream to become a CEO.

It's not a dream to win Bai Fumei

Yundun attack is not a dream

Now !! Fuel

The above section describes how to record a MYSQL update and optimization, including some content. if you are interested in the PHP Tutorial, please help.