大数据集场景下更新关联表不存在的字段值方案咨询

原有SQL执行过慢的核心原因：单次覆盖全表所有符合条件的记录，执行时会持有大量行锁甚至锁表，且相关子查询逐行匹配500万级用户表的效率极低，最终导致执行时间过长。可参考以下优化方案：

• 新增联合索引减少扫描开销
  给entries表新增联合索引idx_userid_status，包含user_id和status两个字段，无需回表即可完成user_id IS NOT NULL和status条件的过滤，大幅降低数据扫描成本：

CREATE INDEX idx_userid_status ON entries(user_id, status);

• 按主键范围分小批量更新
  每次仅处理少量数据，避免长事务锁表，批次大小可根据数据库实际负载调整（建议先在100~1000区间测试执行速度再正式执行：

-- 初始化最后处理的主键ID
SET @last_processed_id = 0;
-- 循环执行直到无待更新数据
WHILE ROW_COUNT() > 0 DO
  UPDATE entries e
  LEFT JOIN users u ON e.user_id = u.id
  SET e.user_id = NULL
  WHERE e.id > @last_processed_id
    AND e.user_id IS NOT NULL
    AND e.status = 'active'
    AND u.id IS NULL
  ORDER BY e.id ASC
  LIMIT 1000;
  -- 更新下一批次的起始ID
  SELECT MAX(id) INTO @last_processed_id FROM entries WHERE id > @last_processed_id ORDER BY id ASC LIMIT 1000;
  -- 每批次执行后休眠1秒，降低对业务读写的影响
  DO SLEEP(1);
END WHILE;

• 改用JOIN更新替代EXISTS子查询
  左连接的执行计划在大多数关系型数据库中执行效率远高于逐行执行的相关子查询，可减少大量匹配查询开销。
• 分状态串行处理
  active状态的记录处理完成后，再依次调整status条件处理inactive、blocked状态的记录，进一步缩小单次处理的数据范围。
• 低峰执行+隔离级别调整
  操作尽量在业务低峰期执行，执行前可将当前会话的事务隔离级别调整为读提交，减少锁等待冲突。

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