Reprint--Batch update data (performance optimization)

Recently done the game, on-line after a lot of problems, so I often go to check the database data, turn all kinds of logs, etc., but in the query when found good egg pain, some places time is written "2016-08-11 20:13:02" This format, some places time is write "1470917582000" This format, and then a lot of table data to compare when it is very painful, I have to keep on time conversion, disgusting. Finally can not endure, I want to unify the time format (because the time is a single field record, so better processing), two formats, it is obvious, for the data, the first format more intuitive, so decided, the database log class, the Message Class table time, unified into "2016-08-11 20:13:02 "this format.

Think about, can not simply use the MySQL statement straight conversion, so I wrote a script to convert the time format (new very good processing, the main problem is to convert the previously recorded data once, when the server maintenance, processing).

The first idea is very simple (rarely use MySQL, the use of the time is also simple to use), that is, the database data are all select out, and then an update, local testing, no problem. In the back, the player part of the external network is imported and tested. Do not know, a guide to scare a jump, memory explosion, haha, outside the network data is too large, it is impossible to let me all of a sudden select out. What to do, think about, in fact, I do not need so much data, for each record, I just select out key and time on the line, said dry, tried, it seems not to explode, but found his execution speed super slow, 1 million records, ran five or six hours, this is only part of the data Ah, If it is all the data, how long to run ah, stop maintenance so long, not to be scolded to death. No way, just optimize it.

The first thought is that one update is too slow, and then want to batch update, update n data at a time. Practice is the only standard to test truth, in a moment, the code is knocked out, try again, the effect is still not ideal. Oh, it's going to crash! And then I thought of the use of asynchronous, I open a number of MySQL connection, while processing, but still slow a stroke. And then give up, the efficiency of the update is certainly not enough. Then thinking about the mealy, I create a new table, the old table data out, after processing, directly inserted into the new table, and then delete the old table, the new table renamed to the old table name. Consider that insert into should be much faster than update. But to get all the old table data out, the memory will explode, so I'll take it slowly, select+limit. Try it a bit faster, but it feels like a one-hour run. It's a lot faster, but it's certainly not going to work.

Then try, select the time with a Select + where + order by + limit method, where and order by are used time to deal with, ran a bit, 10 minutes will be done. Think back, where and order by when using the table's primary key to deal with, should be faster, and then try, wow, the effect is obvious, select + where + order by + Limit method (with primary key to handle where and order by), one minute to deal with All data is finished. Once the data is processed, the data is inserted once, the data can be adjusted to select the number of data according to the size of their data. Remember, select out, processing, one-time insert new table, do not insert!

The end result, with a Select + where + order by + Limit method (with primary key to deal with where and order by), about a minute to deal with 5 million or so of data, there are several tables, of which two tables each have 1 million or 2 million pieces of data, The original one-by-one update method is expected to be more than 10 hours. Although after several twists and turns, finally finally relieved to finish.

Episode: When creating a new table, I created it by copying the old table structure, copying the old table structure, remembering to use the CREATE table newtablename like Oldtablename, or not copying the old table's index.

Reprinted from: http://www.cnblogs.com/lingt/p/5762526.html

Reprint--Batch update data (performance optimization)