MySQL跨表更新执行过慢且提示查询期间丢失服务器连接问题求助

核心问题排查步骤 #

• 首先通过执行计划确认扫描逻辑
  先运行如下语句查看SQL的执行计划，确认是否存在全表扫描的问题：

  EXPLAIN UPDATE `db_a`.`table1`  
  INNER JOIN `db_a`.`table2` ON `table2`.`table2_id` = `table1`.`table1_id`  
  SET `table1`.`table1_number` = `table2`.`table2_number`
  WHERE `table1`.`year` = 2019;
  

  如果执行计划的type列出现ALL，说明触发了全表扫描，是超时的核心原因。

• 补充缺失的索引
  针对上述场景需要确保两个索引存在：

  1. table1的year字段需建立普通索引，过滤2019年数据时避免扫描全表
  2. 关联字段table1.table1_id、table2.table2_id需建立索引，避免JOIN时全表匹配

• 确认关联匹配行数是否符合预期
  运行如下统计语句，确认实际需要更新的行数是否和预期一致：

  SELECT COUNT(*) FROM `db_a`.`table1`  
  INNER JOIN `db_a`.`table2` ON `table2`.`table2_id` = `table1`.`table1_id`  
  WHERE `table1`.`year` = 2019;
  

  如果返回结果远大于3000，说明存在一对多的关联关系，会触发重复更新，既拖慢速度也会导致数据异常，需要先排查关联逻辑的合理性。

• 大数量场景下的优化方案
  如果确认需要更新的行数确实很大，可以采用两种方案避免超时：

  1. 临时调整超时参数（仅当前实例生效，重启后恢复默认）：

  SET GLOBAL net_read_timeout = 600;
  SET GLOBAL net_write_timeout = 600;
  

  2. 分批次更新，每次更新固定行数，避免长事务阻塞和连接超时：

  UPDATE `db_a`.`table1`  
  INNER JOIN `db_a`.`table2` ON `table2`.`table2_id` = `table1`.`table1_id`  
  SET `table1`.`table1_number` = `table2`.`table2_number`
  WHERE `table1`.`year` = 2019
  LIMIT 1000;
  

  循环执行上述语句直到更新行数为0即可。

• 排查锁等待问题
  运行SHOW PROCESSLIST;查看当前MySQL进程，确认是否有其他长事务持有table1的写锁，导致更新语句长时间等待最终超时。

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