Mysql Update optimization Record _mysql

Introduction

Today (August 5, 2015 5:34 PM) makes an adjustment to the structure of a table in a database, adds several fields, and then refreshes the previous data, refreshing it by matching one of the existing field URLs, and then updating the newly added field type and typeid. Later, I wrote a shell script to brush the data, the results of the run shell scripts I have Meng, how so slow ~ ~ ~

Scene reappearance

CREATE TABLE ' fuckspeed ' (
 ' UIn ' bigint () unsigned NOT null default 0,
 ' id ' int (one) unsigned NOT null default 0,
 ' url ' varchar (255) NOT null default ',
 ' type ' int (one) unsigned NOT null default 0,
 ' typeid ' varchar (?) NULL DEFAULT ',
 ...
 KEY ' uin_id ' (' UIn ', ' id ')
) Engine=innodb DEFAULT Charset=utf8;

The table structure is probably above (omitting a lot of fields), and there is only one federated index uin_id in the table, and I'm updating it with the following idea:

Get a certain amount of data based on an ID range first

Select Id,url from Funkspeed where id>=101 and id<=200;

Iterate through all the data and update every piece of data
#首先对数据进行处理, matching get type and typeid

Update fuckspeed set Type=[type],typeid=[typeid] where Id=[id]

Following the above thought, the discovery of special slow, average 3~5 per second, I am also drunk, I look to update the data, a total of 32w+, so that the update down probably need 24h+, that is 1 days more, the amount ~ ~ cried, think about is definitely where the problem.

Find problems

The first thing I thought of was whether it was because only one process was updating, causing it to be slow, I started 5 processes, and I segmented the ID, just like the following

/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 run found that still, the speed did not improve how much, or update every second 3~5 around, think it is ah, time can not spend 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 executing at the command line, 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, this time I guess suddenly dawned, the federated index I did not use, the Union index is effective--must have the left field, 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 |   const,const  | 4 | Using Index |
+-------------+------+---------------+----------+---------+-------------+------+-------------+
1 row in Set ( 0.00 sec)

Can see almost seconds to check, this time basically can determine the problem is to appear in the index this place

I have a small number of select times, with a 10000 ID difference between each two select, so this can be ignored, and there is no way to optimize it unless you add an index to the ID.

The problem occurs in the update fuckspeed set Type=[type],typeid=[typeid] where Id=[id], here in the update will also use the query, my MySQL version is 5.5, can not explain update, Otherwise you can verify what I said, here to update the 32w+ data, each data will be updated, each data 0.2s or so, this is too scary ~ ~

Solve the problem

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

Select when you add a field UIn, instead select Uin,id,url from Funkspeed where id>=101 and id<=200, and then use update when updating Fuckspeed Set Type=[type],typeid=[typeid] where Uin=[uin] id=[id] so that the index is used.

Envelope changed the code, try to start a process to see how the effect, sure enough, the effect of ascension is not a little bit, the average 30+ times/s, so about 3 hours or so will be able to complete all the updates.

Summarizing MySQL statement-level optimization:

1. Performance check reading statements, in the INNODB statistics line number, suggest another to get a statistical table, using MyISAM, regular statistics. The general statistics of the data will not require too precise circumstances apply.

2. Try not to do operations in the database.

3. Avoid negative queries and% prefix ambiguity queries.

4. Do not perform operations or use functions in the indexed column.

5. Do not use the form of SELECT * from to query data in production environment programs. Query only the columns you want to use.

6. Queries use limit to reduce the number of rows returned, reducing data transfer time and bandwidth waste.

7. Where clause uses functions on query columns whenever possible, because the use of functions on query columns is not indexed.

8. Avoid implicit type conversions, such as character type must use ', digital must not use '.

9. All the SQL keywords in uppercase, develop good habits, avoid the duplication of SQL statements caused by the waste of system resources.

10. In the table query, remember to put the small result set in front, follow the principle of small result sets driving large result sets.

11. Open the slow query, periodically use explain to optimize the SQL statements in the slow query.