mysql - Mysql Innodb 能否处理繁重的并行处理

Question

我有一个 Mysql 系统，其中有一个 1.7M 记录的表。这是一个生产系统。它以前是 Myisam 并且非常有弹性，但作为测试，我将它转换为 Innodb(和 php 脚本)，希望它运行得更快，并且行级锁定会使它更有弹性。它由 30 个使用 PHP 7 CLI 的机器人提供服务。他们每个人都扫描表格以查找需要更新的记录，更新它们然后作为团队的一部分继续进行，直到工作完成。他们以 40 行为一组执行此操作，这意味着脚本运行了大约 42,500 次。

但是在测试过程中，我注意到 Innodb 事务的一些功能是我没有预料到的，而且似乎很糟糕。在我回滚之前，我想我会询问其他人的意见，无论我是否完全错了，或者证明或反驳我的发现。问题围绕一个数据库调用(所有搜索字段都已编入索引)展开，下面是伪代码:

update table set busy=$token where condition=true order by id $order limit $units
if affected rows != $units
do function to clear
return
else do stuff.....
endif

之前在 Myisam 下，结果是每个机器人都在获取表级锁时运行，然后排队直到获得它们。这可能会产生瓶颈，但所有问题都会在一分钟内解决。

之后在 Innodb 下，一个机器人的调用是可以的，但任何尝试多用户工作的结果都会导致“超出锁定等待超时；尝试重新启动事务'。更改 wait_timeout/autocommit/tx_isolation 没有区别。也不会将其转换为交易并使用:

begin
select .... for update
update 
test
commit or rollback

在我看来:

1 即使您没有设置事务，Innodb 也会为所有更新创建一个隐式事务。如果这些花费的时间太长，则无法进行并行处理。

2 更重要的是，当 Innodb 锁定行时，它并不“知道”它锁定了哪些行。你不能这样做:

begin
select 10 rows where condition=this for update
update the rows I locked
commit

你必须像这样做两个相同的调用:

begin
select 10 rows where condition=this for update
update 10 rows where condition=this
commit

这是死锁的秘诀，因为 robot1 可能锁定 40 行，robot2 锁定其他 40 行，依此类推，然后 robot1 更新 40 行，这可能与它刚刚锁定的行完全不同。这将一直持续到所有行都被锁定并且它们无法写回表为止。

因此，在我有 30 个机器人争夺需要更新的大块行的情况下，在我看来 Innodb 对我来说毫无用处。它很聪明，但还不够聪明，无法处理繁重的并行处理。

任何想法...

Answer 1

思考这个方法:

SET autocommit = 1;
Restart:
$left_off = 0;
Loop:
  # grab one item:
  BEGIN;
    $id = SELECT id FROM tbl WHERE condition AND id > $left_off
             ORDER BY id LIMIT 1 FOR UPDATE;
    if nothing returned, you are end of table, COMMIT and GOTO Restart
    UPDATE tbl SET busy = $token WHERE id = $id;
  COMMIT;
  do stuff
  UPDATE tbl SET busy = $free WHERE id = $id;  -- Release it
  $left_off = $id;
Goto Loop

注意事项:

• 似乎设置busy 的唯一原因是“do stuff”是否在该行挂起“太长”。我说得对吗？
• 我选择一次只锁定一个——复杂度较低。
• $left_off 是为了避免一次又一次地扫描很多行。不，OFFSET 不是可行的替代方案。
• BEGIN 覆盖自动提交。因此该事务一直持续到 COMMIT。
• 第二个 UPDATE 以 autocommit=1 运行，因此它本身就是一个事务。

一定要调整机器人的数量——太少 = 太慢；太多 = 太多争用。很难预测最优值。