MySQL关联UPDATE查询无限停滞，同条件SELECT秒执行，求原因

核心原因拆解 #

• 读写锁机制的本质差异
  SELECT属于读操作，InnoDB默认隔离级别（可重复读）下会用快照读，直接读取undo log里的历史版本，不需要加排他锁，也不会阻塞其他读操作，执行效率极高。而UPDATE是写操作，每一行数据修改都需要加排他锁（X锁），如果有其他事务正在访问sale表的行，就会进入锁等待状态；即使没有锁冲突，逐行加锁、释放锁的过程本身也比纯读操作耗时。

• 关联结果的重复匹配导致多次更新
  你提到SELECT返回12000行，但sale表只有10000行，说明存在一个销售记录匹配到多个员工的情况（比如重名的员工）。SELECT只是返回所有匹配结果，但UPDATE会对每一次匹配执行一次写入操作——同一个sale行如果被匹配到N次，就会被更新N次，这会产生大量重复的锁竞争和日志写入操作，直接拖慢整体执行速度。

• 写操作的额外开销
  UPDATE除了完成SELECT一样的关联匹配逻辑，还要执行数据修改、redo log写入、undo log生成这些IO密集型操作，而SELECT只需要读取数据返回结果。哪怕是10000行的写入，加上日志持久化的开销，也会比纯读慢很多。

• 临时表与执行计划的隐性差异
  SELECT的执行计划可能只是做简单的全表扫描+匹配后返回，但UPDATE需要将关联后的结果集（12000行）临时存储，再逐行对应更新原表的行，这个临时表的创建、维护以及后续的行映射操作，都会额外增加耗时。

排查与解决建议 #

1. 先检查重名数据
   执行以下查询确认是否存在一个姓名对应多个员工的情况：

   SELECT sale.salesperson, COUNT(DISTINCT employee.employee_id) 
   FROM sale 
   JOIN employee ON sale.salesperson = concat(employee.first_name,' ',employee.last_name) 
   GROUP BY sale.salesperson 
   HAVING COUNT(DISTINCT employee.employee_id) > 1;
   

   如果存在重名，必须先明确匹配规则（比如加中间名、工号前缀），否则更新后的数据会出现覆盖错误。

2. 分批执行UPDATE
   避免一次性更新所有行，拆分成分批次更新，减少单次锁持有时间：

   -- 每次更新1000行，根据实际id范围调整
   UPDATE sale 
   INNER JOIN employee ON sale.salesperson = concat(employee.first_name,' ',employee.last_name) 
   SET salesperson_employee_id = employee.employee_id
   WHERE sale.id BETWEEN 1 AND 1000;
   

3. 检查锁等待情况
   执行SHOW PROCESSLIST;查看是否有其他事务占用了sale表的锁，导致当前UPDATE长时间等待。

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