mysql - 使用 2 个选择优化 SQL 查询

Question

我正在尝试根据 user.id 更新具有最小 used_time(日期时间)的单个 campaign.id，但执行以下代码需要大约 5 秒。反向链接表包含 100 万行。

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

你可以试试UPDATE与 JOIN通过子查询。

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