如何优化mysql中select with in子句中的删除？

Question

要从包含100 k条记录的表中删除大约90k条记录，我会：

 delete from table1 where id in ( select id from table2 ) ; 

大约需要30分钟，如果我用某种编程语言构造相同的动态，它在不到一分钟的时间内就会运行。

for i in (records to delete) 
        delete from table1 where id = i

如何优化原生mysql查询？

Answer 1

我建议您使用一个联接操作而不是一个IN (subquery)，并确保在table1上有一个合适的索引(以id作为前面的列)：

DELETE t1.*
  FROM table1 t1
  JOIN table2 t2
    ON t2.id = t1.id

编辑

我不知道为什么我的答复被否决了。如果这是一个糟糕的答案，或者没有帮助，那么我希望对为什么这是一个糟糕的答案发表评论。

这个答案比我的大多数答案都要简洁；我想我可以解释一下为什么一个合适的索引对于良好的性能是必要的。

OP的问题是如何使用col IN (subquery)谓词“优化删除”。我认识到，我没有解释为什么这种模式会导致次优性能，以及为什么联接操作有时会更高效。OP问“如何优化本机sql查询”。简单的回答是重写查询。

Answer 2

Mysql对IN子查询不好，请使用

delete from table1 where exists ( select 1 from table2 WHERE table1.id=table2.id ) ; 

Answer 3

试试这个：

DELETE 
  table1.*
FROM 
  table1, table2
WHERE 
  table1.id = table2.id

或者使用连接。如果可能的话，尽量避免进入。