I left a little tail last time , Take a moment to talk about it .
First of all, make a summary of the last plan .
Back up some data in the table first , And then directly restore the data to the table .
In the main scenarios encountered , Execution time will be less than direct delete, The main reasons are as follows ：
1.delete In execution （ Delete large amounts of data ）, Most are one or more scope conditions , The index cannot be optimized . Index and table data need to be scanned during execution , Finally, you need to write binlog, This consumes a lot of resources .
2. The scheme is in execution , Used in backup where, It can be done through clever sql grammar , Make the most of the index . In the subsequent recovery process ,drop table and create
table Execution time ignored , Mainly insert Large consumption of resources . however insert Operation delete Compared with the consumption of resources is relatively small . Back up insert Statements are written to more than one line at a time , High efficiency .
in summary , In most cases, this scheme is better than direct delete. In addition, the optimization of index maintenance , In this scheme, the index is actually reconstructed , Already in optimal condition , There is no need for subsequent optimization .
Finally, let's add another situation .
occasionally , The exported data needs to be appended to the specified database . Some data may have been stored in it before .
If we use this scheme directly , Then it will drop Previously stored data , Causing data loss .
After confirming that the data must be saved and stored in the same database and the same table , The export data statement can be modified slightly .
mysqldump -uroot -p -t chickens testdb testtable --where="id>125443450" >
mysql -uroot -p testdb< testtable201901.sql
stay mysqldump Yes “-t” parameter , Do not export table structure . You get a bunch of them insert sentence , After that, you can directly import the specified library .