php - 使用子查询和连接查询更新行——真的很慢

Question

我需要将表 'A' 的标志从 'X' 重置为 'Y'，其中 a 的 update_date行满足条件 1。 update_date > 1 个月，2.flag = 'X' & 3.type = 1。

并根据另一个表 'B' 检查 update_date。我希望以下查询能够解释我到底需要什么。此查询也适用于我。但问题是花费的时间太长了。实际上，我的表 A 和 B 大得多，几乎包含十亿行，大约有 10 列。

当我运行子查询以选择 A.id 时，我立即得到了结果。

 SELECT a.id 
 FROM A a 
 JOIN B b 
 ON (a.id = b.id 
       AND a.name = b.name 
       AND a.type = 1 
       AND a.flag = 'X' 
       AND a.update_date > DATE_SUB(NOW(), INTERVAL 1 MONTH) tmp_table)

但只有更新查询，即使我设置了限制也需要很多时间。

UPDATE A 
SET flag='Y' 
WHERE id IN (SELECT a.id 
             FROM A a 
             JOIN B b 
             ON (a.id = b.id 
                   AND a.name = b.name 
                   AND a.type = 1 
                   AND a.flag = 'X' 
                   AND a.update_date > DATE_SUB(NOW(), INTERVAL 1 MONTH) tmp_table))  
             LIMIT 100

我正在寻找可以加快查询速度的替代解决方案。希望我能为它写一个存储过程。但是在 SP 中，我应该遍历每个 target_ids 对吗？

我不想在 PHP 中编写两个单独的查询，因为我的 PHP 脚本有许多线程在 cron 上运行，它们返回相同的结果(时间延迟)。

还要注意，我确实有足够的列索引。

希望按限制更新限制。即，每次运行更新 1000 多条记录。

Answer 1

改变存在

EXISTS 会更快，因为一旦引擎找到匹配项，它就会在条件被证明为真时停止查找。使用 IN 它将在进一步处理之前收集子查询的所有结果。

UPDATE A a
JOIN B b 
ON (a.id = b.id 
               AND a.name = b.name 
               AND a.type = 1 
               AND a.flag = 'X' 
               AND a.update_date > DATE_SUB(NOW(), INTERVAL 1 MONTH))
SET a.flag='Y'
ORDER BY a.id LIMIT 1000;

EDITED 支持替代 LIMIT(IT 将仅更新前 100 条记录)

SET @rn = 0;
UPDATE A a
JOIN (SELECT @rn:=@rn+1 AS rId, id, name FROM B b 
      JOIN A a 
      ON (@rn < 100 AND a.id = b.id 
               AND a.name = b.name 
               AND a.type = 1 
               AND a.flag = 'X' 
               AND a.update_date > DATE_SUB(NOW(), INTERVAL 1 MONTH)
      ) 

) b 
ON (a.id=b.id) 
SET a.flag='Y'
WHERE b.rId < 100;

使用exist子句

Update A a 
SET a.flag='Y'
WHERE EXISTS (SELECT 1 FROM B b WHERE a.id = b.id 
               AND a.name = b.name 
               AND a.type = 1 
               AND a.flag = 'X' 
               AND a.update_date > DATE_SUB(NOW(), INTERVAL 1 MONTH)) 
ORDER BY a.id LIMIT 1000;

希望对你有帮助