mysql - 使用 DELETE...WHERE NOT IN (SELECT) 删除无子父记录时如何避免超时

Question

我有两个非常大的 MySQL 表之间的多对一关系，如下所示:

行程 -> trippolylines

trips
==========================
BIGINT(20) trippolyline_id

trippolylines
=====================
BIGINT(20) id

外键关系是'On Delete: SET NULL':

CONSTRAINT `FK_trips_trippolylines_id1` FOREIGN KEY (`trippolyline_id`) REFERENCES `trippolylines` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION

我想删除 trippolylines 表中的所有无子父项记录 - 我不知道通过级联删除执行此操作的方法(有吗？) - 所以我正在使用它查询:

DELETE FROM trippolylines WHERE id NOT IN (SELECT trippolyline_id from trips);

问题是它一直超时

18:24:51    Error Code: 2013. Lost connection to MySQL server during query  600.008 sec

10 分钟后。

我可以增加超时 - 但在我这样做之前，有没有更有效的方法来处理这种情况？

解决方案

(V2)

由于结合了下面的评论和 Ike Walker 的回答，解决方案是使用 LEFT JOIN 而不是子查询来优化 DELETE 查询，如下所示:

DELETE trippolylines FROM trippolylines
LEFT JOIN trips ON id = trippolyline_id
WHERE trip_id IS NULL;

这运行速度明显更快(即至少 x10，尚未测量精确加速)并在大约 80 秒内完成。

我怀疑我可以进一步优化这个新查询以批量运行。

Answer 1

避免像这样长时间运行的删除查询超时的最佳方法是执行以下一项或两项操作:

1. 优化删除查询
2. 将删除分成多个较小的 block

对于 #1，根据您运行的 MySQL 版本，您可能会发现使用排除连接或 NOT EXISTS 查询比使用 NOT IN 的性能更快.

对于 #2，我通常使用 common_schema将删除分成多个 block 。这是我写的关于该主题的博客文章:http://mechanics.flite.com/blog/2014/11/24/alternatives-for-chunking-bulk-deletes-in-common-schema/