使用SELECT结果的UPDATE语句执行卡顿问题求助

问题场景 #

执行以下SQL时，UPDATE语句卡顿无法完成，但查询临时表nullmeters正常，且直接用临时表中的具体ID值执行UPDATE则能正常运行：

原卡顿SQL #

SET sql_safe_updates=0;
create temporary table nullmeters
    select me.id from met me
    join  tab1 ac on ac.accounts_id = me.id
    join tab2 po on ac.val1 = po.id
    join tab3 te on te.id = me.team_id
    where
    me.name = 'NULL';

select * from nullmeters;

update tab1 set deleted = 1 where id in (
select id from nullmeters
);

update met set deleted = 1 where id in (
select id from nullmeters
);

drop temporary table nullmeters;

正常运行的SQL #

update tab1 set deleted = 1 where accounts in (
'1',
'2'
);

update met set deleted = 1 where id in (
'1',
'2'
);

• 临时表缺少索引：临时表nullmeters仅存储id字段但未建立索引，UPDATE时每次子查询都要全表扫描临时表。若临时表数据量较大，扫描耗时会剧增。给临时表的id添加主键或普通索引即可优化：

  create temporary table nullmeters
  select me.id from met me
  join tab1 ac on ac.accounts_id = me.id
  join tab2 po on ac.val1 = po.id
  join tab3 te on te.id = me.team_id
  where me.name = 'NULL';
  -- 给临时表id字段加索引
  alter table nullmeters add primary key(id);
  

• 子查询执行计划低效：MySQL处理WHERE id IN (SELECT id FROM nullmeters)时，可能将子查询转换为相关子查询（对主表每一行都执行一次子查询），而非先提取临时表数据再匹配。改用JOIN写法可避免这个问题：

  -- 更新tab1的优化写法
  update tab1 ac
  join nullmeters nm on ac.id = nm.id
  set ac.deleted = 1;
  
  -- 更新met的优化写法
  update met me
  join nullmeters nm on me.id = nm.id
  set me.deleted = 1;
  

• 主表关联字段无索引：检查tab1.id和met.id是否有主键或索引，若这些字段无索引，UPDATE时需全表扫描主表再匹配临时表，数据量大时必然卡顿。确认主表id是否为主键（主键默认带索引），若不是，给tab1.id和met.id添加索引。
• 锁等待或事务冲突：若有其他事务正在操作tab1或met表的相关行，会导致UPDATE语句等待锁释放。可执行SHOW ENGINE INNODB STATUS;查看当前锁等待情况，确认是否有长时间未提交的事务占用锁资源。

内容的提问来源于stack exchange，提问作者Ian Rogers