Shulou(Shulou.com)05/31 Report--

This article is about how mysql databases can clean up hundreds of millions of data quickly. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

An abnormal disk alarm was received today, and the 50g disk was burst. The analysis and solution process is as follows:

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

See? olderdb alone accounts for 25 gigabytes.

two。 Log in to the mysql database with SQLyog to 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), 6),'G') AS 'Data Size', CONCAT (ROUND (index_length/ (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 primary key index

SHOW INDEX FROM orbit

Adopt a strategy

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

① delete statement

We know that the deletion speed of delete statements 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 you use conventional delete statements to delete, it will take several days.

The deletion of the Delete statement will not free up disk space, so it is certain that there will still be an alarm, so this approach is not desirable.

② drop dropped the table

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

The Sql statement is as follows:

CREATE TABLE cc LIKE orbit

Insert data (millions of data must be inserted in batches, 300000-400000 at a time is the best, after all, the data processing capacity of mysql is limited)

Insert after query by date (probably generate more than 300,000 data per day, so date insertion is used)

INSERT INTO cc SELECT * FROM orbit WHERE xttime > '2018-04-16 00 AND xttime

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.