使用2个selects优化SQL查询

Question

我正在尝试使用基于user.id的最小used_time (datetime)更新单个campaign.id，但以下代码需要大约5秒才能执行。反向链接表包含一百万行。

UPDATE `backlinks` 
SET
`backlinks`.`crawler_id` = 'test', 
`backlinks`.`used_time`=NOW() 
WHERE
`backlinks`.`campaign_id`=(
SELECT `id` FROM `campaigns` 
WHERE `campaigns`.`completed`=false 
AND `campaigns`.`status`=true 
GROUP BY `campaigns`.`user_id` 
ORDER BY `campaigns`.`used_time` ASC 
limit 1
)
AND `backlinks`.`googlebot_id` IS NULL 
AND `backlinks`.`used_time` IS NULL 
LIMIT 1;

Answer 1

您可以尝试使用子查询使用JOIN执行UPDATE。

UPDATE `backlinks` b
JOIN (
    SELECT c.id
    FROM campaigns c
    WHERE exists (
        SELECT 1 
        FROM campaigns cc
        WHERE c.user_id = cc.user_id
        GROUP BY cc.user_id
        HAVING min(cc.used_time) = c.used_time
    )
) t1 on b.`campaign_id` = t1.id
SET
    b.`crawler_id` = 'test', 
    b.`used_time`=NOW() 
WHERE 
    b.`googlebot_id` IS NULL 
AND 
    b.`used_time` IS NULL