MySQL中10000行数据批量更新的性能优化问题

你遇到的这个场景我太熟悉了——维护着一个最多存10000行的表T，表结构如下：

CREATE TABLE T(
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    weight MEDIUMINT NOT NULL,
    PRIMARY KEY(id)
);

每6小时要根据新的id->weight映射更新数据，最坏情况要更新全表10000行。一开始你想着用INSERT ... ON DUPLICATE KEY UPDATE的批量写法把所有更新打包成一个语句，省得多次和数据库交互：

INSERT INTO T(id, weight) VALUES ({id_1}, {weight_1}), ..., ({id_n}, {weight_n}) 
ON DUPLICATE KEY UPDATE weight = VALUES(weight);

本来速度还过得去，但很快就发现了坑：表的AUTO_INCREMENT值居然一直在蹭蹭涨，哪怕你根本没插入新行，只是更新现有数据。这不仅看着闹心，长期下来还可能提前碰到SMALLINT UNSIGNED的自增上限（65535），显然不是你想要的结果。

给你几个亲测有效的解决方案，按需选择：

• 方案一：调整InnoDB自增锁模式（首推）
  如果你用的是InnoDB引擎，这是最完美的解决办法。在执行批量插入前，先执行这句会话级的设置：

  SET SESSION innodb_autoinc_lock_mode = 0;
  

  把自增锁模式改成0（传统模式）后，InnoDB只会在真正插入新行的时候才会增加AUTO_INCREMENT的值，触发重复键更新的情况不会再乱涨自增ID。等批量操作执行完，你可以改回默认的模式（一般是1）：

  SET SESSION innodb_autoinc_lock_mode = 1;
  

  这个方法完全不影响批量操作的性能，还能彻底解决自增ID无故上涨的问题。

• 方案二：用CASE WHEN做分块批量UPDATE
  如果你不想再用INSERT的套路，也可以换成真正的批量UPDATE写法，把10000行分成多个小批次（比如每次1000行），用CASE WHEN来批量赋值：

  UPDATE T 
  SET weight = CASE id 
      WHEN 1 THEN 123
      WHEN 2 THEN 456
      -- 这里依次放1000个id对应的weight值
  END
  WHERE id IN (1,2,...,1000); -- 只更新目标id，避免全表扫描
  

  分块的好处是单条语句不会太长，MySQL处理起来更稳定，也不会占用过多内存。10000行分10次执行，总耗时和单条大语句差不了多少，而且完全不会碰自增ID的逻辑，从根源上避免问题。

• 方案三：临时移除自增属性（仅应急用）
  这个方法比较极端，只适合低峰期应急：先把表的AUTO_INCREMENT属性去掉，执行完更新后再加回来。操作语句大概是这样：

  -- 先去掉自增
  ALTER TABLE T MODIFY COLUMN id SMALLINT UNSIGNED NOT NULL;
  -- 执行批量更新
  INSERT INTO T(id, weight) VALUES (...) ON DUPLICATE KEY UPDATE weight = VALUES(weight);
  -- 加回自增
  ALTER TABLE T MODIFY COLUMN id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT;
  

  但要注意，ALTER TABLE会锁表，如果有其他业务在写这个表会受影响，所以尽量别把这个当常规方案用。

最后再提两个性能小细节：

1. 不管用哪种方案，都把批量操作放在事务里执行，比如10个分块UPDATE包在一个START TRANSACTION;和COMMIT;之间，能大幅减少磁盘IO和锁的开销，速度会更快。
2. 提前过滤掉那些weight值和现有数据完全一致的id，能减少无效更新的行数，变相提升性能。

备注：内容来源于stack exchange，提问作者user25485418