Update用join代替in优化执行效率

优化前 in

UPDATE tt_repair_order r
SET r.is_upload_cia = 12781001,
 r.upload_cia_by = 3,
 r.upload_cia_date = SYSDATE()
WHERE
    r.ro_id IN (
        SELECT
            ro_id
        FROM
            tt_vehicle_Record_temp
    );

即使用到的是主键索引仍然很慢

explain

优化后 join

UPDATE tt_repair_order r
JOIN (
 SELECT
  ro_id
 FROM
  tt_vehicle_Record_temp
) temp ON r.ro_id = temp.ro_id
SET r.is_upload_cia = 12781001,
 r.upload_cia_by = 3,
 r.upload_cia_date = SYSDATE();

用时在毫秒级

explain