MySQL更新不使用某个值后带有WHERE IN子句的索引

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个或更少的id时，如果我尝试EXPLAIN，那么'possible_keys'和'key'显示PRIMARY，查询执行得非常快。

场景2：当in子句中有3000个或更多的id(最多30K(时，如果我尝试EXPLAIN，则'possible_keys'显示NULL，'key'显示PRIMARY，查询将永远运行。如果我使用FORCE INDEX(PRIMARY(，那么'possible_keys'和'key'显示PRIMARY，查询执行得非常快。

场景3：当in子句中的id超过30k时，即使我使用FORCE INDEX(PRIMARY(，'possible_keys'也会显示NULL，'key'会显示PRIMARY，查询将永远运行。

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

MySQL版本为5.7

Answer 1

据我所知，您只需要提供一个包含所有id的临时表，并从中加入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';

在mysql8中，您可以使用一个更简洁的值表构造函数(省略mariadb的"行"，例如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是一个具有所有相同更新值的表的重要块时，我会看到一个红旗。

是否总是更新同一组行？这些信息会在你加入的一个较小的单独表中吗？

或者，专注于帮助更新更快的其他结构架构更改？

如果你必须有一个很长的IN列表，我建议你一次做100个。并且不要试图在同一事务中COMMIT所有3000+。(批量提交会违反一些业务逻辑，所以您可能不想这样做。(