如何快速修剪大表？

Question

我目前有一个大约 2000 万行的 MySQL 表，我需要对其进行修剪。我想删除 updateTime （插入时间戳）超过一个月前的每一行。我个人没有对表的顺序进行任何更改，因此数据应该按照插入的顺序排列，并且两个字段上有一个 UNIQUE 键，id 和更新时间。我该如何在短时间内做到这一点？

Answer 1

您可以承受多少停机时间？行有多大？你要删除多少个？

简而言之，删除行是对表执行的最昂贵的操作之一。总的来说，这是一件可怕的事情。

如果您不必这样做，并且您有足够的磁盘空间，并且您的查询不受表大小的影响（索引良好的查询通常会忽略表大小），那么您可以不用管它。

如果您有机会并且可以使表脱机（并且您要删除表的很大一部分），那么最好的选择是将要保留的行复制到新表，删除旧表，重命名将新名称更改为旧名称，然后重新创建索引。

否则，您将几乎陷入良好的删除困境。

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 比使用带有 WHERE 子句的 DELETE 快得多。希望保留。

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-archiver。

Answer 4

实际上，即使您不能长时间使表离线，您仍然可以使用“重命名表”技术来删除旧数据。

停止写入表的进程。

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;

启动进程写入表。

insert into tableName
select * from tmpTableName;
drop table;

对 tableName 的新插入将从正确的索引开始；旧数据将被插入到正确的索引中。