从MySQL InnoDB中删除大量数据

Question

我需要删除生产数据库中约100GB大小的大量数据，如果可能的话，我希望尽量减少停机时间。

我的删除选择标准可能是：

DELETE * FROM POSTING WHERE USER.ID=5 AND UPDATED_AT<100

最好的删除方法是什么？

• 建立索引？
• 编写一个顺序脚本，通过每次分页删除1000行？

- meow

Answer 1

您可以尝试使用mysql doc中提到的方法：

1. 将不需要删除的行选择到一个与原始表具有相同结构的空表中：

   INSERT INTO t_copy SELECT * FROM t WHERE ... ;

2. 使用RENAME TABLE原子性地将原始表移出路径并将副本重命名为原始名称：

   RENAME TABLE t TO t_old, t_copy TO t;

3. 删除原始表：

   DROP TABLE t_old;

- Piotr Pankowski

Answer 2

如果可能的话，请使用行级二进制日志记录而不是语句级二进制日志记录（这将减少锁定数量），至少在此操作期间。按批次执行删除操作（1000是一个不错的大小）。使用主键作为删除每个批次的条件，并按主键排序（以便删除物理上相邻的行）。

- Eric J.

Answer 3

最好的方法是使用 LIMIT 子句（每 10000 项一次）递增删除，但不要排序。这将允许 MySQL 更频繁地刷新结果，事务也不会很大。您可以使用任何已安装有与 mysql 连接器的编程语言轻松完成此操作。在每个语句之后务必提交。

索引肯定会有所帮助，但对于一个 100 GB 的表来说建立它也需要一段时间（不管怎样，当您将来要重用该索引时，它是值得创建的）。顺便说一下，您当前的查询不正确，因为引用了未列在此处的 USER 表。您应该小心索引，以便优化器可以从中受益。

- newtover

---

除非您正在从一组复制的服务器中进行删除： “任何使用LIMIT的UPDATE、DELETE等语句并通过基于语句的复制机制(replication)传播到从服务器可能会导致不一致性(…)。这是因为Slave发现用于更新/删除的记录的实际顺序可能与Master不同，从而导致修改了不同的子集。为确保安全，请在此类语句中添加ORDER BY。此外，请务必确定ORDER BY是确定性的--也就是说，ORDER BY中的字段/表达式是唯一的。” http://mysql.rjweb.org/doc.php/deletebig#non_deterministic_replication - Sebastián Grignoli

Answer 4

前段时间，我想从一张表中删除超过99%的数据。我要删除的表是一个会话表，有超过2.5亿行数据，而我只需要最近的50万行。我想到的最快的方法是将我需要的50万行数据选择出来并存入另一张表中，然后删除旧表，将新表重命名为被删除的表的名称。这比普通的删除方式快了大约100倍，因为普通的删除方式需要选择记录并重建表。

如果你正在使用InnoDB和innodb_file_per_table = 1，这种方法还有一个额外的好处，就是可以减小表文件的大小，因为InnoDB表永远不会缩小。

- Mark Maunder