带INNER JOIN与HAVING的DELETE查询执行耗时异常问题

我编写了一条DELETE查询，用于删除tbljournaalposten表中volgnr字段出现次数超过1次的所有记录，代码如下：

delete from tbljournaalposten
where tbljournaalposten.ID in(
   SELECT
      tbljournaalposten.ID
   FROM
      invoerdatum 
   INNER JOIN
      rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
   INNER JOIN
      tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
   WHERE
      rabobank2_2.invoerdatum = invoerdatum.invoerdatum
   GROUP BY  tbljournaalposten.volgnr
   HAVING  tbljournaalposten.volgnr >1

   ORDER BY
      rabobank2_2.Datum DESC )

在phpMyAdmin中执行该DELETE查询时，长时间加载后才停止，但目标记录已被删除；单独执行子查询中的SELECT语句（代码如下）时，响应迅速且结果正确：

SELECT
  tbljournaalposten.ID
FROM
  invoerdatum 
INNER JOIN
  rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
INNER JOIN
  tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
WHERE
  rabobank2_2.invoerdatum = invoerdatum.invoerdatum
GROUP BY   tbljournaalposten.volgnr
HAVING  tbljournaalposten.volgnr >1

ORDER BY
  rabobank2_2.Datum DESC

多次测试均出现此情况，预期该DELETE查询能正常快速执行。

首先修正逻辑错误 #

先提个关键问题：你的HAVING子句逻辑有误。tbljournaalposten.volgnr >1是判断volgnr字段的数值大于1，而非该volgnr在表中出现次数超过1次。如果要实现「删除出现次数超过1次的volgnr对应的所有记录」，正确的HAVING写法应该是：

HAVING COUNT(*) > 1

之前的逻辑可能误删了所有volgnr值大于1的记录，而非重复出现的记录，这点需要先确认。

优化DELETE执行速度 #

1. 用DELETE JOIN替代IN子查询

MySQL对IN子查询的优化一直不太友好，尤其是子查询关联多表时，数据库会反复扫描主表匹配子查询结果，导致锁表时间变长。改用DELETE JOIN能让优化器生成更高效的执行计划：

DELETE t
FROM tbljournaalposten t
JOIN (
    -- 先找出需要删除的重复volgnr（修正后的逻辑）
    SELECT tbljournaalposten.volgnr
    FROM invoerdatum 
    INNER JOIN rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
    INNER JOIN tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
    WHERE rabobank2_2.invoerdatum = invoerdatum.invoerdatum
    GROUP BY tbljournaalposten.volgnr
    HAVING COUNT(*) > 1
) AS dup_volgnrs ON t.volgnr = dup_volgnrs.volgnr;

这个写法的优势是子查询只返回重复的volgnr值，而非所有ID，减少了数据传输量，JOIN操作也更容易被优化器利用索引加速。

2. 移除不必要的排序

原子查询里的ORDER BY rabobank2_2.Datum DESC完全多余——IN子查询不需要排序结果，排序只会增加额外的CPU和IO开销，删掉它能直接减少子查询的执行时间。

3. 用临时表中转待删除ID

如果表数据量特别大，可以先把待删除的ID存入带索引的临时表，再执行删除：

-- 创建临时表，自动为主键ID建索引
CREATE TEMPORARY TABLE temp_del_ids (id INT PRIMARY KEY);

-- 插入待删除的ID（修正逻辑后的子查询）
INSERT INTO temp_del_ids
SELECT tbljournaalposten.ID
FROM invoerdatum 
INNER JOIN rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
INNER JOIN tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
WHERE rabobank2_2.invoerdatum = invoerdatum.invoerdatum
GROUP BY tbljournaalposten.volgnr
HAVING COUNT(*) > 1;

-- 执行删除，利用临时表的索引快速匹配
DELETE FROM tbljournaalposten WHERE ID IN (SELECT id FROM temp_del_ids);

-- 临时表会在会话结束后自动销毁，也可以手动删除
DROP TEMPORARY TABLE temp_del_ids;

最后检查索引 #

确保关联字段都有合适的索引，这是提升所有查询速度的基础：

• 给invoerdatum.IBAN_BBAN、invoerdatum.invoerdatum加索引
• 给rabobank2_2.IBAN_BBAN、rabobank2_2.Volgnr、rabobank2_2.invoerdatum加索引
• 给tbljournaalposten.Volgnr加索引（ID应该已经是主键，自带索引）

示例索引创建语句：

CREATE INDEX idx_invoerdatum_iban ON invoerdatum(IBAN_BBAN);
CREATE INDEX idx_rabobank2_2_volgnr ON rabobank2_2(Volgnr);
CREATE INDEX idx_tbljournaalposten_volgnr ON tbljournaalposten(Volgnr);

内容的提问来源于stack exchange，提问作者Johan