如何快速修剪大表？

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

已邀请:

4 个回复

械怒等

你可以承受多少停机时间？行有多大？你删除了多少？简而言之，删除行是您可以对表执行的最昂贵的事情之一。这总体上是一件可怕的事情。如果您不必这样做，并且您有足够的磁盘空间，并且您的查询不受表大小的影响（索引良好的查询通常会忽略表大小），那么您可能只留下足够的空间。如果您有机会并且可以使表离线（并且您正在删除表中的一小部分），那么您最好的选择是将要保留的行复制到新表中，删除旧表，重命名新旧的名称，然后重新创建索引。否则，你几乎坚持使用'删除'。

蹄渭信妥扳

有两种方法可以删除大量行。首先有明显的方法：

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要快得多。

惭法搽

使用限制拆分删除可能会加快进程; 我不得不删除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。

缮记箔

实际上，即使你不能让表离线很长时间，你仍然可以使用'重命名表'技术来摆脱旧数据。停止进程写入表。

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的新插入将从正确的索引开始;旧数据将插入正确的索引中。

要回复问题请先登录或注册

如何快速修剪大表？

4 个回复

发起人

pruning

问题状态

如何快速修剪大表？

与内容相关的链接

4 个回复

发起人

pruning

问题状态