mysql - 选择更新与更新，然后选择

我创建了一个服务应用程序，该应用程序使用多线程对位于InnoDB表中的数据进行并行处理(大约2-3百万条记录，并且该应用程序不再执行与InnoDB相关的查询)。每个线程对上述表进行以下查询:

开始交易

SELECT FOR UPDATE(从表WHERE status ='new'LIMIT 100 FOR UPDATE中选择pk)

UPDATE(更新表SET状态=“已锁定”，在X和Y之间的pk处)

COMMIT

删除(从X和Y之间的表WHERE pk中删除)

来自forum.percona.com的人给了我一些建议-不要使用SELECT FOR UPDATE和UPDATE，因为事务执行(2个查询)需要更长的时间，并且等待锁定超时。他们的建议是(启用自动提交):

UPDATE(更新表SET status ='locked'，线程= Z LIMIT 100)

SELECT(从表WHERE线程= Z中选择pk)

删除(从X和Y之间的表WHERE pk中删除)

并应该改善性能。但是，相反，我比以前有更多的死锁和等待锁定超时...

我读了很多关于优化InnoDB的知识，并相应地调整了服务器，因此我的InnoDB设置可以达到99％。第一种情况的效果很好，并且比第二种情况更好，也证明了这一事实。 my.cnf文件:

innodb_buffer_pool_size = 512M
innodb_thread_concurrency = 16
innodb_thread_sleep_delay = 0
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit=2

为什么优化没有成功的任何想法？

最佳答案

从您对过程的描述中我了解到:

您有一个表，其中包含许多需要处理的行。

您从该表中选择一行(用于更新)，以便其他线程无法访问同一行。

完成后，您将更新行并提交事务。

然后从数据库中删除该行。

如果是这种情况，那么您在做正确的事情，因为与您提到的第二种方法相比，它的锁更少。

您可以通过删除delete语句来进一步减少锁争用，因为这将锁定整个表。而不是这样做，而是添加一个标志(名为已处理的新列)并进行更新。并且在所有线程完成处理后，删除最后的行。

您还可以通过分批处理工作负载来使工作分配智能化-在您的情况下，每个线程将要处理的行范围(可能使用PK)-在这种情况下，您可以进行简单的选择，而无需进行FOR UPDATE子句，它将快速运行。