优化MySQL更新语句

Question

我有一个更新查询，它从一个表(HST)中选择数据并将其更新到另一个表(PCL)中。PCL表包含大约30000行，而HST表包含约110万行。我在HST上有多个(复合)索引，尽管它是一个大表，但对它的所有查询都相当快(~2-3秒)。但是，当我试图从这个表中选择行并更新PCL时，需要3-4个小时，而且在大多数情况下，我会得到“锁定等待超时；尝试重新启动事务”错误，并且我必须一直尝试直到它工作。

UPDATE PCL 
SET `T2A` = (SELECT HST.`Date` from `HST` WHERE HST.`SYM`=PCL.INST AND 
HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.T2 order by HST.`Date` limit 1)    
WHERE `BS` = 'B' AND `T1A` IS NOT NULL; 

是否有办法重写上面的查询，使其运行得更快？

Answer 1

想想这样的事情

UPDATE PCL, ( SELECT PCL.SrNo, MIN(HST.Date) minDate
              FROM PCL, HST 
              WHERE HST.SYM=PCL.INST 
                AND HST.DATE >=  PCL.Date 
                AND HST.HP >= PCL.T2 
                AND PCL.BS = 'B' 
                AND PCL.T1A IS NOT NULL
              GROUP BY PCL.SrNo ) subquery
SET PCL.T2A = subquery.minDate
WHERE PCL.SrNo = subquery.SrNo;

当然，两个表上的适当指数都是安全的。

Answer 2

另一种编写此类查询的方法是：

UPDATE PCL AS p
  JOIN HST AS h
    ON h.SrNo = 
       ( SELECT hi.SrNo
         FROM HST AS hi
         WHERE hi.SYM = p.INST
           AND hi.DATE >=  p.Date 
           AND hi.HP >= p.T2
         ORDER BY hi.Date 
         LIMIT 1 
       )    
SET p.T2A = h.Date 
WHERE p.BS = 'B'
  AND p.T1A IS NOT NULL ;