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

我需要将表’A’的标志从’X’重置为’Y’,其中行的update_date满足条件1. update_date> 1个月,2.标志=’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条记录.

解决方法:

改变与存在

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;

使用exists子句

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;

希望这可以帮助

（编辑：北几岛）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!