MySQL UPDATE 语句批处理以避免大量 TRX 大小

Question

我经常编写更新数百万行数据的数据清理。数据驻留在使用 InnoDB 的 24x7x365 OLTP MySQL 数据库中。更新可能会清理表的每一行(其中 DB 最终获得表级锁)或者可能只是清理表中 10% 的行(可能仍为数百万)。

为了避免创建大量事务并最大程度地减少争用，我通常最终会尝试将我的一个大型 UPDATE 语句分解为一系列较小的 UPDATE 事务。所以我最终写了一个循环结构来限制我的 UPDATE 的 WHERE 子句，如下所示:

(警告:这只是为了说明问题的伪代码)

@batch_size=10000;
@max_primary_key_value = select max(pk) from table1

for (int i=0; i<=@max_primary_key_value; i=i+@batch_size)
{
start transaction;

update IGNORE table1
set col2 = "flag set"
where col2 = "flag not set"
and pk > i
and pk < i+@batchsize;

commit;
}

由于很多原因，这种方法很糟糕。

我想在数据库不尝试将所有正在更新的记录分组为单个事务单元的情况下发出 UPDATE 语句。我不希望 UPDATE 作为一个工作单元成功或失败。如果 1/2 的行无法更新...没问题，请告诉我。本质上，每一行都是它自己的工作单元，但批处理或游标是我能弄清楚如何将其表示给数据库引擎的唯一方法。

我考虑过为我的 session 设置隔离级别，但在这种特定情况下这似乎对我没有帮助。

还有其他想法吗？

Answer 1

也许不是您正在寻找的答案，但您可以通过在更新中使用 LIMIT 来稍微简化您的代码。

伪代码:

do {
  update table1 set col2 = 'flag set' where col2 = 'flat not set' LIMIT 10000
} while (ROW_COUNT() > 0)