MySQL Update optimization strategy _mssql

Simulation Scene One:

To adjust the structure of a table in a database, add a few fields, refresh the previous data, refresh the content by matching one of the existing field URLs, and then update 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

Copy Code code as follows:

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 not 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_idin the table, and I'm updating it with the following idea:

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

Copy Code code as follows:

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

Copy Code code as follows:

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

Copy Code code as follows:

./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 again

Copy Code code as follows:

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

Here, try executing at the command line, the result is as follows

Copy Code code 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:

Copy Code code as follows:

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:

Copy Code code as follows:

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

Problem occurs in

Copy Code code as follows:

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, or you can verify what I said, here to update the 32w+ data, Every piece of 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 to add a field uin, change to the following

Copy Code code as follows:

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

And then update it using the

Copy Code code as follows:

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

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

Simulation Scenario Two:
Requirements 6 table PID fields are written to the corresponding brand_id field

Problem SQL background: The project has 6 tables to write the corresponding brand_id field According to the PID field. But two of the tables are tens. After my worker runs, the mysql master-slave sync on line is immediately delayed! After running for one hours, it was delayed to 40 minutes and only a hundred thousand of rows of data were updated. Problem SQL is as follows:

Copy Code code as follows:

<!--update Brand ID--> according to Product ID
<update id= "Updatebrandidbypid" parameterclass= "Com.jd.chat.worker.domain.param.UpdateBrandIdParam" >
UPDATE $tableName $
SET brand_id = #newBrandId #
WHERE pid = #pid #
and brand_id = 0
</update>

The project team's MySQL expert helped me to analyze the next, because the PID field does not have the index, the MySQL engine wants to scan the column that is equal to the incoming PID value row by line, then updates the data, that is to scan the 1000w+ row disk data to finish this SQL. What's more, the number of different PID controllers in this Tens table is how many of these SQL I have to execute.
My colleague gave me the suggested vertical table of SQL code levels based on the ID field. Update 1000 rows of data each time, so that the MySQL engine does not have to sweep the entire table every time, the database pressure is the previous one out of 10,000. And the ID as the primary key, indexing indexed, indexed to greatly optimize query performance, optimized SQL is as follows:

Copy Code code as follows:

<!--update Brand ID--> according to Product ID
<update id= "Updatebrandidbypid" parameterclass= "Com.jd.chat.worker.domain.param.UpdateBrandIdParam" >
UPDATE $tableName $
SET brand_id = #newBrandId #
WHERE pid = #pid #
and brand_id = 0
and ID BETWEEN #startNum # and #endNum #
</update>

Using only the statement of the ID limit interval, a tens of the large table code level is cut vertically. Re-online after the worker, the MySQL master without any delay! And after monitoring, in just 10 minutes to update the a hundred thousand of data, efficiency is 6 times times before! More importantly, the database load balance, the application of healthy operation.

Above through two problems simulation scene reappearance analysis MySQL update optimization strategy, hope for everyone in the database to help.