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

I am in need of resetting flag of a table 'A' from 'X' to 'Y' where the update_date of a row satisfies the conditions 1. update_date > 1 month, 2. flag = 'X' & 3. type = 1.

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

And the update_date is checked against another table 'B'. I hope the following query will explain what exactly I need. Also this query works fine for me. But the problem is it is taking too long time. Actually my tables A & B are much bigger almost contains billion rows and there are about 10 columns.

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

When I run my sub query for selecting A.id I got the result immediately.

当我运行我的子查询选择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)

But only the update query even if I put limit also it's taking much time.

但是只有更新查询即使我放了限制也需要花费很多时间。

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

I am looking for alternate solutions of my query which makes it fast. Hope I could write a stored procedure for it. But in SP I should loop through for each target_ids right?

我正在寻找我的查询的替代解决方案,使其快速。希望我能为它编写一个存储过程。但是在SP中我应该为每个target_ids循环一次吗?

I don't wish to write two separate queries in PHP, since there are many threads of my PHP scripts running on cron which returns same results (time latency).

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

Also to note, I do have enough indexing for columns.

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

Wish to update limits by limit. ie., update 1000+ records for every run.

希望通过限制更新限制。即,为每次运行更新1000+条记录。

3 个解决方案

#1

Change in with exists

改变与存在

EXISTS will be faster because once the engine has found a hit, it will quit looking as the condition has proved true. With IN it will collect all the results from the subquery before further processing.

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 Supporting substitute of LIMIT (IT will update only 1st 100 records)

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;

Using exist clause

使用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;

Hope this helps

希望这可以帮助

#2

You can use a join too

您也可以使用联接

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

#3

Finally, I got the better performing optimized query. Simply A join to temp table.

最后,我得到了性能更好的优化查询。简单地加入临时表。

UPDATE A AS a JOIN (
SELECT a.id FROM A AS a JOIN B AS b ON
    b.type = a.type 
    AND b.name = a.name 
    AND b.last_update_date < DATE_SUB(NOW(), INTERVAL 1 MONTH) 
    AND a.type = 1 
    AND a.flag = 'X' 
ORDER BY a.id DESC LIMIT 1000)
AS source ON source.id = a.id
SET flag = 'Y';

Thanks to http://www.xaprb.com/blog/2006/08/10/how-to-use-order-by-and-limit-on-multi-table-updates-in-mysql

感谢http://www.xaprb.com/blog/2006/08/10/how-to-use-order-by-and-limit-on-multi-table-updates-in-mysql

#1