MySQL中取模导致UPDATE/DELETE死锁的解决方案咨询

核心问题 #

当使用user_id % N = y作为分片条件时，MySQL无法利用user_id索引，会触发全表扫描并锁定全表，进而与针对单条user_id的DELETE操作产生死锁。以下是几种可行的优化方案，按推荐优先级排序：

1. 新增分片键列并建立索引 #

这是最优解，从根源上解决索引失效问题：

• 新增一列（例如shard_key），存储user_id % N的计算结果（N为线程数），并为该列创建普通索引。
• 现有数据可通过批量更新初始化：

  UPDATE table SET shard_key = user_id % 4;
  

• 数据写入时同步计算并设置shard_key，也可通过触发器自动维护，确保user_id变更时同步更新该列。
• 后续每个线程只需执行：

  UPDATE table SET data = x WHERE shard_key = y;
  

  该语句会直接命中shard_key索引，仅锁定目标分片的行，彻底避免全表扫描和大范围锁，死锁概率大幅降低。

2. 分步查询+批量UPDATE（你提到的方案） #

这种方案无需修改表结构，是快速见效的折中方案：

• 先执行查询获取目标user_id列表：

  SELECT user_id FROM table WHERE user_id % 4 = y;
  

• 再用IN(...)执行UPDATE：

  UPDATE table SET data = x WHERE user_id IN (1,2,3,...);
  

• 优势：UPDATE语句能命中user_id索引，仅锁定需要更新的行，减少锁冲突；如果数据量极大，建议分批处理IN列表（例如每次取1000条user_id执行UPDATE），避免性能问题。

3. 关于嵌套SELECT的UPDATE语句 #

你提到的嵌套SELECT写法可行但不推荐：

UPDATE table SET data = x WHERE user_id IN (SELECT user_id from table WHERE user_id % 4 = y)

内层SELECT依然会全表扫描，MySQL优化器通常不会将其转换为高效的索引查询，最终还是会导致大范围锁，和直接用user_id % 4 = y的UPDATE效果差异不大。

4. 其他替代思路 #

• 分区表方案：按user_id % N对表进行分区，每个线程对应一个分区。操作分区数据时，仅锁定对应分区，不会影响其他分区，同时避免全表扫描，适合数据量极大的场景。
• 范围分片替代取模：将user_id按范围分配给线程（例如线程0处理0-9999，线程1处理10000-19999），使用WHERE user_id BETWEEN a AND b作为条件，直接命中user_id索引，锁范围更小。缺点是如果user_id分布不均，可能出现线程负载失衡，需定期调整分片范围。

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