最快的删除巨大MySQL表的方法

Question

我有一个庞大的MySQL（InnoDB）数据库，会话表中有数百万行数据是由与我们运行在同一台服务器上、不相关且故障的网络爬虫创建的。不幸的是，现在我必须来解决这个问题。

如果我尝试执行 truncate table sessions; 看起来需要很长时间（超过30分钟）。我不关心数据；我只想尽可能快地清空该表。是否有更快的方法，或者我必须通宵等待呢？

- Kyle Kaitan

Answer 1

(因为这篇内容在谷歌搜索结果中排名很高，所以我想提供更详细的说明。)

MySQL有一种便捷的方法可以像现有表格一样创建空表格，还有一个原子表重命名命令。结合起来，这是一种快速清除数据的方法:

CREATE TABLE new_foo LIKE foo;

RENAME TABLE foo TO old_foo, new_foo TO foo;

DROP TABLE old_foo;

完成

- searlea

---

完美的决定，本地化，纯粹且逻辑上优秀。太喜欢这个了。 - Arthur Kushman

Vlakarados，这样就总是有一个名为foo的表...如果你删除了foo然后将new_foo重命名为foo...特别是在高流量情况下，客户端会触发SQL错误。 - Caleb Gray

值得注意的是，使用这种方法将会删除正在被删除表格与其他表格之间的任何外键关联。 - Carlos P

我收到了 errno: 150 - 外键约束格式不正确。 - brandones

哦，那是因为我已经用这些相同的表做过一次了，保留了所有旧的外键约束，这些约束指向不再存在的“old_foo”。我希望我更认真地听取了@CarlosP的建议。 - brandones

Answer 2

最快的方法是使用DROP TABLE完全删除表并使用相同的定义重新创建表。如果您的表上没有外键约束，则应该这样做。

如果您使用的MySQL版本大于5.0.3，则TRUNCATE将自动发生。您可能会从手册中获得一些有用的信息，它描述了带有FK约束的TRUNCATE的工作方式。http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

编辑：TRUNCATE不同于drop或DELETE FROM。 对于那些对差异感到困惑的人，请检查上面的手册链接。 如果可以（如果没有FK），则TRUNCATE将像drop一样工作，否则它将像没有where子句的DELETE FROM一样运行。

编辑：如果您有一个大表，您的MariaDB / MySQL正在以ROW格式运行binlog_format，并且您执行没有谓词/ WHERE子句的DELETE，则在保持复制或甚至使Galera节点保持运行而不触发流控状态方面，您将遇到问题。此外，二进制日志可能会让您的磁盘充满。小心谨慎。

- womp

Answer 3

我发现在MySQL中最好的方法是：

DELETE from table_name LIMIT 1000;

或者是10,000（这取决于速度有多快）。

将它放在一个循环中，直到所有行都被删除。

请一定要尝试一下，因为它确实可行。虽然需要些时间，但它会起作用的。

- adnan.

---

抱歉，但如果您真的要删除行，为什么不直接执行 "delete from table_name" 呢？ - shylent

你以前有没有用过大表格来做这个？"delete from ..." 命令通常会占用大量的CPU，并且需要更长的时间。行数越少，删除就越快。试一试吧。这不是一个纯理论的练习，它在MySQL中实际可行。 - adnan.

同时，这种方式可以监控删除操作的进度。 - adnan.

我测试了一下，发现我的DROP查询需要很长时间，有时还会失败。记录数量为717,36,563。我尝试使用带有LIMIT的DELETE语句...超级快。+1感谢。 - cjava

如果在DELETE语句中使用LIMIT，你也应该使用ORDER BY。 - w00t

Answer 4

你能否获取模式，删除表并重新创建它？

- Nate

---

我曾认为“truncate”是“delete from”的同义词。但我已经检查过了，在最近的版本中，它实际上会删除并重新创建表（除非在某个外键中引用了该表）。 - shylent

这就是我想的，显然情况并非如此；然而，其他人也说要使用drop --肯定是在truncate工作方式上有一些区别。 - Nate

Truncate会从表中删除行，但保留模式。Drop会直接删除整个表。如果sessions上有索引，那可能是导致操作时间较长的原因。参见https://dev59.com/5HVC5IYBdhLWcg3w9GHM。 - J. Polfer

如果表上有索引，那你会怎么做？ - John Feminella

如果索引是问题所在，请首先删除它们，然后截断并重新创建索引。但是，整体删除表格（包括首先暂时删除任何外键引用）很可能是最佳解决方案。 - Will Hartung

Answer 5

drop table 应该是最快的摆脱它的方法。

- Paul Sonier

Answer 6

你尝试过使用“drop”吗？我在超过20GB的表上使用它，总是在几秒钟内完成。

- Zenshai

Answer 7

如果你只是想完全摆脱这个表格，为什么不直接删除它呢？

- shylent

Answer 8

截断是非常快的，通常只需要几秒钟。如果它花费了30分钟，你可能遇到了一些外键引用了你要截断的表。这可能涉及到锁定问题。

截断实际上是清空表的最高效方法，但是你可能需要删除外键引用，除非你也想将这些表清空。

- Jeff Ferland

Answer 9

我们曾经遇到过这些问题。在Rails 2.x中，我们不再使用数据库作为会话存储器，而是使用cookie存储器。然而，删除表是一个不错的解决方案。您可能需要考虑停止mysql服务，暂时禁用日志记录，以安全模式启动并进行删除/创建操作。完成后，再次打开日志记录。

- Brian Hogan

Answer 10

我不确定为什么这需要这么长时间。但是也许可以尝试重命名并重新创建一个空表。然后您可以放心地删除“额外”的表，而不必担心需要多长时间。

- Brent Baisley