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

Question

我有一个Mysql系统，有一个包含1.7M条记录的表。这是一个生产系统。它以前是Myisam &非常有弹性，但作为测试，我将它转换为Innodb (和php脚本)，希望它运行得更快，行级锁定会使它更有弹性。它由30个使用PHP7CLI的机器人提供服务。他们中的每一个都扫描表中需要更新的记录，更新它们，然后继续作为团队的一部分，直到工作完成。他们在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“挂在行上”太长“。我选择一次只锁定一个--
• 是为了避免一次又一次地扫描很多行。不，OFFSET不是一个可行的覆盖自动提交的alternative.
• BEGIN。因此该事务将一直持续到使用autocommit=1运行第二个UPDATE，因此它本身就是一个事务。

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

Answer 2

在我对Innodb v MyIsam的测试中，我发现当我确实解决了任何争用问题时，Innodb模型比MyIsam慢40%。但是，我确实相信，通过进一步的调整，这可以减少，使其运行在与MyIsam相同的水平。

我确实注意到，MyIsam会无限期地排队“等待表级锁”，这实际上适合我，但却惩罚了硬盘。而Innodb是一个更加民主的过程，磁盘访问也更加均匀。我暂时回滚了它，但我将在几周内通过我上面评论的调整来追求Innodb版本。

在回答我自己的问题时:是的，Innodb可以通过大量调整和优化数据库设计来处理繁重的并行处理。令人失望的是，没有人回答我关于Innodb记录锁定是否知道它锁定了哪些记录的问题。