How to Realize Fast Cleaning of Billion level Data in mysql Database

Today, I received an abnormal disk alarm, and the disk of 50G was burst. The analysis and solution process is as follows:

1. Go to the linux server and view the disk space occupied by each database in the mysql folder

See, light olderdb accounts for 25G

2. Log in to mysql database with SQLyog 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

Premise: 80% of data needs to be deleted at present

① delete statement

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

Delete statement deletion will not free up disk space, so an alarm will definitely still appear, so this method is not desirable.

② drop dropping table

Create a new table with the same structure, 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, and 300,000-400,000 at a time is the best. After all, mysql has limited data processing capacity)

Insert after querying by date (it will generate more than 300,000 data every day, so date insertion is adopted)

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 more than 500,000 data took less than 5 minutes, which is still relatively fast.

After cleaning, the data table space is released

Then drop drops the old watch

DROP TABLE orbit

Visual inspection took only about 3 seconds

Rename new table "cc"

ALTER TABLE cc RENAME TO orbit

Summarize