MYSQL嵌套查询运行非常缓慢？

Question

以下查询一直超时，有没有更低开销的方法来实现相同的功能？

UPDATE Invoices SET ispaid = 0 
WHERE Invoice_number IN (SELECT invoice_number
    FROM payment_allocation
    WHERE transactionID=305)

我正在进行的操作是从交易中取消发票的分配，可能会返回30多个记录，但每次尝试运行它时，它都会使数据库停止工作。

- Steven Moffat

Answer 1

请使用JOIN而不是子查询，这将提高性能。

如果您还没有创建，请在两个表中的Invoice_number列上创建索引。

尝试这样做：

UPDATE Invoices i 
INNER JOIN payment_allocation pa ON i.Invoice_number = pa.invoice_number 
SET i.ispaid = 0 
WHERE pa.transactionID = 305;

- Saharsh Shah

---

太棒了，内连接表比嵌套表更有效率！非常感谢。 - Steven Moffat

@stevenmoffat 不要忘记接受任何一个你认为适合你的答案。 - Saharsh Shah

Answer 2

我建议尝试使用EXISTS关键字：

UPDATE Invoices a set ispaid=0 
WHERE EXISTS 
(
 SELECT NULL FROM payment_allocation b 
 WHERE b.Invoice_number =a.Invoice_number AND b.transactionID=305
)

- a1ex07

---

为什么要尝试使用 exists？ - Steven Moffat

对我来说，这似乎是合乎逻辑和清晰明了的 - 如果另一个表中存在至少一条满足某些条件的记录，则在一个表中更新该记录。 INNER JOIN 产生相同的执行计划，并且给出相同的结果，因此从性能角度来看没有区别。 - a1ex07

Answer 3

从MySQL 5.5开始，子查询选择（查询内部的另一个完整查询语句）无法进行优化。这可能是您的查询速度缓慢的原因。重构您的查询以消除内部选择语句。

 UPDATE Invoices, payment_allocation
  SET ispaid=0 
  WHERE payment_allocation.transactionID=305 AND       
        Invoices.Invoice_number = payment_allocation.invoice_number 

一个有趣的旁注...但是MariaDB（MySQL的一个分支，由原始创建者创建）已经实现了子查询选择优化。

- Ray

Answer 4

UPDATE invoices i
  JOIN payment_allocation pa
    ON pa.invoice_number = i.invoice_number
   SET i.ispaid=0 
 WHERE pa.transactionID = 305;

- Strawberry