MySQL Tens data removal practice-Enterprise case

One day, in the production process, found a scheduled task table, because each service area query This table will cause slow query, to the MySQL server brought a lot of pressure, after analysis, the table absolute part of the data is garbage data

Need to delete, about 10.5 million lines, due to lack of experience in dealing with big data, began to use Delete with the Where condition for cleanup, the last execution of 1 hours is not completed, followed by query, 10 million data

The deletion takes dozens of hours or even hundreds of hours, decisively giving up and adopting a roundabout strategy.

Three-step strategy:

1. Extract the data to be retained in the backup table

2. Truncate old table

3. Backup data is inserted back

Through the above ideas decisively resolved within 10 minutes, the harvest is TENS data delete is not advisable.

Note the detailed steps to add:

0. Build Backup table CREATE table new table SELECT * from old table WHERE 1=2;
1, extract the old table needs to keep the data INSERT into new table SELECT * from the old table WHERE query conditions;
2, disable foreign keys to initialize the old table to prepare set foreign_key_checks = 0; (if there is a foreign key in the old table, it is best to disable, otherwise truncate will prompt,)
3. Initialize old table TRUNCATE table;
4. Extracted data restored to old table insert into old table SELECT * from new table;
5, open the foreign key set foreign_key_checks = 1; Other precautions: 1. It is best to close the MySQL date record and slow log query during the operation of big data.

MySQL Tens data removal practice-Enterprise case