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" >
testtable201901.sql
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 .

Technology
©2019-2020 Toolsou All rights reserved,
about String How to create objects JavaScript Hundred refining into Immortals 1.15 Legendary Is MCU embedded , It's a cliche Resume the 13th session python Blue Bridge Cup html+css+js Make a simple website home page java Connect to the database to realize basic addition, deletion, modification and query VHDL——JK trigger Java of JDBC programming 3 4j It's not legal python expression _3+4j It's not legal Python expression .【linux】shell: ordinary shell Script exercise