mysql - 使用来自 SELECT 子查询的值高效地更新查询

Question

我试图提出一个查询，使用同一个表中的其他记录来更新 MySQL 表中的记录，但我在本地测试和生产之间得到了混合结果。我对子查询不太了解，所以我想在这里提出这个问题。在使用 MySQL InnoDB 5.6.23 的本地开发中，对大约 180k 条记录的数据集的查询需要 25 到 30 秒。在装有 MySQL InnoDB 5.5.32 和包含 254k 条记录的数据集的临时服务器上，查询似乎会停顿数小时直到停止，占用 100% 的 CPU 核心。

这是我提出的查询:

UPDATE
    `product_lang` AS `pl1`
SET
    pl1.`name` = (
        SELECT pl2.`name` FROM (SELECT `name`, `id_product`, `id_lang` FROM `product_lang`) AS `pl2`
        WHERE pl1.`id_product` = pl2.`id_product`
        AND pl2.`id_lang` = 1
    )
WHERE
    pl1.`id_lang` != 1

目的是将id_lang不为1的产品记录中name的值(为了便于说明，默认语言)替换为的值默认 id_lang 为 1 的记录值的名称。

我知道子查询效率低下，但我真的不知道如何解决这个问题，将其留在 SQL 领域而不是使用应用层来完成繁重的工作将是一个很好的补充。

Answer 1

如果你这样写查询:

UPDATE product_lang pl1
    SET pl1.name = (SELECT pl2.`name`
                    FROM (SELECT `name`, `id_product`, `id_lang`
                          FROM `product_lang`
                         ) `pl2`
                    WHERE pl1.`id_product` = pl2.`id_product` AND pl2.`id_lang` = 1
                   )
WHERE pl1.`id_lang` <> 1

那你就有问题了。唯一有帮助的索引是 product_lang(id_lang)。

我建议将其写成join:

UPDATE product_lang pl1 join
       (select id_product, pl.name
        from product_lang
        where id_lang = 1
       ) pl2
       on pl1.id_lang <> 1 and pl2.id_product = pl1.id_product
    SET pl1.name =  pl2.name
WHERE pl1.id_lang <> 1

此查询所需的索引是 product_lang(id_lang, id_product) 和 product_lang(id_product)。然而，这似乎是一个奇怪的更新，因为它将所有名称设置为来自语言 1 的名称。