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,
One is called “ Asking for the train ” A small village Finally got the train Spring Boot Lesson 16 :SpringBoot Implementation of multithreading with injection class Chrome OS, For programmers and Windows What does it mean ? Internet Marketing JAVA Convert a string to a numeric type I've been drinking soft water for three years ? What is the use of soft water and water softener You don't know ——HarmonyOS Talking about uni-app Page value transfer problem JavaScript Medium Call and ApplySparkSQL Achieve partition overlay write Character recognition technology of vehicle license plate based on Neural Network