MYSQL嵌套查询运行非常慢？

Question

下面的查询经常超时，有没有一种开销更小的方法来实现同样的功能？

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

我正在做的是从事务中取消分配发票，可能会返回多达30+记录，但每次我尝试运行它时，它都会停止数据库

Answer 1

使用JOIN而不是subquery会提高性能。

在两个表中的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;

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
)

Answer 3

从MySQL 5.5开始，不能优化子查询选择(查询中的另一个完整select语句)。这可能就是您的查询如此缓慢的原因。重构查询以去除内部select语句。

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

有趣的旁注..。但是MariaDB (最初创建者对MySQL的一个分支)已经实现了子查询选择优化。