MySQL中Select正常执行但Delete无限挂起的根因排查

咱们先把问题里的两个DELETE写法摆出来，对比着看核心差异：

挂起的写法： #

delete from table1 where ID in ( select min(a.ID) from (select * from table1) a group by id_x, id_y, col_z having count(*) > 1)

正常执行的写法： #

delete from table1 where ID in ( select a.ID from (select min(ID) from table1 group by id_x, id_y, col_z having count(*) > 1) a)

根本原因拆解 #

1. 冗余数据拖垮临时表性能 #

第一个写法里的内层子查询(select * from table1) a会把整个table1的所有列都加载到临时表，再对这个臃肿的临时表做GROUP BY聚合。而第二个写法的内层子查询只处理ID列，临时表体积是前者的几分之一甚至几十分之一（取决于表的列数）。

这种冗余会直接导致：

• 内存不足时被迫写入磁盘，触发大量磁盘IO；
• GROUP BY的排序、聚合操作耗时呈指数级上升，看起来就像“无限挂起”。

2. 锁竞争与表扫描的双重压力 #

执行DELETE时，数据库（比如InnoDB）需要对目标行加锁。第一个写法中，外层DELETE和内层子查询都在访问table1：

• 子查询全表扫描加载所有列时，会持有大量锁或频繁触发锁等待；
• DELETE本身也要扫描表匹配ID，两层操作的锁竞争进一步拖慢执行，甚至因资源耗尽卡住。

而第二个写法的子查询能快速生成待删除ID列表，DELETE只需根据ID精准匹配行，锁竞争和扫描成本大幅降低。

3. 优化器未识别冗余操作 #

数据库优化器有时无法智能识别：你在子查询用select *，但实际只用到min(a.ID)——它不会自动把select *简化为select ID，导致执行计划走了低效的全表加载路径。而第二个写法明确只取ID列，优化器直接生成了高效的聚合计划。

预防机制（避坑指南） #

• 绝对不要在子查询里用select *除非必需：永远只获取需要的列，冗余列会在嵌套子查询里放大资源消耗，尤其是聚合操作前的子查询。
• 把聚合操作放在最内层子查询：GROUP BY、min()这类聚合逻辑要尽量靠近原表，减少中间临时表的数据量，让计算更早完成。
• 尝试用JOIN替代IN子查询：对于DELETE操作，JOIN写法有时比IN子查询更高效，比如：

  delete t1 from table1 t1
  join (
      select min(ID) as target_id from table1 
      group by id_x, id_y, col_z having count(*) > 1
  ) t2 on t1.ID = t2.target_id;
  

• 用EXPLAIN排查执行计划：遇到慢查询或卡住的语句，先跑EXPLAIN看执行计划，重点关注临时表类型、扫描方式、数据量，快速定位冗余步骤。

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