How to quickly crop large tables?

Question

Currently, I have a MySQL table of about 20 million rows, and I need to shorten it. I would like to delete every row that updateTime(insert timestamp) had more than a month ago. I personally did not make any changes to the order of the tables, so the data should be in the order in which they were inserted, and in two fields, idand updateTime, there is a UNIQUEkey. How would I do this in a short time?

Answer 1

How much downtime can you incur? How big are the lines? How much do you delete?

Simply put, deleting rows is one of the most expensive things you can do for a table. This is just a terrible thing.

If you do not need to do this, and you have disk space for it, and your queries are not affected by the size of the table (well-indexed queries usually ignore the size of the table), then you can just leave it pretty good alone.

If you have such an opportunity and you can disconnect the table offline (and you delete a good percentage of the table), then it is best to copy the rows you want to save to the new table, discard the old one, rename the new one to the old name and THEN re-create your indices.

Otherwise, you are very stuck in a good distance.

Answer 2

. :

DELETE FROM table1 WHERE updateTime < NOW() - interval 1 month;

( ) - , , .

CREATE TABLE table2 AS
SELECT * FROM table1 WHERE updateTime >= NOW() - interval 1 month;

TRUNCATE table1;

INSERT INTO table1
SELECT * FROM table2;

TRUNCATE , a DELETE WHERE, , .

Answer 3

;

10M, . .

( )

.

DELETE from table where id > XXXX limit 10000;
DELETE from table where id > XXXX limit 10000;
DELETE from table where id > XXXX limit 10000;
DELETE from table where id > XXXX limit 10000;

.

mysql> source /tmp/delete.sql 

.

, pt-tools. pt-.

Answer 4

In fact, even if you can’t take the table offline for a long time, you can still use the “rename table” technique to get rid of old data.

Stop processes by referencing a table.

rename table tableName to tmpTableName;
create table tableName like tmpTableName;
set @currentId=(select max(id) from tmpTableName);
set @currentId=@currentId+1;
set @indexQuery = CONCAT("alter table test auto_increment = ", @currentId);
prepare stmt from @indexQuery;
execute stmt;
deallocate prepare stmt;

Run the processes that write to the table.

insert into tableName
select * from tmpTableName;
drop table;

New inserts in tableName begin with the correct index; Old data will be inserted into the correct indexes.