在某个值之后不使用WHERE子句的MySQL更新

Question

我们有一个拥有大约1000万条记录的表，我们试图使用where子句中的id(主键)更新一些列。

UPDATE  table_name SET column1=1, column2=0,column3='2022-10-30' WHERE id IN(1,2,3,4,5,6,7,......etc);

场景1:当in子句中有3000个或更少的ids时，如果我试图解释，那么'possible_keys‘和'key’将显示主ids，查询执行得非常快。

场景2:当in子句中有3000个或更多ids(最多30K)，如果我试图解释，那么'possible_keys‘显示NULL，'key’显示主和查询永远运行。如果我使用强制索引(主)，那么'possible_keys‘和'key’显示主查询和查询执行得非常快。

场景3:当in子句中有超过30k ids时，即使我使用强制索引(主)，'possible_keys‘显示为NULL，而'key’显示主和查询永远运行。

我相信优化器将进行全表扫描，而不是索引扫描。我们是否可以进行任何更改，使优化器进行索引扫描而不是表扫描？请建议是否需要对参数进行任何更改以解决此问题。

MySQL版本为5.7

Answer 1

据我所知，您只需提供一个带有所有it的即席表并从其中加入table_name：

update (select 1 id union select 2 union select 3) ids
join table_name using (id) set column1=1, column2=0, column3='2022-10-30';

在mysql 8中，您可以使用一个更简洁的值表构造函数(对于mariadb省略"row“，例如values (1),(2),(3))：

update (select null id where 0 union all values row(1),row(2),row(3)) ids
join table_name using (id) set column1=1, column2=0, column3='2022-10-30';

小提琴

Answer 2

当UPDATEing表中的一个重要部分具有所有相同的更新值时，我会看到一个红旗。

您总是更新同一组行吗？这个信息会在你加入的一个更小的单独的桌子里吗？

或者其他结构模式的改变，重点是帮助更新更快？

如果你必须有一个很长的清单，我建议一次做100。不要在同一个事务中尝试COMMIT所有的3000+。(大块提交违反了某些业务逻辑，因此您可能不想这样做。)