在不影响服务的情况下删除MySQL大表

Question

我有一个表需要删除，该表大约占用了130GB。当我开始删除/清空该表时，mysql服务器会变得缓慢，并且我会遇到很多与正在使用该服务的用户有关的问题。

如何在不影响mysql性能的情况下删除大表?

我已经尝试创建一个新表来代替要删除的表，然后将其重命名并删除未使用的表(旧表)，但是重命名命令会挂起我的mysql服务。

我正在使用:

mysql  Ver 15.1 Distrib 10.1.28-MariaDB, for Linux (x86_64) using readline 5.1

- Francisco Panis Kaseker

---

当我开始使用drop或truncate时，它们并不相同。 - Paul Spiegel

@PaulSpiegel 我想清空那张表，这样两个都可以工作。我没有外键和触发器。TRUNCATE可以解决这个问题，但会导致MySQL服务挂起。 - Francisco Panis Kaseker

Answer 1

操作系统可能是问题所在。一些操作系统删除非常大的文件需要很长时间才能完成。抱歉，但你只能等待。

- Rick James

Answer 2

当您在MySQL或MariaDB中删除（DROP）或清空（TRUNCATE）表时，如果您的缓冲池很大，可能会导致服务器锁定或多秒钟的停顿。这是因为当一个表被删除时，MySQL会对缓冲池执行完整扫描，寻找属于已删除表的页面。这将锁定缓冲池，并且您必须等待直到它解锁。如果您的缓冲池较小，则影响不大，但如果您的缓冲池较大，则会变得毁灭性。

一种可能的解决方案是在删除表之前删除所有行，这不会导致内存停顿，因为内存中没有数据。

另一种解决方案可以使用非InnoDB存储引擎来创建临时表。您可以在CREATE TABLE语句中指定ENGINE=MyISAM。如果您有一个庞大的代码库并且没有明确指定存储引擎，您可以通过调整以下配置选项来将默认存储引擎更改为MyISAM：

default_tmp_storage_engine = MyISAM

当然，这些解决方案也有一些缺点。您可以在MySQL故障排除文章中找到有关此错误、解决方案和缺点的完整详细信息。

好消息是，此问题的修复已在MySQL 8.0.23及更高版本中实现。

- Rami Far

Answer 3

清空大型表的最快方法是创建一个具有相同列的新表。与仅删除相比，截断会慢得多。

1. 禁用外键检查 SET foreign_key_checks = 0;

2. 将旧表重命名为要删除的表 RENAME TABLE old TO temp;

3. 创建与旧表完全相同的新空表 CREATE TABLE new LIKE old;

4. 删除旧表 DROP TABLE temp

5. 启用外键检查 SET foreign_key_checks = 1;

- texdevelopers

---

第三步可以使用CREATE TABLE new LIKE old;。 - Rick James

学到了新东西！ - texdevelopers