MySQL Clear Big Data form

Background: The MySQL database has a log table record of up to more than 8 million, affecting the normal business access of MySQL, now need to clean up all data three months ago, about more than 6 million

Method One: Traditional delete from xxx, traditional, normal, efficient, high data cleanup easy to bring down the database.

Method Two: Truncate, this operation will erase all the data in the table. (This is the most efficient if you want to erase the data).

Method Three: Indirect method. The steps are as follows:

1. Create a new table t_req_log_new that t_req_log the same structure.

CREATE table t_req_log_new like T_req_log or creates a row from the original table-building statement.

2. When the number of visitors is low, the Web service is stopped (otherwise the operation will also write the log into the table).

3. Execute SQL Imports data from the T_req_log table for nearly three months into a new t_req_log_new table.
INSERT INTO T_req_log_new
SELECT * from T_req_log where time between ' 2018-02-01 00:00:00 ' and now ();

4. Rename two tables at the same time, indirectly implement the data delete operation, replace the t_req_log_new with the original table name instead of the original table.

RENAME TABLE t_req_logto t_req_log_old, t_req_log_new to T_req_log;

5. Delete the old table (see if the deletion is not deleted).

drop table T_req_log_old

6. Restart the server and observe the log.

The above actions are more than n times faster than delete deletes.

can be executed at intervals of time, or you can do a thing in the database, automatic daily deletion

MySQL Clear Big Data form