How to quickly clean up billions of data in MySQL database

Today I received a disk alarm exception. The 50G disk was blown up. The analysis and solution process is as follows:

1. Enter the Linux server and check the disk space occupied by each database in the mysql folder

Did you see that olderdb alone takes up 25G?

2. Use SQLyog to log in to the MySQL database and check the space occupied by each table in the database

SELECT CONCAT(table_schema,'.',table_name) AS 'aaa',  
  table_rows AS 'Number of Rows',  
  CONCAT(ROUND(data_length/(1024*1024*1024),6),' G') AS 'Data Size',  
  CONCAT(ROUND(index_length/(1024*1024*1024),6),' G') AS 'Index Size' ,  
  CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),' G') AS'Total' 
FROM information_schema.TABLES  
WHERE table_schema LIKE 'olderdb';

3. Query the primary key index

SHOW INDEX FROM orbit

Adopt a strategy

Prerequisite: Currently 80% of the data needs to be deleted

① delete statement

We know that the deletion speed of the delete statement is proportional to the number of indexes. The number of indexes in this table is already very large, and the amount of data is very large. If a conventional delete statement is used to delete, it will definitely take several days.

Deleting with the Delete statement will not release disk space, so an alarm will definitely appear, so this method is not advisable.

② Drop table

Create a new table with the same structure and name it "cc", insert the data to be saved into this table, and then drop the old table.

The SQL statement is as follows:

Create a new table based on the old table

CREATE TABLE cc LIKE orbit ;

Insert data (millions of data must be inserted in batches, 300,000-400,000 at a time is the best, after all, MySQL's data processing capacity is limited)

Query by date and insert (about 300,000 data points will be generated every day, so date insertion is used)

INSERT INTO cc SELECT * FROM orbit WHERE xttime > '2018-04-16 00:00:00' AND xttime<='2018-04-17 00:00:00';

The results are as follows:

It can be seen that it took less than 5 minutes to process more than 500,000 pieces of data, which is relatively fast.

After cleaning, the data table space is released

Then drop the old table

DROP TABLE orbit

It only took about 3 seconds.

Rename the new table "cc"

ALTER TABLE cc RENAME TO orbit

Summarize

The above is my introduction to how to quickly clean up billions of data in MySQL database. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!