MySQL WHERE NOT IN极其缓慢

Question

以下是一个存储过程中的SQL语句（为了简洁而省略部分内容）：

SELECT * 
FROM item a 
WHERE a.orderId NOT IN (SELECT orderId FROM table_excluded_item);

这个语句需要大约30秒钟的时间！但如果我删除内部的SELECT查询，它会降到1秒。 table_excluded_item 不是很大，但我怀疑内部查询被执行了比必要更多的次数。

有没有更有效率的方法来做这件事？

- pixelfreak

---

内部查询是一个依赖子查询，因为子查询针对外部查询的每一行运行，所以它们是臭名昭著的瓶颈。请参阅MySQL开发网站上的"优化子查询"（http://dev.mysql.com/doc/refman/5.1/en/optimizing-subqueries.html）。 - Anthony

Answer 1

使用LEFT JOIN

SELECT  a.* 
FROM    item a 
        LEFT JOIN table_excluded_item b
            ON a.orderId = b.orderId
WHERE   b.orderId IS NULL

确保两个表中的orderId都已经被索引。

- John Woo

---

嘿，那个有效，谢谢！现在只剩下2秒了。这种语言对我来说总是非常反直觉的 :( - pixelfreak

一个聪明的解决方案，尽管我没有看到性能的提升。但是在过去的9年里，情况已经发生了变化。 - Brad Rhoads

Answer 2

左连接方法的问题在于可能会处理重复记录来生成输出。有时，情况并非如此……根据article的说法，当列被索引时，MySQL确实正确地优化了left outer join，即使存在重复项。尽管如此，我仍然持怀疑态度，认为这种优化并不总是发生。

MySQL有时无法优化带有子查询的IN语句。最好的解决方法是相关子查询：

SELECT * 
FROM item a 
WHERE not exists (select 1
                  from table_excluded_item tei
                  where tei.orderid = a.orderid
                  limit 1
                 )

如果在 table_excluded_item.orderid 上有索引，那么这将扫描索引并停留在第一个值处（limit 1 对此可能不是严格必要的）。这是在 MySQL 中实现您想要的最快和最安全的方法。

- Gordon Linoff

---

从技术上讲，“limit 1”并不是必要的；“反连接”仍然会做完全相同的事情。（可能是因为mysql不够聪明） - wildplasser

左连接方法的问题在于输出结果可能会包含重复记录。因为你正在寻找不存在的记录，所以会导致这种情况的发生。 - John Woo

@jW . . . 我重新表达了这个。 - Gordon Linoff

Answer 3

试一下这个，然后与 LEFT JOIN 查询时间进行比较：

SELECT * 
FROM item a 
HAVING orderId NOT IN (SELECT orderId FROM table_excluded_item);

在 IT 相关领域，使用 HAVING 而不是 WHERE 是被不赞成的。因为 HAVING 假设限制条件 (orderId) 是结果集的一部分。但我认为在这种情况下，使用 HAVING 更有意义（因为它是结果集的一部分），并且比使用 LEFT JOIN 更清晰易懂。

实际上，这可能会稍微慢一些，但请发布结果，以便我们知道它是否比您原来的查询更好。

- Anthony

---

干杯 - 在我的数据集上，我始终得到了约7秒的结果，而左连接方法则需要约8秒。 - hoju