INNER JOIN UPDATE查询执行过慢(耗时10分钟)优化问题咨询

耗时10分钟是否正常 #

完全不正常。两张表总数据量仅9万行，在无特殊硬件/资源瓶颈的前提下，这类关联更新操作正常耗时应该在秒级甚至毫秒级，10分钟的耗时说明执行逻辑存在明显可优化空间。

核心优化方案 #

• 第一步：查看执行计划定位问题
  执行如下语句查看执行计划，确认是否因为未走索引导致全表扫描：

  EXPLAIN UPDATE year_2021 t1 
  INNER JOIN pricelist t2 
  ON t1.material = t2.material
  SET t1.rabat = t2.rs
  

  如果输出结果中type列出现ALL，说明对应表触发了全表扫描，是慢查询的核心诱因。
• 第二步：为关联字段加索引（核心优化手段）
  本次查询的关联条件为material字段，优先为pricelist表创建覆盖索引，避免回表查询：

  -- 覆盖索引：关联字段material在前，需要查询的rs字段在后，直接从索引取数无需回表
  CREATE INDEX idx_material_rs ON pricelist(material, rs);
  

  如果year_2021表的material字段也经常用于关联查询，也可以为其添加索引：

  CREATE INDEX idx_material ON year_2021(material);
  

  加完索引后再次执行更新语句，正常情况下耗时会降到1秒以内。
• 第三步：生产环境可选分批更新
  如果是线上生产环境，为了避免一次性更新3万行导致表锁长时间阻塞其他业务，可以拆分为多批次小量更新，示例逻辑如下：

  -- 每次更新1000行，循环执行直到所有行更新完成
  UPDATE year_2021 t1 
  INNER JOIN pricelist t2 ON t1.material = t2.material
  SET t1.rabat = t2.rs
  WHERE t1.rabat != t2.rs
  LIMIT 1000;
  

• 第四步：检查锁阻塞情况
  如果加完索引还是慢，可以查询数据库的锁等待信息，确认是否有其他长事务持有year_2021或者pricelist表的锁，导致当前更新操作一直处于等待状态。

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