借助MySQL执行计划优化跨表慢更新查询

1. 优化关联字段的索引 #

• 确保TBL1与TB2用于关联的字段（如主键、业务唯一键）都建立普通索引或唯一索引，避免全表扫描。若DDL中未包含对应索引，执行以下语句添加：

  ALTER TABLE TBL1 ADD INDEX idx_tbl1_join_field (关联字段名);
  ALTER TABLE TB2 ADD INDEX idx_tb2_join_field (关联字段名);
  

• 核对执行计划：若计划中出现ALL类型扫描，排查是否存在隐式类型转换（如关联字段一个是INT、一个是VARCHAR），这会导致索引失效。

2. 拆分大更新为批量操作 #

一次性全表更新会占用大量锁资源与IO带宽，拆分成分批更新可显著降低负载：

SET @batch_size = 1000; -- 根据服务器负载调整，建议1000-5000
SET @max_id = (SELECT MAX(主键字段) FROM TBL1);
SET @current_id = 0;

WHILE @current_id < @max_id DO
    UPDATE TBL1 t1
    JOIN TB2 t2 ON t1.主键字段 = t2.主键字段
    SET t1.更新字段1 = t2.字段1, t1.更新字段2 = t2.字段2
    WHERE t1.主键字段 BETWEEN @current_id AND @current_id + @batch_size;
    
    SET @current_id = @current_id + @batch_size + 1;
    COMMIT; -- 每批提交释放锁资源
END WHILE;

3. 简化UPDATE语句逻辑 #

• 避免在SET子句中使用复杂函数或嵌套子查询，直接用TB2的字段赋值。
• 仅更新TBL1中存在匹配记录的数据，用WHERE EXISTS过滤无效更新：

  UPDATE TBL1 t1
  SET t1.字段1 = (SELECT 字段1 FROM TB2 t2 WHERE t2.关联字段 = t1.关联字段),
      t1.字段2 = (SELECT 字段2 FROM TB2 t2 WHERE t2.关联字段 = t1.关联字段)
  WHERE EXISTS (SELECT 1 FROM TB2 t2 WHERE t2.关联字段 = t1.关联字段);
  

4. 调整事务与锁策略 #

• 临时降低会话的事务隔离级别至READ COMMITTED，减少锁持有时间：

  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

• 避免将所有更新放入单个大事务，每批更新后及时提交，释放锁资源。

5. 用临时表预处理更新数据 #

若TB2数据需提前过滤或计算，先将目标数据导入临时表并加索引，再执行更新：

-- 创建临时表并导入需要更新的数据
CREATE TEMPORARY TABLE tmp_update_data
SELECT 关联字段, 字段1, 字段2 FROM TB2 WHERE 过滤条件;

-- 给临时表添加关联字段索引
CREATE INDEX idx_tmp_join_field ON tmp_update_data(关联字段);

-- 用临时表更新TBL1
UPDATE TBL1 t1
JOIN tmp_update_data t2 ON t1.关联字段 = t2.关联字段
SET t1.字段1 = t2.字段1, t1.字段2 = t2.字段2;

-- 清理临时表
DROP TEMPORARY TABLE tmp_update_data;

6. 排查进程资源竞争 #

• 查看进程列表，终止长时间运行的大查询、锁等待进程，避免资源抢占。
• 执行SHOW ENGINE INNODB STATUS，检查TRANSACTIONS模块的锁等待信息，定位阻塞源。

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