How to clean up data in MySQL online database

01 Scenario Analysis

This afternoon, a development colleague proposed a requirement to delete some data records online. After a quick look at the data distribution, it is estimated that more than 27 million records in the data table need to be deleted. The total number of records in the data table is more than 28 million, which means that the records to be deleted account for the vast majority of the total records. If more than 27 million data records are deleted, it will take a long time to use them, which will definitely affect the online business. Here we simply reconstruct the actual application case into the following method:

mysql> select date,count(*) from test.tbl_a group by date;
+----------+----------+
| date | count(*) |
+----------+----------+
| | 63103 |
| 20190118 | |
| | 125916 |
| 20190120 | |
| | 129198 |
| 20190122 | |
| | 5191247 |
+----------+----------+
 rows in set (13.21 sec)

The above is the structure of the reconstructed table. We can see that after table tbl_b in the test database is grouped by date, the amount of data in each group is not small. Our requirement is to delete the records with dates of 20190118 and 20190123. It can be seen that there are more than 20 million records of these two types, accounting for the vast majority of the data in the table. If they are deleted directly, online business will definitely be blocked.

The following are the operating methods. It should be noted that the premise for applying the following operating methods is:

The data to be deleted accounts for the vast majority of the data table.

02 Operation Method

Through analysis, we know that the remaining table data is a very small part of the entire table. This operation is divided into 4 steps:

1. Let's first store the remaining data in another database test1:

create table test1.tbl_b_new as 
select * from test.tbl_b 
where date in(,,,,);

This operation takes only about 3 seconds;

2. We create a table tbl_b with the same name in the test1 database. Its structure is consistent with the tbl_b data structure in the test database:

create table test1.tbl_b like test.tbl_b;

3. Next, we use the rename operation in the database to exchange table tbl_b in table test with table tbl_b in test1, which is equivalent to clearing all the data in the table in the test database.

RENAME TABLE test.tbl_b TO test1.tbl_b_bak,
         test1.tbl_b TO test.tbl_b,
         test1.tbl_b_bak TO test1.tbl_b;

4. Then fill the remaining data saved in the first step into the new table, as follows:

insert into test.tbl_b select * from test1.tbl_b_new;

03 Results Analysis

It seems that we have complicated the problem. We have added several steps to the direct deletion. But this is not the case. This set of operations can save us several minutes and have less impact on online business. The reasons are as follows:

1. We use the create table as method to create the remaining data tables. This method allows us to operate on only a small part of the data in the data table. This prevents us from scanning too much data.
2. When renaming a large table in MySQL, the rename command will directly modify the underlying .frm file, so its speed is quite fast.

The second feature provides us with an idea. In a very urgent business, when we need to use a table, we often do not have enough time to back up the table. If we want to delete the data in a large table and need to make relevant backups, we can quickly process it through the rename operation, and then find a way to back up the table after the rename.

The above example uses two methods of cloning tables: create table as and create table like. There are a few points to note:

The create table like method will completely clone the table structure, but will not insert data. You need to use the insert into or load data method to load data separately.
The create table as method will partially clone the table structure and keep the data intact.

If you are interested, you can do some experiments to verify it.

The above is the detailed content of the method of cleaning data in MySQL online database. For more information about cleaning data in MySQL online database, please pay attention to other related articles on 123WORDPRESS.COM!