如何避免在使用DELETE删除无子的父记录时超时.不在的地方(选择)

Question

我在两个非常大的MySQL表之间有许多一种关系，如下所示：

trips ->三角线

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

十分钟后。

我可以增加超时时间-但在此之前，是否有更有效的方法来处理这种情况？

解决方案

(V2)

由于下面的注释和Ike Walker的回答相结合，解决方案是使用LEFT JOIN而不是子查询优化删除查询，如下所示：

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

它运行速度要快得多(至少x10，还没有测量到精确的速度)，并在大约80秒内完成。

我怀疑我可以进一步优化这个新的查询，使其分批运行。

Answer 1

使用长时间运行的delete查询避免超时的最佳方法是执行以下一项或两项操作：

1. 优化删除查询
2. 将删除拆分为多个较小的块

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

对于#2，我通常使用模式将delete分解为多个块。这是我写的关于这个主题的博客文章：http://mechanics.flite.com/blog/2014/11/24/alternatives-for-chunking-bulk-deletes-in-common-schema/