MySQL big table update SQL optimization strategy

After reading the article, it is very enlightening that MySQL is usually the first select when it is update, and at this point, if the index is not used, it will lock the entire table. The best use of indexes

By using the primary key, if we know the scope of the primary key (as long as it is a superset of the exact range), you can add the scope of the primary key in the query condition so that the query

By using the primary key index, you can increase the speed of the query. In this way, we don't have to index other fields individually, and we can speed up the query with a known index, which feels very nice.

Original: http://blog.csdn.net/bruce128/article/details/17426671

Problem SQL background: The project has 6 tables to be written according to the PID field to the corresponding brand_id field. But there are two of these tables that are tens other. After my worker runs, the MySQL master-slave synchronization on the line is immediately delayed! After one hours of operation, it was delayed to 40 minutes, and only a hundred thousand of rows of data were updated. The problem SQL is as follows:

    UPDATE Product     SET = [newbrandid]     WHERE = [pid]         and = 0

The project team's MySQL expert helped me analyze it because the PID field was not indexed, and the MySQL engine scanned the columns that were equal to the PID values passed in, and then updated the data, that is, to scan the 1000w+ rows of disk data to complete the SQL. Because it is an update operation, the index is not used, which causes the SQL to occupy the table lock, the other SQL can only wait until this SQL execution is completed before it can start execution. What's more, the number of different PID in this Tens table, I will execute how many such SQL.

My colleague gave me the suggestion of a horizontal 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 whole table every time, the database pressure is one out of 10,000 before. And the ID as the primary key, is indexed, this time occupy the 1000 rows of data row-level lock, will not affect the other data. There are indexes that can greatly optimize query performance, and the following SQL is optimized:

UPDATEProductSETbrand_id= [Newbrandid]WHEREPid= [PID]     andbrand_id= 0       andIdbetween [Startnum]  and [Endnum]  

Just use the ID limit interval of the statement, a Tens large table code level on the horizontal cutting. After re-online worker, MySQL master and slave no delay! And after monitoring, just 10 minutes to update a hundred thousand of data, efficiency is 6 times times before! More importantly, the database is load balanced and the application is running healthily.

MySQL big table update SQL Optimization strategy (GO)