occasionally , More data leads to trouble . Remove the related table data in the library （ Storage in other warehouses ） It may be necessary work to be done on a regular basis .
The basic steps are as follows ：
1. Export scheduled in table data
2. Import to the specified library
3. Remove table data from original database
Is it simple . We do it step by step .
1. Export scheduled in table data
generally speaking , Most tables do not export all of the data , Data that meets the criteria is retained . commonly , Time conditions are more common . such as ： Request for retention 2 Data within months .
We use mysqldump This tool can export the specified table and satisfy the specified logical conditions . Here is an example ：
mysqldump -uroot -p testdb testtable --where="id<=125443450" >
testtable201901.sql
The command above is export testdb In the library testtable In the table id Less than or equal to 125443450 All data of .
This command will include creating tables testtable And insert data into testtable Table commands .
If more than one table needs to be exported , Just refer to the order above , Just write more .
Pay attention here , It is common to use time as a requirement to retain data . But use time as a condition （ especially datetime Type ） There may be more or less data （ Measured results ,mysql
5.6, Other versions not detected ）. therefore , It is suggested to use time condition to display some data first 10-20 It's OK , Then confirm the last data , And take the id（ Self growing field ） Operate as a derived data condition .
2. Import to the specified library
This implementation is simpler , use mysql The tool can be implemented easily . Here is an example ：
mysql -uroot -p testdbbak < testtable201901.sql
There are multiple table files that can refer to the above command .
3. Remove table data from original database
The first two steps are very simple to implement .
This step is supposed to be very simple , But being bold and practical can drive you crazy .
Methods for removing table data mysq There are 2 individual ,delete and truncate table
truncate table The command to clear all data in the table .
It is characterized by high speed . If all the data are confirmed, do not , This is the best .
If it is a partial deletion , that truncate table It's not suitable .
In the case of less deleted data ,delete Nothing bad .
If there are more , such as 500 10000 pieces of data , Then you may face the following problems ：
a. Longer execution time
b. Index efficiency becomes low
c. Disk space not freed
I've met enforcement delete Over time 40 Minutes ....
In the presence of bc After the situation , It is usually necessary to optimize the table , This is another long process of implementation ...
In view of the problems described above , If it is time to delete more data , Not recommended delete To achieve .
One of the suggestions given here is as follows , For your reference ：
use mysqldump Export the data that needs to be preserved in the table , After that, the exported data is immediately restored to the original table .
mysqldump -uroot -p chickens testdb testtable --where="id>125443450" >
testtable201901.sql
mysql -uroot -p testdb< testtable201901.sql
Why is this program better than direct use delete good ?
Export first and then import , The essence is to delete the original table, then rebuild and re insert the data . It avoided delete Problems caused by indexing tables , There is no need to optimize later . that is bc The problem is gone .
Then the execution time and delete Less than that one ? This is not a simple answer , Although my test result is delete Longer time .
This mainly depends on the amount of data to be deleted and retained . Accurate data cannot be given here for the time being , But when the two are similar ,delete It will be longer .
As for why , Interested friends can study on their own .
There are other issues that need further discussion , Time limit to be continued later .
Technology
Daily Recommendation