mysql - 如何加快与子查询关联的更新查询

Question

我有一个非常漂亮的查询，它包含 LEFT JOIN 子查询。完全加载需要 20 分钟。

这是我的查询:

UPDATE orders AS o

LEFT JOIN (
    SELECT obe_order_master_id, COUNT(id) AS count_files, id, added
    FROM customer_instalments 
    GROUP BY obe_order_master_id
) AS oci ON oci.obe_order_master_id = SUBSTRING(o.order_id, 4)

SET o.final_customer_file_id = oci.id,
    o.client_work_delivered  = oci.added

WHERE oci.count_files = 1

有什么方法可以让这个查询运行得更快？

Answer 1

在临时表中移动 Where 条件并将 WHERE 替换为 HAVING 子句，这将从临时表中消除不必要的行，从而减少过滤和可能有助于提高性能

UPDATE orders AS o
LEFT JOIN (
   SELECT obe_order_master_id, id, added
   FROM customer_instalments 
   GROUP BY obe_order_master_id
   HAVING COUNT(id) = 1 
) AS oci ON oci.obe_order_master_id = SUBSTRING(o.order_id, 4)
SET o.final_customer_file_id = oci.id,
    o.client_work_delivered  = oci.added