优化存在的MySQL更新查询的性能

Question

谁能给我一个提示，如何优化这个更新MySQL查询，需要花大约一分钟的时间处理？

UPDATE store s 
SET reservation=1 
WHERE EXISTS (
  SELECT 1 
  FROM item i 
  WHERE s.reservation=0 
    AND s.status!=9 
    AND s.id=i.store_id 
    AND i.store_id!=0
)

我需要更新(设置reservation=1)“存储”表中的所有行(这是非常大的)，其中当前有reservation=0，但是id存在于另一个表"item“中。表“项目”也很大，但不像“商店”那么大。

我不是创建高效查询的专家，所以请原谅我，如果这只是一个完全错误的态度，而且整个事情都有一个简单的解决方案。

谢谢你的建议。

Answer 1

看起来，关联子查询中的一些谓词可以移到外部查询中。例如，我相信这是等同的：

UPDATE store s 
   SET s.reservation = 1
 WHERE s.reservation = 0
   AND s.status != 9
   AND s.id != 0
   AND EXISTS ( SELECT 1
                  FROM item i
                 WHERE i.store_id = s.id
              )

为了获得最好的性能，我们至少希望在store上建立一个索引，该索引以reservation作为前面的列。此外，包含status和id列意味着可以从索引页中检查这些条件，而无需查找表中的基础页。

对于相关子查询(依赖查询)，我们希望在item上建立一个索引，并以store_id作为前面的列。

作为另一种选择，考虑将相关子查询重写为联接操作，例如：

UPDATE store s
  JOIN item i
    ON i.store_id = s.id
   SET s.reservation = 1
 WHERE s.reservation = 0
   AND s.status != 9
   AND s.id != 0

如果运行的是MySQL 5.5或更高版本，则无法获得UPDATE语句的解释。我们能得到的最接近的是将查询重写为SELECT，并获得对此的解释。MySQL 5.6支持对UPDATE语句进行解释。

Answer 2

您可以尝试使用：

UPDATE store s INNER JOIN item i ON s.id=i.store_id SET reservation=1 WHERE i.store_id!=0 AND s.reservation=0 AND s.status != 9;

这种情况应该工作得更快，因为每次需要检查“存储”行时，都不会对所有的“item”表进行检查。