Remember MySQL update optimization

Introduction

Today (August 5, 5:34 PM) in the database to the structure of a table to make an adjustment, add a few fields, followed by the previous data refresh, refresh the content is: one of the existing fields to url match, and then update the newly added fields type and typeid . Then wrote a shell script to brush the data, the result of running the shell scripts I was ignorant, how so slow ~ ~ ~

Scene 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 probably the above (omitting a lot of fields), there is only one federated index in the table, uin_id and I am updating the following ideas:

• Get a certain amount of data first based on an ID range
  Select Id,url from Funkspeed where id>=101 and id<=200;
  
• Iterate through all the data and update each piece of data
  #首先对数据进行处理, match get type and typeid
  Update fuckspeed set Type=[type],typeid=[typeid] where Id=[id]
  According to the ideas above, found that the update is particularly slow, on average, about three or so, I am also drunk, I look to update the data, a total of 32w+, so update down probably need to 24h+, that is, 1 days more, the amount of ~ ~ Cry, think of something is definitely wrong.

Discover problems

First I think about is not because only one process in the update, causing very slow, I started 5 processes, the ID is segmented, like this

./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 running the discovery or that, the speed does not improve how much, or update every second around, think of Ah, time can not be spent in the steps before inserting data (matching, assembling SQL statements 、。。。 ), there should be a problem when inserting

Take a look at my SQL statement, select id,url from funkSpeed where id>=101 and id<=200; here, try to execute it at the command line, and the results are as follows

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

It took 0.18 seconds, and this time I guess it dawned on me that the federated index is not used, the condition of the Union index is--must have the field on the left, with explain verification, sure enough:

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 Federated 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)

Can see almost the second, this time basically can be concluded that the problem is in the index of this place

I select a few times, the ID between every two select 10000, so this can be ignored, and there is no way to optimize, unless you add an index on the ID.

The problem occurs update fuckSpeed set type=[type],typeid=[typeid] where id=[id] , here in the update is also used to query, my MySQL version is 5.5, can not explain update , or can certainly verify what I said, here to update 32w+ data, each data will go to update, each data is about 0.2s, this is too scary ~ ~

Solve the problem

The problem is found, the solution is much easier ~ ~

Select adds a field uin to the following select uin,id,url from funkSpeed where id>=101 and id<=200; , which is then used when updating, so that the update fuckSpeed set type=[type],typeid=[typeid] where uin=[uin] id=[id] index is used.

Rinsed changed the code, try to start a process, see how the effect, sure enough, the effect is not a little bit, the average number of times/s, so that about 3 hours can be completed all the updates.

Number: Love_skills

The more effort, the luckier! The more fortunate, the harder!

Being CEO isn't a dream.

Winning mating is not a dream

Cock Silk is not a dream

is now!! Come on

The above is introduced to remember the MySQL update optimization, including the aspects of the content, I hope the PHP tutorial interested in a friend helpful.

